How Does SQL Server Store Data?

                        This is one of the important topic for database developer.We all know data is stored in table in sql server.All of us are interested to know  little more about it.How Sql Server stores data into table.I will try to explain in simple word.
                      Microsoft SQL Server databases are stored on disk in two files: a data file and a log file.
Data file named as .MDF (Master data file) and .LDF (Log data file).Next thing ,What kind of data is stored in .MDF and .LDF file.

Whats .MDF file stores :
                                     The SQL MDF file contains tables, stored procedures and user accounts. The MDF file is "attached" to the database. The MDF file is automatically created when the administrator creates a new database. The file continues to grow each time a user creates a new record in the tables. The user tables are the main storage objects in the MDF file. These objects can hold millions of records for the company.

                                      MDF files can grow to several megabytes. Databases with millions of rows can even grow into gigabytes of information. For this reason, database administrators who maintain large, enterprise servers should ensure that the hard drive has enough storage space to hold the growing MDF file. This is especially important when the hard drive houses several databases for the company. With each MDF file growing to several gigabytes, hard drive space is quickly consumed.The database administrator can use the MDF file as a backup. The MDF file is copied to an external media device such as an external hard drive, USB flash drive, CD or DVD. 

Whats .LDF file stores :                                    LDF is a file extension for a log file used with Microsoft SQL Server. LDF files contain logging information for all transactions completed by the server. LDF files are used to time stamp any transactions to the SQL Server database, allowing the SQL database to be easily recoverable in the case of data loss.

e.g  . 

           I am creating table MyMemory .Now I am going to explain how data will store internally

 Query : 

              Create table MyMemory (ID int identity(1,1),Name varchar(100))

 Insert :

        Insert into MyMemory (Name) values  ('Shrikant')

        Insert into MyMemory (Name) values  ('Sai')

        Insert into MyMemory (Name) values  ('Santosh')

        Insert into MyMemory (Name) values  ('deelip')

Now ,This data will store in table .It will look like 

      

ID     Name

1 Shrikant

2 Sai

3 Santosh

4 deelip

         We are interested to know how it will store internally.We have DBCC command which will help us to know how data is stored internally.

Data Files Are Broken Up Into 8KB Pages 

           These pages are the smallest unit of storage both in memory and on disk.  When we write the very first row into a table, SQL Server allocates an 8KB page to store that row – and maybe a few more rows, depending on the size of our data.  In our Friends example, each of our rows is small, so we can cram a bunch of ‘em onto a page.  If we had bigger rows, they might take up multiple pages even just to store one row.  For example, if you added a VARCHAR(MAX) field and stuffed it with data, it would span multiple pages.

Each page is dedicated to just one table.  If we add several different small tables, they’ll each be stored on their own pages, even if they’re really small tables.

DBCC IND('Shri_Test_17012014', 'MyMemory', -1);

 -->Database name - Shri_Test_17012014

--> Table Name - MyMemory

            Result of this command will tell us how many pages it has occupied for storing data. Above table has occupied 2 pages means 16KB of data.

 

 PagePID        ObjectID       Table Name [object_Name(Object_id)]

 611164   1280827725 MyMemory

 611163   1280827725 MyMemory

How the Data File and Log File are Accessed :

                      Data files, on the other hand, are a jumbled mess of stuff.  You’ve got tables and pages all over the place, and your users are making unpredictable changes all over the place.  SQL Server’s access for data files tends to be random, and it’s a combination of both reads and writes.  The more memory your server has, the less data file reads happen – SQL Server will cache the data pages in memory and just work off that cache rather than reading over and over.  This is why we often suggest stuffing your SQL Server with as much memory as you can afford; it’s cheaper than buying good storage.

                    Log files are written to sequentially, start to finish.  SQL Server doesn’t jump around – it just makes a little to-do list and keeps right on going.  Eventually when it reaches the end of the log file, it’ll either circle back around to the beginning and start again, or it’ll add additional space at the end and keep on writing.  Either way, though, we’re talking about sequential writes.  It’s not that we never read the log file – we do, like when we perform transaction log backups.  However, these are the exception rather than the norm.