10776A ENU Companion

O F F I C I A L M I C R O S O F T L E A R N I N G P R O D U C T
10776A
Developing Microsoft ®
SQL Server 2012 ®
Databases
Companion Content
2 Developing Microsoft® SQL Server® 2012 Databases
Information in this document, including URL and other Internet Web site references, is subject to change
without notice. Unless otherwise noted, the example companies, organizations, products, domain names,
e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with
any real company, organization, product, domain name, e-mail address, logo, person, place or event is
intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the
user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in
or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical,
photocopying, recording, or otherwise), or for any purpose, without the express written permission of
Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property
rights covering subject matter in this document. Except as expressly provided in any written license
agreement from Microsoft, the furnishing of this document does not give you any license to these
patents, trademarks, copyrights, or other intellectual property.
The names of manufacturers, products, or URLs are provided for informational purposes only and
Microsoft makes no representations and warranties, either expressed, implied, or statutory, regarding
these manufacturers or the use of the products with any Microsoft technologies. The inclusion of a
manufacturer or product does not imply endorsement of Microsoft of the manufacturer or product. Links
may be provided to third party sites. Such sites are not under the control of Microsoft and Microsoft is not
responsible for the contents of any linked site or any link contained in a linked site, or any changes or
updates to such sites. Microsoft is not responsible for webcasting or any other form of transmission
received from any linked site. Microsoft is providing these links to you only as a convenience, and the
inclusion of any link does not imply endorsement of Microsoft of the site or the products contained
therein.
© 2012 Microsoft Corporation. All rights reserved.
Microsoft, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the
United States and/or other countries.
All other trademarks are property of their respective owners.
Product Number: 10776A
Part Number: X18-28011
Released: 05/2012
Developing Microsoft® SQL Server® 2012 Databases 3
Module 1
Introduction to SQL Server® 2012 and its Toolset
Contents:
Lesson 1: Introduction to the SQL Server Platform 8
Lesson 2: Working with SQL Server Tools 10
Lesson 3: Configuring SQL Server Services 15
Module Reviews and Takeaways 18
Lab Review Questions and Answers 19

Lesson 1
Introduction to the SQL Server Platform
Contents:
Question and Answers 9
Question and Answers

SQL Server Instances
Question: Why might you need to separate databases by service level agreement?
Answer: Different database applications might need to meet different service level
agreements, particularly in relation to recovery time objectives (RTO) and recovery point
objectives (RPO).
SQL Server Editions

Question: What would be a good business case example for using a cloud-based service?
Answer: Startup companies. (Purchase too many servers and go broke. Purchase too little
and go broke).
SQL Server Versions

Question: Which versions of SQL Server have you worked with?
Answer: Answers will vary by student.

Lesson 2
Working with SQL Server Tools
Contents:
Detailed Demonstration Step 12


Demonstration 2A: Using SQL Server Management Studio
Question: When would displaying an estimated execution plan be helpful?
Answer: When troubleshooting query performance or when designing indexing strategies

for a database.
Demonstration 2B: Using SQL Server Data Tools

Question: Can you suggest a situation where the ability to schedule the execution of a
report would be useful?
Answer: Monthly or weekly sales reports.

Detailed Demonstration Steps

Demonstration 2A: Using SQL Server Management Studio
Detailed demonstration steps
1. Revert the virtual machines as per the instructions in D:\10776A_Labs\Revert.txt.
2. In the Virtual Machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
3. In the Connect to Server window, ensure that Server type is set to Database Engine.
4. In the Server name text box, type (local).
5. In the Authentication drop-down list, select Windows Authentication, and click Connect.
6. If required, from the View menu, click Object Explorer.
7. In Object Explorer, expand Databases, expand AdventureWorks, and Tables. Review the database
objects.
8. Right-click the AdventureWorks database and choose New Query.
9. Type the query shown in the snippet below.
SELECT * FROM Production.Product ORDER BY ProductID;
10. Note the use of Intellisense while entering it, and then click Execute on the toolbar. Note how the
results can be returned.
11. From the File menu click Save SQLQuery1.sql. Note this saves the query to a file. In the Save File As
window click Cancel.
12. In the Results tab, right-click on the cell for ProductID 1 (first row and first cell) and click Save
Results As…. In the FileName textbox, type Demonstration2AResults and click Save. Note this
saves the query results to a file.
13. From the Query menu, click Display Estimated Execution Plan. Note that SSMS is capable of more
than simply executing queries.
14. From the Tools menu, click Options.
15. In the Options pane, expand Query Results, expand SQL Server, and expand General. Review the
available configuration options and click Cancel.
16. From the File menu, click Close. In the Microsoft SQL Server Management Studio window, click
No.
17. In the File menu, click Open, and click Project/Solution.
18. In the Open Project window, open the project
D:\10776A_Labs\10776A_02_PRJ\10776A_02_PRJ.ssmssln.
19. From the View menu, click Solution Explorer. Note the contents of Solution Explorer. SQL Server
projects have been supplied for each module of the course and contain demonstration steps and
suggested lab solutions, along with any required setup/shutdown code for the module.
20. In the Solution Explorer, click the X to close it.
21. In Object Explorer, from the Connect toolbar icon, note the other SQL Server components that
connections can be made to:
• Database Engine, Analysis Services, Integration Services, Reporting Services
22. From the File menu, click New, and click Database Engine Query to open a new connection.
23. In the Connect to Database Engine window, type (local) in the Server name text box.
25. In the Available Databases drop-down list, click tempdb database. Note this will change the
database that the query is executed against.
26. Right-click in the query window and click Connection, and click Change Connection… Note: this will
reconnect the query to another instance of SQL Server. In the Connect to Database Engine window,
click Cancel.
27. From the View menu, click Registered Servers.
28. In the Registered Servers window, expand Database Engine, right-click Local Server Groups, and
click New Server Group…
29. In the New Server Group Properties window type Dev Servers in the Group name textbox and
click OK.
30. Right-click Dev Servers and click New Server Registration…
31. In the New Server Registration window, click the Server name drop-down list, type (local) and click
Save.
32. Right-click Dev Servers and click New Server Registration…
33. In the New Server Registration window, click the Server name drop-down list, type .\MKTG and
click Save.
34. In the Registered Servers window, right-click the Dev Servers group and choose New Query.
35. Type the query as shown in the snippet below and click the Execute toolbar icon.
SELECT @@version;
36. Close SQL Server Management Studio.
37. Click No in the SQL Server Management Studio window.
Demonstration 2B: Using SQL Server Data Tools

1. If Demonstration 2A was not performed, revert the virtual machines as per the instructions in
D:\10776A_Labs\Revert.txt.
SQL Server Data Tools. From the File menu, click New and then click Project. Note the available
project templates (also if other languages are installed).
3. In the Templates pane, click Report Server Project, and click OK.
4. In Solution Explorer, right-click Reports and click Add New Report.
5. In the Report Wizard window, click Next.
6. In the Select the Data Source window, click Edit.
7. In the Connection Properties window, type (local) for the Server name and in the Connect to a
database drop-down list, select AdventureWorks, and click OK.
8. In the Select the Data Source window, click Next then in the Design the Query window, for the
Query string textbox, type the following query as shown in snippet below and click Next.
SELECT ProductID, Name, Color, Size FROM Production.Product ORDER BY ProductID;
9. In the Select the Report Type window, click Next.

10. In the Design the Table window, click Details four times, and click Finish>>|.
11. In the Completing the Wizard window, click Finish.
12. In the Report1.rdl [Design]* tab, click Preview and note the report that is rendered.
13. Click on the Design tab, from the File menu click Exit. Note if prompted do not save the changes.
Demonstration 2C: Using Books Online

2. In the Virtual Machine, click Start, click All Programs, click Microsoft SQL Server 2012, click
Documentation & Community, and click SQL Server Documentation.
3. Maximize the Microsoft Help Viewer window and note the basic navigation options available.
6. In the Server name text box, type (local).
8. From the File menu, click New, and click Query with Current Connection.
9. In the SQLQuery1.sql tab, type the query as shown in the snippet below and click the Execute
toolbar icon.
SELECT SUBSTRING('test string',2,7);
10. Click the name of the function SUBSTRING, then hit the F1 key to open the BOL topic for
SUBSTRING.
11. Note the content of the page and scroll to the bottom to see the examples then close the Microsoft
Help Viewer window.
12. Close SQL Server Management Studio, without saving any changes.
13. If your host system has Internet access available, open Internet Explorer in the host system and
browse to the SQL Server Books Online page: http://go.microsoft.com/fwlink/?LinkID=233780
and note the available online options.
Lesson 3
Configuring SQL Server Services
Contents:


SQL Server Configuration Manager
Question: Why would a server system need to have a client configuration node?
Answer: Because client applications (including tools and utilities) need to connect to the
server and to other servers.
Demonstration 3A: Using SQL Server Profiler

Question: What could you use captured trace files for?
Answer: Performance tuning, workload testing, upgrade testing.


4. In the Server name text box, type (local), in the Authentication drop-down list, select Windows
Authentication, and click Connect.
5. From the Tools menu, click SQL Server Profiler.
7. In the Server name text box, type (local), in the Authentication drop-down list, select Windows
Authentication, and click Connect.
8. In the Trace Properties window, click Run. Note this will start a new trace with the default options.
9. Switch to SQL Server Management Studio, click New Query toolbar icon.
10. In the Query window, type the query as shown in the snippet below, and click Execute.
USE AdventureWorks;
GO
SELECT * FROM Person.Contact ORDER BY FirstName;
GO
11. Switch to SQL Server Profiler. Note the statement trace occurring in SQL Server Profiler then from
the File menu and click Stop Trace.
12. In the Results grid, click individual statements to see the detail shown in the lower pane.
13. Close SQL Server Management Studio and SQL Server Profiler without saving any changes.
Module Reviews and Takeaways

Review questions
Question: What is the difference between a SQL Server version and an edition?
Answer: Versions are releases of the product. Editions are levels of the product with differing
capabilities.
Question: What is the purpose of the SQL Server Data Tools?
Answer: It adds templates to Visual Studio for constructing and testing business intelligence
projects.
Question: Does Visual Studio need to be installed before SSDT?
Answer: No, SQL Server installation will install the partner edition of Visual Studio if Visual
Studio is not already present.
Best Practices
1. Ensure that developer edition licenses are not used in production environments.
2. Develop using the least privileges possible, to avoid accidentally building applications that will not
run for standard users.
3. If using an offline version of Books Online, ensure it is kept up to date.
4. Ensure that service accounts are provisioned with the least workable permissions.
Lab Review Questions and Answers

