Index

A

abbreviations 11

ACID properties 196–203

actual query plans 314–319

AFTER triggers 160, 176–179

algebrizer 283

ALTER INDEX statement 89, 90, 92

ALTER statement 61

ALTER TABLE command 120–122

ALTER TABLE statement 14–15, 27, 253

approximate numeric data type 16

artificial keys 5, 47

ASCII characters 18

atomic blocks 249

atomicity 196, 198–201

attributes 3

relationship of non-key to key 8–11

auto-commit transactions 203

autocommit transactions 151

AUTO_CREATE_STATISTICS 271

Autogrowth 338

AUTO_UPDATE_STATISTICS 271

AUTO_UPDATE_STATISTICS_ASYNC 271

Azure Access Panel. See Access Panel

Azure portal 319–320, 356–357

Azure SQL Database

DMVs for 358

Extended Events in 358

performance monitoring 356–358

Azure SQL Database Performance Insight 283, 319–324

Azure SQL Database query plans 346

B

backward compatibility 64

baseline performance metrics 347–362

compared to observed metrics 352–355

batch execution mode 79–80

batch separator 12

BEGIN ATOMIC_END clause 249

BEGIN TRANSACTION statement 151, 205, 207

binary data 17

bookmark lookups 30

bottlenecks 242, 243, 340, 345

Boyce-Codd normal form 9

brackets 11

B-Tree indexes 24–50

designing 26–40

structure 24–26

bulk data loading

into clustered columnstore index 89–93

bulk update locks 214

business requirements

for database 2–4

view structure design based on 53–57

business rules

enforcing with constraints 102–115

C

cache issues 345–346

Camel Casing 12

cardinality 74, 266–270

cascading operations 113–115

CASE statement 247

character data 17

CHECK constraint 65

CHECK constraints 102, 107–111, 121, 160

classification process 325

classifier user-defined functions 329–330

client-side tracing 290–292, 360

CLR. See Common Language Runtime (CLR)

CLR stored procedures 130

clustered columnstore indexes 73–80, 85–88

bulk data loading 89–93

clustered indexes 25, 46–48, 69, 85

Clustered Index Scan operator 36, 50, 69, 293–294

clustering key 46–47, 48

COALESCE() function 20

columnar data 165–167

columnar databases 70–71

column constraints 102

COLUMNPROPERTYEX() function 34

columns 5, 5–6

adding 14–15

cardinality of 266–270

clustered index 46–48

computed 20–21, 34

data format for 109–110

data types for 15–24

density of 268

dropping 15

foreign key 28–32, 74

indexed vs. included 41–45

limiting to set of values 118–119

NOT NULL 104

NULL values in 28, 106

column segments

in columnstore indexes 72

columnstore indexes 70–93, 252

adding rows to compressed rowgroups 83

attributes of 85

batch execution mode 79–80

clustered 73–80, 85–88

loading data into 89–93

maintenance 89–95

non-bulk operations on 93–95

non-clustered 78, 80–88

structure of 71–72

targeting analytically valuable columns in 83

use cases 70–73

Columnstore Scan operator 88

COMMIT statement 204–205

COMMIT TRANSACTION 151

COMMIT TRANSACTION statement 205

Common Language Runtime (CLR) 130

common table expressions (CTEs) 116, 248

company names 18–19

comparison operators 59

composite indexes 25, 39

compressed rowgroups 83

COMPRESSION_DELAY setting 83

computed columns 20–21

indexing 34

Compute Scalar operator 30

concurrency 26

concurrent processes

potential problems with 211–216

concurrent queries

results of 219–228

connectors. See also receive connectors; See also send connectors

consistency 196, 201

constraints 14, 61, 101, 102–129

adding to tables 119–122

CHECK 65, 102, 107–111, 121, 160

column 102

DEFAULT 48, 65, 102, 103–105

FOREIGN KEY 73, 75, 102

relating to UNIQUE constraint 117–119

use of 110–116

PRIMARY KEY 27, 28, 41, 47, 65, 102, 106, 111–112, 120, 125–130

results of DML statements and 123–125

table 102

UNIQUE 102, 105–106, 117–119, 120

uniqueness 27–28

