Mcts 70-432

Front 1

What are the minimum hardware requirements for SQL Server 2008 32 bit?

Back 1

Processor: P3 or higher
Processor Speed: 1 GHz or more
Memory: 512 MB

Front 2

What are the minimum hardware requirements for SQL Server 2008 64 bit?

Back 2

Processor: Itanium, Opteron, Athelon or Xeon/Pentium with EM64t support
Processor Speed: 1.6 GHz or more
Memory: 512 MB

Front 3

The operating systems supported for all editions of SQL Server are:

Back 3

Windows Server 2008 Standard or higher
Windows Server 2003 SP2 or higher

Front 4

The operating systems supported for SQL Developer, Evaluation and Express are:

Back 4

Windows XP Professional SP2 or higher
WIndows Vista Home Basic or higher

Front 5

SQL Server Express is supported on:

Back 5

Windows XP Home Edition SP2 or higher
Windows XP Home Reduced Media Edition
Windows XP Tablet Edition SP2 or higher
Windows XP Media Center 2002 SP2 or higher
Windows XP Professional Reduced Media Edition
Windows XP Professional Embedded Edition Feature Pack 2007 SP2
Windows XP Professional Embedded Edition for Point of Service SP2
Windows Server 2003 Small Business Server Standard Edition R2 or higher

Front 6

What version of Server 2008 is SQL 2008 not supported on and why?

Back 6

SQL Server 2008 is not supported on Windows Server 2008 Server Core. Windows Server
2008 Server Core is not supported because the .NET Framework is not supported on Server
Core. SQL Server 2008 relies on .NET Framework capabilities to support FILESTREAM,
SPATIAL, and DATE data types, along with several additional features.

Front 7

What editions is SQL Server 2008 available in?

Back 7

Enterprise
Standard
Workgroup
Express
Compact
Developer
Evaluation

Front 8

How do the developer and evaluation editions differ from the enterprise edition?

Back 8

They contain the same functionality, but cannot be run in a production environment.

Front 9

Which editions of SQL Server are designed as storage engines for embedded applications with limited hardware and feature support?

Back 9

Express and Compact editions are designed as storage engines for embedded applications and support only a single CPU, up to 1 GB of RAM, and a maximum
database size of 4 GB.

Front 10

How many CPUs are supported by the Standard, Workgroup, Express and Compact editions?

Back 10

Standard: 4
Workgroup: 4
Express: 1
Comact: 1

Front 11

What database size is supported by the Standard, Workgroup, Express and Compact editions?

Back 11

Standard: Unlimited
Workgroup: Unlimited
Express: 4 GB
Compact: 4 GB

Front 12

What RAM size is supported by the Standard, Workgroup, Express and Compact editions?

Back 12

Standard: Unlimited
Workgroup: Unlimited
Express: 1 GB
Compact: 1 GB

Front 13

Which versions among Standard, Workgroup, Express and Compact allow the use of SQL Server Management Studio?

Back 13

Standard: Yes
Workgroup: Yes
Express: Separate Download
Compact: No

Front 14

Which versions among Standard, Workgroup, Express and Compact allow for full text search?

Back 14

Standard: Yes
Workgroup: Yes
Express: Advanced services only
Compact: No

Front 15

Which versions among Standard, Workgroup, Express and Compact allow for partitioning?

Back 15

Standard: No
Workgroup: No
Express: No
Compact: No

Front 16

Which versions among Standard, Workgroup, Express and Compact allow for parallel operations?

Back 16

Standard: No
Workgroup: No
Express: No
Compact: No

Front 17

Which versions among Standard, Workgroup, Express and Compact allow for multiple instances?

Back 17

Standard: No
Workgroup: No
Express: No
Compact: No

Front 18

Which versions among Standard, Workgroup, Express and Compact allow for database snapshots?

Back 18

Standard: No
Workgroup: No
Express: No
Compact: No

Front 19

Which versions among Standard, Workgroup, Express and Compact allow for scalable shared databases?

Back 19

Standard: No
Workgroup: No
Express: No
Compact: No

Front 20

Which versions among Standard, Workgroup, Express and Compact allow for index views?

Back 20

Standard: No
Workgroup: No
Express: No
Compact: No

Front 21

Which versions among Standard, Workgroup, Express and Compact allow for log compression?

Back 21

Standard: No
Workgroup: No
Express: No
Compact: No

Front 22

Which versions among Standard, Workgroup, Express and Compact allow for clustering?

Back 22

Standard: 2 nodes
Workgroup: No
Express: No
Compact: No

Front 23

Which versions among Standard, Workgroup, Express and Compact allow for database mirroring?

Back 23

Standard: single-thread
Workgroup: No
Express: No
Compact: No

Front 24

Which versions among Standard, Workgroup, Express and Compact allow for online operations?

Back 24

Standard: No
Workgroup: No
Express: No
Compact: No

Front 25

Which versions among Standard, Workgroup, Express and Compact allow for resource governor?

Back 25

Standard: No
Workgroup: No
Express: No
Compact: No

Front 26

Which versions among Standard, Workgroup, Express and Compact allow for backup compression?

Back 26

Standard: No
Workgroup: No
Express: No
Compact: No

Front 27

Which versions among Standard, Workgroup, Express and Compact allow for hot add memory/CPU?

Back 27

Standard: No
Workgroup: No
Express: No
Compact: No

Front 28

Which versions among Standard, Workgroup, Express and Compact allow for Data Encryption?

Back 28

Standard: Limited
Workgroup: Limited
Express: Limited
Compact: Password-based only

Front 29

Which versions among Standard, Workgroup, Express and Compact allow for Change Data Capture?

Back 29

Standard: No
Workgroup: No
Express: No
Compact: No

Front 30

Which versions among Standard, Workgroup, Express and Compact allow for Data Compression?

Back 30

Standard: No
Workgroup: No
Express: No
Compact: No

Front 31

Which versions among Standard, Workgroup, Express and Compact allow for Policy-based management?

Back 31

Standard: Yes
Workgroup: No
Express: No
Compact: No

Front 32

Which versions among Standard, Workgroup, Express and Compact allow for Performance Data Collection?

Back 32

Standard: Yes
Workgroup: No
Express: No
Compact: No

Front 33

Which versions among Standard, Workgroup, Express and Compact allow for CLR?

Back 33

Standard: Yes

Front 34

Which versions among Standard, Workgroup, Express and Compact allow for XML?

Back 34

Standard: Native
Workgroup: Native
Express: Native
Compact: Stored as text

Front 35

Which versions among Standard, Workgroup, Express and Compact allow for Spatial Data?

Back 35

Standard: Yes

Front 36

Which versions among Standard, Workgroup, Express and Compact allow for Stored procedures, triggers and views?

Back 36

Standard: Yes
Workgroup: Yes
Express: Yes
Compact: No

Front 37

Which versions among Standard, Workgroup, Express and Compact allow for Merge Replication?

Back 37

Standard: Yes
Workgroup: Yes
Express: Subscriber only
Compact: Subscriber only

Front 38

Which versions among Standard, Workgroup, Express and Compact allow for Transactional Replication?

Back 38

Standard: Yes
Workgroup: Subscriber only
Express: Subscriber only
Compact: No

Front 39

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact have an Import/Export Wizard?

Back 39

Standard: Yes
Workgroup: Yes
Express: NA
Compact: NA

Front 40

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact have a Package Designer?

Back 40

Standard: Yes
Workgroup: Yes
Express: NA
Compact: NA

Front 41

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact support Data Mining?

Back 41

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 42

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact support Fuzzy grouping/lookup?

Back 42

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 43

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact support term extraction/lookup?

Back 43

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 44

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact support OLAP processing?

Back 44

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 45

Regarding SSRS ,which versions among Standard, Workgroup, Express and Compact support Microsoft Office Integration?

Back 45

Standard: Yes
Workgroup: Yes
Express: Advanced Services Only
Compact: NA

Front 46

Regarding SSRS ,which versions among Standard, Workgroup, Express and Compact support report builder?

Back 46

Standard: Yes
Workgroup: Yes
Express: Advanced Services Only
Compact: NA

Front 47

Regarding SSRS ,which versions among Standard, Workgroup, Express and Compact support Scale-out reporting?

Back 47

Standard: No
Workgroup: No
Express: No
Compact: NA

Front 48

Regarding SSRS ,which versions among Standard, Workgroup, Express and Compact support Data-driven subscriptions?

Back 48

Standard: No
Workgroup: No
Express: No
Compact: NA

Front 49

Regarding SSAS-OLAP support ,which versions among Standard, Workgroup, Express and Compact have support Linked measures/dimensions?

Back 49

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 50

Regarding SSAS-OLAP support ,which versions among Standard, Workgroup, Express and Compact have support Perspectives?