Question: Why does the Reporting Services encryption key need to be backed up?
Answer: Reporting Services encrypts sensitive information such as connection details and
this key is needed if the RS databases ever need to be restored on another server.
Question: How can SQL Server be configured to use a different IP port?
Answer: SQL Server Configuration Manager network configuration provides the ability to
configure ports for protocols.
Module 2
Working with Data Types
Contents:
Lesson 1: Using Data Types 21
Lesson 2: Working with Character Data 24
Lesson 3: Converting Data Types 27
Lesson 4: Working with Specialized Data Types 30

Lesson 1
Using Data Types
Contents:


Introducing Data Types
Question: Why would it be faster to compare two integer variables that are holding the
values 3240 and 19704 than two varchar(10) variables that are holding the values "3240" and
"19704"?
Answer: Because the number of bytes needing to be compared is much less. And no
collation or sorting rules need to be considered, as is needed for most string values.
Exact Numeric Data Types

Question: What would be a suitable data type for storing the value of a check box that can
be 0 for unchecked, 1 for checked, or -1 for disabled?
Answer: smallint (note that tinyint cannot be negative)
Date and Time Data Types

Question: Why is the specification of a date range from the year 0000 to the year 9999
based on the Gregorian Calendar not entirely meaningful?
Answer: Because the Gregorian Calendar was introduced by Pope Gregory XIII in 1582.
Unique Identifiers
Question: The slide mentions that a common error is to store GUIDs as strings. What would
be wrong with this?
Answer: Much larger storage and much slower comparisons, etc.
NULL or NOT NULL Columns

Question: When should a value be nullable?
Answer: If it is possible for the value to be unknown.

Demonstration 1A: Working with Numeric Data Types
1. Revert the virtual machines as per the instructions in D:\10776A_Labs\Revert.txt.
2. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, click SQL
Server Management Studio. In the Connect to Server window, type Proseware in the Server
name text box and click Connect. From the File menu, click Open, click Project/Solution, navigate
to D:\10776A_Labs\10776A_02_PRJ\10776A_02_PRJ.ssmssln and click Open.
3. From the View menu, click Solution Explorer. Open and execute the 00 – Setup.sql script file from
within Solution Explorer.
4. Open the 11 – Demonstration 1A.sql script file.
5. Follow the instructions contained within the comments of the script file.
Lesson 2
Working with Character Data
Contents:


Character Data Types
Question: Why would you use the sysname data type rather than the nvarchar(128) data
type?
Answer: To minimize the amount of change required to scripts should the sysname data
type ever change in SQL Server.
Understanding Collations
Question: What are the code page and sensitivity values for the collation
SQL_Scandinavian_Cp850_CI_AS?
Answer: Code page 850, case insensitive and accent sensitive.


Demonstration 2A: Working with Character Data
1. If Demonstration 1A was not performed,
• Revert the virtual machine as per the instructions in D:\10776A_Labs\Revert.txt.
• In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, click
SQL Server Management Studio. In the Connect to Server window, type Proseware in the
Server name text box and click Connect. From the File menu, click Open, click
Project/Solution, navigate to D:\10776A_Labs\10776A_02_PRJ\10776A_02_PRJ.ssmssln and
click Open.
• From the View menu, click Solution Explorer. Open and execute the 00 – Setup.sql script file
from within Solution Explorer.

Lesson 3
Converting Data Types
Contents:


Using CAST, CONVERT, and PARSE
Question: Give an example of a situation where you would need to cast a number as a
string.
Answer: Where strict formatting of the output is required, particularly if the number is being
concatenated within a string value.
Implicit Data Conversion

Question: Look at the slide examples. Suggest where implicit conversions are happening and
from which data types to which other data types.
Answer: First SET: @Annual is an integer and is being implicitly converted to a decimal(18,2)
value at the point where it is assigned to @Salary.
Second SET: @Annual is an integer and is being implicitly converted to a decimal(18,2) value
at the point where it is added to the @Salary value.
Third SET: The constant is a string and is being cast to xml at the point that it is being
assigned to @XmlData.

Demonstration 3A: Common Conversion Issues
1. If Demonstration 1A was not performed:
click Open.
Lesson 4
Working with Specialized Data Types
Contents:

Demonstration 4A: rowversion Data Type
click Open.


Review questions
Question: What is the uniqueidentifier data type commonly used for?
Answer: Storing GUID values (globally unique identifier values)
Question: What are common errors that can occur during data type conversion?
Answer: Many but perhaps truncation, rounding, range errors, inappropriate values
Question: What date is present in a datetime data type if a value is assigned to it that only
contains a time?
Answer: A. 1-1-1900
Best Practices
1. Always choose an appropriate data type for columns and variables rather than using generic data
types such as string or xml except where they are necessary.
2. When defining columns, always specify the nullability rather than leaving it to the system default
settings.
3. Avoid the use of any of the deprecated data types.
4. In the majority of situations, do not store currency values in approximate numeric data types such as
real or float.
5. Use the unicode-based data types where there is any chance of needing to store non-English
characters.
6. Use sysname data type in administrative scripts involving database objects rather than nvarchar(128).

Question: What data type should I use to store the number of seconds since midnight?
Answer: Even though it is time related, it's likely you would use an integer here. (Values can
also exceed smallint)
Question: Which of the following columns are likely to be nullable: YTD_Sales, DateOfBirth?
Answer:
A. YTD_Sales would rarely make sense as nullable. Why would you not know the YTD_Sales value? (It
should be zero if no sales)
B. DateOfBirth might be nullable. It is possible to have regulations that might disallow you to store
people's ages.
Module 3
Designing and Implementing Tables
Contents:
Lesson 1: Designing Tables 35
Lesson 2: Working with Schemas 38
Lesson 3: Creating and Altering Tables 41

Lesson 1
Designing Tables
Contents:
Detailed Demonstration Steps 37


Primary Keys
Question: What is an advantage of using a natural key?
Answer: A human can look at the value and has a chance of knowing if it is correct or not.
Question: What is a disadvantage of using a natural key?

Answer: A combination of columns suitable for forming a natural key might be hard to find.
Question: What might be an appropriate primary key for the Owner table mentioned in the
previous demonstration?
Answer: It is very difficult to come up with anything natural to describe a person, particularly
anything that will not ever change. National ID numbers are useful but are very country-
specific. The aim here is to get the students to realize that there isn't a good answer.
Foreign Keys
Question: What would be an example of multiple foreign keys in a table referencing the
same table?
Answer: A Pet table might have both Owner and Handler columns that both refer to a
Person table or a ResellerSales table might have an OrderDateKey, a DueDateKey, and a
ShipDateKey.

Demonstration 1A: Normalization
1. Revert the virtual machine as per the instructions in D:\10776A_Labs\Revert.txt.
Lesson 2
Working with Schemas
Contents:


Creating Schemas
Question: What would be different about the outcome of the second statement if the
CREATE SCHEMA and the CREATE TABLE parts of the statement were executed separately?
Answer: The table Article would be created in the user's default schema which will not likely
be the KnowledgeBase schema.

Demonstration 2A: Schemas
• Revert the virtual machine using the instructions in D:\10776A_Labs\Revert.txt.
click Open.
Lesson 3
Creating and Altering Tables
Contents:


Creating Tables
Question: In the example shown in the slide, could the OwnerName column have been used
as the primary key instead of a surrogate key?
Answer: It would be a poor choice as two owners could easily have the same name.
Dropping Tables
Question: Why would a reference to a table stop it from being dropped?
Answer: References need to be maintained. As an example, you could then end up with
Orders that referred to non-existent Customers.
Demonstration 3A: Working with Tables

Question: Why should you ensure that you specify the nullability of a column when
designing a table?
Answer: To make sure your DDL scripts are reliable in that you don't have the outcome
change depending upon the system settings.

Demonstration 3A: Working with Tables
click Open.
Demonstration 3B: Temporary Tables


click Open.
2. Open the 32 – Demonstration 3B.sql script file.
Demonstration 3C: Computed Columns


click Open.
2. Open the 33 – Demonstration 3C.sql script file.

Review questions
Question: What is a primary key?
Answer: A key made up of one or more columns that can uniquely identify a row in the
table. It cannot be NULL.
Question: What is a foreign key?
Answer: A key in one table that references a candidate key (normally a primary key) from
another table.
Question: What is meant by the term “referential integrity”?
Answer: Ensuring that foreign key relationships are enforced.
Best Practices
1. All tables should have primary keys.
2. Foreign keys should be declared within the database in almost all circumstances. Often developers
will suggest that the application will ensure referential integrity. Experience shows that this is a poor
option. Databases are often accessed by multiple applications. Bugs are also easy to miss when they
first start to occur.

Question: When should a column be declared as nullable?
Answer: When the value can be unknown.
Question: Could columns such as AddressLine1, AddressLine2, AddressLine3 be reasonable

in a normalized design?
Answer: Yes, they might represent distinct attributes of an object such as a customer. This is
different to the example of Owner1, Owner2. As an example, the addresses might represent
different lines on a form.
Question: How would this differ from fields called PhoneNumber1, PhoneNumber2,
PhoneNumber3?
Answer: While it's not possible to precisely know the answer, it is likely that these really
represent different phone numbers eg: HomeNumber, WorkNumber, etc.
Module 4
Ensuring Data Integrity through Constraints
Contents:
Lesson 1: Enforcing Data Integrity 48
Lesson 2: Implementing Domain Integrity 50
Lesson 3: Implementing Entity and Referential Integrity 52

Lesson 1
Enforcing Data Integrity
Contents:

Types of Data Integrity
Question: When might more than one type of integrity apply to a scenario?
Answer: Prompt the students for examples. An example to bring up would be an employee
database where the date of birth cannot be null or in the future (domain integrity) and the
employee id enforces entity integrity (deleting the employee record in one table should
delete references in the other tables).
Options for Enforcing Data Integrity