covering indexes 41–45, 57, 77

CPU pressure 341

CPU usage 350

CREATE COLUMNSTORE INDEX statement 83

CREATE INDEX statement 29, 39, 43, 48

CREATE SCHEMA statement 12

CREATE TABLE statement 12–15

CTEs. See common table expressions (CTEs)

D

DAC. See Datacenter Activation Coordination

DAGs. See Database Availability Groups

DAS. See Direct-Attached Storage

data

binary 17

character (string) 17

hiding 53

limiting, through DDL 61–62

modifying in views, with multiple tables 62–64

redundant 8, 9

reformatting 53, 54–55

view structure design to select 53–57

data access

real-time 244

database concurrency 195–264

isolation levels and 216–229

locking behavior and 230–241

memory-optimized tables and 242–255

natively compiled stored procedures and 242, 255–258

transactions and 195–216

DATABASE DDL triggers 172–174

database design

adding indexes during 26–32

based on business requirements 2–4

denormalization 11

determining data types 15–24

using normalization 4–11

writing table create statements 11–15

Database Engine Tuning Advisor 281

database file optimization 336–337

database instances

performance management for 324–346

database file optimization 336–337

database workload 325–331

Elastic Scale 331–333

memory optimization 339–340

query plans 346

storage, IO, and cache troubleshooting 343–346

wait statistics 340–343

tempdb optimization 338–339

Database Manipulation Language (DML)

statements 196–203

database objects 1–100

changing definition of 61

columnstore indexes 70–93

dropping 61

indexes 24–50

naming 11–12, 12–13

relational database schema 2–24

views 52–69

databases. See mailbox databases

columnar 70–71

logical database model 4

tuning 32

database scalability 331–333

Database Throughput Unit (DTU) 321–322

database workload

managing, in SQL Server 325–331

Resource Governor management queries 330–331

resource pools 326–328

user-defined functions 328–329

workload groups 328

Databse Definition Language (DDL) triggers 159

Data Definition Language (DDL) 123

Data Definition Language (DDL) triggers 169–174

data distribution 266–270

data files 336

data format 107, 109–110

data input

CHECK constraints on 107–111

coordinating multiple values 110–111

data integrity

complex 160–164

constraints for 102–129

data loading

into columnstore index 89–95

Data Manipulation Language (DML) 123–125

Data Manipulation Language (DML) triggers 159–160, 169. See also triggers

data storage. See also storage architectures; See also storage requirements

datatype

choosing 108–109

conversion 144

int 107

precedence 144

data types 15–24

approximate numeric 16

binary data 17

character (string) data 17

computed columns 20–21

considerations for choosing 18–20

date and time values 16–17

dynamic data masking 20, 21–24

for clustering key 48

importance of choosing 15–16

not supported in columnstore indexes 71

other 17–18

precise numeric 16

data values 15

data warehouses

clustered columnstore indexes and 73–80

date values 16–17

DBCC SHOW_STATISTICS command 266–268

DDL. See Data Definition Language (DDL)

deadlock graphs

capture and analyze 237–240

Extended Events 240

SQL Server Profiler 239–240

deadlocks 351

remediation of 241–242

decimals 16

DEFAULT constraint 48, 65

DEFAULT constraints 102, 103–105

DEFAULT keyword 182

DEFAULT VALUES 104–105

degenerate dimensions 75

degree of parallelism (DOP) 328

delayed durability 254

delayed durable transactions 202–203

DELETE statement 63

delimiters 11, 12

deltastore structure 72

denormalization 11

design

database

adding indexes during 26–32

based on business requirements 2–4

determining data types 15–24

using normalization 4–11

writing table create statements 11–15

indexes 26–40

tables

improving, using normalization 4–11

deterministic calculations 20

deterministic functions 186–188

Developer edition 335

DFS. See Distributed File Share

dimensional formatted data warehouses

using clustered columnstore indexes on 73–80

dimension keys 74

dimensions 74

dirty reads 212

Disk Usage Summary report 353–354

distributed transactions 208–209, 211, 219

divide-by-zero errors 180–181

DML. See Data Manipulation Language (DML)

DMV. See dynamic management view

DMVs. See dynamic management views (DMVs)

doomed transactions 152

