Index

A

ABS function, 108

$action option, 407

ad hoc DELETE statements, 218

admin function, 116

Advanced queries, 391

CTE (see Common table expressions (CTEs))

database cleanup, 417

GROUPING SETS, 409–410

MERGE statement

$action option, 407

dbo.CustomerSource and dbo.CustomerTarget, 408

disadvantage, 406

execution code, 407–408

FirstName, MiddleName, and LastName columns, 408

results, 409

syntax, 406

target table, 406

UPDATE, INSERT, and DELETE statement, 406

numbers table

calendar table, 415

comma-delimited list parsing, 416

data management, 416

primary key constraint, 414

sequential list of numbers, 414

table creation, 414

tblNumbers populating, 414–415

tempdb, 414

OUTPUT clause

saving data to table, 404–406

view data, 402–404

pivoted queries

data with CASE, 411–412

PIVOT function, 412–414

AdventureWorks databases, 13–14

AdventureWorks2012 database, 32, 37

Aggregate functions, 170

Aggregate queries

aggregate functions, 170

CTEs, 193

CUBE and ROLLUP, 197–198

DISTINCT vs. GROUP BY, 183

GROUP BY clause, grouping on columns, 172

GROUPING SETS, 196–197

HAVING clause, 179

ORDER BY clause, 177

performance, 200

WHERE clause, using correlated subquery, 187–189

with more than one table, 186

Alias, 41, 223

ALTER TABLE command, 271, 312

ALTER TABLE statement, 314

Arrays, 275

Asterisk (*), 87, 169, 170, 253

AVG, 169

B

BEGIN keyword, 249, 252

BETWEEN operator, 45–47

Big Bang Theory, The, 421

BIGINT, 89

BinaryData column, 370

Books, 421

BREAK statement, 259

BusinessEntityID column, 340

C

calcCol1, 327

Cancel Executing Query icon, 133

Cartesian product, 134, 149

CASCADE rule, 324

CASCADE value, 321

CASE function

listing a column as the return value, 113

overview, 111

CASE function (cont.)

searched CASE, 112

simple CASE, 111

updating data, 225

CAST function, 84–86, 369

ChangeDate column, 405

CHARINDEX function, 92

CHAR string data type, 368

Checkpoint, 373

CHOOSE function, 94

CIRCULARSTRING command, 386, 387

C# language, error handling, 264

CLR integration. See Common Language Runtime (CLR) integration

Clustered index, 31

COALESCE, 83–84, 115

CodePlex samples, 13

Column sets, 388

Common Language Runtime (CLR) integration

assembly, 351, 358

C# code, password generation, 351–356

CREATE PROCEDURE command, 358

dbo.usp_CustomerTotals, 359

dbo.usp_ProductSales, 359

DLL, 350

execution, 358–359

GEOMETRY and GEOGRAPHY data types, 350

HOME-PC server, 357

LoadData() function, 357

parameter @CustomerID, 359

sp_passwordgenerator results, 359

SqlProcedure call in C#, 357

tblHashCode, 357–358

TEST database, 357

Common table expressions (CTEs)

aggregate queries, 193

alternate syntax, 398–399

calling multiple times, 395–396

derived tables, 391

joining another CTE, 396–398

multiple

AS keyword, 394

CREATE TABLE statements, 392

execution code, 392–394

partial results, 394

SELECT statement, 392

tempdb, 392

tempdbdatabase, 394

temp tables syntax, 391

recursive query, 400–402

using to solve complicated join problem, 163

WITH keyword, statement types, 391

Composite keys, 316

COMPOUNDCURVE command, 386, 387

COMPUTED columns, 214, 325, 326

CONCAT statement, 81–82

Conditional code, 349

Conferences, 420

CONTAINS function

how to use, 65

using multiple terms, 66–67

results of full-text search operation, 66

syntax, 65

CONVERT function, 84–86, 104–105, 369, 401

COUNT, 169

CREATE TABLE command, 271

CREATE TABLE statement, 314

CROSS APPLY command, 309

CROSS APPLY operator, 340

CROSS JOIN, 150

D

Data Definition Language (DDL)

statements, 311

triggers, 361

Data manipulation, 203

database cleanup, 238–240

deleting rows

DELETE statement, 218–221

join/subquery, 221–223

overview, 218

truncating process, 223–225

inserting rows