Question: In your organization, which data integrity features are currently implemented in
one of your databases?
Answer: Prompt the students to discuss the scenarios in their organizations where data
integrity is currently implemented, or could be implemented.
Lesson 2
Implementing Domain Integrity
Contents:

Demonstration 2A: Data and Domain Integrity
1. Revert the virtual machines using the instructions at D:\10776A_Labs\Revert.txt.
Lesson 3
Implementing Entity and Referential Integrity
Contents:


PRIMARY KEY Constraints
Question: In the example table shown in the slide, if the table did not have the
OpportunityID column, what combinations of columns would be needed to create a
candidate key?
Answer: Requirements and ReceivedDate together might work. If SalespersonID was

included, it would need to also be changed to NOT NULL. Large textual keys like this are
usually not good candidate keys.
Cascading Referential Integrity

Question: Think of a scenario involving data for a human resources department. What types
of cascading options would be appropriate for updating or deleting records?
Answer: Prompt the students to come up with scenarios and how they will affect the tables.
You can suggest an employee retiring, or an employee getting married and changing the
family name as examples.

Demonstration 3A: Entity and Referential Integrity
• Revert the virtual machines using the instructions at D:\10776A_Labs\Revert.txt.
click Open.
Demonstration 3B: Working with Identity and Sequences

click Open.

Review questions
Question: Why implement check constraints if an application is already checking the input
data?
Answer: Even if an application checks that data conforms in the user interface or backend
code, error conditions may arise that cause fields to become corrupt or null. Also, procedures
for archiving, backing up, and triggers may attempt to copy bad data into the table, which
can then cause an application to fail. Multiple applications may be accessing the same data.
Question: What are some scenarios in which you may want to temporarily disable constraint
checking?
Answer: Since constraint checking can impact performance, you might want to disable it
when performing large inserts, such as in a restore procedure or copying large number of
records for an archive. In addition, you may know that duplicate or invalid data exists in your
source or destination and have a plan to deal with cleaning up the data afterwards, such as
with a script or other procedure.
Best Practices
When you create a constraint on a column, if you do not specify a name for the constraint, SQL will
generate a unique name for the constraint. However, you may want to be sure to always name constraints
to adhere to your naming conventions.

Question: In SQL Server Management Studio, you successfully ran a script that created a
table but you don’t see the table in Object Explorer. What do you need to do?
Answer: In Object Explorer, expand the database, right-click Tables, and then choose
Refresh.
Question: What does the option Default do when creating a column?
Answer: The Default value specifies a default value for the column for new records.
Question: What requirement does a primary key constraint have that a unique constraint
doesn’t?
Answer: Primary keys cannot be NULL.

Module 5
Planning for SQL Server® 2012 Indexing
Contents:
Lesson 1: Core Indexing Concepts 58
Lesson 2: Data Types and Indexes 61
Lesson 3: Single Column and Composite Indexes 63

Lesson 1
Core Indexing Concepts
Contents:


How SQL Server Accesses Data
Question: When might a table scan be more efficient than using an index?
Answer: An example would be when all the data in a table needs to be accessed in no
particular order.
The Need for Indexes

Question: Which different ways might you want to locate books in a physical library?
Answer: Author name (and potentially multiple authors), book name, category, ISBN, release
date and many more.
Index Fragmentation
Question: Why does fragmentation affect performance?
Answer: If pages are only half full, twice as many pages need to be read to access the same
amount of data.
Demonstration 1A: Viewing Index Fragmentation

Question: How might solid state disk drives change concerns around fragmentation?
Answer: Concerns about external fragmentation are largely based on the assumption that
accessing adjacent data is faster than accessing data elsewhere on a drive. SSDs start to
challenge this assumption.

Demonstration 1A: Viewing Index Fragmentation
1. Revert the virtual machine using the instructions in D:\10776A_Labs\Revert.txt.
Lesson 2
Data Types and Indexes
Contents:

Numeric Index Data
Question: Would you imagine that processor bit size affects the speed when comparing INT
or BIGINT values?
Answer: Comparisons work fastest when the processor architecture is at least as big as the
data type. For example, 32 bit values compare quickly on 32 bit systems but 64 bit values
comparisons are much more work for 32 bit systems. Most SQL Server installations today
should be 64 bit.
Indexing Computed Columns

Question: If a column in a database mostly held character values but occasionally (30 rows
out of 50,000 rows in the table) holds a number, how could you quickly locate a row with a
specific numeric value?
Answer: Create a calculated column that holds the number if the column is numeric but
NULL otherwise. Then index the computed column.
Lesson 3
Single Column and Composite Indexes
Contents:


Single Column vs. Composite Indexes
Question: Why might an index on customer then order date be more or less effective than
an index on order date then customer?
Answer: Selectivity is important and the two columns might differ greatly in terms of
selectivity. Usually, you want the most selective column first when designing an index in the
absence of any other criteria. Once you understand the pattern of your data (the number of
orders per customer is high or low) and the query you need to satisfy, you can start to decide
on an appropriate indexing strategy.
Index Statistics
Question: Before starting to perform your lookup in a physical library, how would you know
which way was quicker?
Answer: You would need to know how many books there were for each author and also
need to know what percentage of the author list you are traversing.
Demonstration 3A: Viewing Index Statistics

Question: Why would you not always choose to use FULLSCAN for statistics?
Answer: Reading all the rows in a large table might take too long or too many resources.

Demonstration 3A: Viewing Index Statistics
click Open.

Review questions
Question: Do tables need indexes?
Answer: In theory no, they are in place to improve performance.
Question: Why do some constraints use indexes?
Answer: To make checking values quicker.
Best Practices
1. Design indexes to maximize sensitivity which leads to lower I/O.
2. In absence of other requirements, aim to have the most selective columns first in composite indexes.

Question: Which types of queries would most likely lead to widely-differing query plans?
Answer: Range queries.
Question: If you have an equality predicate and a LIKE predicate in your most important
query, which predicate would you try to satisfy as the first column of a composite index?
Answer: The equality index.

Module 6
Implementing Table Structures in SQL Server® 2012
Contents:
Lesson 1: SQL Server Table Structures 69
Lesson 2: Working with Clustered Indexes 72
Lesson 3: Designing Effective Clustered Indexes 75

Lesson 1
SQL Server Table Structures
Contents:


What is a Heap?
Question: Why might modifying a row cause it to need to move between pages?
Answer: The row might now be larger and there might be insufficient space on the existing
page.
Operations on Heaps
Question: What would be involved in finding a book in a library structured as a heap? (This
would simulate a SELECT operation).
Answer: You would have to scan all the books in the library. Note that queries don't only
return the first row that matches unless you specify that. They return all matching rows. So
even once you've found a matching book, you would keep scanning the whole library.
Operations on Clustered Indexes

Question: What sort of queries would now perform better in this library?
Answer: A search for a particular ISBN or for a range of ISBNs.

Demonstration 1A: Rebuilding Heaps
Lesson 2
Working with Clustered Indexes
Contents:


Creating Clustered Indexes
Question: What else would be added to your table if you added a non-unique clustered
index to it?
Answer: A uniqueifier
Dropping a Clustered Index

Question: How could you remove a primary key constraint that was being referenced by a
foreign key constraint?
Answer: Drop the foreign key constraint first.
Incorporating Free Space in Indexes

Question: While you could avoid many page splits by setting a FILLFACTOR of 50, what
would be the downside of doing this?
Answer: Reading the whole table would now need twice as many pages to be read. I/O is
typically the biggest bottleneck in SQL Server systems today so this might be very counter-
productive.
Question: When would a FILLFACTOR of 100 be useful?
Answer: Read-only data.
Demonstration 2A: Clustered Indexes

Question: Where was the performance of the UPDATE statement against this table much
faster than the one against the heap?
Answer: It was quick to find the row to update because of the index.

Demonstration 2A: Clustered Indexes
click Open.
Lesson 3
Designing Effective Clustered Indexes
Contents:

Appropriate Data Types for Clustering Keys
Question: New uniqueidentifier values in SQL Server can be generated with the NEWID()
function. SQL Server 2005 introduced the NEWSEQUENTIALID() function to try to address the
issue of increasing values. Why doesn't this typically solve the problem of random values?
Answer: Because the values are normally generated by other application tiers.

Review questions
Question: What is the main problem with uniqueidentifiers used as primary keys?
Answer: The random order of their values.
Question: Where are newly inserted rows placed when a table is structured as a heap?
Answer: In any available page that has sufficient space available.
Best Practices
1. Unless specific circumstances arise, most tables should have a clustered index.
2. The clustered index may or may not be placed on the table's primary key.
3. When using GUID primary keys in the logical data model, consider avoiding their use throughout the
physical implementation of the data model.

Question: When is it important that a clustered index has an increasing key?
Answer: When significant insert operations are expected in the order of the key.
Question: Which table structure is automatically assigned when a table is assigned a primary
key during the table creation, without specifying a structure?
Answer: Clustered index.

Module 7
Reading SQL Server® 2012 Execution Plans
Contents:
Lesson 1: Execution Plan Core Concepts 80
Lesson 2: Common Execution Plan Elements 83
Lesson 3: Working with Execution Plans 86

Lesson 1
Execution Plan Core Concepts
Contents:


Query Execution Phases
Question: Can you think of a type of query that might lead to a trivial plan?
Answer: SELECT * FROM SomeTable;
What is an Execution Plan?

Question: What resources do you imagine the cost would be based upon?
Answer: Combination of CPU, memory, I/O
Execution Plan Formats

Question: What benefit does having SSMS associated with the .sqlplan filetype have?
Answer: You can double-click (or right-click and Open) a .sqlplan file and it will
automatically open up in SSMS.
Demonstration 1A: Viewing Execution Plans in SSMS

Question: How do you explain that such different queries return the same plan?
Answer: The query optimizer works out how to execute the query. Logically, these two
queries are identical so they should have the same plan.

Demonstration 1A: Viewing Execution Plans in SSMS
Lesson 2
Common Execution Plan Elements
Contents:


Filter and Sort
Question: What would affect the cost of a sort operation?
Answer: The number of rows, the data type, the collation (if string data) and the length of
the data.
Data Modification
Question: Can you think of an example where an INSERT statement in T-SQL need to
perform more than an INSERT operation in an execution plan?
Answer: Foreign key references might need to be checked.
Demonstration 2A: Working with Common Execution Plan Elements

