Calculating Row Size

[Carl Williams]

Does anyone know of a stored procedure that will calculate the max row size
for a table. I found the formula in the BOL (Estimating the Size of a
Table), but was wandering if it is already written up in a stored proc
somewhere before I created one of my own.

Thanks,
Carl Williams
Carl.W...@OraMetrix.com

[Andrew J. Kelly]

Carl,

What do you mean by calculating the MAX row size? It's fixed at 8060 bytes
(not including Text / Image).

--
Andrew J. Kelly
Targitmail.com

"Carl Williams" <Carl.W...@OraMetrix.com> wrote in message
news:O#tL1SMgAHA.1764@tkmsftngp05...

[Carl Williams]

By MAX row size I mean that if a table has variable length columns, what
would the row size be for that particular table definition with all of its
variable length columns filled 100%.

In other words, I am wanting the max row size for that particular table
definition, not for any table definition.

Sorry for the confusion.

Thanks,
Carl Williams

"Andrew J. Kelly" <ake...@targitmail.com> wrote in message
news:OSNf1jMgAHA.2044@tkmsftngp04...

[Gert-Jan Strik]

Carl,

there are several factors involved, for example whether all column definitions
are fixed width or not. My advice would be to get a hold of "Inside SQL-Server
7.0" (or Inside SQL-Server 2000) by Kalen Delaney and Ron Soukup. This books
covers this issue in great detail.

Gert-Jan

Carl Williams wrote:

--
(Please reply only to the newsgroup)

[Carl Williams]

I created this stored procedure and I believe it will do what I want. If
anyone has a better way, or sees somewhere I have messed up, please let me
know.

The reason I am wanting this is because Replication has lower row size
limitations and I am wanting to check if any of my tables exceed the
replication row size limitations before I start using replication.

Thanks,
Carl Williams

CREATE PROCEDURE sp_CalcRowSize
@TableName varchar(128)
AS

DECLARE @Num_Cols int
DECLARE @Fixed_Data_Size int
DECLARE @Num_Variable_Cols int
DECLARE @Max_Var_Size int
DECLARE @Null_Bitmap int
DECLARE @Variable_Data_Size int
DECLARE @Row_Size int

SELECT
@Num_Cols = COUNT(*),
@Fixed_Data_Size =
SUM
(
CASE
WHEN DATA_TYPE = 'bigint' THEN 8
WHEN DATA_TYPE = 'int' THEN 4
WHEN DATA_TYPE = 'smallint' THEN 2
WHEN DATA_TYPE = 'tinyint' THEN 1
WHEN DATA_TYPE = 'decimal' OR DATA_TYPE = 'numeric' THEN
CASE
WHEN NUMERIC_PRECISION <= 9 THEN 5
WHEN NUMERIC_PRECISION >= 10 AND NUMERIC_PRECISION <= 19 THEN 9
WHEN NUMERIC_PRECISION >= 20 AND NUMERIC_PRECISION <= 28 THEN 13
ELSE 17
END
WHEN DATA_TYPE = 'float' OR DATA_TYPE = 'real' THEN
CASE
WHEN NUMERIC_PRECISION <= 24 THEN 4
ELSE 8
END
WHEN DATA_TYPE = 'money' THEN 8
WHEN DATA_TYPE = 'smallmoney' THEN 4
WHEN DATA_TYPE = 'datetime' THEN 8
WHEN DATA_TYPE = 'smalldatetime' THEN 4
WHEN DATA_TYPE = 'char' THEN CHARACTER_OCTET_LENGTH
WHEN DATA_TYPE = 'nchar' THEN CHARACTER_OCTET_LENGTH
WHEN DATA_TYPE = 'binary' THEN CHARACTER_OCTET_LENGTH + 4
ELSE 0
END
),
@Num_Variable_Cols =
SUM
(
CASE
WHEN DATA_TYPE = 'varchar' THEN 1
WHEN DATA_TYPE = 'nvarchar' THEN 1
WHEN DATA_TYPE = 'varbinary' THEN 1
ELSE 0
END
),
@Max_Var_Size =
SUM
(
CASE
WHEN DATA_TYPE = 'varchar' THEN CHARACTER_OCTET_LENGTH
WHEN DATA_TYPE = 'nvarchar' THEN CHARACTER_OCTET_LENGTH
WHEN DATA_TYPE = 'varbinary' THEN CHARACTER_OCTET_LENGTH
ELSE 0
END
)
FROM
information_schema.columns
WHERE
table_name = @TableName

SET @Null_Bitmap = 2 + ((@Num_Cols + 7) / 8)

IF @Num_Variable_Cols > 0
SET @Variable_Data_Size = 2 + (@Num_Variable_Cols * 2) + @Max_Var_Size
ELSE
SET @Variable_Data_Size = 0

SET @Row_Size = @Fixed_Data_Size + @Variable_Data_Size + @Null_Bitmap + 4

SELECT
@TableName AS Table_Name,
@Row_Size AS Row_Size,
@Num_Cols AS Num_Cols,
@Fixed_Data_Size AS Fixed_Data_Size,
@Num_Variable_Cols AS Num_Variable_Cols,
@Max_Var_Size AS Max_Var_Size,
@Null_Bitmap AS Null_Bitmap,
@Variable_Data_Size AS Variable_Data_Size

RETURN @Row_Size