double-quotes 11

DROP command 61

DROP [objectType] IF EXISTS command 61

duplicate key values 46

durability 197, 202–203

durable memory-optimized tables 254

dynamic data masking 20, 21–24

dynamic link library (DLL) files 246, 247

dynamic management objects (DMOs) 276–281, 340–343, 347, 348, 355

dynamic management view (DMV) 45

dynamic management views (DMVs) 231–237, 256–259, 276–280, 304–305, 341–342, 345–346, 358

E

EFS. See Encrypting File System

elastic database client library 332

Elastic Scale 331–333

email() function 23

ENCRYPTION 52

Enterprise edition 334–335

error handling

@@ERROR system function for 148–149

in stored procedures 144–158

rethrowing errors 150

throwing errors 144, 145–147

THROW statement for 157

transaction control logic in 144, 151–158

TRY...CATCH construct for 149–151, 153–154, 157

ERROR_PROCEDURE() function 158

@@ERROR system function 148–149, 154

ESRA. See EdgeSync replication account (ESRA)

estimated query plans 268–270, 314–319

ETDATE() function 20

Evaluation edition 335

EVENTDATA() function 171

exclusive locks 213, 229, 236

EXECUTE AS statement 24

EXECUTE statement 142

execution statistics 256–259

explicit transactions 151, 203–204, 205–209

Express edition 334

Extended Events 283–286, 313, 346, 348, 352, 355, 358

actions 360

best practice use cases for 359

compared with SQL Trace 360

events 360

packages 360

sessions 360

targets 359–360, 360

Extended Events deadlock graph 240

external resource pools 327

Extract-Transform-Load (ETL) operations 243, 245–246

F

fact tables 74, 78

federated SQL Servers 64

file groups 336–337

file placement 336, 338

file size 338

filtered rowstore indexes 29, 84

first normal form 6–7

forced query plans 313–314

foreign key columns 28–32, 74

FOREIGN KEY constraints 28–32, 75, 102

cascading operations 113–115

limiting column to set of values using 118–119

PRIMARY KEY constraints and 111–112

relating to UNIQUE constraint 117–119

table hierarchies and 115–116

use of 110–116

forms 4

FROM clause 60, 248

FSW. See File Share Witness

FUNCTION query 182

functions

deterministic 186–188

non-deterministic 186–188

system. See system functions

user-defined. See User-Defined Functions (UDFs)

G

globally unique identifier (GUID) 18, 48

Globally Unique Identifiers (GUIDs) 128

GO 12

GROUP BY query 182

GUID. See globally unique identifier

H

hash indexes 252

Hash Match (Aggregate) operator 298–299

Hash Match (Inner Join) operator 299–302

Hash Match join operator 51

Hash Match operator 35, 37, 51, 77

heaps 25

heirarchyId data type 18

high-concurrency databases

transactions in 211–216

hygiene. See message hygiene

I

IDENTITY property 65, 104, 105, 126–128

implicit transactions 203–205, 216

included columns 41–45

INCLUDE keyword 43, 46

index create memory 340

indexed columns 41–45

indexed views 67–69

indexes 24–50, 252–253

adding during coding phase 26

adding during database design phase 26–32

bookmark lookups 30

clustered 25, 46–48, 69, 85

columnstore 70–93

clustered and non-clustered 73–87

data loading 89–95

maintenance 89–95

use cases 70–73

composite 25, 39

consolidating overlapping 281–282

covering 41–45, 57, 77

designing 26–40

common search paths 32–35

foreign key columns 28–32

once data is in tables 32–40

uniqueness constraints 27–28

filtered 29, 84

finding unused 278–279

fragmented 279–280

identifying missing 280–281

indexed vs. included columns 41–45

joins 35–37

missing 45, 46

non-clustered 25, 85

optimization of 266–284

optimizing 28

query plans 30–32, 34–36, 42, 46, 49–52

review current usage 276–279

simple 25, 39

sorts 38–41

structure 24–26

uses of 24, 31–32

using dynamic managemet objects to review 276–281

index keys 42

maximum size of 25–26

Index Seek (NonClustered) operator 294–295

In-Memory OLTP feature 242

inner data set 301