Question: Why is the plan for a simple delete so complex?
Answer: Because references to other tables need to be checked.


Demonstration 2A: Working with Common Execution Plan Elements
click Open.
Lesson 3
Working with Execution Plans
Contents:


Demonstration 3A: Capturing Plans in Activity Monitor
Question: What could cause an expensive query to be removed from the Activity Monitor
window?
Answer: More expensive queries being executed or a server restart.
Demonstration 3B: Viewing Cached Plans

Question: No matter how quickly you execute the command to check the cache after you
clear it, you would not see it empty. Why?
Answer: Because the command to check the cache would be there.


Demonstration 3A: Capturing Plans in Activity Monitor
click Open.
Demonstration 3B: Viewing Cached Plans


click Open.

Review questions
Question: What is the difference between a graphical execution plan and an XML execution
plan?
Answer: A graphical plan is a rendering of an XML plan that contains the most important
information in an easy to read format.
Question: Give an example of why a T-SQL DELETE statement could have a complex
execution plan?
Answer: There might be referential integrity checks to be done (ie: foreign keys)
Best Practices
1. Avoid capturing execution plans for large numbers of statements when using SQL Profiler.
2. If you need to capture plans using Profiler, make sure the trace is filtered to reduce the number of
events being captured.

Question: Can two different queries end up with the same execution plan?
Answer: yes
Question: If so, how can that occur? If not, why not?

Answer: The execution plan details the steps to retrieve the required results. There are many
ways to express the requirements of a single query.
Module 8
Improving Performance through Nonclustered Indexes
Contents:
Lesson 1: Designing Effective Nonclustered Indexes 92
Lesson 2: Implementing Nonclustered Indexes 95
Lesson 3: Tracing and Tuning Queries 98

Lesson 1
Designing Effective Nonclustered Indexes
Contents:


Nonclustered Indexes Over Heaps
Question: What is an upside of having the indexes point directly to RowIDs?
Answer: Finding a data row is quick once the index has been accessed.
Question: What is the downside of having multiple indexes pointing to data pages via
RowID?
Answer: The data row might need to move to another location during update activity.
Nonclustered Indexes Over Clustered Indexes

Question: What is the downside of holding clustering keys in the leaf nodes of a
nonclustered index instead of RowIDs?
Answer: Two indexes need to be looked up to find a data row.
Question: What is the upside of holding clustering keys in the leaf nodes of a nonclustered
index instead of RowIDs?
Answer: Nonclustered indexes do not need to be modified when rows move within the
clustered index. Row movement could relate to data changes or index rebuild/reorganize
operations.
Demonstration 1A: Obtaining Index Information

Question: What would be another way to find information about the physical structure of
indexes?
Answer: Querying the sys.dm_db_index_physical_stats dynamic management function.


Demonstration 1A: Obtaining Index Information
Lesson 2
Implementing Nonclustered Indexes
Contents:


Performance Impact of Lookups in Nested Loops
Question: How selective would you imagine a query needs to be before SQL Server will
decide to ignore the index and just scan the data?
Answer: Students will often suggest numbers like 10% but the reality is that it is more likely
to be a value like 1/3 of one percent.
Question: Is there any situation where there is no need for the lookups?
Answer: Yes, when the index entry already contains all the necessary data.
INCLUDE Clause
Question: For an index to cover a single table query, which columns would need to be
present in the index?
Answer: Every column that is mentioned in the query.
Filtered Indexes
Question: What is the downside of having an entry at the leaf level for every transaction
row, whether finalized or not?
Answer: Maintenance operations are much more complex and long running.
Demonstration 2A: Working with Nonclustered Indexes

Question: If included columns only apply to nonclustered indexes, why do you imagine that
the columns in the clustered primary key also showed as included?
Answer: All columns in a clustered index are, by definition, included already.


Demonstration 2A: Working with Nonclustered Indexes
click Open.
Lesson 3
Tracing and Tuning Queries
Contents:


SQL Server Profiler
Question: Where would the ability to replay a trace be useful?
Answer: In load testing and when testing queries against upgraded versions of SQL Server.

Question: When so many statements were executed, why was there only one entry in the
trace?
Answer: Because the statements in the loop were all part of the same batch.
Database Engine Tuning Advisor

Question: Why is it important to tune an entire workload rather than individual queries?
Answer: Because a single query might not be executed very often and have little impact on
the overall server load. Also, changes to improve the performance of one query might
impact the performance of other queries adversely.
Demonstration 3B: Using Database Engine Tuning Advisor

Question: Should you immediately apply the recommendations to your server?
Answer: No, each recommendation should be reviewed first. For example, when DETA
suggests new statistics, often this is an indication of missing or inappropriate indexes.

click Open.
Demonstration 3B: Using Database Engine Tuning Advisor

click Open.

Review questions
Question: What is a covering index?
Answer: An index that provides all columns needed by SQL Server when executing a query
without the need to perform lookups to the base table.
Question: Can a clustered index be a covering index?
Answer: A clustered index contains all columns at the leaf level of its index so, by definition,
it covers any query on the table. However, the INCLUDE clause only makes sense for
nonclustered indexes and a targeted covering nonclustered index can almost always be
designed to outperform the clustered index on a given query. Consideration needs to be
given, however, to the performance impacts of maintaining nonclustered indexes.
Best Practices
1. Never apply Database Engine Tuning Advisor recommendations without further reviewing what is
being suggested.
2. Record details of why and when you create any indexes. DBAs are hesitant to ever remove indexes
without this knowledge.
3. When DETA suggests new statistics, this should be taken as a hint to investigate the indexing
structure of the table.

Question: Do you ever need to include a column that is part of the table's clustering key as
an included column in a nonclustered index when trying to create a covering index?
Answer: The leaf level of a nonclustered index always includes the clustering key anyway.
Question: If so, why? If not, why not and should you include it anyway?
Answer: You should include it anyway in case the clustering key ever gets changed. There is
no downside to including it as SQL Server will not store it twice within the leaf pages of the
index.
Module 9
Designing and Implementing Views
Contents:
Lesson 1: Introduction to Views 104
Lesson 2: Creating and Managing Views 107
Lesson 3: Performance Considerations for Views 110

Lesson 1
Introduction to Views
Contents:


What is a View?
Question: Why would you limit which columns are returned by a view?
Answer: Users may not be permitted to view the data in all the columns.
Types of Views
Question: What advantages would you assume that views would provide?
Answer: This is a lead-in question for the next topic.
Advantages of Views
Question: If tables can be replaced by views (and vice-versa) during maintenance, what does
that suggest to you about the naming of views and tables?
Answer: Tables and views should be named based on their contents, not on how they are
implemented. Prefixes are a problem with this. You don’t want to end up with views named
tblSomething or tables named vSomething.
Dynamic Management Views

Question: What sort of information about how SQL Server is performing and its health
would it be useful to have easy access to?
Answer: Answers will vary based on experience and backgrounds of the students. Examples
would be the fragmentation level of indexes or details of recent expensive query executions.
Demonstration 1A: Querying System and Dynamic Management Views

Question: When are the values returned by most dynamic management views reset?
Answer: When the server instance is restarted.


Demonstration 1A: Querying System and Dynamic Management Views
Server Management Studio. In the Connect to Server window, type Proseware and click Connect.
From the File menu, click Open, click Project/Solution, navigate to
D:\10776A_Labs\10776A_09_PRJ\10776A_09_PRJ.ssmssln and click Open.
Lesson 2
Creating and Managing Views
Contents:


Creating Views
Question: Why is the ORDER BY clause ever permitted in a view definition if it doesn’t
impact the output order of the rows?
Answer: It is needed to implement the selection logic for the TOP clause.
Obfuscating View Definitions

Question: Do you think you might be deploying encrypted views in your organization?
Answer: Most students should see that it is not usually worth the effort as it does not
achieve the desired outcome anyway and it complicates work on the system.
Demonstration 2A: Implementing Views

Question: Why is the ability to script a view useful?
Answer: To view its definition or to be able to recreate in on another server, possibly in

another environment.

Demonstration 2A: Implementing Views
SQL Server Management Studio. In the Connect to Server window, type Proseware and click
Connect. From the File menu, click Open, click Project/Solution, navigate to
Lesson 3
Performance Considerations for Views
Contents:


Views and Dynamic Resolution
Question: Suggest a type of join that could easily be eliminated when views are resolved.
Answer: LEFT OUTER JOINS where no columns from the joined table are used.
Demonstration 3B: Querying Indexed Views

Question: How could you ensure that an indexed view is selected when working with
Standard Edition of SQL Server?
Answer: Use the NOEXPAND query hint.


Demonstration 3A: Investigating Views and Performance
SQL Server Management Studio. In the Connect to Server window, type Proseware and click
Connect. From the File menu, click Open, click Project/Solution, navigate to
Demonstration 3B: Querying Indexed Views

click Open.

Review questions
Question: How does SQL Server store the view in the database?
Answer: What is stored in the database is the SELECT statement.
Question: What is a Standard non-indexed view?
Answer: Standard views combine data from one or more base tables (or views) into a new
virtual table, and is materialized at run time.
Question: What is an unbroken ownership chain?
Answer: When the same user owns the source object, the view, stored procedure, or user-
defined function, and all target objects (underlying tables, views, or other objects), the
ownership chain is said to be unbroken.
Best Practices
1. Use views to focus data for users.
2. Avoid nesting many layers within views.
3. Avoid ownership chain problems within views.
4. Ensure consistent connection SET options when intending to index views.

Question: What considerations are there for views that involve multiple tables?
Answer: If the view is updatable, only data from a single table can be updated in any
UPDATE statement.
Question: What is required for columns in views that are created from expressions?
Answer: Columns in views that are based on expressions need to be aliased.

Module 10
Designing and Implementing Stored Procedures
Contents:
Lesson 1: Introduction to Stored Procedures 116
Lesson 2: Working With Stored Procedures 119
Lesson 3: Implementing Parameterized Stored Procedures 122
Lesson 4: Controlling Execution Context 125

Lesson 1
Introduction to Stored Procedures
Contents:


