T-Sql Set

Front 1

Which system stored procedure changes the configuration settings for Database Mail, such as limiting the size of e-mail attachments or configuring the logging level?

Back 1

The sysmail_configure_sp system stored procedure

Front 2

Which database object, when enabled, can execute Transact-SQL code automatically when rows in the table are inserted, updated, or deleted?

Back 2

A DML trigger

Front 3

With Change Tracking, which column of the change-tracked table is captured along with the change information?

Back 3

The primary key of the change-tracked table

Front 4

Which clause must you include in a CREATE PROCEDURE statement to create a CLR stored procedure?

Back 4

The EXTERNAL NAME clause

Front 5

When must columns specified in an ORDER BY clause be included in the SELECT list of the query?

Back 5

When the DISTINCT keyword, a GROUP BY clause, or the UNION operator is used

Front 6

What is the result of setting IMPLICIT_TRANSACTIONS to ON?

Back 6

Transactions are implicitly created when specific SQL statements are encountered and no transaction is active, but must be explicitly committed or rolled back.

Front 7

What is the result of including an empty set in a grouping set?

Back 7

Grand totals are returned.

Front 8

Which join allows you to join a table to itself by specifying the table name twice in the FROM clause with different table aliases?

Back 8

A self join

Front 9

Which type of trigger is executed in place of the statement that fires it, but before any table constraints are checked?

Back 9

An INSTEAD OF trigger

Front 10

Which SQL Server monitoring tool can be used to capture SQL Server activity in a trace?

Back 10

SQL Server Profiler

Front 11

When including an OUTPUT clause in a DML statement, in which three ways can the information about the affected rows be used?

Back 11

It can be displayed to the user, inserted into a permanent or temporary table, or passed to a nested DML statement for processing.

Front 12

Which system stored procedure is used to update an existing Database Mail account?

Back 12

The sysmail_update_account_sp system stored procedure

Front 13

For an UPDATE statement that includes an OUTPUT clause, which column value is returned by a column prefixed with DELETED?

Back 13

The column value that existed before the DML operation

Front 14

What are the three types of secondary XML indexes?

Back 14

Path, Value, and Property

Front 15

Which system stored procedure returns details about a previously created Database Mail profile?

Back 15

The sysmail_help_profile_sp system stored procedure

Front 16

Which function should you use in the FROM clause to provide a rowset view over an XML document?

Back 16

The OPENXML function

Front 17

Which type of join is performed when the JOIN keyword is used without the LEFT, RIGHT, FULL, or CROSS keyword?

Back 17

An inner join

Front 18

What is the result of specifying the COLLATE database_default clause for a column when creating a table?

Back 18

The default collation for the database in which the object is created is used.

Front 19

When multiple columns are specified in the ORDER BY clause, how is the result set sorted?

Back 19

First by the column specified first in the ORDER BY clause, then by the second specified column, and so on

Front 20

When performing inserts, updates, deletes, and merges, which clause can you include to return information about the rows affected by the DML operation?

Back 20

The OUTPUT clause

Front 21

Which clause is used in a procedure or function definition to specify the security context under which the procedure or function will execute?

Back 21

The EXECUTE AS clause

Front 22

When defining a Common Table Expression (CTE), which clause specifies the expression name that will be used in the subsequent statement?

Back 22

The WITH clause

Front 23

Which clause can you include with a GROUP BY clause to group rows in the result set based on values in the GROUP BY clause, return one summary row for each group, and return a grand total summary row?

Back 23

The WITH ROLLUP clause

Front 24

Which constraint type identifies a column or set of columns that uniquely identify any given row of data in a table, and cannot contain a NULL value?

Back 24

A PRIMARY KEY constraint

Front 25

Which data type should you use to store rows of data from a result set so they can be processed later?

Back 25

The table data type

Front 26

At which level must a CHECK constraint be defined to be able to reference more than one column in the constraint expression?

Back 26

At the table level

Front 27

Which logical operator returns a TRUE value if the value of a test expression is equal to the value of one of the values in a specified list?

Back 27

The IN operator

Front 28

With Change Tracking, which column can you specify in a query to identify the type of DML statement that generated the change?

Back 28

The SYS_CHANGE_OPERATION column

Front 29

Which clause of the CREATE VIEW statement ensures that the statement creating the view is not stored in the database in plain text and readable by others?

Back 29

The WITH ENCRYPTION clause

Front 30

What is the result if you issue a DELETE statement that does not include a WHERE clause?

Back 30

All the rows in the table are deleted.

Front 31

Which clause should you use in a SELECT query if you only want to retrieve the first 15 rows from the result set?

Back 31

The TOP 15 clause

Front 32

Which clause of the MERGE statement specifies the action to take when the records from the source table are in the target table?

Back 32

The WHEN MATCHED THEN clause

Front 33

If you create a database and specify a database collation that is different from the server collation, for which objects created in the database will this collation be used?

Back 33

All permanent objects for which you have not explicitly overridden the collation at the column level

Front 34

What is the result of specifying the WITH STATUS=OFF clause with the CREATE QUEUE statement?

Back 34

The queue will not be able to receive messages.

Front 35

Which join should be used to return the rows that exist in both tables along with the rows that exist only in the table to the right of the JOIN keyword?

Back 35

A right outer join

Front 36

Which Transact-SQL statement uses two tables and can insert, update, or delete rows in one of the tables based on the other table's rows?

Back 36

The MERGE statement

Front 37

After using Database Engine Tuning Advisor to make recommendations, what is the result of selecting Save Recommendations from the Actions menu?