adding one row with literal values, 204–205

automatically populating columns, 214–218

avoiding common insert errors, 205–207

creating and populating table in one statement, 210–212

with default column values, 212–214

missing, 210

multiple rows with one statement, 207–208

from other tables, 208–209

overview, 203

performance, 237–238

transactions

locking tables, 235–236

overview, 233

rolling back, 234–235

writing explicit, 233–234

updating rows

with aggregate functions, 230–233

expressions and columns, 227–229

with JOIN, 229–230

overview, 225

UPDATE statement, 225–227

Data Manipulation Language (DML) statements, 311

Data types, 367

DATE, 375–376

DATETIME2, 375–376

DATETIMEOFFSET, 376–377

HIERARCHYID

CLR, 377

hierarchy creation, 378–380

HumanResources.Employee table, 377

OrganizationalNode, 377

stored procedures, hierarchical data management, 380–382

viewing, 377–378

large-value binary data types

FILESTREAM (see FILESTREAM)

FileTables, 373–375

VARBINARY(MAX) data, 369–370

large-value string data types, 368

NTEXT and TEXT data types, 367

VARCHAR(MAX), 368–369

sparse columns, 388–390

spatial

circular arcs, 386–388

GEOGRAPHY, 384–385

GEOMETRY, 383–384

results tab, 385–386

TIME, 375–376

Database cleanup, 362–365

Database Engine Configuration, 9

Database schemas, 32

DATALENGTH function, 92

DATEADD function, 100–101

DATEDIFF, 101–102

Date function

CONVERT, 104–105

DATEADD, 100–101

DATEDIFF, 101–102

DATENAME and DATEPART, 102–103

DAY, MONTH, and YEAR, 103–104

FORMAT, 105–107

GETDATE and SYSDATETIME, 99

DATENAME and DATEPART function, 102–104

DATETIME2 data type, 375–376

DATETIMEOFFSET data types, 376–377

DAY function, 103

dbo.BinaryTest table, 370

dbo.DateDemo table, 375

dbo.demoCustomer table, 335–336

dbo.demoDept table, 333

dbo.demoPerson table, 335

dbo.fn_AddTwoNumbers function, 341

dbo.fn_FormatPhone function, 342

dbo.fn_RemoveNumbers function, 342

dbo.testCustomer, 328

dbo.tempCustomer table, 348

dbo.testOrder, 328

dbo.testOrderDetail, 328

dbo.Trim function, 341

dbo.udf_Delim, 339

dbo.udf_Product, 339

dbo.ufnGetContactInformation function, 339

dbo.usp_CustomerName, 344

dbo.usp_OrderDetailCount, 347

dbo.vw_Customer, 336

dbo.vw_CustomerTotals, 336

dbo.vw_Products, 336

Default constraint, 212

DEFAULT values, 325–327

defCol, 327

DELETED table, 402–403

Deleting rows

DELETE statement, 218–221

join/subquery, 221–223

truncating process, 223–225

DENSE_RANK function, 123–124

DepartmentID column, 316

Derived tables, 161

DiffInMeters column, 385

Disk Space Requirements screen, 7

DivLevel, 379

DivNode column, 379

E

END keyword, 249, 252

Error Functions, 264

Error handling

T-SQL

using RAISERROR, 268

THROW statement, 269–270

using TRY CATCH with transactions, 269

Error handling, T-SQL (cont.)

using TRY CATCH, 264–265

Error message, 175

Error trapping, 349

ERROR_LINE() function, 264

ERROR_MESSAGE() function, 264

ERROR_NUMBER() function, 264

ERROR_PROCEDURE() function, 264

ERROR_SEVERITY() function, 264

ERROR_STATE() function, 264

EXEC command, 344

Execution plan icon, 74–76

exist() method, 304–305

EXISTS function, 253

EXISTS keyword, 255

Explicit transaction, 382

Expression using operators

COALESCE, 83–84

CONCAT, 81–82

concatenating other data types to strings, 84–86

concatenating strings and NULL, 80–81

data type precedence, 89

ISNULL, 83–84

mathematical operators, 87–88

string concatenation, 79–80

F

FAST_FORWARD option, cursors, 277

FETCH clauses, 121

FILESTREAM, 10

CHECKPOINT command, 373

column populating, 372

Documents folder, 372–373

FileData column, 372

FileStreamDocuments folder, 371

