Term
| T or F, Data in a database is stored in one disk file |
|
Definition
| False, it's stored in one or more disk files |
|
|
Term
| T of F, Every transaction to the database is written to a transaction log file BEFORE being written to the data files |
|
Definition
|
|
Term
| Why must data be persisted to disk? |
|
Definition
| Data stored in memory is volatile |
|
|
Term
| SQL Server's 3 types of files |
|
Definition
| primary data files, secondary data files, and log files |
|
|
Term
|
Definition
• The starting point of the database
• Points to other files in the database
• Every DB has one primary data file
• Recommended file name extension is .mdf |
|
|
Term
|
Definition
• Responsible for long-term data storage
• Make up all the data files other than the primary data file
• A DB may or may not have secondary data files
• Recommended file name extension is .ndf |
|
|
Term
| what is the recommended file extension for Primary Data files? |
|
Definition
|
|
Term
| what is the recommended file extension for Secondary Data files? |
|
Definition
|
|
Term
| what is the recommended file extension for Log files? |
|
Definition
|
|
Term
|
Definition
• Store the transactions that are executed against the database
• Log the information used to recover the database
• Must be at least one log file for each DB
• Recommended file name extension is .ldf |
|
|
Term
|
Definition
used to refer to the physical file in all
Transact-SQL (T-SQL) statements |
|
|
Term
|
Definition
name of the physical file including the
directory path; must follow OS file naming rules |
|
|
Term
| Should you use FAT or NTFS for data and log files |
|
Definition
| NTFS is recommended because of security aspects |
|
|
Term
|
Definition
| pages in a data file are numbered sequentially, starting with 0 for the first page in the file |
|
|
Term
|
Definition
| Files can grow automatically from the originally specified size |
|
|
Term
| T or F, If there are multiple files in a filegroup, they will not autogrow UNTIL all the files are full |
|
Definition
|
|
Term
|
Definition
a level of abstraction for more flexibility in
managing files |
|
|
Term
| What are the two types of filegroups |
|
Definition
primary and user-defined
(secondary) |
|
|
Term
|
Definition
| contains primary data file and any other file not specifically assigned to another filegroup |
|
|
Term
| What filegroup are all system tables allocated to? |
|
Definition
|
|
Term
| User-defined (secondary) filegroups: |
|
Definition
Specified using the FILEGROUP keyword in a CREATE
DATABASE or ALTER DATABASE statement |
|
|
Term
| T or F, Log files are part of a filegroup |
|
Definition
| False, Log files are never part of a filegroup. Log space is managed separately from data space |
|
|
Term
| Can a file be a member of more than one filegroup? |
|
Definition
| Nope. No file can be a member of more than one filegroup |
|
|
Term
| Where will tables and indexes that don't specify a filegroup be stored? |
|
Definition
|
|
Term
|
Definition
| specifies which filegroup should store the object |
|
|
Term
| What fill algorithm does SQL Server use for filegroups? |
|
Definition
it uses a proportional fill algorithm
• Designed to ensure that all files within a filegroup reach maximum capacity at the same time
• Resize operations occur at the filegroup level – all files within a
filegroup expand at the same time |
|
|