Back 37

A Transact-SQL script that could be used to implement the recommendations is created.

Front 38

For an UPDATE statement that includes an OUTPUT clause, which column value is returned by a column prefixed with INSERTED?

Back 38

The column value that exists after the DML operation is complete, but before any table triggers are executed

Front 39

Which three parameters must be specified with the DATEDIFF function?

Back 39

The starting date, the ending date, and the portion of the date to use to calculate the difference

Front 40

To use the UNION operator to combine the result sets of several SELECT statements, what must be true of the columns in the result sets of the individual SELECT statements?

Back 40

Each result set must have the same number of columns in the same order, and the data types of the corresponding columns must be compatible.

Front 41

To what does the term dirty reads refer?

Back 41

When statements read rows that have been modified by other transactions but have not yet been committed

Front 42

Which option can be used with the FOR XML clause to include a root element with a specific name?

Back 42

The ROOT option

Front 43

Which ranking function assigns a numerical ranking to rows, but does not skip rankings if two rows have an identical value in the ranking column?

Back 43

The DENSE_RANK function

Front 44

Which value is an IDENTITY column assigned by default when a row is inserted?

Back 44

The next identity value

Front 45

What is returned by the sp_helptext system stored procedure for a stored procedure created with the WITH ENCRYPTION clause?

Back 45

An error message indicating the stored procedure is encrypted

Front 46

Which SET option should you include in an ALTER DATABASE statement to enable change tracking?

Back 46

The SET CHANGE_TRACKING=ON option

Front 47

Which type of join creates a Cartesian product of two tables, joining each row in one table with all possible rows from the other table?

Back 47

A cross join

Front 48

Which clause should you include in a SELECT query with a GROUP BY clause to restrict the rows returned by using an expression after the grouping?

Back 48

The HAVING clause

Front 49

Which database object would you create to accept input parameters and execute Transact-SQL code without returning a value?

Back 49

A stored procedure

Front 50

Which constraint type can ensure that the non-NULL values in a given column will match values in column of another table?

Back 50

A FOREIGN KEY constraint

Front 51

What can each grouping set specified in a GROUPING SETS clause contain?

Back 51

One or more columns or an empty set

Front 52

Which clause should you include with the OUTPUT clause of a DML statement to store information about rows affected by the statement in another permanent or temporary table?

Back 52

The INTO clause

Front 53

When a user references a view in a query, how is the data from the underlying tables or views retrieved?

Back 53

Using the SELECT statement that defines the view

Front 54

What syntax is used to create a computed column in a table?

Back 54

The column_name AS expression syntax

Front 55

Which clause of the CREATE PARTITION FUNCTION statement specifies the boundary values of each partition?

Back 55

The FOR VALUES clause

Front 56

Which Transact-SQL function returns an integer representing a portion of a specified date, such as the month, day, or year?

Back 56

The DATEPART function

Front 57

On which columns can an XML index be created?

Back 57

On a single xml data type column in a database table

Front 58

Which query hint specifies an integer value that identifies the number of processors on which a single query may run, and can be used to override the max degree of parallelism configuration option for a single query?

Back 58

The MAXDOP query hint

Front 59

When creating a stored procedure, which clause specifies that the stored procedure executes under the security context of the current user?

Back 59

The EXECUTE AS SELF clause

Front 60

Which keyword should be used with the FOR XML clause to retrieve nested XML results that use table names as elements and column names as attributes?

Back 60

The AUTO keyword

Front 61

When specified with the UNION operator, which keyword specifies that every row of the individual SELECT statement result sets, including duplicate rows, should appear in the final result set?

Back 61

The ALL keyword

Front 62

Which statement ends the current transaction by discarding all pending data changes?

Back 62

The ROLLBACK TRANSACTION statement

Front 63

Which clause should you use in a SELECT query if you only want to retrieve the first 10 percent of rows from the result set?

Back 63

The TOP 10 PERCENT clause

Front 64

Which type of triggers fire when database schema changes are made?

Back 64

DDL triggers

Front 65

Which tool can you use to identify missing clustered indexes that might improve the performance of your Transact-SQL queries?

Back 65

The Database Engine Tuning Advisor

Front 66

What is the result of issuing a TRUNCATE TABLE statement?

Back 66

All rows are removed from the table, but the table's definition is retained.

Front 67

Which statement allows you to move a partition from one partitioned table to another partitioned table?

Back 67

The ALTER TABLE...SWITCH statement

Front 68

Which catalog view returns a row for each SQL Server trace event, including the numeric identifier of the trace, the identifier for the trace's category, and the trace's name?

Back 68

The sys.trace_events catalog view

Front 69

When a Common Table Expression (CTE) definition references itself, which query hint can you include with the query following the CTE definition to limit the levels of recursion for the CTE?

Back 69

The MAXRECURSION query hint

Front 70

Which database's collation is used by default if you create a temporary table and do not override the collation at the table level?

Back 70

The collation defined in the tempdb database

Front 71

Which columns in a table can the CONTAINS and FREETEXT predicates reference?

Back 71

Either a single column enabled for full-text search or all of the columns that are enabled for full-text search

Front 72

Which type of index should you create on a column when you want to perform keyword searches on the column?

Back 72

A full-text index

Front 73

Which catalog view returns a row for each current trace along with its status, but not the trace's details?

Back 73

The sys.traces catalog view

Front 74

Which data type can be used to store data of different data types, such as int, binary, and char, within a single column?

Back 74

The sql_variant data type

Front 75

Which clause should you include in an ALTER PARTITION FUNCTION statement to merge two partitions?