Back 50

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 51

Regarding SSAS-OLAP support, which versions among Standard, Workgroup, Express and Compact have support for Partitioned cubes?

Back 51

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 52

Regarding SSAS-Data Mining support, which versions among Standard, Workgroup, Express and Compact have support for Time series?

Back 52

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 53

Regarding SSAS-Data Mining support, which versions among Standard, Workgroup, Express and Compact have support for Parallel processing and prediction?

Back 53

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 54

Regarding SSAS-Data Mining support, which versions among Standard, Workgroup, Express and Compact have support for Advanced mining algorithms?

Back 54

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 55

In addition to the core database engine technologies, SQL Server 2008 Enterprise
supports Service Broker for asynchronous processing.

Back 55

summary

Front 56

Which core components of SQL, which run as services, require dedicated service accounts?

Back 56

Database Engine and SQL Server Agent.

Front 57

What is a collation sequence?

Back 57

A collation sequence defines the character set that is supported, including case sensitivity, accent sensitivity, and kana sensitivity.

Front 58

What are the two authentication modes that you can set for SQL Server authentication?

Back 58

Windows Only (integrated security) or Windows and SQL Server (mixed mode).

Front 59

Why can you not use the Windows Service Control Applet to change service accounts or service account passwords?

Back 59

SQL Server Confi guration Manager needs to be used, because it includes the code to regenerate the service master key that is critical to the operation of SQL Server services.

Front 60

What is an enumeration request and when would you disable responses?

Back 60

By disabling enumeration responses by setting the Hide Instance to Yes, you prevent someone from using discovery techniques to locate servers running SQL Server for a possible attack.

Front 61

What is database mail?

Back 61

Database mail enables a computer running SQL to send outbound mail messages. It uses SMTP rather than MAPI.

Front 62

What are the two types of profiles that you can configure within Database Mail?

Back 62

Either a public or private profile. A public profile can be accessed by any user with the ability to send mail. A private profile can be accessed only by those users who have been granted access to the mail profile explicitly.

Front 63

How is the SQL Server Agent releated to Database Mail?

Back 63

Database Mail utilizes the services of SQL Server Agent to send messages as a background process. If SQL Server Agent is not running, messages will accumulate in a queue within the Msdb database.

Front 64

What are the two file types that SQL uses?

Back 64

Data and Transaction log files. Data files are responsible for the long-term storage of all the data within a database. Transaction log files are responsible for storing all the transactions that are executed against a database.

Front 65

When you create a database, it should have three files and two filesgroups; what are they?

Back 65

You should have a file with an .mdf extension within a filegroup named PRIMARY, a file with an .ndf extension in a filegroup with any name that you choose, and the transaction log with an .ldf extension.

Front 66

After you create a database, why should you mark the second database as the default filegroup?

Back 66

By changing the default filegroup, you ensure that any objects you create are not accidentally placed on the primary filegroup and that only the system objects for the database reside on the primary filegroup--reduces the possibility of introducing corruption.

Front 67

What is the command to change the default filegroup?

Back 67

ALTER DATABASE <database name> MODIFY FILEGROUP <filegroup name> DEFAULT

Front 68

What determines the maximum transaction throughput for any database?

Back 68

The amount of data per second that SQL Server can write to the transaction log.

Front 69

What is the FILESTREAM feature?

Back 69

FILESTREAM feature allows you to associate files with a database. The files are stored in a folder on the operating system, but are linked directly into a database where the files can be backed up, restored, full-text-indexed, and combined with other structured data.

Front 70

What is the filestream.hdr file?

Back 70

It is a system file that is created to manage the files subsequently written to the filestream folder.

Front 71

What is unique about the FILENAME property defined for a FILESTREAM filegroup?

Back 71

The initial part of the folder path definition must exist; however, the last folder in the path defined cannot exist and is created automatically

Front 72

In addition to temporary objects, what does SQL Server use the tempdb for?

Back 72

Worktables used in grouping/sorting operations, worktables to support cursors, the version store supporting snapshot isolation level, and overflow for table variables. You can also cause index build operations to use space in tempdb.

Front 73

Every database in SQL server has a recovery model property; which recovery models are available?

Back 73

Full
Bulk-logged
Simple

Front 74

In the full recovery model, what changes are logged to the transaction logs and how long are the changes retained in the transaction logs?

Back 74

All changes made, using both data manipulation language (DML) and data definition language (DDL), are logged to the transaction log. Changes are retained in the transaction log indefinitely and are removed only by executing a transaction log backup.

Front 75

What are the operations that are performed in a minimally logged manner with the database set in the Bulk-logged recovery model?

Back 75

BCP
BULK INSERT
SELECT. . .INTO
CREATE INDEX
ALTER INDEX. . .REBUILD

Front 76

How does the simple recovery model differ from the full recovery model?

Back 76

A database in the Simple recovery model logs operations to the transaction log exactly as the Full recovery model does. However, each time the database checkpoint process executes, the committed portion of the transaction log is discarded. A database in the Simple recovery model cannot be recovered to a point in time because it is not possible to issue a transaction log backup for a database in the simple recovery model.

Front 77

What is the command to set the recovery model of a database?

Back 77

ALTER DATABASE database_name SET RECOVERY {FULL | BULK_LOGGED | SIMPLE }

Front 78

What types of backup (Full, Differential, Trans log) are available for a database in the Full recovery model?

Back 78

Full: Yes
Differential: Yes
Transaction Log: Yes

Front 79

What types of backup (Full, Differential, Trans log) are available for a database in the Bulk Logged model?

Back 79

Full: Yes
Differential: Yes
Transaction Log: Yes/No minimally logged

Front 80

What types of backup (Full, Differential, Trans log) are available for a database in the Simple recovery model?

Back 80

Full: Yes
Differential: Yes
Transaction Log: No

Front 81

What is the PAGE_VERIFY CHECKSUM?

Back 81

Allows you to discover and log damaged page files (default configuration of SQL does not check for damaged pages).

Front 82

What happens when a damaged page is encountered?

Back 82

If PAGE_VERIFY CHECKSUM is not enabled, database could go offline. If enabled, an 824 error is returned to the calling application and logged to the SQL Server error log and Windows Application Event log and the ID of the damaged page is logged to the suspect_pages table in the msdb database.

Front 83

What happens if AUTO_CLOSE is enabled?

Back 83

When the last connection to a database is closed, SQL Server shuts down the database and releases all resources related to the database. When a new connection is made to the database, SQL Server starts up the database and begins allocating resources.

Front 84

What happens if AUTO_SHRINK is enabled?

Back 84

SQL Server periodically checks the space utilization of data and transaction log files. If the space checking algorithm finds a data file that has more that 25 percent free space, the file automatically shrinks to reclaim disk space.

Front 85

What happens if AUTO_CREATE_STATISTIS is enabled?

Back 85

SQL Server automatically creates statistics that are missing during the optimization phase of query processing.

Front 86

What happens if AUTO_UPDATE_STATISTIS is enabled?

Back 86

Updates out-of-date statistics during query optimization.

Front 87

What happens if AUTO_UPDATE_STATISTIS_ASYNCH is enabled?

Back 87

Controls whether statistics are updated during query optimization or if query optimization continues while the statistics are updated asynchronously.

Front 88

What is the CHANGE_TRACKING database option that SQL Server 2008 introduced? How do you specify how long to keep tracking information?

Back 88

Change tracking is a lightweight mechanism that associates a version with each row in a table that has been enabled for change tracking. Each time the row is changed, the version number is incremented. The CHANGE_RETENTION option and the AUTO_CLEANUP option control how long to keep tracking information.

Front 89

What are the three states that a database can be set to in order to control access?

Back 89

ONLINE
OFFLINE
EMERGENCY

Front 90

What access is granted to a database in an EMERGENCY state?

Back 90

Can only be accessed by members of the db_owner role and the only command allowed to be executed is SELECT.

Front 91

How can you control the ability to modify data for an online database? What impact does this have on transaction log files?

Back 91

By setting the database to READ_ONLY or READ_RIGHT. A database in READ_ONLY cannot be written to and SQL removes any transaction log file that is specified for the database. If changed to READ_WRITE, SQL recreates the transaction log file.

Front 92

What three option settings control user access to a database?

Back 92

SINGLE_USER
RESTRICTED_USER
MULTI_USER

Front 93

A database set to RESTRICTED_USER only allows access to: ?

Back 93

members of the db_owner, dbcreator and sysadmin roles.

Front 94

What happens if multiple users are using the database when you change the mode to SINGLE_USER or users conflict with the allowed set for RESTRICTED_USER? What option can you add to to control this?

Back 94