NEWSEQUENTIALID function, 372

ROWGUIDID, 372

SQL Server Configuration Manager, 371

VARBINARY(MAX) column, 370

working with column, 372

FileTables, 373–375

FileTableTest.txt, 374

Filtered index, 390

Foreign keys, 131

FORMAT, 105–107

FOR XML AUTO mode, 292–294

FOR XML clause

AUTO mode, 292–294

EXPLICIT mode, 294–296

modes, 290

PATH mode, 296–299

RAW mode, 291–292

SELECT statement, 290

FOR XML EXPLICIT mode, 294–296

FOR XML PATH mode, 296–299

FOR XML RAW mode, 291–292

FREETEXT function, 68

FROM clause, 37, 131, 132, 149

FULLGLOBE data type, 388

Functions. See also Ranking function

date functions (see Date function)

mathematical functions, 107–110

nesting functions, 97–98

ORDER BY clause, 117–118

performance, 126–128

string functions (see String function)

system functions (see System function)

WHERE clauses, 117–118

G

GEOGRAPHY_AUTO_GRID data type, 388

GEOGRAPHY data type, 382–385

GEOMETRY data types, 382–384

GETDATE function, 99, 214, 375, 405

GetDescendant method, 380

GetLevel method, 377

GetRoot method, 380

Global variables, 262

GROUP BY clause, 171–174, 178, 184, 247, 411

GROUPING SETS, 196–197, 409–410

H

HAVING clause, 177–179, 184

HIERARCHYID data type, 150

CLR, 377

hierarchy creation, 378–380

HumanResources.Employee table, 377

OrganizationalNode, 377

stored procedures, hierarchical data management, 380–382

viewing, 377–378

HumanResources.Employee table, 340

I

IDENTITY columns, 214, 276, 325

IDENTITY_INSERT setting, 215

IF ELSE construct

ELSE, 250

IF, 248

IF EXISTS, 254

IIF statement, 114

IMAGE data type, 369

Include Actual Execution Plan setting, 165

IN expression, 414

INNER JOINs, 186

deleting from tables, 221

joining three/more tables, 137–138

updating with, 229

IN operator, 61–62, 187

INSERTED table, 402–403

Inserting rows

automatically populating columns, 214–218

avoiding common insert errors, 205–206

creating and populating table in one statement, 210–212

multiple rows with one statement, 207–208

overview, 203

INSERT INTO clause, 204

INSERT statements, 204–205

Instance Configuration screen, 6, 7

INT, 89

IntelliSense, 20

INTO keyword, 204

ISNULL, 83–84

ISNUMERIC function, 342

Isolation levels, 235

J

Joins, 131

JustTheDate value, 375

JustTheTime values, 375

K

Kindle, 421

L

Large-value binary data types

FILESTREAM (see FILESTREAM)

FileTables, 373–375

VARBINARY(MAX) data, 369–370

Large-value string data types, 368

NTEXT and TEXT data types, 367

VARCHAR(MAX), 368–369

LEFT function, 91, 369

LEFT OUTER JOIN, 140, 186, 210

LEN function, 92, 369

LIKE with %, 52

LoadData() function, 357

LocalDB option, 1

Local variables, 242

Loops, 237–238, 254, 349

LOWER function, 95

LTRIM functions, 90

M

Many-to-many relationship, 137

Materialized view, 328

MAX, 169

MAXRECURSION option, 401

MERGE statement

$action option, 407

dbo.CustomerSource and dbo.CustomerTarget, 408

disadvantage, 406

execution code, 407–408

FirstName, MiddleName, and LastName columns, 408

results, 409

syntax, 406

target table, 406

UPDATE, INSERT, and DELETE statement, 406

Microsoft Help Viewer, 16, 17

MIN, 169

minus symbol (−), 87

Misc Documents, 373

modify() method, 305–307

modulo (%), 87

MONTH function, 103

N

Named Instances, 7

NCHAR string data type, 368

NEWSEQUENTIALID function, 372

NO ACTION option, 322

NO ACTION value, 321

NO ACTION rules, 324

NOCOUNT property, 276

node() method, 307–309

Nonclustered index, 31

NOT BETWEEN operator, 48–49

NotepadFiles table, 372

NOT NULL options, 312

NOT operator, and subqueries, 154

NOT with Parentheses, 60

