Chapter 1 - foundations of Querying
1
a.
Lesson 1: Understanding the Foundations of
T-SQL. . . . . . . . . . . . . . . . . . . . 2
i.
Evolution of T-SQL 2
ii.
Using T-SQL in a Relational Way 5
iii.
Using Correct Terminology 10
iv.
Lesson Summary
13
·
T-SQL is based on strong mathematical
foundations. It is based on standard SQL, which in turn is based on the
relational model, which in turn is based on set theory and predicate logic.
·
It is important to understand the relational
model and apply its principals when writing T-SQL code.
·
When describing concepts in T-SQL, you should
use correct terminology because it reflects on your knowledge.
b.
Lesson 2: Understanding Logical Query Processing
. . . . . . . . . . . . . . . . . . . 14
i.
T-SQL As a Declarative English-Like Language 14
ii.
Logical Query Processing Phases 15
iii.
Lesson Summary
23
·
T-SQL was designed as a declarative language
where the instructions are provided in an English-like manner. Therefore, the
keyed-in order of the query clauses starts with the SELECT clause.
·
Logical query processing is the conceptual
interpretation of the query that defines the correct result, and unlike the
keyed-in order of the query clauses, it starts by evaluating the FROM clause.
·
Understanding logical query processing is
crucial for correct understanding of T-SQL.
Chapter 2 - Getting started with the select statement 29
a.
Lesson 1: Using the FROM and SELECT Clauses . .
. . . . . . . . . . . . . . . . . . . . .30
i.
The FROM Clause
30
ii.
The SELECT Clause 31
iii.
Delimiting Identifiers 34
iv.
Lesson Summary
36
·
The FROM clause is the first clause to be
logically processed in a SELECT query. In this clause, you indicate the tables
you want to query and table operators. You can alias tables in the FROM clause
with your chosen names and then use the table alias as a prefix to attribute
names.
·
With the SELECT clause, you can indicate
expressions that define the result attributes. You can assign your own aliases
to the result attributes, and in this way, create a relational result. If
duplicates are possible in the result, you can eliminate those by specifying
the DISTINCT clause.
·
If you use regular identifiers as object and
attribute names, using delimiters is optional. If you use irregular
identifiers, delimiters are required.
b.
Lesson 2: Working with Data Types and
i.
Built-in Functions . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
ii.
Choosing the Appropriate Data Type 37
iii.
Choosing a Data Type for Keys 41
iv.
Date and Time Functions 44
v.
Character Functions 46
vi.
CASE Expression and Related Functions 49
vii.
Lesson Summary
55
·
Your choices of data types for your attributes
will have a dramatic effect on the functionality and performance of the T-SQL
code that interacts with the data—even more so for attributes used as keys.
Therefore, much care and consideration should be taken when choosing types.
·
T-SQL supports a number of functions that you
can use to apply manipulation of date and time data, character string data, and
other types of data. Remember that T-SQL was mainly designed to handle data
manipulation, and not formatting and similar needs. Therefore, in those areas,
you will typically find only fairly basic support. Such tasks are usually best
handled in the client.
·
T-SQL provides the CASE expression that allows
you to return a value based on conditional logic, in addition to a number of
functions that can be considered abbreviations of CASE.
Chapter 3 - filtering and sorting Data
61
a.
Lesson 1: Filtering Data with Predicates . . . .
. . . . . . . . . . . . . . . . . . . . . . . . .62
i.
Predicates, Three-Valued Logic, and Search
Arguments 62
ii.
Combining Predicates 66
iii.
Filtering Character Data 68
iv.
Filtering Date and Time Data 70
v.
Lesson Summary
73
·
With the WHERE clause, you can filter data by
using predicates. Predicates in T-SQL use three-valued logic. The WHERE clause
returns cases where the predicate evaluates to true and discards the rest.
·
Filtering data by using the WHERE clause helps
reduce network traffic and can potentially enable using indexes to minimize
I/O. It is important to try and phrase your predicates as search arguments to
enable efficient use of indexes.
·
When filtering different types of data, like
character and date and time data, it is important to be familiar with best
practices that will ensure that you write both correct and efficient code.
b.
Lesson 2: Sorting Data . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
i.
Understanding When Order Is Guaranteed 75
ii.
Using the ORDER BY Clause to Sort Data 76
iii.
Lesson Summary
83
·
Queries normally return a relational result
where ordering isn’t guaranteed. If you need to guarantee presentation
ordering, you need to add an ORDER BY clause to your query.
·
With the ORDER BY clause, you can specify a list
of expressions for primary ordering, secondary ordering, and so on. With each
expression, you can indicate ASC or DESC for ascending or descending ordering,
with ascending being the default.
·
Even when an ORDER BY clause is specified, the
result could still have nondeterministic ordering. For deterministic ordering,
the ORDER BY list must be unique.
·
You can use ordinal positions of expressions
from the SELECT list in the ORDER BY clause, but this is considered a bad
practice.
·
You can sort by elements that do not appear in
the SELECT list unless the DISTINCT clause is also specified.
·
Because the ORDER BY clause is conceptually
evaluated after the SELECT clause, you can refer to aliases assigned in the
SELECT clause within the ORDER BY clause.
·
For sorting purposes, SQL Server considers NULLs
as being lower than non-NULL marks and equal to each other. This means that
when ascending ordering is used, they sort together before non-NULL marks
c.
Lesson 3: Filtering Data with TOP and
OFFSET-FETCH . . . . . . . . . . . . . . . . .84
i.
Filtering Data with TOP 84
ii.
Filtering Data with OFFSET-FETCH 88
iii.
Lesson Summary
93
·
With the TOP and OFFSET-FETCH options, you can
filter data based on a specified number of rows and ordering.
·
The ORDER BY clause that is normally used in the
query for presentation ordering is also used by TOP and OFFSET FETCH to
indicate which rows to filter.
·
The TOP option is a proprietary T-SQL feature
that you can use to indicate a number or a percent of rows to filter.
·
You can make a TOP query deterministic in two
ways: one is by using the WITH TIES option to return all ties, and the other is
by using unique ordering to break ties.
·
The OFFSET-FETCH option is a standard option
similar to TOP, supported by SQL Server 2012. Unlike TOP, it allows you to
specify how many rows to skip before indicating how many rows to filter. As
such, it can be used for
ad-hoc paging purposes.
·
Both TOP and OFFSET-FETCH support expressions as
inputs and not just constants.
Chapter 4 - combining sets 101
a.
Lesson 1: Using Joins . . . . . . . . .. . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .102
i.
Cross Joins
102
ii.
Inner Joins
105
iii.
Outer Joins
108
iv.
Multi-Join Queries 112
v.
Lesson Summary
116
·
Cross joins return a Cartesian product of the
rows from both sides.
·
Inner joins match rows based on a predicate and
return only matches.
·
Outer joins match rows based on a predicate and
return both matches and non-matches from the tables marked as preserved.
·
Multi-join queries involve multiple joins. They
can have a mix of different join types.
·
You can control the logical join ordering by
using parentheses or by repositioning the ON clauses.
·
Note the difference between ON and WHERE - the
matching predicate (ON) and the filter predicate (WHERE).
b.
Lesson 2: Using Subqueries, Table Expressions,
and the APPLY
i.
Operator . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117
ii.
Subqueries
118
iii.
Table Expressions 121
iv.
APPLY 128
v.
Lesson Summary
135
·
With subqueries, you can nest queries within
queries. You can use self-contained subqueries as well as correlated ones. You
can use subqueries that return single-valued results, multi-valued results, and
table-valued results.
·
T-SQL supports four kinds of table expressions:
o
Derived tables
o
Common table expressions (CTEs)
o
Views
o
Inline table-valued functions
Derived tables and CTEs are types of table expressions that are visible
only in the scope of the statement that defined them. Views and inline
table-valued functions are reusable table expressions whose definitions are
stored as objects in the database. Views do not support input parameters,
whereas inline table-valued functions do.
·
The APPLY operator operates on two table expressions as input. It applies the right table expression
to each row from the left side. The inner query in the right table
expression can be correlated to elements from the left table. The APPLY
operator has two versions; the CROSS APPLY version doesn’t return left rows
that get an empty set back from the right side. The OUTER APPLY operator
preserves the left side, and therefore, does return left rows when the right
side returns an empty set. NULLs are used as placeholders in the attributes
from the right side in the outer rows.
c.
Lesson 3: Using Set Operators . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . .136
i.
UNION and UNION ALL 137
ii.
INTERSECT
139
iii.
EXCEPT
140
iv.
Lesson Summary
142
·
Set operators compare complete rows between the
result sets of two queries.
·
The UNION operator unifies the input sets,
returning distinct rows.
·
The UNION ALL operator unifies the inputs
without eliminating duplicates.
·
The INTERSECT operator returns only rows that
appear in both input sets, returning distinct rows.
·
The EXCEPT operator returns the rows that appear
in the first set but not the second, returning distinct rows.
Chapter 5 Grouping and Windowing 149
a.
Lesson 1: Writing Grouped Queries . . . . . . .
. . . . . . . . . . . . . . . . .. . . . . . . .150
i.
Working with a Single Grouping Set 150
ii.
Working with Multiple Grouping Sets 155
iii.
Lesson Summary
161
·
With T-SQL, you can group your data and perform
data analysis operations against the groups.
·
You can apply aggregate functions to the groups,
such as COUNT, SUM, AVG, MIN, and MAX.
·
Traditional grouped queries define only one
grouping set.
·
You can use newer features in the language to
define multiple grouping sets in one query by using the GROUPING SETS, CUBE,
and ROLLUP clauses.
b.
Lesson 2: Pivoting and Unpivoting Data . . . . .
. . . . . . . . . . . . . . . . . . . . . . .163
i.
Pivoting Data
163
ii.
Unpivoting Data
166
iii.
Lesson Summary
171
·
Pivoting is a special form of grouping and
aggregating data where you rotate data from a state of rows to a state of
columns.
·
When you pivot data, you need to identify three
things: the grouping element (the data on rows), spreading element (the data on
columns), and aggregation element (the data in the cell).
USE
TSQL2012;
IF OBJECT_ID('Sales.FreightTotals')
IS NOT NULL
DROP TABLE Sales.FreightTotals;
GO
WITH
PivotData AS
(
SELECT
custid ,
-- grouping column
shipperid,
-- spreading column
freight --
aggregation column
FROM
Sales.Orders
)
SELECT *
INTO
Sales.FreightTotals
FROM
PivotData
PIVOT( SUM(freight) FOR shipperid IN ([1],[2],[3]) ) AS P;
SELECT * FROM Sales.FreightTotals;
custid
|
1
|
2
|
3
|
1
|
95.03
|
61.02
|
69.53
|
2
|
43.9
|
NULL
|
53.52
|
3
|
63.09
|
116.56
|
88.87
|
4
|
41.95
|
358.54
|
71.46
|
5
|
189.44
|
1074.51
|
295.57
|
6
|
0.15
|
126.19
|
41.92
|
(89
rows)
·
T-SQL supports a native table operator called
PIVOT that you can use to pivot the data from the input table.
·
Unpivoting rotates data from a state of columns
to a state of rows.
·
To unpivot data, you need to identify three
things: the source columns that you need to unpivot (custid), the target names
column (shipperid), and the target values column (freight).
SELECT custid,
shipperid, freight
FROM Sales.FreightTotals
UNPIVOT( freight FOR
shipperid IN([1],[2],[3]) ) AS U;
custid
|
shipperid
|
freight
|
1
|
1
|
95.03
|
1
|
2
|
61.02
|
1
|
3
|
69.53
|
2
|
1
|
43.9
|
2
|
3
|
53.52
|
3
|
1
|
63.09
|
3
|
2
|
116.56
|
3
|
3
|
88.87
|
4
|
1
|
41.95
|
4
|
2
|
358.54
|
(239
rows)
·
T-SQL supports a native operator called UNPIVOT
that you can use to unpivot data from the input table.
c.
Lesson 3: Using Window Functions . . . . . . . .
. . . . . . . . . . .. . . . . . . . . . . . .172
i.
Window Aggregate Functions 172
ii.
Window Ranking Functions 176
iii.
Window Offset Functions 178
iv.
Lesson Summary
183
·
Window functions perform data analysis
computations. They operate on a set of rows defined for each underlying row by
using a clause called OVER.
·
Unlike grouped queries, which hide the detail
rows and return only one row per group, windowed queries do not hide the
detail. They return a row per each row in the underlying query, and allow
mixing detail elements and window functions in the same expressions.
·
T-SQL supports window aggregate, ranking, and
offset functions. All window functions support window partition and window
order clauses. Aggregate window functions, in addition to FIRST_VALUE and
LAST_VALUE, also support a window frame clause.
Chapter 6 - Querying full-text Data
191
a.
Lesson 1: Creating Full-Text Catalogs and
Indexes . . . . . . . . . . . . . . . . . . .192
i.
Full-Text Search Components 192
ii.
Creating and Managing Full-Text Catalogs and
Indexes 194
iii.
Lesson Summary
201
·
You can create full-text catalogs and indexes by
using SQL Server Full-Text Search and Sematinc Search.
·
You can improve full-text searches by adding
stopwords to stoplists, enhancing a thesaurus, and enabling a search over
document properties.
·
You can use the sys.dm_fts_parser dynamic
management view to check how Full-Text Search breaks your documents into words,
creates inflectional forms of words, and more.
b.
Lesson 2: Using the CONTAINS and FREETEXT
Predicates . . . . . . . . . . . .202
i.
The CONTAINS Predicate 202
ii.
The FREETEXT Predicate 204
iii.
Lesson Summary
208
·
You can use the CONTAINS predicate for selective
searches.
·
The FREETEXT predicate can be used for more
general searches.
c.
Lesson 3: Using the Full-Text and Semantic
Search
i.
Table-Valued Functions . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . .209
ii.
Using the Full-Text Search Functions 209
iii.
Using the Semantic Search Functions 210
iv.
Lesson Summary
214
·
Full-text functions are useful for ranking
results.
·
Semantic similarity functions give you a lot of
insight into the documents. You can find key phrases and compare documents.
Chapter 7 - Querying and Managing XML Data
221
a.
Lesson 1: Returning Results As XML with FOR XML
. . . . . . . . . . . . . . . . . .222
i.
Introduction to XML 222
ii.
Producing XML from Relational Data 226
iii.
Shredding XML to Tables 230
iv.
Lesson Summary
234
·
You can use the FOR XML clause of the SELECT
T-SQL statement to produce XML.
·
Use the OPENXML function to shred XML to tables.
b.
Lesson 2: Querying XML Data with XQuery . . . .
. . . . . . . . . . . . . . . . . . . . .235
i.
XQuery Basics
236
ii.
Navigation
240
iii.
FLWOR Expressions 243
iv.
Lesson Summary
248
·
You can use the XQuery language inside T-SQL
queries to query XML data.
·
XQuery supports its own data types and
functions.
·
You use XPath expressions to navigate through an
XML instance.
·
The real power of XQuery is in the FLWOR
expressions （FOR, LET, WHERE, ORDER BY,
RETURN）.
c.
Lesson 3: Using the XML Data Type . . . . . .
. . . . . . . . . . . . . . . . . . . .
. . . . .249
i.
When to Use the XML Data Type 250
ii.
XML Data Type Methods 250
iii.
Using the XML Data Type for Dynamic Schema 252
iv.
Lesson Summary
259
·
The XML data type is useful for many scenarios
inside a relational database.
·
You can validate XML instances against a schema
collection.
·
You can work with XML data through XML data type
methods.
Chapter 8 - creating tables and enforcing Data integrity 265
a.
Lesson 1: Creating and Altering Tables . . . . .
. . . . . . . . . . . . . . . . . . . . . . . .265
i.
Introduction
266
ii.
Creating a Table
267
iii.
Altering a Table
276
iv.
Choosing Table Indexes 276
v.
Lesson Summary
280
·
Creating a table involves specifying a table
schema as a namespace or container for the table.
·
Name tables and columns carefully and
descriptively.
·
Choose the most efficient and accurate data
types for columns.
·
Choose the appropriate remaining properties of
columns, such as the identity property and whether a column should allow NULLs.
·
You can specify whether a table should be
compressed when creating the table.
·
You can use ALTER TABLE to change most
properties of columns after a table has been created.
b.
Lesson 2: Enforcing Data Integrity . . . . . . .
. . . . . . .. . . . . . . . . . . . . . . . . . .281
i.
Using Constraints 281
ii.
Primary Key Constraints 282
iii.
Unique Constraints 283
iv.
Foreign Key Constraints 285
v.
Check Constraints 286
vi.
Default Constraints 288
vii.
Lesson Summary
292
·
To help preserve data integrity in database tables,
you can declare constraints that persist in the database.
·
Constraints ensure that data entered into tables
has to obey more complex rules than those defined for data types and
nullability.
·
Table constraints include primary key and unique
constraints, which SQL Server enforces using a unique index. They also include
foreign key constraints, which ensures that only data validated from another
lookup table is allowed in the original table. And they include check and
default constraints, which apply to columns.
Chapter 9 - Designing and creating views, inline functions, and
synonyms 299
a.
Lesson 1: Designing and Implementing Views and
Inline Functions . . . .300
i.
Introduction
300
ii.
Views 300
iii.
Inline Functions
307
iv.
Lesson Summary
313
·
Views are stored T-SQL SELECT statements that
can be treated as though they were tables.
·
Normally, a view consists of only one SELECT
statement, but you can work around this by combining SELECT statements with
compatible results using UNION or UNION ALL.
·
Views can
reference multiple tables and simplify complex joins for users. By default,
views do not contain any data. Creating a unique clustered index on a view
results in an indexed view that materializes data.
·
When you select from a view, SQL Server takes
your outer SELECT statement and combines it with the SELECT statement of the
view definition. SQL Server then executes the combined SELECT statement.
·
You can modify data through a view, but only one
table at a time, and only columns of certain types.
·
You can add WITH CHECK OPTION to a view to
prevent any updates through the view that would cause some rows to get values
no longer satisfying a WHERE clause of the view.
·
Views can refer to tables or views in other
databases and in other servers via linked servers.
·
Special views called partitioned views can be
created if a number of conditions are satisfied, and SQL Server routes suitable
queries and updates to the correct partition of the view.
·
Inline functions can be used to simulate
parameterized views. T-SQL views cannot take parameters. However, an inline
table-valued function can return the same data as a view and can accept
parameters that can filter the results.
b.
Lesson 2: Using Synonyms . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . .315
i.
Creating a Synonym 315
ii.
Comparing Synonyms with Other Database
Objects 318
iii.
Lesson Summary
322
·
A synonym is a name that refers to another
database object such as a table, view, function, or stored procedure.
·
No T-SQL code or any data is stored with a
synonym. Only the object referenced is stored with a synonym.
·
Synonyms are scoped to a database, and therefore
are in the same namespace as the objects they refer to. Consequently, you
cannot name a synonym the same as any other database object.
·
Synonym chaining is not allowed; a synonym
cannot refer to another synonym.
·
Synonyms do not expose any metadata of the
objects they reference.
·
Synonyms can be used to provide an abstraction
layer to the user by presenting different names for database objects.
·
You can modify data through a synonym, but you
cannot alter the underlying object.
·
To change a synonym, you must drop and recreate
it.
Chapter 10 - inserting, updating, and Deleting Data 329
a.
Lesson 1: Inserting Data . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . .330
i.
Sample Data
330
ii.
INSERT VALUES
331
iii.
INSERT SELECT
333
iv.
INSERT EXEC
334
v.
SELECT INTO
335
vi.
Lesson Summary
340
·
T-SQL supports different statements that insert
data into tables in your database. Those are INSERT VALUES, INSERT SELECT,
INSERT EXEC, SELECT INTO, and others.
·
With the INSERT VALUES statement, you can insert
one or more rows based on value expressions into the target table.
·
With the INSERT SELECT statement, you can insert
the result of a query into the target table.
·
You can use the INSERT EXEC statement to insert
the result of queries in a dynamic batch or a stored procedure into the target
table.
·
With the statements INSERT VALUES, INSERT
SELECT, and INSERT EXEC, you can omit columns that get their values
automatically. A column can get its value automatically if it has a default
constraint associated with it, or an IDENTITY property, or if it allows NULLs.
·
The SELECT INTO statement creates a target table
based on the definition of the data in the source query, and inserts the result
of the query into the target table.
·
It is considered a best practice in INSERT
statements to specify the target column names in order to remove the dependency
on column order in the target table definition.
b.
Lesson 2: Updating Data . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
. . . . . . . .341
i.
Sample Data
341
ii.
UPDATE Statement
342
iii.
UPDATE Based on Join 344
iv.
Nondeterministic UPDATE 346
v.
UPDATE and Table Expressions 348
vi.
UPDATE Based on a Variable 350
vii.
UPDATE All-at-Once 351
viii.
Lesson Summary
354
·
T-SQL supports the standard UPDATE statement as
well as a few extensions to the standard.
·
You can modify data in one table based on data
in another table by using an UPDATE based on joins. Remember though that if
multiple source rows match one target row, the update won’t fail; instead, it
will be nondeterministic. You should generally avoid such updates.
·
T-SQL supports updating data by using table
expressions. This capability is handy when you want to be able to see the
result of the query before you actually update the data. This capability is
also handy when you want to modify rows with expressions that are normally
disallowed in the SET clause, like window functions.
·
If you want to modify a row and query the result
of the modification, you can use a specialized UPDATE statement with a variable
that can do this with one visit to the row.
c.
Lesson 3: Deleting Data . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . .356
i.
Sample Data
356
ii.
DELETE Statement
357
iii.
TRUNCATE Statement 358
iv.
DELETE Based on a Join 359
v.
DELETE Using Table Expressions 360
vi.
Lesson Summary
362
·
With the DELETE statement, you can delete rows
from a table, and optionally limit the rows to delete by using a filter based
on a predicate. You can also limit the rows to delete using the TOP filter, but
then you cannot control which rows get chosen.
·
With the TRUNCATE statement, you can delete all
rows from the target table. This statement doesn’t support a filter. The
benefit of TRUNCATE over DELETE is that the former uses optimized logging, and
therefore tends to be much faster than the latter. However, TRUNCATE has more
restrictions than DELETE and requires stronger permissions.
·
T-SQL supports a DELETE syntax based on joins,
enabling you to delete rows from one table based on information in related rows
in other tables.
·
T-SQL also supports deleting rows through table
expressions like CTEs and derived tables.
Chapter 11 - Other Data Modification Aspects 369
a.
Lesson 1: Using the Sequence Object and IDENTITY
Column Property .370
i.
Using the IDENTITY Column Property 370
ii.
Using the Sequence Object 374
iii.
Lesson Summary
381
·
SQL Server provides two features to help you
generate a sequence of keys: the IDENTITY column property and the sequence
object.
·
The IDENTITY column property is defined with a
seed and an increment. When you insert a new row into the target table, you
don’t specify a value for the IDENTITY column; instead, SQL Server generates it
for you automatically.
·
To get the newly generated identity value, you
can query the functions SCOPE_IDENTITY, @@IDENTITY, and IDENT_CURRENT. The
first returns the last identity value generated by your session and scope. The
second returns the last identity value generated by your session regardless of
your scope. The third returns the last identity value generated in the input
table regardless of the session.
·
The sequence object is an independent object in
the database. It is not tied to a specific column in a specific table.
·
The sequence object supports defining the start
value, increment value, minimum and maximum supported values, cycling, and
caching.
·
You use the NEXT VALUE FOR function to request a
new value from the sequence. You can use this function in INSERT and UPDATE
statements, DEFAULT constraints, and assignments to variables.
·
The sequence object circumvents many of the
restrictions of the IDENTITY property.
b.
Lesson 2: Merging Data . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
. . . . . . . .382
i.
Using the MERGE Statement 383
ii.
Lesson Summary
392
·
With the MERGE statement, you can merge data
from a source table or table expression into a target table.
·
You specify the target table in the MERGE INTO
clause and the source table in the USING clause. The USING clause is designed
similar to the FROM clause in a SELECT statement, meaning that you can use
table operators, table expressions, table functions, and so on.
·
You specify a MERGE predicate in the ON clause
that defines whether a source row is matched by a target row and whether a
target row is matched by a source row.
·
Remember that the ON clause is not used to
filter data; instead, it is used only to determine matches and nonmatches, and
accordingly, to determine which action to take against the target.
·
You define different WHEN clauses that determine
which action to take against the target depending on the outcome of the
predicate. You can define actions to take when a source row is matched by a
target row, when a source row is not matched by a target row, and when a target
row is not matched by a source row.
c.
Lesson 3: Using the OUTPUT Option . . . . . . .
. . . . . . . . . . . . . . . . . . . .
. . .394
i.
Working with the OUTPUT Clause 394
ii.
INSERT with OUTPUT 395
iii.
DELETE with OUTPUT 396
iv.
UPDATE with OUTPUT 397
v.
MERGE with OUTPUT 397
vi.
Composable DML
399
vii.
Lesson Summary
403
·
With the OUTPUT clause, you can return
information from modified rows in modification statements.
·
The OUTPUT clause is designed like the SELECT
clause, allowing you to form expressions and assign the result columns with
column aliases.
UPDATE Sales.MyOrders
SET orderdate = DATEADD(day, 1, orderdate)
OUTPUT inserted.orderid ,
deleted.orderdate
AS old_orderdate ,
inserted.orderdate AS
neworderdate
WHERE empid = 7;
·
The result of the OUTPUT clause can be sent back
to the caller as a result set from a query or stored in a target table by using
the INTO clause.
·
When you refer to columns from the modified
rows, you prefix the column names with the keyword inserted for inserted rows
and deleted for deleted rows.
·
In a MERGE statement, you can use the $action
function to return a string that represents the action that affected the target
row.
·
Use the composable DML feature to filter output
rows that you want to store in a target table.
o
As an example of composable DML, consider the
previous MERGE statement. Suppose that you need to capture only the rows
affected by an INSERT action in a table variable for further processing. You
can achieve this by using the following code.
DECLARE @InsertedOrders AS TABLE
(
orderid INT NOT NULL PRIMARY KEY ,
custid INT NOT NULL ,
empid INT NOT NULL ,
orderdate DATE NOT NULL
);
INSERT INTO @InsertedOrders
( orderid ,
custid ,
empid ,
orderdate
)
SELECT orderid ,
custid ,
empid ,
orderdate
FROM ( MERGE INTO Sales.MyOrders AS TGT
USING ( VALUES
( 1 , 70 , 1 , '20061218'),
( 2 , 70 , 7 , '20070429'),
( 3 , 70 , 7 , '20070820'),
( 4 , 70 , 3 , '20080114'),
( 5 , 70 , 1 , '20080226'),
( 6 , 70 , 2 , '20080410')
) AS SRC ( orderid, custid, empid, orderdate )
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid
<> SRC.empid
OR TGT.orderdate
<> SRC.orderdate
) THEN
UPDATE SET TGT.custid = SRC.custid ,
TGT.empid =
SRC.empid ,
TGT.orderdate =
SRC.orderdate
WHEN NOT MATCHED THEN
INSERT
VALUES ( SRC.orderid ,
SRC.custid ,
SRC.empid ,
SRC.orderdate
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
$action AS
the_action ,
inserted.*
) AS D
WHERE the_action = 'INSERT';
SELECT *
FROM @InsertedOrders;
orderid custid empid orderdate
1 70 1 2006-12-18
2 70 7 2007-04-29
3 70 7 2007-08-20
4 70 3 2008-01-14
5 70 1 2008-02-26
6 70 2 2008-04-10
Chapter 12 - implementing transactions, error handling, and Dynamic SQL 411
a.
Lesson 1: Managing Transactions and Concurrency
. . . . . . . . . . . . . . . . . .412
i.
Understanding Transactions 412
ii.
Types of Transactions 415
iii.
Basic Locking
422
iv.
Transaction Isolation Levels 426
v.
Lesson Summary
434
·
All SQL Server data changes occur in the context
of a transaction. Executing a ROLLBACK command at any level in the transaction
immediately rolls back the entire transaction.
·
Every COMMIT statement reduces the value of
@@TRANCOUNT by 1, and only the outermost COMMIT statement commits the entire
nested transaction.
·
SQL Server uses locking to enforce the isolation
of transactions.
·
A deadlock can result between two or more
sessions if each session has acquired incompatible locks that the other session
needs to finish its statement. When SQL Server sees a deadlock, it chooses one
of the sessions and terminates the batch.
·
SQL Server enforces the isolation level ACID
property with varying degrees of strictness.
·
The READ COMMITTED isolation level is the
default isolation level for on-premise SQL Server.
·
The READ COMMITTED SNAPSHOT isolation option
(RCSI) of the default isolation level allows read requests to access previously
committed versions of exclusively locked data. This can greatly reduce blocking
and deadlocking. RCSI is the default isolation level in Windows Azure SQL
Database.
·
The READ UNCOMMITTED isolation level allows a
session to read uncommitted data, known as “dirty reads.”
b.
Lesson 2: Implementing Error Handling . . . . .
. . . . . . . . . . . . . . . . . . . . . . .435
i.
Detecting and Raising Errors 435
ii.
Handling Errors After Detection 440
iii.
Lesson Summary
449
·
SQL Server 2012 uses both RAISERROR and the
THROW command to generate errors.
·
You can query the @@ERROR system function to
determine whether an error has occurred and what the error number is.
·
You can use the SET XACT_ABORT ON command to
force a failure of a transaction and abort a batch when an error occurs.
o SET XACT_ABORT {ON | OFF} has
appeared since SQL Server 2000, at least.
o
When SET XACT_ABORT
is ON, if a Transact-SQL statement raises a run-time error, the entire
transaction is terminated and rolled back.
o
When SET XACT_ABORT
is OFF, in some cases only the Transact-SQL statement that raised the error is
rolled back and the transaction continues processing. Depending upon the
severity of the error, the entire transaction may be rolled back even when SET
XACT_ABORT is OFF. OFF is the default setting.
·
Unstructured error handling does not provide a
single place in your code to handle errors.
·
The TRY/CATCH block provides each batch of T-SQL
code with a CATCH block in which to handle errors.
·
The THROW command can be used to re-throw
errors.
·
There is a complete set of error functions to
capture information about errors.
c.
Lesson 3: Using Dynamic SQL . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
. . . . . .450
i.
Dynamic SQL Overview 451
ii.
SQL Injection
456
iii.
Using sp_executesql 457
iv.
Lesson Summary
462
·
Dynamic SQL can be used to generate and execute
T-SQL code in cases where the T-SQL statements must be constructed at run time.
·
SQL injection refers to the potential for
applications to accept input that injects unwanted code that dynamic SQL
executes.
·
The sp_executesql stored procedure can be used
to help prevent SQL injection by forcing the relevant parts of dynamic SQL to
be parameterized.
Chapter 13 - using tools to analyze Query performance 469
a.
Lesson 1: Getting Started with Query
Optimization . . . . . . . . . . . . . . . . . .470
i.
Query Optimization Problems and the Query
Optimizer
ii.
SQL Server Extended Events, SQL Trace, and SQL Server
Profiler
iii.
Lesson Summary
480
·
The Query Optimizer generates candidate
execution plans and evaluates them.
·
SQL Server provides many tools that help you
analyze your queries, including Extended Events, SQL Trace, and SQL Server
Profiler.
·
Extended Events is a more lightweight monitoring
mechanism than SQL Trace.
·
SQL Server Profiler provides you with the UI to
access SQL Trace.
b.
Lesson 2: Using SET Session Options and
Analyzing Query Plans . . . .. . . .481
i.
SET Session Options
ii.
Execution Plans
iii.
Lesson Summary
490
·
You can use SET session options to analyze your
queries.
·
You can use graphical execution plans to get
detailed information about how SQL Server executes a query.
·
You can display an estimated or an actual
execution plan.
·
In a graphical execution plan, you can get
detailed properties of each operator.
c.
Lesson 3: Using Dynamic Management Objects . . .
. . . . . . . . . . . . . . . . . .491
i.
Introduction to Dynamic Management Objects
ii.
The Most Important DMOs for Query Tuning
iii.
Lesson Summary
496
·
Dynamic management objects help you to
immediately gather the information collected by SQL Server.
·
For query analysis, use SQLOS,
execution-related, and index-related DMOs.
·
Not only do index-related DMOs provide useful
information about index usage, they also provide information about missing
indexes.
Chapter 14 - Designing and implementing t-SQL routines 501
a.
Lesson 1: Designing and Implementing Stored
Procedures . . . . . . . . . . .502
i.
Understanding Stored Procedures
ii.
Executing Stored Procedures
iii.
Branching Logic
iv.
Developing Stored Procedures
v.
Lesson Summary
521
·
T-SQL stored procedures are modules of T-SQL
code that are stored in a database and can be executed by using the T-SQL
EXECUTE command.
·
Stored procedures can be used to encapsulate
code on the server side, thereby reducing network overhead from applications;
to present a data access layer to applications; and to perform maintenance and
administrative tasks.
·
Stored procedures can be defined by using
parameters. Input parameters are sent to the stored procedure for the
procedure's internal use. Output parameters can be used to return information
to the caller of the procedure.
·
Within the stored procedure, parameters are
defined by using the same syntax as T-SQL variables, and they can be referenced
and manipulated within the procedure just like variables.
·
Every stored procedure consists of only one
batch of T-SQL code.
·
Stored procedures can call other stored
procedures.
·
Whenever a RETURN is executed, execution of the
stored procedure ends and control returns to the caller.
·
Stored procedures can return more than one
result set to the caller.
b.
Lesson 2: Implementing Triggers . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
. . . . .522
i.
DML Triggers
ii.
AFTER Triggers
iii.
INSTEAD OF Triggers
iv.
DML Trigger Functions
v.
Lesson Summary
531
·
A DML trigger is a T-SQL batch of code, similar
to a stored procedure, that is associated with a table and sometimes a view.
You can use DML triggers for auditing, enforcing complex integrity rules, and
more.
·
Triggers execute when a particular DML event
such as an INSERT, UPDATE, or DELETE occurs.
·
SQL Server supports two kinds of DML triggers: AFTER triggers
and INSTEAD OF triggers. Both types of DML triggers execute as part of the
transaction associated with the INSERT, UPDATE, or DELETE statement.
·
In the T-SQL code for both types of DML
triggers, you can access tables that are named inserted and deleted. These
tables contain the rows that were affected by the modification that caused the
trigger to fire.
c.
Lesson 3: Implementing User-Defined Functions .
. . . . . . . . . . . . . . . . . . .533
i.
Understanding User-Defined Functions
ii.
Scalar UDFs
iii.
Table-Valued UDFs
iv.
Limitations on UDFs
v.
UDF Options
vi.
UDF Performance Considerations
vii.
Lesson Summary
541
·
User-defined functions (UDFs) encapsulate
reusable T-SQL code and return a scalar value or a table to the caller.
·
Like stored procedures, UDFs can accept
parameters, and the parameters can be accessed inside the function as
variables. Unlike stored procedures, UDFs are embedded in T-SQL statements, and
they execute as part of a T-SQL command. UDFs cannot be executed by using the
EXECUTE command.
·
UDFs access SQL Server data, but they cannot
perform any DDL—that is, they cannot make
modifications to tables, indexes, or other objects, or change the data tables
by using DML.
·
There are two major types of UDFs: scalar and
table-valued. The scalar UDF returns a single value back to the caller and can
be invoked in numerous places, including a SELECT list and a WHERE clause. The
table-valued function returns a table and can appear in a FROM clause. Both
scalar UDFs and table-valued UDFs can consist of a single line or of multiple
lines of T-SQL code.
Chapter 15 - implementing indexes and statistics 549
a.
Lesson 1: Implementing Indexes . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . .550
i.
Heaps and Balanced Trees 550
ii.
Implementing Nonclustered Indexes 564
iii.
Implementing Indexed Views 568
iv.
Lesson Summary
573
·
You can store a table as a heap or as a balanced
tree. If the table is stored as a balanced tree, it is clustered; this is also
known as a clustered index.
·
You can create a nonclustered index on a heap or
on a clustered table.
·
You can also index a view.
b.
Lesson 2: Using Search Arguments . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . .573
i.
Supporting Queries with Indexes 574
ii.
Search Arguments
578
iii.
Lesson Summary
584
·
You support different parts of queries with
indexes.
·
Consider supporting the WHERE, JOIN, GROUP BY,
ORDER BY, and SELECT clauses of queries with appropriate indexes.
·
You write appropriate search arguments by not
including key columns of indexes in expressions.
c.
Lesson 3: Understanding Statistics . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . .585
i.
Auto-Created Statistics 585
ii.
Manually Maintaining Statistics 589
iii.
Lesson Summary
592
·
The SQL Server Query Optimizer uses statistics
to determine the cardinality of a query.
·
Besides leaving it to SQL Server to maintain
statistics automatically, you can also maintain statistics manually.
Chapter 16 - understanding cursors, sets, and temporary tables 599
a.
Lesson 1: Evaluating the Use of Cursor/Iterative
Solutions vs.
i.
Set-Based Solutions . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . .600
ii.
The Meaning of “Set-Based” 600
iii.
Iterations for Operations That Must Be Done Per
Row 601
iv.
Cursor vs. Set-Based Solutions for Data
Manipulation Tasks 604
v.
Lesson Summary
610
·
You can use one of two main approaches to handle
querying tasks; one is using set-based solutions and the other is using
iterative solutions.
·
Set-based solutions essentially use SQL queries
that follow principles from the relational model. They interact with the input
tables (sets) as a whole, as opposed to interacting with one row at a time.
They also don’t assume that the data will be consumed or returned in a
particular order.
·
Some tasks have to be handled with iterative
solutions, such as management tasks that need to be handled per object or a
stored procedure that you need to execute per row in a table.
·
Regarding querying tasks, generally it is
recommended to use set-based solutions as your default choice, and reserve the
use of iterative solutions for exceptional cases.
b.
Lesson 2: Using Temporary Tables vs. Table
Variables . . . . . . . . . . . . . . . .611
i.
Scope 612
ii.
DDL and Indexes
613
iii.
Physical Representation in tempdb 616
iv.
Transactions
617
v.
Statistics
618
vi.
Lesson Summary
623
·
You can use temporary tables and table variables
when you need to temporarily store data such as an intermediate result set of a
query.
·
Temporary tables and table variables differ in a
number of ways, including scope, DDL and indexing, interaction with
transactions, and distribution statistics (see below for the details).
·
Local temporary tables are visible in the level
that created them, across batches, and also in inner levels in the call stack.
Table variables are visible only to the batch that declared them.
·
You can apply a DDL to a temporary table after
it is created, including creating indexes and other DDL changes. You cannot
apply DDL changes to a table variable after it is declared. You can get indexes
indirectly in a table variable through primary key and unique constraints.
·
Changes applied to a temporary table in a
transaction are undone if the transaction is rolled back. Changes against a
table variable are not undone if the user transaction is rolled back.
·
SQL Server maintains distribution statistics on
temporary tables but not on table variables. As a result, the plans for queries
using temporary tables tend to be more optimized compared to those for queries
using table variables.
Chapter 17 - understanding further Optimization aspects 631
a.
Lesson 1: Understanding Plan Iterators . . . . .
. . . . . . . . . . . . . . . . . . . . . . .632
i.
Access Methods
632
ii.
Join Algorithms
638
iii.
Other Plan Iterators 641
iv.
Lesson Summary
647
·
SQL Server uses many different data access
methods.
·
SQL Server uses different join and aggregation
algorithms.
·
There are no “good” and “bad” iterators. Any iterator can be the best choice for
a specific query and specific data.
b.
Lesson 2: Using Parameterized Queries and Batch
Operations . . . . . . . .647
i.
Parameterized Queries 648
ii.
Batch Processing
653
iii.
Lesson Summary
660
·
SQL Server parameterizes queries for better
execution plan reusage.
·
You can also parameterize queries yourself.
·
Batch processing mode, new to SQL Server 2012,
can improve the performance of data warehousing queries substantially,
especially by lowering the CPU usage.
·
Batch processing goes well with columnstore
indexes.
c.
Lesson 3: Using Optimizer Hints and Plan Guides
. . . . . . . . . . . . . . . . . . . .661
i.
Optimizer Hints
661
ii.
Plan Guides
666
iii.
Lesson Summary
670
·
You can force SQL Server to execute a query in a
specific way by using optimizer hints.
·
With plan guides, you can force a specific
execution plan without modifying the query text.
