BANGALORE, INDIA: Providing data driven solutions does not end with writing queries. Rather, writing queries is only the beginning. For you to provide solutions to your customers, you would need to understand how your database server provides you with solutions.
Microsoft SQL Server arranges data in your database in files, and the 'page' is the fundamental unit of data storage for SQL Server. Understanding the architecture of pages is important for designing and developing data driven solutions that perform effectively. Here, we describe the architecture as it applies to Microsoft SQL Server 2008 (Note that the page architecture has not changed significantly since version 7.0).
Every database stores its data at the operating system level in the form of one or more data files, which by convention have an extension of .mdf or .ndf. For example, this is what I see when I right click on one of my databases in SQL Server Management Studio, after clicking on Properties, and then selecting the Files tab.
Every database has a name and an ID, and so has every file. Run these statements to find out for yourself.
SELECT 'The name of this database is: ' + DB_NAME();
SELECT 'The id of the database ' + DB_NAME() + ' is: ' + STR(DB_ID());
SELECT 'Here are the files that make the database: ';
SELECT * FROM sys.database_files;
The disk space allocated to a data file in a database is logically divided into pages. In SQL Server, the page size is 8 KB. Within each file the pages are numbered contiguously from 0 to x. The value of x would depend on the size of the file. You can refer to any page by specifying the database ID, the file ID and the page number.
 |
| Click on Properties and then select the Files tab. Right click on one of the databases in SQL Server Management Studio to get all relevant details. |
The space in a database is used for storing tables and indexes. Data rows of a table or index are put on the page serially. Every page begins with a page header. The 96-byte header is used to store system information about the page. This information includes the page number, the amount of free space on the page, and some other details.
 |
| You can see that every database has a name and an ID, and so has every file. Plus, you can see all relevant details. |