NTEXT data type, 367

NTEXT string data type, 368

NTILE function, 124–125

NULL counter variable, 256

NULL in the SalesOrderID column, 142

NULL in the Sales.SalesOrderHeader column, 142

NULL options, 312

NULL values, 63–64, 212

Numbers table

calendar table, 415

comma-delimited list parsing, 416

data management, 416

primary key constraint, 414

sequential list of numbers, 414

table creation, 414

tblNumbers populating, 414–415

tempdb, 414

NVARCHAR string data type, 243, 368

NVARCHAR(MAX) string data type, 368

O

Object creation statements, 349

OFFSET clauses, 121

Online resources, 419–420

OPENXML

attribute-centric mapping, 287–288

ColPattern, WITH clause, 289–290

element-centric mapping, 288–289

query, 285–286

rowset, 285

shredding, 285

sp_xml_preparedocument and sp_xml_removedocument commands, 285

WITH clause, 286–287

ORDER BY clause, 69–70, 117–118, 176, 177, 331, 344

ORDER BY FETCH NEXT–ONLY clause, 71, 72

ORDER BY OFFSET clause, 70

OrganizationalLevel column, 377

OrganizationalNode, 377

OUTER JOINS

adding table to left side of, 147

adding table to right side of, 144

CROSS JOIN, 150

deleting from tables, 221

FULL OUTER JOIN, 149

LEFT OUTER JOIN, 140

self-joins, 151, 152

updating with, 229

Outer query, 396

OUTPUT clause

saving data to table, 404–406

view data, 402–404

OUTPUT parameter, 346–349

OVER clause, 195

P

PARTITION BY clause, 121, 195

Pattern matching

using LIKE with %, 52

restricting the characters

results of queries, 54

square brackets with LIKE, 54

syntax for using brackets, 53

PERSISTED COMPUTED columns, 214, 326

PERSISTED property, 88

Person.Person table, 134

Phone data type, 359, 360

PIVOT function, 412–414

Pivoted queries

data with CASE, 411–412

PIVOT function, 412–414

PK_Department_DepartmentID, 316

plus symbol (+), 87

POWER function, 108

Predicates, 245

Primary key, tables, 206

PRINT statements, 248

IF and ELSE blocks, 250

nesting WHILE loops, 258

variables, 243

Production.ProductColor table, 148

Production.ProductCostHistory table, 336

Production.Product table, 147, 336

Professional Association for SQL Server (PASS), 420

Q

Query, definition, 205

query() method, 302–303

Querying multiple tables

CTEs, 163

derived tables, 161

INNER JOINS

joining on different column name, 135

joining three/more tables, 137–138

OUTER JOINS

adding table to left side of, 145–147

adding table to right side of, 143–145

CROSS JOIN, 150

FULL OUTER JOIN, 149

LEFT OUTER JOIN, 140

self-joins, 151, 152

performance, 166–167

UNION queries, 157–158

R

RAND function, 109

RANK function, 123–124

Ranking function

DENSE_RANK, 123–124

NTILE, 124–125

RANK, 123–124

ROW_NUMBER function, 121–123

Readable values, 378

Recursive code, 400

Referential integrity, 319, 361

REPLACE function, 96

REPLICATE function, 369

REVERSE function, 95

RIGHT function, 91

ROLLBACK command, 234

ROUND function, 109

Row constructors, 207

ROWCOUNT, 257

ROWGUIDID, 372

ROWVERSION columns, 214, 325–327

RTRIM function, 90

rvCol, 327

S

Sales.SalesOrderDetail table, 132

Sales.SalesOrderHeader table, 132

Sales.SalesSpecialOfferProduct table, 136

Sales.Territory table, 145

Sample databases installation

AdventureWorks databases, 13, 14

installing books online, 15

Microsoft Help Viewer, 16, 17

Scalar valued user-defined functions, 337–339

Scoping rules, table variables, 273

Scripted SELECT statement, 39

SELECT INTO statements, 210–212, 271, 273, 336

SELECT list, 132, 414

SELECT statements, 204

cursor loops, 281

data insertion, 209

execution plans, 74–76

filtering data

adding a WHERE clause (see WHERE clause)

using BETWEEN operator, 45–47

combining wildcards, 54–56

date and time, 50–51

using the IN operator, 61–62

using NOT BETWEEN operator, 48–49

