Transcription
APPENDIX AExercise AnswersThis appendix contains the answers to the exercises at the end of each chapter. The answers are grouped bychapter and numbered to match the associated exercises in the correspondingg chapter.Chapter 11.Imperative languages require you to provide the computer with step-by-stepdirections to performfa task—essentiaklly, you tell the computer howw to achieve theend result. Declarative languages allow you to tell the computer wwhat the end resultshould be and trust the computer to take appropriate action to achieve it. Instead oftelling the computer how to achieve the result, in declarative languages you tell thecomputer whatt the end result should be.2.ACICID stands for “atomicity, consistency, isolation, durability.” These represent thebasic properties off a database that guarantee reliability of data storage, processing,and manipulations.3.The ffive index types that SQL Server supports are clustered indexes, nonclusteredindexes, XML indexes, spatial indexes, and fuf ll-text indexes.4.All of the following are restrictions on all SQL Server UDFs: (1) they cannot performDML or DDL statements, (2) they cannot change the state of the database (noside effects), (3) they cannot use dynamic SQL, and (4) they cannot utilize certainnondeterministic functionsf.5.False. All newlw y declared variables are set to NULL on creation. You should alwaysinitialize newlw y created variables immediately afterf creation.Chapter 21.SSDT is an integrated project oriented development environment forf database andapplication development.2.The correct answers are A, B, C and D. SQL Server 2012 SSMS provides integrated ObjectExplorer, and IntelliSense. Code snippets and customizable keyby oard mapping scheme.3.SSIS is considered an ETL (extract, transform, load) tool.4.True. SQLCMD scripting variables can be set via command-line options andenvironment variables, and in script via the SQLCMD :setvar command.607
APPENDIX A N EXERCISE ANSWERS5.The correct answer is D, all off the above. BCP can generate formatff les that can befiused with the SSIS Bulkk Insert task, with the T-SQL BULK INSERT statement, or withBCP itself.f BCP can also import data into tables without a formatff le and export datafiffrom a table to a fif le.6.You can query the Extended Events trace fif les directly. With SQL Profif ler trace, youhave to load the captured trace data to a table and then you can query them. Directf ler trace data is not supported.querying against Profi7.SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Azure.Chapter 31.True. SQL 3VL supports the three Boolean results true, faf lse, and unknown.2.The correct answer is A. In SQL, NULL represents an unknown or missing value. NULLdoes not represent a numeric value off 0 or a zero-length string.3.False. SQL’s BEGIN.END construct definesfa statement block, but does not limitthe scope off variables declared within the statement block. This is contrary to thebehavior of C#’s curly braces ({ }).4.The BREAK statement fforces 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 off 10 or less, among others.6.SQL CASE expressions come in both simplee and searched CASE expression forms.f7.The correct answers are A and B. T-SQL provides support ffor read-only cursors andfforward-only cursors. There is no such thing as a backwarkd-only cursor or awrite-only cursor.r8.The fof llowing code modifiesf the example in Listing 4–13 to return the total sales(TotalDue) by region in pivot table fformat. The required change to the code is shownin bold.-- Declare variables DECLARE @sql nvarchar(4000);DECLARE @temp pivot table (TerritorylD int NOT NULL PRIMARY KEY, CountryRegion nvarchar(20) NOTNULL, 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 pivotFOR XML PATH('') ), 2, 4000) N' FROM Sales.SalesOrderHeader soh ' N' INNER JOIN Sales.SalesTerritory t ' N' ONsoh.TerritoryID t.TerritoryID; ' ;-- Print and execute dynamic SQL PRINT @sql;EXEC (@sql);608
APPENDIX A N EXERCISE ANSWERSChapter 41.SQL Server supports three ttypes of T-SQL UDFs: scalar UDFs,s multistatement TVFs,sand inlinee TVFFs.2.True. The RETURNS NULL ON NULL INPUT option is a performance-enhancingg optionthat automaticallyly returns NULL if anyny of the parameters passed into a scalar UDFare NULL.3.False. The ENCRYPTION option performs a simple code obfuscation that is easilyreverse-engineered. In fact, there are several programs and scripts available onlinethat allow anyn one 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 temporarytables. In multistatement TVFs, you can declare table variables.5.The followingg 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 UDFdeterministic.CREATE FUNCTION dbo.FahrenheitToCelsius (@Degrees float)RETURNS floatWITH SCHEMABINDINGASBEGINRETURN (@Degrees - 32.0) * (5.0 / 9.0); END;Chapter 51.False. The SP RETURN statement can return only an int scalar value.2.One method off proving that two SPs that call each other recursively are limited to32 levels off recursion in total is shown fof llowing. Differencesfffrom the code in theoriginal listing are shown in bold.CREATE PROCEDURE dbo.FirstProc (@i int)ASBEGINPRINT @i;SET @i l;EXEC dbo.SecondProc @i;END; GOCREATE PROCEDURE dbo.SecondProc (@i int)ASBEGINPRINT @i;SET @i 1;EXEC dbo.FirstProc @i; END; GOEXEC dbo.FirstProc 1;609
APPENDIX A N EXERCISE ANSWERS3.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 WITHRECOMPILE option to fforce SQL Server to recompile an SP FORCE RECOMPILE and DBCCRECOMPILEALLSPS are not valid options/statements.Chapter 61.True. In DDL triggers, the EVENTDATA fuf nction returns infof rmation about the DDLevent that fired the trigger.r2.True. In a DML trigger, an UPDATE event is treated as a DELETE followed byy 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, DDLtriggers, and DML triggers.4.The SET NOCOUNT ON statement prevents extraneous rows affected messages.5.The correct answer is A. The COLUMNSUPDATED fuf nction returns a varbinary stringwith bits set to represent affected columns.6.True. @@R0WC0UNT at the beginningg of a trigger returns the number of rows affected bythe DML statement that fired the trigger.7.False. You cannot create anyn AFTER triggers on a view.Chapter 71.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 forfDES, AES, and RC4 encryption. Although the Loki and Blowfisf h algorithms are realencryption algorithms, SQL Server does not provide native support ffor them.3.False. SQL Server 2012 T-SQL provides no BACKUP ASYMMETRIC KEY statement.4.You must turn on the EKM provider enabledd option with spconfigure to activate EKMon your server.5.False. TDE automatically encrypts the tempdb database, but it does not encrypt themodel and master databases.6.True. SQL Server automatically generates random initialization vectors wwhen youencrypt data with symmetric encryption.Chapter 86101.True. When a CTE is not the first statement in a batch, the statement precedingg itmust end with a semicolon statement terminator.2.The correct answers are A, B, and D. Recursive CTEs require the WITH keyword,yananchor query, and a recursive query. SQL Server does not support anEXPRESSION keywordy.
APPENDIX A N EXERCISE ANSWERS3.The MAXRECURSION option can accept a value between 0 and 32767.4.The correct answer is E, all off the above. SQL Server supports the ROWNUMBER, RANK,DENSE RANK, and NTILE functions.f5.False. You cannot use ORDER BY with the OVER clause wwhen used with aggregateffunctions.6.True. WhW en PARTITION BY and ORDER BY are both used in the OVER clause, PARTITIONBY must appear first.f7.The names off all columns returned by a CTE must be unique.8.The defauf lt fframing clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.9.True. WhW en Orderby is not specifief d then there is no starting or ending point ffor theboundary. So entire partition is used ffor the window frame.fChapter 91.False. European language accents fit in the ANSI encoded characters. You needUNICODE for non-Latin characters.2.The correct answers are A, C, and D. image and (n)text are deprecated since SQLServer 2005.3.False. The date data ttype does not store time zone information. Use thedatetimeoffset data ttype if you need to store time zone information with yourdate/time data.4.The hierarchyid data ttype uses the materialized path model to represent hierarchiesin the database.5.The correct answer is B. The geography data ttype requires Polygon objects to have acounterclockwise orientation. Also, spatial objects created with the geography datat pe must be contained in a single hemisphere.ty6.The correct answer is B. The SWITCHOFFSET function adjd usts a given datetimeoffsetvalue to another specified time offset.7.True. FILESTREAM functionalityty utilizes NTFS functionalitty to provide streaming BLOBdata support.8.The column is named path locator. It is a hierarchyid tyt pe column.Chapter 101.True. Stoplists and fuf ll-text indexes are stored in the database.2.The correct answer is C. You can create a fuf ll-text index using the wizard in SSMS orthe T-SQL CREATE FULLTEXT INDEX statement.3.The FREETEXT predicate automatically performsfword stemming and thesaurusreplacementss and expansions.611
APPENDIX A N EXERCISE ANSWERS4.Stoplists contain stopwords, wwhich are words that are ignoredd during full-textquerying.5.True. The sys.dmftsparser dynamic management ffunction shows the resultsproduced by word breaking and stemming.Chapter 111.The correct answers are A, B, C, and D. The SQL Server FOR XML clause supports theFOR XML RAW,W 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 edgee table format by default.3.True. The xml data ttype query() method returns results as untyt ped xml instances.4.The correct answer is C. A SQL Server primaryy XML index stores your xml data ttypecolumns in a preshredded relational format.5.True. When you haven’t defined a primaryy XML index on an xml data ttyype column,performingg XQuery queries against the column causes SQL Server to perform on-the-flylshreddingg of your XML data. This can result in a severe performance penaltyt .6.True. Additional XML functionalityt , available through the .NET Framework, can beaccessed via SQL Server’s SQL CLR integration.Chapter 126121.True. The FOR XML PATH clause supports a subset of the W3C XPath recommendationfor explicitlyy speciffying your XML result structure.2.The correct answer is A. The at sign (@) is used to identifyfy attribute nodes in bothXPath and XQuery.3.The context item (indicated byy a single period) specifies the current nodee or scalarvaluee 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 ServerXQueryy expressions with the WITH XMLNAMESPACES clause, the declare defaultelement namespace statement, or the declare namespace statement. There is noCREATE XML NAMESPACE statement.5.In XQuery, you can ddynamicallyy construct XML via directt constructors orcomputedd constructors.6.True. SQL Server 2012 supports all five clauses of FLWOR expx ressions: 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. XQueryy provides three types of comparisonoperators: general comparison operators, node comparison operators, and valuecomparison operators.
APPENDIX A N EXERCISE ANSWERSChapter 131.“Metadata” is “data that describes data.”2.Catalog viewss provide insight into database objects and server-wide configurationoptions.3.The correct answer is B. Many catalog views are definef d using an inheritance model.In the inheritance model, catalog views inherit columns ffrom other catalog views.Some catalog views are also definef d as the union off two other catalog views.4.True. Dynamic management views and ffunctions provide access to internal SQLServer data structures that would be otherwise inaccessible. DMVs and DMFs presentthese internal data structures in relational tabular format.f5.The correct answers are A and C. INFORMATION SCHEMA views provide the advantagesoff ISO SQL standard compatibility and, as a consequence, cross-platformfcompatibility.Chapter 141.The correct answers are A, B, C, D and E. SQL Server 2012 provides support ffor SQLCLR UDFs, UDAs, UDTs, SPs, and triggers.2.False. SQL Server 2012 expands the limit on MaxByteSize ffor UDAs and UDTs to over2 billion bytes. In SQL Server 2005, there was an 8000-byte limit on the size off UDAsand 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 fif le 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 theIBinarySerialize interfacef .6.The correct answers are A, C, D, and E. A SQL CLR UDA is required to implement thef llowing methods: Init, Terminate, Merge, and Accumulate. The Aggregate methodfois not a required method ffor UDAs.Chapter 151.True. The System.Data.SqlClient namespace provides support for the SQL ServerNative Client library, which provides optimized access to SQL Server.2.The correct answer is B. Disconnected datasets cache required data locallyy and allowyou to connect to a database onlyy as needed.3.The correct answers are A and C. The benefits of query parameterization includeprotection against SQL injection attacks and increased efficiency through queryplan reuse.613
APPENDIX A N EXERCISE ANSWERS4.False. WhW en you turn on MARS, you can open two or more result sets over a singleopen connection. MARS requires only one open connection.5.True. Visual Studio provides a visual O/RM designer with a drag-and-drop interface.f6.The correct answer is D. LINQ to SQL uses deferrefd query execution, meaning that itdoes not execute your query until the data returned by the query is actually needed.Chapter 161.False. A LocalDB instance cannot run as a service.2.False, you can access XML columns from Linux byy using the Microsoft ODBC driverffor Linux.3.False. HTTP SOAP endpoints are deprecated in SQL Server 2008.4.Visual Studio 2010 and 2012 provides the ASP.NET Web Servicee template for creatingnew web services.5.True. Visual Studio includes a built-in graphical EDM designer beginningg with SP 1.6.The correct answer is C. WCF Data Services accepts REST-styt le queries in requests.Chapter 171.The @@error system function automaticallyy resets to 0 after everyy successfulstatement execution.2.The correct answer is D. The ERROR SEVERITY() function, available onlyy in the CATCHblock in SQL Server, returns the severityty 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 debuggingg of T-SQL functions and SPs. UsingVisual 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 includeperformance issues caused byy lack of query plan reuse, and exposure to SQLinjn ection attacks.Chapter 186141.The correct answers are A, B, and C. SQL Server 2012 uses data fif les with an .mdfextension, transaction log fif les with an .ldf extension, and additional data fif les withan .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 off storage in SQL Server areknown as an extent.4.A heap is an unorderedd collection of data pages.
APPENDIX A N EXERCISE ANSWERS5.Clustered indexes and nonclustered indexes are managed by SQL Server as B-treestructures.6.Extended events sessions can be used to trace waits.7.Optimized nonclustered index is called fif ltered index.8.The correct answers are A and C. SQL Server performancefis measured in terms ofthroughput and response time.615
APPENDIX BXQuery Data TypesSQL Server 2012 supports the data ttypes defined in the XQueryy Data Model (XDM). The supported data types arelisted with their definitions in TTable B-1. The diagram in Figure B-1 is a quick reference showingg the relationshipsbetween the XDM data ttypes.Table B-1. XQueryry Data TypesTTypeDescriptionBase Typesypxs:anySimpleTypeThis is the base ttype for all simple built-in ttypes.xs:anyTypeThis is the base ttype for xs:anySimpleType and complex built-in ttypes.Date/Time/Typesypxs:dateThis ttype represents a Gregorian calendar–based date value exactlyy one dayay inlength, represented in the format yyyy-mm-dd[time offo fset]. time offo fset can bea capital Z for zero-meridian (UTC), or in the format / hh:mm to represent a UTCoffset. An example of a valid xs:date is 2006-12-25Z, which represents December25, 2006, UTC time.xs:dateTimeThis type represents a Gregorian calendar–based date and time value withprecision to 1/1000th of a second. The format is yyyy-mm-ddThh: mm:ss.sss[timeo fset]. Time is specified using a 24-hour clock. As with xs:date, time offoffo fset canbe a capital Z (UTC) or a UTC offset in the format / hh:mm. A valid xs:dateTimevalue is 2006-10-30T13:00: 59.500-05:00, which represents October 30, 2006,1:00:59.5 PM, US Eastern Standard time. Unlike SQL Server 2005, in SQL Server2012 the xs:dateTime type maintains the time zone information you assign insteadoff automatically converting all date/time values to a single time zone. The timezone is also not mandatory in SQL Server 2012.xs:durationThis ttype represents a Gregorian calendar–based temporal (time- based) duration,represented as PyyyyYmmMddDThhHmmMss.sssS. P0010Y03M12DT00H00M00.000S, forinstance, represents 10 years, 3 months, 12 days.xs:gDayThis ttype represents a Gregorian calendar–based daya . The format is dd[timeo fset] (notice the three precedingg hyphen [-] characters). The time offoffo fset isoptional. A valid xs:gDay value is 09Z, which stands for the ninth day of the month,UTC time.(continued)617
APPENDIX B N XQUERY DATAATTATA TYPESTable B-1. (continued)TTypeDescriptionxs:gMonthThis ttype represents a Gregorian calendar–based month. The format is --mm[timeo fset] (notice the two precedingg hyphen characters). time offoffo fset is optional. Avalid xs:gMonth value is 12, which stands for December.xs:gMonthDayThis ttype represents a Gregorian calendar–based month and daya . The format is--mm-dd[time offo fset] (notice the two precedingg hyphens). The time offo fset fof rthis data ttype is optional. A valid xs:gMonthDay value is --02-29 for Februaryy 29.xs:gYearThis ttype represents a Gregorian calendar–based year. The format is yyyy[timeo fset]. The time offoffo fset is optional. The year can also have a precedingg hyphencharacter indicatingg a negative (BCE—“before the Christian Era”) year as opposedto a positive (CE—“Christian Era”) date. A valid xs:gYear value is 0044 for 44 BCE.Notice that all four digits are required in the year representation, even for years thatcan be normallyy represented with less than four digits.xs:gYearMonthThis ttype represents a Gregorian calendar–based year and month. The format isyyyy-mm[time offo fset]. The time offo fset for this data ttype is optional and can be Zor a UTC offset. A valid xs:gYearMonth value is 2001–01 for Januaryy 2001.xs:timeThis ttype represents a time value with precision to 1/1000th of a second, usingg a24-hour clock representation. The format is hh:mm:ss.sss [time offo fset]. Aswith other temporal data ttypes, time offo fset can be Z (UTC) or a UTC offset in thef rmat / hh:mm. A valid xs:time value is 23:59:59.000-06:00, which representsfo11:59:59 PM, US Central Standard time. The canonical representation of midnightin24-hour fof rmat is 00:00:00.Binaryy Typesypxs:base64BinaryThis ttype represents Base64-encoded binaryy data. Base64-encoding symbols aredefined in RFC 2045 (www.ietf.org/rfc/rfc2045.txt) as A through Z, a throughz, 0 through 9, , /, and the trailing sign. Whitespace characters are also allowed,and lowercase letters are considered distinct from uppercase letters. An exampleof a valid xs:base64Binary value is QVByZXNzIEJvb2tzIEFuZCBTUUwgU2VഋydmVyIDIwMDU .xs:hexBinaryThis ttype represents hexadecimal-encoded binaryy data. The symbols defined forencodingg data in hexadecimal format are 0 through 9, A through F, and a throughf. Upper- and lowercase letters A through F are considered equivalent byy this datat pe. An example of a valid xs:hexBinary value is 6170726573732E636F6D.tyBoolean Typeypxs:BooleanThis type represents a Boolean binary truth value. The values supported aretrue (1) and false (0). An example of a valid xs:boolean value is true.Numeric Typesypxs:byteThis type represents an 8-bit signed integer in the range 128 to 127.xs:decimalThis type represents an exact decimal value up to 38 digits in length. Thesenumbers can have up to 28 digits beforefthe decimal point and up to 10 digits afterfthe decimal point. A valid xs:decimal value is 8372.9381.(continued)618
APPENDIX B N XQUERYR DATAATTTAA TYPESTable B-1. (continued)TTypeDescriptionxs:doubleThis ttype represents a double-precision floatingg point value patterned after theIEEE standard for floatingg point types. The representation of values is similar toxs:float values nE[ / ]e, where n is the mantissa followed byy the letter E or eand an expx onent e. The range of valid values for xs:double are approximately 1.79E 308 to 2.23E 308 for negative numbers, 0, and 2.23E 308 to 1.79E 308 for positive numbers.xs:floatThis ttype represents an approximate single-precision floatingg point value perthe IEEE 754–1985 standard. The format for values of this ttype is nEe, where nis a decimal mantissa followed byy the letter E or e and an expx onent. The valuerepresents n·10e. The range forf xs:float values is approximately 3.4028e 38 to 1.401298E 45 ffor negative numbers, 0, and 1.401298E 45 to 3.4028e 38 forfpositive numbers. The special values INF and INF represent negative and positiveinfinity.fSQL Server does not support the XQuery-specifief d special value NaN, whw ichstands for “not a number.” A valid xs:float value is 1.98E 2.xs:intThis type represents a 32-bit signed integer in the range 2147483648 to 2147483647.xs:integerThis type represents an integer value up to 28 digits in length. A valid xs:integervalue is 76372.xs:longThis type represents a 64-bit signed integer in the range 9223372036854775808 to 9223372036854775807.xs:negativeIntegerThis type represents a negative nonzero integer value derived ffrom the xs:integerttype. It can be up to 28 digits in length.xs:nonNegativeIntegerThis type represents a positive or zero integer value derived ffrom the xs:integerttype. It can be up to 28 digits in length.xs:nonPositiveIntegerThis type represents a negative or zero integer value derived ffrom the xs:integerttype. It can be up to 28 digits in length.xs:positiveIntegerThis type represents a positive nonzero integer value derived ffrom the xs:integerttype. It can be up to 28 digits in length.xs:shortThis type represents a 16-bit signed integer in the range 37268 to 32767.xs:unsignedByteThis type represents an unsigned 8-bit integer in the range 0 to 255.xs:unsignedIntThis type represents an unsigned 32-bit integer in the range 0 to 4294967295.xs:unsignedLongThis type represents an unsigned 64-bit integer in the range 0 to 18446744073709551615.xs:unsignedShortThis type represents an unsigned 16-bit integer in the range 0 to 65535.Stringg Typesypxs:ENTITIESThis ttype is a space-separated list of ENTITY tyt pes.xs:ENTITYThis ttype is equivalent to the ENTITY tyt pe from the XML 1.0 standard. The lexicalspace has the same construction as an xs:NCName.(continued)619
APPENDIX B N XQUERY DATAATTATA TYPESTable B-1. (continued)T peTyDescriptionxs:IDThis ttype is equivalent to the ID attribute tyt pe ffrom the XML 1.0 standard. An xs:IDvalue has the same lexical construction as an xs:NCName.xs:IDREFThis ttype represents the IDREF attribute ttype from the XML 1.0 standard. The lexicalspace has the same construction as an xs:NCName.xs:IDREFSThis ttype is a space-separated list of IDREF attribute ttypes.xs:languageThis ttype is a language identifier stringg representing natural language identifiersas specified byy RFC 3066 (www.ietf.org/rfc/rfc3066.txt). A complete list oflanguage codes is maintained byy the IANA registryy at www.iana.org/assignments/language-subtag-registry. Language identifiers must conform to the regularexpx ression pattern [a-zA- Z]{1,8}( [a-zA-Z0-9]{1,8})*. An example of a validlanguage identifier is tlh, which is the identifier for the Klingon language.xs:NameThis ttype is an XML name string. A name stringg must match the XML- specifiedproduction for Name. Per the standard, a Name must begin with a letter, anunderscore, or a colon, and mayay then contain a combination of letters, numbers,underscores, colons, periods, hhyphens, and various other characters designatedin the XML standard as combining characterss and extendersr . Refef r to the XMLstandard at www.w3.org/TR/2000/WD-xml-2e-20000814#NT-Name for specificinfof rmation about these additional allowable Name characters.xs:NCNameThis ttype is a noncolonized name. The format for an xs:NCName is the same as fof rxs:Name, but without colon characters.xs:NMTOKENThis ttype is an NMTOKEN ttype from the XML 1.0 standard. An xs:NMTOKEN value iscomposed of anyny combination of letters, numbers, underscores, colons, periods,h phens, and XML combiningg characters and extenders.hyxs:NMTOKENSThis ttype is a space-separated list of xs:NMTOKEN values.xs:normalizedStringThis ttype is an XML whitespace-normalizedd string, which is one that does notcontain the whitespace characters #x9 (tab), #xA (line feed), or #xD (carriagereturn).xs:stringThis ttype is an XML character string.xs:tokenThis ttype is an XML whitespace-normalized stringg with the following additionalrestrictions on #x20 (space) characters: (1) it can have no leadingg or trailing spaces,and (2) it cannot contain anyny sequences of two space characters in a row.620
APPENDIX B N XQUERYR DATAATTTAA TYPESFigure B-1. XQuery data type systemFi621
APPENDIX CGlossaryACIDThis is an acronyn m for atomicityty, consistenccy, isolation, durabilitty. These four concepts of transactional datastores, includingg SQL databases, ensure data integrity.tAdjd acency list modelThis is the representation of all arcs or edges of a graph as a list. In SQL, this is often implemented as aself-referential table in which each row maintains a pointer to its parent node in the graph.ADO.NET Data ServicesAlso known as “Project Astoria,” ADO.NET Data Services provides middle-tier support for accessing SQL Serverdatabases through REST-style queries and entity data models (EDMs).Anchor queryThis is the nonrecursive query specifief d in the body off a CTE.Application programming interface (API)This is a well-defined interface provided byy an application or service to support requests and communicationsfrom other applications.AssemblyIn SQL Server, a .NET assemblyy is a compiled SQL CLR executable or DLL.Asymmetric encryptionAsymmetric encryption is encryption that requires two differentfkeys: one to encrypt data and another todecrypt it. The most common fform of asymmetric encryption is public key encryption, in which the two keys aremathematically related.623
APPENDIX C N GLOSSARYAtomic data tyt pes, list data tyt pes, and union data tyt pesAtomic data types are indivisible data types that derive ffrom the xs:anyAtomicType type. Examples includexs:boolean, xs:date, and xs:integer. List data types are types that are constructed off sequences of other types.r eredd union off two or more data types, or a restricted subset off aUnion data types are constructed ffrom the orddata type. The XML Schema 1.1 Part 2: Data types specification working draft (www.w3.org/TR/xmlschemall-2/#ordinary-built-ins) defines no built-in union data types.AxisAn axis specifier indicates the relationship between the nodes selected byy the location step and the context node.Examples of axis specifiers include child, parent, and ancestor.Bulk Copy Program (BCP)This is a command-line utilityty supplied with SQL Server for the purpose of quicklly loading large datasets into tables.Catalog viewA catalog view returns a SQL Server database and server-specific metadata.CertificatefA certificatefis an electronic document consisting off an asymmetric key with additional metadata such as anexpiration date and a digital signature that allows it to be verified by a third-party like a certificate authority (CA).Checkk constraintA checkk constraint is a condition placed on a table
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, NULLrepresents an unknown or missing value. NULL does not represent a numeric value of 0 or a zero-length string. 3. False.