What is a Stored Procedure?
Question: Why might it be useful to return multiple rowsets from a stored procedure?
Answer: It can help avoid round trips to the server to obtain additional data. For example, an
order header and all the detail lines could be returned in a single call.
Benefits of Stored Procedures

Question: Stored procedures can be created in any order. What could cause the tables that
are referenced by the stored procedures to need to be created in a specific order?
Answer: Tables might need to reference each other. Delayed name binding does not work
for tables.
Demonstration 1A: Working with System Stored Procedures and Extended

Stored Procedures
Question: What does the mismatch of prefixes in system stored procedure and system
extended stored procedure names suggest?
Answer: Prefixes on the names of objects are best avoided.


Demonstration 1A: Working with System Stored Procedures and Extended
Stored Procedures
Lesson 2
Working with Stored Procedures
Contents:


Obfuscating Stored Procedure Definitions
Question: Why might you want to obfuscate the definition of a stored procedure?
Answer: Significant intellectual property might be contained within the definition of the
stored procedure.
Demonstration 2A: Implementing Stored Procedures

Question: How could the GetBlueProductsAndModels stored procedure be made more
useful?
Answer: By adding a parameter and making it work for any selected color.

Demonstration 2A: Implementing Stored Procedures
click Open.
Lesson 3
Implementing Parameterized Stored Procedures
Contents:


Using Output Parameters
Question: Why might you use output parameters in conjunction with IDENTITY columns?
Answer: To return the value assigned by the IDENTITY column.
Parameter Sniffing and Performance

Question: How would you determine the value to assign in an OPTIMIZE FOR hint?
Answer: You would assign a value that it typical of the values supplied in most executions.
Demonstration 3A: Passing Parameters to Stored Procedures

Question: Why do we need to treat NULL differently to other possible values?
Answer: Because NULL is not a value; it is the lack of a value.


Demonstration 3A: Passing Parameters to Stored Procedures
click Open.
Lesson 4
Controlling Execution Context
Contents:


Controlling Execution Context
Question: What is an authenticator?
Answer: One principal that vouches for the identity of another principal.

Demonstration 4A: Viewing Execution Context
click Open.

Review questions
Question: What does the WITH RECOMPILE option do when used with a CREATE PROC statement?
Answer: It causes a new execution plan to be generated every time the procedure is executed.
Question: What does the WITH RECOMPILE option do when used with an EXECUTE statement?
Answer: It causes a new execution plan to be generated for this particular execution of the procedure
and for the plan to be discarded after execution.
Best Practices
1. Use the EXECUTE AS clause to override the execution context of stored procedures that use dynamic
SQL, rather than granting permissions on the underlying tables to users.
2. Design procedures to perform individual tasks. Avoid designing procedures that perform a large
number of tasks, unless those tasks are performed by executing other stored procedures.
3. Keep consistent ownership of stored procedures, views, tables and other objects within databases.

Question: When is the OUTPUT keyword needed for output parameters in working with
stored procedures?
Answer: Both when declaring the parameters in the stored procedure and when calling the
stored procedure in the EXEC statement.
Question: What does the sys.login_token view show?
Answer: All tokens associated with the login, including the login itself and server role
membership
Module 11
Merging Data and Passing Tables
Contents:
Lesson 1: Using the MERGE Statement 131
Lesson 2: Implementing TABLE Types 134
Lesson 3: Using TABLE Types As Parameters 137

Lesson 1
Using the MERGE Statement
Contents:


WHEN MATCHED
Question: What is different about the UPDATE statement in the example shown, compared
to a normal UPDATE statement?
Answer: No table name is specified.
WHEN NOT MATCHED BY SOURCE

Question: What would the DELETE statement look like if it only deleted rows where the date
in a column called LastModifed were older than a year?
Answer: DELETE WHERE LastModified < DATEADD(year,-1,SYSDATETIME());
OUTPUT Clause and $action

Question: How could the OUTPUT clause be useful in a DELETE statement?
Answer: For obtaining a list of rows actually deleted.
Demonstration 1A: Updating Data by Using the MERGE Statement

Question: What is meant by the term "composable query"?
Answer: The output of one query can be used as an input to another query.

Demonstration 1A: Updating Data by Using the MERGE Statement
Lesson 2
Implementing TABLE Types
Contents:


Reducing Round-Trip Overhead
Question: How could the number of round trips being made to the server be reduced?
Answer: By sending more than one piece of information in each call to the server.
Demonstration 2A: Passing Delimited Lists

Question: What are the basic problems with using delimited lists for parameters?
Answer: Difficulties with passing and parsing multiple columns and with checking data
types.
Populating TABLE Types with Row Constructors

Question: What would improve the INSERT query shown in the slide example?
Answer: Including a column list.
Demonstration 2B: Using TABLE Types and Row Constructors

Question: Can other users make use of the TABLE type that you create?
Answer: Yes, it becomes a standard data type within the database.


Demonstration 2A: Passing Delimited Lists
click Open.
Demonstration 2B: Using TABLE Types and Row Constructors


click Open.
Lesson 3
Using TABLE Types As Parameters
Contents:


TABLE Input Parameters for Stored Procedures
Question: What would you have to do to be able to pass multiple sales and their detail lines
in a single call?
Answer: You'd need a table-valued parameter for the sales headers and another for the sales
details. the sales details would also have to include a column for the sales number.
Demonstration 3A: Passing Tables to Stored Procedures

Question: What is the purpose of the SCOPE_IDENTITY() function shown in the
demonstration?
Answer: It returns the last IDENTITY value that has been allocated in the same scope.

Demonstration 3A: Passing Tables to Stored Procedures
click Open.

Review questions
Question: What is the difference between SOURCE NOT MATCHED and TARGET NOT MATCHED in a
MERGE statement?
Answer: The source is the input table, the target is the table being modified.
Question: What is a key advantage of the MERGE statement in terms of performance?
Answer: Only a single pass is made through the data.
Best Practices
1. Use multi-row inserts when the rows being inserted are related in some way, for example, the detail
rows of an invoice.
2. Consider making multiple-entity procedures instead of single-entity procedures to help minimize

round trip behavior and to reduce locking. For example, very minor changes are required to construct
a stored procedure that can insert multiple sales orders compared to a stored procedure that can
insert a single sales order.

Question: What is the purpose of the OUTPUT clause?
Answer: Allows returning relevant rows of data as a side-effect of a statement that modifies
data in the database
Question: In the values returned by an OUTPUT clause, how can we tell if an INSERT,
UPDATE or DELETE occurred?
Answer: By using $action in the OUTPUT clause

Module 12
Designing and Implementing User-Defined Functions
Contents:
Lesson 1: Overview of Functions 143
Lesson 2: Designing and Implementing Scalar Functions 145
Lesson 3: Designing and Implementing Table-valued Functions 147
Lesson 4: Considerations for Implementing Functions 150

Lesson 1
Overview of Functions
Contents:

Types of Functions
Question: How have you used functions in other programming languages?
Answer: Will vary based on experience.
System Functions
Question: Have you used any of the functions apart from data type and date time when
writing code?
Answer: Will vary based on experience.

Lesson 2
Designing and Implementing Scalar Functions
Contents:

Demonstration 2A: Working with Scalar Functions
Lesson 3
Designing and Implementing Table-valued Functions
Contents:


Inline Table-valued Functions
Question: TVFs return rows of data as tables. You have learned that tables do not have a
predefined order. Why does the example function in the slide include an ORDER BY clause?
Answer: Because it also includes TOP. The ORDER BY is used only for selection of rows, not
for ordering the output.
Multi-statement Table-valued Functions

Question: Can you think of a situation where you would need to use a Multi-statement
Table-valued Function rather than an Inline Table-valued Function?
Answer: Where you need to use complex logic that cannot be expressed in a single SELECT
statement, such as iterating through counters.
Demonstration 3A: Implementing Table-valued Functions

Question: What are some commonly used SQL Scalar functions that you can think of?
Answer: Most string manipulation functions.

Demonstration 3A: Implementing Table-valued Functions
click Open.
Lesson 4
Considerations for Implementing Functions
Contents:

Demonstration 4A: Controlling Execution Context
click Open.

Review questions
Question: When using the EXECUTE AS clause, what privileges should the login or user being
impersonated have?
Answer: For the login or user being impersonated, specify a login or user that has the least privileges
required to perform the operations required.
in the session. For example, do not specify a login name with server-level permissions, if only
database-level permissions are required; or do not specify a database owner account unless those
permissions are required.
Question: When using the EXECUTE AS clause, what privileges should the login or user creating the
code have?
Answer: IMPERSONATE permission for the login or user being impersonated.
Best Practices
1. Avoid calling multi-statement TVFs for each row of a query. In many cases, you can dramatically
improve performance by extracting the code from the query into the surrounding query.
2. Use the WITH EXECUTE AS clause to override the security context of code that needs to perform
actions that the user that is executing the code, does not have.

Question: When might it be practical to use an Inline Table-valued Function?
Answer: You could create a function that takes one input parameter, a customer (store) ID,
and returns the columns ProductID, Name, and the aggregate of year-to-date sales as YTD
Total for each product sold to the store.
Question: What is the biggest concern about the use of scalar functions?
Answer: Performance impacts.

Question: Why would you alter a function rather than dropping and recreating it?
Answer: To retain existing permissions associated with it.

Module 13
Creating Highly Concurrent SQL Server® 2012 Applications
Contents:
Lesson 1: Introduction to Transactions 155
Lesson 2: Introduction to Locks 158
Lesson 3: Management of Locking 160

Lesson 1
Introduction to Transactions
Contents:


What Are Transactions?
Question: Can you think of database operations in your organization where database
transactions are especially critical?
Answer: Answers will vary, but any place where integrity of the data is mission critical.
Auto Commit Transactions

Question: When might autocommit mode not be appropriate in a database application?
Answer: Answers can vary; discuss applications that might require a great deal of control
over transactions.
Explicit Transactions
Question: When might you want to use a savepoint?
Answer: A savepoint can be useful in a long transaction with several components. Instead of
rolling back the entire transaction, it may make more sense to roll back only certain portions
of a transaction by using a savepoint.
Implicit Transactions
Question: Can you think of an application in your organization where implicit transactions
might be appropriate?
Answer: Answers will vary.
Transaction Recovery
Question: A server crash occurs while two transactions are running. Transaction A is an
autocommit transaction that has been written to the transaction log, but not written to the
disk. Transaction B is an explicit transaction that has not been committed, though a
checkpoint was written while Transaction B was running. What will happen to each
transaction when the server is recovered?
Answer: Transaction A will be rolled forward because it already appears in the transaction
log. Transaction B will be rolled back because it was not explicitly committed, but it will only
be rolled back to the checkpoint.
Considerations for Using Transactions