Back 75

The MERGE RANGE (boundary_value) clause

Front 76

Which Transact-SQL function returns a specific number of characters from a specified character string, beginning at a specified position?

Back 76

The SUBSTRING function

Front 77

Which clause of the ALTER TABLE statement should you use to change the data type or size of an existing column?

Back 77

The ALTER COLUMN clause

Front 78

Which two conditions must be true to catch an execution error using a TRY...CATCH construct?

Back 78

The execution error must not terminate the database connection and must have a severity level greater than 10.

Front 79

Which system stored procedure accepts parameters defining the details of an e-mail message, such as the recipients, message subject, message body, and importance, and sends the e-mail message using Database Mail?

Back 79

The sp_send_dbmail system stored procedure

Front 80

Which clause of the CREATE PARTITION SCHEME statement identifies the partition function?

Back 80

The AS PARTITION clause

Front 81

When creating a secondary XML index, which clause of the CREATE XML INDEX statement specifies the name of the primary XML index defined for the column?

Back 81

The USING XML INDEX clause

Front 82

To round a specified column, expression, or value to the nearest whole number, which value should you pass as the second argument to the ROUND function?

Back 82

A value of 0

Front 83

When using the OPENXML function, which flag parameter value indicates that element-specific mapping should be performed instead of attribute-specific mapping?

Back 83

A flag parameter value of 2

Front 84

When creating a temporary table, which collation will be used by default?

Back 84

The collation defined in the tempdb database, which after each server restart is the same as the server's collation

Front 85

Which data type is used to store XML data, such as XML documents or XML document fragments?

Back 85

The xml data type

Front 86

Which clause of the SELECT statement allows you to insert the result set returned into a new table?

Back 86

The INTO clause

Front 87

When enabling change tracking using the ALTER DATABASE statement, what does an AUTO_CLEANUP value of ON indicate?

Back 87

That change tracking information will be automatically deleted from the database after the retention period specified by CHANGE_RETENTION

Front 88

Which type of parameters allow you to pass multiple rows of data into a stored procedure or user-defined function?

Back 88

Table-valued parameters

Front 89

Which two transaction isolation levels prevent phantom reads and dirty reads?

Back 89

The SNAPSHOT and SERIALIZABLE transaction isolation levels

Front 90

Which operator evaluates two queries and returns a single result set containing all rows returned from the first query that are not also returned by the second query?

Back 90

The EXCEPT operator

Front 91

Is Database Mail enabled or disabled by default?

Back 91

Disabled

Front 92

Which constraint type specifies that a given row cannot contain NULL values?

Back 92

A NOT NULL constraint

Front 93

Which Transact-SQL statement registers an XML schema in the database?

Back 93

The CREATE XML SCHEMA COLLECTION statement

Front 94

Which term refers to a user-defined function that returns a single scalar value?

Back 94

A scalar-valued function

Front 95

Which full-text search predicate is used to search by the criteria that are explicitly defined in the search conditions?

Back 95

The CONTAINS predicate

Front 96

Which operator evaluates to TRUE if the comparison is true for at least one value returned by the subquery?

Back 96

The ANY operator

Front 97

Which system stored procedure associates a previously created Database Mail profile with a previously created Database Mail account?

Back 97

The sysmail_add_profileaccount_sp system stored procedure

Front 98

Which system stored procedure creates a Database Mail account?

Back 98

The sysmail_add_account_sp stored procedure

Front 99

When inserting a value into a table, which columns must you specify in the INSERT statement?

Back 99

All columns that require a value and have no default defined, except a column with the IDENTITY property or a computed column

Front 100

Within a DDL trigger, which statement can you include to cancel the DDL operation that fired the trigger?

Back 100

A ROLLBACK statement

Front 101

Which three actions should you take to create a CLR stored procedure?

Back 101

Define and compile a static class method within the .NET Framework language, register the assembly in SQL Server with the CREATE ASSEMBLY statement, and specify the EXTERNAL NAME clause in a CREATE PROCEDURE statement to reference the method

Front 102

Which Transact-SQL statement is used to define the type of data that can be sent and received in a conversation, and can be subsequently allowed or disallowed in a contract?

Back 102

The CREATE MESSAGE TYPE statement

Front 103

What does a partition function do?

Back 103

It maps the rows of a table or index into partitions based on the specified partition boundary values.

Front 104

Which query hint can you specify to force recompilation and generation of a new execution plan each time the query is executed?

Back 104

The RECOMPILE query hint

Front 105

Which action should you take to modify the fill factor of all indexes subsequently created or rebuilt in the database?

Back 105

Use the sp_configure system stored procedure to set the fill factor option.

Front 106

Which permission set value is the most restrictive and prevents access to external files, the registry, and unmanaged code?

Back 106

SAFE

Front 107

Which operator only evaluates to TRUE if the comparison is true for every value returned by the subquery?

Back 107

The ALL operator

Front 108

Which statement should you execute to allow specific values to be inserted into a column that has the IDENTITY property?

Back 108

The SET IDENTITY_INSERT table_name ON statement

Front 109

When using the CONTAINSTABLE or FREETEXTTABLE function, what does the Key column of the result table contain?

Back 109

The value of the column defined as the full-text key column in the base table

Front 110

When using the LIKE operator, what does the percent sign (%) wildcard character indicate?

Back 110

Any character(s) or no character

Front 111

To send mail using SMTP instead of having an Extended MAPI client installed on the server, should you use SQL Mail or Database Mail?

Back 111

Database Mail

Front 112

