Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Programming / SQL / July 2008

Tip: Looking for answers? Try searching our database.

Estimate resultset size

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bob - 21 Jul 2008 13:50 GMT
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 ?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.