December 19, 2010

SQL 2005 - Creating database in SQL Server and understanding Data storage


This article shows how do you create a SQL Server 2005 database. Here, we will use SQL server management studio to generate SQL Script that creates the database. Also it explains, how storage is allocated for the database created and explains files involved in creating the database.

Steps to Create a Database

1) Launch the SQL Server 2005 by using the sa login.
2) In the left side pane, right click the Database Node and Select New Database from the Context menu.

New Database...
Fig 1. New Database...

3) The New Database option will open a dialog for creating the database. Portion of the dialog is shown below:

Create Database Dialog
Fig 2. Database Creation Dialog

4) In the Database name field type Sample. This will fill the Data and Log file names for you in the Database files grid. But, you can override these default file names supplied by the dialog by your own database file names. The screen shot below shows typing the Sample in the database name field:

Database Name
Fig 3. Provide Database Name

In the above picture, blue box shows the files created by the dialog based on supplied database name Sample. File type data denotes the Sample.mdf file and Log denotes the Sample_Log.ldf file. MDF is the Main Database File and LDF is used for logging purpose. Do not confuse LDF file with the Transaction Log used for the Database recovery.

5) Scroll the database files grid to the left to see the path in which these files are stored. If required, you can provide a different path by clicking the ellipsis button(marked in red) shown below:

Data Files
Fig 4. Database File Names

6) If you click the OK button on the dialog, the database will be created for you. But, do not click the OK button now. Instead, Click on the Down Arrow button next to Script  and select the First option "Script Action to New Query Window" as shown below:

Script Action
Fig 5. Script Action

7) Now you can use this script to create the database. This script will take care of all the actions you did in the dialog. Also, it is useful when you deploy the database application on your client machine. Running the script through a setup program will create the database on the client machine. OK., Now, Click the Execute button and create the database. 

If the database is not displayed under the database folder, right click the database folder and select the refresh.

T-SQL for SQL Database Create
Fig 6. Generated T-SQL Script for Database Creation

How the data stored in the MDF file

The database Sample is now ready. When you create a table and insert data into it, the size of the Sample.mdf gets increased as all your information goes to this file. The data is stored in the form of something called extents. A database file has multiple extends when the data is increased. A Maximum of 8 pages constitutes a single extent. A Page is a block of storage, which has a maximum of 8 KB in size. A database row can be a maximum of 8 KB in size. This excludes the large data type columns say Text, Image, Varchar(max) etc. That means a database row excluding the large data type can fit into a single page. Also note that large data type values are stored in a separate page and location offset is stored in the page which has normal data like integer and char.  Below picture shows how the storage in physical media (Disc) is organized:

Pages and extends
Fig 7. Pages and Extends

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Friend :)