The ALTER DATABASE command is blocked until all the non-allowed users disconnect. ROLLBACK IMMEDIATE forcibly rolls back any open transactions along with disconnecting any nonallowed users. Using ROLLBACK AFTER <number of seconds> waits for the specified number of seconds before rolling back transactions and disconnecting users.

Front 95

What is the benefit of a parameterized query?

Back 95

It can reuse cached query plans more frequently and avoid the time required to build a query plan.

Front 96

How can you force SQL Server to read every page from disk and check the integrity?

Back 96

Use the DBCC CHECKDB command.

Front 97

When DBCC CHECKDB is executed, what actions are performed?

Back 97

Checks page allocation within the database
Checks the structural integrity of all tables and indexed views
Calculates a checksum for every data and index page to compare against the stored checksum
Validates the contents of every indexed view
Checks the database catalog
Validates Service Broker data within the database

Front 98

What commands does DBCC CHECKDB execute?

Back 98

DBCC CHECKALLOC, to check the page allocation of the database
DBCC CHECKCATALOG, to check the database catalog
DBCC CHECKTABLE, for each table and view in the database to check the structural integrity

Front 99

If a database is participating in a database mirroring session and a corrupt page is found using PAGE_VERIFY CHECKSUM, what happens?

Back 99

SQL Server automatically retrieves a copy of the page from the mirror, replaces the page on the principal, and logs an entry in the sys.dm_db_mirroring_auto_page_repair view.

Front 100

If your application accesses multiple databases or you are creating an application with multiple databases that do not need to be stored on separate instances for increased capacity, what should you do?

Back 100

You should combine the objects into a single database and use schemas to separate groups of objects.

Front 101

What are the nine numeric data types in SQL Server 2008?

Back 101

TINYINT
SMALLINT
INT
BIGINT
DECIMAL(P,S) and NUMERIC(P,S)
SMALLMONEY
MONEY
REAL
FLOAT(N)

Front 102

What is the range of values and how much storage space is required for the data type TINYINT?

Back 102

Values: 0 to 255
Space: 1 byte

Front 103

What is the range of values and how much storage space is required for the data type SMALLINT?

Back 103

Values: -32,768 to 32,767
Space: 2 bytes

Front 104

What is the range of values and how much storage space is required for the data type INT?

Back 104

Values: -2^31 to 2^31-1
Space: 4 bytes

Front 105

What is the range of values and how much storage space is required for the data type BIGINT?

Back 105

Values: -2^63 to 2^63-1
Space: 8 bytes

Front 106

What is the range of values and how much storage space is required for the data type DECIMAL(P,S) and NUMERIC(P,S)?

Back 106

Value: - 10^38 +1 through 10^38 - 1
Space: Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17

Front 107

What is the range of values and how much storage space is required for the data type SMALLMONEY?

Back 107

Value: - 214,748.3648 to 214,748.3647
Space: 4 bytes

Front 108

What is the range of values and how much storage space is required for the data type MONEY?

Back 108

Value: -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Space: 8 bytes

Front 109

What is the range of values and how much storage space is required for the data type REAL?

Back 109

Value: - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38
Space: 4 bytes

Front 110

What is the range of values and how much storage space is required for the data type FLOAT(N)?

Back 110

Value: - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
Space: depends on the value of n; if n's value is between 1-24, is has a precision of 7 digits and requires 4 bytes; if n is between 25-53, the precision is 15 digits and requires 8 bytes.

Front 111

What are the two parameters of decimal data types?

Back 111

Precision and scale. The precision indicates the total number of digits that can be stored both to the left and to the right of the decimal. The scale indicates the maximum number of digits to the right of the decimal point.

Front 112

What are the four character data types in SQL Server 2008? How much storage space is associated with each one?

Back 112

CHAR(n) - 1 byte per character defined by n, up to a maximum of 8,000 bytes.
VARCHAR(n) - 1 byte per character stored up to a maximum of 8,000 bytes.
NCHAR(n) - 2 bytes per character defi ned by n, up to a maximum of 4,000 bytes
NVARCHAR(n) - 2 bytes per character stored up to a maximum of 4,000 bytes

Front 113

What are the 6 time and date data types in SQL Server 2008?

Back 113

SMALLDATETIME
DATETIME
DATETIME2
DATETIMEOFFSET
DATE
TIME

Front 114

What are the range of values, the accuracy and storage space associated with SMALLDATETIME?

Back 114

Range: 01/01/1900 to 06/06/2079
Accuracy: 1 minute
Storage: 4 bytes

Front 115

What are the range of values, the accuracy and storage space associated with DATETIME?

Back 115

Range: 01/01/1753 to 12/31/9999
Accuracy: 0.00333 seconds
Storage: 8 bytes

Front 116

What are the range of values, the accuracy and storage space associated with DATETIME2?

Back 116

Range: 01/01/0001 to 12/31/9999
Accuracy: 100 nanoseconds
Storage: 6 to 8 bytes

Front 117

What are the range of values, the accuracy and storage space associated with DATETIMEOFFSET?

Back 117

Range: 01/01/0001 to 12/31/9999
Accuracy: 100 nanoseconds
Storage: 8 to 10 bytes
DATETIMEOFFSET allows you to store a time zone.

Front 118

What are the range of values, the accuracy and storage space associated with DATE?

Back 118

Range: 01/01/0001 to 12/31/9999
Accuracy: 1 day
Storage: 3 bytes

Front 119

What are the range of values, the accuracy and storage space associated with TIME?

Back 119

Range: 00:00:00.0000000 to 23:59:59.9999999
Accuracy: 100 nanoseconds
Storage: 3 to 5 bytes

Front 120

What are the three binary data types in SQL Server 2008?

Back 120

BIT
BINARY
VARBINARY

Front 121

What are the range of values and storage space associated with BIT?

Back 121

Range: Null, 0, 1
Storage: 1 bit

Front 122

What are the range of values and storage space associated with BINARY?

Back 122

Range: Fixed-length binary data
Storage: Up to 8,000 bytes

Front 123

What are the range of values and storage space associated with VARBINARY?

Back 123

Range: Variable-length binary data
Storage: Up to 8,000 bytes

Front 124

What is the XML data type?

Back 124

It allows you to store and manipulate Extensible Markup Language (XML) documents natively. You are limited to 2 GB per document as well as 128 levels within a document.

Front 125

What is an XML Schema Collection used for?

Back 125

When an XML Schema Collection is applied to an XML column, the only documents allowed to be stored within the XML column must first validate to the associated XML schema collection.

Front 126

What are the two spatial data types that SQL Server 2008 supports?

Back 126

GEOMETRY - based on Euclidian geometry and is used to store points, lines, curves, and polygons
GEOGRAPHY - based on an ellipsoid and is used to store data such as latitudes and longitudes.

Front 127

What are the 7 different geometric objects that a GEOMETRY column can contain?

Back 127

1. Point - Has x and y coordinates, with optional elevation and measure values.
2. LineString - A series of points that defi nes the start, end, and any bends in the line, with optional elevation and measure values.
3. Polygon - A surface defi ned as a sequence of points that defi nes an exterior boundary, along with zero or more interior rings. A polygon has at least three distinct points.
4. GeometryCollection - Contains one or more instances of other geometry shapes, such as a Point and a LineString.
5. MultiPolygon - Contains the coordinates for multiple Polygons.
6. MultiLineString - Contains the coordinates of multiple LineStrings.
7. MultiPoint - Contains the coordinates of multiple Points.

Front 128

What is the HIERARCHYID data type?

Back 128

The HIERARCHYID data type is used to organize hierarchical data, such as organization charts, bills of materials, and fl owcharts. The HIERARCHYID stores a position within a tree hierarchy.

Front 129

What are the 7 properties that you can apply to a column?

Back 129

1. nullability
2. COLLATE
3. IDENTITY
4. ROWGUIDCOL
5. FILESTREAM
6. NOT FOR REPLICATION
7. SPARSE

Front 130

What database property sets the nullability property?

Back 130

ANSI_NULL_DEFAULT

Front 131

How can you insert a value into an identity column explicitly?

Back 131

By using the SET IDENTITY_INSERT <table name> ON command.

Front 132

How do you change the next value generated for an identity column by modifying the seed?

Back 132

Use the DBCC CHECKIDENT command.

Front 133

What is the ROWGUIDCOL property used for?

Back 133

Used mainly by merge replication to designate a column that is used to identify rows uniquely across databases. The ROWGUIDCOL property is used to ensure that only a single column of this type exists and that the column has a UNIQUEIDENTIFIER data type.

Front 134

What data type do columns that have the FILESTREAM property applied have?

Back 134

VARBINARY(MAX). The column maintains a 16 byte identifier for the file.

Front 135

Can you implement database mirroring against a database containing data stored with FILESTREAM?

Back 135

NO

Front 136

