估算表的长度

--估算表的长度
--varchar(max),nvarchar(max),varbinary(max)未估算在内
declare
@TableName nvarchar(50);

SELECT @TableName='admin.Menu';

WITH Base AS
(
SELECT DATA_TYPE
--可变长度类型取最大值的一半,不包含bit类型
,CASE DATA_TYPE
WHEN 'bigint' THEN 8
WHEN 'smallint' THEN 2
WHEN 'int' THEN 4
WHEN 'tinyint' THEN 1
WHEN 'money' THEN 8
WHEN 'smallmoney' THEN 4
WHEN 'datetime' THEN 8
WHEN 'smalldatetime' THEN 4
WHEN 'numeric' THEN CASE WHEN NUMERIC_PRECISION BETWEEN 1 AND 9 THEN 5
WHEN NUMERIC_PRECISION BETWEEN 10 AND 19 THEN 9
WHEN NUMERIC_PRECISION BETWEEN 20 AND 28 THEN 13
WHEN NUMERIC_PRECISION BETWEEN 29 AND 38 THEN 17
ELSE 0
END
WHEN 'float' THEN CASE WHEN NUMERIC_PRECISION BETWEEN 1 AND 24 THEN 4
WHEN NUMERIC_PRECISION BETWEEN 25 AND 53 THEN 8
ELSE 0
END
WHEN 'real' THEN 4
WHEN 'char' THEN CHARACTER_MAXIMUM_LENGTH
WHEN 'varchar' THEN CASE WHEN CHARACTER_MAXIMUM_LENGTH=-1 THEN 0 ELSE CHARACTER_MAXIMUM_LENGTH/2 END
WHEN 'nchar' THEN CHARACTER_OCTET_LENGTH
WHEN 'nvarchar' THEN CASE WHEN CHARACTER_MAXIMUM_LENGTH=-1 THEN 0 ELSE CHARACTER_MAXIMUM_LENGTH END
WHEN 'binary' THEN CHARACTER_MAXIMUM_LENGTH
WHEN 'varbinary' THEN CASE WHEN CHARACTER_MAXIMUM_LENGTH=-1 THEN 0 ELSE CHARACTER_MAXIMUM_LENGTH/2 END
ELSE 0
END AS DATA_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA+'.'+TABLE_NAME=@TableName
AND DATA_TYPE<>'bit'
UNION
--bit类型一个字节存储8位
SELECT DATA_TYPE,(COUNT(*)-1)/8+1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA+'.'+TABLE_NAME=@TableName
AND DATA_TYPE='bit'
GROUP BY DATA_TYPE
UNION
--可变长度类型每列需要两个字节开销，每行需要两个字节开销
SELECT '可变',COUNT(*)*2+2
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA+'.'+TABLE_NAME=@TableName
AND DATA_TYPE IN ('varchar','nvarchar','varbinary')
)
SELECT SUM(DATA_LENGTH)+10 AS [每行估计字节数] --每行需要10个字节开销
,8096/(SUM(DATA_LENGTH)+10) AS [每页估计行数]
FROM Base