This appendix contains the answers to the exercises at the end of each chapter. The answers are grouped by chapter and numbered to match the associated exercises in the corresponding chapter.

Chapter 1

1. Imperative languages require you to provide the computer with step-by-step directions to perform a task—essentially, you tell the computer how to achieve the end result. Declarative languages allow you to tell the computer what the end result should be and trust the computer to take appropriate action to achieve it.
2. ACID stands for “atomicity, consistency, isolation, durability.” These represent the basic properties of a database that guarantee reliability of data storage, processing, and manipulations.
3. The seven index types that SQL Server supports are clustered indexes; nonclustered indexes; XML indexes; spatial indexes; full-text indexes; and two in-memory table index types, nonclustered hash index and memory-optimized nonclustered index.
4. All of the following are restrictions on all SQL Server UDFs: (1) they cannot perform DML or DDL statements, (2) they cannot change the state of the database (no side effects), (3) they cannot use dynamic SQL, and (4) they cannot utilize certain nondeterministic functions.
5. False. All newly declared variables are set to NULL on creation. You should always initialize newly created variables immediately after creation.

Chapter 2

1. SSDT is an integrated project-oriented development environment for database and application development. SSDT is the replacement for Business Intelligence Development Studio (BIDS).
2. The correct answers are A, B, C, and D. SQL Server 2014 SSMS provides integrated Object Explorer, IntelliSense, code snippets, and a customizable keyboard mapping scheme.
3. SSIS is considered an ETL (extract, transform, load) tool.
4. True. SQLCMD scripting variables can be set via command-line options and environment variables, and in script via the SQLCMD :setvar command.
5. The correct answer is D, All of the Above. BCP can generate format files that can be used with the SSIS Bulk Insert task, with the T-SQL BULK INSERT statement, or with BCP itself. BCP can also import data into tables without a format file and export data from a table to a file.
6. You can query Extended Events trace files directly. With a SQL Profiler trace, you have to load the captured trace data to a table and then query it. Direct querying against Profiler trace data is not supported.
7. SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Azure.

Chapter 3

1. True. SQL 3VL supports the three Boolean results true, false, and unknown.
2. The correct answer is A. In SQL, NULL represents an unknown or a missing value. NULL does not represent a numeric value of 0 or a zero-length string.
3. False. SQL’s BEGIN...END construct defines a statement block but does not limit the scope of variables declared within the statement block. This is contrary to the behavior of C#’s curly braces ({ }).
4. The BREAK statement forces a WHILE loop to terminate immediately.
5. False. TRY...CATCH can’t capture syntax errors, errors that cause a broken connection, or errors with severity of 10 or less, among others.
6. SQL CASE expressions come in both simple and searched CASE expression forms.
7. The correct answers are A and B. T-SQL provides support for read-only cursors and forward-only cursors. There is no such thing as a backward-only cursor or a write-only cursor.
8. The following code modifies the example in Listing 3-10 to return the total sales (TotalDue) by region in pivot-table format. The required change to the code is shown in bold:

   -- Declare variables DECLARE @sql nvarchar(4000);
   DECLARE @temp_pivot table (
       TerritorylD int NOT NULL PRIMARY KEY,
       CountryRegion nvarchar(20) NOT NULL,
       CountryRegionCode nvarchar(3) NOT NULL
   );
   -- Get column names from source table rows INSERT INTO @temp_pivot (TerritorylD,
   CountryRegion,
   CountryRegionCode) SELECT TerritorylD,
   Name,
   CountryRegionCode FROM Sales.SalesTerritory GROUP BY TerritorylD, Name, CountryRegionCode;
   -- Generate dynamic SOL query SET @sql = N'SELECT' + SUBSTRING(
   (
   SELECT N', SUM(CASE WHEN t.TerritoryID = ' + CAST(TerritoryID AS NVARCHAR(3)) +
   N' THEN soh.TotalDue ELSE 0 END) AS ' + QUOTENAME(CountryRegion) AS "*"
   FROM @temp_pivot
   FOR XML PATH('') ), 2, 4000) +
   N' FROM Sales.SalesOrderHeader soh ' +
   N' INNER JOIN Sales.SalesTerritory t ' +
   N' ON soh.TerritoryID = t.TerritoryID; ' ;
   -- Print and execute dynamic SQL PRINT @sql;
   EXEC (@sql);