How is a FILEGROUP designated for FILESTREAM storage treated in a database snapshot?

Back 136

It is off-line and inaccessible.

Front 137

What is the SPARSE option used for?

Back 137

When a NULL is stored in a column designated as SPARSE, no storage space is consumed. However, non-NULL values require 4 bytes of storage space in addition to the normal space consumed by the data type. The column must allow nulls.

Front 138

What can you not apply the SPARSE property to?

Back 138

- Columns with the ROWGUIDCOL or IDENTITY property
- TEXT, NTEXT, IMAGE, TIMESTAMP, GEOMETRY, GEOGRAPHY, or user-defined data types
- A VARBINARY(MAX) with the FILESTREAM property
- A computed column of a column with a rule or default bound to it
- Columns that are part of either a clustered index or a primary key
- A column within an ALTER TABLE statement

Front 139

What is the maximum size a row can be before you cannot issue an ALTER statement to either change a column to SPARSE or add an additional SPARSE column? Why?

Back 139

4,009 bytes. During the ALTER, each row is recomputed by writing a second copy of the row on the same data page. Because two copies of a row that exceed 4,009 bytes would exceed the 8,018 bytes allowed per page, the ALTER TABLE statement fails.

Front 140

How can you make a column SPARSE if the row size is more than 4,009 bytes?

Back 140

- Reduce the data within a row so that the maximum row size is greater than 4,009 bytes
- Create a new table, copy all the data to the new table, drop the old table, and then rename the newly created table
- Export the data, truncate the existing table, make the changes, and import the data back into the table

Front 141

If you do not want to incur the overhead of making the calculation of a Computed Column at runtime, what can you specify?

Back 141

The PERSISTED property. If a computed column is PERSISTED, SQL Server stores the result of the calculation in the row and updates the value anytime data that the calculation relies upon is changed.

Front 142

What is row-level compression? What is the limitation?

Back 142

Allows you to compress individual rows to fit more rows on a page, which reduces the storage space for the table because you don't need to store as many pages on a disk. Because you can uncompress the data at any time, in order for it to succeed, you cannot use compression to store more than 8,060 bytes in a single row.

Front 143

What commands do you use to have SQL Server compress new data added to a heap?

Back 143

BULK INSERT or INSERT INTO...WITH (TABLLOCK) statement

Front 144

Do compression settings for a table pass to any nonclustered indexes or indexed views craeted against a table?

Back 144

NO

Front 145

Why can you not compress VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data?

Back 145

They store data in specialized structures outside the row; any data stored outside the row cannot be compressed.

Front 146

How many columns can a standard table in SQL 2008 have?

Back 146

1,024 columns

Front 147

What are wide tables?

Back 147

Tables that exceed 1,024 columns (up to a max of 30,000columns) by using a column set definition; data stored in any row still cannot exceed 8,019 bytes unless you have a VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX) column defined for the table.

Front 148

Where are temporary tables stored?

Back 148

The tempdb database.

Front 149

What are the two types of temporary tables and how are they designated?

Back 149

Local or global. Local is designated by a name beginning with # and is visible only within the connection that created it. A global is designated by a name beginning with ## and is visible across all connections to the instance.

Front 150

What is a table variable?

Back 150

A table variable is a memory resident structure that is visible only within the connection that declared the variable and is deallocated after the code which declared the variable completes.

Front 151

What is a clustered primary key?

Back 151

A clustered primary key, the default SQL Server behavior, causes SQL Server to store the table in sorted order according to the primary key.

Front 152

When a clustered primary key is created on a table that is compressed, when is compression applied to the primary key?

Back 152

When the table is rebuilt.

Front 153

How is a unique constraint similar to a primary key?

Back 153

Neither allows duplicates, though a unique constraint allows NULLs. A unique constraint treats NULLs as it does any other data value; if a unique constraint is defined on a single column, then a single row within the table is allowed to have a NULL within the column.

Front 154

What is a default constraint?

Back 154

A default constraint allows you to specify a value that is written to the column if the application does not supply a value. SQL SERVER WRITES THE DEFAULT VALUE TO A COLUMN ONLY IF THE APPLICATION DOES NOT SPECIFY THE COLUMN IN THE INSERT STATEMENT.

Front 155

What is a check constraint?

Back 155

A check constraint limits the range of values within a column.

Front 156

How do check constraints at a column level differ from check constraints at a table level?

Back 156

Column-level check constraints cannot reference any other column in the table; table-level check constraints can reference any column within a table, but not other tables.

Front 157

What structure does SQL Server use to build and maintain indexes?

Back 157

A balanced tree (B-tree). A B-tree is constructed of a root node that contains a single page of data, one or more optional intermediate level pages, and one or more optional leaf level pages.

Front 158

How many bytes of data are in a SQL Server data page? How many can be used for actual data?

Back 158

A data page is 8,192 bytes, 8,060 bytes can be used to store data.

Front 159

If you are creating an index on a sparse column, what type of index should you use?

Back 159

You should use a filtered index.

Front 160

What is the maximum number of columns that can be used to define an index?

Back 160

16

Front 161

What is the maximum size of the index key?

Back 161

900 bytes

Front 162

What is a clustering key?

Back 162

The column(s) defined for a clustered index.

Front 163

What is special about a clustered index?

Back 163

It causes SQL to arrange the data in the table according to the clustering key.

Front 164

How many clustered indexes can you have for a table?

Back 164

Only one, since a table can only be sorted one way.

Front 165

What is a heap?

Back 165

A table without a clustered index.

Front 166

What does the leaf node of a clustered index correspond to; how does this compare to the leaf node of a heap?

Back 166

In a clustered index, the leaf node is the actual data; in a heap, it is a forward pointer to the location of the row in the table's data pages.

Front 167

When is the FILESTREAM_ON clause used?

Back 167

When clustered indexes are crated on a table containing FILESTREAM data.

Front 168

If you specify a different filegroup in the FILESTREAM_ON clause than where the FILESTREAM data is currently located, what happens?

Back 168

All the FILESTREAM data will be moved to the newly specified filegroup during the creation of the clustered index.

Front 169

What restrictions doe nonclustered indexes have?

Back 169

Max size of 900 bytes in the index key, a maximum of 16 columns and a table is limited to 1,000 nonclustered indexes.

Front 170

Where does the pointer at the leaf level of a nonclustered index point if a clustered index exists on the table; if one does not?

Back 170

If a clustered index exists on the table, the leaf level of the nonclustered index points to the clustering key; if a clustered index does not exist, it points to the row of data in the table.

Front 171

What is a covering index?

Back 171

An index that is constructed such that SQL Server can completely satisfy queries by reading only the index.

Front 172

What is the benefit of using the INCLUDE clause with indexes?

Back 172

Included columns become part of the index only at the leaf level; values do not appear in root or intermediate levels and do not count against the 900 byte or 16 column restrictions.

Front 173

What does the query optimizer do?

Back 173

It is a component that decides whether or not to use an index to satisfy a query based on the distribution statistics that are stored for the index.

Front 174

What is meant by the selectivity of an index?

Back 174

The degree to which values in a column allow you to locate small sets of data; as the number of unique values within a column increases, so does the selectivity of an index. The query optimizer chooses the most selective indexes to satisfy queries.

Front 175

What is a filtered index?

Back 175

An index with a WHERE clause. Only the index keys matching the WHERE clause are added to the index, allowing you to build indexes that focus on the highly selective portions of the table.

Front 176

What restrictions do filtered indexes have?

Back 176

The must be a nonclustered index.
They cannot be created on computed columns.
Columns cannot undergo implicit or explicit data type conversion.

Front 177

What is FILLFACTOR used for?

Back 177

It is used to control the rate at which page splits occur; it specifies the percentage of free space that should be left on the leaf level of an index during creation or rebuild.

Front 178

What is the PAD_INDEX option used for?

Back 178

It causes the FILLFACTOR to be applied to the intermediate-level page(s) and the root page of an index.

Front 179

What is the SORT_IN_TEMPDB option used for?

Back 179

Normally, during index creation, the work tables SQL uses for sorting the values prior to building the B-tree are created in the same database as the index. The use of this option causes them to be created in the tempdb database.

Front 180

What is the IGNORE_DUP_KEY option used for?

Back 180

In indexes designated as unique, multi-row inserts with a duplicate in one row would cause the entire transaction to roll back; this option rejects only the row with a duplicate.

Front 181

Index can be created either online or offline, how is this done and what does it mean?

Back 181

It is done using WITH ONLINE = ON | OFF. When off, SQL locks the entire table, preventing any changes until the index is created. When on, SQL allows changes to the table during creation of hte index by using the version store within the tempdb database.

Front 182

How to clustered and nonclustered index builds differ when done off-line.

Back 182

