How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a numbe
r
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point an
d
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before addin
g
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
KeenerHi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
----
----
----
----
--
---
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
----
----
----
----
-- -- -- --
-- --
--
----
----
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
----
----
---
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
----
----
No rowguidcol column defined.
Data_located_on_filegroup
----
----
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:

> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database
.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "W
e
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will
be
> permitted to be added. For example, if I wish to add a field to CRM as te
xt
> and set it to have a limit of 6K, then CRM will only allow me to add a num
ber
> of fields adding up to 2K. The thing is also is that CRM will not allow y
ou
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point
and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before add
ing
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKI
NG
> CRM?
> Thanks for any help.
> Regards,
> Keener|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You can create a row that is greater than than 8060 characters, but you wi
ll
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDA
TE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ----
---
> ----
---
> --
> ---
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length P
rec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ----
---
> ----
---
> -- -- -- --
> -- --
> --
> ----
---
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replicatio
n
> ----
---
> ---
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ----
---
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ----
---
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
>|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:

> Keener wrote:
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>sql

How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a number
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point and
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before adding
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
KeenerHi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
------
------
--
---
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- --
-- --
--
------
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
------
---
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "We
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will be
> permitted to be added. For example, if I wish to add a field to CRM as text
> and set it to have a limit of 6K, then CRM will only allow me to add a number
> of fields adding up to 2K. The thing is also is that CRM will not allow you
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before adding
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKING
> CRM?
> Thanks for any help.
> Regards,
> Keener|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
> Hi
> You can create a row that is greater than than 8060 characters, but you will
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ------
> ------
> --
> ---
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ------
> ------
> -- -- -- --
> -- --
> --
> ------
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replication
> ------
> ---
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ------
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ------
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
> > I have a question. A bit new to SQL Server, but how does one
> > determine the row size of a given table. Here is my issue:
> >
> > I am a CRM user and I understand that CRM uses SQL Server for its database.
> >
> > When using SQL Server on its own, it will only allow ~8k of DATA to enter
> > the database and will truncate the rest (from my understanding anyway).
> > However, CRM has set its own restrictions on the database and says that "We
> > will not allow
> > any data that will exceed SQL Server's limits of ~8k and therefore we will
> > calculate
> > the size of the fields in the table and determine if anymore columns will be
> > permitted to be added. For example, if I wish to add a field to CRM as text
> > and set it to have a limit of 6K, then CRM will only allow me to add a number
> > of fields adding up to 2K. The thing is also is that CRM will not allow you
> > to DELETE or reconfigure any columns, therefore, if I said to make the new
> > field to be 1K, it will not allow me. Basically CRM breaks at this point and
> > I can no longer add ANYTHING to this table, but I need to.
> >
> > Bearing this in mind, I wish to check and see what the table row size is
> > before hand so I can allocate certain fields more appropriately before adding
> > them to know the size of each field. Is there an store procedure to get
> > these values from SQL Server, so I can know if I am on the brink of BREAKING
> > CRM?
> >
> > Thanks for any help.
> >
> > Regards,
> > Keener|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:
> Keener wrote:
> > I have a question. A bit new to SQL Server, but how does one
> > determine the row size of a given table. Here is my issue:
> >
> > I am a CRM user and I understand that CRM uses SQL Server for its
> > database.
> >
> > When using SQL Server on its own, it will only allow ~8k of DATA to
> > enter the database and will truncate the rest (from my understanding
> > anyway). However, CRM has set its own restrictions on the database
> > and says that "We will not allow
> > any data that will exceed SQL Server's limits of ~8k and therefore we
> > will calculate
> > the size of the fields in the table and determine if anymore columns
> > will be permitted to be added. For example, if I wish to add a field
> > to CRM as text and set it to have a limit of 6K, then CRM will only
> > allow me to add a number of fields adding up to 2K. The thing is
> > also is that CRM will not allow you to DELETE or reconfigure any
> > columns, therefore, if I said to make the new field to be 1K, it will
> > not allow me. Basically CRM breaks at this point and I can no longer
> > add ANYTHING to this table, but I need to.
> >
> > Bearing this in mind, I wish to check and see what the table row size
> > is before hand so I can allocate certain fields more appropriately
> > before adding them to know the size of each field. Is there an store
> > procedure to get these values from SQL Server, so I can know if I am
> > on the brink of BREAKING CRM?
> >
> > Thanks for any help.
> >
> > Regards,
> > Keener
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a number
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point and
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before adding
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
Keener
Hi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
------
------
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- --
-- --
------
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
------
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:

> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "We
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will be
> permitted to be added. For example, if I wish to add a field to CRM as text
> and set it to have a limit of 6K, then CRM will only allow me to add a number
> of fields adding up to 2K. The thing is also is that CRM will not allow you
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before adding
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKING
> CRM?
> Thanks for any help.
> Regards,
> Keener
|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You can create a row that is greater than than 8060 characters, but you will
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ------
> ------
> --
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ------
> ------
> -- -- -- --
> -- --
> --
> ------
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replication
> ------
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ------
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ------
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:

> Keener wrote:
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

how to determine a cursor?

Can you elaborate a bit more? Are you trying to update every row in the
table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
news:TFOng.17571$sM4.70290@.weber.videotron.net...
I have this procedure that executes only once. How can I do so it does
update every lines in the database?
Thanks
declare @.tlongtextvar as varchar (8000),
@.tspecK as int
select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
@.tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @.tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')I have this procedure that executes only once. How can I do so it does
update every lines in the database?
Thanks
declare @.tlongtextvar as varchar (8000),
@.tspecK as int
select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
@.tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @.tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')|||Can you elaborate a bit more? Are you trying to update every row in the
table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
news:TFOng.17571$sM4.70290@.weber.videotron.net...
I have this procedure that executes only once. How can I do so it does
update every lines in the database?
Thanks
declare @.tlongtextvar as varchar (8000),
@.tspecK as int
select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
@.tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @.tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')|||I'm trying to update every row in one table with the appropriate contextual
update
Thanks
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> a crit dans le message de
news: OAcHH8QmGHA.3468@.TK2MSFTNGP03.phx.gbl...
> Can you elaborate a bit more? Are you trying to update every row in the
> table?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
> news:TFOng.17571$sM4.70290@.weber.videotron.net...
> I have this procedure that executes only once. How can I do so it does
> update every lines in the database?
> Thanks
>
> declare @.tlongtextvar as varchar (8000),
> @.tspecK as int
> select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
> @.tspecK = dbo.tLongTxt.k
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
> '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
> (dbo.tLongTxt.K = @.tspecK)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>|||I'm trying to update every row in one table with the appropriate contextual
update
Thanks
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> a crit dans le message de
news: OAcHH8QmGHA.3468@.TK2MSFTNGP03.phx.gbl...
> Can you elaborate a bit more? Are you trying to update every row in the
> table?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
> news:TFOng.17571$sM4.70290@.weber.videotron.net...
> I have this procedure that executes only once. How can I do so it does
> update every lines in the database?
> Thanks
>
> declare @.tlongtextvar as varchar (8000),
> @.tspecK as int
> select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
> @.tspecK = dbo.tLongTxt.k
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
> '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
> (dbo.tLongTxt.K = @.tspecK)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>

How to design "product kits"

Hi,

I've run into a bit of a sticky design issue. We have products in
three categories which I will call 'A', 'B' and 'C'. We have "kits"
which contain three products, one from each category.

Below is some sample SQL to set things up, but I need to ensure that
each kit gets three products -- one from each category. Obviously,
this basic SQL doesn't allow that. Any suggestions? Do I need a
different schema design, or is there something else I should be
looking at?

Cheers,
Curtis

CREATE TABLE category (
id int identity primary key,
name varchar(30)
);

CREATE TABLE products (
id int identity primary key,
name varchar(30),
category_id int references category(id)
);

CREATE TABLE kits (
id int identity primary key,
name varchar(30)
);

CREATE TABLE kit_products (
kit_id int references kits(id),
product_id int references products(id)
);>> We have products in three categories which I will call 'A', 'B' and
'C'. <<

... and you declared them as INTEGER.

>> We have "kits" which contain three products, one from each
category. <<

So, do you have only three categories??

>> Do I need a different schema design, ... <<

Oh yeah! You do not have any keys (IDENTITY is never a key by
definition) and "id" is to vague to be a data element name (read
ISO-11179 rules). Category is singular, while the other table names
are plural; ergo, category must have one and only one row? All the
important data is NULL-able.

