Section 1
Preview this deck
What two constraints, by default, cause an index to be created?
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (214)
Section 1
(50 cards)
What two constraints, by default, cause an index to be created?
PRIMARY KEY and UNIQUE
What is the maximum depth level for nested triggers?
32 levels
Which type of trigger is executed in place of the statement that fires it, but before any table constraints are checked?
An INSTEAD OF trigger
Against which two database object types can you define an INSTEAD OF trigger?
tables and views
In what situation can a function be used to execute the required programming logic?
If there is exactly one value returned, but not as a parameter
Which TSQL command allows you to establish the database to which you are connected?
The USE command
What type of trigger runs in place of the INSERT, UPDATE, or DELETE statement that fired it?
INSTEAD OF triggers
Which clause determines the order of characters in a character set during sort and comparison operations?
COLLATE clause
When you execute an AFTER DELETE trigger, which system table will contain the rows and the values they contained before they were deleted?
the deleted system table
What logical concept defines a "missing value" in a relational database?
NULL
What is a user-defined function that returns a single simple value?
a scalar UDF
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?
The WITH SCHEMABINDING clause
Which clause in a foreign key constraint allows you to delete a row from the parent table and maintains referential integrity by deleting all of the corresponding child rows of that parent?
ON DELETE CASCADE
Which five steps must you perform to pass a table-valued variable to a user-defined stored procedure?
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
What does the expression COUNT(*) mean when used in a SQL SELECT statement?
Count the number of rows
Which database object would you create to accept input parameters and execute Transact-SQL code without returning a value?
A stored procedure
Which user privilege is required to view the metadata for views stored in the system tables?
VIEW DEFINITION
Which two optional words can enclose your declarations and executable code in a procedure?
BEGIN and END
What is the term for the various business rules on the columns of a table which control data integrity?
constraints
Which T-SQL statement can you include in the code of the trigger to indicate a normal exit from the trigger?
RETURN
What supplied function is used for converting data types?
CAST
What privilege is required for a developer to create a UDF?
the CREATE FUNCTION privilege
Which T-SQL data type is used to process one row at a time in a result set?
A cursor
On what type of database object can you define an AFTER trigger?
A table
What is the name of the DML trigger that fires following the triggering event?
AFTER
What object allows users access to only certain columns and certain rows of a given table?
a view
If a DML trigger fires and executes another DML statement also contains a trigger, what will happen to that second trigger?
The second trigger will fire
What command explicitly fires a trigger?
None. A trigger is implicitly fired when the triggering event occurs.
What is the requirement regarding the columns of multiple tables which are combined together using one of the SET operators?
The number and datatype of the columns must be the same for all of the tables in the statement.
The primary key constraint is logically equivalent to the combination of which two other constraints?
UNIQUE and NOT NULL
What does the expression COUNT(salary) mean when used in a SQL SELECT statement?
Count the number of non-NULL values in the salary column
Which type of parameters allow you to pass multiple rows of data into a stored procedure or user-defined function?
Table-valued parameters
What is the name of the default schema created in every database?
dbo
If a trigger aborts due to a run-time error and no exception handler exists, what will occur?
The transaction will be rolled back and control passes to the calling environment.
How many primary keys can a table contain?
0 or 1
If there is an AFTER INSERT trigger on a table, how many times will that trigger fire if you insert 50 rows using a single INSERT?
once
Which database object, when enabled, can execute Transact-SQL code automatically to enforce complex data integrity rules or perform auditing applications on a table?
AFTER trigger
If a stored procedure is running in a production system, and that stored procedure performs a SELECT from a table, what will happen the next time that procedure is run if the table is dropped?
It will fail
What built-in scalar function can examine a list of column names and variables, and return the first non-null value it finds, or return a null if all column names and variables are null?
COALESCE
What constraint is used to override the default value of NULL from being inserted for any column which is not explicitly specified in an INSERT statement?
default
What database object would enforce security on your table by granting different departments permission to different subsets of columns and rows from that table?
a view
Which clause is last in the logical processing order or binding order for a SELECT statement?
TOP
Which clause is typically executed last in a SELECT statement?
the ORDER BY clause
Which operator should be used to determine whether a column contains a value?
The IS NULL operator
What is the term for logical containers in a database that stores database objects such as tables, indexes, and stored procedures?
schemas
If a view is formed by joining two tables together, what database object would allow you to insert a new row into both of the two tables joined together?
an INSTEAD OF trigger
What two DML triggers can be fired by INSERT, UPDATE, or DELETE statements?
AFTER and INSTEAD OF
What business rule creates a one-to-many relationship between rows in two different tables?
foreign key
If a column named State was specified in a table, which constraint would ensure that only the valid two-character abbreviations for state was specified?
CHECK
When one stored procedure calls another, which of the variables and values defined in the calling stored procedure can be seen by the called stored procedure?
Only the variables that were explicitly passed to the called stored procedure in the parameter list
Section 2
(50 cards)
Which clause should you include in an ALTER PARTITION FUNCTION statement to merge two partitions?
MERGE RANGE (boundary_value)
What T-SQL statement allows you to set a local variable to a value of your choosing?
SET
All parameters and local variables in a procedure, function, or trigger must begin with which character?
@
How would you construct an expression using a built-in function to add 30 days to the current date?
DATEADD(day,30,SYSDATETIME())
Which function returns the operating system date and time without any timezone offset?
SYSDATETIME
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?
The ALL keyword
What is the correct syntax for a label?
The name of the label terminated by a colon
Can a stored procedure call a function or another stored procedure?
Yes
In order to make the code of your function not readable, what keyword must be part of the header of the CREATE FUNCTION command?
ENCRYPTION
Which statement will improve a query plan by rebuilding statistics on an index, table, or database?
UPDATE STATISTICS
If the mode of a parameter in a procedure or function is not declared, will the default mode be INPUT, OUTPUT, or INPUT/OUTPUT?
INPUT
Which built-in function returns the number of active transactions?
@@TRANCOUNT
Which statement will persist transactional work to the storage and terminate the transaction?
COMMIT TRANSACTION
Which clause in a SELECT statement allows you to establish multiple conditions with a corresponding action for each condition?
the CASE clause
How does a stored procedure use memory to improve performance?
Once a stored procedure is compiled, the compilation remains in memory.
Which SET statement will automatically terminate and roll back a transaction if a run-time error occurs?
SET XACT_ABORT ON
Which T-SQL command used to execute a procedure?
EXEC or EXECUTE
Which T-SQL construct will execute one set of statement (s) when an expression is true or another set of statement(s) when the expression is false?
IF/ELSE
What built-in scalar function can examine a column value, and if it is null, return an alternate value rather than the null?
ISNULL
Which column attribute is not transferred to a new table when using the SELECT INTO statement?
FILESTREAM
How many scalar values can be returned by a function?
one
Which SQL configuration option affects performance of queries that use dynamic values?
the optimize for ad hoc workloads option
What object types is used to return certain columns from a table based on the primary key column of that table using parameters?
A stored procedure or table-valued user defined function
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?
READONLY
When using the current error-handling mechanism in T-SQL, which block contains statements that should execute if an error occurs?
CATCH
Which three SET statements will display details about the execution plan, not a query's result set?
SET SHOWPLAN_ALL, SET SHOWPLAN_TEXT and SET SHOWPLAN_XML
When must columns specified in an ORDER BY clause be included in the SELECT list of the query?
When the DISTINCT keyword, a GROUP BY clause, or the UNION operator is used
Does the use of sequences for primary keys eliminate the possibility of gaps in the assigned values?
No
Which SET statement will begin a transaction automatically when certain T-SQL statements are executed?
SET IMPLICIT_TRANSACTIONS ON
Which is the default for a procedure or function: to execute AS OWNER or to execute AS CALLER?
execute AS CALLER
Which keyword should you include in the CREATE SEQUENCE command to generate repeated numbers in a result set?
CYCLE
Which database object is used to group other database objects to reduce administrative effort?
schema
Which compression type performs Unicode and row compression and also includes prefix and dictionary compression?
page compression
What type of programming objects cannot call a stored procedure?
a function or a view
Which three DML actions can be performed by a MERGE statement?
INSERT, DELETE or UPDATE
In which block is the COMMIT TRANSACTION statement normally inserted in a TRY...CATCH construct?
TRY
What is the result of the following statement: UPDATE emp SET sal += 250?
It will increase the sal column of every row in the emp table by 250 dollars
Which transaction isolation level only uses a schema stability lock, impacts transaction concurrency the least, and allows dirty, non-repeatable, and phantom reads?
READ UNCOMMITTED
Which two permission objects are used to group users in a database to reduce administrative effort?
database or application role
What is a contained database?
A database that is isolated from other databases within its SQL Server instance, so that it can be moved to other SQL Server instances.
Which T-SQL statement should you use if you need to change the source code of your stored procedure without first dropping it?
ALTER PROCEDURE
Which operator should you use to create a single result set consisting of results from two SELECT statements, but excluding duplicates?
The UNION operator
Which physical join operation is optimal for large, non-indexed tables, or when there is a significant difference in size between the two tables?
HASH join
What statement should be used to INSERT rows in a table that are the output of a procedure in table form (not a scalar)?
INSERT EXEC or INSERT EXECUTE
What command changes the code in a user-defined function?
ALTER FUNCTION
What T-SQL statement allows you to establish local variables and assign data types to those variables?
DECLARE
In an INSERT statement, the number of values in the VALUES clause must match the number of what in the INSERT clause?
column names
What T-SQL statement allows you to put a pause in the execution of your program?
WAITFOR
If a function is deterministic, what will occur if you constantly execute that function with the same set of arguments?
It will always return the exact same result.
If a UDF refers to a table which is subsequently dropped, what happens the next time someone tries to run that UDF?
It will fail
Section 3
(50 cards)
Which table hint holds shared, update, or exclusive locks at the page level?
PAGLOCK
In which block is the ROLLBACK TRANSACTION statement normally inserted?
CATCH
Which type of nonclustered index will improve performance for queries that use large tables for aggregation functions and filter and join operations?
columnstore
Which transaction isolation level uses row versioning with update conflict detection?
SNAPSHOT
Which built-in function returns an integer indicating the current state of a transaction?
XACT_STATE
Which two data types can have a specified precision for number of digits stored and a specified scale for the number of digits stored to the right of the decimal point?
numeric and decimal
Which physical join operation is optimal when both tables are not small and relatively similar sizes and are indexed on their join column?
MERGE join
Which physical join operation is optimal when one table has an index and one or both tables are small in size?
LOOP join
Which transaction isolation level is the default for SQL Server 2012?
READ COMMITTED
Which transaction isolation level uses shared locks for read operations and update locks for write operations on each statement, prevents dirty reads, and allows non-repeatable, and phantom reads?
READ COMMITTED
Which data type is used in row iteration when performing row-based operations?
cursor
Which argument in the FOR XML clause will convert FROM sources to XML elements and SELECT columns to XML attributes, and nest inner join sources as children to the original source element?
AUTO
Which query hint will improve a parameterized query based on certain parameter values?
OPTIMIZE FOR
Which compression type stores row values for fixed-length data types as variable-length?
row compression
Which option in an ALTER INDEX statement drops and recreates the index?
REBUILD
What is the maximum number of clustered indexes allowed in a table or view?
one
Which non-standard comparison operator returns true if both operands are not equal?
!=
Which dynamic management view returns only the optimized plans that are cached?
sys.dm_exec_cached_plans
When using the current error-handling mechanism in T-SQL, which block contains statements that may raise an error?
TRY
Which four SET statements will display a result set of statistical information about query execution, not the query's result set?
SET STATISTICS IO, SET STATISTICS XML, SET STATISTICS PROFILE, and SET STATISTICS TIME
Which table hint holds shared, update, or exclusive locks at the table level?
TABLOCK
Which transaction isolation level uses row versioning and update locks for write operations?
READ COMMITTED SNAPSHOT
Which argument specified in the GROUP BY clause is used to specify how columns will be grouped for aggregation?
GROUPING SETS
Which table hint holds shared locks until the transaction completes and is equivalent to SERIALIZABLE?
HOLDLOCK
Which clause is used to define the scope of aggregation functions?
GROUP BY
Which SET statement will ignore the default optimization when processing SELECT statements, and ignores nonclustered indexes?
SET FORCEPLAN ON
Which option in an ALTER INDEX statement will reduce fragmentation on the index without taking the index offline?
REORGANIZE
Which table hint holds exclusive locks until the transaction completes?
XLOCK
Which query hint will force a join in a query to use inner looping?
LOOP
Which table hint holds only exclusive locks at the table level?
TABLOCKX
Which aggregation function returns the number of items in the specified group?
COUNT
Which transaction isolation level uses shared locks for read operations on the entire transaction and update locks for write operations on each statement, prevents dirty and non-repeatable reads, and allows phantom reads?
REPEATABLE READ
Which relational operator takes unique rows from a single column and rotates them into multiple columns with aggregation values for rows?
PIVOT
Which data type is a float with 24-bit storage?
real
Which table hint holds shared, update, or exclusive locks at the row level?
ROWLOCK
Which dynamic management view returns the optimized plans that are cached or currently executing in a text format?
sys.dm_exec_text_query_plan
Which logical operator returns true if a value matches a value within a list?
view
Which option is used in conjunction with the RAISEERROR statement to store an error in the error and application log for the SQL Server instance?
WITH LOG
Which statement marks the start of an explicit transaction?
BEGIN TRANSACTION
When using the current error-handling mechanism in T-SQL, which function returns the error number if invoked in a CATCH block?
ERROR_NUMBER
Which compression type is automated for nchar and nvarchar column using UCS-2 encoding?
Unicode compression
When using the traditional error-handling mechanism in T-SQL, which system function returns a non-zero value, indicating an error occurred?
@@ERROR
Which analytic function will return percent of a value within a partition?
PERCENT_RANK
Which integer value for XACT_STATE indicates there is an active transaction without an error that affects how it is committed or rolled back?
1
Which table hint holds update locks until the transaction completes?
UPDLOCK
Which transaction isolation level uses range locks for index values or all table rows, impacts transaction concurrency the most, and prevents dirty, non-repeatable, and phantom reads?
SERIALIZABLE
Which integer value for XACT_STATE indicates there is no active transaction?
0
What is an indexed view?
A view that has a unique clustered index and is persisted to disk.
Which table hint uses only a schema stability lock and is equivalent to READUNCOMMITTED?
NOLOCK
Which statement will undo transactional work and terminate the transaction?
ROLLBACK TRANSACTION
Section 4
(50 cards)
Which clause should be used in conjunction with the TOP clause to specify which rows are returned?
ORDER BY
Which clause in an XML index optimizes the index for queries that use path expressions?
FOR PATH
Which two logical operators return true if at least one in a set of comparisons is true?
ANY and SOME
Which clause limits a result set to a specified number or percentage of rows?
TOP
Which logical operator returns true if a value is within a range?
BETWEEN
Which logical operator reverses the value of a Boolean operator?
NOT
Which expression is a temporary result set whose scope exists only within the statement where it is defined?
common table expression (CTE)
Which argument in the FOR XML clause will use a <row> element for each row in the result set and convert SELECT columns to XML attributes without nesting?
RAW
Which analytic function compares previous row(s) in the same result set without a self-join?
LAG
Which set operator returns any distinct values found in both the first query and second query?
INTERSECT
Which database object is a grouping of SQL statements that can receive and send arguments and return a value and/or result set?
stored procedure
Which four numeric data type store variable-sized integer values?
bigint, int, smallint, and tinyint
Which APPLY operator returns only those rows that match between the outer query and the table-valued function?
CROSS APPLY
Which function generates a random 16-byte GUID?
NEWID
Which data types stored variable sized data, intended for images, Office documents and other raw content?
varbinary
Which set operator combines all rows from multiple queries into a single result set with no duplicated rows?
UNION
Which spatial aggregate method will combine multiple spatial objects into logical geometric shapes with interior boundaries remaining?
CollectionAggregate
Which function replaces NULL values with a specified value?
ISNULL
Which dynamic management function returns the metadata for the first result set of a stored procedure or trigger?
sys.dm_exec_describe_first_result_for_object
Which analytic function will return the median value within a partition?
PERCENTILE_DISC
Which relational operator invokes a table-valued function for each row returned by a query?
APPLY
Which ISO standard comparison operator returns true if both operands are not equal?
<>
Which data type represents a binary 1 or 0?
bit
Which default join type includes all matching rows in both tables?
inner join
Which two data conversion functions accept any value type and attempt to return that value as the specified data type?
CAST and CONVERT
Which two data types represent a variable number of characters?
varchar and nvarchar
Which join type includes all rows in the first table and matching rows in the second table?
left outer join
Which two data types represent a fixed number of characters?
char and nchar
Which data type stores hierarchical values to support fine-grained access, indexing, and validation?
xml
Which logical operator returns true if any rows are returned by a subquery?
EXISTS
Which relational operator takes unique columns from a single row and rotates them into multiple rows?
UNPIVOT
Which clause is used in conjunction with the RANK function to specify aggregation functions and partitioning?
OVER
Which three data conversion functions will return a value if successful, but will return NULL if unsuccessful?
TRY_CAST, TRY_CONVERT, and TRY_PARSE
Which data type is a datetime that also supports localization across time zones?
datetimeoffset
Which type of user-defined function (UDF) should you use to perform a complex calculation for a computed column or dynamic table lookups in a WHERE clause?
scalar-valued UDF
Which analytic function compares next row(s) in the same result set without a self-join?
LEAD
Which data type has a larger date range than a datetime and supports user-specified precision?
datetime2
Which join type includes matching rows in the first table and all rows in the second table?
right outer join
Which function distributes values based on its relative placement in a logical grouping based on its partition and skips ranking for ties?
RANK
Which data type can have a specified storage size for its mantissa?
float
Which option in the FOR XML clause will convert SELECT columns to XML elements?
ELEMENTS
Which data conversion function accepts a string value and attempts to return that value as the specified data type for an optional culture?
PARSE
Which spatial aggregate method will remove interior boundaries between spatial objects and combine contiguous spatial objects into a composite spatial object?
UnionAggregate
Which clause is used in conjunction with the GROUP BY clause to limit the aggregated rows in a result set?
HAVING
Which join type will combine each row from the first table with each row of the second table?
cross join
Which logical operator returns true if all comparisons are true?
ALL
Which clause is used to specify a common table expression (CTE)?
WITH clause
Which logical operator returns true if a value matches a wildcard pattern?
LIKE
Which APPLY operator includes those rows in the outer query that do not have matching rows in the table-valued function?
OUTER APPLY
Which argument specified in the GROUP BY clause aggregates subtotals and calculates a grand total depending on the order of specified columns?
ROLLUP
Section 5
(14 cards)