How to measure table size in GB in table in SQL

Question

In a previous question, @Morawski said that "a table with 1000 columns and 44,000 rows . It is about 330 MB that the browser uses only a few open tabs."

How many columns and rows should a table indicate in order to indicate its size of 10 GB (if the table has only double values).

How did @Morawski conclude that 1000 columns and 44 000 - 330 MB ?

Is there a script that could say this in SQL?

Answer 1

-- 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 

Answer 2

There is a sproc call to sp_spaceused. I don’t know if this was what @Morawski used, but as an example on dev db it was convenient for me:

 exec sp_spaceused 'aspnet_users' 

gives

 name rows reserved data index_size unused ------------- ------- ------------ -------- ------------ ---------- aspnet_Users 3 48 KB 8 KB 40 KB 0 KB 

Answer 3

There are exact formulas for capacity planning for SQL Server:

• Cluster Index Size Estimation
• Estimating the size of a non-clustered index
• Heap Sizing

With 1000 fixed-length columns doubled (this will be a float(53) SQL type, 8 bytes of memory), your row approaches the maximum row size limit, but it does fit on the page. For 44 thousand lines, 44 thousand pages are required (due to the huge size of the line, only one line per page will correspond), that is, 8 KB per page 44000 * 8kb = ~ 344 Mb. If the size of the clustered index increases depending on the size of the key, see the first link above.

But a 1000-column table design is a huge smell of code. Your question is very vague regarding the part of the database, your previous question never mentions the database and is in memory arrays when they are combined, these two questions simply do not make much sense.

You might be interested in reading about Sparse columns , EAV modeling, or the XML data type .

Answer 4

Not sure about the TSQL script (I'm sure it exists), but you can find it through the UI (SSMS) as follows:

1) R-click on the table
2) ... Properties
3) ... Storage tab

From there, it will tell you both “data space” and “index space” - so if you want to get the total size, just add them.

EDIT
Consider also the log space if you are looking for total table space.

Below is the information about the stored procedure given in @jon's answer. In addition, it refers to sys views, where you can directly query space usage data. http://msdn.microsoft.com/en-us/library/ms188776.aspx