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
>