Which clause of the CREATE QUEUE statement can you specify to identify a stored procedure that should automatically process incoming messages for the queue?

Back 112

The ACTIVATION clause

Front 113

Which method is used to retrieve a value of SQL type from an XML instance?

Back 113

The value() method

Front 114

Before creating a stored procedure that accepts a table-valued input parameter, which statement must you execute first?

Back 114

The CREATE TYPE...AS TABLE statement

Front 115

Which Service Broker object stores a service's incoming messages?

Back 115

A queue

Front 116

Which clause should be used with a FOREIGN KEY constraint to ensure that if a row in the parent table is deleted, all referenced rows in the child table are also deleted?

Back 116

The ON DELETE CASCADE clause

Front 117

When using an OUTPUT clause with a MERGE statement, which function can you use to return the type of action that occurred for the row?

Back 117

$action

Front 118

Which statement can you use to create a primary XML index on an xml data type column?

Back 118

The CREATE PRIMARY XML INDEX statement

Front 119

On which data types can full-text indexes be created?

Back 119

The char, varchar, nvarchar, and varbinary(max) data types

Front 120

Which data type represents data in a round-earth coordinate system, such as latitude and longitude coordinates?

Back 120

The geography data type

Front 121

Which Transact-SQL function returns the position of the first occurrence of a given text pattern within a text string?

Back 121

The PATINDEX function

Front 122

When an UPDATE statement is issued against a table that has an INSTEAD OF UPDATE trigger, which system table can be used to access the row values before the update?

Back 122

The deleted system table

Front 123

Which dynamic management function returns one row for each database entity that is referenced within a user-defined function or stored procedure?

Back 123

The sys.dm_sql_referenced_entities dynamic management function

Front 124

What is the basic syntax to define a Common Table Expression (CTE)?

Back 124

WITH expression_name [(column_name [,...n])] AS (CTE_query_definition);

Front 125

Which two Service Broker object names are specified when creating a service?

Back 125

The queue name and the contract name

Front 126

When creating a computed column in a table, which four items may be referenced in the expression that defines the column's calculation?

Back 126

Constants, functions, operators, and references to other columns within the table

Front 127

What is the result of specifying WITH TIES in a SELECT statement that includes the TOP keyword?

Back 127

All rows that have a value equal to the last row returned by the TOP keyword are also returned.

Front 128

When you create a stored procedure that accepts a table-valued input parameter, which clause must you include for the parameter in the parameter list?

Back 128

The READONLY clause

Front 129

Which Service Broker object defines the type of data that can be sent and received in a conversation?

Back 129

A message type

Front 130

Which term describes an index that is physically separate from the table data and contains ordered pointers to the table data?

Back 130

A nonclustered index

Front 131

Which prefix is not allowed in a DELETE statement's OUTPUT clause?

Back 131

The INSERTED prefix

Front 132

How many primary XML indexes can a XML column have in a table?

Back 132

One

Front 133

Which full-text indexing component is used to eliminate specific noise words from the full-text index?

Back 133

A stoplist

Front 134

How many primary XML indexes can you create on a single xml data type column?

Back 134

Only one

Front 135

Which clause should you include in a CREATE VIEW or ALTER VIEW statement to ensure that no data modifications can be made through the view that would cause the underlying data to violate the view definition?

Back 135

The WITH CHECK OPTION clause

Front 136

Which statement would you use to transfer the data from the first partition of the Sales table into the second partition of the Sales2 table?

Back 136

ALTER TABLE Sales SWITCH PARTITION 1 TO Sales2 PARTITION 2;

Front 137

What must exist to successfully create a secondary XML index on an xml column?

Back 137

A primary XML index for the same column

Front 138

Which PowerShell cmdlet displays all objects for the current node?

Back 138

The Get-ChildItem cmdlet

Front 139

Which keyword should you include in the ORDER BY clause to sort date values from latest to earliest?

Back 139

The DESC keyword

Front 140

When using WITH ROLLUP and WITH CUBE, how can aggregate rows be identified in the result set?

Back 140

They will have NULL values for one or more of the columns in the GROUP BY clause.

Front 141

When must a subquery in a WHERE clause return a single scalar value to avoid generating an error?

Back 141

When the WHERE clause condition uses a single-row operator, such as >, <, >=, or <=

Front 142

Which Transact-SQL function returns a specified number of characters from the left-hand side of a specified character string?

Back 142

The LEFT function

Front 143

To what value does NOT EXISTS evaluate if the subquery returns no rows?

Back 143

TRUE

Front 144

Which transaction isolation level is the default?

Back 144

The READ COMMITTED transaction isolation level

Front 145

Which clause should you include in an ALTER TABLE statement to temporarily disable a FOREIGN KEY or CHECK constraint?

Back 145

The NOCHECK CONSTRAINT clause

Front 146

To pass the value of an output parameter to an external variable, which keyword must be specified in the statement that calls the procedure?

Back 146

The OUTPUT keyword

Front 147

Which keyword is used to begin a Transact-SQL query that uses a Common Table Expression (CTE)?

Back 147

The WITH keyword

Front 148

What is the result of specifying the DEFAULT contract type when creating a service?

Back 148

Both sides of conversations can send and receive DEFAULT message types.

Front 149

Which three sources can the Database Engine Tuning Advisor use as a workload?

Back 149

A Transact-SQL script, a trace file, or a trace table

Front 150

What do the RANGE LEFT and RANGE RIGHT clauses of the CREATE PARTITION FUNCTION statement specify?

Back 150

Whether a boundary value should be stored in the partition on the right side or left side of the boundary

Front 151