Chapter 4

1. SQL Server supports three types of T-SQL UDFs: scalar UDFs, multistatement TVFs, and inline TVFs.
2. True. The RETURNS NULL ON NULL INPUT option is a performance-enhancing option that automatically returns NULL if any of the parameters passed into a scalar UDF are NULL.
3. False. The ENCRYPTION option performs a simple code obfuscation that is easily reverse-engineered. In fact, several programs and scripts are available online that allow anyone to decrypt your code with the push of a button.
4. The correct answers are A, B, and D. Multistatement TVFs (as well as all other TVFs) do not allow you to execute PRINT statements, call RAISERROR, or create temporary tables. In multistatement TVFs, you can declare table variables.
5. The following code creates a deterministic scalar UDF that accepts a float parameter, converts it from degrees Fahrenheit to degrees Celsius, and returns a float result. Notice that the WITH SCHEMABINDING option is required to make this scalar UDF deterministic:

   CREATE FUNCTION dbo.FahrenheitToCelsius (@Degrees float)
   RETURNS float
   WITH SCHEMABINDING
   AS
   BEGIN
   RETURN (@Degrees - 32.0) * (5.0 / 9.0); END;

Chapter 5

1. False. The SP RETURN statement can return only an int scalar value.
2. One method of proving that two SPs that call each other recursively are limited to 32 levels of recursion in total is shown here. Differences from the code in the original listing are shown in bold:

   CREATE PROCEDURE dbo.FirstProc (@i int)
   AS
   BEGIN
   PRINT @i;
   SET @i += l;
   EXEC dbo.SecondProc @i;
   END; GO
   CREATE PROCEDURE dbo.SecondProc (@i int)
   AS
   BEGIN
   PRINT @i;
   SET @i += 1;
   EXEC dbo.FirstProc @i; END; GO
   EXEC dbo.FirstProc 1;

3. The correct answer is D. Table-valued parameters must be declared READONLY.
4. The correct answers are A and B. You can use the sprecompile system SP or the WITH RECOMPILE option to force SQL Server to recompile an SP. FORCE RECOMPILE and DBCC RECOMPILEALLSPS are not valid options/statements.

Chapter 6

1. The correct answers are A and B. Developer Edition, Enterprise Edition, and Evaluation Edition of the software support the new in-memory features.
2. False. BIN2 collation on a string data type column is necessary only if it is being used in an index or an ORDER BY clause.
3. The correct answers is C, range index. There is no concept of a clustered index on an in-memory table, and hash indexes are best suited for single-item point lookups.
4. True. By default, if the durability option for a memory-optimized table is not specified, it defaults to durable (SCHEMA_AND_DATA).
5. False. All memory-optimized tables require an index, but only tables that are durable (SCHEMA_AND_DATA option) require a primary key constraint.
6. The correct answers are A, B, and C. Execute as Owner, Self, and User are valid execution contexts. The only listed execution context that is not valid is EXECUTE AS CALLER. This execution context does not allow SQL Server to hardcode execution rights at the time the stored procedure is compiled.

Chapter 7

1. True. In DDL triggers, the EVENTDATA function returns information about the DDL event that fired the trigger.
2. True. In a DML trigger, an UPDATE event is treated as a DELETE followed by an INSERT, so both the deleted and inserted virtual tables are populated for UPDATE events.
3. False. DML triggers are not available for SQL Server 2014 in-memory tables.
4. The correct answers are A, C, and E. SQL Server 2014 supports logon triggers, DDL triggers, and DML triggers.
5. The SET NOCOUNT ON statement prevents extraneous rows affected messages.
6. The correct answer is A. The COLUMNSUPDATED function returns a varbinary string with bits set to represent affected columns.
7. True. @@R0WC0UNT at the beginning of a trigger returns the number of rows affected by the DML statement that fired the trigger.
8. False. You cannot create any AFTER triggers on a view.

Chapter 8

1. True. Symmetric keys can be used to encrypt data or other symmetric keys.
2. The correct answers are A, B, and E. SQL Server 2012 provides native support for DES, AES, and RC4 encryption. Although the Loki and Blowfish algorithms are real encryption algorithms, SQL Server does not provide native support for them.
3. False. SQL Server 2014 T-SQL provides no BACKUP ASYMMETRIC KEY statement.
4. You must turn on the EKM provider-enabled option with spconfigure to activate EKM on your server.
5. False. TDE automatically encrypts the tempdb database, but it does not encrypt the model and master databases.
6. True. SQL Server automatically generates random initialization vectors when you encrypt data with symmetric encryption.