When you build a clustered index offline, the table is locked and does not allow select statements or data modifications. If you build a nonclustered index offline, a shared table lock is acquired, which allows select statements but not data modification.

Front 183

What versions of SQL Server 2008 support online index creation, rebuild or online restore?

Back 183

Only SQL server 2008 enterprise.

Front 184

What is a primary XML index?

Back 184

A primary XML index is built against all the nodes within the XML column. The primary XML index is also tied to the table by maintaining a link to the corresponding row in the clustered index. Therefore, a clustered index is required before you can create a primary XML index.

Front 185

What is a secondary XML index?

Back 185

Secondary indexes can be created on PATH, VALUE, or PROPERTY. A primary XML index is first required, because secondary XML indexes are built against the data contained within the primary XML index.

Front 186

What is a spatial index?

Back 186

It is an index created against a spatial column that it types as either geometry or geography.

Front 187

What is the decomposition process by which spatial data is converted from a 2D system to a linear structure?

Back 187

Tessellation.

Front 188

What is the BOUNDING_BOX parameter?

Back 188

The BOUNDING_BOX parameter defines the maximum and minimum x, y coordinates that are considered when constructing the grid hierarchy and tessellating the rows of geometry data.

Front 189

What are the three types of XML indexes that you can create?

Back 189

PATH, VALUE and PROPERTY.

Front 190

How do you defragment an index?

Back 190

You use the ALTER INDEX statement.

Front 191

What are the two options you can use when you defragment an index?

Back 191

You can either REBUILD or REORGANIZE.

Front 192

What does the REBUILD option in an ALTER INDEX statement do?

Back 192

The REBUILD option rebuilds all levels of the index and leaves all pages filled according to the FILLFACTOR setting of an index. If you rebuild the clustered index, only the clustered index is rebuilt. However, rebuilding the clustered index with the ALL keyword also rebuilds all nonclustered indexes on the table. The rebuild of an index effectively re-creates the entire B-tree structure, so unless you specify the ONLINE option, a shared table lock is acquired, preventing any changes until the rebuild operation completes.

Front 193

What does the REORGANIZE option in an ALTER INDEX statement do?

Back 193

The REORGANIZE option removes fragmentation only at the leaf level. Intermediate-level pages and the root page are not defragmented during a reorganize. REORGANIZE is always an online pperation that does not incur any long-term blocking.

Front 194

How do you disable an index?

Back 194

ALTER INDEX { index_name | ALL }
ON <object>
DISABLE [ ; ]

Front 195

What happens when an index is disabled?

Back 195

When an index is disabled, the defi nition remains in the system catalog but is no longer used. SQL Server does not maintain the index as data in the table changes, and the index cannot be used to satisfy queries. If a clustered index is disabled, the entire table becomes inaccessible.

Front 196

How do you re-enable an index that was disabled?

Back 196

It must be rebuilt using the REBUILD option:
ALTER INDEX { index_name | ALL }
ON <object>
REBUILD [ ; ]

Front 197

In SQL Server 2008, where are the contents of a full text catalog stored?

Back 197

In the database; unlike previous versions, where it was stored in a directory on the operating system.

Front 198

What does the ACCENT_SENSITIVITY option provide?

Back 198

It allows you to configure whether the full text engine considers accent marks when building or querying a full text index.

Front 199

What do you need to do if you change the ACCENT_SENSITIVITY option on a full text catalog?

Back 199

You need to rebuild the full text indexes with the catalog.

Front 200

What columns can be used to create a full text index?

Back 200

CHAR
VARCHAR
XML
VARBINARY

Front 201

What are word breakers?

Back 201

Assemblies that locate breaks between words within the data being indexed for a full text index.

Front 202

What are stemmers?

Back 202

Stemmers conjugate verbs so that your queries can locate information even across multiple verb tenses.

Front 203

What are stop words?

Back 203

In the creation of a full text index, stop words are common words that you would not normally search upon (e.g. the, a, an).

Front 204

How many full text indexes can a table or index view have?

Back 204

Only one, though it can be created on multiple columns.

Front 205

What is the CHANGE_TRACKING option for a full text index used for?

Back 205

It determines how SQL maintains the index when underlying data changes are made. The options are AUTO, OFF or MANUAL.

Front 206

Before you can create a full text index, what structure do you need to specify?

Back 206

A full text catalog.

Front 207

What do you need to specify to create a full text index on documents stored within a FILESTREAM?

Back 207

SQL Server stores the documents within a VARBINARY(MAX) column with the FILESTREAM property enabled. In order to create a full text index, you need to also specify a type column that designates what type of document is stored in the VARBINARY(MAX) column to load the appropriate fi lter for the word breaker to use.

Front 208

What columns can you create a full text index on?

Back 208

CHAR/VARCHAR, XML, and VARBINARY.

Front 209

What are the four commands that SQL Server uses to query full text?

Back 209

CONTAINS
FREETEXT
CONTAINSTABLE
FREETEXTTABLE

Front 210

What is the difference between CONTAINS and FREETEXT and CONTAINSTABLE and FREETEXTTABLE?

Back 210

CONTAINS and FREETEXT return a True/False value used to restrict a result set, and CONTAINSTABLE and FREETEXTTABLE return a result set that can be used to extend query functionality.

Front 211

Are all search terms used with full text Unicode strings or non-Unicode strings?

Back 211

Unicode?

Front 212

What happens if you pass a non-Unicode string to a full-text query?

Back 212

If you pass in a non-Unicode string, the query still works, but it is much less effi cient because the optimizer cannot use parameter sniffi ng to evaluate distribution statistics on the full text index.

Front 213

What features does the CONTAINS query allow you to search on?

Back 213

Search word forms
Search for word proximity
Provide relative weighting to terms

Front 214

How do CONTAINS AND FREETEXT differ in fuzzy searching?

Back 214

It is the default in FREETEXT, while you have to specify that you want it in CONTAINS.

Front 215

What are the two options you can use with FORMSOF?

Back 215

INFLECTIONAL, which causes the full text search engine to consider word stems (e.g. searching for driven also searches for drove, drive, driving).
THESAURUS which produces synonyms for the search terms.

Front 216

What type of file are Thesaurus files and where are they located?

Back 216

XML; the FTDATA directory underneath your default SQL Server installation path.

Front 217

What does ISABOUT perform?

Back 217

ISABOUT performs proximity searches and it also applies weighting if the WEIGHT keyword and weighting value are supplied.

Front 218

When is the thesaurus file used?

Back 218

After it is defined, FREETEXT and FREETEXTTABLE use it automatically; CONTAINS and CONTAINTSTABLE use it if specified with the FORMSOF THESAURUS option.

Front 219

What are the two set types that a thesaurus file contain?

Back 219

A thesaurus can contain expansion sets or replacement sets. A replacement set defines a term or terms that are replaced within the search argument prior to the word breaker tokenizing the argument list. An expansion set defi nes a set of terms that are used to expand upon a search argument. When an expansion set is used, a match on any term within the expansion set causes SQL Server to retrieve the row.

Front 220

What setting specifies whether the thesaurus is accent sensitive?

Back 220

the diacritics setting.

Front 221

Where are Stop lists contained in SQL 2008, how does this differ from SQL 2005?

Back 221

In 2008, they are stored in the database. In 2005, they were stored in the FTDATA directory.

Front 222

What are the options for populating a Full text index?

Back 222

- FULL Reprocesses every row from the underlying data to rebuild the full text index completely
- INCREMENTAL Processes only the rows that have changed since the last population; requires a timestamp column on the table
- UPDATE Processes any changes since the last time the index was updated; requires that the CHANGE_TRACKING option is enabled for the index and set to MANUAL

Front 223

How do you reload the English thesaurus file?

Back 223

EXEC sys.sp_fulltext_load_thesaurus_file 1033

Front 224

How do you manage thesaurus files?

Back 224

You edit the language specific file that is contained in the FTDATA directory for the instance.

Front 225

What commands do you use to build a list of stop words for a full text index?

Back 225

CREATE FULLTEXT STOPLIST and ALTER FULLTEXT STOPLIST

Front 226

What is the process for partitioning a table, index or indexed view?

Back 226

1. Create a partition function.
2. Create a partition scheme mapped to a partition function.
3. Create the table, index or indexed view on the partition scheme.

Front 227

What data types cannot be used with partition functions?

Back 227

You cannot use text, ntext, image, xml, varbinary(max), varchar(max), or any CLR data types.

Front 228

What is the maximum number of partitions allowed for a table?

Back 228

The maximum number of partitions for a table is 1,000.

Front 229

What is the maximum number of boundary points allowed for a partition function?

Back 229

The maximum number of boundary points for a partition function is 999.

Front 230

What is the AS clause used for in a partition function?

Back 230