Which data type represents data in a Euclidean or flat coordinate system?

Back 151

The geometry data type

Front 152

Which XML method is used to shred an XML data type instance into relational data?

Back 152

The nodes() method

Front 153

Which option can you set to ON to return information about the logical, physical, and read-ahead reads for subsequent queries?

Back 153

The STATISTICS IO option

Front 154

Which XML method is used to query and retrieve XML nodes from an XML instance?

Back 154

The query() method

Front 155

What is the maximum number of clustered indexes that can be created on a single table?

Back 155

One

Front 156

Which dynamic management view can you query to return the session ID of blocking sessions in the database?

Back 156

The sys.dm_exec_requests dynamic management view

Front 157

Which ranking function divides the result set or each partition into a specified number of groups?

Back 157

The NTILE function

Front 158

Which parameter of the sp_addmessage system stored procedure identifies the error number?

Back 158

The @msgnum parameter

Front 159

Which data type stores only a date in YYYY-MM-DD format?

Back 159

The date data type

Front 160

When querying the sys.dm_os_wait_stats dynamic management view, what does a high wait_time_ms value for the SOS_SCHEDULER_YIELD wait type indicate?

Back 160

A CPU bottleneck

Front 161

In which clause of a SELECT statement can the CONTAINSTABLE and FREETEXTTABLE functions be used?

Back 161

The FROM clause of a SELECT statement

Front 162

If you create a view including the WITH CHECK OPTION clause, what is the result if a user attempts to modify data through the view that would cause data to violate the view definition?

Back 162

The user's statement returns an error, and the data is not modified.

Front 163

Which full-text search predicate initiates a search using the criteria that are dynamically generated by parsing the text specified as the search condition?

Back 163

The FREETEXT predicate

Front 164

Which statement will you use to complete a transaction successfully, make any data changes permanent, and release all locks associated with the transaction?

Back 164

The COMMIT TRANSACTION statement

Front 165

How can you create a self-referencing relationship for a table?

Back 165

Create a FOREIGN KEY constraint on one column of the table that references another column in the table.

Front 166

When creating a stored procedure, which clause causes the stored procedure to be recompiled each time it is run?

Back 166

The WITH RECOMPILE clause

Front 167

Which clause can you include when creating a full-text index to override the system stoplist and explicitly specify a custom stoplist to use with the full-text index?

Back 167

The WITH STOPLIST clause

Front 168

What does the COLLATE clause of a CREATE TABLE or CREATE VIEW statement do?

Back 168

It explicitly sets the collation for the table or view.

Front 169

When enabling change tracking using an ALTER DATABASE statement, what does the CHANGE_RETENTION value specify?

Back 169

The length of time that change tracking information should be kept in the database

Front 170

Which Transact-SQL statement is used only to modify values in an existing table?

Back 170

The UPDATE statement

Front 171

What does the @@ERROR function return?

Back 171

Either the error number of the error generated by the most recently executed Transact-SQL statement or zero if the statement did not generate an error

Front 172

In SQL Server Management Studio, which action can you take to view the graphical execution plan used for a query?

Back 172

After executing the query, click the Include Actual Execution Plan button on the query editor toolbar.

Front 173

In which clause of a SELECT statement are the CONTAINS and FREETEXT predicates typically used?

Back 173

The WHERE clause

Front 174

Which Transact-SQL statement removes a column containing data from an existing table without affecting data stored in other columns?

Back 174

The ALTER TABLE...DROP COLUMN statement

Front 175

Which collation is used if no collation is specified at the database, column, or expression level?

Back 175

The collation set at the server during setup

Front 176

Which join should be used to return the rows that exist in both tables along with the rows that exist only in the table to the left of the JOIN keyword?

Back 176

A left outer join

Front 177

Which index must exist on a table before an XML index can be created on it?

Back 177

A primary key clustered index

Front 178

Which system stored procedure should you call to obtain a document handle to pass to the OPENXML function?

Back 178

The sp_xml_preparedocument system stored procedure

Front 179

Which PowerShell cmdlet should you use to position to a specific node?

Back 179

The Set-Location cmdlet

Front 180

How is explicit collation assigned?

Back 180

By including a COLLATE clause in the expression

Front 181

Which system stored procedure is provided to generate a user-configurable event that can be traced in the SQL Profiler?

Back 181

The sp_trace_generateevent system stored procedure

Front 182

Which keyword can you use with the SELECT statement to specify that the result set should suppress duplicates and return only unique rows?

Back 182

The DISTINCT keyword

Front 183

Which syntax is used to create a partition scheme?

Back 183

CREATE PARTITION SCHEME partition_scheme AS PARTITION partition_function [ALL] TO ({file_group | [PRIMARY]} [,...n]);

Front 184

Which Service Broker object specifies how Service Broker will locate services on other computers or in other databases?

Back 184

A route

Front 185

When using the CHANGETABLE function in the FROM clause of a SELECT statement, is a table alias required or optional?

Back 185

Required

Front 186

What is the syntax of a basic UPDATE statement?

Back 186

UPDATE tablename SET colname_1 = {expression | DEFAULT | NULL}, colvalue_1 [,...colname_n = {expression | DEFAULT | NULL}] WHERE condition;

Front 187

Which two functions can you use to perform a full-text search?

Back 187

The CONTAINSTABLE and FREETEXTTABLE functions

Front 188

What defines a data value to be assigned automatically to a column if the user does not specify a value for the column when adding a record?

Back 188

A DEFAULT definition

Front 189

Which parameter of the sp_addmessage system stored procedure specifies the custom error message text to be displayed when the error is raised?