Question: When would nested transactions be appropriate?
Answer: Nested transactions are primarily intended to support transactions in stored

procedures that can be called either from a process already in a transaction or from
processes that have no active transaction, so if you are using applications that those types of
stored procedures, nested transactions would be appropriate to use.

Demonstration 1A: Working with Transactions
5. Open the 12 – Demonstration 1A 2nd Window.sql script file.
6. Follow the instructions contained within the comments of the script files.
Lesson 2
Introduction to Locks
Contents:

Methods of Concurrency Control
Question: Can you think of an application in your organization that might work well with
optimistic concurrency control?
Answer: Answers may vary; discuss them with students.
What Are Locks?

Question: If a doctor's office uses a database application to manage patient records, how
might locks play a role in that application?
Answer: There could be a variety of ways locks are used, but a common use for locks would
be to ensure that two simultaneous updates to patient records could not occur; one update
would have to occur before the other.
Blocking vs. Locking

Question: What symptoms do you imagine that "excessive" blocking might relate to?
Answer: A good example would be locks that are held for a long time on resources that are
needed by other processes.
What Concurrency Problems Are Prevented by Locking?

Question: Has your organization experienced concurrency problems with database
applications? If so, what behavior did you see?
Lockable Resources
Question: If a database needs to lock several rows of data at once, what resources might be
locked?
Answer: For locking several rows at once, there are different possibilities, but the most likely
resources to be locked are Pages and Extents.
Types of Locks
Question: What happens if a query tries to read data from a row that is currently locked by
an exclusive (X) lock?
Answer: Even though the lock is exclusive, a SELECT statement will still be able to read data
from the locked row.
Lock Compatibility
Question: Can you think of situations where lock compatibility is important?
Answer: Answers will vary, but any situation in which data might be updated and read from
different sources at the same time should be analyzed for lock compatibility.
Lesson 3
Management of Locking
Contents:


Locking Timeout
Question: Can you think of any situations where READPAST might be useful?
Answer: Possible situations involve deleting archival rows. If they are locked, it is no problem
and they would be deleted the next time the archive deletion runs.
Lock Escalation
Question: Why do you imagine that SQL Server might find escalating locks worthwhile?
Answer: Taking and releasing large numbers of locks is time-consuming. Sometimes it's
better to just get the work done.
What Are Deadlocks?

Question: Have you experienced deadlocking problems in your current environment? If so,
how did you determine that deadlocks were a problem, and how was it resolved?
Locking-related Table Hints

Question: Why would you ever take an exclusive table-lock?
Answer: When you know you are the only process accessing the table and that locking
overhead would slow your operation down.

Demonstration 3A: Viewing Locking Information
click Open.

4. Open the 33 – Demonstration 3A 3rd Window.sql script file.
5. Follow the instructions contained within the comments of the script files.

Review questions
Question: Why is snapshot isolation level helpful?
Answer: The most common benefit is minimizing the number of times that readers block writers.
Question: What is the difference between a shared lock and an exclusive lock?
Answer: Multiple processes can hold a shared lock on the same resource.
Question: Why would you use read committed snapshot rather than snapshot isolation level?
Answer: It avoids the need for application changes in many situations.
Best Practices
1. Always use the lowest transaction isolation level possible to avoid blocking and to avoid the chance of
deadlocks.
2. Many Microsoft-supplied components default to Serializable transactional isolation level but do not
need to be run at that level. Common examples are Component Services and BizTalk adapters.
3. Before spending too much time investigating blocking issues, make sure that all the queries that are
involved are executing quickly. This usually involves making sure that appropriate indexes are in
place. Often when query performance issues are resolved, blocking issues disappear.

Question: What transaction isolation levels does SQL Server offer?
Answer: Read committed, Read uncommitted, Repeatable read, Snapshot, Serializable
Question: How does blocking differ from locking?

Answer: Locking is the mechanism used to avoid concurrency issues. Blocking is what
happens to one process while it is waiting for another process to release locks on resources.
Module 14
Handling Errors in T-SQL Code
Contents:
Lesson 1: Understanding T-SQL Error Handling 166
Lesson 2: Implementing T-SQL Error Handling 169
Lesson 3: Implementing Structured Exception Handling 172

Lesson 1
Understanding T-SQL Error Handling
Contents:


Where T-SQL Errors Occur
Question: Can you suggest a reason why you might want to catch errors in a client
application rather than allowing the errors to be seen by the end users?
Answer: The errors returned by the database engine are often too cryptic for end users to
understand.
What's in an Error?
Question: Why is it useful to be able to localize error messages?
Answer: Because users that speak different languages might need to run the same
application. Localization allows the users to see versions of the error messages in their own
languages.
Demonstration 1A: Working with Error Types and Severity

Question: What do you imagine the "is_event_logged" column relates to?
Answer: Whether or not the error is automatically sent to the event log.

Demonstration 1A: Working with Error Types and Severity
Lesson 2
Implementing T-SQL Error Handling
Contents:


Raising Errors Using RAISERROR
Question: Why might you want to intentionally raise an error in your code?
Answer: When incorrect parameters are passed.
Raising Custom Errors

Question: What do the DB_ID and DB_NAME functions return?
Answer: Database ID (an integer) and Database Name.
Creating Alerts When Errors Occur

Question: Can you suggest an example of an error that would require immediate attention
from an administrator?
Answer: Out of space.
Demonstration 2A: Handling Errors Using T-SQL

Question: Why is the ability to substitute values in error messages useful?
Answer: Because you often need to know which object the error applies to.

Demonstration 2A: Handling Errors Using T-SQL
click Open.

Lesson 3
Implementing Structured Exception Handling
Contents:


TRY/CATCH Block Programming
Question: In what situation might it have been useful to be able to raise a system error?
Answer: When testing error handling code.
Catchable vs. Non-catchable Errors

Question: Given the earlier discussion on the phases of execution of T-SQL statements, how
could a syntax error occur once a batch has already started executing?
Answer: In dynamic SQL.


Demonstration 3A: Applying Retry Logic to Deadlocks
click Open.


Review questions
Question: What is the purpose of the SET XACT_ABORT ON statement?
Answer: It converts statement terminating errors to batch terminating errors.
Question: Why should retry logic be applied to deadlock handling?
Answer: Because it is desirable to stop the end user from seeing these errors if they can be handled
in code.
Question: Give an example of an error that retries would not be useful for.
Answer: Primary key violation.
Best Practices
When designing client-side database access code, do not assume that database operations will always
occur without error. Instead of a pattern like:
• Start a transaction
• Do some work
• Commit the transaction
Consider instead a pattern like:
• Reset the retry count
• While the transaction is not committed and the retry count is not exhausted, attempt to perform the
work and commit the transaction.
• If an error occurs and it is an error that retries could apply to, retry. Otherwise, return the error to the
calling code.

Question: Why do we need to test for transaction state in a CATCH block?
Answer: Because we need to rollback the transaction only if there is one that is doomed.
Question: Why do we insert a delay within the retry logic for a deadlock?
Answer: To give the situation that caused the deadlock time to clear.
Module 15
Responding to Data Manipulation via Triggers
Contents:
Lesson 1: Designing DML Triggers 178
Lesson 2: Implementing DML Triggers 180
Lesson 3: Advanced Trigger Concepts 184

Lesson 1
Designing DML Triggers
Contents:

What Are DML Triggers?
Question: Why would you choose to use a DML trigger instead of a constraint?
Answer: Triggers allow for more complex logic than is possible in the definition of a
constraint.
AFTER Triggers vs. INSTEAD OF Triggers

Question: Why would the ability to run alternate code help to allow views with multiple base
tables to be updatable?
Answer: INSERT, UPDATE and DELETE statements only permit modifications to a single table
in a single statement.
Lesson 2
Implementing DML Triggers
Contents:


AFTER INSERT Triggers
Question: When would you use an INSERT trigger?
Answer: Prompt the students for scenarios. As an example, you may suggest a trigger that
checks to make sure the credit rating for the vendor is good when an attempt is made to
insert a new purchase order into the PurchaseOrderHeader table. To obtain the credit rating
of the vendor corresponding to the purchase order that was just inserted, the Vendor table
must be referenced and joined with the inserted table. If the credit rating is too low, a
message is displayed and the insertion does not execute.
AFTER DELETE Triggers

Question: What performance and archival considerations should you think about when
planning how to handle deleted records?
Answer: Is it often better for performance and archival to simply mark records as inactive by
using a column reserved for that purpose. You can use an instead of trigger to mark records
as inactive when a delete statement is executed. You can then schedule regular archival to
remove those records when necessary. This also makes it easier for applications to undo
accidental deletions and ensures that business rules which require archival of information are
easily upheld. In addition, you can enforce primary key uniqueness when uniqueness also
applies to archived records (for example, instead of creating a duplicate record to what is in
the archive when rehiring a vendor, you can simply restore the record to active status.)
AFTER UPDATE Triggers

Question: When would you imagine you might use an UPDATE trigger in your own coding?
Answer: Will depend on background experience of students but maintaining columns like
the one shown in the example is a very common usage.

Demonstration 2A: Working with AFTER INSERT Triggers
Demonstration 2B: Working with AFTER DELETE Triggers

click Open.
Demonstration 2C: Working with AFTER UPDATE Triggers

click Open.
Lesson 3
Advanced Trigger Concepts
Contents:


INSTEAD OF Triggers
Question: What sort of situations would lead you to need to execute different statements to
the data modification statements requested?
Answer: Prompt the students for scenarios from their organizations and how they could
implement INSTEAD OF triggers. The most common response is that they would use them to
allow for updatable views. Another option would be that in a view, an aggregate might be
formed from multiple columns. This could allow the underlying columns to be updated.
Demonstration 3A: Working with INSTEAD OF Triggers