INSERT INTO clause 105

INSERT statements 248

INSTEAD OF INSERT triggers 168

INSTEAD OF triggers 160, 166–170, 176–179

INSTEAD OF UPDATE triggers 166

int data type 107

integers 16, 48

intent locks 213–214

internal resource pools 327

Internet of Things 243

interpreted SQL stored procedures 130

interpreted stored procedures 249

IO issues 341

IO troubleshooting 343–345

isolation 196–197, 201–202

isolation levels 216–229

concurrent queries based on 219–228

differences between 217–219

READ COMMITTED 218, 220–221, 229

READ_COMMITTED_SNAPSHOT 219, 227–228, 230–231

READ UNCOMMITTED 218, 221–222, 229

REPEATABLE READ 218, 222–223, 229

resource and performance impact of 228–230

SERIALIZABLE 218, 223–224, 229

SNAPSHOT 219, 224–227, 229

J

joins 35–37

K

key attributes

relationship to non-key attributes 8–11

Key Lookup (Clustered) operator 294–295

Key Lookup operator 38

key-range locks 214–215

keys 5, 6

artificial 5

natural 6

surrogate 6

L

lazy commits 202–203

linked servers 67

lock compatibility 215

lock escalation events 351

lock hierarchy 213–215

locking behavior 230–241

deadlocking 237–241

escalation behaviors 237–238

troubleshooting 231–237

log files 336. See transaction log files

logging tools 355

login triggers 159, 174–176

logon triggers 169, 174–176

log sequence numbers (LSNs) 202

long running queries 323–324

lost updates 212

Lync Online. See Skype for Business

M

maintenance

columnstore indexes 89–95

Management Data Warehouse 352–355

materialized views. See indexed views

max server memory 339

max worker threads 340

measures 75

memory cache 345–346

memory optimization 339–340

memory-optimized tables 242–255

analytics workloads 253

durability options 253–254

indexes 252–253

natively compile stored procedures and 247–252

performance optimization of 245–255

SQL Server editions supporting 245

use cases for 242–245

memory pressure 342

memory usage monitoring 350

Merge Join operator 38, 40–41

MERGE statement 248

message transport. See transport

Microsoft Azure. See Azure

Microsoft Azure Active Directory. See Azure Active Directory (Azure AD)

min memory per query 340

min server memory 339

missing indexes 45, 46

monetary values 16, 19

N

names

company 18–19

database objects 12–13

object 11–12

NAT. See network address translation (NAT)

natively compiled objects 130

natively compiled stored procedures 242

creating 247–252

execution statistics for 256–259

unsupported T-SQL constructs for 249

usage scenarios for 255

natural keys 6

Nested Loop operator 78

nested loops 35

nested transactions 206–207

NEWSEQUENTIALID() function 128

NICs. See network interface cards (NICs)

non-bulk operations

on columnstore 93–95

nonclustered B-tree indexes 252

non-clustered columnstore indexes 73, 78, 80–88

designing in conjuction with clustered 85–88

filtered 84–85

on OLTP tables 81–85

non-clustered indexes 25, 85

non-deterministic functions 186–188

non-durable memory-optimized tables 254

non-key attributes

relationship to key attributes 8–11

non-repeatable reads 212

normalization

Boyce-Codd normal form 9

defined 4

first normal form 6–7

rules

covering relationship of non-key attributes to attributes 8–11

covering shape of table 5–7

second normal form 8–10

table design using 4–11

third normal form 8, 9–11

NOT NULL columns 104

NULL columns 106

NULL expression 13

NULL values 28, 29

O

object naming 11–13

objects 3

Office Telemetry. See telemetry

OLTP. See online transaction processing

OLTP databases 28

OLTP tables

using non-clustered columnstore indexes on 81–85

ON clause 14

one-to-one cardinality 10

online transaction processing (OLTP) 1–100

operating system performance metrics 347–352

ORDER BY clause 38, 40, 52

outer data set 301

Overall Resource Consumption view 314

P

parallelism 76

parameters

input and output 135–137

stored procedures 132, 135–139

table-valued 137–139, 244

type mismatch 143–144

parameter sniffing 310

partial() function 23

partitioned views 64–67