The AS clause allows you to specify whether the partition function you are creating is RANGE LEFT or RANGE RIGHT.

Front 231

What is the FOR VALUES clause used for in a partition function?

Back 231

It is used to specify the boundary points for the partition function. If the partition function is created as RANGE LEFT, then the boundary point is included in the left partition. If the partition function is created as RANGE RIGHT, then the boundary point is included in the right partition.

Front 232

Where are NULL values stored in a partition?

Back 232

They are always stored in the leftmost partition until you explicitly specify null as a boundary point and use the RANGE RIGHT syntax, in which case nulls are stored in the rightmost partition.

Front 233

How do you partition an existing object after it has been populated with data?

Back 233

To partition an existing table, you need to drop the clustered index and re-create the clustered index on the partition scheme. To partition an existing index or indexed view, drop the index and re-create the index on a partition scheme. You will want to be very careful when partitioning existing objects that already contain data, because implementing the partition will cause a signifi cant amount of disk input/output (I/O).

Front 234

What happens if more filegroups are included in the partition scheme than there are partitions?

Back 234

Any excess filegroups will not be used to store data unless explicitly specified by using the ALTER PARTITION SCHEME command.

Front 235

If you specify the ALL keyword when creating a partition scheme, what is the maximum number of filegroups you can specify?

Back 235

1

Front 236

Can you create a new fi legroup at the same time that you are creating a partition scheme?

Back 236

No. Any filegroups that you specify in the CREATE PARTITION SCHEME statement must already exist in the database.

Front 237

How do you create a partitioned table or indexed view?

Back 237

Instead of using the ON clause to specify a filegroup, you specify a partition scheme.

Front 238

What property must be set to use a computed column as a partitioning key?

Back 238

The computed column must be persisted.

Front 239

Which operators are used to add or remove boundary points from a partition function?

Back 239

The SPLIT operator is used to introduce a new boundary point. The MERGE operator is used to remove a boundary point.

Front 240

Which operator is used to move partitions between tables?

Back 240

The SWITCH operator is used to move partitions between tables.

Front 241

What are the two purposes of the NEXT USED clause?

Back 241

1. It adds a new filegroup to the partition scheme, if the specifi ed fi legroup is not already part of the partition scheme.
2. It marks the NEXT USED property for a filegroup (The filegroup that is marked with the NEXT USED flag is the filegroup that contains the next partition that is created when a SPLIT operation is executed.).

Front 242

What is aligned storage?

Back 242

When a table and all its indexes use the same partition function and the same partition scheme. This ensures that if a single partition is backed up or restored, the data and corresponding indexes are kept together in a single unit.

Front 243

What requirements does the SWITCH operator have?

Back 243

-- The data and index for the source and target tables must be aligned.
-- Source and target tables must have the same structure.
-- Data cannot be moved from one filegroup to another.
-- Two partitions with data cannot be exchanged.
-- The target partition must be empty.
-- The source or target table cannot participatie in replication.
-- The source or target tables cannot have full text indexes or a FILESTREAM data type defined.

Front 244

If you are exporting data using BCP, the account that BCP is running under needs what permissions?

Back 244

Only SELECT

Front 245

If you are importing data using BCP, the account that BCP is running under needs what permissions?

Back 245

SELECT, INSERT and ALTER TABLE

Front 246

In BCP, what does the first argument specify?

Back 246

The table or query that BCP operates upon.

Front 247

In BCP, what does the second argument specify?

Back 247

Can be set to in, out or queryout. When in, BCP imports the contents of the file specified. When out, BCP exports the entire contents of the table into the file specified.

Front 248

In BCP, what does the third argument specify?

Back 248

The file that is the source or target of the BCP command.

Front 249

How do you use queryout with BCP?

Back 249

You replace the name of the table with a query delimited by double quotes.

Front 250

What do the -n and -c switches specify in BCP?

Back 250

They are mutually exclusive. -n specifies that the data in the files is in the native format of SQL Server. The-c switch specifies that the data is a character format.

Front 251

In BCP, what does the -S switch specify?

Back 251

The instance name to connect to.

Front 252

In BCP, what does the -T switch specify?

Back 252

designates a trusted connection and BCP uses the windows credentials of the account that is executing the BCP command to connect.

Front 253

In BCP, what does the -U switch specify?

Back 253

login name

Front 254

In BCP, what does the -P switch specify?

Back 254

password

Front 255

In BCP, what does the -h switch specify?

Back 255

enforces check constraints and fire triggers during the import

Front 256

How does the BULK INSERT command differ from BCP?

Back 256

BULK INSERT cannot export data and is a T-SQL command, thus does not require the instance name or login credentials.

Front 257

The Import and Export wizard is based on which feature of SQL?

Back 257

SSIS

Front 258

What three data types can the Import and Export Wizard not work with?

Back 258

hierarchy, geography and geometry

Front 259

What permission is required by BCP to use the -h option other than SELECT and INSERT?

Back 259

ALTER TABLE

Front 260

Where are all the objects for Policy Based Management stored?

Back 260

Within the msdb database.

Front 261

How can you get a list of the facets available?

Back 261

By querying the dbo.syspolicy_management_facets table.

Front 262

What are the five objects that are used within Policy Based Management?

Back 262

Facets, conditions, policies, policy targets, and policy categories.

Front 263

What are the allowed execution modes for a policy?

Back 263

The policy execution modes are On demand, On schedule, On change, Log only, and On change, prevent.

Front 264

Which object has a property that allows you to mandate checking for all databases on an instance?

Back 264

Policy categories allow you to mandate checking of all databases within an instance.

Front 265

How many facets can be checked within a single condition?

Back 265

A condition can be defi ned on only one facet.

Front 266

How many conditions can be checked within a single policy?

Back 266

A policy can check only a single condition.

Front 267

What is a facet?

Back 267

Facets define the type of object or option to be checked, such as database, Surface Area and login.

Front 268

If you define a condition using the advanced editor, can a policy that incorporates the condition be scheduled?

Back 268

No, it must be executed manually.

Front 269

What are the four execution modes that a policy can be set for?

Back 269

-- On demand Evaluates the policy when directly executed by a user
-- On change, prevent Creates data defi nition language (DDL) triggers to prevent a change that violates the policy
-- On change, log only Checks the policy automatically when a change is made using the event notification infrastructure
-- On schedule Creates a SQL Server Agent job to check the policy on a defined schedule

Front 270

What effect does the policy category mandate property have when applied to a databse?

Back 270

When a policy category is set to Mandate and a sysadmin subscribes the instance to a policy category, all databases that meet the target set are controlled by the policies within the policy category. A policy subscription to a policy category set to Mandate cannot be overridden by a database owner.

Front 271

What is the mandate property setting for the default category?

Back 271

mandate enabled.

Front 272

What security context do all backup execute under?

Back 272

Under the SQL Server service account.

Front 273

What databases can a sysadmin backup?

Back 273

All the databases in an instance.

Front 274

What databases can a db_owner backup?

Back 274

Only their databases.

Front 275

What database role can you add a user to in order to allow them to back up a database while preventing any other access to the database?

Back 275

db_backupoperator

Front 276

What are the four types of backups you can perform in SQL Server 2008?

Back 276

Full
Differential
Transaction Log
Filegroup

Front 277

What does a Full Backup contain?

Back 277

All pages within a database that contain data; pages that do no contain data are no included in the backup.

Front 278

What is the limiting factor for the speed of a backup?

Back 278

The performance of the device where the backup is being written.

Front 279

What are the backup procedure steps that SQL Server performs?

Back 279

1. Locks the database, blocking all transactions
2. Places a mark in the transaction log
3. Releases the database lock
4. Extracts all pages in the data files and writes them to the backup device
5. Locks the database, blocking all transactions
6. Places a mark in the transaction log
7. Releases the database lock
8. Extracts the portion of the log between the marks and appends it to the backup

Front 280

What are the only operations not allowed during a backup?

Back 280

adding or removing a database file
shrinking a database

Front 281

What are the only two parameters required for a backup?

Back 281

The name of the database and the backup device.

Front 282

What happens when you specify more than one backup device? What is the max number you can have?

Back 282

SQL Server stripes the backup across each of the devices specified. The max is 64.

Front 283

What effect does the MIRROR TO clause have on a backup?

Back 283

When you include the MIRROR TO clause, SQL Server retrieves the page once from the database and writes a copy of the page to each backup mirror.

Front 284

What requirements do mirrored backups have?

Back 284

-- All backup devices must be of the same media type.
-- Each mirror must have the same number of backup devices.
-- WITH FORMAT must be specified in the backup command.

Front 285

Why is a compressed backup always worth the processing overhead?

Back 285

The amount of time saved far exceeds the overhead associated with the compression operation.

Front 286

What do the INIT/NOINIT options of a BACKUP command control?

