SQL Training and Certification

 

Recommended Books

  

* The Training Kit requires supplemental material for passing the exam (now retired)
** Microsoft has retired this exam as of 1/31/2021, BUT…there really is no replacement. I’ll be leaving the content here.

 

 

About

Exam 70-461 is intended for SQL Server database administrators, system engineers, and developers with two or more years of experience, who are seeking to validate their skills and knowledge in writing queries. The exam measures skills in creating database objects, querying basics and modifying data, manipulating data using operators and functions, creating functions and triggers, and creating programming objects and optimizing queries.

 

Cost

$165.00

schedule exam

* It’s free at any Ignite event (register early)

See SQL code in action: SCCM – SQL Queries

 

Resources


Udemy, A Beginner’s Guide to SQL: A MySQL Tutorial (excellent)

Download the SQL Server Evaluation

Download the Software: Microsoft SQL Server 2014 Express

Download the Software: Microsoft SQL Server 2012 Express

AdventureWorks 64bit Sample Database

Sample Database and Chapter Content for 70-461 Exam Book (MS Press)

SQL on facebook

SQLCourse.com

SQL at W3Schools.com (excellent reference)

SQL Database Language TutorialsPoint (excellent reference)

Beginner SQL Tutorial

Relational Databases at studytonight.com

 

Training Videos


SQL Queries by Wise Owl Tutorials (excellent)

Training for the 70-461 Exam

Database Lesson #2 of 8 – The Relational Model

Database Lesson #3 of 8 – The Structured Query Language (SQL)

Database Lesson #4 of 8 – Data Modeling and the ER Model

 

How To


How to Calculate Your Age in SQL

How to use a Subquery

How to use Join

 

Design


Database Design Create Tables and Relationships with SQL

Entity-Relationship Diagram

Normalization at inform IT

Normalization at studytonight.com

Tips on Optimizing SQL Server Database Design

 

Articles


Five Simple Database Design Errors You Should Avoid

SQLSentry

 

Exam Objectives

Reference Exam objectives 70-461 Querying Microsoft SQL Server 2012 

 

Create Database Objects (24%)
Work with Data (27%)
Modify Data (24%)
Troubleshoot and Optimize Queries (25%)

 

 

Test Yourself

Which clause is last in the logical processing order or binding order for a SELECT statement?

TOP

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?

AFTER

What is the term for logical containers in a database that stores database objects such as tables, indexes, and stored procedures?

schemas

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?

dbo

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?

32 levels

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?

RETURN

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

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?

once

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

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?

A table

What logical concept defines a “missing value” in a relational database?

NULL

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?

foreign key

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?

CHECK

Which clause determines the order of characters in a character set during sort and comparison operations?

COLLATE clause

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

What object allows users access to only certain columns and certain rows of a given table?

a view

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?

constraints

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?

VIEW DEFINITION

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

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?

Table-valued parameters

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?

CAST

Which T-SQL data type is used to process one row at a time in a result set?

A cursor

What T-SQL statement allows you to set a local variable to a value of your choosing?

SET

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?

No

Which column attribute is not transferred to a new table when using the SELECT INTO statement?

FILESTREAM

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 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?

ISNULL

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?

ALTER 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?

ENCRYPTION

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

Can a stored procedure call a function or another stored procedure?

Yes

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?

one

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?

IF/ELSE

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?

WAITFOR

Which keyword should you include in the CREATE SEQUENCE command to generate repeated numbers in a result set?

CYCLE

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?

DECLARE

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?

DATEADD(day,30,SYSDATETIME())

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?

ALTER PROCEDURE

Which function returns the operating system date and time without any timezone offset?

SYSDATETIME

In an INSERT statement, the number of values in the VALUES clause must match the number of what in the INSERT clause?

column names

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?

page compression

In which block is the COMMIT TRANSACTION statement normally inserted in a TRY…CATCH construct?

TRY

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?

schema

Which statement will persist transactional work to the storage and terminate the transaction?

COMMIT 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?

@@TRANCOUNT

When using the current error-handling mechanism in T-SQL, which block contains statements that should execute if an error occurs?

CATCH

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

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 statement will improve a query plan by rebuilding statistics on an index, table, or database?

UPDATE STATISTICS

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?

ROLLBACK TRANSACTION

Which data type is used in row iteration when performing row-based operations?

cursor

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

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?

LOOP join

Which dynamic management view returns the optimized plans that are cached or currently executing in a text format?

sys.dm_exec_text_query_plan

What is the maximum number of clustered indexes allowed in a table or view?

one

When using the current error-handling mechanism in T-SQL, which function returns the error number if invoked in a CATCH block?

ERROR_NUMBER

When using the current error-handling mechanism in T-SQL, which block contains statements that may raise an error?

TRY

Which option in an ALTER INDEX statement drops and recreates the index?

REBUILD

Which dynamic management view returns only the optimized plans that are cached?

sys.dm_exec_cached_plans

Which compression type is automated for nchar and nvarchar column using UCS-2 encoding?

Unicode compression

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 is the default for SQL Server 2012?

READ COMMITTED

Which table hint holds shared, update, or exclusive locks at the row level?

ROWLOCK

In which block is the ROLLBACK TRANSACTION statement normally inserted?

CATCH

Which transaction isolation level uses row versioning with update conflict detection?

SNAPSHOT

Which query hint will force a join in a query to use inner looping?

LOOP

Which built-in function returns an integer indicating the current state of a transaction?

XACT_STATE

Which table hint holds only exclusive locks at the table level?

TABLOCKX

Which table hint holds exclusive locks until the transaction completes?

XLOCK

Which compression type stores row values for fixed-length data types as variable-length?

row compression

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?

BEGIN TRANSACTION

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 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 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 shared locks until the transaction completes and is equivalent to SERIALIZABLE?

HOLDLOCK

When using the traditional error-handling mechanism in T-SQL, which system function returns a non-zero value, indicating an error occurred?

@@ERROR

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 query hint will improve a parameterized query based on certain parameter values?

OPTIMIZE FOR

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 table hint holds shared, update, or exclusive locks at the table level?

TABLOCK

Which table hint holds update locks until the transaction completes?

UPDLOCK

Which table hint holds shared, update, or exclusive locks at the page level?

PAGLOCK

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 uses only a schema stability lock and is equivalent to READUNCOMMITTED?

NOLOCK

Which argument specified in the GROUP BY clause is used to specify how columns will be grouped for aggregation?

GROUPING SETS

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?

PERCENT_RANK

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 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 clause is used to define the scope of aggregation functions?

GROUP BY

Which aggregation function returns the number of items in the specified group?

COUNT

Which logical operator returns true if a value matches a value within a list?

view

 

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?

32 characters

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?

Shared
Exclusive

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
5. Snapshot
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?

Four.
1. Error number
2. Severity level
3. State
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?

EXECUTE
sp_executesql

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?

One.

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?

No.

What are the five UDF options?

1. Encryption.
2. SchemaBinding
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?

4GB.

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?

16

How large can the key be?

900 bytes

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?

999

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?

8 bytes

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?

JOIN
WHERE
GROUP BY
AGGREGATES
ORDER BY

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

sys.dm_exec_query_stats

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.

tags: MrNetTek