Books I recommend
(click to access on Amazon.com)
SQL at W3Schools.com (excellent reference)
Exam Cost $150
Passing Score is 700/1000
Create Database Objects (24%)
- Create and alter tables using T-SQL syntax (simple statements).
- Create and alter views (simple statements).
- Design views.
- View Summary
- ensure code non regression by keeping consistent signature for procedure, views, and function (interfaces)
- security implications
- Create and modify constraints (simple statements).
- Create and alter DML triggers.
Work with Data (27%)
- Query data by using SELECT statements.
- Ranking Function
- write and perform queries efficiently using the new code items such as synonyms and joins (except, intersect)
- implement logic which uses dynamic SQL and system metadata
- write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables
- determine what code may or may not execute based on the tables provided
- given a table with constraints, determine which statement set would load a table use and understand different
- data access technologies
- CASE versus ISNULL versus COALESCE
- Implement sub-queries.
- Implement data types.
- Implement aggregate queries.
- Query and manage XML data.
Modify Data (24%)
- Create and alter stored procedures (simple statements).
- Modify data by using INSERT, UPDATE, and DELETE statements.
- Combine datasets.
- Work with functions.
Troubleshoot and Optimize Queries (25%)
- Optimize queries.
- Manage transactions.
- Evaluate the use of row-based operations vs. set-based operations.
- Implement error handling.
SQL Questions – Test Yourself
Which clause is last in the logical processing order or binding order for a SELECT statement?
Which TSQL command allows you to establish the database to which you are connected?
The USE command
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 is the name of the DML trigger that fires following the triggering event?
What is the term for logical containers in a database that stores database objects such as tables, indexes, and stored procedures?
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
What is the name of the default schema created in every database?
What type of trigger runs in place of the INSERT, UPDATE, or DELETE statement that fired it?
INSTEAD OF triggers
What is the maximum depth level for nested triggers?
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.
Which T-SQL statement can you include in the code of the trigger to indicate a normal exit from the trigger?
What database object would enforce security on your table by granting different departments permission to different subsets of columns and rows from that table?
What privilege is required for a developer to create a UDF?
the CREATE FUNCTION privilege
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 type of trigger is executed in place of the statement that fires it, but before any table constraints are checked?
An INSTEAD OF trigger
What two constraints, by default, cause an index to be created?
PRIMARY KEY and UNIQUE
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
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?
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?
Against which two database object types can you define an INSTEAD OF trigger?
tables and views
Which operator should be used to determine whether a column contains a value?
The IS NULL operator
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
On what type of database object can you define an AFTER trigger?
What logical concept defines a “missing value” in a relational database?
How many primary keys can a table contain?
0 or 1
Which two optional words can enclose your declarations and executable code in a procedure?
BEGIN and END
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
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 business rule creates a one-to-many relationship between rows in two different tables?
The primary key constraint is logically equivalent to the combination of which two other constraints?
UNIQUE and NOT NULL
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?
Which clause determines the order of characters in a character set during sort and comparison operations?
Which database object, when enabled, can execute Transact-SQL code automatically to enforce complex data integrity rules or perform auditing applications on a table?
What object allows users access to only certain columns and certain rows of a given table?
What does the expression COUNT(*) mean when used in a SQL SELECT statement?
Count the number of rows
What is a user-defined function that returns a single simple value?
a scalar UDF
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 is the term for the various business rules on the columns of a table which control data integrity?
Which clause is typically executed last in a SELECT statement?
the ORDER BY clause
What two DML triggers can be fired by INSERT, UPDATE, or DELETE statements?
AFTER and INSTEAD OF
What command explicitly fires a trigger?
None. A trigger is implicitly fired when the triggering event occurs.
Which user privilege is required to view the metadata for views stored in the system tables?
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?
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
Which type of parameters allow you to pass multiple rows of data into a stored procedure or user-defined function?
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.
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 database object would you create to accept input parameters and execute Transact-SQL code without returning a value?
A stored procedure
What supplied function is used for converting data types?
Which T-SQL data type is used to process one row at a time in a result set?
What T-SQL statement allows you to set a local variable to a value of your choosing?
Which T-SQL command used to execute a procedure?
EXEC or EXECUTE
Does the use of sequences for primary keys eliminate the possibility of gaps in the assigned values?
Which column attribute is not transferred to a new table when using the SELECT INTO statement?
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?
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
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.
What built-in scalar function can examine a column value, and if it is null, return an alternate value rather than the null?
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
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
What command changes the code in a user-defined function?
All parameters and local variables in a procedure, function, or trigger must begin with which character?
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
In order to make the code of your function not readable, what keyword must be part of the header of the CREATE FUNCTION command?
If the mode of a parameter in a procedure or function is not declared, will the default mode be INPUT, OUTPUT, or INPUT/OUTPUT?
Can a stored procedure call a function or another stored procedure?
Which clause should you include in an ALTER PARTITION FUNCTION statement to merge two partitions?
MERGE RANGE (boundary_value)
How many scalar values can be returned by a function?
What type of programming objects cannot call a stored procedure?
a function or a view
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?
What is the correct syntax for a label?
The name of the label terminated by a colon
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
Which is the default for a procedure or function: to execute AS OWNER or to execute AS CALLER?
execute AS CALLER
What T-SQL statement allows you to put a pause in the execution of your program?
Which keyword should you include in the CREATE SEQUENCE command to generate repeated numbers in a result set?
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
Which clause in a SELECT statement allows you to establish multiple conditions with a corresponding action for each condition?
the CASE clause
What T-SQL statement allows you to establish local variables and assign data types to those variables?
How does a stored procedure use memory to improve performance?
Once a stored procedure is compiled, the compilation remains in memory.
How would you construct an expression using a built-in function to add 30 days to the current date?
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 T-SQL statement should you use if you need to change the source code of your stored procedure without first dropping it?
Which function returns the operating system date and time without any timezone offset?
In an INSERT statement, the number of values in the VALUES clause must match the number of what in the INSERT clause?
Which three DML actions can be performed by a MERGE statement?
INSERT, DELETE or UPDATE
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
Which SET statement will automatically terminate and roll back a transaction if a run-time error occurs?
SET XACT_ABORT ON
Which compression type performs Unicode and row compression and also includes prefix and dictionary compression?
In which block is the COMMIT TRANSACTION statement normally inserted in a TRY…CATCH construct?
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 two permission objects are used to group users in a database to reduce administrative effort?
database or application role
Which database object is used to group other database objects to reduce administrative effort?
Which statement will persist transactional work to the storage and terminate the transaction?
Which SET statement will begin a transaction automatically when certain T-SQL statements are executed?
SET IMPLICIT_TRANSACTIONS ON
Which SQL configuration option affects performance of queries that use dynamic values?
the optimize for ad hoc workloads option
Which built-in function returns the number of active transactions?
When using the current error-handling mechanism in T-SQL, which block contains statements that should execute if an error occurs?
Which physical join operation is optimal for large, non-indexed tables, or when there is a significant difference in size between the two tables?
Which transaction isolation level only uses a schema stability lock, impacts transaction concurrency the least, and allows dirty, non-repeatable, and phantom reads?
Which statement will improve a query plan by rebuilding statistics on an index, table, or database?
Which transaction isolation level uses row versioning and update locks for write operations?
READ COMMITTED SNAPSHOT
Which statement will undo transactional work and terminate the transaction?
Which data type is used in row iteration when performing row-based operations?
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?
Which integer value for XACT_STATE indicates there is no active transaction?
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 physical join operation is optimal when one table has an index and one or both tables are small in size?
Which dynamic management view returns the optimized plans that are cached or currently executing in a text format?
What is the maximum number of clustered indexes allowed in a table or view?
When using the current error-handling mechanism in T-SQL, which function returns the error number if invoked in a CATCH block?
When using the current error-handling mechanism in T-SQL, which block contains statements that may raise an error?
Which option in an ALTER INDEX statement drops and recreates the index?
Which dynamic management view returns only the optimized plans that are cached?
Which compression type is automated for nchar and nvarchar column using UCS-2 encoding?
Which type of nonclustered index will improve performance for queries that use large tables for aggregation functions and filter and join operations?
Which transaction isolation level is the default for SQL Server 2012?
Which table hint holds shared, update, or exclusive locks at the row level?
In which block is the ROLLBACK TRANSACTION statement normally inserted?
Which transaction isolation level uses row versioning with update conflict detection?
Which query hint will force a join in a query to use inner looping?
Which built-in function returns an integer indicating the current state of a transaction?
Which table hint holds only exclusive locks at the table level?
Which table hint holds exclusive locks until the transaction completes?
Which compression type stores row values for fixed-length data types as variable-length?
What is an indexed view?
A view that has a unique clustered index and is persisted to disk.
Which statement marks the start of an explicit transaction?
Which physical join operation is optimal when both tables are not small and relatively similar sizes and are indexed on their join column?
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?
Which integer value for XACT_STATE indicates there is an active transaction without an error that affects how it is committed or rolled back?
Which table hint holds shared locks until the transaction completes and is equivalent to SERIALIZABLE?
When using the traditional error-handling mechanism in T-SQL, which system function returns a non-zero value, indicating an error occurred?
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?
Which query hint will improve a parameterized query based on certain parameter values?
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?
Which table hint holds shared, update, or exclusive locks at the table level?
Which table hint holds update locks until the transaction completes?
Which table hint holds shared, update, or exclusive locks at the page level?
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?
Which table hint uses only a schema stability lock and is equivalent to READUNCOMMITTED?
Which argument specified in the GROUP BY clause is used to specify how columns will be grouped for aggregation?
Which non-standard comparison operator returns true if both operands are not equal?
Which analytic function will return percent of a value within a partition?
Which relational operator takes unique rows from a single column and rotates them into multiple columns with aggregation values for rows?
Which data type is a float with 24-bit storage?
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?
Which clause is used to define the scope of aggregation functions?
Which aggregation function returns the number of items in the specified group?
Which logical operator returns true if a value matches a value within a list?
Definition of 1NF
A table is in first normal form is all columns are atomic. No multivalue columns are allowed.
Definition of 2NF
Must be in 1NF. Every nonkey column must be functionally dependent of the entire key.
Definition of 3NF
Must be in 2NF. Every nonkey column must be nontransitively dependent on every key.
Definition of BCNF
Every determinant must be a key.
Definition of 4NF
There must be no nontrivial multivalued dependencies that are not functional dependencies.
Definition of 5NF
Every nontrivial join dependency in the table is implied by the keys of the table.
What is the difference between correlated and self-contained sub-queries?
A correlated sub query contains a reference to the outer query.
What does ACID stand for?
Atomicity – all statements in a transaction succeed or none succeed.
Consistent – every transaction, successful or not, leaves the database in a consistent state.
Isolation – transactions occur in isolation from each other. Isolation levels can vary.
Durability – transactions endure through an interruption of service.
How is transactional durability achieved?
Via the transaction log.
How is transaction isolation achieved?
Via locking and row versioning.
How is atomicity achieved?
Via all statements in a transaction succeeding or not succeeding in a single unit.
How is consistency achieved?
Via the database ensuring that all constraints are enforced, .e.g, data types, uniqueness, foreign keys, etc.
List and describe the transaction modes available.
Autocommit – the default mode. Single statements are committed if they succeed or are rolled back if they fail.
Implicit – when SET IMPLICIT_TRANSACTIONS ON. Requires that a COMMIT or ROLLBACK statement is issued to finish the transaction. (Can span batches)
Explicit – occurs when BEGIN TRAN is used.
What is the transaction name size limit?
What can transaction names be used for?
When used with the WITH MARK clause the database can be restored to that point.
What are savepoints?
By using the SAVE TRANSACTION <savepoint name> statement you can rollback the transaction to selective subsets of work.
What are the two basic types of locks?
What is special about exclusive locks?
They are not compatible with any other type of lock. For example, they have to wait until a shared lock is released on an object before locking it.
List the isolation levels.
1. Read committed – default
2. Read uncommitted
3. Read committed snapshot
4. Repeatable read
6. Serializable – most conservative
What is the scope of an isolation level?
It is scoped to the session.
Describe the Read Committed level.
Only reads data that has been committed. Gets shared locks on SELECTS.
Describe the Read Uncommitted Level.
Can read uncommitted data. Does not attempt a shared lock on SELECT. May read dirty data.
Describe the Read Committed Snapshot level.
Is used by Azure database. Not actually a new isolation level. Different implementation of Read Committed that prevents writers from blocking readers.
Creates snapshot of original data in tempdb. Shared locks not taken on original data.
Is a database scope property.
Is default isolation level for Azure.
Describe the Repeatable Read level.
Shared locks are kept until the end of the transaction preventing updates and deletes to rows. However rows can be inserted.
Describe the Snapshot level.
Uses row versioning in tempdb. Set as persistent database property then set per transaction. Does not suffer from insert problem of repeatable read level (phantom reads, i.e., inserts) because is using snapshot. Does not require shared locks on table.
Describe the serializable level.
Strongest level. All reads repeatable. Inserts not allowed on tables by other transactions.
How many parts do SQL Server error messages have?
1. Error number
2. Severity level
4. Error message
What are the error number ranges?
1 – 49999 for SQL server message.
50000 reserved for custom message that does not have error number.
> 50001 for custom error messages.
What are the severity level ranges?
0 – 10 are informational only
20 – 25 are fatal
> 16 are logged automatically
19 – 25 can only be raised by sysadmin role
Size limit for error messages
255 Unicode characters
How is THROW different from RAISEERROR?
Severity is always 16.
THROW always terminates the batch except when used in TRY block.
The statement before the THROW must be terminated by a semicolon.
Error_number parm does not require a matching defined message in sys.messages.
What’s the difference between TRY_CONVERT and TRY_PARSE?
TRY_PARSE attempts to convert a string to datetime and number data types, while TRY_CONVERT attempts to convert different types in general. Both return NULL if they fail.
What does XACT_ABORT do?
Causes entire batch to fail if any error occurs. Is set per session.
What are the two methods to execute dynamic SQL?
How does sp_executesql differ from EXECUTE?
sp_executesql allows you to parameterize your SQL statement, avoiding the need to use concatenation. However, parameters cannot replace table and column names.
How many batches can a stored procedure contain?
How do you make a stored procedure parameter optional?
Provide a default initialization.
How many result sets can a stored procedure return?
More than one.
How many types of DML triggers are there?
Two. AFTER, and INSTEAD OF
Does a trigger execute for each row affected by the DML statement?
No. It executes only once.
Can AFTER triggers be defined on views?
No. Only tables.
How can you improve the performance of an AFTER trigger?
By checking if @@ROWCOUNT = 0 as the very first statement of the trigger.
Can you nest triggers?
Yes. It is an instance level option, on by default, and the nesting limit is 32.
What is an inline table-valued function?
A user defined function that has a single statement that returns a set.
What are the differences between inline and multi-statement table-valued functions?
1. Inline does not include BEGIN and END block.
2. Inline has RETURN at beginning of statement while multi-statement has RETURN at the end.
3. Multi-statement defines a table variable at the beginning of function into which data must be inserted.
4. Optimizer treats inline function like view. You can use INSERT, UPDATE, and DELETE operations against it.
Can functions create or access temporary tables?
What are the five UDF options?
3. Returns Null on Null Input.
4. Called on Null Input (default).
5. Execute As
What is an extent?
A collection of eight pages.The pages can belong to one or different database objects.
How does SQL Server keep track of which pages and extents belong to a database object?
Using Index Allocation Map (IAM) pages. Every table or index has at least one IAM page.
How much space can an IAM page point to?
How are IAM pages organized?
As doubly-linked lists.
Where does SQL Server store new rows in a heap?
Anywhere it has room.
When does SQL Server allocate pages for a new table?
On the first insert the first IAM and data page are allocated.
How many columns can you include in a key?
How large can the key be?
What is a uniquifier?
It is a sequential integer that SS adds to duplicate values in a key to insure uniqueness internally.
What is internal fragmentation?
Happens when SQL Server has to insert a new row into a full page in order to maintain the logical order of the index. The page must be split. The new page can be allocated anywhere in the mdf file.
What is external fragmentation?
When pages are out of physical order. It can slow down partial or full scans. Also known as logical fragmentation.
How can you control internal fragmentation?
By using FILLFACTOR for leaf-level pages and PAD_INDEX for higher level pages.
How to control external fragmentation?
By rebuilding or re-organizing the index.
How many non-clustered indexes can you have on a single table?
What does a leaf of a non-clustered index contain?
The index keys and row locators.
What are the different types of row locators?
1. Row identifier (RID) if the table is a heap.
2. Clustering key if the table is a b-tree.
What is a key lookup?
A lookup that requires a search on a non-clustered index to retrieve a clustering key which is then used to search a clustered index.
Can you rebuild or reorganize a clustered index without updating the non-clustered indexes?
Yes. As long as the clustering key is not changed the clustered index can be rebuilt without affecting the non-clustered indexes.
How big is a RID?
What is the best practice for the clustered index key?
Keep it short and unique because it is used in all nonclustered indexes.
What is a filtered nonclustered index?
An index that spans a subset of a column’s values only. Typically used to index rare values.
What option do indexed views require?
The SCHEMABINDING option.
What query clauses can benefit from an index?
How can you create a covering index without adding additional columns to the key?
By using the INCLUDE operator in the CREATE INDEX statement an adding the columns to cover the query.
When is the LIKE operator inclusive?
Only when a wildcard is not used at the beginning of the string.
What does the sys.sp_updatestats command do?
Updates all statistics on all tables in the database. (Resource intensive)
When should you consider manually updating statistics?
1. After upgrading a database.
2. When query execution times are slow and the queries are written correctly with the appropriate indexes.
3. Before using query hints.
4. After bulk inserts or truncation of tables.
What is the scope of table variables?
To the batch they are created in.
What is special about constraint names on temporary tables?
They must have unique names per schema. Since temp tables are created in the dbo schema in tempdb, temp table constraint names in different sessions could conflict.
What is the recommendation for naming constraints on temporary tables?
Don’t do it. SQL Server will automatically generate unique names.
Can you create indexes on a table variable?
No. But you can create them indirectly by using PRIMARY and UNIQUE constraints.
Do table variables reside in memory?
No. Both table variables and temporary tables reside in tempdb.
Do table expressions, e.g., CTEs, get persisted like temporary tables/table variables?
No. They are unnested and the database engine acts directly against the base tables.
What’s special about table variables and transactions?
If a table variable is updated and then the transaction is rolled back, the table variable update is not rolled back.
What are the differences in statistics between temp tables and table variables.
Statistics are not kept for table variables which can lead to inefficient queries.
List the types of optimizer hints.
1. Table hints
2. Query hints
3. Join hints
List the algorithms used for joins
1. nested loops
2. merge joins
3. hash joins
What are the requirements for a merge join?
1. At least on equijoin predicate.
2. Sorted inputs from both sides.
What are the algorithms for calculating aggregates?
1. Stream aggregation
2. Hash aggregation
What DMO can you use to get information about query plans
How do you force a different execution plan for each call of a stored procedure?
Use WITH RECOMPILE option when you create the proc.
What is the best practice for optimizer hints?
Use them as a last resort, after creating indexes, updating statistics, and plan guides, etc.
Constraint on optimizer hints?
Cannot be used in subquery, only outer query.
Can only be used in last query in UNION.
Constraint on table hints?
Can be used only on a single table in a query.
List the types of plan guides.
1. OBJECT plan guides.
2. SQL plan guides.
3. TEMPLATE plan guides.