using NOT with parentheses, 60

pattern matching (see Pattern matching)

full-text search

using CONTAINS (see CONTAINS function)

using FREETEXT, 68

searching multiple columns, 67

tables with full-text indexes, 65

index properties, 73, 74

mixing literals and column names, 40–41

retrieving from a table, 36–38

running first T-SQL statement, 36

scripted SELECT statement, 39

sorting data

ORDER BY clause, 69, 70

ORDER BY FETCH NEXT–ONLY clause, 71, 72

ORDER BY OFFSET clause, 70

statements returning literal values, 36

variables and, 241

working with nothing, 63–64

Service Configuration screen, 8

Set-based approach, 283

SET command, variables, 243

SET DEFAULT rule, 324

SET DEFAULT value, 321

SET NULL rule, 324

SET NULL value, 321

SET statement, 241

Setup Support Rules, 3

slash (/), 87

SPACES function, 378, 382

Sparse columns, 388–390

Spatial data types, 382

circular arcs, 386–388

GEOGRAPHY, 384–385

GEOMETRY, 383–384

results tab, 385–386

SpatialLocation.ToString method, 385

SportsOrg table, 379, 382

SQL Server

clustered index, 31

database as container, 25–26

database schemas, 32

data in tables, 26

data types

HumanResources.Employee table, 28

phone user-defined data type, 29

denormalized database, 30

editions, 24

nonclustered index, 31

normalization process, 29

normalized database, 31

service vs. application, 24–25

SQL sever files, 26

SQL Server Books Online, 421

SQL Server Business Intelligence Edition, 24

SQL Server Compact Edition, 24

SQL Server Developer Edition, 24

SQL Server Enterprise Edition, 24

SQL Server Express edition installation

Database Engine Configuration, 9, 10

Feature Selection screen, 5, 6

FILESTREAM configuration, 10, 11

Hardware and Software Requirements link, 3

Installation pane, 3, 4

installation report, 11, 12

Instance Configuration screen, 6, 7

LocalDB option, 1

planning pane, 2

Product Key screen, 4

Service Configuration screen, 8, 9

Setup Support Rules, 2, 3

Space Requirements screen, 7, 8

system checks, 5

SQL Server Installation Center, 12

SQL Server Integration Services (SSIS), 203

SQL Server Management Studio (SSMS), 311

launch

Object Explorer, 19

server dialog box connection, 18

running queries

AdventureWorks2012 database, 20

automatically generated code, 22

commented code, 23

file icon results, 23

HumanResources.Employee table, 21

IntelliSense, 20

results, 21

selected code, 22

SQL Server Standard Edition, 24

SQL Server Web Edition, 24

SQL Server Workgroup Edition, 24

SQL Server World User Group, 420

SQRT function, 108

SQUARE function, 108

Statement, definition, 205

Stored procedures

CLR integration

assembly, 351, 358

C# code, password generation, 351–356

complex functions, 350

CREATE PROCEDURE command, 358

dbo.usp_CustomerTotals, 359

dbo.usp_ProductSales, 359

DLL, 350

execution, 358–359

GEOMETRY and GEOGRAPHY data types, 350

HOME-PC server, 357

LoadData() function, 357

parameter @CustomerID, 359

sp_passwordgenerator results, 359

SqlProcedure call in C#, 357

tblHashCode, 357, 358

TEST database, 357

create, alter, drop and execute syntax, 343

default values with parameters, 345–346

encapsulate logic, 342

EXEC command, 344

execution code, 344

using logic, 349–350

ORDER BY clause, 344

OUTPUT parameters, 342, 346–347

partial results, 345

Person.Person table, 344

programming logic, 342

saving results, 347–349

vs. user-defined functions, 343

String function

CHARINDEX, 92

CHOOSE, 94

LEFT and RIGHT, 91

LEN and DATALENGTH, 92

REPLACE, 96

REVERSE, 95

RTRIM and LTRIM, 90

SUBSTRING, 93

UPPER and LOWER, 95

SUBSTRING function, 93

SUM, 169

SWITCHOFFSET function, 376

SYSDATETIME function, 99, 375

SYSDATETIMEOFFSET function, 376

System function

admin function, 116

CASE Function (see CASE function)

COALESCE, 115

IIF, 114

SYSUTCDATETIME function, 375

T

Tables

adding check constraints, 312–313