partitioning 14, 337–338

Pascal-casing 12

PERCENT clause 248

performance

data types and 15

performance counters 344–346, 348–351

Performance Monitor 347, 348–351, 355

performance monitoring

Azure SQL Database 356–358

baseline performance metrics 347–362

DMOs for 276–280, 340–343, 347, 348, 355

Extended Events for 355, 358, 359–362

Management Data Warehouse 352–355

Performance Monitor for 347, 348–351, 355

SQL Trace for 347, 355

vs. logging 355

phantom reads 212

plan summary 308, 310, 311

platform-as-a-service. See PaaS

precise numeric data type 16

prefixes 11

PRIMARY KEY constraint 14, 27, 28, 41, 47, 65

PRIMARY KEY constraints 102, 106, 120

FOREIGN KEY constraints and 111–112

use of 125–130

Q

quantum 340

queries

concurrent 219–228

dimensional data warehouses 73–80

grouping data in 76

long running 323–324

parallelism 76

range 47

resource consuming 321–323

Resource Governor management 330–331

slow 351

statistics collection 258–259

that return large results 47

Query Menu 31

query optimizer 283

query parsing 283

Query Performance Insight 319–324

query plan operators 291–301

Clustered Index Scan operator 293–294

Hash Match (Aggregate) operator 298–299

Hash Match (Inner Join) operator 299–302

Hash Match operator 296

Index Seek (NonClustered) operator 294–295

Key Lookup (Clustered) operator 294–295

Sort operator 295–297

Table Scan operator 293

query plans 30–32, 34–36, 35–36, 42, 46, 49–52, 268–270, 283–324

Azure SQL Database 346

Azure SQL Database Performance Insight 319–324

capturing, using extended events and traces 283–292

comparing estimated and actual 314–319

estimated 268

forced 313–314

performance impacts of 351

poorly performing operators 291–301

query_post_execution_showplan 284

query_pre_execution_showplan 284

Query Statistics History report 354

Query Store 283, 346

components 304–306

enabling 319–320

properties 302–304

views 306–314

query_store_plan_forcing_failed Extended Event 313

R

RAISERROR statement 145–147

range queries 47

READ COMMITTED isolation level 218, 220–221, 229

READ_COMMITTED_SNAPSHOT isolation level 219, 227–228, 230–231

READ UNCOMMITTED isolation level 218, 221–222, 229

real data types 19

real-time analytics

using non-clustered columnstore indexes 81–85

real-time data access 244

REBUILD setting 89, 92

redundant data 8, 9

Regressed Queries view 314

relational database schema 2–24

designing

based on business requirements 2–4

determining data types 15–24

using normalization 4–11

writing table create statements 11–15

REORGANIZE setting 89, 90, 92

REPEATABLE_READ isolation level 213, 218, 222–223, 229

reporting

views for 53, 55–56

resource consuming queries 321–323

resource consumption monitoring 330–331, 359

Resource Governor 325–331

management queries 330–331

resource locks 212–216

resource pools 325, 326–328

RETURN statement 139–140

REVERT statement 24

ROLLBACK TRANSACTION statement 151, 164, 207, 209

row groups 83

in columnstore index 71

Row-Level Security feature 53

rows 5, 5–6

rowstore 24

rowstore indexes

uses of 85

rowversion data type 18

S

savepoints 157, 209–211

SAVE TRANSACTION statement 209

scalar user-defined functions 180–183

SCHEMABINDING 52

SCHEMABINDING clause 249

schema locks 214

schema modification locks 236

schemas

defined 3

designing

based on business requirements 2–4

relational database 2–24

SCOPE_IDENTITY() function 136

search conditions 35

secondary data files 336–337

secondary uniqueness criteria 105–106

second normal form 8–10

security

Row-Level Security feature 53

security audits 359

SELECT clause 183

SELECT INTO clause 248

SELECT statement 317

SELECT statements 52

self-service deployment. See user-driven client deployments

semicolons 12

semi joins 51

SEQUENCE object 65

SEQUENCE objects 128

SERIALIZABLE isolation level 213, 218, 223–224, 229

Server Activity History report 353

SERVER DDL triggers 170–172

server principal 169, 171

servers

linked 67