Back 189

The @msgtext parameter

Front 190

Which statement can you use to create a custom stoplist named CustomStop from the system stoplist?

Back 190

CREATE FULLTEXT STOPLIST CustomStop FROM SYSTEM STOPLIST;

Front 191

What is the syntax used to create a primary XML index on an xml data type column?

Back 191

CREATE PRIMARY XML INDEX indexname ON tablename(colname);

Front 192

Which operator should you use to create a single result set consisting of results from two SELECT statements, but excluding duplicates?

Back 192

The UNION operator

Front 193

Which Service Broker object specifies the message types that can be sent and received between two targets and the conversation participants that can send messages?

Back 193

A contract

Front 194

Which function can be used within the body of a DDL trigger to return information on the event that caused the trigger to fire, such as the name of the instance on which the event was fired, the Transact-SQL statement that caused the event to fire, and the type of event that was fired?

Back 194

The EVENTDATA function

Front 195

Which XML method is used to determine if the query returns a nonempty result?

Back 195

The exist() method

Front 196

Which clause can be used with the GROUP BY clause to explicitly specify the groups for which aggregate information should display, and allows you to use multiple groupings within a single query?

Back 196

The GROUPING SETS clause

Front 197

Which Transact-SQL function can be used to calculate the time interval between two specified dates?

Back 197

The DATEDIFF function

Front 198

When creating a Service Broker application, which Service Broker object specifies the queue in which to store messages and a contract defining the type of messages that can be sent and received?

Back 198

A service

Front 199

Which clause is used to specify the columns by which rows must be grouped when aggregate functions are included in the SELECT list?

Back 199

The GROUP BY clause

Front 200

Which two fill factor values indicate that leaf-nodes will be filled completely when the index is created or rebuilt?

Back 200

A fill factor value of 100 or 0

Front 201

When using the OPENXML function, what does the row pattern indicate?

Back 201

The name of the node from which to begin processing

Front 202

Which query hint can you specify to optimize queries based on a specific value for a local variable used in the query?

Back 202

The OPTIMIZE FOR query hint

Front 203

What is the result of including CHANGETABLE(CHANGES Employee, NULL) in the FROM clause of a SELECT statement?

Back 203

A result set is used that contains information on rows that changed to the Employee table within the change tracking retention period.

Front 204

Which query hint can you specify to quickly retrieve a specified number of rows, and return the remainder of the result set when the query completes?

Back 204

The FAST query hint

Front 205

When using an OUTPUT clause in a DML statement, which two prefixes can you use to retrieve information about the columns affected by the DML operation?

Back 205

The INSERTED and DELETED prefixes

Front 206

Which statement within the body of a user-defined function returns a value to the caller?

Back 206

The RETURN statement

Front 207

Which dynamic management function returns one row for each database entity that references another specified entity, such as a user-defined function or stored procedure?

Back 207

The sys.dm_sql_referencing_entities dynamic management function

Front 208

Which keyword should you include in your CREATE TABLE or ALTER TABLE statement for a computed column if you want the column's values to be physically stored in the table?

Back 208

The PERSISTED keyword

Front 209

Which dynamic management function should you use in the FROM clause of a query to extract information stored in a trace file for a specific time period?

Back 209

The fn_trace_gettable dynamic management function

Front 210

What does the default TRUSTWORTHY database property value of OFF indicate?

Back 210

That user-defined functions or stored procedures that use an impersonation context cannot access external resources

Front 211

Which features should you use to capture historical changes to a table over time, including the data values that changed?

Back 211

The Change Data Capture feature

Front 212

Which clause of the MERGE statement specifies the action to take when the records from the source table are not in the target table?

Back 212

The WHEN NOT MATCHED THEN clause

Front 213

In which two ways can you enable FILESTREAM to be able to create a table that can store FILESTREAM data, such as image and video files?

Back 213

Using SQL Server Configuration Manager or using the sp_configure system stored procedure

Front 214

Which aggregate function returns the number of rows in the specified rowset, including null values and duplicates?

Back 214

The COUNT(*) function

Front 215

Which command-line utility is used to incorporate Database Engine Tuning Advisor functionality with scripting?

Back 215

The dta.exe utility

Front 216

Which three Transact-SQL statements prevent subsequent queries from being executed, and instead return execution plan information for the queries?

Back 216

The SET SHOWPLAN_TEXT ON, SET SHOWPLAN_ALL ON, and SET_SHOWPLAN_XML ON statements

Front 217

Which five steps must you perform to pass a table-valued variable to a user-defined stored procedure?

Back 217

Create a user-defined data type, create the stored procedure that accepts a table parameter, declare a variable of the user-defined data type, initialize the variable, and pass the variable as an input parameter to the stored procedure.

Front 218

Which clause can be included with the GROUP BY clause to return a summary row for each possible combination of columns in the GROUP BY clause?

Back 218

The WITH CUBE clause

Front 219

After you create a Common Table Expression (CTE), which statement or statements reference the CTE expression by name as if it were a table or view?

Back 219

Only the statement immediately following the CTE definition

Front 220

For what purpose is a full-text filter that implements the iFilter interface used?

Back 220

To extract text information from a varbinary, varbinary(max), image, or xml data type column based on the documents extension to be able to index the column for full-text searches

Front 221

Which transaction isolation level, SNAPSHOT or SERIALIZABLE, consumes the most system resources and increases the possibility of blocking?

Back 221

The SERIALIZABLE isolation level

Front 222

Which type of join combines two tables and returns all rows with matching values in the joining column or columns, as well as rows from either table that contain NULL values in the joining column or columns?