Question: Why does the DELETE succeed when INSERT and UPDATE fail?
Answer: Because the row is contained within a single table and only accessed by the
CustomerID
How Nested Triggers Work

Question: How might nested triggers work in an Employee database?
Answer: There are multiple scenarios where multiple tables may be updated using nested
triggers. You might suggest what happens when an employee is hired or promoted and how
that impacts benefits, contact details, payroll, etc.
Considerations for Recursive Triggers

Question: Think of a database containing genealogy data. How might a recursive trigger be
used when a relationship between two people is corrected (such as from child and parent to
grandchild and grandparent, with an intermediate generation inserted)?
Answer: A recursive UPDATE trigger can be used to keep the parentID column up-to-date as
new records are inserted. The INSERT trigger ParentID column of the child record, which
recursively updates the parents column of other records down the hierarchy

Demonstration 3A: Working with INSTEAD OF Triggers
click Open.
Demonstration 3B: Replacing Triggers with Computed Columns


click Open.

Review questions
Question: How do constraints and triggers differ regarding timing of execution?
Answer: Constraints fire before data modification. AFTER triggers fire after the data modification.
Question: Why would you use the UPDATE function rather than the COLUMNS_UPDATED function
when designing a trigger?
Answer: UPDATE() allows you to specify columns by name.
Best Practices
1. In many business scenarios, it makes sense to mark records as deleted with a status column and use a
trigger or stored procedure to update an audit trail table. The changes can then be audited, the data
is not lost, and the IT staff can perform purges or archival of the deleted records.
2. Avoid using triggers in situations where constraints could be used instead.


Question: What advantages does the use of triggers for auditing provide over other
options?
Answer: Triggers can be written to implement complex logic.
Question: What did you need to specify as well as the trigger’s name when altering it?
Answer: The schema name.

Module 16
Implementing Managed Code in SQL Server® 2012
Contents:
Lesson 1: Introduction to SQL CLR Integration 190
Lesson 2: Importing and Cataloging Assemblies 193
Lesson 3: Implementing SQL CLR Integration 196

Lesson 1
Introduction to SQL CLR Integration
Contents:


Options for Extending SQL Server
Question: Are there any aspects of the Database Engine that you would like to extend?
Answer: Answers will vary but many will suggest a desire to extend the tools in SQL Server
Management Studio

Demonstration 1A: Choosing Appropriate Use Cases for Managed Code
and T-SQL
4. Open the 11 – Demonstration 1A Example 1.txt file.

Lesson 2
Importing and Cataloging Assemblies
Contents:


Assembly Permission Sets
Question: Which permission set should be rarely allowed?
Answer: UNSAFE
Demonstration 2A: Importing and Cataloging an Assembly

Question: Of the three trust levels, the UNSAFE level is the least protected. What situations
can you think of that would warrant the risk of using this trust level?
Answer: A good example would be the need to access COM-based code. (The SQL Server
Spatial assembly does this). Only in very rare circumstances should this be permitted and
with a very solid justification.

Demonstration 2A: Importing and Cataloging an Assembly
click Open.
Lesson 3
Implementing SQL CLR Integration
Contents:


Table-valued User-defined Functions
Question: How could a TVF be used with environment variables?
Answer: It could return a table of environment variable names and their current values.
Stored Procedures – External Access

Question: What would be a good use case for stored procedures in managed code?
Answer: Will vary but examples would be external access of the filesystem (listing files in a
folder), reading or writing file data (output an XML file to a folder), retrieve environment
variable values.
User-defined Aggregates
Question: Can you think of another common mathematical aggregate that would be useful
in SQL Server?
Answer: Answer will vary but an example would be MODE


Demonstration 3A: Creating User-defined Functions
click Open.
• Open the 21 – Demonstration 2A.sql script file and execute steps 1 to 3.

Demonstration 3B: Creating Stored Procedures and Triggers

click Open.
Demonstration 3C: Creating Aggregates and User-defined Data Types


click Open.

Review questions
Question: Which types of database objects can be implemented using managed code?
Answer: User-defined functions (scalar and table-valued), stored procedures, triggers (DML and DDL),
user-defined aggregates, user-defined data types.
Question: What purpose do attributes have in CLR managed code?
Answer: They can relate to performance, correctness or deployment.
Best Practices
1. The biggest mistake made when deciding between T-SQL and Managed Code is to assume that either
one is the correct answer for every situation. Each has benefits and limitations and should be used for
the appropriate tasks.
2. Developers should avoid implementing using SQL CLR to implement code that would be better
placed on another application tier (such as on a client system).
3. DBAs should avoid refusing to allow SQL CLR code without consideration. As you have seen in this
module, there is code that should be implemented in Managed Code rather than in T-SQL.
4. DBAs should set boundaries for developers:

• No row-based code that should be set-based T-SQL operations.
• Limited use of EXTERNAL_ACCESS permissions and only after justification.
• Rare use of UNSAFE permissions and only after very serious justifications and testing.

Question: Suggest some other potential uses for user-defined data types.
Answer: Answers will vary but interesting options would be jpeg data type, chemical data
types, audio data types, etc.
Module 17
Storing XML Data in SQL Server® 2012
Contents:
Lesson 1: Introduction to XML and XML Schemas 203
Lesson 2: Storing XML Data and Schemas in SQL Server 206
Lesson 3: Implementing XML Indexes 209

Lesson 1
Introduction to XML and XML Schemas
Contents:


Core XML Concepts
Question: With no knowledge apart from the document above, what would you imagine it
contains details of?
Answer: Explain that a key distinction of XML is that it is self-describing. Note that you can
infer most of what the document is about directly from the document itself.
Question: Do you use XML for exchanging data between your organization and another
organization?
Answer: Answers will vary but a good example would be if the organization interchanges
data using BizTalk Server.
Fragments vs. Documents

Question: How could the XML fragment shown in the slide be converted to an XML
document?
Answer: Add a root element.
XML Namespaces
Question: Why do you imagine that aliases are typically used with namespaces?
Answer: To avoid having to repeat the details of the entire namespace in each element.
Demonstration 1A: Using XML and XML Schemas

Question: What would the likely problem be if NULL elements are simply omitted?
Answer: If you were trying to decide what the schema of the document was, you might not
realize that the element even exists, depending upon which rows of data you happen to have
included.

Demonstration 1A: Using XML and XML Schemas
Lesson 2
Storing XML Data and Schemas in SQL Server
Contents:


Untyped vs. Typed XML
Question: What types of errors could occur if the XML data you were querying did not
follow the schema you were expecting?
Answer: A variety of errors can occur but an example would be that elements that you were
expecting to be present might not be present.
Demonstration 2A: Working with Typed vs. Untyped XML

Question: What is the difference between an XML SCHEMA COLLECTION and an XML
schema?
Answer: An XML SCHEMA COLLECTION is a set of XML schemas. XML data that is validated
by the XML SCHEMA COLLECTION must meet the requirements of at least one of the XML
schemas contained in the XML SCHEMA COLLECTION.

Demonstration 2A: Working with Typed vs. Untyped XML
click Open.
Lesson 3
Implementing XML Indexes
Contents:


Demonstration 3A: Implementing XML Indexes
Question: How does SQL Server enforce the requirement that a primary XML index must be
created before a secondary XML index can be created?
Answer: The name of the primary XML index needs to be specified when creating a
secondary XML index.

Demonstration 3A: Implementing XML Indexes
click Open.

Review questions
Question: What is XML?
Answer: XML is a plain-text, unicode-based meta-language. It is a language for defining markup

languages. It is not tied to any particular programming language, operating system or software
vendor. XML provides access to a wide variety of technologies for manipulating, structuring,
transforming and querying data.
Question: How are NULL elements represented in an XML document?
Answer: They are omitted.
Question: What is the difference between an element and an attribute?
Answer: An attribute is a property of an element.
Best Practices
1. Use appropriate data types for your database columns. Do not store all your data in XML columns.
2. Use XML schemas only when required. Validating data against schemas incurs substantial processing
overhead.
3. Ensure you have at least basic XML proficiency when working with SQL Server, even if you will be
working primarily in database administration.
4. Index XML data stored in database columns. Use the appropriate type of index for the types of
queries expected.

Question: What is the purpose of an XML schema?
Answer: The purpose of an XML schema is to defined the allowed structure of an XML
document.
Question: When would you use untyped XML?
Answer: Use untyped XML data type when you do not have a schema for your XML data or
you do not wish the server to validate the data against the schema.
Question: When would you use typed XML?
Answer: Use typed XML data type when you have schemas for your data and you want the
server to validate the data against the schema.
Module 18
Querying XML Data in SQL Server® 2012
Contents:
Lesson 1: Using the T-SQL FOR XML Statement 215
Lesson 2: Getting Started with XQuery 218
Lesson 3: Shredding XML 221

Lesson 1
Using the T-SQL FOR XML Statement
Contents:


Introducing the FOR XML clause
Question: Why would sending XML data to a supplier be more useful than sending text files
with fixed width columns?
Answer: The XML data is self-describing – you often would not need to also send
documentation or additional files that would be required with text files. Also, if you add
additional elements into the XML at a later time, existing systems that work with the previous
format might not need to be altered.
Using PATH Mode Queries

Question: What does an @ symbol relate to in an XQuery?
Answer: An attribute.
Retrieving Nested XML

Question: Why is hyperlinking of XML columns in SSMS useful?
Answer: It makes it easy to open an XML editor for the data contained in the link.
Demonstration 1A: Using FOR XML Queries

Question: When should you use EXPLICIT mode in an XQuery?
Answer: Rarely and only when using PATH mode is not flexible enough.

Demonstration 1A: Using FOR XML Queries
Lesson 2
Getting Started with XQuery
Contents:


What Is XQuery?
Question: Why do you think it is important to learn XPath query language?
Answer: XPath is a language for managing XML documents. It provides the ability to
navigate XML documents, and manage the XML nodes using a variety of criteria. Learning
this language is important for administrators to help them developing comprehensive
queries of XML document.
exist() Method
Question: Why would the exist() method outperform the value() method?
Answer: It can exit and return a value as soon as an element or attribute is found. No data
conversions are necessary.
Demonstration 2A: Using XQuery Methods in a DDL Trigger