adding UNIQUE constraints, 313–315

automatically populated columns

AdventureWorks2012 database, 328

COMPUTED columns, 325, 326

dbo.testCustomer, 328

dbo.testOrder, 328

dbo.testOrderDetail, 328

DEFAULT values, 325, 326

defCol and calcCol1, 327

execution code, 326–327

IDENTITY columns, 325

PERSISTED COMPUTED columns, 326

results, 327

ROWVERSION columns, 325

data-modelling software, 311

foreign keys

with delete and update rules, 321–325

execution code, 320–321

REFERENCES keyword, 320

referential integrity, 319

results, 321

syntax, 319–320

primary keys

characteristics, 316

CLUSTERED keyword, 318

execution code, 317

HumanResources.Department table, 316, 317

NOT NULL, 318

syntax, 316

tables creation, 319

variables

application, 274

using as array, 276

Table-valued user-defined functions, 339–342

tblHashCode, 357

Temporary tables

application, 274

using as array, 276

TEXT data type, 367, 368

THROW statement, 269–270

TODATETIMEOFFSET function, 377

TOP keyword, 119–121

ToString method, 378, 384

Triggers, 361

Truncating process, 224

TRY CATCH, 264–265

T-SQL

classes, 421

definition, 1

error handling

using RAISERROR, 268

THROW statement, 269–270

using TRY CATCH, 264–265

using TRY CATCH with transactions, 269

IF ELSE construct

ELSE, 250

IF, 248

IF EXISTS, 254

performance, 280, 282

teaching, 422

temp table/table variables

application, 273–275

using as array, 275–276

variables

declaring and initializing, 241–243

using in WHERE and HAVING clause, 245

WHILE

exiting loop early, 260

using ROWCOUNT, 257

using WHILE loop, 255

U

UDFs. See User-defined functions

Unary relationship, 151

UNION ALL query, 400

Union query, 131, 156–159, 409

grouping sets, 196

inserting multiple rows, 208

Updating rows

with aggregate functions, 230–233

with expressions and columns, 227

UPPER function, 95

Upsert, 406

User groups, 420

User-defined data types (UDTs), 359–360

User-defined functions (UDFs)

performance, 361–362

scalar valued functions, 337–339

vs. stored procedures, 343

table-valued function, 339–342

V

value() method, 303–304

VALUES clause, INSERT statement, 204

VARCHAR(MAX) string data type, 368–369

Variables, T-SQL

declaring and initializing, 241–243

using in WHERE and HAVING clause, 245

VB language, error handling, 264

Vendors, 421

Views

avoiding common problems, 331–333

CREATE VIEW code, 330

data manipulation, 334–336

execution code, 329

graphical designer, 331

HumanResources.Employee table, 328

materialized views, 328

ORDER BY clause, 330

Person.Person table, 328

results, 330

Sales.Customer table, 329

SELECT statements, 329

syntax, 329

WHERE clause, 330

W

WHERE clauses, 178, 180, 181

with alternate operators, 43–45

using correlated subquery, 187

DELETE statements, 218

derived tables and, 161

using functions in, 117–118

how to use, 42

performance, 126–128

predicates expressions, 42

results of using, 43

subqueries, 153, 156

syntax, 42

with three/more predicates, 58–59

with two predicates, 57–58

UPDATE statement, 225

WHILE loop

cursor loops, 276–280

exiting loop early, 260

using ROWCOUNT, 257

table variables, 275

using WHILE loop, 255

Wildcards, 54–56

WITH keyword, 162

Work tables (temporary tables), 210

X

XML, 285

data retrieval, FOR XML clause

AUTO mode, 292–294

EXPLICIT mode, 294–296

modes, 290

PATH mode, 296–299

RAW mode, 291–292

SELECT statement, 290

data type

AdditionalContactInfo column, NULL, 300

built-in table, 300

execution code, 300–301

exist() method, 304–305

modify() method, 305–307

node method, 307–309

query() method, 302–303

results, 301

rules, 300

value() method, 303–304

OPENXML

attribute-centric mapping, 287–288

ColPattern, WITH clause, 289–290

element-centric mapping, 288–289

FORXML, 286

query, 285–286

rowset, 285

shredding, 285

sp_xml_preparedocument and sp_xml_removedocument commands, 285

WITH clause, 286–287

Y, Z

YEAR function, 103