Back 222

A full outer join

Front 223

If you include EXECUTE AS CALLER when creating a stored procedure, what permissions must a user calling the procedure have been granted to successfully execute the procedure?

Back 223

Permissions on the stored procedure and permissions on any underlying database objects referenced by the stored procedure

Front 224

Which clause should you specify in a CREATE VIEW statement if you do not want users to be able to drop base tables or modify base tables in a way that affects the view?

Back 224

The WITH SCHEMABINDING clause

Front 225

Which data type stores only a time in hh:mm:ss.nnnnnnn format?

Back 225

The time data type

Front 226

Which clause can be used with the CREATE FUNCTION statement to ensure that objects referenced by the new function cannot be altered or dropped until the function is altered or dropped?

Back 226

The WITH SCHEMABINDING clause

Front 227

Which Transact-SQL function searches for a given character string within another string and returns the search string's starting position in the other string, if found?

Back 227

The CHARINDEX function

Front 228

Which two predicates can you use to perform a full-text search?

Back 228

The CONTAINS and FREETEXT predicates

Front 229

What is the result if you attempt to insert data that violates a CHECK constraint into a table, but the constraint is disabled?

Back 229

The constraint condition is ignored, and the data is inserted.

Front 230

Which parameter of the sp_addmessage system stored procedure should you specify to create a custom error message that will display in another language?

Back 230

The @lang parameter

Front 231

When you create a custom error message with the sp_addmessage system stored procedure, where is the custom error message stored?

Back 231

The sysmessages table in the master database

Front 232

Which provider allows you to access the hierarchy of SQL Server objects by using a drive and path structure similar to Windows file system?

Back 232

The SQL Server PowerShell provider

Front 233

For what purpose would you include the IDENTITY property for a column when creating a table?

Back 233

To create a column that will automatically be assigned unique, incremental integer values as rows are inserted into the table

Front 234

When creating a table, which column definition should you use to create a column named Picture that can contain FILESTREAM data?

Back 234

Picture varbinary(max) FILESTREAM

Front 235

When using a row constructor to insert multiple rows of data into a table, what is the result if one of the lines in the VALUES clause fails?

Back 235

The entire INSERT statement fails, and no rows are inserted.

Front 236

For what purpose would you include the COLLATE clause in a CREATE DATABASE statement?

Back 236

To create a database with a different collation than the server's collation

Front 237

Which type of storage is used to store large binary objects, such as image or video files, as files on the file system?

Back 237

FILESTREAM storage

Front 238

Which parameter of the sp_addmessage system stored procedure identifies the error's severity level?

Back 238

The @severity parameter

Front 239

Which execution error severity levels are considered informational messages and cannot be caught using a TRY...CATCH construct?

Back 239

Severity levels 0 through 10

Front 240

What is the result of issuing the RAISERROR(60000, 16, 1, 1, 2); statement?

Back 240

The error message associated with error number 60000 in the sysmessages table is displayed with the values 1 and 2 substituted for the error's first two substitution parameters.

Front 241

Which term refers to a user-defined function that returns a value of the table data type?

Back 241

A table-valued function

Front 242

Which database object should you use if you have a third-party application that accesses a table, and schema changes are required that might break the application?

Back 242

A view

Front 243

Which function can you call within a CATCH block to retrieve the name of the stored procedure or trigger that generated the error?

Back 243

The ERROR_PROCEDURE() function

Front 244

Which transaction isolation level is the least restrictive, and consumes the fewest system resources?

Back 244

The READ UNCOMMITTED isolation level

Front 245

Which GROUP BY clause syntax is used with grouping sets to specify multiple groupings within a single query?

Back 245

GROUP BY GROUPING SETS (groupingset1 [,...groupingsetn])

Front 246

Which constraint type enforces data uniqueness for a column and will allow only a single NULL value for the column?

Back 246

A UNIQUE constraint

Front 247

After executing the SET SHOWPLAN_XML ON statement, what is the result on subsequent Transact-SQL statements?

Back 247

SQL Server will not execute subsequent Transact-SQL statements, but will return well-formed XML containing execution plan information for the statements.

Front 248

Which operator would you use to display rows with a value that falls within a range of specified values?

Back 248

The BETWEEN...AND operator

Front 249

Which constraint type applies a Boolean condition to data that is entered in a column and either allows or disallows the data based on the conditional result?

Back 249

A CHECK constraint

Front 250

For what purpose would you create a trace template?

Back 250

To easily create multiple traces with the same characteristics

Front 251

Does EXISTS evaluate to TRUE or FALSE if one or more rows are returned by the subquery?

Back 251

TRUE

Front 252

Which data type should you use to store variable-length Unicode character data?

Back 252

The nvarchar data type

Front 253

Is FILESTREAM enabled or disabled by default?

Back 253

Disabled

Front 254

Which clause in a SELECT statement restricts the rows returned by the query before any grouping occurs?

Back 254

The WHERE clause

Front 255

Which join returns only rows that meet the join criteria in the ON clause?

Back 255

An inner join

Front 256

Which system stored procedure should you use to create a custom error message?

Back 256

The sp_addmessage system stored procedure

Front 257

Which action should you take if you notice that excessive page splitting is occurring for a table?

Back 257

Rebuild the table's indexes decreasing the fill factor.

Front 258

Why does using a covering index improve query performance?

Back 258

All the data required for the query can be retrieved directly from the index.

Front 259

What is created when you create a unique clustered index on a view?

Back 259

An indexed view

Front 260

