What is File Structure into SQL Server?
1) Primary File
2) Secondary File
3) Transaction Log File.
1) Primary File :- In a Database We have just .mdf extension primary file. It can store two objects.
- System Define Objects.
- User Define Objects.
2) Secondary File :- This file is Optional and User Define. It doesn’t Store System Define Objects. It’s Extension is .ndf. These can be multiple. There is no limitation of size. It depends on Hard disk.
Transaction Log File :- It’s essential, Every Inserting table record entry first in log file in sequence and then into permanent table or database. SQL Server uses a write-ahead modification process that requires all changes to be
written to transaction logs before they are written to the database. Means wright ahead log. It’s basically used for backup and recovery purpose.
Data Storage Architecture of SQL SERVER.
It store data into pages. This is a smallest unit of Datasotrage into SQL Server. The size of one page 8192 bytes. We never used it fully. 96 bytes for page header. And Actual size of page is 8096 byte is used by Us.
We have different Type of Pages for data storage.
1) Data pages.
2) Global Allocation Map
3) Index Allocation Map
4) Index Pages.
5) Page Free space Pages.
6) Text/ Image Pages.
How Many Types of Database into SQLSERVER 2008 BY Default?
There are Five Types
- Master Database
- Model Database
- MSDB Database
- Tempdb Database
- Resource Database