Back 286

Whether an existing backup file is overwritten or appended to.
NOINIT = append
INIT = overwrite

Front 287

What does the CHECHSUM option of a BACKUP command control?

Back 287

When CHECKSUM is specified, SQL Server verifies the page checksum, it if exists, before writing the page to the backup. Also, a checksum is calculated for the entire backup that can be used to determine if the backup has been corrupted.

Front 288

What is the default behavior for errors encountered during a backup?

Back 288

STOP_ON_ERROR - if an invalid page checksum is encountered during backup, the backup terminates with an error.

Front 289

How do you continue to backup past an invalid checksum?

Back 289

Use the CONTINUE_PAST_ERROR option.

Front 290

What is Log Sequence Number (LSN)?

Back 290

A unique number assigned to each row of a transaction log. It begins with one and increments to infinity, never repeating.

Front 291

What does the active portion of the transaction log contain? The inactive?

Back 291

The active portion contains all changes that have not yet been committed. The inactive portion contains all the changes that have been committed to the database.

Front 292

What happens if an LSN gap is introduced into the transaction log backup?

Back 292

You must create a full backup before you can start backing up the transaction log.

Front 293

What does a differential backup capture?

Back 293

All extents that have changed since the last full backup.

Front 294

How are the changes since the last full backup tracked?

Back 294

By using a special page in the header of a database called the Differential Change Map (DCM). A full backup zeroes out the contents of the DCM.

Front 295

When an extent is changed, how does the change appear in the DCM?

Back 295

The bit corresponding to the extent is set to 1.

Front 296

What does the COPY_ONLY option in a backup allow?

Back 296

It allows you to create a backup but does not affect the database state or set of backups in production. e.g. a full backup with COPY_ONLY set does not zero out the DCM and a transaction log backup with COPY_ONLY does not remove transactions from the transaction log.

Front 297

What is a Filegroup backup?

Back 297

It allows you to target a portion of the database to be backed up (reducing time and the footprint of the backup).

Front 298

What is a partial backup?

Back 298

Partial backups back up the primary filegroup, all read/write filegroups and any explicitly specified read-only filegroups. In this way, you eliminate backing up read-only filegroups, which would not change.

Front 299

How do you perform a partial backup?

Back 299

By specifying the READ_WRITE_FILEGROUPS option:
BACKUP DATABASE database_name READ_WRITE_FILEGROUPS [,<file_filegroup_list>] TO <backup_device>

Front 300

What command to you execute to have SQL Server detect and quarantine corrupted pages?

Back 300

ALTER DATABASE <dbname> SET PAGE_VERIFY CHECKSUM

Front 301

Is there a limit to the number of corrupt pages in a database?

Back 301

Yes, SQL puts a limit of 1000 corrupt pages on a database. Once you reach the limit, the database is taken offline and places it in a suspect state to protect it from further damage.

Front 302

What are maintenance plans and what tasks are supported?

Back 302

They provide a mechanism to graphically create job workflows that support common administrative functions. The tasks supported are:
-- Backing up of databases and transaction logs
-- shrinking databases
-- re-indexing
-- updating of statistics
-- performing consistency checks

Front 303

What are maintenance plans based on?

Back 303

SSIS

Front 304

What is a service master key?

Back 304

A service master key is created automatically the first time than an instance is started. It is regenerated each time that you change the SQL Server service account or password.

Front 305

How do you backup the service master key?

Back 305

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'

Front 306

What are database master keys (DMKs)?

Back 306

DMKs are created prior to the creation of a certificate, symmetric key or asymmetric key. A DMK is the root of the encryption hierarchy in a database.

Front 307

How do you backup a DMK?

Back 307

BACKUP MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'

Front 308

What must you do before you backup the DMK?

Back 308

The DMK must be open:
USE <database name>;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<SpecifyStrongPasswordHere>';

Front 309

By default, what is the DMK encrypted with?

Back 309

The service master key.

Front 310

How do you backup just the public key of a certificate?

Back 310

BACKUP CERTIFICATE certname TO FILE = 'path_to_file'

Front 311

How do you backup a certificate (both the private and public keys)?

Back 311

BACKUP CERTIFICATE certname TO FILE = 'path_to_file'
[ WITH PRIVATE KEY
(FILE = 'path_to_private_key_file' ,
ENCRYPTION BY PASSWORD = 'encryption_password'
[ , DECRYPTION BY PASSWORD = 'decryption_password' ] ) ]

Front 312

How do you validate a backup?

Back 312

RESTORE VERIFYONLY FROM <backup device>

Front 313

What checks does SQL perform when a backup is validated?

Back 313

-- Calculates a checksum for the backup and compares to the checksum stored in the backup file
-- Verifies that the header of the backup is correctly written and valid
-- Transits the page chain to ensure that all pages are contained in the database and can be located

Front 314

What does the NO_TRUNCATE option allow?

Back 314

It allows you to backup the transaction log without writing to the master data file.

Front 315

Where is the LSN of the last modification stored at the database level, the data file level and the data page?

Back 315

At database level, the LSN of the last modification to the db is stored in the header of the master data file. At data file level, the LSN of the last change to a page within the file is stored in the header of the data file. At data page level, the LSN of the last change to the data page is stored.

Front 316

What is a dirty page?

Back 316

A modified page in the buffer pool. Every dirty page tracks the LSN in the transaction log that corresponds to the change that modified the page in the buffer pool.

Front 317

What happens to dirty pages when SQL server executes a checkpoint?

Back 317

All dirty pages in the buffer pool are written out to the data files.

Front 318

What happens during the checkpoint process?

Back 318

During the checkpoint process, SQL Server compares the LSN of the dirty page in the buffer pool to the LSN of the data page on disk. If the LSN of the data page on disk is equal to or less than the LSN of the dirty page in the buffer pool as well as equal to or less than the LSN for the data fi le, the page on disk is overwritten with the page from the buffer pool. If the LSN of the dirty page is greater than the page on disk or the data fi le containing the page, the page in the buffer pool is overwritten by the page on disk. When the checkpoint process fi nishes writing dirty pages to the data fi les, the largest LSN written to each fi le is written into the header of the fi le. In addition, the largest LSN written across the entire checkpoint process is written to the header of the master data fi le. SQL Server ensures that the LSN for every page within a fi le is equal to or less than the LSN for the fi le and that the LSN for every file within a database is equal to or less than the LSN for the database. The fi nal step in the process is to clear the fl ag on each dirty page affected by the checkpoint that designates the page has changed.

Front 319

What are the two phases of restart recover. When does restart recovery take place?

Back 319

UNDO and REDO. It takes place when SQL server is started.

Front 320

What happens during REDO phase of restart recovery?

Back 320

All committed transactions in the transaction log are flushed to disk.

Front 321

What happens during the UNDO phase of restart recovery?

Back 321

During UNDO, which occurs after REDO, the transaction log is examined and any transactions that are still open are invalidated, ensuring that they cannot be written to disk.

Front 322

What is rolling forward?

Back 322

After UNDO phase completes, the database is rolled forward: SQL Server reads the last LSN recorded in the transaction log, increments the LSN and writes the new LSN into the header of every data file within the database, ensuring that transactions older than the roll-forward point cannot be written to the data files.

Front 323

What do all restore sequences begin with?

Back 323

Either a full backup or filegroup backup.

Front 324

After the database or filegroup being restored has been brought back online, can you apply additional differential or transaction log backups to the database?

Back 324

No.

Front 325

During a RESTORE command, what happens if the database does not already exist within the instance?

Back 325

It is created.

Front 326

What does the REPLACE option during a RESTORE command control.

Back 326

Forces the restore over the top of an existing database.

Front 327

Why does a restore take about 30% longer than the backup?

Back 327

SQL is optimized to backup and can pull pages into the backup regardless of order. During a restore, the pages must be placed back into the database in sequential order.

Front 328

What do the first pages within a database store?

Back 328

The structural information about the db, such as the list of pages allocated to the db.

Front 329

During a RESTORE command, what does the RECOVERY option provide?

Back 329

After the RESTORE operation completes, the db is online and accessible.

Front 330

During a RESTORE command, what does the NORECOVERY option provide?

Back 330

The RESTORE operation completes, but the database is left in a RECOVERYING state, such that subsequent differential and/or transaction log backups can be applied.

Front 331

During a RESTORE command, what does the STANDBY option provide?

Back 331

Allows you to issue SELECT statements against the database while still issuing additional differential and/or transaction log restores. If you restore a db with the STANDBY option, an additional file is created to make the db consistent as of the last restore that was applied.

Front 332

During a RESTORE command, what does the MOVE option provide?

Back 332

It allows you to change the location of db files during the restore.

Front 333

What is the first step in any restore operation?