server-side tracing 286–289, 360

service tiers 335

session state management 244

SET command 134

SET SHOWPLAN_ALL ON statement 315

SET SHOWPLAN_TEXT ON statement 315

SET SHOWPLAN_XML ON statement 315

sharding 331–332

shared locks 229

simple indexes 25, 39

SIMPLE recovery model 338

slow queries 351

SMTP. See Single Mail Transfer Protocol (SMTP)

SNAPSHOT isolation level 219, 224–227, 229

snowflake schema 74

Sort operator 295–297

sorts 38–41

spatial data type 18

SPF. See send policy framework (SPF) records

Split-Merge service 333

sp_query_store_flush_db 305

sp_query_store_force_plan 305

sp_query_store_remove_plan 305

sp_query_store_remove_query 305

sp_query_store_reset_exec_stats 305

sp_query_store_unforce_plan 305

sp_trace_create 286

sp_trace_setevent 286

sp_trace_setfilter 286

sp_trace_setstatus 286

SQL Database. See also Azure SQL Database

DMVs for 358

Elastic Scale for 331–333

Extended Events in 358

SQL Operating System (SQLOS) Scheduler 340

SQL Server

baseline performance metrics 347–362

Enterprise Edition 67

managing database workload in 325–331

memory optimization 339–340

Standard Edition 67

SQL Server 2012

columnstar indexes and 85

SQL Server 2014

columnstar indexes and 85

SQL Server 2016

columnstar indexes and 85–86

editions 334–335

service tiers 334–335

SQL Server Agent stored procedures 273

SQL Server Integration Services (SSIS) 275

SQL Server Lock Manager 212–213

SQL Server Management Studio 352

SQL Server Profiler 286, 288–289, 290

SQL Server Profiler deadlock graph 239–240

SQL Server Resource Governor 325–331

SQL Trace 283, 286–292, 347, 351, 355, 360

sql_variant 17

Standard edition 334

star schema 73–74

statistics 266–277

accuracy of 266–273

automatic updates 271–273

data distribution and cardinality 267–271

execution. See execution statistics

maintenance tasks 273–276

wait 340–343

statistics collection queries 258–259

STATS_DATE system function 272

storage

troubleshooting 343–345

stored procedures 53, 101

CLR 130

complex business logic in 243

creating 130–158

designing, based on business requirements 131–135

error handling in 144–158

interpreted 249

interpreted SQL 130

natively compiled 130, 242, 247–252, 255–258

parameters 132

input and output 135–137

table-valued 137–139

type mismatch 143–144

return codes 139–140

returning data from 133–134

server-side tracing 286

SQL Server Agent extended 273

streamlining logic 141–144

structure of 131

transactions in 206–208

use of 131

string data 17

STRING_SPLIT() function 137–138

surrogate keys 6

surrograte keys 126–129

syntax

names 11–12

sys.database_connection_stats 358

sys.dm_db_index_physical_stats 279–280

sys.dm_db_index_usage_stats 276–278

sys.dm_db_missing_index_details 280

sys.dm_db_missing_index_groups 280

sys.dm_db_missing_index_group_stats 280

sys.dm_db_resource_stats 358

sys.dm_exec_query_stats 358

sys.dm_exec_session_wait_stats 342

sys.dm_io_virtual_file_stats 343

sys.dm_os_memory_cache_counters 345

sys.dm_os_memory_clerks 345

sys.dm_os_performance_counters 344

sys.dm_os_sys_memory 345

sys.dm_os_waiting_tasks 231, 234–235, 342

sys.dm_os_wait_stats 231, 235–236, 341–342

sys.dm_tran_locks 231, 231–234, 358

sys.event_log 358

sys.fn_trace_getinfo system function 288

sys.master_files 343

sys.query_store_plan 304

sys.query_store_query 304

sys.query_store_query_text 304

sys.query_store_runtime_stats 304

sys.query_store_runtime_stats_interval 305

sys.sp_xtp_control_proc_exec_stats 256

sys.sp_xtp_control_query_exec_stats 257–258

system functions

invalid use of, on search arguments 143

system health 359

T

table constraints 102

tables

adding constraints to 119–122

adding indexes to 32–40

