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. 
It is untill when you execute your first INSERT, this is when SQL actually assign you a set of pages.
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.
- 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.
- 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.
