Pr.Pg Next Pg

File Extensions tutorials

  • Although storing all your data in memory would provide extremely fast access, you would lose everything after the machine was shut down. To protect your data, it has to be persisted to disk. Underneath each database is one or more files for persisting your data.

  • SQL Server uses two different types of files—data and transaction log files. Data files are responsible for the long-term storage of all the data within a database. Transaction log files, discussed in more detail later in this lesson, are responsible for storing all the transactions that are executed against a database.

  • SQL Server uses three file extensions: .mdf, .ndf, and .ldf.

1. A file with an .mdf extension is usually the first data file that is created within a database, generally is associated with the primary filegroup, and usually is considered the primary data file which contains all the system objects necessary to a database.

2. The .ndf extension is generally used for all other data files underneath a database, regardless of the filegroup to which the file is associated.

3. The .ldf extension generally is used for transaction logs

  • All data manipulation within SQL Server occurs in memory within a set of buffers.

  • If you are adding new data to a database, the new data is first written to a memory buffer, then written to the transaction log, and finally persisted to a data file via a background process called check pointing.

  • When you modify or delete an existing row, if the row does not already exist in memory, SQL Server first reads the data off disk before making the modification.

  • Similarly if you are reading data that has not yet been loaded into a memory buffer, SQL Server must read it out of the data files on disk.



Pr.Pg border                                              Next Pg