Term
| What system variable contains the SQL Server version and edition information? |
|
Definition
|
|
Term
| What edition of SQL Server support backup compression? |
|
Definition
| backup compression was introduced in SS 2008 Enterprise. |
|
|
Term
|
Definition
| Only accessed by db_owner role & only SELECT command. |
|
|
Term
| user access: SINGLE_USER VS. RESTRICTED_USER |
|
Definition
SINGLE_USER = one user at a time.
RESTRICTED_USER = access limited to db_owner & db_creator. Ch2.2 |
|
|
Term
| SQL to restrict database access to db_owner's & terminate all active transactions & connections in one statement. |
|
Definition
| ALTER DATABASE database_name SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE p.52 |
|
|
Term
Max number of columns a table is allowed.
512
1024
4009
8019 |
|
Definition
|
|
Term
| data types: DECIMAL VS NUMERIC |
|
Definition
| Exactly the same. Both exist for backward compatibility. |
|
|
Term
| data types: FLOAT VS NUMERIC |
|
Definition
| use the float or real data types only if the precision provided by decimal is insufficient. FLOAT takes arg mantissa, NUMBERIC takes args precision & scale. WWW |
|
|
Term
Data type: DECIMAL(4,2) supports:
A 9999.99
B 99.99
C 99.9999 |
|
Definition
B 99.99
a total of 4 digits with 2 to the right of the decimal. Ch3.1 |
|
|
Term
|
Definition
Range from 1/1/0001 - 12/31/9999
VS 1765 - 9999 p.66 |
|
|
Term
| What is the DATETIMEOFFSET data type used for? |
|
Definition
| for localizing timezones. Ch3.1 |
|
|
Term
| Precision of data type DATE |
|
Definition
|
|
Term
| What database is used to create a snapshot of a FILESTREAM column? |
|
Definition
| FILESTREAM is not accessible to a snapshot. Ch.3 |
|
|
Term
|
Definition
| A column option used with IDENTITY property. If an IDENTITY value is specified the column is reseeded. This is avoided during replication via NOT FOR REPLICATION. p.71 |
|
|
Term
|
Definition
| used to insert a specific value into an IDENTITY column without reseeding. p. 70 |
|
|
Term
| True-False: Primary Key is always clustered. |
|
Definition
| Default option is clustered. When a clustered PK is added to a compression table, compression is applied when the table is rebuilt. |
|
|
Term
| To create a foreign key which table must have a a PK on the referenced column? |
|
Definition
|
|
Term
| NULL behavior in a UNIQUE column |
|
Definition
| There can be only one row with a NULL in a UNIQUE column. |
|
|
Term
| A data page is ____ bytes in size, which can store up to ____ bytes of actual data. |
|
Definition
8192 -- {page-foot-pan}
8060 --{face-juice} |
|
|
Term
| Max number of columns allowed on an index. |
|
Definition
|
|
Term
|
Definition
|
|
Term
| Max number of indexes per table on SS2005 & SS2008 |
|
Definition
|
|
Term
|
Definition
| Created with a WHERE clause to exclude 'Smith', NULL, ect. |
|
|
Term
| SQL to create a filtered clustered index. |
|
Definition
| clustered indexes cannot be clustered nor on a computed column. |
|
|
Term
|
Definition
| When a multi-row INSERT fails because of duplicates on a UNIQUE column, instead of the entire transaction being rolled back, a warning is generated and only the duplicate rows are rejected. |
|
|
Term
|
Definition
| holes in the index when data rows are changed/deleted causing changes in the index key. |
|
|
Term
|
Definition
| percent of free space left on leaf pages when an index is created or rebuilt. |
|
|
Term
| List the 3 components of a B-Tree structure |
|
Definition
|
|
Term
| SQL to defragment an index. |
|
Definition
| ALTER INDEX IndexName ON TableName REBUILT/REORGANIZE P.105 |
|
|
Term
| Index Maintenance: REBUILD VS REORGANIZE |
|
Definition
REBUILD: rebuilds all levels according to FILLFACTOR.
REORGANIZE: rebuilds at the leaf level only. |
|
|
Term
What does this statement do?
ALTER INDEX CX_IndexName ON TableName REBUILD ALL |
|
Definition
| Rebuilds ALL indexes on TableName. |
|
|
Term
A disabled index is
A: used and maintained
B: not used nor maintained
C: used but not maintained
D: maintained but not used |
|
Definition
|
|
Term
| DROP CX_index VS DISABLE CX_index |
|
Definition
| A disabled CX_index renders the entire table inaccessible. |
|
|
Term
| Full Text Indexes: Where is the full text catalog stored? |
|
Definition
SS2005 in an OS directory
SS2008 within the database |
|
|
Term
|
Definition
FREETEXT basic search using stemming(plurals) and thesaurus. NO precision nor customization.
CONTAINS Allows for precision & exact match plus many options. |
|
|
Term
|
Definition
| A view with a clustered index on it. |
|
|
