Home » SQL Server

SQL Server

SQL Server Table Size Estimator

  • by

A Developers needs to know the calculation involved in Estimating the Size of a Table. This will give inside into how to estimate database size in SQL Server.
The following steps can be useful as SQL Server Table Size estimator for a developer to estimate the amount of space required to store the data in a table:

1.Specify the number of rows present in the table:

Number of rows in the table = Num_of_Rows

2.If there are fixed-length and variable-length columns in the table definition, calculate the space that each of these groups of columns occupies within the data row. The size of a column depends on the data type and length specification.
Number of columns = Num_of_Cols
Sum of bytes in all fixed-length columns = Fixed_Col_Data_Size
Number of variable-length columns = Num_of_Variable_Cols
Maximum size of all variable-length columns = Max_Variable_Size

3.If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:

Null Bitmap (Null_Bitmap) = 2 + (( Num_of_Cols + 7) / 8 )
Only the integer portion of the above expression should be used; discard any remainder.

4.If there are variable-length columns in the table, determine how much space is used to store the columns within the row:

Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_of_Variable_Cols x 2) + Max_Variable_Size
If there are no variable-length columns, set Variable_Data_Size to 0.
This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a lower percentage of the variable-length column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall table size.

5.Calculate the row size:

Total row size (Row_Size) = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap +4
The final value of 4 represents the data row header.

6.Calculate the number of rows per page (8096 free bytes per page):

Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row.

7.If a clustered index is to be created on the table, calculate the number of reserved free rows per page, based on the fill factor specified. If no clustered index is to be created, specify Fill_Factor as 100.
Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 – Fill_Factor) / 100) / (Row_Size + 2)
The fill factor used in the calculation is an integer value rather than a percentage.
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. As the fill factor grows, more data will be stored on each page and there will be fewer pages.

8.Calculate the number of pages required to store all the rows:

Number of pages (Num_Pages) = Num_Rows / (Rows_Per_Page – Free_Rows_Per_Page)
The number of pages estimated should be rounded up to the nearest whole page.

9.Calculate the amount of space required to store the data in a table (8192 total bytes per page):

Table size (bytes) = 8192 x Num_Pages

Example for SQL Server Table Size Estimator calculations:

Lets take a table with two integer columns, two NVarchars data type columns in my table with one column nvarchar(50) and other nvarchar(30).
Assuming one thousand rows of data to be stored in the table.

Read More »SQL Server Table Size Estimator

SQL Server is a Relational Database Management System (RDBMS) that runs on platforms as diverse as tablets and smartphones up to 64-bit Clustered Servers.In all “Editions”, SQL Server runs only on Microsoft operating systems. It can also run inside a Virtual Machine (again, the guest must be running almost any modern Microsoft operating system). The database is accessed by many users who access the database through programs on their desktop or through a website application.It’s important to understand that while SQL Server must run in a Windows Operating system, the clients do not. They can run almost anything.

SQL Server is controlled by a set of Windows Services. The main service that starts the database is called MSSQLServer. There can only exist one default SQL instance in a Server.
If you see that service name followed by a $ sign and then more letters which means your SQL instance has been setup as Named Instance. An Instance is just a way of having more than one SQL Server running on a single box.

Another service you’ll see is called SQLServerAgent. This is a service that controls all of the automatic things/jobs that run on the server which is sort of an AT command or a CRON system if you’re used to those programs.

If the system has automated maintenance on it (always a good idea) then you should see this service running.

The most likely versions you’ll see of SQL Server are 2000, 2005, 2008 and 2008 R2. Expecting release of SQL Denali is few months.

Within these versions, there are different Editions. The Editions have to do with the limitations and capabilities that are available. They range anywhere from the MSDE engine (in versions through 2000) and the Express edition (2005 and higher), Personal, Standard, Workgroup (2005 and higher) and then Enterprise and Datacenter Editions.

On the security side, SQL Server can use Windows accounts to allow access to the server and then the databases and their objects (such as tables and views) or you can create new logins within SQL Server that don’t require Windows accounts.

In this Section I will show various tools available in SQL Server.

For More Articles and Posts on SQL Server Administration both Basics and Expert level by me can be found here.