Question: Suggest an example of where the ability to create triggers on DDL statements
would be useful.
Answer: To avoid the execution of undesirable DDL statements. For example, to prevent
unintentional changes to the master database.

Demonstration 2A: Using XQuery Methods in a DDL Trigger
click Open.
Lesson 3
Shredding XML
Contents:


nodes() Method
Question: When would it make sense to use OPENXML rather than the nodes() method for
shredding XML data to relational format?
Answer: When the XML document needs to be processed many times within a batch.

Demonstration 3A: Shredding XML
click Open.

Review questions
Question: What are AUTO mode queries?
Answer: AUTO mode gives you more control over the returned XML than RAW mode. It generates
nesting in the resulting XML where necessary, based on the SELECT statement supplied.
Question: What are PATH mode queries?
Answer: PATH mode is a simpler way to introduce additional nesting for representing complex
properties.
Question: What does the nodes() method do?
Answer: The nodes() method shreds XML data into relational data.
Question: What are RAW mode queries?
Answer: The RAW mode generates a single <row> element per row in the rowset that is returned by
a SELECT statement.
Best Practices
1. Convert existing code that uses the nvarchar data type for XML parameters to use the XML data type.
2. Provide meaningful row names when using RAW mode by using the optional name parameter to the
RAW clause.
3. Check the query plans for queries using the nodes() method to ensure that the lack of cardinality
estimates is not producing a poor execution plan.

Question: XML data could be passed to a stored procedure using either the XML data type
or the nvarchar data type. What advantage does the XML data type provide over the
nvarchar data type for this purpose?
Answer: You already know that the data is well-formed XML. This simplifies the level of
error-checking that you would otherwise need to perform.
Question: Which XML query mode did you use for implementing the
WebStock.GetAvailableModelsAsXML stored procedure?
Answer: RAW mode

Module 19
Working with SQL Server® 2012 Spatial Data
Contents:
Lesson 1: Introduction to Spatial Data 227
Lesson 2: Working with SQL Server Spatial Data Types 230
Lesson 3: Using Spatial Data in Applications 233

Lesson 1
Introduction to Spatial Data
Contents:


Types of Spatial Data
Question: Which existing SQL Server data type could be used to store (but not directly
process) raster data?
Answer: varbinary(max)
Planar vs. Geodetic

Question: What is the difference between an ellipsoid and a sphere?
Answer: A sphere is one type of ellipsoid. Other ellipsoids are more like squashed spheres.

Demonstration 1A: Viewing Available Spatial Reference Systems
Lesson 2
Working with SQL Server Spatial Data Types
Contents:


SQL Server Spatial Data
Question: You may have used a web service to calculate the coordinates of an address. What
is this process commonly called?
Answer: Geocoding. Bing provides a geocoding service.
Spatial Data Formats

Question: Why is there a need to represent spatial data types as strings within SQL Server?
Answer: Because there are no literal geometry or geography formats.


Demonstration 2A: Working with Spatial Data Types
click Open.
Lesson 3
Using Spatial Data in Applications
Contents:


Performance Issues in Spatial Queries
Question: What is the challenge in locating the intersecting streets?
Answer: The list of streets might be very large.
Question: Which streets would you need to check?

Answer: All streets unless there was some way of narrowing them down.
Question: How could you minimize this problem?
Answer: By eliminating any street that does not come near the target area, then only
checking those that are in the vicinity.

Demonstration 3A: Using Spatial Data in Applications
click Open.

Review questions
Question: What is the main difference between the geometry and geography data types?
Answer: geometry is for flat-Earth calculations, geography is for round-Earth.
Question: Why does the order of points matter when defining a polygon?
Answer: Because anti-clockwise returns the inner area, clockwise returns the outside area.
Best Practices
1. Set the SRID for geometry objects to 0 to ensure that operations on multiple geometry objects can
always be performed.
2. Use a CHECK CONSTRAINT to ensure that the SRID values for a column are consistent across all rows.
3. Before creating spatial indexes, make sure that the queries that need to be executed against the data
use predicate forms that are supported by the types of index you are creating.

Question: Where would you imagine you might use spatial data in your own business
applications?
Answer: Spatial data will be a completely new concept for many students. Answers will vary
depending upon their backgrounds and experience levels.
Module 20
Working with Full-Text Indexes and Queries
Contents:
Lesson 1: Introduction to Full-Text Indexing 239
Lesson 2: Implementing Full-Text Indexes in SQL Server 242
Lesson 3: Working with Full-Text Queries 245

Lesson 1
Introduction to Full-Text Indexing
Contents:


Discussion: The Need for More Flexible User Interaction
Question: Why aren't interfaces to business systems built like that?
Answer: Mostly because our searching tools haven't been capable of providing appropriate
answers.
Why LIKE Isn’t Enough

Question: What is really needed to find the word Pen?
Answer: An understanding of words.
Question: What would you need to know to be able to find words rather than substrings?
Answer: You would at least need to have an understanding of the language and its
punctuation.
Question: Is LIKE case sensitive?

Answer: It depends upon the collation settings.

Demonstration 1A: Using Full-Text Queries
Lesson 2
Implementing Full-Text Indexes in SQL Server
Contents:


Language Support and Supported Word Breakers
Question: Which of the languages in the list do you need to support in your applications or
databases?


Demonstration 2A: Implementing Full-Text Indexes
click Open.
Lesson 3
Working with Full-Text Queries
Contents:


Table Functions and Ranking Results
Question: Would a search engine be more likely to use the table forms of these or the
predicate forms?
Answer: Table forms as ranking is important.


Demonstration 3A: Working with Full-Text Queries
click Open.

Review questions
Question: What is the function of a stopword?
Answer: They avoid indexing commonly used words that do not add value to the index.
Question: What are iFilters used for?
Answer: They are used to extract a stream of text from a document.
Question: What is the difference between FREETEXT and FREETEXTTABLE?
Answer: The first is a predicate used in a WHERE clause. The second returns a table of results and can
include ranking of the results.
Question: How do you configure a thesaurus for use with full-text indexing?
Answer: You need to edit an XML file on the server.
Best Practices
1. Create a stoplist for your company. Add to the stoplist, any words that are used in almost all your
company documents.
2. Use auto-population of indexes except in rare cases with specific issues. (These situations would
typically involve data that is updated at a high rate and where the index does not need to be kept
completely up to date).
3. Try to encourage developers in your organization to offer much more flexible user interfaces to your
end users, based on full-text indexes in SQL Server.

Question: What sorts of values would be useful in stoplists?
Answer: Answers will vary but it should be values that offer no usefulness in the index, often
as they are repeated in most rows.
Question: What sorts of values would be useful in a thesaurus?
Answer: Terms that are commonly used interchangeably within the organization.
Send Us Your Feedback

You can search the Microsoft Knowledge Base for known issues at Microsoft Help and Support before
submitting feedback. Search using either the course number and revision, or the course title.
Note Not all training products will have a Knowledge Base article – if that is the case,
please ask your instructor whether or not there are existing error log entries.
Courseware Feedback
Send all courseware feedback to support@mscourseware.com. We truly appreciate your time and effort.
We review every e-mail received and forward the information on to the appropriate team. Unfortunately,
because of volume, we are unable to provide a response but we may use your feedback to improve your
future experience with Microsoft Learning products.
Reporting Errors
When providing feedback, include the training product name and number in the subject line of your e-
mail. When you provide comments or report bugs, please include the following:
4. Document or CD part number

10. Page number or location
11. Complete description of the error or suggested change
Please provide any details that are necessary to help us verify the issue.
Important All errors and suggestions are evaluated, but only those that are validated are
added to the product Knowledge Base article.

10776A ENU Companion

Uploaded by

Document Informationclick to expand document information10776A Companion Content

Document Informationclick to expand document information

Copyright:

Available Formats

10776A ENU Companion

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

10776A ENU Companion

Uploaded by

Copyright:

Available Formats

O F F I C I A L M I C R O S O F T L E A R N I N G P R O D U C T

Product Number: 10776A

Part Number: X18-28011

Lesson 2: Working with SQL Server Tools 10

Lesson 3: Configuring SQL Server Services 15

Module Reviews and Takeaways 18

Lab Review Questions and Answers 19

Question and Answers

SQL Server Editions

SQL Server Versions

Answer: Answers will vary by student.

Detailed Demonstration Step 12

Question and Answers

Answer: When troubleshooting query performance or when designing indexing strategies

Demonstration 2B: Using SQL Server Data Tools

Answer: Monthly or weekly sales reports.

Detailed Demonstration Steps

SELECT * FROM Production.Product ORDER BY ProductID;

36. Close SQL Server Management Studio.

37. Click No in the SQL Server Management Studio window.

Demonstration 2B: Using SQL Server Data Tools

SELECT ProductID, Name, Color, Size FROM Production.Product ORDER BY ProductID;

9. In the Select the Report Type window, click Next.

Demonstration 2C: Using Books Online

SELECT SUBSTRING('test string',2,7);

Detailed Demonstration Step 17

Question and Answers

Demonstration 3A: Using SQL Server Profiler

Answer: Performance tuning, workload testing, upgrade testing.

Detailed Demonstration Steps

Module Reviews and Takeaways

Question: What is the purpose of the SQL Server Data Tools?

Question: Does Visual Studio need to be installed before SSDT?

Lab Review Questions and Answers

Question: How can SQL Server be configured to use a different IP port?

Lesson 2: Working with Character Data 24

Lesson 3: Converting Data Types 27

Lesson 4: Working with Specialized Data Types 30

Module Reviews and Takeaways 32

Lab Review Questions and Answers 33

Detailed Demonstration Step 23

Question and Answers

Exact Numeric Data Types

Answer: smallint (note that tinyint cannot be negative)

Date and Time Data Types

Answer: Much larger storage and much slower comparisons, etc.

NULL or NOT NULL Columns

Detailed Demonstration Steps

Detailed Demonstration Step 26

Question and Answers

Answer: Code page 850, case insensitive and accent sensitive.

Detailed Demonstration Steps

• Revert the virtual machine as per the instructions in D:\10776A_Labs\Revert.txt.

2. Open the 21 – Demonstration 2A.sql script file.

Detailed Demonstration Step 29

Question and Answers

Implicit Data Conversion

Detailed Demonstration Steps