Which permission set value for assemblies allows access to external system resources, such as the registry or files on the network?

Back 260

EXTERNAL_ACCESS

Front 261

How are column names specified when using FOR XML PATH?

Back 261

As XPath expressions

Front 262

For a datetimeoffset data type, what does a time zone offset of -5:00 indicate?

Back 262

A date/time with a local time zone that is five hours behind Coordinated Universal Time (UTC)

Front 263

Which ranking function returns each row's row number within the returned result set?

Back 263

The ROW_NUMBER function

Front 264

How are transactions committed when running in autocommit mode with IMPLICIT_TRANSACTIONS set to OFF?

Back 264

Each DML statement is automatically committed if it executes successfully.

Front 265

Which dynamic management view provides information regarding each authenticated session in the SQL server?

Back 265

The sys.dm_exec_sessions dynamic management view

Front 266

In a Service Broker application, which Service Broker object must be specified when creating a service?

Back 266

The queue that the service will use to store messages

Front 267

Must DDL triggers be created to fire after an event has occurred, or can they fire instead of the triggering event?

Back 267

They can only fire after the triggering event.

Front 268

Which data type stores variable-length binary data?

Back 268

The varbinary data type

Front 269

Which option should be added to the FOR XML AUTO clause to have columns returned as nested elements rather than as attributes?

Back 269

The ELEMENTS option

Front 270

Which statement should you use to map different partitions created by the partition function to filegroups?

Back 270

The CREATE PARTITION SCHEME statement

Front 271

Which PowerShell cmdlet displays the current item's properties?

Back 271

The Get-Item cmdlet

Front 272

Which three actions must you take to partition a table?

Back 272

Create the partition function, create the partition scheme, and partition the table.

Front 273

Which system stored procedure deletes the internal representation of the XML document that was created using the sp_xml_preparedocument system stored procedure?

Back 273

The sp_xml_removedocument system stored procedure

Front 274

Which operator returns distinct values that are returned by both the query on the left and right sides the operand?

Back 274

The INTERSECT operator

Front 275

Which clause of the CREATE PARTITION SCHEME statement identifies the filegroups?

Back 275

The TO clause

Front 276

What are the two basic types of XML indexes?

Back 276

Primary and secondary

Front 277

Which full-text indexing component is used to identify synonyms when performing queries containing the FREETEXT predicate or the FREETEXTTABLE function?

Back 277

A thesaurus file

Front 278

Which syntax is used to create a partitioned table?

Back 278

CREATE TABLE table_name (coldef1, coldef2, ...) ON partition_scheme (partition_column);

Front 279

For which three structures does the Database Engine Tuning Advisor make recommendations?

Back 279

Indexes, indexed views, and partitions

Front 280

When nesting transactions, what determines whether the inner transaction is committed or rolled back?

Back 280

Whether the outer transaction is committed or rolled back

Front 281

Which ranking function assigns a numerical ranking to rows, but skips a ranking if two rows have an identical value in the ranking column?

Back 281

The RANK function

Front 282

Must columns defined using the SPARSE clause be nullable only, non-nullable only, or either nullable or non-nullable?

Back 282

Nullable

Front 283

Which mode should be specified in the FOR XML clause in the SELECT statement if a single <row> element must be generated for each row in the result set?

Back 283

The RAW mode

Front 284

Which term describes an index that denotes the physical order of data in a table?

Back 284

A clustered index

Front 285

Which system stored procedure creates a Database Mail profile that can be used to group related Database Mail accounts?

Back 285

The sysmail_add_profile_sp system stored procedure

Front 286

Which data type is similar to datetime but offers greater range and precision?

Back 286

The datetime2 data type

Front 287

Which transaction isolation level is the most restrictive, completely isolating transactions from one another and preventing all concurrency side effects?

Back 287

The SERIALIZABLE isolation level

Front 288

Which clause in the CREATE FUNCTION statement specifies the data type of the variable that is returned by the function?

Back 288

The RETURNS clause

Front 289

Which tool should you use to enable Database Mail?

Back 289

The Database Mail Configuration Wizard

Front 290

What is the result of disabling change tracking for the database if one of the database's tables currently has change tracking enabled?

Back 290

The ALTER DATABASE statement fails, and change tracking remains enabled.

Front 291

With a datetimeoffset data type, what does the time zone offset portion represent?

Back 291

The difference between Coordinated Universal Time (UTC) and the local time

Front 292

When an UPDATE statement is issued against a table that has an INSTEAD OF UPDATE trigger, which system table can be used to access the updated row values?

Back 292

The inserted system table

Front 293

Which clause should you include in a CREATE TRIGGER statement to create a trigger that will fire when any user drops a table in the database?

Back 293

The ON DATABASE AFTER DROP_TABLE clause

Front 294

When using the CONTAINSTABLE or FREETEXTTABLE function, what does the Rank column of the result table represent?

Back 294

The relevance ranking, with higher values representing matches that are more relevant

Front 295

Which feature should you use to capture the fact that rows in a table changed, but not capture the actual changed values?

Back 295

The Change Tracking feature

Front 296

When creating a user-defined function or stored procedure, which four values can you specify for the EXECUTE AS clause?

Back 296

SELF, OWNER, CALLER, or a user name

Front 297

For which purpose should you use the sysmail_start_sp system stored procedure?

Back 297

To start the necessary Database Mail components and allow e-mail messages to be processed using Database Mail

Front 298

With default settings enabled, what is the result of including an IDENTITY column in the column list and VALUES clause of an INSERT statement?

Back 298

The INSERT fails and generates an error.