|
| |
| |
|
|
|
Da Notes -
70-029 - F
Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender. |
Passing Score: 693, Questions: 49, Time: 150 Min.
|
Da Notes Index
|
|
|
Filegroups
|
Tables, nonclustered indexes , text & image data can be on specified
filegroups.
Multiple files in one filegroup for the same type does NOT improve performance.
Logs on a separate device is the best performance boost.
Allows you to break up a large database into smaller chunks so backup is separated.
Contains a collection of files.
PRIMARY is the default filegroup. Use commands Sp_helpfile and sp_helpfilegroup
|
Fill factor
|
see Index fill factor
|
FOREIGN KEY
|
Only a one-to-one or many-to-one relationship. NO one-to-many!!!!
Must reference a primary key or a unique key.
|
FORWARD-ONLY
|
Must fetch data rows in a serial pattern from the result set. FETCHNEXT is the only operation allowed. Can modify data.
|
Fragmentation
|
Internally occurs when page density is low. Page density refers to how full, or dense, a page is. Lower page density equates to more I/O's when performing a SELECT statement.
In SQL 7.0, a page is 8K.
The maximum amount of data which can be contained is a single row is 8060 bytes, not including text, ntext and image data. Let's say that you have a SQL 7.0 table with a row size of 4,040 bytes. Only one row will fit on a page in this scenario. However, if you were able to reduce the row size to 4,030 bytes, then two rows will fit on one page. This would result in half the number of I/O's per SELECT statement, making a much more efficient table design. Internal fragmentation can be evaluated by looking at the "Avg. Page Density (full)" line on DBCC SHOWCONTIG output. As a general rule, it should be greater than 90%.
Externally occurs when extents are not contiguous. Space is allocated to tables and indexes in extents. An extent is 8 pages. So, in SQL 7.0, an extent is 64K. When extents are out of order on the disk, this will result in less than optimal data access. It's basically the same philosophy as disk
fragmentation. External fragmentation can be evaluated by looking at the "Scan Density [Best Count:Actual Count]line on DBCC SHOWCONTIG output. This value should be 100.00%
OUTPUT parameters must be assigned to a local variable and the keyword OUTPUT must be used.
|
FULL TEXT SEARCH
|
see MICROSOFT SEARCH SERVICE
|
|
|
|
|
