How SQL store your data?

What happens when you create a table?

Have you ever give it a thought, how sql server actually managing your table? Where is your data actually got stored?

If yes, then you are equally passionate about sql server. Which means you are a competitor and we might be fighting for same job some other day. :p

So here how it actually goes.

SQL server store your data in a page. A single page is of 8kb size, 96 byte of the page is used as header to store certain information like which table it belongs too. Each row of your table is stored into these pages. Data Row will not span pages, however if your data row is too large like in case of nvarchar(max) etc. SQL server automatically moves your certain data to ROW_OVERFLOW_DATA allocation unit and keep the pointer on the original page.

So When you create a table. At that time SQL server will not assign you any pages. WHAT!!!. Yes, that’s true. It might be that you are just playing with the create statement, SQL server cannot let anyone play with its important resources. 🙂

2015-08-30_23h56_39

It is untill when you execute your first INSERT,  this is when SQL actually assign you a set of pages.

2015-08-30_23h58_20

Did I just said set of pages!

Yeaah!

Sql server will assign you an Extent. An extent is a set of 8 pages. Before proceeding let me explain about extents.

As I said, An Extent is Group of 8 Pages. Which make its total size of 64KB. SQL Server works with two type of extents.

  1. Mixed Extent : Pages present in a mixed extent can belong to different table. For ex. Page 1 can belong to Customer Table. Page 2 can belong to Employee. Page 3 Can belong to Manager. Page 4 again Customer and so on. There is not actual sequence and it will be very random.
  2. Uniform Extent : This type of Extent belongs to one and only one table.

Now the question arises, How SQL server will decide when to assign any table a Mixed Extent or Uniform Extent?

It actually depends on the amount of data. Whenever the size of your table grows upto 8 pages(or 64KB). SQL Server will assign your table its own dedicated Extent. Before that SQL Server will allocate pages from a Mixed Extent.

NOTE – A Page belongs to one and only one table. Pages are never shared among tables.

So when you execute your first insert statement, SQL Server will assign you a page from a Mixed Extent. As time proceeds and your data grows. At a certain point SQL will realise that now this table needs its own extent.

I would also like to mention here that not only your table but your indexes as well are stored in pages.

Below is the link to give you more detailed knowledge of Pages and extents.

https://msdn.microsoft.com/en-us/library/ms190969.aspx

Leave your comments. Share your knowledge. Or shoot me with your questions.

7 thoughts on “How SQL store your data?

  1. Vikas says:

    Awesome article!!! Very well structured. Got all the information in very little time :). If you could write on indexes that would be really helpful… Keep it up!!

    Like

  2. Mala says:

    Awesome explanation!!
    Curious to know ..
    where does memory get allocated for create table schema when there is no data in the table?

    Also, does querying the table from mixed/uniform extent has any effect on performance?

    Liked by 1 person

    • lokesh says:

      hmmmm.. Nice Question Mala.

      Q. where does memory get allocated for create table schema when there is no data in the table?
      A. I really do not have any idea about it. It has to be some page associated with Master Database. Reason why I am saying so is, Master DB is the one which hold the basic information of all other databases. Which include Table related information.

      Q. Does querying the table from mixed/uniform extent has any effect on performance?
      A. Naahhh!!. When your data grows up to the limit where performance can be an issue, Your table will already have its own extent.

      Like

Shoot Your Comments!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s