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. Instead of telling the computer how to achieve the result, in declarative languages you tell the computer what the end result should be.
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 five index types that SQL Server supports are clustered indexes, nonclustered indexes, XML indexes, spatial indexes, and full-text indexes.
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.
2. The correct answers are A, B, C and D. SQL Server 2012 SSMS provides integrated Object Explorer, and IntelliSense. Code snippets and 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 the Extended Events trace files directly. With SQL Profiler trace, you have to load the captured trace data to a table and then you can query them. Direct querying against Profiler trace data is not supported.
7. SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, 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 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 searchedCASE 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 4–13 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, there are several programs and scripts 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 following. 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. 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. The correct answers are A, C, and E. SQL Server 2012 supports logon triggers, DDL triggers, and DML triggers.
4. The SET NOCOUNT ON statement prevents extraneous rows affected messages.
5. The correct answer is A. The COLUMNSUPDATED function returns a varbinary string with bits set to represent affected columns.
6. True. @@R0WC0UNT at the beginning of a trigger returns the number of rows affected by the DML statement that fired the trigger.
7. False. You cannot create any AFTER triggers on a view.

Chapter 7

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 2012 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 8

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 then there is no starting or ending point for the boundary. So entire partition is used for the window frame.

Chapter 9

1. False. European language accents fit 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 are 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 10

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 11

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 your 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 12

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 2012 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 13

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 14

1. The correct answers are A, B, C, D and E. SQL Server 2012 provides support for SQL CLR UDFs, UDAs, UDTs, SPs, and triggers.
2. False. SQL Server 2012 expands the limit on MaxByteSize for UDAs and UDTs to over 2 billion bytes. In SQL Server 2005, there was an 8000-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 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 that it does not execute your query until the data returned by the query is actually needed.

Chapter 16

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 are 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 17

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 18

1. The correct answers are A, B, and C. SQL Server 2012 uses data files with an .mdf extension, transaction log files with an .ldf extension, and additional data files with an .ndf extension.
2. True. SQL Server stores data in 8 KB storage units known as pages.
3. The correct answer is C. Eight contiguous 8 KB pages of storage in SQL Server are known as an extent.
4. A heap is an unordered collection of data pages.
5. Clustered indexes and nonclustered indexes are managed by SQL Server as B-tree structures.
6. Extended events sessions can be used to trace waits.
7. Optimized nonclustered index is called filtered index.
8. The correct answers are A and C. SQL Server performance is measured in terms of throughput and response time.