Back 333

You should issue a transaction log backup against the original db. This is referred to as backing up the tail of the log.

Front 334

What is the syntax for restoring a full or differential backup?

Back 334

RESTORE DATABASE { database_name | @database_name_var }
[ FROM <backup_device> [ ,...n ] ]
[ WITH {[ RECOVERY | NORECOVERY |
STANDBY = {standby_file_name | @standby_file_name_var } ]
| , <general_WITH_options> [ ,...n ]
| , <replication_WITH_option>
| , <change_data_capture_WITH_option>
| , <service_broker_WITH options>
| , <point_in_time_WITH_options—RESTORE_DATABASE>
} [ ,...n ]
]
<general_WITH_options> [ ,...n ]::=
--Restore Operation Options
MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
[ ,...n ] | REPLACE | RESTART | RESTRICTED_USER

Front 335

What is the syntax for restoring a transaction log backup?

Back 335

RESTORE LOG { database_name | @database_name_var }
[ <file_or_filegroup_or_pages> [ ,...n ] ]
[ FROM <backup_device> [ ,...n ] ]
[ WITH {[ RECOVERY | NORECOVERY |
STANDBY = {standby_file_name | @standby_file_name_var } ]
| , <general_WITH_options> [ ,...n ]
| , <replication_WITH_option>
| , <point_in_time_WITH_options—RESTORE_LOG> } [ ,...n ] ]
<point_in_time_WITH_options—RESTORE_LOG>::=
| { STOPAT = { 'datetime' | @datetime_var }
| STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime' ]
| STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime' ]

Front 336

During a transaction log restore, what does the STOPAT command allow?

Back 336

Allows you to specify a date and time to which SQL Server restores.

Front 337

During a transaction log restore, what do the STOPATMARK and STOPBEFOREMARK commands allow?

Back 337

Allow you to specify either an LSN or a transaction log MARK to use for the stopping point in a restore operation.

Front 338

How does the primary filegroup state affect the state of the db.

Back 338

The state of a db equals the state of the primary filegroup.

Front 339

To take advantage of the online restore capabilities of SQL Server 2008 Enterprise, what type of restores can you do?

Back 339

Either a file or filegroup restore, which does not include the primary filegroup. A full restore affects the entire database, which includes the primary filegroup (which sets the state of the db).

Front 340

What do you do if page corruption occurs in an index?

Back 340

Drop and recreate the index.

Front 341

What are the requirements for a page restore?

Back 341

-- The database must be in either the Full or Bulked-logged recovery model.
-- You must be able to create a transaction log backup.
-- A page restore can apply only to a read/write filegroup.
-- You must have a valid full, file, or filegroup backup available.
-- The page restore cannot be executed at the same time as any other restore operation.

Front 342

What is the syntax to restore a page?

Back 342

RESTORE DATABASE database_name
PAGE = 'file:page [ ,...n ]' [ ,...n ]
FROM <backup_device> [ ,...n ]
WITH NORECOVERY

Front 343

What are the steps to restore a page to a database?

Back 343

1. Retrieve the PageID of the damaged page.
2. Using the most recent full, file, or filegroup backup, execute the following command:
RESTORE DATABASE database_name
PAGE = 'file:page [ ,...n ]' [ ,...n ]
FROM <backup_device> [ ,...n ]
WITH NORECOVERY
3. Restore any differential backups with the NORECOVERY option.
4. Restore any additional transaction log backups with the NORECOVERY option.
5. Create a transaction log backup.
6. Restore the transaction log backup from step #5 using the WITH RECOVERY option.

Front 344

What is a best effort restore?

Back 344

If the backup media is damaged, SQL server will continue past the source of damage and continue restoring as much of the database as possible. After the restore, the db is set to EMERGENCY mode.

Front 345

What do you need to specify to restore from backup media that has been damaged?

Back 345

CONTINUE_AFTER_ERROR for a RESTORE DATABASE or RESTORE LOG command.

Front 346

What versions of SQL is database snapshot available?

Back 346

Only SQL Server 2008 Enterprise

Front 347

What happens if you create a database snapshot against a database with FILESTREAM data?

Back 347

The FILESTREAM filegroup is disabled and not accessible.

Front 348

What is the syntax to create a database snapshot?

Back 348

CREATE DATABASE database_snapshot_name
ON
(NAME = logical_file_name,
FILENAME = 'os_file_name') [ ,...n ]
AS SNAPSHOT OF source_database_name

Front 349

What are the requirements to create a database snapshot?

Back 349

-- You must include an entry for each data file specified in the source database
--the logical name of each file must match the name in the source database exactly

Front 350

What restrictions exist for a database snapshot?

Back 350

-- You can’t back up, restore, or detach a Database Snapshot.
-- The Database Snapshot must exist on the same instance as the source database.
-- Full text indexes are not supported.
-- FILESTREAM is not supported, and any FILESTREAM data is inaccessible through the Database Snapshot.
-- You can’t create a Database Snapshot against a system database.
-- You can’t drop, restore, or detach a source database that has a Database Snapshot created against it.
-- You can’t reference fi legroups that are off-line, defunct, or restoring.

Front 351

How much space on disk does SQL Server allocate for a database snapshot?

Back 351

SQL Server takes advantage of sparse files--essentially an entry in the file allocation table and consumes almost no space on disk. As data is added to the file, the file automatically grows on disk.

Front 352

How does a database snapshot act from an application perspective.

Back 352

It looks and acts like a read-only database to any queries.

Front 353

How much data does a database snapshot contain at the time of creation?

Back 353

None.

Front 354

How many pages are in an extent?

Back 354

8 pages; SQL Server reads and writes extents.

Front 355

What is the copy-on-write mechanism?

Back 355

The means by which SQL Server manages changes that occur within the source database of a database that has a database snapshot. The first time a modification is made to a data page within an extent occurs in the source db, SQL Server copies the before image of the extent to the Database snapshot.

Front 356

What is the max size of a database snapshot?

Back 356

The amount of data that existed in the source database at the time of creation.

Front 357

What is a database revert?

Back 357

A special category of restoring data when you have a database snapshot. If you need to revert only a row or portion of a db, you can use and INSERT, UPDATE, DELETE or MERGE statement. SQL Server also allows you to revert the entire db using the snapshot, if necessary.

Front 358

What is the syntax to revert a db from a database snapshot?

Back 358

RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>

Front 359

What restrictions are there on reverting a source database?

Back 359

-- Only a single Database Snapshot can exist for the source database.
-- Full-text catalogs on the source database must be dropped and then re-created after the revert completes.
-- Because the transaction log is rebuilt, the transaction log chain is broken.
-- Both the source database and Database Snapshot are off-line during the revert process.
-- The source database cannot be enabled for FILESTREAM.

Front 360

What two features are incompatible with Database snapshots?

Back 360

FILESTREAM and full text indexes.

Front 361

Prior to reverting a database using a Database snapshot, what must you do?

Back 361

You must drop all database snapshots except the database snapshot being used as the source for the RESTORE command.

Front 362

What is a job step?

Back 362

The execution elements within a job.

Front 363

What type of job steps can be executed?

Front 364

What is the default security context for a job step?

Back 364

It corresponds to the login that is set as the owner of the job. You can also override the security context by specifying a proxy account that the SQL Server agent uses for the job step based on credentials assigned to the proxy account.

Front 365

What options can a job step be configured with?

Front 366

What control flow options are available for a job?

Back 366

Quit job reporting success
Quit job reporting failure
Go to next step
Go to a specific step number

Front 367

What are the three logging options that are available to a job?

Back 367

Log to a file that is overwritten each time, append to a file or log to a table (not recommended due to overhead).

Front 368

In addition to any logging configured for a job step, what log information is gathered?

Back 368

SQL Server logs information into the dbo.sysjobhistory table in the msdb database for each job step that is executed within the job. Some of the information recorded is:
-- job step
-- status
-- execution date and time
-- duration
--if an error occurs, the number, severity, and text of the last error message generated

Front 369

If a job fails, where can you look to diagnose the problem?

Back 369

The first place to look is in the job history, which can be accessed from SQL Server Management Studio (SSMS) by right-clicking a job and selecting View History. You can also look in the logging files that are configured for each job step. In some cases, you might find additional information in the Microsoft Windows event logs.

Front 370

What is an Operator for a job?

Back 370

An alias for a person, group or device. Operators are used to send notifications when jobs fail or an alert is generated. For each operator, you specify a name along with contact information.

Front 371

What are the three types of SQL Server Agent alerts?

Back 371

-- A SQL Server event
-- A performance condition alert
-- A windows management instrumentation (WMI) event

Front 372

What are the two response actions that can be configured for an alert?

Back 372

You can have an alert send a notification or execute a job in response to the alert condition.