ALTER TABLE statement 14–15, 27

cascading operations 113–115

columns 5, 5–6

adding 14–15

data types for 15–24

dropping 15

indexed vs. included 41–45

creating 11

designing

based on business requirements 2–4

using normalization 4–11

fact 74, 78

hierarchies 115–116

joins 35–37

keys 5, 6

memory-optimized 242–255

modifying, using views 58–64

multiple, in views 62–64

names 11–13

OLTP

using non-clustered columnstore indexes on 81–85

PRIMARY KEY constraints 27

redundancy in 9

relationship of non-key to key attributes 8–11

rows 5–6

rules covering shape of 5–7

sorts 38–41

temporary 244

virtual 62

Table Scan operator 293

table-valued parameters 137–139, 244

table-valued user-defined functions 183–186

table variables 244

tempdb 224, 242, 247, 343

tempdb optimization 338–339

temporal extensions 14

temporary tables 244

third normal form 8, 9–11

THROW statement 145–147, 155, 157, 164

timestamp 18

time values 16–17

Top Resource Consuming Queries view 308

traces 283, 286–292, 351

client-side 290–292, 360

server-side 286–289, 360

Tracked Querie view 314

@@TRANCOUNT variable 204, 206, 207, 209

transaction control logic

in stored procedures 144–158

transactions 195–216

ACID properties of 196–203

auto-commit 203

distributed 208–209, 211, 219

DLM statement results based on 196–203

explicit 203–204, 205–209

implicit 203–205, 216

in high-concurrency databases 211–216

isolation levels 216–229

nested 206–207

resource locks and 212–216

savepoints within 209–211

Transact-SQL code 2–24, 12

Transact-SQL statements

to add contraints to tables 119–122

triggers 101

AFTER 176–179

columnar data and 165–167

complex data integrity and 160–164

creating 159–179

DDL 159, 169–174

designing logic for, based on business requirements 159–169

DML 159–160, 169

INSTEAD OF 166–170, 176–179

login 159

logon 169, 174–176

running code in response to action 164–165

uses of 160

troubleshooting

IO 343–345

locking behavior 231–237

performance issues 352–355

storage 343–345

TRY...CATCH construct 149–151, 153–154, 157

T-SQL statements 346

tuple mover 72, 89

U

UDFs. See User-Defined Functions (UDFs)

uncommitable transactions 152

underscores 12

UNION ALL set operator 64

UNIQUE constraints 102, 120

FOREIGN KEY constraints relating to 117–119

use of 105–106

uniqueidentifier data type 18

uniqueness constraints

on indexes 27–28

updateable views 57–63

update locks 213

UPDATE operations

DEFAULT constraints on 104

in columnstore index 72

UPDATE statement 179

UPDATE STATISTICS statement 271, 275

Update Statistics Task dialog box 274

user accounts. See also identities

user-defined functions 50

User-Defined Functions (UDFs) 101, 159, 180–186

classifier 328–329

scalar 180–183

table-valued 183–186

user-defined resource pools 327

user identities. See identities

user input

DEFAULT constraints on 103–105

limiting with constraints 107–111

user requirements

creating views to meet 53–57

V

version store 227

VIEW_METADATA 52, 58

views 52–69

basic form of 52

designing

based on user or business requirements 53–57

updateable 57–63

editable 58–64

indexed 67–69

layers of 55

limiting what data can be added to table 61–62

modifiable 167–169

modifying data in, with multiple tables 62–64

options for 52

partitioned 64–67

that reference single table, modifying 58–61

uses 52, 53

as reporting interface 55–56

reformatting data in output 54–55

table modification 58–64

to hide data 53

virtual tables 62

W

wait statistics 340–343

wait types 235

Web edition 334

WHERE clause 62, 87, 88, 183

WIM. See Windows Imaging Format (WIM)

WITH CHECK OPTION clause 52, 61–62

WITH NATIVE_COMPILATION clause 249

WITH TIES in TOP clause 248

worker threads 340–341

workload groups 325, 328

X

XACT_ABORT() function 152, 155–156

XACT_STATE() function 152

XML data type 18

xml_deadlock_report 240

Y

ys.sp_xtp_control_query_exec_stats 256