SQL SERVER INTERVIEW QUESTION & ANSWERS
| 16. |
Explain Model Data Base? |
| |
The Model database is the template database that SQL Server uses to create new databases. Each time you create a new database in SQL Server, the contents of the Model database are copied to the new database to establish its default
objects, including tables, stored procedures, and other database objects. The Model database is required even if you do not intend to create any new user databases. Each time SQL Server starts, the TempDB is recreated using the Model database as its template. By default, the Model database is empty when it is created. |
| 17. |
What is Data File? |
| |
There are two types of data files in SQL Server: primary and secondary. When we create a new database, a single primary data file is created to store all our data. This primary data file is, logically, located in the PRIMARY filegroup, which is also automatically created and set as the default filegroup for the database. New objects that are created in the database are allocated to the PRIMARY file group by default, unless you specify otherwise. Data files can belong to only one filegroup.
In comparison to filegroups that represent logical groups of database objects, data files represent the actual physical storage files. Primary data files are stored by default with the .mdf extension; secondary data files are stored with the .mdf extension. |
| 18. |
What is Transaction Log? |
| |
Every database contains at least one data file and one log file. The log file contains the transaction log, which records information that describes the changes that are applied to data in the database. The log records contain the beginning of
each transaction, the commit or rollback state of the transaction, the data that were changed, and the information necessary to undo the transaction, such as previous column values. Additional events such as page allocations and index events are also recorded in the transaction log. This "record of changes" allows you to return the database to a specific point in time and guarantees data integrity by playing forward each change in its original sequence. Transaction logs do not contain pages like data files; rather, they store information as log records. By default, log files are physically stored with the *.ldf extension. Similarly to data files, the .ldf extension is not required, but it is recommended for easy identification of log files. Log files are configured by default to autogrow with 10 percent file growth. As with data files, these measures can be adjusted, restricting the maximum file growth to a megabyte limit.
|
| 19. |
What command do you use to access a Data Base? |
| |
USE DataBase_Name |
| 20. |
Can we store our database files on a compressed drive to save space? |
| |
No. Microsoft recommends that we do not put any of the files on a compressed drive. The compression and decompression process will impact the performance of SQL Server and can cause database integrity problems. |