Chapter 9

1. True. When a CTE is not the first statement in a batch, the statement preceding it must end with a semicolon statement terminator.
2. The correct answers are A, B, and D. Recursive CTEs require the WITH keyword, an anchor query, and a recursive query. SQL Server does not support an EXPRESSION keyword.
3. The MAXRECURSION option can accept a value between 0 and 32767.
4. The correct answer is E, All of the Above. SQL Server supports the ROWNUMBER, RANK, DENSE_RANK, and NTILE functions.
5. False. You cannot use ORDER BY with the OVER clause when used with aggregate functions.
6. True. When PARTITION BY and ORDER BY are both used in the OVER clause, PARTITION BY must appear first.
7. The names of all columns returned by a CTE must be unique.
8. The default framing clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
9. True. When Orderby is not specified, there is no starting or ending point for the boundary. So, the entire partition is used for the window frame.

Chapter 10

1. False. European language accents are included in the ANSI-encoded characters. You need Unicode for non-Latin characters.
2. The correct answers are A, C, and D. image and (n)text have been deprecated since SQL Server 2005.
3. False. The date data type does not store time zone information. Use the datetimeoffset data type if you need to store time zone information with your date/time data.
4. The hierarchyid data type uses the materialized path model to represent hierarchies in the database.
5. The correct answer is B. The geography data type requires Polygon objects to have a counterclockwise orientation. Also, spatial objects created with the geography data type must be contained in a single hemisphere.
6. The correct answer is B. The SWITCHOFFSET function adjusts a given datetimeoffset value to another specified time offset.
7. True. FILESTREAM functionality utilizes NTFS functionality to provide streaming BLOB data support.
8. The column is named path_locator. It is a hierarchyid type column.

Chapter 11

1. True. Stoplists and full-text indexes are stored in the database.
2. The correct answer is C. You can create a full-text index using the wizard in SSMS or the T-SQL CREATE FULLTEXT INDEX statement.
3. The FREETEXT predicate automatically performs word stemming and thesaurus replacements and expansions.
4. Stoplists contain stopwords, which are words that are ignored during full-text querying.
5. True. The sys.dmftsparser dynamic-management function shows the results produced by word breaking and stemming.

Chapter 12

1. The correct answers are A, B, C, and D. The SQL Server FOR XML clause supports the FOR XML RAW, FOR XML PATH, FOR XML AUTO, and FOR XML EXPLICIT modes. FOR XML RECURSIVE is not a valid FOR XML mode.
2. OPENXML returns results in edge table format by default.
3. True. The xml data type query() method returns results as untyped xml instances.
4. The correct answer is C. A SQL Server primary XML index stores xml data type columns in a preshredded relational format.
5. True. When you haven’t defined a primary XML index on an xml data type column, performing XQuery queries against the column causes SQL Server to perform on-the-fly shredding of your XML data. This can result in a severe performance penalty.
6. True. Additional XML functionality, available through the .NET Framework, can be accessed via SQL Server’s SQL CLR integration.

Chapter 13

1. True. The FOR XML PATH clause supports a subset of the W3C XPath recommendation for explicitly specifying your XML result structure.
2. The correct answer is A. The at sign (@) is used to identify attribute nodes in both XPath and XQuery.
3. The context item (indicated by a single period) specifies the current node or scalar value being accessed at any given point in time during query execution.
4. The correct answers are A, B, and D. You can declare XML namespaces for SQL Server XQuery expressions with the WITH XMLNAMESPACES clause, the declare default element namespace statement, or the declare namespace statement. There is no CREATE XML NAMESPACE statement.
5. In XQuery, you can dynamically construct XML via direct constructors or computed constructors.
6. True. SQL Server 2014 supports all five clauses of FLWOR expressions: for, let, where, order by, and return. Note that SQL Server 2005 did not support the let clause.
7. _SC collation enables SQL Server to be UTF-16 aware.
8. The correct answers are B, C, and D. XQuery provides three types of comparison operators: general comparison operators, node comparison operators, and value comparison operators.

