如何在SQL中的表中以GB为单位来衡量表大小 [英] How to measure table size in GB in a table in SQL

问题描述

在上一个问题 @Morawski正在说一张有 1,000列和44,000行大约为330 MB;这是浏览器只用了几个打开的选项卡。

表应该具有多少列和行的大小是> 10 GB
（假设表只有两倍价值）

@Morawski的结论是， 1,000列和44,000是330MB ？

解决方案

 测量表大小（千字节）
  - 在MS SQL Server 2008 R2中测试
 
声明@t表（
名称nvarchar（100），[rows] int，[reserved ] nvarchar（100），[data] nvarchar（100），[index_size] nvarchar（100），[unused] nvarchar（100）
）
 declare @name nvarchar（100）
 
声明tt光标为
从sys.tables中选择名称
打开tt 
 
从tt进入到@name 
，而@@ FETCH_STATUS = 0 
 begin 
 insert into @t 
 exec sp_spaceused @name 
 fetch next from tt into @name 
 end 
 
 close tt 
 deallocate tt 
 
选择nam e作为table_name，[rows] as rows_count，data + [index] as total_size，data as data_size，[index] as index_size 
 from（select name，
 [rows]，
 cast LEFT（data，LEN（data）-3）as int）data，
 cast（LEFT（index_size，LEN（index_size）-3）as int）[index] 
 from @t 
 ）x 
 order by 3 desc，1 
  

In a previous question @Morawski was saying that "a table with 1,000 columns and 44,000 rows It's about 330 MB; that's how much a browser uses for just a few open tabs".

How many columns and rows the table should have to tell its size is > 10 GB (suposing the table has only double values).

How did @Morawski concluded that 1,000 columns and 44,000 is 330MB?

Is there any script that could tell this in SQL?

解决方案

-- Measures tables size (in kilobytes)
-- Tested in MS SQL Server 2008 R2

declare @t table (
name nvarchar(100), [rows] int, [reserved] nvarchar(100), [data] nvarchar(100), [index_size] nvarchar(100), [unused] nvarchar(100)
)
declare @name nvarchar(100)

declare tt cursor for
Select name from sys.tables
open tt

fetch next from tt into @name
while @@FETCH_STATUS = 0
begin
  insert into @t
  exec sp_spaceused @name
  fetch next from tt into @name
end

close tt
deallocate tt

select name as table_name, [rows] as rows_count, data + [index] as total_size, data as data_size, [index] as index_size
from (select name,
[rows],
cast (LEFT(data, LEN(data)-3) as int) data,
cast (LEFT(index_size, LEN(index_size)-3) as int) [index]
 from @t
) x
order by 3 desc, 1

这篇关于如何在SQL中的表中以GB为单位来衡量表大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！