Table Size Estimation!!!

Someone says Table is the core object of a database, I would say its the heart of a SQL Server.

Today, I come up with one interesting observation regarding the table size estimation.
The requirement is to design a table to store Customer information for which i have to find the estimated size of the table for 1000000 records.

Table: CustomerInfo

Column Name	Datatype
ID	INT
FIRSTNAME	VARCHAR(30)
LASTNAME	VARCHAR(30)
ADDRESS	VARCHAR(50)
CITY	VARCHAR(15)
STATE	CHAR(2)
ZIP	CHAR(10)

Heres the procedure to estimate the size:

1. Find size of the row :

4+30+30+50+15+2+10 = 141 bytes

2. Divide 8096 by result above:

8096/141 = 57 (# of rows that fit on page)

3. Assuming 1000000 is the record count expected, divide the count with step 2 results:

1000000/57 = 17543.8 ~ 17544 (# of pages)

4. Multiplying # of pages with 8192

17544 * 8192 = 143720448 bytes = 137 MB

And thats how we estimate the size of the table!!!

Facts of SQL Server !!!

There are few moments where we face interesting but very rarely asked questions like " How many databases can a SQL Server Instance handle? " and like this too, " How many maximum objects can we create in a SQL Server database? "


For a guy battling all the day with strange requirements (trying to fix with strange solutions ;)) may not find time to understand the platform to the deepest. Understanding the SQL Server Inside face is like looking at the GOD in real!!!


So I want to share this information on web and thought of creating a blog for the same. We may be aware of few but not all, today I took some time to publish all this in a single post. 

• The technical limit for one SQL Server Instance is 32,767 databases. (Literally that count would never be reached).
• A SQL Server database which comprises of different types of objects like tables, views, Monster stored procedures etc., has a technical limit of (2 Power 31) - 1 (just more than 2 million objects). I never saw a database more than tens of thousands objects in all those several projects worked in various domains.
• The size, from 2MB to 524,272 Terabytes (This figure is no where close to the present business database sizes, and by the time they grow, SQL Server will grow too ;)).
• The SQL Server database, which is enough intelligent to shrink itself automatically or manually, has its own set of system tables to hold the database catalog.

If I have to talk something SQL Server database is NOT:

It is NOT synonymous with an entire SQL Server Instance. Its neither a single SQL Server table nor a specific operating system file.