Chapter 14

1. Metadata is “data that describes data.”
2. Catalog views provide insight into database objects and server-wide configuration options.
3. The correct answer is B. Many catalog views are defined using an inheritance model. In the inheritance model, catalog views inherit columns from other catalog views. Some catalog views are also defined as the union of two other catalog views.
4. True. Dynamic-management views and functions provide access to internal SQL Server data structures that would be otherwise inaccessible. DMVs and DMFs present these internal data structures in relational tabular format.
5. The correct answers are A and C. INFORMATION_SCHEMA views provide the advantages of ISO SQL standard compatibility and, as a consequence, cross-platform compatibility.

Chapter 15

1. True. The System.Data.SqlClient namespace provides support for the SQL Server Native Client library, which provides optimized access to SQL Server.
2. The correct answer is B. Disconnected datasets cache required data locally and allow you to connect to a database only as needed.
3. The correct answers are A and C. The benefits of query parameterization include protection against SQL injection attacks and increased efficiency through query plan reuse.
4. False. When you turn on MARS, you can open two or more result sets over a single open connection. MARS requires only one open connection.
5. True. Visual Studio provides a visual O/RM designer with a drag-and-drop interface.
6. The correct answer is D. LINQ to SQL uses deferred query execution, meaning it does not execute your query until the data returned by the query is actually needed.

Chapter 16

1. The correct answers are A, B, C, D, and E. SQL Server 2014 provides support for SQL CLR UDFs, UDAs, UDTs, SPs, and triggers.
2. False. SQL Server 2014 expands the limit on MaxByteSize for UDAs and UDTs to more than 2 billion bytes. In SQL Server 2005, there was an 8,000-byte limit on the size of UDAs and UDTs.
3. The correct answer is D. SAFE permissions allow SQL CLR code to execute managed .NET code. EXTERNALACCESS permissions are required to write to the file system, access network resources, and read the computer’s registry.
4. True. SQL CLR UDAs and UDTs must be declared with the Serializable attribute.
5. A SQL CLR UDA that is declared as Format.UserDefined must implement the IBinarySerialize interface.
6. The correct answers are A, C, D, and E. A SQL CLR UDA is required to implement the following methods: Init, Terminate, Merge, and Accumulate. The Aggregate method is not a required method for UDAs.

Chapter 17

1. False. A LocalDB instance cannot run as a service.
2. False. You can access XML columns from Linux by using the Microsoft ODBC driver for Linux.
3. False. HTTP SOAP endpoints were deprecated in SQL Server 2008.
4. Visual Studio 2010 and 2012 provides the ASP.NET Web Service template for creating new web services.
5. True. Visual Studio includes a built-in graphical EDM designer beginning with SP 1.
6. The correct answer is C. WCF Data Services accepts REST-style queries in requests.

Chapter 18

1. The @@error system function automatically resets to 0 after every successful statement execution.
2. The correct answer is D. The ERROR_SEVERITY() function, available only in the CATCH block in SQL Server, returns the severity level of the error that occurred.
3. True. The RAISERROR statement allows you to raise errors in SQL Server.
4. True. Visual Studio provides integrated debugging of T-SQL functions and SPs. Using Visual Studio, you can step into T-SQL code and set breakpoints.
5. The correct answers are A and B. The potential problems with dynamic SQL include performance issues caused by lack of query plan reuse, and exposure to SQL injection attacks.

Chapter 19

1. The correct answers are A, B, and C. SQL Server 2014 uses data files with an .mdf extension, transaction log files with an .ldf extension, and additional data files with an .ndf extension.
2. False. In-memory optimized tables must be created in a memory-optimized filegroup, specified by the CONTAINS MEMORY_OPTIMIZED_DATA syntax when creating the filegroup.
3. True. SQL Server stores data in 8 KB storage units known as pages.
4. The correct answer is C. Eight contiguous 8 KB pages of storage in SQL Server are known as an extent.
5. A heap is an unordered collection of data pages.
6. Clustered indexes and nonclustered indexes are managed by SQL Server as B-tree structures.
7. Extended Events sessions can be used to trace waits.
8. An optimized nonclustered index is called a filtered index.
9. The correct answers are A and C. SQL Server performance is measured in terms of throughput and response time.