评估插入会增长的页级空间

 1 ;WITH NonClustedIndexColumn AS
 2 (
 3     SELECT dataid,column_id,c.object_id, MAX(c.dataneme) dataneme FROM 
 4     sys.index_columns  d
 5     INNER JOIN (SELECT index_id dataid,name dataneme ,* FROM sys.indexes WHERE index_id > 1
 6                 UNION ALL 
 7                 SELECT a.index_id dataid,a.name dataneme, b.* FROM sys.indexes a
 8                 INNER JOIN sys.indexes b ON a.object_id = b.object_id AND b.index_id = 1 
 9      ) c ON c.object_id  = d.object_id  AND d.index_id = c.index_id
10      GROUP BY dataid,column_id,c.object_id
11 ),NonClustedIndexSize
12 AS(
13 SELECT --a.name,a.max_length,b.name,c.index_id
14      OBJECT_NAME(a.object_id) name ,a.object_id,MAX(c.dataneme) index_name, c.dataid
15     ,SUM(CASE WHEN b.name IN ('varchar','nvarchar') THEN 2+a.max_length ELSE 0 END) [Variable_Data]
16     ,SUM(CASE WHEN b.name not IN ('varchar','nvarchar') THEN a.max_length ELSE 0 END) [Fixed_Data]
17     ,CEILING(SUM(CASE WHEN a.is_nullable = 1 THEN 1 ELSE 0 END)*1.0 /8) [NullByte]        
18     ,CASE WHEN c.dataid <=1 THEN 2 ELSE 1 END [RowHeader]
19     ,CASE WHEN c.dataid <=1 THEN 2 ELSE 0 END [Fixed_Length]
20     ,MAX( CASE WHEN b.name IN ('varchar','nvarchar')  THEN 2 ELSE 0 END) [Variable_Length]
21     , 2 [Column_Count]
22 FROM sys.columns a
23     INNER JOIN sys.types b ON a.user_type_id = b.user_type_id
24     INNER JOIN NonClustedIndexColumn c ON a.object_id = c.object_id AND a.column_id = c.column_id
25 WHERE a.object_id >100 AND dataid > 1 -- AND OBJECT_NAME(a.object_id) = 'userorder_package'
26  GROUP BY a.object_id,c.dataid
27  )
28 , ClustedHeapSize AS(
29 SELECT --a.name,a.max_length,b.name,c.index_id
30      OBJECT_NAME(a.object_id) name ,a.object_id,MAX(c.name) index_name, c.index_id
31     ,SUM(CASE WHEN b.name IN ('varchar','nvarchar') THEN 2+a.max_length ELSE 0 END) [Variable_Data]
32     ,SUM(CASE WHEN b.name not IN ('varchar','nvarchar') THEN a.max_length ELSE 0 END) [Fixed_Data]
33     ,CEILING(SUM(CASE WHEN a.is_nullable = 1 THEN 1 ELSE 0 END)*1.0 /8) [NullByte]        
34     ,CASE WHEN c.index_id <=1 THEN 2 ELSE 1 END [RowHeader]
35     ,CASE WHEN c.index_id <=1 THEN 2 ELSE 0 END [Fixed_Length]
36     ,MAX( CASE WHEN b.name IN ('varchar','nvarchar')  THEN 2 ELSE 0 END) [Variable_Length]
37     , 2 [Column_Count]
38 FROM sys.columns a
39     INNER JOIN sys.types b ON a.user_type_id = b.user_type_id
40     INNER JOIN sys.indexes c ON c.object_id  = a.object_id  --AND d.index_id = c.index_id
41 WHERE a.object_id >100 AND c.index_id <= 1
42  GROUP BY a.object_id,c.index_id )
43 ,RowSize AS
44 (
45     SELECT name = MAX(name),OBJECT_ID,rowsize=SUM([Variable_Data]+[Fixed_Data]+[NullByte]+[RowHeader]+[Fixed_Length]+[Variable_Length]+[Column_Count])
46     FROM (
47         SELECT * FROM NonClustedIndexSize
48         UNION ALL
49         SELECT * FROM ClustedHeapSize
50     ) a
51     GROUP BY OBJECT_ID
52 )
53 
54 SELECT * FROM rowsize