If I have table, can I estimate approximately, how large x rows is going to be?
CREATE TABLE #tmp( id INT IDENTITY PRIMARY KEY, test_col1 VARCHAR(20),
test_col2 DATETIME, test_col3 FLOAT, test_col4 VARCHAR(MAX) )
GO
-- How many kb / mb will 100 rows take up ?
Roy Harvey (SQL Server MVP) - 21 Jul 2008 14:40 GMT
The simple answer: Create the table, load 100 rows of data, and run
sp_spaceused against the table.
An incomplete version of the long answer:
Without knowing the size of the VARCHAR(MAX) how could anyone guess?
The space consumed by INT (4), DATETIME (4) and FLOAT (8) datatypes
are fixed. VARCHAR(20) will take from 2 to 22 bytes, depending on the
actual value stored. The size of a VARCHAR(MAX) can vary from 2 to 2
+ (2^31-1). (If I counted on my fingers correctly that last is up to
2GB.) Plus some row overhead.
Roy Harvey
Beacon Falls, CT
>If I have table, can I estimate approximately, how large x rows is going to be?
>
[quoted text clipped - 3 lines]
>
>-- How many kb / mb will 100 rows take up ?
Eric Russell - 21 Jul 2008 17:30 GMT
The Int datatype will always be 4 bytes and the DateTime and Float datatypes
will always be 8 bytes each, even when NULL. There is no way to predict
VarChar without statistical sampling, but you can get the average lenth of a
VarChar column this way:
select avg(len(test_col1)) from TestTable
Also, just as an aside, the DMF dm_db_index_physical_stats() will return
average row width for a physical table.
SELECT
db_name(database_id) AS DatabaseName,
object_name(object_id) as ObjName,
index_type_desc,
record_count,
min_record_size_in_bytes,
max_record_size_in_bytes,
avg_record_size_in_bytes
FROM
sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'DETAILED')
phystat
order by
object_name(object_id)
> If I have table, can I estimate approximately, how large x rows is going to be?
>
[quoted text clipped - 3 lines]
>
> -- How many kb / mb will 100 rows take up ?
Uri Dimant - 22 Jul 2008 06:53 GMT
Bob
Calculate numbers of rows that can fit on an 8K data page
bytes per row=4+20+8+8+(2*1024*1024)
8060/bytes per row= rows per page
--Calculate the number of data pages needed
Number of estimated rows=100
Number of estimated rows / rows per page =data page needed
8192* data pages needed = Total bytes
Total bytes\1024= Total KB
> If I have table, can I estimate approximately, how large x rows is going
> to be?
[quoted text clipped - 4 lines]
>
> -- How many kb / mb will 100 rows take up ?