I am going to assume that you have so many categories that they
require a separate table; if not, put them in a CHECK() clause.

CREATE TABLE Categories
(category_id INTEGER PRIMARY KEY,
category_name VARCHAR(30) NOT NULL);

CREATE TABLE Products
(product_name VARCHAR(30) NOT NULL,
product_id INTEGER NOT NULL UNIQUE,
category_id INTEGER NOT NULL
REFERENCES Categories(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (product_id, category_id));

CREATE TABLE ProductKits
(kit_id INTEGER NOT NULL
kit_name VARCHAR(30) NOT NULL,
product_id_1 INTEGER NOT NULL,
category_id_1 INTEGER NOT NULL
FOREIGN KEY (product_id_1, category_id_1)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
product_id_2 INTEGER NOT NULL,
category_id_2 INTEGER NOT NULL
FOREIGN KEY (product_id_2, category_id_2)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
product_id_3 INTEGER NOT NULL,
category_id_3 INTEGER NOT NULL
FOREIGN KEY (product_id_3, category_id_3)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
CHECK (category_id_1 = 1
AND category_id_2 = 2
AND category_id_3 = 3));

The sneaky trick is to put both (product_id, category_id) in the
primary key of Products, so both can be referenced. The product_id is
still unique (another assumption, since your original schema allowed a
product to be named NULL or repeated under a thousand different
IDENTITY numbers, making data integrity impossible). I also assume
that the categories for the kits is (1, 2, 3) instead of ('a', 'b',
'c').

If there are onlyn three categories, then use this and no separate
Categories table:

CREATE TABLE Products
(product_name VARCHAR(30) NOT NULL,
product_id INTEGER NOT NULL UNIQUE,
category_id INTEGER NOT NULL
CHECK(category_id IN (3,2,1))
PRIMARY KEY (product_id, category_id));

How To Defrag SQL Server 2000

Hi everyone, I am fairly new with SQL Server and need a little bit of help in regards to boosting my SQL servers performance. I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before. Can someone please help guide me through the necessary steps to execute this task. Thanks in advance.I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before.
Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.

You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.

You can fragment your indexes for better performance, syntax is given below.

DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]|||Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.

You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.

You can fragment your indexes for better performance, syntax is given below.

DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]
I am curious; is there any value in doing a backup/restore?

I have a daily scheduled run of Executive Software's Diskkeeper on all the servers. That keeps the files defragged on the file-system level, but of course doesn't reorder anything within the database.

As I understand it, the concept of Defragmenetation offers an optimization of physical aspects of the disk drive (rotations, head movements) and the software activities of piecing together the fragments. It follows that having all the bits of an index in order would have a similar effect (as you described above).

I guess in a database there's also a matter of eliminating all the holes left by prior deletes and of spreading indexes out more intelligently.

So then; I'm displaying a complete ignorance of "database layer fragmentation". Am I missing a lot of fundamentals in my thinking?

Question: Would it be a benifit to backup-then-restore a database?|||Backing up and restoring a database has no effect on fragmentation. Database fragmentation that is, the DBA's nerves will become highly fragmented if this sort of thing is implemented. In Oracle, you can export and import tables to remove fragmentation, which may be what you are thinking of. In SQL Server, a backup collects all pages that have data on them, and stashes them away. On a restore, the data pages are simply rewritten in place. without any moving of data around the pages.

Database fragmentation happens mainly with deletes, sometimes with updates, and somewhat less frequently with inserts (depending on your indexes).

Suppose you have a data page that originally has 20 entries (rows) in it. When you read in that page, you get 20 rows in memory. Suppose further that 19 of these rows are deleted. Now when you read in the same 8KB page, you only get 1 row of data. The space taken up by the rows that were there is not reclaimed automatically, and depending on insert/update activity and clustered index layout may not ever be reclaimed unless you rebuild the indexes. Rebuilding the indexes has the effect of re-arranging, or regenerating the entries packed closer together making read operations more efficient.

Here is a link to a decent paper about it. Note, users tend to not notice the difference, until tables get above 10,000 pages or so.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx|||I am curious; is there any value in doing a backup/restore?

yes, there is some value|||Check this, a single web page consist of various subjects for SQL Server Maintenance.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/default.mspx