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
6232B Lab Instructions and Lab Answer Key: Implementing a Microsoft® SQL Server® 2008 R2 Database
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. © 2011 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: 6232B Part Number : X17-52339 Released: 03/2011
Lab Instructions: Introduction to SQL Server 2008 R2 and its Toolset
Module 1 Lab Instructions: Introduction to SQL Server 2008 R2 and its Toolset Contents: Exercise 1: Verify SQL Server Component Installation
4
Exercise 2: Alter Service Accounts for New Instance
5
Exercise 3: Enable Named Pipes Protocol for Both Instances
6
Exercise 4: Create Aliases for AdventureWorks and Proseware
7
Challenge Exercise 5: Ensure SQL Browser is Disabled and Configure a Fixed TCP/IP Port (Only if time permits)
8
1
2
Lab Instructions: Introduction to SQL Server 2008 R2 and its Toolset
Lab 1: Introduction to SQL Server and its Toolset
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V window. In the Virtual Machines list, right-click 623XB-MIA-DC and click Start. Right-click 623XB-MIA-DC and click Connect. In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, then close the Virtual Machine Connection window. 6. In Hyper-V Manager, in the Virtual Machines list, right-click 623XB-MIA-SQL1 and click Start. 7. Right-click 623XB-MIA-SQL1 and click Connect. 8. In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. 9. On the Action menu, click the Ctrl+Alt+Delete menu item. 10. Click Switch User, then click Other User. 11. Log on using the following credentials: 1. 2. 3. 4. 5.
• •
User name: AdventureWorks\Administrator Password: Pa$$w0rd
12. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 13. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window.
Lab Scenario AdventureWorks is a global manufacturer, wholesaler and retailer of cycle products. The owners of the company have decided to start a new direct marketing arm of the company. It has been created as a new company named Proseware, Inc. Even though it has been set up as a separate company, it will receive
Lab Instructions: Introduction to SQL Server 2008 R2 and its Toolset
some IT-related services from the existing AdventureWorks company and will be provided with a subset of the corporate AdventureWorks data. The existing AdventureWorks company SQL Server platform has been moved to a new server that is capable of supporting both the existing workload and the workload from the new company. In this lab, you are ensuring that the additional instance of SQL Server has been configured appropriately and making a number of additional required configuration changes.
3
4
Lab Instructions: Introduction to SQL Server 2008 R2 and its Toolset
Exercise 1: Verify SQL Server Component Installation A new instance of SQL Server has been installed by the IT department at AdventureWorks. It will be used by the new direct marketing company. The SQL Server named instance is called MKTG. In the first exercise, you need to verify that the required SQL Server components have been installed. The main tasks for this exercise are as follows: 1. 2. 3.
Check that Database Engine and Reporting Services have been installed for the MKTG instance. Note the services that are installed for the default instance and that Integration Services is not installed on a per instance basis. Ensure that all required services including SQL Server Agent are started and set to autostart for both instances.
Task 1: Check that Database Engine and Reporting Services have been installed for the MKTG instance •
Open SQL Server Configuration Manager.
•
Check the installed list of services for the MKTG instance and ensure that the database engine and Reporting Services have been installed for the MKTG instance.
Task 2: Note the services that are installed for the default instance and that Integration Services is not installed on a per instance basis •
Note the list of services that are installed for the default instance.
•
Note that Integration Services has no instance name shown as it is not installed on a per-instance basis.
Task 3: Ensure that all required services including SQL Server Agent are started and set to autostart for both instances •
Ensure that all the MKTG services are started and set to autostart. (Ignore the Full Text Filter Daemon at this time).
•
Ensure that all the services for the default instance are set to autostart. (Ignore the Full Text Filter Daemon at this time). Results: After this exercise, you have checked that the required SQL Server services are installed, started, and configured to autostart.
Lab Instructions: Introduction to SQL Server 2008 R2 and its Toolset
Exercise 2: Alter Service Accounts for New Instance Scenario The SQL Server services for the MKTG instance have been configured to execute under the AdventureWorks\SQLService service account. In this exercise, you will configure the services to execute under the AdventureWorks\PWService service account. The main tasks for this exercise are as follows: 1. 2. 3.
Change the service account for the MKTG database engine. Change the service account for the MKTG SQL Server Agent. Change the service account for the MKTG Reporting Services service.
Task 1: Change the service account for the MKTG database engine •
Change the service account for the MKTG database engine service to AdventureWorks\PWService using the properties page for the service.
Task 2: Change the service account for the MKTG SQL Server Agent •
Change the service account for the MKTG SQL Server Agent service to AdventureWorks\PWService using the properties page for the service and then restart the service. Results: After this exercise, you have configured the service accounts for the MKTG instance.
5
6
Lab Instructions: Introduction to SQL Server 2008 R2 and its Toolset
Exercise 3: Enable Named Pipes Protocol for Both Instances Scenario Client applications that are installed on the server will connect to the database engine using the named pipes protocol. In this exercise, you will enable the named pipes protocol for both database engine instances. The main tasks for this exercise are as follows: 1. 2. 3.
Enable the named pipes protocol for the default instance. Enable the named pipes protocol for the MKTG instance. Restart database engine services for both instances.
Task 1: Enable the named pipes protocol for the default instance •
Enable the named pipes protocol for the default database engine instance using the Protocols window.
Task 2: Enable the named pipes protocol for the MKTG instance •
Enable the named pipes protocol for the MKTG database engine instance using the Protocols window.
Task 3: Restart both database engine services •
Restart the default database engine instance
•
Restart the MKTG database engine instance
•
Check to ensure that both instances have been restarted successfully Results: After this exercise, you should have enabled the named pipes protocol for both database engine instances.
Lab Instructions: Introduction to SQL Server 2008 R2 and its Toolset
Exercise 4: Create Aliases for AdventureWorks and Proseware Scenario Client applications that are installed on the server will use aliases to connect to the database engine services. In this exercise, you will configure aliases for both the default instance (AdventureWorks) and for the MKTG instance (Proseware). Both 32-bit and 64-bit aliases should be configured. You will use SQL Server Management Studio to test the aliases once they have been configured. The main tasks for this exercise are as follows: 1. 2. 3. 4. 5.
Create a 32-bit alias (AdventureWorks) for the default instance. Create a 32-bit alias (Proseware) for the MKTG instance. Create a 64-bit alias (AdventureWorks) for the default instance. Create a 64-bit alias (Proseware) for the MKTG instance. Use SQL Server Management Studio to connect to both aliases to ensure they work as expected.
Task 1: Create a 32-bit alias (AdventureWorks) for the default instance •
Create a 32-bit alias for the default instance. Call the alias AdventureWorks and connect via named pipes. Use the servername ".".
Task 2: Create a 32-bit alias (Proseware) for the MKTG instance •
Create a 32-bit alias for the MKTG instance. Call the alias Proseware and connect via named pipes. Use the servername ".\MKTG".
Task 3: Create a 64-bit alias (AdventureWorks) for the default instance •
Create a 64-bit alias for the default instance. Call the alias AdventureWorks and connect via named pipes. Use the servername ".".
Task 4: Create a 64-bit alias (Proseware) for the MKTG instance •
Create a 64-bit alias for the MKTG instance. Call the alias Proseware and connect via named pipes. Use the servername ".\MKTG".
Task 5: Use SQL Server Management Studio to connect to both aliases to ensure they work as expected •
Open SQL Server Management Studio.
•
Connect to the Proseware alias.
•
In Object Explorer, connect also to the AdventureWorks alias. Results: After this exercise, you should have created and tested aliases for both database engine instances.
7
8
Lab Instructions: Introduction to SQL Server 2008 R2 and its Toolset
Challenge Exercise 5: Ensure SQL Browser is Disabled and Configure a Fixed TCP/IP Port (Only if time permits) Scenario Client applications will need to connect to the MKTG database engine instance via the TCP/IP protocol. As their connections will need to traverse a firewall, the port used for connections cannot be configured as a dynamic port. The port number must not change. Corporate policy at AdventureWorks is that named instances should be accessed via fixed TCP ports and the SQLBrowser service should be disabled. In this exercise, you will make configuration changes to comply with these requirements. A firewall exception has already been created for port 51550, for use with the MKTG database engine instance. The main tasks for this exercise are as follows: 1.
Configure the TCP port for the MKTG database engine instance to 51550. •
Disable the SQLBrowser service.
Task 1: Configure the TCP port for the MKTG database engine instance to 51550 •
Using the property page for the TCP/IP server protocol, configure the use of the fixed port 51550. (Make sure that you clear the dynamic port)
•
Restart the MKTG database engine instance.
•
Ensure that the MKTG database engine instance has been restarted successfully.
Task 2: Disable the SQLBrowser service •
Stop the SQLBrowser service.
•
Set the Start Mode for the SQL Browser service to Disabled. Results: After this exercise, you will have configured a fixed TCP port for the MKTG database engine instance and disabled the SQLBrowser service.
Lab Instructions: Working with Data Types
Module 2 Lab Instructions: Working with Data Types Contents: Exercise 1: Choosing Appropriate Data Types
6
Exercise 2: Writing Queries With Data Type Conversions
7
Challenge Exercise 3: Designing and Creating Alias Data Types (Only if time permits)
8
1
2
Lab Instructions: Working with Data Types
Lab 2: Working with Data Types
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager.
2.
Maximize the Hyper-V Manager window.
3.
In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started:
4.
•
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
5.
In Virtual Machine Connection window, click on the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete.
7.
In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
Lab Instructions: Working with Data Types
•
Click Switch User, and then click Other User.
•
Log on using the following credentials: i.
User name: AdventureWorks\Administrator
ii.
Password: Pa$$w0rd
8.
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode.
9.
If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window.
10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_02_PRJ\6232B_02_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario A new developer has sought your assistance in deciding which data types to use for three new tables she is designing. She presents you with a list of organizational data requirements for each table. You need to decide on appropriate data types for each item. You also need to export some data from your existing system but while being exported, some of the columns need to be converted to alternate data types. If you have time, there is another issue that your manager would like you to address. She is concerned about a lack of consistency in the use of data types across the organization. At present, she is concerned about email addresses and phone numbers. You need to review the existing data types being used in the MarketDev database for this and create new data types that can be used in applications, to avoid this inconsistency.
Supporting Documentation Table 1: PhoneCampaign Description Which campaign this relates to. The prospect that was contacted. When contact was first attempted with the prospect. Comments related to the contact that was made, if it was made.
3
4
Lab Instructions: Working with Data Types
When contact was actually made with the prospect. Outcome of the contact: sale, later follow-up, or no interest Value of any sale made (up to 2 decimal places)
Table 2: Opportunity Description Name of the opportunity Which prospect this opportunity relates to Stage the sale is at: Lead, Qualification, Proposal Development, Contract Negotiations, Complete, Lost Date that the opportunity was raised Probability of success Rating: Cold, Warm, Hot Estimated closing date Estimated revenue Delivery address
Table 3: SpecialOrder Description Which prospect this order is for External supplier of the item Description of the item Quantity Required (some quantities are whole numbers, some are fractional with up to three decimal places) Date of order Promised delivery date
Lab Instructions: Working with Data Types
Actual delivery date Special requirements (any comments related to the special order) Quoted price per unit (up to two decimal places)
Query Requirement 1: A list of products from the Marketing.Product table that are no longer sold, that is they have a SellEndDate. The output should show ProductID, ProductName, and SellEndDate formatted as a string based on the following format: YYYYMMDD. The output should appear similar to:
Query Requirement 2: A list of products from the Marketing.Product table that have demographic information. The output should show ProductID, ProductName, and Demographics formatted as nvarchar(1000) instead of XML. The output should appear similar to:
5
6
Lab Instructions: Working with Data Types
Exercise 1: Choosing Appropriate Data Types Scenario In this exercise, a new developer has sought your assistance in deciding which data types to use for three new tables she is designing. She presents you with a list of organizational data requirements for each table. You need to decide on appropriate data types for each item. The main tasks for this exercise are as follows:
1.
Determine column names and data types
Task 1: Determine column names and data types •
Review the supporting documentation for details of the PhoneCampaign, Opportunity, and SpecialOrder tables and determine column names and data types for each data item in the design. Results: After this exercise, you should have determined the columns names and data types for the following tables: PhoneCampaign, Opportunity, and SpecialOrder.
Lab Instructions: Working with Data Types
Exercise 2: Writing Queries With Data Type Conversions Scenario In this exercise, you need to export some data from your existing system. While being exported, some of the columns need to be converted to alternate data types. The main tasks for this exercise are as follows: 1.
Connect to the MarketDev Database
2.
Review the first query requirement and write a SELECT statement to meet the requirement.
3.
Review the second query requirement and write a SELECT statement to meet the requirement.
Task 1: Connect to the MarketDev Database •
Open a new query window against the MarketDev database.
Task 2: Review the first query requirement and write a SELECT statement to meet the requirement •
Review the supporting documentation for details for the first query requirement.
•
Write a SELECT statement that returns the required data. The output should look similar to the supplied sample.
Task 3: Review the second query requirement and write a SELECT statement to meet the requirement •
Review the supporting documentation for details for the second query requirement.
•
Write a SELECT statement that returns the required data. The output should look similar to the supplied sample. Results: After this exercise, you should have created two new SELECT statements as per the design requirements.
7
8
Lab Instructions: Working with Data Types
Challenge Exercise 3: Designing and Creating Alias Data Types (Only if time permits) Scenario In this exercise, your manager is concerned about a lack of consistency in the use of data types across the organization. At present, she is concerned about email addresses and phone numbers. You need to review the existing data types being used in the MarketDev database for this and create new data types that can be used in applications, to avoid this inconsistency. The main tasks for this exercise are as follows: 1.
Investigate the storage of phone numbers and email addresses
2.
Create a data type to be used to store phone numbers
3.
Create a data type to be used to stored email addresses
Task 1: Investigate the storage of phone numbers and email addresses •
Investigate how phone numbers and email addresses have been stored in the MarketDev database.
Task 2: Create a data type that stores phone numbers •
Create a data type to be used to store phone numbers.
Task 3: Create a data type that stores email addresses •
Create a data type to be used to stored email addresses. Results: After this exercise, you should have created two new data types that store phone numbers and email addresses.
Lab Instructions: Designing and Implementing Tables
Module 3 Lab Instructions: Designing and Implementing Tables Contents: Exercise 1: Improve the Design of Tables
6
Exercise 2: Create a Schema
7
Challenge Exercise 3: Create the Tables (Only if time permits)
8
1
2
Lab Instructions: Designing and Implementing Tables
Lab 3: Designing and Implementing Tables
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager.
2.
Maximize the Hyper-V Manager window.
3.
In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started:
4.
•
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
5.
In Virtual Machine Connection window, click on the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete.
7.
In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
Lab Instructions: Designing and Implementing Tables
•
Click Switch User, and then click Other User.
•
Log on using the following credentials: i. ii.
User name: AdventureWorks\Administrator Password: Pa$$w0rd
8.
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode.
9.
If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window.
10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_03_PRJ\6232B_03_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario A business analyst from your organization has provided you with a first-pass at a schema design for some new tables being added to the MarketDev database. You need to provide an improved schema design based on good design practices and an appropriate level of normalization. The business analyst was also confused about when data should be nullable. You need to decide about nullability for each column in your improved design. The new tables need to be isolated in their own schema. You need to create the required schema DirectMarketing. The owner of the schema should be dbo. When the schema has been created, if you have available time, you need to create the tables that have been designed.
Supporting Documentation Proposed Schema Table1: Competitor Name
Data Type
CompetitorCode
nvarchar(6)
Name
varchar(30)
Address
varchar(max)
Date_Entered
varchar(10)
Strength_of_competition
varchar(8)
3
4
Lab Instructions: Designing and Implementing Tables
Name
Data Type
Comments
varchar(max)
Table2: TVAdvertisement Name
Data Type
TV_Station
nvarchar(15)
City
nvarchar(25)
CostPerAdvertisement
float
TotalCostOfAllAdvertisements
float
NumberOfAdvertisements
varchar(4)
Date_Of_Advertisement_1
varchar(12)
Time_Of_Advertisement_1
int
Date_Of_Advertisement_2
varchar(12)
Time_Of_Advertisement_2
int
Date_Of_Advertisement_3
varchar(12)
Time_Of_Advertisement_3
int
Date_Of_Advertisement_4
varchar(12)
Time_Of_Advertisement_4
int
Date_Of_Advertisement_5
varchar(12)
Time_Of_Advertisement_5
int
Table3: Campaign_Response Name
Data Type
ResponseOccurredWhen
datetime
RelevantProspect
int
RespondedHow
varchar(8) (phone, email, fax, letter)
ChargeFromReferrer
float
RevenueFromResponse
float
Lab Instructions: Designing and Implementing Tables
Name
Data Type
ResponseProfit
float (revenue less charge)
5
6
Lab Instructions: Designing and Implementing Tables
Exercise 1: Improve the Design of Tables Scenario A business analyst from your organization has provided you with a first-pass at a schema design for some new tables being added to the MarketDev database. You need to provide an improved schema design based on good design practices and an appropriate level of normalization. The business analyst was also confused about when data should be nullable. You need to decide about nullability for each column in your improved design. The main tasks for this exercise are as follows: 1. 2.
Review the supplied design Suggest an improved design
Task 1: Review the supplied design •
Review the supplied design in the supporting documentation for the exercise
Task 2: Suggest an improved design •
Provide recommendations on how to improve the schema design Results: After this exercise, you have provided an improved schema design based on good design practices.
Lab Instructions: Designing and Implementing Tables
Exercise 2: Create a Schema Scenario The new tables need to be isolated in their own schema. You need to create the required schema DirectMarketing. The owner of the schema should be dbo. The main tasks for this exercise are as follows: 1. 2.
Connect to the MarketDev Database Create a schema named DirectMarketing
Task 1: Connect to the MarketDev Database •
Connect to the MarketDev Database
Task 2: Create a schema named DirectMarketing •
Create a schema named DirectMarketing with dbo as the owner Results: After this exercise, you should have created a new Direct Marketing schema.
7
8
Lab Instructions: Designing and Implementing Tables
Challenge Exercise 3: Create the Tables (Only if time permits) Scenario You need to create the tables that have been designed. The main tasks for this exercise are as follows:
1.
Create the tables that you designed in Exercise 1.
Task 1: Create the tables •
Create the tables that were designed in Exercise 1. Take into consideration the nullability of each column and each table should have a primary key. At this point there is no need to create CHECK or FOREIGN KEY constraints. Results: After this exercise, you should have created the tables that were designed in Exercise 1.
Lab Instructions: Designing and Implementing Views
Module 4 Lab Instructions: Designing and Implementing Views Contents: Exercise 1: Design, Implement and Test the WebStock Views
5
Exercise 2: Design and Implement the Contacts View
6
Challenge Exercise 3: Modify the AvailableModels View (Only if time permits)
7
1
2
Lab Instructions: Designing and Implementing Views
Lab 4: Designing and Implementing Views
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager.
2.
Maximize the Hyper-V Manager window.
3.
In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started:
4.
•
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
5.
In Virtual Machine Connection window, click on the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete.
7.
In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
Lab Instructions: Designing and Implementing Views
•
Click Switch User, and then click Other User.
•
Log on using the following credentials:
3
i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8.
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode.
9.
If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window.
10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_04_PRJ\6232B_04_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario A new web-based stock promotion system is being rolled out. Your manager is very concerned about providing access from the web-based system directly to the tables in your database. She has requested you to design some views that the web-based system could connect to instead. Details of organizational contacts are held in a number of tables. The relationship management system being used by the account management team needs to be able to gain access to these contacts. However, they need a single view that comprises all contacts. You need to design, implement and test the required view. Finally, if you have time, a request has been received from the new Marketing team that the catalog description of the product models should be added to the AvailableModels view. They would appreciate you modifying the view to provide this additional column.
Supporting Documentation View1: OnlineProducts ViewColumn
SourceColumn
ProductID
ProductID
ProductName
ProductName
ProductNumber
ProductNumber
Color
Color (note ‘N/A’ should be returned when NULL)
Availability
Based on DaysToManufacture (0 = Instock, 1 = Overnight, 2 = Fast, Other Values = Call)
4
Lab Instructions: Designing and Implementing Views
ViewColumn
SourceColumn
Size
Size
UnitOfMeasure
SizeUnitMeasureCode
Price
ListPrice
Weight
Weight
Note: Based on table Marketing.Product. Rows should only appear if the product has begun to be sold and is still being sold. (Derive this from SellStartDate and SellEndDate). View2: AvailableModels ViewColumn
SourceColumn
ProductID
ProductID
ProductName
ProductName
ProductModelID
ProductModelID
ProductModel
ProductModel
Based on tables Marketing.Product and Marketing.ProductModel. Rows should only appear if the product has at least one model, has begun to be sold and is still being sold. (Derive this from SellStartDate and SellEndDate). View3: Contacts ViewColumn
SourceColumn in Prospect
Source Column in Salesperson
ContactID
ProspectID
SalespersonID
FirstName
FirstName
FirstName
MiddleName
MiddleName
MiddleName
LastName
LastName
LastName
ContactRole
‘PROSPECT’
‘SALESPERSON’
Based on tables Marketing.Prospect and Marketing.Salesperson tables.
Lab Instructions: Designing and Implementing Views
Exercise 1: Design, Implement and Test the WebStock Views Scenario A new web-based stock promotion system is being rolled. Your manager is very concerned about providing access from the web-based system directly to the tables in your database. She has requested you to design some views that the web-based system could connect to instead. The main tasks for this exercise are as follows: 1.
Create the WebStock schema
2.
Review the design requirements
3.
Design and implement the views
4.
Test the views
Task 1: Create the WebStock schema •
In the MarketDev database create a new schema named WebStock with dbo as the owner.
Task 2: Review the design requirements •
You have been provided with the design requirements for the OnlineProducts and AvailableModels views. Review these requirements.
Task 3: Design and implement the views •
Design and implement the views
Task 4: Test the views •
Query both views to ensure they return the required data Results: After this exercise, you should have created two new views: OnlineProducts and AvailableModels, both within the WebStock schema.
5
6
Lab Instructions: Designing and Implementing Views
Exercise 2: Design and Implement the Contacts View Scenario Details of organizational contacts are held in a number of tables. The relationship management system being used by the account management team needs to be able to gain access to these contacts. However, they need a single view that comprises all contacts. You need to design, implement and test the required view. 1.
Create the Relationship schema
2.
Review the design requirements
3.
Design and implement the view
4.
Test the view
Task 1: Create the Relationship schema •
In the MarketDev database create a new schema named Relationship with dbo as the owner.
Task 2: Review the design requirements •
You have been provided with the design requirements for the Contacts view. Review these requirements.
Task 3: Design and implement the view •
Design and implement the view
Task 4: Test the view •
Query the view to ensure it returns the required data Results: After this exercise, you should have created a new Contacts view within a new Relationship schema.
Lab Instructions: Designing and Implementing Views
7
Challenge Exercise 3: Modify the AvailableModels View (Only if time permits) Scenario A request has been received from the new Marketing team that the catalog description of the product models should be added to the AvailableModels view. You need to now modify the view to provide this additional column. The new column should be called CatalogDescription and should be taken from the ProductDescription table. Multiple descriptions can exist for each model. If an English description exists (based on the LanguageID ‘en’), it should be returned. If no English description exists, the invariant language description (based on a blank string for LanguageID) should be returned. If no descriptions exist, the column should be null. 1.
Alter the AvailableModels view to add the CatalogDescription column.
2.
Test the view
Task 1: Alter the AvailableModels View •
Use the ALTER VIEW statement to change the view to suit the new requirements as described in the Exercise 3 scenario above.
Task 2: Test the view •
Query the view to ensure it now returns the required data Results: After this exercise, you should have modified the AvailableModels view and it should return the new CatalogDescription column.
Lab Instructions: Planning for SQL Server 2008 R2 Indexing
Module 5 Lab Instructions: Planning for SQL Server 2008 R2 Indexing Contents: Exercise 1: Explore existing index statistics
5
Challenge Exercise 2: Design column orders for indexes (Only if time permits) 7
1
2
Lab Instructions: Planning for SQL Server 2008 R2 Indexing
Lab 5: Planning for SQL Server Indexing
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager.
2.
Maximize the Hyper-V Manager window.
3.
In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started:
4.
•
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
5.
In Virtual Machine Connection window, click on the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete.
7.
In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
Lab Instructions: Planning for SQL Server 2008 R2 Indexing
•
Click Switch User, and then click Other User.
•
Log on using the following credentials: i.
User name: AdventureWorks\Administrator
ii.
Password: Pa$$w0rd
8.
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode.
9.
If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window.
10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_05_PRJ\6232B_05_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario You have been asked to explain the concept of index statistics and selectivity to a new developer. You will explore the statistics available on an existing index and determine how selective some sample queries would be. One of the company developers has provided you with a list of the most important queries that will be executed by the new marketing management system. Depending upon how much time you have available, you need to determine the best column orders for indexes to support each query. Complete as many as possible within the allocated time. In later modules, you will consider how these indexes would be implemented. Each query is to be considered in isolation in this exercise.
Supporting Documentation Query 1: SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE ProspectID = 12553;
Query 2: SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName LIKE 'Arif%';
Query 3: SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName LIKE 'Alejandro%'
3
4
Lab Instructions: Planning for SQL Server 2008 R2 Indexing
ORDER BY LastName, FirstName;
Query 4: SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName >= 'S' ORDER BY LastName, FirstName;
Query 5: SELECT LanguageID, COUNT(1) FROM Marketing.ProductDescription GROUP BY LanguageID;
Lab Instructions: Planning for SQL Server 2008 R2 Indexing
5
Exercise 1: Explore existing index statistics Scenario You have been asked to explain the concept of index statistics and selectivity to a new developer. You will explore the statistics available on an existing index and determine how selective some sample queries would be. The main tasks for this exercise are as follows: 1.
Execute the following command in the MarketDev database:
EXEC sp_helpstats ‘Marketing.Product’
2.
Review the results. Have any autostats been generated?
3.
Create manual statistics on the Color column. Call the statistics Product_Color_Stats. Use a full scan of the data when creating the statistics.
4.
Re-execute the command from task 1 to see the change.
5.
Using the DBCC SHOW_STATISTICS command, review the created Product_Color_Stats statistics.
6.
Answer the following questions related to the Product_Color_Stats statistics:
7.
a.
How many rows were sampled?
b.
How many steps were created?
c.
What was the average key length?
d.
How many Black products are there?
Execute the following command to check how accurate the statistics that have been generated are:
SELECT COUNT(1) FROM Marketing.Product WHERE Color = 'Black';
8.
Calculate the selectivity of each of the three queries shown: a)
SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName LIKE 'A%';
b)
SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName LIKE 'Alejandro%';
c)
SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName LIKE 'Arif%';
Task 1: Execute SQL Command 1.
Execute the following command in the MarketDev database:
EXEC sp_helpstats ‘Marketing.Product’
Task 2: Review the results •
Review the results.
•
Check to see if any autostats has been generated?
Task 3: Create statistics •
Create manual statistics on the Color column. Call the statistics Product_Color_Stats. Use a full scan of the data when creating the statistics.
6
Lab Instructions: Planning for SQL Server 2008 R2 Indexing
Task 4: Re-execute the SQL command from task 1 •
Re-execute the following command in the MarketDev database:
EXEC sp_helpstats ‘Marketing.Product’
Task 5: Use DBCC SHOW_STATISTICS •
Using the DBCC SHOW_STATISTICS command, review the created Product_Color_Stats statistics
Task 6: Answer questions •
Answer the following questions related to the Product_Color_Stats statistics: a.
How many rows were sampled?
b.
How many steps were created?
c.
What was the average key length?
d.
How many Black products are there?
Task 7: Execute SQL Command and check accuracy of statistics •
Execute the following command to check how accurate the statistics that have been generated are:
SELECT COUNT(1) FROM Marketing.Product WHERE Color = 'Black';
Task 8: Calculate Selectivity of each query •
Calculate the selectivity of each of the three queries shown:
Query 1: SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName LIKE 'A%';
Query 2: SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName LIKE 'Alejandro%';
Query 3: SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName LIKE 'Arif%';
Results: After this exercise, you have assessed Selectivity on each various queries.
Lab Instructions: Planning for SQL Server 2008 R2 Indexing
Challenge Exercise 2: Design column orders for indexes (Only if time permits) Scenario One of the company developers has provided you with a list of the most important queries that will be executed by the new marketing management system. You need to determine the best column orders for indexes to support each query. In later modules, you will consider how these indexes would be implemented. Each query is to be considered in isolation in this exercise. The main tasks for this exercise are as follows: 1. 2. 3. 4. 5.
Determine which columns should be part of an index for Query1 and the best order for the columns to support the query. Determine which columns should be part of an index for Query2 and the best order for the columns to support the query. Determine which columns should be part of an index for Query3 and the best order for the columns to support the query. Determine which columns should be part of an index for Query4 and the best order for the columns to support the query. Determine which columns should be part of an index for Query5 and the best order for the columns to support the query.
Task 1: Design an index •
Review the supporting documentation, determine which columns should be part of an index for Query 1 and the best order for the columns to support the query.
Task 2: Design an index •
Review the supporting documentation, determine which columns should be part of an index for Query 2 and the best order for the columns to support the query.
Task 3: Design an index •
Review the supporting documentation, determine which columns should be part of an index for Query 3 and the best order for the columns to support the query.
Task 4: Design an index •
Review the supporting documentation, determine which columns should be part of an index for Query 4 and the best order for the columns to support the query.
Task 5: Design an index •
Review the supporting documentation, determine which columns should be part of an index for Query 5 and the best order for the columns to support the query. Results: After this exercise, you should designed new indexes taking into consideration of Selectivity
7
Lab Instructions: Implementing Table Structures in SQL Server 2008 R2
Module 6 Lab Instructions: Implementing Table Structures in SQL Server 2008 R2 Contents: Exercise 1: Creating Tables as Heaps
5
Exercise 2: Creating Tables with Clustered Indexes
6
Challenge Exercise 3: Comparing the Performance of Clustered Indexes vs. Heaps (Only if time permits)
7
1
2
Lab Instructions: Implementing Table Structures in SQL Server 2008 R2
Lab 6: Implementing Table Structures in SQL Server
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager.
2.
Maximize the Hyper-V Manager window.
3.
In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started:
4.
•
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
5.
In Virtual Machine Connection window, click on the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete.
7.
In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
Lab Instructions: Implementing Table Structures in SQL Server 2008 R2
•
Click Switch User, and then click Other User.
•
Log on using the following credentials: i. ii.
User name: AdventureWorks\Administrator Password: Pa$$w0rd
8.
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode.
9.
If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window.
10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_06_PRJ\6232B_06_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario One of the most important decisions when designing a table is to choose an appropriate table structure. In this lab, you will choose an appropriate structure for some new tables required for the relationship management system.
Supporting Documentation Table 1: Relationship.ActivityLog Name
Data Type
ActivityTime
datetimeoffset
SessionID
int
Duration
int
ActivityType
int
Constraint
Table 2: Relationship.PhoneLog Name
Data Type
Constraint
PhoneLogID
int
Primary Key
SalespersonID
int
CalledPhoneNumber
nvarchar(16)
CallDurationSeconds
int
3
4
Lab Instructions: Implementing Table Structures in SQL Server 2008 R2
Table 3: Relationship.MediaOutlet Name
Data Type
MediaOutletID
int
MediaOutletName
nvarchar(40)
PrimaryContact
nvarchar(50)
City
nvarchar(50)
Constraint
Table 4: Relationship.PrintMediaPlacement Name
Data Type
Constraint
PrintMediaPlacementID
int
Primary Key
MediaOutletID
int
PlacementDate
datetime
PublicationDate
datetime
RelatedProductID
int
PlacementCost
decimal(18,2)
Table 5: Name
Data Type
Constraint
ApplicationID
int
IDENTITY(1,1)
ApplicantName
nvarchar(150)
EmailAddress
nvarchar(100)
ReferenceID
uniqueidentifier
Comments
nvarchar(500)
Lab Instructions: Implementing Table Structures in SQL Server 2008 R2
Exercise 1: Creating Tables as Heaps Scenario You need to create some new tables to support the relationship management system. You will create two tables that are structured as heaps. The main tasks for this exercise are as follows: 1.
Review the Requirements.
2.
Create the Tables in the MarketDev database.
Task 1: Review the Requirements •
Review the requirements in the supporting documentation for Table 1 and 2.
Task 2: Create the Tables in the MarketDev database •
Create a table based on the supporting documentation for Table 1.
•
Create a table based on the supporting documentation for Table 2. Results: After this exercise, you have created two tables that are structured as Heaps.
5
6
Lab Instructions: Implementing Table Structures in SQL Server 2008 R2
Exercise 2: Creating Tables with Clustered Indexes Scenario The design documentation also calls for some tables with clustered indexes. You will then create two tables that have clustered indexes. The main tasks for this exercise are as follows: 1.
Review the Requirements.
2.
Create the Tables in the MarketDev database.
Task 1: Review the Requirements •
Review the requirements in the supporting documentation for Table 3 and 4.
Task 2: Create the Tables in the MarketDev database •
Create a table based on the supporting documentation for Table 3.
•
Create a table based on the supporting documentation for Table 4. Results: After this exercise, you have created two tables that have clustered indexes.
Lab Instructions: Implementing Table Structures in SQL Server 2008 R2
7
Challenge Exercise 3: Comparing the Performance of Clustered Indexes vs. Heaps (Only if time permits) Scenario A company developer has approached you to decide whether a new table should have a clustered index or not. Insert performance of the table is critical. You will consider the design, create a number of alternatives and compare the performance of each against a set of test workloads. The main tasks for this exercise are as follows: 1.
Review the Design for Table 5.
2.
Create a table based on the design with no clustered index. Call the table Relationship.Table_Heap.
3.
Create a table based on the design with a clustered index on the ApplicantID column. Call the table Relationship.Table_ApplicationID.
4.
Create a table based on the design with a clustered index on the EmailAddress column. Call the table Relationship.Table_EmailAddress.
5.
Create a table based on the design with a clustered index on the ReferenceID column. Call the table Relationship.Table_ReferenceID.
6.
Load and execute the workload script. (Note: this may take some minutes to complete. You can check where it is up to by viewing the Messages tab. A message is printed as each of the four sections is completed. While the script is running, review the contents of the script and estimate the proportion of time difference you expect to see in the results).
7.
Compare the performance of each table structure.
Task 1: Review the Table Design •
Review the table design in the supporting documentation for Table 5.
Task 2: Create the Relationship.Table_Heap Table •
In the supporting documentation for Table5, create a table based on the design with no clustered index.
•
Call the table Relationship.Table_Heap
Task 3: Create the Relationship.Table_ApplicationID Table •
In the supporting documentation for Table5, create a table based on the design with a clustered index on the ApplicantID column.
•
Call the table Relationship.Table_ApplicationID
Task 4: Create the Relationship.Table_EmailAddress Table •
In the supporting documentation for Table5, create a table based on the design with a clustered index on the EmailAddress column.
•
Call the table Relationship.Table_EmailAddress.
8
Lab Instructions: Implementing Table Structures in SQL Server 2008 R2
Task 5: Create the Relationship.Table_ReferenceID Table •
In the supporting documentation for Table5, create a table based on the design with a clustered index on the ReferenceID column.
•
Call the table Relationship.Table_ReferenceID.
Task 6: Load and Execute the Workload Script •
Load and execute the workload script. (Note: this may take some minutes to complete. You can check where it is up to by viewing the Messages tab. A message is printed as each of the four sections is completed. While the script is running, review the contents of the script and estimate the proportion of time difference you expect to see in the results).
Task 7: Compare Table Performance •
Compare the performance of each table structure Results: After this exercise, you have created four tables compare performance between clustered and non-clustered indexes.
Lab Instructions: Reading SQL Server 2008 R2 Execution Plans
Module 7 Lab Instructions: Reading SQL Server 2008 R2 Execution Plans Contents: Exercise 1: Actual vs. Estimated Plans
4
Exercise 2: Identify Common Plan Elements
6
Challenge Exercise 3: Query Cost Comparison (Only if time permits)
8
1
2
Lab Instructions: Reading SQL Server 2008 R2 Execution Plans
Lab 7: Reading SQL Server Execution Plans
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager.
2.
Maximize the Hyper-V Manager window.
3.
In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started:
4.
•
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
5.
In Virtual Machine Connection window, click on the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete.
7.
In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
Lab Instructions: Reading SQL Server 2008 R2 Execution Plans
•
Click Switch User, and then click Other User.
•
Log on using the following credentials: i. ii.
3
User name: AdventureWorks\Administrator Password: Pa$$w0rd
8.
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode.
9.
If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window.
10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_07_PRJ\6232B_07_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario You have been learning about the design of indexes. To take this learning further, you need to have a way to view how these indexes are used. In the first exercise, you will learn to view both estimated and actual execution plans. Execution plans can contain many types of elements. In the second exercise, you will learn to identify the most common plan elements and see how statements lead to these elements being used. You regularly find yourself trying to decide between different ways of structuring SQL queries. You are concerned that you aren’t always choosing the highest-performing options. If time permits, you will learn to use execution plans to compare the cost of statements in multi-statement batches.
4
Lab Instructions: Reading SQL Server 2008 R2 Execution Plans
Exercise 1: Actual vs. Estimated Plans Scenario In the first exercise, you will learn to view both estimated and actual execution plans. The main tasks for this exercise are as follows: 1.
Load the test script.
2.
Generate an estimated execution plan for script 7.1.
3.
View the estimated execution plan for script 7.2 using SHOWPLAN_XML.
4.
Generate the actual execution plan for script 7.3.
5.
Try to generate an estimated execution plan for script 7.4
6.
Review the actual execution plan for script 7.4.
7.
Review the execution plans currently cached in memory using script 7.5.
Task 1: Load the test script •
Load the 51 – Lab Exercise 1.sql script from Solution Explorer.
•
Change the database context to AdventureWorks2008R2.
Task 2: Generate an estimated execution plan for script 7.1 •
Generate an estimated plan for script 7.1
Task 3: View the estimated execution plan for script 7.2 using SHOWPLAN_XML •
Execute script 7.2 in SQL Server Query Analyzer.
•
Click on the returned XML and view the execution plan.
•
Right-click in the whitespace in the plan.
•
Choose Show Execution Plan XML.
•
Briefly review the XML.
•
Close the XML window and the execution plan window.
Task 4: Generate the actual execution plan for script 7.3 •
Enable the option to include actual plans, then execute script 7.3. Note the returned execution plan tab and note that the plan is identical from the previous task.
Task 5: Try to generate an estimated execution plan for script 7.4 •
Request an estimated plan for script 7.4.
•
Note the inability to create an estimated plan the reason is shown in the messages tab.
Task 6: Review the actual execution plan for script 7.4 •
Execute script 7.4 and note the returned plan
Task 7: Review the execution plans currently cached in memory using script 7.5 •
Execute script 7.5 to view the plans currently cached in memory
Lab Instructions: Reading SQL Server 2008 R2 Execution Plans
Results: After this exercise, you have reviewed various actual and estimated query plans.
5
6
Lab Instructions: Reading SQL Server 2008 R2 Execution Plans
Exercise 2: Identify Common Plan Elements Scenario Execution plans can contain many types of elements. You will learn to identify the most common plan elements and see how statements lead to these elements being used. The main tasks for this exercise are as follows: 1.
Load the test script
2.
Explain the actual execution plan from script 7.6
3.
Explain the actual execution plan from script 7.7
4.
Explain the actual execution plan from script 7.8
5.
Explain the actual execution plan from script 7.9
6.
Explain the actual execution plan from script 7.10
7.
Explain the actual execution plan from script 7.11
8.
Explain the actual execution plan from script 7.12
9.
Explain the actual execution plan from script 7.13
10. Explain the actual execution plan from script 7.14
Task 1: Load the test script •
Load the 61 – Lab Exercise 2.sql script from Solution Explorer.
•
Change the database context to AdventureWorks2008R2.
•
Select the option to include actual execution plans from the Query menu.
Task 2: Explain the actual execution plan from script 7.6 •
Execute script 7.6.
•
Explain the plan returned based upon the existing table structure.
Task 3: Explain the actual execution plan from script 7.7 •
Execute script 7.7.
•
Explain the plan returned based upon the existing table structure.
Task 4: Explain the actual execution plan from script 7.8 •
Execute script 7.8.
•
Explain the plan returned based upon the existing table structure.
Task 5: Explain the actual execution plan from script 7.9 •
Execute script 7.9.
•
Explain the plan returned based upon the existing table structure.
Task 6: Explain the actual execution plan from script 7.10 •
Execute script 7.10.
•
Explain the plan returned based upon the existing table structure.
Lab Instructions: Reading SQL Server 2008 R2 Execution Plans
Task 7: Explain the actual execution plan from script 7.11 •
Execute script 7.11.
•
Compare the plan to the one returned by script 7.10.
•
Suggest a reason for the difference in plan, where the queries are almost identical. Also note the green Missing Index warning.
Task 8: Explain the actual execution plan from script 7.12 •
Execute script 7.12.
•
Explain the plan returned based upon the existing table structure.
Task 9: Explain the actual execution plan from script 7.13 •
Execute script 7.13.
•
Compare the plan to the one returned by script 7.12.
•
Suggest a reason for the difference in plan, where the queries are very similar.
Task 10: Explain the actual execution plan from script 7.14 •
Execute script 7.14.
•
Note the difference in this plan from the plan for script 7.12. Results: After this exercise, you will have analyzed the most common plan elements returned from queries.
7
8
Lab Instructions: Reading SQL Server 2008 R2 Execution Plans
Challenge Exercise 3: Query Cost Comparison (Only if time permits) Scenario You regularly find yourself trying to decide between different ways of structuring SQL queries. You are concerned that you aren’t always choosing the highest-performing options. You will learn to use execution plans to compare the cost of statements in multi-statement batches. The main tasks for this exercise are as follows: 1.
Load the test script
2.
Explain the actual execution plan from script 7.15.
Task 1: Load the test script •
Load the 71 – Lab Exercise 3.sql script from Solution Explorer.
•
Change the database context to AdventureWorks2008R2.
•
Select the option to include actual execution plans from the Query menu.
Task 2: Explain the actual execution plan from script 7.15 •
Execute script 7.15 as a single batch (both queries should be executed together).
•
Explain the execution plan that is returned. In particular, explain the relationship between the two query plans. Results: After this exercise, you have used execution plans to compare the cost of statements in multistatement batches.
Lab Instructions: Improving Performance through Nonclustered Indexes
Module 8 Lab Instructions: Improving Performance through Nonclustered Indexes Contents: Exercise 1: Nonclustered index usage review
5
Exercise 2: Improving nonclustered index designs
6
Exercise 3: SQL Server Profiler and Database Engine Tuning Advisor
7
Challenge Exercise 4: Nonclustered index design (Only if time permits)
8
1
2
Lab Instructions: Improving Performance through Nonclustered Indexes
Lab 8: Improving Performance through Nonclustered Indexes
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
Lab Instructions: Improving Performance through Nonclustered Indexes
•
3
Log on using the following credentials:
i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_08_PRJ\6232B_08_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario The marketing system includes a query that is constantly executed and is performing too slowly. It retrieves 5000 web log entries beyond a given starting time. Previously, a non-clustered index was created on the SessionStart column. When 100 web log entries were being retrieved at a time, the index was being used. The developer is puzzled that changing the request to 5000 entries at a time has caused SQL Server to ignore the index he built. You need to investigate the query and suggest the best non-clustered index to support the query. You will then test your suggestion. After you have created the new index, the developer noted the cost of the sort operation and tried to create another index that would eliminate the sort. You need to explain to him why SQL Server has decided not to use this index. Later you will learn to set up a basic query tuning trace in SQL Server Profiler and use the trace captured in Database Engine Tuning Advisor. If time permits, you will design a required nonclustered index.
Supporting Documentation Query 1: Query to test DECLARE @StartTime datetime2 = '2010-08-30 16:27'; SELECT TOP(5000) wl.SessionID, wl.ServerID, wl.UserName FROM Marketing.WebLog AS wl WHERE wl.SessionStart >= @StartTime ORDER BY wl.SessionStart, wl.ServerID;
Query 2: Index Design CREATE INDEX IX_WebLog_Perf_20100830_B ON Marketing.WebLog (ServerID, SessionStart) INCLUDE (SessionID, UserName);
Query 3: Query to review SELECT PostalCode, Country
4
Lab Instructions: Improving Performance through Nonclustered Indexes
FROM Marketing.PostalCode WHERE StateCode = 'KY' ORDER BY StateCode, PostalCode;
Lab Instructions: Improving Performance through Nonclustered Indexes
5
Exercise 1: Nonclustered index usage review Scenario The marketing system includes a query that is constantly executed and is performing too slowly. It retrieves 5000 web log entries beyond a given starting time. Previously, a non-clustered index was created on the SessionStart column. When 100 web log entries were being retrieved at a time, the index was being used. The developer is puzzled that changing the request to 5000 entries at a time has caused SQL Server to ignore the index he built. You need to investigate the query and suggest the best non-clustered index to support the query. You will then test your suggestion. The main tasks for this exercise are as follows: 1. 2. 3.
4.
Review the query. Review the existing Index and Table structures. Design a more appropriate index. Test your design.
Task 1: Review the query •
Review the Query 1 in the supporting documentation.
Task 2: Review the existing Index and Table structures •
Review the existing Index and Table structures.
Task 3: Design a more appropriate index •
Design a more appropriate index.
Task 4: Test your design •
In the supporting documentation, use Query 1 to test your new index. Results: After this exercise, you have created a non-clustered index.
6
Lab Instructions: Improving Performance through Nonclustered Indexes
Exercise 2: Improving nonclustered index designs Scenario After you have created the new index, the developer noted the cost of the sort operation and tried to create another index that would eliminate the sort. Explain why SQL Server has decided not to use this index: The main tasks for this exercise are as follows: 1. 2. 3.
Review the index design. Implement the index. Test the design and explain why the index was not used.
Task 1: Review the index design •
In Query 2 in the supporting documentation, review the index design.
Task 2: Implement the index •
Create the index as per the index design.
Task 3: Test the design and explain why the index was not used •
Enable Include Actual Execution Plan.
•
Execute the query.
•
Review the Execution Plan and explain why the index was not used. Results: After this exercise, you have understood why some indexes are not appropriate in some scenerios.
Lab Instructions: Improving Performance through Nonclustered Indexes
Exercise 3: SQL Server Profiler and Database Engine Tuning Advisor Scenario Query 3 is another important query. You need to investigate the query and suggest the best nonclustered index to support the query. You will then test your suggestion. The main tasks for this exercise are as follows: 1. 2. 3. 4.
Review the query. Review the existing Index and Table structures. Design a more appropriate index by following the Missing Index suggestion. Create a better index that removes the sort operation. If you create another index, confirm that SQL Server selects it.
Task 1: Review the query •
Review Query 3 in the supporting documentation.
Task 2: Review the existing Index and Table structures •
Review the existing Index and Table structures.
Task 3: Design a more appropriate index by following the Missing Index suggestion •
Review and implement the Missing Index that SQL Server has suggested.
•
Test to ensure that the new index is being used.
Task 4: Create a better index that removes the sort operation. If you create another index, confirm that SQL Server selects it •
Create a new index that will remove the Sort operation.
•
Test to ensure that the new index is being used. Results: After this exercise, you should have created a better index that will remove the sort operation.
7
8
Lab Instructions: Improving Performance through Nonclustered Indexes
Challenge Exercise 4: Nonclustered index design (Only if time permits) Scenario You will learn to set up a basic query tuning trace in SQL Server Profiler and to analyze use the trace captured in Database Engine Tuning Advisor. The main tasks for this exercise are as follows: 1. 2. 3.
Open SQL Server Profiler and configure and start a trace. Load and execute the workload file. Stop and analyze the trace using DTA.
Task 1: Open SQL Server Profiler and configure and start a trace •
Open SQL Server Profiler.
•
Configure it use the following: a.
Template: Tuning
b.
Save To File: should be selected and any file name provided for a file on the desktop
c.
Enable file rollover: Not selected
d.
Maximum File Size: 500MB
e.
Filter: DatabaseName LIKE MarketDev
•
Start the SQL Server Profiler Trace.
•
Disable AutoScroll from the Window Menu.
Task 2: Load and execute the workload file •
Load and execute the workload file 81 – Lab Exercise 4.sql.
Task 3: Stop and analyze the trace using DTA •
Stop the SQL Server Profiler trace.
•
Analyze the trace results using DTA.
•
Review the recommendations provided by the Database Tuning Advisor. Results: After this exercise, you should have created a SQL Server Profiler trace and analyzed the recommendations from the Database Tuning Advisor.
Lab Instructions: Designing and Implementing Stored Procedures
Module 9 Lab Instructions: Designing and Implementing Stored Procedures Contents: Exercise 1: Create stored procedures
5
Exercise 2: Create a parameterized stored procedure
6
Challenge Exercise 3: Alter the execution context of stored procedures (Only if time permits)
7
1
2
Lab Instructions: Designing and Implementing Stored Procedures
Lab 9: Designing and Implementing Stored Procedures
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
Lab Instructions: Designing and Implementing Stored Procedures
•
3
Log on using the following credentials:
i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_09_PRJ\6232B_09_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario You need to create a set of stored procedures to support a new reporting application. The procedures will be created within a new Reports schema.
Supporting Documentation Stored Procedure
Reports.GetProductColors
Input Parameters:
None
Output Parameters:
None
Output Columns:
Color (from Marketing.Product)
Output Order:
Color
Notes:
Colors should not be returned more than once in the output. NULL values should not be returned.
Stored Procedure
Reports.GetProductsAndModels
Input Parameters:
None
Output Parameters:
None
Output Columns:
ProductID, ProductName, ProductNumber, SellStartDate, SellEndDate and Color (from Marketing.Product), ProductModelID (from Marketing.ProductModel), EnglishDescription, FrenchDescription, ChineseDescription.
Output Order:
ProductID, ProductModelID
Notes:
For descriptions, return the Description column from the Marketing.ProductDescription table for the appropriate language. The
4
Lab Instructions: Designing and Implementing Stored Procedures
Stored Procedure
Reports.GetProductColors LanguageID for English is 'en', for French is 'fr' and for Chinese is 'zh-cht'. If no specific language description is available, return the invariant language description if it is present. The LanguageID for the invariant language is a blank string ''. Where neither the specific language or invariant language descriptions exist, return the ProductName instead.
Stored Procedure
Reports.GetProductsByColor
Input Parameters:
@Color (same datatype as the Color column in the Marketing.Product table)
Output Parameters:
None
Output Columns:
ProductID, ProductName, ListPrice (returned as a column named Price), Color, Size and SizeUnitMeasureCode (returned as a column named UnitOfMeasure) (from Marketing.Product)
Output Order:
ProductName
Notes:
The procedure should return products that have no Color if the parameter is NULL.
Input Parameters:
None
Output Parameters:
None
Output Columns:
Color (from Marketing.Product)
Output Order:
Color
Notes:
Colors should not be returned more than once in the output. NULL values should not be returned.
Lab Instructions: Designing and Implementing Stored Procedures
Exercise 1: Create stored procedures Scenario In this exercise, you will create two stored procedures to support one of the new reports. The main tasks for this exercise are as follows: 1. 2. 3. 4.
Review the Reports.GetProductColors stored procedure specification Design, create and test the Reports.GetProductColors stored procedure Review the Reports.GetProductsAndModels stored procedure specification Design, create and test the Reports.GetProductsAndModels stored procedure
Task 1: Review the Reports.GetProductColors stored procedure specification •
Review the Reports.GetProductColors specification in the supporting documentation
Task 2: Design, create and test the Reports.GetProductColors stored procedure •
Design, create and test the stored procedure based on the specifications given in the supporting documentation for the exercise
Task 3: Review the Reports.GetProductsAndModels stored procedure specification •
Review the second specification (Reports.GetProductsAndModels)in the supporting documentation
Task 4: Design, create and test the Reports.GetProductsAndModels stored procedure •
Design, create and test the stored procedure based on the second specifications given in the supporting documentation for the exercise Results: After this exercise, you should have created two new stored procedures. Tests should have shown that they are working as expected.
5
6
Lab Instructions: Designing and Implementing Stored Procedures
Exercise 2: Create a parameterized stored procedure Scenario In this exercise, you will create another stored procedure that takes parameters. The main tasks for this exercise are as follows: 1. 2.
Review the Reports.GetProductsByColor stored procedure specification Design, create and test the Reports.GetProductsByColor stored procedure
Task 1: Review the Reports.GetProductsByColor stored procedure specification •
Review the Reports.GetProductsByColor specification in the supporting documentation
Task 2: Design, create and test the Reports.GetProductsByColor stored procedure •
Design, create and test the Reports.GetProductsByColor stored procedure based on the specifications given in the supporting documentation for the exercise Results: After this exercise, you should have created a new stored procedure that takes parameters. Tests should have shown that it is working as expected.
Lab Instructions: Designing and Implementing Stored Procedures
Challenge Exercise 3: Alter the execution context of stored procedures (Only if time permits) Scenario In this exercise, you will alter the stored procedures to use a different execution context. The main tasks for this exercise are as follows: 1. 2. 3.
Alter the Reports.GetProductColors stored procedure to execute as OWNER. Alter the Reports.GetProductsAndModels stored procedure to execute as OWNER. Alter the Reports.GetProductsByColor stored procedure to execute as OWNER.
Task 1: Alter the Reports.GetProductColors stored procedure to execute as OWNER •
Alter the Reports.GetProductColors stored procedure to execute as OWNER and test that the procedure still works.
Task 2: Alter the Reports.GetProductsAndModels stored procedure to execute as OWNER •
Alter the Reports.GetProductsAndModels stored procedure to execute as OWNER and test that the procedure still works.
Task 3: Alter the Reports.GetProductsByColor stored procedure to execute as OWNER •
Alter the Reports.GetProductsByColor stored procedure to execute as OWNER and test that the procedure still works. Results: After this exercise, you should have altered the stored procedures to execute as OWNER. Tests should have shown that they are working as expected.
7
Lab Instructions: Merging Data and Passing Tables
Module 10 Lab Instructions: Merging Data and Passing Tables Contents: Exercise 1: Create a Table Type
4
Exercise 2: Use a Table Type Parameter
5
Challenge Exercise 3: Use a Table Type with MERGE (Only if time permits)
6
1
2
Lab Instructions: Merging Data and Passing Tables
Lab 10: Passing Tables and Merging Data
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
•
Log on using the following credentials:
Lab Instructions: Merging Data and Passing Tables
i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_10_PRJ\6232B_10_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario In earlier versions of SQL Server, passing lists of values to stored procedures was a challenge. SQL Server 2008 introduced the table type and table-valued parameters. In this lab, you will create a replacement stored procedure Reports.GetProductsByColorList_Test that uses a table-valued parameter to replace an existing stored procedure Reports.GetProductsByColorList that was based on passing a comma-delimited list of values. If time permits, you will then create a new procedure that processes complete rows of data and performs updates using the MERGE statement.
Supporting Documentation Procedure Required: Marketing.SalespersonMerge Requirements Input Parameters:
Table of Salesperson details, including SalespersonID, FirstName, MiddleName, LastName, BadgeNumber, EmailAlias, SalesTerritoryID. The parameter should be named: SalespersonDetails
Output Parameters:
None
Output Rows:
For each row, return one column called Action that contains INSERT or UPDATE and another column with the SalespersonID.
Notes:
The SalespersonID must be provided. If it matches an existing salesperson, that row should be updated. Only update columns that are provided. Any SalesTerritoryID that is provided must be valid as it is defined as a foreign key to the Marketing.SalesTerritory table.
3
4
Lab Instructions: Merging Data and Passing Tables
Exercise 1: Create a Table Type Scenario In this exercise, you will create a table type to support the parameter that will later need to be passed to the replacement stored procedure. The main tasks for this exercise are as follows: 1. 2. 3.
Review the parameters of a stored procedure Review the existing function Create a new table type
Task 1: Review the parameters of a stored procedure •
Review the parameters of the existing stored procedure Reports.GetProductsByColorList
Task 2: Review the existing function •
Review the function dbo.StringListToTable used by the existing stored procedure. Note the hardcoded length of 1000 for each component of the returned table entries
Task 3: Create a new table type •
Create a new table type to support this type of input parameter. Call the type StringList Results: After this exercise, you have created a new table type.
Lab Instructions: Merging Data and Passing Tables
5
Exercise 2: Use a Table Type Parameter Scenario In this exercise, you will create a replacement stored procedure Reports.GetProductsByColorList_Test that uses the table type for its parameter. The main tasks for this exercise are as follows: 1. 2.
Create the stored procedure Test the stored procedure
Task 1: Create the stored procedure •
Create a new stored procedure that is functionally equivalent to Reports.GetProductsByColorList except that the new procedure (call it Reports.GetProductsByColorList_Test) takes a single table @ColorList as a parameter
Task 2: Test the new procedure •
Test the new procedure Results: After this exercise, you should have created a new stored procedure that uses the table type for its parameter.
6
Lab Instructions: Merging Data and Passing Tables
Challenge Exercise 3: Use a Table Type with MERGE (Only if time permits) Scenario In this exercise, you will create a new stored procedure that takes a table-valued parameter and uses the MERGE statement to update a table in the marketing system. The procedure should allow for the creation or update of salespeople held in the Marketing.Salesperson table. The main tasks for this exercise are as follows: 1. 2. 3.
Create a new table type Create a replacement procedure Test the replacement procedure.
Task 1: Create a new table type •
Create the required table type. (Create it in the dbo schema)
Task 2: Create a replacement stored procedure •
Review the supporting documentation and create a replacement procedure based on the requirements
Task 3: Test the replacement procedure •
Test the replacement procedure Results: After this exercise, you should have created a new stored procedure that takes a table-valued parameter and uses the MERGE statement to update a table.
Lab Instructions: Creating Highly Concurrent SQL Server 2008 R2 Applications
Module 11 Lab Instructions: Creating Highly Concurrent SQL Server 2008 R2 Applications Contents: Exercise 1: Detecting Deadlocks
4
Challenge Exercise 2: Investigating Transaction Isolation Levels (Only if time permits)
5
1
2
Lab Instructions: Creating Highly Concurrent SQL Server 2008 R2 Applications
Lab 11: Creating Highly Concurrent SQL Server Applications
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
Lab Instructions: Creating Highly Concurrent SQL Server 2008 R2 Applications
•
Log on using the following credentials:
i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_11_PRJ\6232B_11_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario In this lab, you will perform basic investigation of a deadlock situation. You are trying to determine an appropriate transaction isolation level for a new application. If you have time, you will investigate the trade-off between concurrency and consistency.
3
4
Lab Instructions: Creating Highly Concurrent SQL Server 2008 R2 Applications
Exercise 1: Detecting Deadlocks Scenario In this exercise, you will explore typical causes of deadlocks and learn to view them in SQL Server Profiler traces. The main tasks for this exercise are as follows: 1. 2. 3.
Start and configure SQL Server Profiler. Load and execute the test scripts. Stop the trace and review the deadlock graph.
Task 1: Start and configure SQL Server Profiler •
Start SQL Server Profiler and create a new trace called Deadlock Detection
•
Add Deadlock Graph to the events
•
Remove all other events
•
Start the trace in Profiler
Task 2: Load and execute the test scripts •
Open the 51 – Lab Exercise 1.sql script
•
Review the script
•
Open the 52 – Lab Exercise 1 2nd Window.sql script
•
Review the script
•
Execute 51 – Lab Exercise 1.sql and then immediately execute 52 – Lab Exercise 1 2nd Window.sql. Wait for both to complete
Task 3: Stop the trace and review the deadlock graph •
Stop the trace
•
Review the deadlock graph Results: After this exercise, you have executed queries that create a deadlock situation. You will observe how this can be traced in SQL Server Profiler
Lab Instructions: Creating Highly Concurrent SQL Server 2008 R2 Applications
5
Challenge Exercise 2: Investigating Transaction Isolation Levels (Only if time permits) Scenario In this exercise, you will execute a supplied set of T-SQL scripts that demonstrate how different transaction isolation levels work. The main tasks for this exercise are as follows: 1. 2.
Load the scripts Execute the code
Task 1: Load the scripts •
Open the 62 – Lab Exercise 2 2nd Window.sql script
•
Open the 61 – Lab Exercise 2.sql script
Task 2: Execute the code •
Execute the code step by step, making sure to highlight and execute just the required code blocks in each script window, by following the step by step instructions Results: After this exercise, you will have seen how transaction isolation levels work.
Lab Instructions: Handling Errors in T-SQL Code
Module 12 Lab Instructions: Handling Errors in T-SQL Code Contents: Exercise 1: Replace @@ERROR based error handling with structured exception handling
4
Challenge Exercise 2: Add deadlock retry logic to the stored procedure (Only if time permits)
5
1
2
Lab Instructions: Handling Errors in T-SQL Code
Lab 12: Handling Errors in T-SQL Code
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
•
Log on using the following credentials:
Lab Instructions: Handling Errors in T-SQL Code
3
i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logoncheck box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_12_PRJ\6232B_12_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario In this lab, a company developer asks you for assistance with some code he is modifying. The code was written some time back and uses simple T-SQL error handling. He has heard that structured exception handling is more powerful and wishes to use it instead. If time permits, you will also design and implement changes to a stored procedure to provide for automated retry on deadlock errors.
4
Lab Instructions: Handling Errors in T-SQL Code
Exercise 1: Replace @@ERROR based error handling with structured exception handling Scenario In this exercise, you need to modify his code to use structured exception handling. The main tasks for this exercise are as follows: 1. 2. 3.
Review the existing code Rewrite the stored procedure to use structured exception handling Test that the procedure
Task 1: Review the existing code •
Review the existing code in the procedure Marketing.MoveCampaignBalance
Task 2: Rewrite the stored procedure to use structured exception handling •
Rewrite the stored procedure to use structured exception handling, calling the rewritten stored procedure Marketing.MoveCampaignBalance_Test.
Task 3: Test the stored procedure •
Test that the stored procedure still works as expected Results: After this exercise, you have created a stored procedure that uses structured exception handling.
Lab Instructions: Handling Errors in T-SQL Code
Challenge Exercise 2: Add deadlock retry logic to the stored procedure (Only if time permits) Scenario In this exercise, the operations team have mentioned that the same stored procedure also seems to routinely fail with deadlock errors. To assist them, make further modifications to your new procedure to add automatic retry code for deadlock errors. The main tasks for this exercise are as follows: 1. 2.
Modify the code to re-try on deadlock Test the stored procedure
Task 1: Modify the code to re-try on deadlock •
Modify the code for the Marketing.MoveCampaignBalance_Test stored procedure to re-try on deadlock up to five times
Task 2: Test the stored procedure •
Test that the procedure still works as expected Results: After this exercise, you have modified a stored procedure to automatically retry code for deadlock errors.
5
Lab Instructions: Designing and Implementing User-Defined Functions
Module 13 Lab Instructions: Designing and Implementing User-Defined Functions Contents: Exercise 1: Formatting Phone Numbers
5
Exercise 2: Modifying an Existing Function
6
Challenge Exercise 3: Resolve a Function-related Performance Issue (Only if time permits)
7
1
2
Lab Instructions: Designing and Implementing User-Defined Functions
Lab 13: Designing and Implementing User-Defined Functions
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
Lab Instructions: Designing and Implementing User-Defined Functions
•
Log on using the following credentials:
i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_13_PRJ\6232B_13_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario The existing marketing application includes some functions. Your manager has requested your assistance in creating a new function for formatting phone numbers. She also needs you to modify an existing function to improve its usability. Finally, if you have time, she would also like you to explore a performance-related problem with another existing function.
Supporting Documentation Function Specifications: Phone Number Function Name: FormatPhoneNumber (created in the dbo schema) Input Parameter: PhoneNumberToFormat nvarchar(16) Return Value: nvarchar(16) Rules to apply in formatting: • • • • • • •
Any phone number beginning with the international dialing code (ie: a + sign), should be left unformatted. Phone numbers that contain 10 digits should be formatted as: (XXX) XXX-XXXX Phone numbers that contain 8 digits should be formatted as: XXXX-XXXX Phone numbers that contain 7 digits should be formatted as: XXX-XXXX Phone numbers that contain 6 digits should be formatted as: XXX-XXX All other characters should be stripped out Phone numbers that have different numbers of digits should have only the digits returned ie: (9234) 2345-2342 should be returned as 923423452342.
Requirements: Comma-Delimited List Function You need to create another version of this function called dbo.IntegerListToTable that takes a commadelimited list of integers and returns a similar table. You need to design, implement and test the function. You can assume that all integers sent to the function will be eight digits or less in length. Problematic Query SELECT dbo.JoinNames(FirstName,MiddleName,LastName) AS FullName
3
4
Lab Instructions: Designing and Implementing User-Defined Functions
FROM Marketing.Prospect ORDER BY FullName;
Lab Instructions: Designing and Implementing User-Defined Functions
Exercise 1: Formatting Phone Numbers Scenario ScenarioYour manager has noticed that phone numbers that are entered into the database tend to be formatted in different ways by different users. She has asked you to create a function that will be used to format the phone numbers. You need to design, implement and test the function. The main tasks for this exercise are as follows: 1. 2. 3.
Review the requirements. Design and create the function. Test the function.
Task 1: Review the design requirements •
Review the Function Specifications: Phone Number in the supporting documentation.
Task 2: Design and create the function •
Design and create the function for reformatting phone numbers.
Task 3: Test the function •
Execute the FormatPhoneNumber function to ensure function correctly formats the phone number. Results: After this exercise, you should have created a new FormatPhoneNumber function within the dbo schema.
5
6
Lab Instructions: Designing and Implementing User-Defined Functions
Exercise 2: Modifying an Existing Function Scenario An existing function dbo.StringListToTable takes a comma-delimited list of strings and returns a table. In some application code, this causes issues with data types as the list often contains integers rather than just simple strings. The main tasks for this exercise are as follows: 1. 2. 3. 4.
Review the requirements. Design and create the function. Test the function. Test the function with an alternate delimiter such as the pipe | character.
Task 1: Review the requirements •
Review the requirement for the dbo.IntegerListToTable function in the Supporting Documentation.
Task 2: Design and create the function •
Design and create the dbo.IntegerListToTable function.
Task 3: Test the function •
Execute the dbo.IntegerListToTable function to ensure it returns the correct results.
Task 4: Test the function with an alternate delimiter such as the pipe | character •
Test the dbo.IntegerListToTable function and pass in an alternate delimiter such as the pipe | character. Results: After this exercise, you should have created a new IntegerListToTable function within a dbo schema.
Lab Instructions: Designing and Implementing User-Defined Functions
7
Challenge Exercise 3: Resolve a Function-related Performance Issue (Only if time permits) Scenario The operations team manager has approached you about a query that is performing badly. You need to investigate it and suggest changes that might improve its performance. The main tasks for this exercise are as follows: 1. 2. 3.
Review the query Design an alternate query Use SET STATISTICS TIME ON to compare the performance of the new and old queries
Task 1: Review the query •
Review the problematic query in the Supporting Documentation.
Task 2: Design an alternate query •
Design the query.
Task 3: Use SET STATISTICS TIME ON to compare the performance of the new and old queries •
Turn SET STATISTICS TIME ON.
•
Use the times returned to test how your new query compares with the original. Results: After this exercise, you should have created an alternate query for the poorly-performing query.
Lab Instructions: Ensuring Data Integrity through Constraints
1
Module 14 Lab Instructions: Ensuring Data Integrity through Constraints Contents: Exercise 1: Constraint Design
4
Challenge Exercise 2: Test the constraints (Only if time permits)
5
2
Lab Instructions: Ensuring Data Integrity through Constraints
Lab 14: Ensuring Data Integrity through Constraints
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
•
Log on using the following credentials:
Lab Instructions: Ensuring Data Integrity through Constraints
3
i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_14_PRJ\6232B_14_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario A table named Marketing.Yield has recently been added to the Marketing system in the MarketDev database but has no constraints in place. In this lab, you will implement the required constraints to ensure data integrity and, if you have time, test that constraints work as specified.
Supporting Documentation Table Marketing.Yield Note: Primary key should be a combination of ProspectID and LanguageID and should be a clustered primary key.
Column
Data Type
Mandatory Field
Validation rule
ProspectID
int
Required
Must be a valid prospect
LanguageID
nchar(6)
Required
Must be a valid language
YieldOutcome
int
Required
Default value should be zero if not supplied Must be a value between 0 and 9
RowID
uniqueidentifier
Required
Default value should be a new uniqueidentifier if not supplied Must be unique
LastUpdate
datetime2
Required
Notes
nvarchar(MAX)
Optional
Default value should be SYSDATETIME() if not supplied
4
Lab Instructions: Ensuring Data Integrity through Constraints
Exercise 1: Constraint Design Scenario You have been provided with the design for a table called Marketing.Yield. You need to alter the table with the appropriate constraints based upon the provided specifications. The main tasks for this exercise are as follows: 1. 2.
Review the supporting documentation Alter the Marketing.Yield table
Task 1: Review the supporting documentation •
Review the table design requirements supplied in the supporting documentation.
Task 2: Alter the Marketing.Yield table •
Work through the list of requirements and alter the table with appropriate constraints based on the requirements. Results: After this exercise, you should have altered the Marketing.Yield table.
Lab Instructions: Ensuring Data Integrity through Constraints
5
Challenge Exercise 2: Test the constraints (Only if time permits) Scenario You should now test each of the constraints that you designed to ensure they work as expected. The main tasks for this exercise are as follows: 1. 2. 3. 4.
Test the default values and data types. Test the primary key. Test the foreign key reference on language. Test the foreign key reference on prospect.
Task 1: Test the default values and data types •
Execute T-SQL statements to ensure that the default values and data types work as expected.
Task 2: Test the primary key •
Execute T-SQL statements to ensure that the primary key reference works as expected.
Task 3: Test the foreign key reference on language •
Execute T-SQL statements to ensure that the foreign key reference on LanguageID works as expected.
Task 4: Test the foreign key reference on prospect •
Execute T-SQL statements to ensure that the foreign key reference on ProspectID works as expected. Results: After this exercise, you should have tested and confirmed that the constraints that are functioning as expected.
Lab Instructions: Responding to Data Manipulation via Triggers
Module 15 Lab Instructions: Responding to Data Manipulation via Triggers Contents: Exercise 1: Creating and Testing the Audit Trigger
4
Challenge Exercise 2: Improve the Audit Trigger (Only if time permits)
5
1
2
Lab Instructions: Responding to Data Manipulation via Triggers
Lab 15: Responding to Data Manipulation via Triggers
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
Lab Instructions: Responding to Data Manipulation via Triggers
•
Log on using the following credentials: I. II.
8. 9. 10. 11. 12. 13. 14. 15.
User name: AdventureWorks\Administrator Password: Pa$$w0rd
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. In Connect to Server window, type Proseware in the Server name text box. In the Authentication drop-down list box, select Windows Authentication and click Connect. In the File menu, click Open, and click Project/Solution. In the Open Project window, open the project D:\6232B_Labs\6232B_15_PRJ\6232B_15_PRJ.ssmssln. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario You are required to audit any changes to data in a table that hold sensitive balance data. You have decided to implement this via DML triggers as the requirements in this case are not provided for directly by the SQL Server Audit mechanism.`
Supporting Documentation The Marketing.CampaignAudit table is used to hold audit entries. When inserting rows into this table, the data required in each column is as shown in the following table: Column
Data Type
Value to Insert
CampaignAuditID
int
IDENTITY
AuditTime
datetime2
SYSDATETIME()
ModifyingUser
sysname
ORIGINAL_LOGIN()
RemainingBalance
decimal(18,2)
RemainingBalance after update
3
4
Lab Instructions: Responding to Data Manipulation via Triggers
Exercise 1: Creating and Testing the Audit Trigger Scenario The Marketing.CampaignBalance table includes a column called RemainingBalance. Any time an update is made to the table, if either the existing balance or the new balance is greater than 10000, an entry needs to be written to the audit table Marketing.CampaignAudit. Note: Inserts or Deletes to the table do not need to be audited. Details of the current user can be taken from the function ORIGINAL_LOGIN(). The main tasks for this exercise are as follows: 1. 2. 3.
Review the supporting documentation and existing system. Design a trigger to meet the requirements as stated in the scenario for this exercise. Write code to test the behavior of the trigger.
Task 1: Review the supporting documentation and existing system •
Review the existing structure of the Marketing.CampaignAudit table and the values required in each column, based on the supporting documentation.
•
Review the existing structure of the Marketing.CampaignBalance table.
Task 2: Design a trigger to meet the requirements as stated in the scenario for this exercise •
Design and create a trigger that meets the needs identified in Task 1.
Task 3: Write code to test the behavior of the trigger •
Execute data modification statements designed to test that the trigger is working as expected.
Results: After this exercise, you should have created a new trigger. Tests should have shown that it is working as expected.
Lab Instructions: Responding to Data Manipulation via Triggers
5
Challenge Exercise 2: Improve the Audit Trigger (Only if time permits) Scenario Now that the trigger that was created in Exercise 1 has been deployed to production, the operations team is complaining that too many entries are being audited. Many accounts have more than 10000 as a balance and minor movements of money are causing audit entries. You need to modify the trigger so that only changes in the balance of more than 10000 are audited instead. The main tasks for this exercise are as follows: 1. 2. 3.
Modify the trigger based on the updated requirements. Delete all rows from the Marketing.CampaignAudit table. Test the modified trigger.
Task 1: Modify the trigger based on the updated requirements •
Review the design of the existing trigger and decide what modifications need to be made to it.
•
Use an ALTER TRIGGER statement to change the existing trigger so that it will meet the updated requirements.
Task 2: Delete all rows from the Marketing.CampaignAudit table •
Execute a DELETE statement to remove all existing rows from the Marketing.CampaignAudit table.
Task 3: Test the modified trigger •
Execute data modification statements designed to test that the trigger is working as expected.
Results: After this exercise, you should have altered the trigger. Tests should show it is now working as expected.
Lab Instructions: Implementing Managed Code in SQL Server 2008 R2
1
Module 16 Lab Instructions: Implementing Managed Code in SQL Server 2008 R2 Contents: Exercise 1: Assess Proposed CLR Code
4
Exercise 2: Implement a CLR Assembly
5
Challenge Exercise 3: Implement a CLR User-defined Aggregate and CLR User-defined Data Type (Only if time permits)
6
2
Lab Instructions: Implementing Managed Code in SQL Server 2008 R2
Lab 16: Implementing Managed Code in SQL Server
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
Lab Instructions: Implementing Managed Code in SQL Server 2008 R2
•
Log on using the following credentials: i. ii.
8. 9. 10. 11. 12. 13. 14. 15.
3
User name: AdventureWorks\Administrator Password: Pa$$w0rd
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. In Connect to Server window, type Proseware in the Server name text box. In the Authentication drop-down list box, select Windows Authentication and click Connect. In the File menu, click Open, and click Project/Solution. In the Open Project window, open the project D:\6232B_Labs\6232B_16_PRJ\6232B_16_PRJ.ssmssln. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario You are concerned that one of your company developers has decided to implement almost all of her logic in SQL CLR assemblies. You will determine if this is appropriate. Also in this lab, you will implement and test a supplied .NET assembly. You will also investigate all installed assemblies on the system.
Supporting Documentation The following list details the proposed functionality being considered for managed code. Proposed SQLCLR Functionality Table-valued function that returns a list of files in a particular folder. Function that formats phone numbers as strings. Trigger that records balance movements with a value of more than 1000. Stored procedure that writes an XML file for a given XML parameter. Function that counts rows in a table. A new Customer data type.
4
Lab Instructions: Implementing Managed Code in SQL Server 2008 R2
Exercise 1: Assess Proposed CLR Code Scenario You need to assess a list of proposed functions and determine which functions should or should not be implemented via SQL CLR logic. The main tasks for this exercise are as follows: 1. 2.
Review the supporting documentation For each object listed, determine if implementing it in managed code is appropriate or not.
Task 1: Review the supporting documentation 1.
Review the proposed list of managed code objects
Task 2: For each object listed, determine if implementing it in managed code is appropriate or not 1.
Work through the list of proposed objects and for each object, decide if it should or should not be implemented in managed code and why.
Results: After this exercise, you should have created a list of which objects should and should not be implemented in managed code and the reasons for your decision.
Lab Instructions: Implementing Managed Code in SQL Server 2008 R2
Exercise 2: Implement a CLR Assembly Scenario You have been provided with an existing .NET assembly. You will implement it within SQL Server. The main tasks for this exercise are as follows: 1. 2. 3.
Ensure the database is configured appropriately to support an EXTERNAL_ACCESS assembly. Catalog the assembly and the functions contained within it. Test the functions contained within the assembly.
Task 1: Ensure the database is configured appropriately to support an EXTERNAL_ACCESS assembly 1.
Ensure that SQL CLR integration is enabled for the SQL Server instance.
2.
Flag the MarketDev database as trustworthy.
Task 2: Catalog the assembly and the functions contained within it 1.
Use CREATE ASSEMBLY to catalog the supplied sample assembly as alias SQLCLRDemo. The path to the assembly is D:\6232B_Labs\6232B_16_PRJ\6232B_16_PRJ\SQLCLRDemo.DLL.
2.
Query the sys.assemblies and sys.assembly_files system views to confirm the details of how the assembly has been cataloged.
3.
Use the CREATE FUNCTION statement to catalog the function dbo.IsValidEmailAddress. It takes a parameter named @email of type nvarchar(4000) and returns a bit. It is found in the assembly at path: SQLCLRDemo.[SQLCLRDemo.CLRDemoClass].IsValidEmailAddress.
4.
Use the CREATE FUNCTION statement to catalog the function dbo.FormatAustralianPhoneNumber. It takes a single parameter @PhoneNumber of type nvarchar(4000). It returns nvarchar(4000). It is found in the assembly at path: SQLCLRDemo.[SQLCLRDemo.CLRDemoClass].FormatAustralianPhoneNumber.
5.
Use the CREATE FUNCTION statement to catalog the function dbo.FolderList. It takes two parameter @RequiredPath of type nvarchar(4000) and @FileMask of type nvarchar(4000). It returns a table of filenames, with one column called FileName of type nvarchar(4000). It is found in the assembly at path: SQLCLRDemo.[SQLCLRDemo.CLRDemoClass].FolderList.
Task 3: Test the functions contained within the assembly 1.
Execute the following T-SQL statements to test the functions that have been cataloged.
SELECT dbo.IsValidEmailAddress('
[email protected]'); GO SELECT dbo.IsValidEmailAddress('test.somewhere.com'); GO SELECT dbo.FormatAustralianPhoneNumber('0419201410'); SELECT dbo.FormatAustralianPhoneNumber('9 87 2 41 23'); SELECT dbo.FormatAustralianPhoneNumber('039 87 2 41 23'); GO SELECT * FROM dbo.FolderList( 'D:\6232B_Labs\6232B_16_PRJ\6232B_16_PRJ','*.txt'); GO
Results: After this exercise, you should have three functions working as expected.
5
6
Lab Instructions: Implementing Managed Code in SQL Server 2008 R2
Challenge Exercise 3: Implement a CLR User-defined Aggregate and CLR User-defined Data Type (Only if time permits) Scenario The sample assembly also includes a user-defined aggregate and a user-defined data type. You will now catalog and test these objects. The main tasks for this exercise are as follows: 1. 2.
Catalog and test the user-defined aggregate Catalog and test the user-defined data type
Task 1: Catalog and test the user-defined aggregate 1.
Use the CREATE AGGREGATE statement to catalog the user-defined aggregate dbo.AggString.
2.
dbo.AggString takes a single nvarchar(4000) parameter and returns type nvarchar(4000). It is found in the assembly at path: SQLCLRDemo.[SQLCLRDemo.AggString].
3.
Execute the following T-SQL statements to test the operation of the aggregate.
SELECT dbo.AggString(DISTINCT ProductNumber) FROM Marketing.Product WHERE Color = 'Black'; GO
Task 2: Catalog and test the user-defined data type 1.
Use the CREATE TYPE statement to catalog the user-defined data type dbo.zVarChar. It is found in the assembly at path: SQLCLRDemo.[SQLCLRDemo.zVarChar].
2.
Execute T-SQL statements similar to the following to test the operation of the data type. (The exact statements required would depend upon your table design).
CREATE TABLE dbo.TestTable ( RecID int IDENTITY(1,1), TextValue zVarChar ); INSERT INTO dbo.TestTable VALUES('Some GO
compressible
data');
SELECT TextValue.ToString(), TextValue.CompressedLength(), TextValue.CompressionPercentage() FROM dbo.TestTable; GO SELECT AVG(TextValue.CompressionPercentage()) FROM dbo.TestTable; GO DROP TABLE dbo.TestTable; GO
3.
Execute the following T-SQL statements to see how to call a method on the class itself, rather than on an instance of the class. (First, show how to call the method on an instance. Then, show how to call directly on the class so that you can see the difference).
DECLARE @TestString zVarChar; SET @TestString = 'Some compressible PRINT @TestString.CompressedValue();
value';
Lab Instructions: Implementing Managed Code in SQL Server 2008 R2
GO PRINT zVarChar::Compress( 'Some GO
compressible
value');
Results: After this exercise, you should have cataloged and tested both the user-defined aggregate and the user-defined data type.
7
Lab Instructions: Storing XML Data in SQL Server 2008 R2
Module 17 Lab Instructions: Storing XML Data in SQL Server 2008 R2 Contents: Exercise 1: Appropriate Usage of XML Data Storage in SQL Server
4
Exercise 2: Investigate the Storage of XML Data in Variables
5
Exercise 3: Investigate the use of XML Schema Collections
6
Challenge Exercise 4: Investigate the Creation of Database Columns Based on XML (Only if time permits)
7
1
2
Lab Instructions: Storing XML Data in SQL Server 2008 R2
Lab 17: Storing XML Data in SQL Server
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
Lab Instructions: Storing XML Data in SQL Server 2008 R2
•
Log on using the following credentials: I. II.
8. 9. 10. 11. 12. 13. 14. 15.
3
User name: AdventureWorks\Administrator Password: Pa$$w0rd
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. In Connect to Server window, type Proseware in the Server name text box. In the Authentication drop-down list box, select Windows Authentication and click Connect. In the File menu, click Open, and click Project/Solution. In the Open Project window, open the project D:\6232B_Labs\6232B_17_PRJ\6232B_17_PRJ.ssmssln. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario A new developer in your organization has discovered that SQL Server can store XML directly. He is keen to use this mechanism extensively. In this lab, you will decide on appropriate usage of XML within the documented application. You also have an upcoming project that will require the use of XML data within SQL Server. No members of your current team have experience working with XML data in SQL Server. You need to learn how to process XML data within SQL Server and you have been provided with some sample queries to assist with this learning.
Supporting Documentation Use Cases Use Cases Requirements Existing XML data that is stored but not processed Storing attributes for a customer Relational data that is being passed through a system but not processed within it Storing attributes that are nested (that is attributes stored within attributes)
4
Lab Instructions: Storing XML Data in SQL Server 2008 R2
Exercise 1: Appropriate Usage of XML Data Storage in SQL Server Scenario In this exercise, you will need to assess the list of use cases provided by your new developer and determine which are appropriate for XML storage in SQL Server and which are not. The main tasks for this exercise are as follows: 1. 2.
Review the list of Use Cases Determine whether each is appropriate for XML storage
Task 1: Review the list of Use Cases •
Review the list of use cases in the supporting documentation
Task 2: Determine whether each is appropriate for XML •
Determine whether the use cases are suitable for XML storage
Results: After this exercise, you have seen how to analyze requirements and determine on appropriate use cases for XML storage.
Lab Instructions: Storing XML Data in SQL Server 2008 R2
Exercise 2: Investigate the Storage of XML Data in Variables Scenario Before you can begin to work with XML data in your organization, you need to explore how XML data is stored in variables. You have been provided with a set of sample XML queries to assist with this. In this exercise, you will review the effect of executing these queries. The main tasks for this exercise are as follows: 1.
Review and execute the queries, and review the results
Task 1: Review and execute the queries, and review the results •
Review the queries, execute the queries and determine how the output results relate to the queries. Do this one query at a time for scripts 17.1 to 17.9.
Results: After this exercise, you have seen how XML data is stored in variables.
5
6
Lab Instructions: Storing XML Data in SQL Server 2008 R2
Exercise 3: Investigate the use of XML Schema Collections Scenario For some of the XML processing that you will need to perform in your upcoming project, you will need to validate XML data using XML schemas. In SQL Server, XML schemas are stored in XML schema collections. You need to investigate how these schemas are used. You have been provided with a set of sample queries to assist with this. In this exercise, you will review the effect of executing these queries. The main tasks for this exercise are as follows: 1.
Review and execute the queries, and review the results
Task 1: Review and execute the queries, and review the results •
Review the queries, execute the queries and note the output. Do this one query at a time for scripts 17.10 and 17.11.
Results: After this exercise, you have seen how to create XML schema collections.
Lab Instructions: Storing XML Data in SQL Server 2008 R2
Challenge Exercise 4: Investigate the Creation of Database Columns Based on XML (Only if time permits) Scenario The final aspect of XML data storage in SQL Server that you will need to understand for your upcoming project is how XML data is stored in tables. You need to investigate how columns of XML data type are used. You have been provided with a set of sample queries to assist with this. In this exercise, you will review the effect of executing these queries. The main tasks for this exercise are as follows: 1.
Review and execute the queries, and review the results
Task 1: Review and execute the queries, and review the results •
Review the queries, execute the queries and note the results of the queries. Do this one query at a time for scripts 17.12 to 17.20.
Results: After this exercise, you have seen how to create database columns based on the XML data type.
7
Lab Instructions: Querying XML Data in SQL Server
Module 18 Lab Instructions: Querying XML Data in SQL Server Contents: Exercise 1: Learn to query SQL Server data as XML
5
Exercise 2: Write a stored procedure returning XML
6
Challenge Exercise 3: Write a stored procedure that updates using XML (Only if time permits)
7
1
2
Lab Instructions: Querying XML Data in SQL Server
Lab 18: Querying XML Data in SQL Server
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
Lab Instructions: Querying XML Data in SQL Server
•
Log on using the following credentials: i. ii.
8. 9. 10. 11. 12. 13. 14. 15.
3
User name: AdventureWorks\Administrator Password: Pa$$w0rd
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. In Connect to Server window, type Proseware in the Server name text box. In the Authentication drop-down list box, select Windows Authentication and click Connect. In the File menu, click Open, and click Project/Solution. In the Open Project window, open the project D:\6232B_Labs\6232B_18_PRJ\6232B_18_PRJ.ssmssln. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario In this lab, you will investigate several ways in which XML data can be used in SQL Server. You will query relational data and return it as XML and also process existing XML data using T-SQL. If you have time, your manager has an additional task for you. A new web service is being added to the marketing system. You need to create a stored procedure that will query data from a table and return it as an XML value.
Supporting Documentation Stored Procedure Specifications Stored Procedure
WebStock.GetAvailableModelsAsXML
Input Parameters:
None
Output Parameters:
None
Returned Rows:
One XML document with attribute-centric XML. Root element is AvailableModels. Row element is AvailableModel. Row contains ProductID, ProductName, ListPrice, Color and SellStartDate (from Marketing.Product) and ProductModelID and ProductModel (from Marketing.ProductModel) for rows where there is a SellStartDate but not yet a SellEndDate.
Output Order:
Rows within the XML should be in order of SellStartDate ascending and then ProductName ascending. That is, sort by SellStartDate first and then ProductName within SellStartDate.
4
Lab Instructions: Querying XML Data in SQL Server
Stored Procedure: Marketing.UpdateSalesTerritoriesByXML Stored Procedure
Marketing.UpdateSalesTerritoriesByXML
Input Parameters:
@SalespersonMods xml
Output Parameters:
None
Returned Rows:
None
Actions:
Update the SalesTerritoryID column in the Marketing.Salesperson table based upon the SalesTerritoryID values extracted from the input parameter.
Incoming XML object format:
Lab Instructions: Querying XML Data in SQL Server
Exercise 1: Learn to query SQL Server data as XML Scenario In this exercise, you have decided to learn to query SQL Server data to return XML. You will review and execute scripts that demonstrate the most important FOR XML querying techniques. The main tasks for this exercise are as follows: 1.
Review and execute the query and review the results
Task 1: Review and execute the query and review the results •
Review the query, execute the query and review the results for scripts 18.1 to 18.9
Results: After this exercise, you have executed queries that return SQL Server relational data as XML.
5
6
Lab Instructions: Querying XML Data in SQL Server
Exercise 2: Write a stored procedure returning XML Scenario In this exercise, a new web service is being added to the marketing system. You need to create a stored procedure that will query data from a table and return it as an XML value. The main tasks for this exercise are as follows: 1. 2. 3.
Review the requirements Create the stored procedure Test the stored procedure
Task 1: Review the requirements •
Review the supporting documentation for details of the required stored procedure WebStock.GetAvailableModelsAsXML
Task 2: Create the stored procedure •
Create and implement the stored procedure based on the specifications provided
Task 3: Test the stored procedure •
Test the stored procedure by executing the following code: EXEC WebStock.GetAvailableModelsAsXML;
Results: After this exercise, you should have created and tested the required stored procedure that returns XML.
Lab Instructions: Querying XML Data in SQL Server
7
Challenge Exercise 3: Write a stored procedure that updates using XML (Only if time permits) Scenario In this exercise, the reassignment of salesperson territories is done by an external management system. When it completes reassignments, it sends an XML document containing the changes. You need to construct a stored procedure Marketing.UpdateSalesTerritoriesByXML that can process this XML and use it to update the SalesTerritoryID column in the Marketing.Salesperson table. The main tasks for this exercise are as follows: 1. 2. 3.
Review the requirements Create the stored procedure Test the stored procedure and ensure the updates have been applied
Task 1: Review the requirements •
Review the supporting documentation for details of the required stored procedure Marketing.UpdateSalesTerritoriesByXML
Task 2: Create the stored procedure •
Create and implement the stored procedure based on the specifications given in the supporting documentation for the lab
Task 3: Test the stored procedure •
Test the stored procedure by executing the following code: DECLARE @SalespersonMods xml; SET @SalespersonMods = ' '; EXEC Marketing.UpdateSalesTerritoriesByXML @SalespersonMods; GO SELECT * FROM Marketing.Salesperson; GO
Results: After this exercise, you should have created and tested the required stored procedure that updates XML.
Lab Instructions: Working with SQL Server® 2008 R2 Spatial Data
Module 19 Lab Instructions: Working with SQL Server® 2008 R2 Spatial Data Contents: Exercise 1: Familiarity With Geometry Data Type
4
Exercise 2: Adding Spatial Data to an Existing Table
5
Challenge Exercise 3: Business Application of Spatial Data (Only if time permits)
6
1
2
Lab Instructions: Working with SQL Server® 2008 R2 Spatial Data
Lab 19: Working with SQL Server Spatial Data
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
Lab Instructions: Working with SQL Server® 2008 R2 Spatial Data
•
Log on using the following credentials: i. ii.
8. 9. 10. 11. 12. 13. 14. 15.
3
User name: AdventureWorks\Administrator Password: Pa$$w0rd
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. In Connect to Server window, type Proseware in the Server name text box. In the Authentication drop-down list box, select Windows Authentication and click Connect. In the File menu, click Open, and click Project/Solution. In the Open Project window, open the project D:\6232B_Labs\6232B_19_PRJ\6232B_19_PRJ.ssmssln. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario Your organization has only recently begun to acquire spatial data within its databases. The new Marketing database was initially designed prior to the company beginning to implement spatial data. One of the developers has provided a table of the locations where prospects live. It is called Marketing.ProspectLocation. A second developer has added columns to it for Latitude and Longitude and geocoded the addresses. You will make some changes to the system to help support the need for spatial data.
Supporting Documentation Stored Procedure Specifications Stored Procedure Name
Marketing.GetNearbyProspects
Input Parameters
@ProspectID int @DistanceInKms int
Output Parameters
None
Output Rowset Columns
Distance (in Kms) ProspectID LastName FirstName WorkPhoneNumber CellPhoneNumber AddressLine1 AddressLine2 City Longitude Latitude
Output Order
Distance
4
Lab Instructions: Working with SQL Server® 2008 R2 Spatial Data
Exercise 1: Familiarity With Geometry Data Type Scenario In this lab, you have decided to learn to write queries using the geometry data type in SQL Server. You will review and execute scripts that demonstrate querying techniques. The main tasks for this exercise are as follows: 1.
Review and execute the sample script
Task 1: Review and execute the sample script •
Review the query, execute the query, and review the results for scripts 19.1 to 19.9. Results: After this exercise, you should have seen how to work with the geometry data type.
Lab Instructions: Working with SQL Server® 2008 R2 Spatial Data
5
Exercise 2: Adding Spatial Data to an Existing Table Scenario In this lab, you need to modify an existing table Marketing.ProspectLocation to replace the existing Latitude and Longitude columns with a new Location column of type geography. You need to migrate the data to the new Location column before you delete the existing Latitude and Longitude columns. The main tasks for this exercise are as follows: 1. 2. 3.
Add a Location column Write code to assign values to this column Drop the existing Latitude and Longitude columns
Task 1: Add a Location column •
Add a Location column to the Marketing.ProspectLocation table.
Task 2: Write code to assign values to this column •
Write code to assign values to this column based on the existing Latitude and Longitude columns.
Task 3: Drop the existing Latitude and Longitude columns •
Once you are sure the new column has correct data, drop the existing Latitude and Longitude columns.
Results: After this exercise, you should have replaced the existing Longitude and Latitude columns with a new Location column.
6
Lab Instructions: Working with SQL Server® 2008 R2 Spatial Data
Challenge Exercise 3: Business Application of Spatial Data (Only if time permits) Scenario Salespeople are keen to visit with prospects at their own locations, rather than just on the phone. To minimize effort, they are keen when visiting a prospect to also see other prospects in the same area. You will write a stored procedure that provides details of other prospects in the area. To ensure it performs quickly, you will create a spatial index on the table. The main tasks for this exercise are as follows: 1. 2. 3. 4.
Review the requirements Create a spatial index Design and implement the Stored Procedure Test the procedure
Task 1: Review the requirements •
Review the supporting documentation for details of the required stored procedure
Task 2: Create a spatial index •
Create a spatial index on the Marketing.ProspectLocation table
Task 3: Design and implement the Stored Procedure •
Design and implement the stored procedure based on the specifications given in the supporting documentation for the lab
Task 4: Test the procedure •
Test the stored procedure by executing the following code: EXEC Marketing.GetNearbyProspects 2,50;
Results: After this exercise, you should have created and tested the required stored procedure.
Lab Instructions: Working with Full-Text Indexes and Queries
1
Module 20 Lab Instructions: Working with Full-Text Indexes and Queries Contents: Exercise 1: Implement a full-text index
4
Exercise 2: Implement a stoplist
5
Challenge Exercise 3: Create a stored procedure to implement a full-text search (Only if time permits)
6
2
Lab Instructions: Working with Full-Text Indexes and Queries
Lab 20: Working with Full-Text Indexes and Queries
Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: •
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
Lab Instructions: Working with Full-Text Indexes and Queries
•
Log on using the following credentials: i. ii.
8. 9. 10. 11. 12. 13. 14. 15.
User name: AdventureWorks\Administrator Password: Pa$$w0rd
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. In Connect to Server window, type Proseware in the Server name text box. In the Authentication drop-down list box, select Windows Authentication and click Connect. In the File menu, click Open, and click Project/Solution. In the Open Project window, open the project D:\6232B_Labs\6232B_20_PRJ\6232B_20_PRJ.ssmssln. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Scenario Users have been complaining about the limited querying ability provided in the marketing system. You are intending to use full-text indexing to address these complaints. You will implement a full-text index on the Marketing.ProductDescription table to improve this situation. You will implement a stoplist to avoid excessive unnecessary index size. If you have time, your manager would like you to help provide a more natural interface for your users. This will involve creating a new stored procedure.
Supporting Documentation Stored Procedure Specifications Name
Marketing.GetRelevantDescriptions
Input Parameters
@PhraseToSearch nvarchar(1000)
Output Parameters
Nil
Columns Returned
ProductDescriptionID, Description, Ranking
Provide relevant results by using the FREETEXTTABLE function and limit your results to rows with LanguageID = 'en' (for English).
3
4
Lab Instructions: Working with Full-Text Indexes and Queries
Exercise 1: Implement a full-text index Scenario In this exercise, you will implement a full-text index on the Marketing.ProductDescription table to improve the searching abilities. The main tasks for this exercise are as follows: 1. 2. 3. 4. 5. 6. 7.
Create a full-text catalog. Create a full-text index on the Description column of the Marketing.ProductDescription table. Enable automatic change tracking for the index. Check to see that the population of the index is complete. Write a simple CONTAINS query. Write a CONTAINS query that searches for two words. Write a CONTAINS query that uses INFLECTIONAL forms.
Task 1: Create a full-text catalog •
Execute T-SQL statements to create a new full-text catalog called DefaultFullTextCatalog.
•
Ensure that the catalog is set as the default full-text catalog.
Task 2: Create a full-text index on the Description column of the Marketing.ProductDescription table •
Execute T-SQL statements to create a full-text index on the Description column of the Marketing.ProductDescription table.
•
Specify the primary key of the table as the key index.
•
Initially configure change tracking and population off.
Task 3: Enable automatic change tracking for the index •
Execute T-SQL statements to enable automatic change tracking for the table.
Task 4: Check to see that the population of the index is complete •
Query the sys.fulltext_indexes system view to determine if the population is complete.
•
Look in particular at the has_crawl_completed column.
Task 5: Write a simple CONTAINS query •
Write a query to search for ProductDescriptionID and Description where the Description column contains the word “bottle”.
Task 6: Write a CONTAINS query that searches for two words •
Write a query to search for ProductDescriptionID and Description where the Description column contains the words, “elastic” and “lycra”.
Task 7: Write a CONTAINS query that uses INFLECTIONAL forms •
Write a query to search for ProductDescriptionID and Description where the Description column contains inflectional forms of the word “wash”.
Results: After this exercise, you should have created and tested a full-text index.
Lab Instructions: Working with Full-Text Indexes and Queries
Exercise 2: Implement a stoplist Scenario In this exercise, you will implement a stoplist to avoid excessive unnecessary index size. The main tasks for this exercise are as follows: 1. 2. 3. 4.
Review the existing system stopwords. Create a full-text stoplist. Add words to the stoplist. View the stoplist.
Task 1: Review the existing system stopwords •
Review the existing system stopwords by querying the view sys.fulltext_system_stopwords for language_id 1033. (English)
Task 2: Create a full-text stoplist •
Create a full-text stoplist called “CommonWords”.
Task 3: Add words to the stoplist •
Add the words, “Bike” and “AdventureWorks” to the stoplist for the English language.
Task 4: View the stoplist •
View the new stoplist by querying the sys.fulltext_stoplists and sys.fulltext_stopwords views.
Results: After this exercise, you should have created a new stoplist that will help avoid excessive index size growth.
5
6
Lab Instructions: Working with Full-Text Indexes and Queries
Challenge Exercise 3: Create a stored procedure to implement a full-text search (Only if time permits) Scenario
In this exercise, you want to provide a more natural interface for your users. In this exercise, you will create a stored procedure that implements a more advanced type of search. The main tasks for this exercise are as follows: 1. 2.
Review the supporting documentation. Design, implement, and test the stored procedure.
Task 1: Review the supporting documentation •
The supporting documentation includes specifications for the design of a new stored procedure.
Task 2: Design, implement, and test the stored procedure •
Design and implement the stored procedure. The procedure Marketing.GetRelevantDescriptions takes an input search term and returns the specified columns from the Marketing.ProductDescription table. Rows are returned where they are considered relevant, based on the FREETEXTTABLE function and the English language.
•
Test the stored procedure using the search phrases 'strong frame' and 'serious competition'.
Results: After this exercise, you should have created a new stored procedure and tested its behavior.
Lab Answer Key: Introduction to SQL Server 2008 R2 and its Toolset
Module 1 Lab Answer Key: Introduction to SQL Server 2008 R2 and its Toolset Contents: Exercise 1: Verify SQL Server Component Installation
2
Exercise 2: Alter Service Accounts for New Instance
4
Exercise 3: Enable Named Pipes Protocol for Both Instances
4
Exercise 4: Create Aliases for AdventureWorks and Proseware
5
Challenge Exercise 5: Ensure SQL Browser is Disabled and Configure a Fixed TCP/IP Port (Only if time permits)
6
1
2
Lab Answer Key: Introduction to SQL Server 2008 R2 and its Toolset
Lab: Introduction to SQL Server and its Toolset Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • • •
4.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL1 is not started: • • •
5. 6. 7.
Right-click 623XB-MIA-DC and click Start. Right-click 623XB-MIA-DC and click Connect. In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
Right-click 623XB-MIA-SQL1 and click Start. Right-click 623XB-MIA-SQL1 and click Connect. In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: • • •
On the Action menu, click the Ctrl-Alt-Delete menu item. Click Switch User, and then click Other User. Log on using the following credentials: • •
8. 9.
User name: AdventureWorks\Administrator Password: Pa$$w0rd
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window.
Exercise 1: Verify SQL Server Component Installation Task 1: Check that Database Engine and Reporting Services have been installed for the MKTG instance 1.
Click Start, click All Programs, click Microsoft SQL Server 2008 R2, click Configuration Tools, and then click SQL Server Configuration Manager.
2.
In the left-hand pane of the SQL Server Configuration Manager window, click SQL Server Services.
3.
In the right-hand pane, ensure that the following services are listed for the MKTG instance: •
SQL Server (MKTG)
•
SQL Full-text Filter Daemon Launcher (MKTG)
•
SQL Server Reporting Services (MKTG)
•
SQL Server Agent (MKTG)
Lab Answer Key: Introduction to SQL Server 2008 R2 and its Toolset
3
Note: The SQL Full-text Filter Daemon Launcher is present and it is part of the Database Engine.
Task 2: Note the services that are installed for the default instance and that Integration Services is not installed on a per instance basis 1.
In the left-hand pane of the SQL Server Configuration Manager window, click SQL Server Services.
2.
In the right-hand pane, ensure that the following services are listed for the default instance:
3.
•
SQL Server (MSSQLSERVER)
•
SQL Full-text Filter Daemon Launcher (MSSQLSERVER)
•
SQL Server Analysis Services (MSSQLSERVER)
•
SQL Server Agent (MSSQLSERVER)
In the right-hand pane, note that SQL Server Integration Services 10.0 is not installed as a perinstance basis as there is no instance name shown.
Task 3: Ensure that all required services including SQL Server Agent are started and set to autostart for both instances 1.
Check that all the services for the MKTG instance have a Start Mode of Automatic. Ignore the SQL Full-text Filter Daemon Launcher service at this time. Note: The SQL Server Agent (MKTG) service is not set to autostart.
2.
Right-click the SQL Server Agent (MKTG) service and click Properties.
3.
In the Log On tab, click Start and the SQL Server Agent service should start.
4.
In the Service tab, set the value for the Start Mode to Automatic, and then click OK.
5.
Check that all the services for the default instance have a Start Mode of Automatic. Ignore the SQL Full-text Filter Daemon Launcher service at this time.
6.
Check that the SQL Server Agent (MSSQLSERVER) service has Start Mode set to Automatic. Note: The SQL Server Agent (MSSQLSERVER) service is not set to autostart.
7.
Right-click the SQL Server Agent (MSSQLSERVER) service and click Properties.
8.
In the Log On tab, click Start and the SQL Server Agent service should start.
4
Lab Answer Key: Introduction to SQL Server 2008 R2 and its Toolset
9.
In the Service tab, set the value for the Start Mode to Automatic, and then click OK.
10. The SQL Server Services need to be configured as the screenshot below:
Exercise 2: Alter Service Accounts for New Instance Task 1: Change the service account for the MKTG database engine 1.
In the left-hand pane of the SQL Server Configuration Manager window, click SQL Server Services.
2.
In the right-hand pane, right-click SQL Server (MTKG), and select Properties.
3.
In the Account Name text box, type AdventureWorks\PWService.
4.
In the Password text box, type Pa$$w0rd.
5.
In the Confirm Password text box, type Pa$$w0rd and click OK.
6.
In the Confirm Account Change window, click Yes.
Task 2: Change the service account for the MKTG SQL Server Agent 1.
In the left-hand pane of the SQL Server Configuration Manager window, click SQL Server Services.
2.
In the right-hand pane, right-click SQL Server Agent (MTKG), and select Properties.
3.
In the Account Name text box, type
4.
AdventureWorks\PWService. In the Password text box, type Pa$$w0rd.
5.
In the Confirm Password text box, type Pa$$w0rd and click OK.
6.
Right-click SQL Server Agent (MTKG) and select Start to restart the service.
Exercise 3: Enable Named Pipes Protocol for Both Instances Task 1: Enable the named pipes protocol for the default instance 1.
In the left-hand pane of the SQL Server Configuration Manager window, expand SQL Server Network Configuration and then click Protocols for MSSQLSERVER.
2.
In the right-hand pane, right-click Named Pipes and select Enable.
3.
In the Warning window, click OK.
Task 2: Enable the named pipes protocol for the MKTG instance 1.
In the left-hand pane of the SQL Server Configuration Manager window, click Protocols for MKTG.
Lab Answer Key: Introduction to SQL Server 2008 R2 and its Toolset
2.
In the right-hand pane, right-click Named Pipes and select Enable.
3.
In the Warning window, click OK.
5
Task 3: Restart both database engine services 1.
In the left-hand pane of the SQL Server Configuration Manager window, click SQL Server Services.
2.
Right-click SQL Server (MSSQLSERVER) and select Restart.
3.
Right-click SQL Server (MKTG) and select Restart.
4.
In the left-hand pane of the SQL Server Configuration Manager window, click SQL Server Services.
5.
In the toolbar, click the Refresh icon.
Exercise 4: Create Aliases for AdventureWorks and Proseware Task 1: Create a 32-bit alias (AdventureWorks) for the default instance 1.
In the left-hand pane of the SQL Server Configuration Manager window, expand SQL Native Client 10.0 Configuration (32bit) and click Client Protocols.
2.
Confirm that the Named Pipes protocol is Enabled.
3.
In the left-hand pane, right-click Aliases and select New Alias.
4.
In the Alias – New window, in the Alias Name text box, type AdventureWorks.
5.
In the Protocol drop-down list box, select Named Pipes.
6.
In the Server text box, type . and click OK.
Task 2: Create a 32-bit alias (Proseware) for the MKTG instance 1.
In the left-hand pane of the SQL Server Configuration Manager window, expand SQL Native Client 10.0 Configuration (32bit) and click Client Protocols.
2.
Confirm that the Named Pipes protocol is Enabled.
3.
In the left-hand pane, right-click Aliases and select New Alias.
4.
In the Alias – New window, in the Alias Name text box, type Proseware.
5.
In the Protocol drop-down list box, select Named Pipes.
6.
In the Server text box, type .\MKTG and click OK.
Task 3: Create a 64-bit alias (AdventureWorks) for the default instance 1.
In the left-hand pane of the SQL Server Configuration Manager window, expand SQL Native Client 10.0 Configuration and click Client Protocols.
2.
Confirm that the Named Pipes protocol is Enabled.
3.
In the left-hand pane, right-click Aliases and select New Alias.
4.
In the Alias – New window, in the Alias Name text box, type AdventureWorks.
5.
In the Protocol drop-down list box, select Named Pipes.
6.
In the Server text box, type . and click OK.
Task 4: Create a 64-bit alias (Proseware) for the MKTG instance 1.
In the left-hand pane of the SQL Server Configuration Manager window, expand SQL Native Client 10.0 Configuration and click Client Protocols.
6
Lab Answer Key: Introduction to SQL Server 2008 R2 and its Toolset
2.
Confirm that the Named Pipes protocol is Enabled.
3.
In the left-hand pane, right-click Aliases and select New Alias.
4.
In the Alias – New window, in the Alias Name text box, type Proseware.
5.
In the Protocol drop-down list box, select Named Pipes.
6.
In the Server text box, type .\MKTG and click OK.
Task 5: Use SQL Server Management Studio to connect to both aliases to ensure they work as expected 1.
Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
2.
In the Connect to Server window, ensure that Server Type is set to Database Engine.
3.
In the Server name text box, type Proseware.
4.
In the Authentication drop-down list, select Windows Authentication, and click Connect.
5.
In Object Explorer, under Proseware expand Databases. Note: The databases that are present include at least the following: System Databases, Database Snapshots, ReportServer$MKTG, and ReportServer$MKTGTempDB.
6.
In Object Explorer, click Connect, click Database Engine.
7.
In the Connect to Server window, ensure that Server Type is set to Database Engine.
8.
In the Server name text box, type AdventureWorks.
9.
In the Authentication drop-down list, select Windows Authentication, and click Connect.
10. In Object Explorer, under AdventureWorks expand Databases. Note: The databases that are present include at least the following: System Databases, Database Snapshots, AdventureWorks2008R2, and AdventureWorksDW2008R2. 11. Close SQL Server Management Studio. 12. Close SQL Server Configuration Manager.
Challenge Exercise 5: Ensure SQL Browser is Disabled and Configure a Fixed TCP/IP Port (Only if time permits) Task 1: Configure the TCP port for the MKTG database engine instance to 51550 1.
In the Virtual Machine window, click Start.
2.
From the Start menu, click All Programs, click Microsoft SQL Server 2008 R2, click Configuration Tools, and then click SQL Server Configuration Manager.
3.
In the left-hand pane of the SQL Server Configuration Manager window, expand SQL Server Network Configuration and then click Protocols for MKTG.
4.
Right-click the TCP/IP protocol and select Properties.
5.
In the TCP/IP Properties window, click IP Addresses tab.
6.
Scroll to the bottom of the screen, under the IPAll section, clear the value for TCP Dynamic Ports.
Lab Answer Key: Introduction to SQL Server 2008 R2 and its Toolset
7.
For TCP Port, type 51550, and click OK.
8.
In the Warning window, click OK.
9.
In the left-hand pane, click SQL Server Services.
7
10. Right-click SQL Server (MKTG) and select Restart. 11. In the left-hand pane of the SQL Server Configuration Manager window, click SQL Server Services. 12. In the toolbar, click the Refresh icon and make sure the service starts.
Task 2: Disable the SQLBrowser service 1.
In the left-hand pane of the SQL Server Configuration Manager window, click SQL Server Services.
2.
In the right-hand pane, right-click SQL Server Browser and click Stop.
3.
Right-click the SQL Server Browser and click Properties.
4.
In the SQL Server Browser Properties window, in the Service tab, set the Start Mode to Disabled and click OK.
Lab Answer Key: Working with Data Types
Module 2 Lab Answer Key: Working with Data Types Contents: Exercise 1: Choosing Appropriate Data Types
2
Exercise 2: Writing Queries With Data Type Conversions
4
Challenge Exercise 3: Designing and Creating Alias Data Types (Only if time permits)
5
1
2
Lab Answer Key: Working with Data Types
Lab: Working with Data Types Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. 4. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. 5. In Virtual Machine Connection window, click on the Revert toolbar icon. 6. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. 7. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 1. 2. 3.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_02_PRJ\6232B_02_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Choosing Appropriate Data Types Task 1: Determine column names and data types 1.
Review the supporting documentation for details of the PhoneCampaign, Opportunity and SpecialOrder tables and determine column names, data types, and nullability for each data item in the design. Note: With any design exercise, there is no one correct answer. A sample solution has been provided below.
Lab Answer Key: Working with Data Types
Table 1: PhoneCampaign Description
Column Name and Data Type
Which campaign this relates to.
PhoneCampaignID INT NOT NULL
The prospect that was contacted.
ProspectID INT NOT NULL
When contact was first attempted with the prospect.
FirstAttemptedContact DATETIME NULL
Comments related to the contact that was made, if it ContactComments NVARCHAR(MAX) NULL was made. When contact was actually made with the prospect.
InitialContact DATETIME NULL
Outcome of the contact: sale, later follow-up or no interest
ContactOutcomeCode CHAR(1) NULL
Value of any sale made (up to 2 decimal places)
SalesValue DECIMAL(10,2) NULL
Table 2: Opportunity Description
Column Name and Data Type
Name of the opportunity
OpportunityID INT NOT NULL
Which prospect this opportunity relates to
ProspectID INT NOT NULL
Stage the sale is at: Lead, Qualification, Proposal Development, Contract Negotiations, Complete, Lost
SalesStageCode CHAR(2) NOT NULL
Date that the opportunity was raised
DateRaised DATETIME NOT NULL
Probability of success
Likelihood TINYINT NOT NULL
Rating: Cold, Warm, Hot
Rating CHAR(1) NOT NULL
Estimated closing date
EstimatedClosingDate DATE NOT NULL
Estimated revenue
EstimatedRevenue DECIMAL(10,2) NOT NULL
Delivery address
DeliveryAddress NVARCHAR(MAX) NOT NULL
Table 3: SpecialOrder Description
Column Name and Data Type
Which prospect this order is for
ProspectID INT NOT NULL
External supplier of the item
SupplierID INT NOT NULL
Description of the item
ItemDescription NVARCHAR(100) NOT NULL
3
4
Lab Answer Key: Working with Data Types
Description
Column Name and Data Type
Quantity Required (some quantities are whole numbers, some are fractional with up to 3 decimal places)
QuantityRequired DECIMAL(10,3) NOT NULL
Date of order
OrderDate DATETIME NOT NULL
Promised delivery date
PromisedDeliveryDate DATE NOT NULL
Actual delivery date
ActualDeliveryDate DATE NULL
Special requirements (any comments related to the SpecialRequirements NVARCHAR(MAX) NULL special order) Quoted price per unit (up to 2 decimal places)
QuotedPricePerUnit DECIMAL(10,2) NOT NULL
Exercise 2: Writing Queries With Data Type Conversions Task 1: Connect to the MarketDev Database 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
Task 2: Review the first query requirement and write a SELECT statement to meet the requirement 1.
Review the supporting documentation for details for the first query requirement.
2.
In Object Explorer, expand the Databases under Proseware.
3.
Right-click the MarketDev database and click New Query.
4.
Type the query below in the query pane: SELECT ProductID, ProductName, CONVERT(varchar(8),SellEndDate,112) AS SellEndDate FROM Marketing.Product; GO
5.
In the toolbar, click Execute.
6.
Ensure that the results from the query match the required output as shown in the supporting documentation.
Task 3: Review the second query requirement and write a SELECT statement to meet the requirement 1.
Review the supporting documentation for details for the second query requirement.
2.
In Object Explorer, expand the Databases under Proseware.
3.
Right-click the MarketDev database and click New Query.
4.
Type the query below in the query pane: SELECT ProspectID,
Lab Answer Key: Working with Data Types
CAST(Demographics AS nvarchar(1000)) AS Demographics FROM Marketing.Prospect; GO
5.
In the toolbar, click Execute.
6.
Ensure that the results from the query match the required output as shown in the supporting documentation.
Challenge Exercise 3: Designing and Creating Alias Data Types (Only if time permits) Task 1: Investigate the storage of phone numbers and email addresses 1.
In the Object Explorer, expand Databases, and expand MarketDev, and expand Tables.
2.
In each table, expand Columns, noting any columns that are holding details of Phone Numbers or Email Addresses. The following columns should be identified: Table
Phone Number Columns
Definition
Marketing.Prospect
CellPhoneNumber
NVARCHAR(20)
Marketing.Prospect
HomePhoneNumber
NVARCHAR(25)
Marketing.Prospect
WorkPhoneNumber
VARCHAR(22)
Table
Email Address Columns
Definition
Marketing.Prospect
EmailAddress
NVARCHAR(100)
Marketing.SalesPerson
EmailAlias
NVARCHAR(256)
Task 2: Create a data type that stores phone numbers 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: CREATE TYPE PhoneNumber FROM nvarchar(25); GO
4.
In the toolbar, click Execute.
Task 3: Create a data type that stores email addresses 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: CREATE TYPE EmailAddress FROM nvarchar(256);
5
6
Lab Answer Key: Working with Data Types
GO
4.
In the toolbar, click Execute
Lab Answer Key: Designing and Implementing Tables
Module 3 Lab Answer Key: Designing and Implementing Tables Contents: Exercise 1: Improve the Design of Tables
2
Exercise 2: Create a Schema
5
Challenge Exercise 3: Create the Tables (Only if time permits)
5
1
2
Lab Answer Key: Designing and Implementing Tables
Lab: Designing and Implementing Tables Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. 4. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. 5. In Virtual Machine Connection window, click on the Revert toolbar icon. 6. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. 7. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 1. 2. 3.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_03_PRJ\6232B_03_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Improve the Design of Tables Task 1: Review the supplied design 1.
Review the supplied design in the supporting documentation for the exercise.
Task 2: Suggest an improved design Note: With any design exercise, there is no one correct answer. A sample solution has been provided below. Table1: Competitor (Table name change to singular to match existing singular table names in the database)
Lab Answer Key: Designing and Implementing Tables
Name
Data Type
Design Rationale
CompetitorID
INT
Prefer numeric code for surrogate keys
NOT NULL CompetitorName
StreetAddress
DateEntered
NVARCHAR(30) NOT NULL
The column name “Name” is too generic
NVARCHAR(max) NOT NULL
Common to have multiple addresses
DATE NOT NULL
Removed underscore for consistency
StrengthOfCompetitio NVARCHAR(8) n NOT NULL
Unicode data type for flexibility
Unicode data type for flexibility
Chose appropriate data type Renamed for consistency Note: Suspect this should be a code or a number but would require more information from Business Analyst Unicode data type for flexibility
Comments
NVARCHAR(max) NULL Unicode data type for flexibility NULL because there may be no comments
Table2A: City (Duplicated City details extracted from the original table called TVAdvertisements) Name
Data Type
Design Rationale
CityID
INT
Added a surrogate key
NOT NULL CityName
NVARCHAR(25) NOT NULL
Renamed original column Retained data type where no further information is available
Table2B: TVStation (Duplicated TV Station details extracted from the original table called TVAdvertisements) Name
Data Type
Design Rationale
TVStationID
INT NOT NULL
Added a surrogate key
TVStationName
NVARCHAR(15) NOT NULL
Renamed original column
INT NOT NULL
City details extracted to a separate table
CityID
Retained data type where no further information is available
3
4
Lab Answer Key: Designing and Implementing Tables
Name
Data Type
Design Rationale
CostPerAdvertisement
DECIMAL(12,2) NOT NULL
Chose appropriate data type
TotalCostOfAllAdvertisements removed as it should be calculated NumberOfAdvertisements removed as it should be calculated Table2C: TVAdvertisement (Extracted repeating groups from the original table called TVAdvertisements) Name
Data Type
Design Rationale
TVAdvertisementID
INT NOT NULL
Added a surrogate key
TVStationID
INT NOT NULL
Reference to TV Station table
ScreeningTime
DATETIME NOT NULL
Single data type to hold both values Name to reflect combination of values
Table3: CampaignResponse (Table name change to singular to match existing singular table names in the database and removed underscore for consistency) Name
Data Type
Design Rationale
TVAdvertisementID
INT NOT NULL
Added a surrogate key
ResponseReceived
DATETIME NOT NULL
Improved column name
ProspectID
INT NOT NULL
Reference to existing Prospect table
ResponseMethodCode CHAR(1) NOT NULL
Changed to a straightforward code using first letter
ChargeFromReferrer
DECIMAL(12,2) NOT NULL
Chose appropriate data type
RevenueReceived
DECIMAL(12,2) NOT NULL
Chose appropriate data type
An alternative would be to extract the response method as another lookup table
ResponseProfit should be removed as it should be calculated rather than stored in the table
Lab Answer Key: Designing and Implementing Tables
Exercise 2: Create a Schema Task 1: Connect to the MarketDev Database 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
Task 2: Create a schema named DirectMarketing 1.
Type the query below in the query pane: CREATE SCHEMA DirectMarketing AUTHORIZATION dbo; GO
2.
In the toolbar, click Execute.
Challenge Exercise 3: Create the Tables (Only if time permits) Task 1: Create the tables 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: Note: The sample query below is based on the sample solution. Your query should be based on your design. The solution shown below is contained in the 71 – Lab Exercise 3.sql script file in Solution Explorer. CREATE TABLE DirectMarketing.Competitor ( CompetitorID INT NOT NULL PRIMARY KEY, CompetitorName NVARCHAR(30) NOT NULL, StreetAddress NVARCHAR(MAX) NOT NULL, DateEntered DATE NOT NULL, StrengthOfCompetition NVARCHAR(8) NOT NULL, Comments NVARCHAR(MAX) NULL ); GO CREATE TABLE DirectMarketing.City ( CityID INT NOT NULL PRIMARY KEY, CityName NVARCHAR(25) NOT NULL ); GO CREATE TABLE DirectMarketing.TVStation ( TVStationID INT NOT NULL PRIMARY KEY, TVStationName NVARCHAR(15) NOT NULL, CityID INT NOT NULL, CostPerAdvertisement DECIMAL(12,2) NOT NULL ); GO CREATE TABLE DirectMarketing.TVAdvertisement ( TVAdvertisementID INT NOT NULL PRIMARY KEY, TVStationID INT NOT NULL, ScreeningTime DATETIME NOT NULL ); GO
5
6
Lab Answer Key: Designing and Implementing Tables
CREATE TABLE DirectMarketing.CampaignResponse ( CampaignResponseID INT NOT NULL PRIMARY KEY, ResponseReceived DATETIME NOT NULL, ProspectID INT NOT NULL, ResponseMethodCode CHAR(1) NOT NULL, ChargeFromReferrer DECIMAL(12,2) NOT NULL, RevenueReceived DECIMAL(12,2) NOT NULL ); GO
4.
In the toolbar, click Execute.
Lab Answer Key: Designing and Implementing Views
Module 4 Lab Answer Key: Designing and Implementing Views Contents: Exercise 1: Design, Implement and Test the WebStock Views
2
Exercise 2: Design and Implement the Contacts View
4
Challenge Exercise 3: Modify the AvailableModels View (Only if time permits)
4
1
2
Lab Answer Key: Designing and Implementing Views
Lab: Designing and Implementing Views Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. 4. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. 5. In Virtual Machine Connection window, click on the Revert toolbar icon. 6. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. 7. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 1. 2. 3.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_04_PRJ\6232B_04_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Design, Implement and Test the WebStock Views Task 1: Create the WebStock schema 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: CREATE SCHEMA WebStock AUTHORIZATION dbo; GO
4.
In the toolbar, click Execute.
Lab Answer Key: Designing and Implementing Views
Task 2: Review the design requirements 1.
Review the supplied design in the supporting documentation for the OnlineProducts and AvailableModels views.
Task 3: Design and implement the views 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE VIEW WebStock.OnlineProducts AS SELECT p.ProductID, p.ProductName, p.ProductNumber, COALESCE(p.Color,'N/A') AS Color, CASE p.DaysToManufacture WHEN 0 THEN 'Instock' WHEN 1 THEN 'Overnight' WHEN 2 THEN 'Fast' ELSE 'Call' END AS Availability, p.Size, p.SizeUnitMeasureCode AS UnitOfMeasure, p.ListPrice AS Price, p.Weight FROM Marketing.Product AS p WHERE p.SellEndDate IS NULL AND p.SellStartDate IS NOT NULL; GO CREATE VIEW WebStock.AvailableModels AS SELECT p.ProductID, p.ProductName, pm.ProductModelID, pm.ProductModel FROM Marketing.Product AS p INNER JOIN Marketing.ProductModel AS pm ON p.ProductModelID = pm.ProductModelID WHERE p.SellEndDate IS NULL AND p.SellStartDate IS NOT NULL; GO
3.
In the toolbar, click Execute.
Task 4: Test the views 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT * FROM WebStock.OnlineProducts; GO SELECT * FROM WebStock.AvailableModels; GO
3.
In the toolbar, click Execute.
3
4
Lab Answer Key: Designing and Implementing Views
Exercise 2: Design and Implement the Contacts View Task 1: Create the Relationship schema 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: CREATE SCHEMA Relationship AUTHORIZATION dbo; GO
4.
In the toolbar, click Execute.
Task 2: Review the design requirements 1.
Review the supplied design in the supporting documentation for the Contacts view.
Task 3: Design and implement the view 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE VIEW Relationship.Contacts AS SELECT p.ProspectID AS ContactID, p.FirstName, p.MiddleName, p.LastName, 'PROSPECT' AS ContactRole FROM Marketing.Prospect AS p UNION ALL SELECT sp.SalespersonID, sp.FirstName, sp.MiddleName, sp.LastName, 'SALESPERSON' FROM Marketing.Salesperson AS sp; GO
3.
In the toolbar, click Execute.
Task 4: Test the view 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT * FROM Relationship.Contacts; GO
3.
In the toolbar, click Execute.
Challenge Exercise 3: Modify the AvailableModels View (Only if time permits) Task 1: Alter the AvailableModels View 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane:
Lab Answer Key: Designing and Implementing Views
ALTER VIEW WebStock.AvailableModels AS SELECT p.ProductID, p.ProductName, pm.ProductModelID, pm.ProductModel, COALESCE(ed.Description,id.Description) AS CatalogDescription FROM Marketing.Product AS p INNER JOIN Marketing.ProductModel AS pm ON p.ProductModelID = pm.ProductModelID LEFT OUTER JOIN Marketing.ProductDescription AS ed ON pm.ProductModelID = ed.ProductModelID AND ed.LanguageID = 'en' LEFT OUTER JOIN Marketing.ProductDescription as id ON pm.ProductModelID = id.ProductModelID AND id.LanguageID = '' WHERE p.SellEndDate IS NULL AND p.SellStartDate IS NOT NULL; GO
3.
In the toolbar, click Execute.
Task 2: Test the view 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT * FROM WebStock.AvailableModels; GO
3.
In the toolbar, click Execute.
4.
This page intentionally left blank
5
Lab Answer Key: Planning for SQL Server 2008 R2 Indexing
Module 5 Lab Answer Key: Planning for SQL Server 2008 R2 Indexing Contents: Exercise 1: Explore existing index statistics
2
Challenge Exercise 2: Design column orders for indexes (Only if time permits) 5
1
2
Lab Answer Key: Planning for SQL Server 2008 R2 Indexing
Lab: Planning for SQL Server Indexing Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect.
•
4.
5. 6. 7.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator
ii. Password: Pa$$w0rd From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 8. 9.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_05_PRJ\6232B_05_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Explore existing index statistics Task 1: Execute SQL Command 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane:
Lab Answer Key: Planning for SQL Server 2008 R2 Indexing
3
EXEC sp_helpstats ‘Marketing.Product’ GO
4.
In the toolbar, click Execute.
Task 2: Review the results 1.
In the Query Analyzer Results pane, review the results.
2.
Check to see if any autostats has been generated. If any autostats have been created, they will appear in the results with an _WA prefix.
Task 3: Create statistics 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE STATISTICS Product_Color_Stats ON Marketing.Product (Color) WITH FULLSCAN; GO
3.
In the toolbar, click Execute.
Task 4: Re-execute the SQL command from task 1 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: EXEC sp_helpstats ‘Marketing.Product’ GO
3.
In the toolbar, click Execute.
Task 5: Use DBCC SHOW_STATISTICS 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: DBCC SHOW_STATISTICS('Marketing.Product',Product_Color_Stats); GO
3.
In the toolbar, click Execute.
Task 6: Answer questions 1.
Complete the answer column in the table below. Note: The results returned can vary. Sample results are shown in the following table
Question
Answer
4
Lab Answer Key: Planning for SQL Server 2008 R2 Indexing
How many rows were sampled?
504
How many steps were created?
10
What was the average key length?
5.178571
How many Black products are there?
93
Task 7: Execute SQL Command and check accuracy of statistics 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT COUNT(1) FROM Marketing.Product WHERE Color = 'Black'; GO
3.
In the toolbar, click Execute.
Task 8: Calculate Selectivity of each query 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: -- Calculate the total number of rows in the table SELECT COUNT(1) FROM Marketing.Prospect; GO
3.
In the toolbar, click Execute. Note: A sample result would be 19955
4.
Type the query below in the same query pane: -- Query 1 SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName LIKE 'A%'; GO
5.
Highlight only Query 1 and click Execute.
6.
Calculate the selectivity of the query. Note: A sample result would be 2013 / 19955 or approximately 10.1%
7.
Type the query below in the same query pane: -- Query 2 SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName LIKE 'Alejandro%'; GO
Lab Answer Key: Planning for SQL Server 2008 R2 Indexing
8.
Highlight only Query 2 and click Execute.
9.
Calculate the selectivity of the query. Note: A sample result would be 48 / 19955 or approximately 0.2%
10. Type the query below in the same query pane: -- Query 3 SELECT ProspectID, FirstName, LastName FROM Marketing.Prospect WHERE FirstName LIKE 'Arif%'; GO
11. Highlight only Query 3 and click Execute. 12. Calculate the selectivity of the query. Note: A sample result would be 1 / 19955 or approximately 0.0%
Challenge Exercise 2: Design column orders for indexes (Only if time permits) Task 1: Design an index 1.
Review the supporting documentation for Query 1, the columns and column order is detailed in the table below: Columns
Column Order
ProspectID
1
Rationale: Selecting by ProspectID only and no output order
Task 2: Design an index 1.
Review the supporting documentation for Query 2, the columns and column order is detailed in the table below: Columns
Column Order
FirstName
1
Rationale: Selecting by FirstName only and no output order
Task 3: Design an index 1.
Review the supporting documentation for Query 3, the columns and column order is detailed in the table below:
5
6
Lab Answer Key: Planning for SQL Server 2008 R2 Indexing
Columns
Column Order
FirstName
1
LastName
2
Rationale: Selecting only a single FirstName. Adding LastName to the index will supply the correct output order
Task 4: Design an index 1.
Review the supporting documentation for Query 4, the columns and column order is detailed in the table below: Columns
Column Order
FirstName
1
LastName
2
Rationale: Selecting a range of FirstNames could have varying selectivity. In this case, the selectivity of the FirstName column is sufficient to warrant the index on the FirstName. Note that if the constant was an ‘A’ rather than an ‘S’, the index would be better defined as LastName, FirstName instead
Task 5: Design an index 1.
Review the supporting documentation for Query 5, the columns and column order is detailed in the table below: Columns
Column Order
LanguageID
1
Rationale: Grouping by LanguageID. Note that in this case an alternate output order would not change the recommendation
Lab Answer Key: Implementing Table Structures in SQL Server 2008 R2
Module 6 Lab Answer Key: Implementing Table Structures in SQL Server 2008 R2 Contents: Exercise 1: Creating Tables as Heaps
2
Exercise 2: Creating Tables with Clustered Indexes
3
Challenge Exercise 3: Comparing the Performance of Clustered Indexes vs. Heaps (Only if time permits)
4
1
2
Lab Answer Key: Implementing Table Structures in SQL Server 2008 R2
Lab: Implementing Table Structures in SQL Server Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect.
•
4.
5. 6. 7.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator
ii. Password: Pa$$w0rd From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 8. 9.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_06_PRJ\6232B_06_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Creating Tables as Heaps Task 1: Review the Requirements 1.
Review the supporting documentation for Table 1 and 2.
Lab Answer Key: Implementing Table Structures in SQL Server 2008 R2
Task 2: Create the Tables in the MarketDev database 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: CREATE TABLE Relationship.ActivityLog ( ActivityTime datetimeoffset, SessionID int, Duration int, ActivityType int ); GO CREATE TABLE Relationship.PhoneLog ( PhoneLogID int PRIMARY KEY NONCLUSTERED, SalespersonID int, CalledPhoneNumber nvarchar(16), CallDurationSeconds int ); GO
4.
In the toolbar, click Execute.
Exercise 2: Creating Tables with Clustered Indexes Task 1: Review the Requirements 1.
Review the supporting documentation for Tables 3 and 4.
Task 2: Create the Tables in the MarketDev database 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: CREATE TABLE Relationship.MediaOutlet ( MediaOutletID int PRIMARY KEY CLUSTERED, MediaOutletName nvarchar(40), PrimaryContact nvarchar(50), City nvarchar(50) ); GO CREATE TABLE Relationship.PrintMediaPlacement ( PrintMediaPlacementID int PRIMARY KEY CLUSTERED, MediaOutletID int, PlacementDate datetime, PublicationDate datetime, RelatedProductID int, PlacementCost decimal(18,2) ); GO
4.
In the toolbar, click Execute.
3
4
Lab Answer Key: Implementing Table Structures in SQL Server 2008 R2
Challenge Exercise 3: Comparing the Performance of Clustered Indexes vs. Heaps (Only if time permits) Task 1: Review the Table Design 1.
Review the supporting documentation for Table 5.
Task 2: Create the Relationship.Table_Heap Table 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: CREATE TABLE Relationship.Table_Heap ( ApplicationID int IDENTITY(1,1), ApplicantName nvarchar(150), EmailAddress nvarchar(100), ReferenceID uniqueidentifier, Comments nvarchar(500) ); GO
4.
In the toolbar, click Execute.
Task 3: Create the Relationship.Table_ApplicationID Table 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: CREATE TABLE Relationship.Table_ApplicationID ( ApplicationID int IDENTITY(1,1), ApplicantName nvarchar(150), EmailAddress nvarchar(100), ReferenceID uniqueidentifier, Comments nvarchar(500) ); GO CREATE CLUSTERED INDEX IX_ApplicantID ON Relationship.Table_ApplicationID (ApplicationID); GO
4.
In the toolbar, click Execute.
Task 4: Create the Relationship.Table_EmailAddress Table 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: CREATE TABLE Relationship.Table_EmailAddress ( ApplicationID int IDENTITY(1,1),
Lab Answer Key: Implementing Table Structures in SQL Server 2008 R2
ApplicantName nvarchar(150), EmailAddress nvarchar(100), ReferenceID uniqueidentifier, Comments nvarchar(500) ); GO CREATE CLUSTERED INDEX IX_EmailAddress ON Relationship.Table_EmailAddress (EmailAddress); GO
4.
In the toolbar, click Execute.
Task 5: Create the Relationship.Table_ReferenceID Table 1.
In Object Explorer, expand the Databases under Proseware.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: CREATE TABLE Relationship.Table_ReferenceID ( ApplicationID int IDENTITY(1,1), ApplicantName nvarchar(150), EmailAddress nvarchar(100), ReferenceID uniqueidentifier, Comments nvarchar(500) ); GO CREATE CLUSTERED INDEX IX_ReferenceID ON Relationship.Table_ReferenceID (ReferenceID); GO
4.
In the toolbar, click Execute.
Task 6: Load and Execute the Workload Script 1.
In Solution Explorer, double-click the file 72 – Lab Exercise 3 Workload Script.sql.
2.
In the toolbar, click Execute.
Note: this may take approximately five minutes to complete, depending on available hardware resources. You can check where it is up to by periodically switching to the Messages tab. A message is printed as each of the four sections is completed. While the script is running, review the contents of the script and estimate the proportion of time difference you expect to see in the results.
Task 7: Compare Table Performance 1.
Compare the performance of each table structure as reported by the results of executing the Workload script.
Note: Results will vary substantially depending upon the hardware the Virtual Machine is running on. Sample results are shown below. HeapTime
ApplicationIDTime
EmailAddressTime
ReferenceIDTime
23 seconds
21 seconds
91 seconds
134 seconds
5
Lab Answer Key: Reading SQL Server 2008 R2 Execution Plans
Module 7 Lab Answer Key: Reading SQL Server 2008 R2 Execution Plans Contents: Exercise 1: Actual vs. Estimated Plans
2
Exercise 2: Identify Common Plan Elements
5
Challenge Exercise 3: Query Cost Comparison (Only if time permits)
8
1
2
Lab Answer Key: Reading SQL Server 2008 R2 Execution Plans
Lab: Reading SQL Server Execution Plans Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window.
3. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • •
Right-click 623XB-MIA-DC and click Start. Right-click 623XB-MIA-DC and click Connect.
•
4.
5. 6. 7.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator
ii. Password: Pa$$w0rd From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 8. 9.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_07_PRJ\6232B_07_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Actual vs. Estimated Plans Task 1: Load the test script 1.
In Solution Explorer, double-click the file
2.
51 – Lab Exercise 1.sql. Highlight the following section of the script:
Lab Answer Key: Reading SQL Server 2008 R2 Execution Plans
USE AdventureWorks2008R2; GO
3.
In the toolbar, click Execute.
Task 2: Generate an estimated execution plan for script 7.1 1.
Highlight the following section of the script SELECT * FROM dbo.DatabaseLog; GO
2.
From the Query menu, select Display Estimated Execution Plan. Note: A Table Scan operator was used.
Task 3: View the estimated execution plan for script 7.2 using SHOWPLAN_XML 1.
Highlight the following section of the script SET SHOWPLAN_XML ON; GO SELECT * FROM dbo.DatabaseLog; GO SET SHOWPLAN_XML OFF; GO
2.
In the toolbar, click Execute.
3.
In the Results pane, click on the XML that is returned to view the execution plan.
4.
In the Results pane, right-click in the whitespace in the plan, click Show Execution Plan XML.
5.
In the ExecutionPlanX.sqlplan tab, review the XML. Note: The XML is complex and it includes a large amount of detail that is not immediately obvious from the graphical plan.
6.
Close the XML window and the execution plan window. If prompted to save changes, do not save the changes.
Task 4: Generate the actual execution plan for script 7.3 1.
In the Query menu and click Include Actual Execution Plan.
2.
Highlight the following section of the script SELECT * FROM dbo.DatabaseLog; GO
3.
In the toolbar, click Execute.
4.
Click on the Execution Plan pane.
3
4
Lab Answer Key: Reading SQL Server 2008 R2 Execution Plans
Note: The plan is identical to the estimated plan from the previous task.
Task 5: Try to generate an estimated execution plan for script 7.4 1.
Highlight the following section of the script CREATE TABLE dbo.SomeTable ( SomeTableID INT IDENTITY(1, 1) PRIMARY KEY, FullName varchar(35) ); INSERT INTO dbo.SomeTable VALUES('Hello'),('There'); SELECT * FROM dbo.SomeTable; DROP TABLE dbo.SomeTable; GO
2.
From the Query menu, select Display Estimated Execution Plan. Note: An Estimated Execution Plan cannot be created. Note the reason (invalid object name) for the inability to create a plan in the messages tab.
Task 6: Review the actual execution plan for script 7.4 1.
Highlight the following section of the script again CREATE TABLE dbo.SomeTable ( SomeTableID INT IDENTITY(1, 1) PRIMARY KEY, FullName varchar(35) ); INSERT INTO dbo.SomeTable VALUES('Hello'),('There'); SELECT * FROM dbo.SomeTable; DROP TABLE dbo.SomeTable; GO
2.
In the toolbar, click Execute.
3.
Review the Execution Plan that has been returned. Note: The returned plan shows the INSERT and SELECT statements but not the CREATE and DROP statements.
Task 7: Review the execution plans currently cached in memory using script 7.5 1.
Highlight the following section of the script SELECT cp.objtype AS PlanType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.refcounts AS ReferenceCounts, cp.usecounts AS UseCounts, st.text AS SQLBatch, qp.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st; GO
2.
In the toolbar, click Execute.
Lab Answer Key: Reading SQL Server 2008 R2 Execution Plans
3.
Review the results being returned. Note: The results returned will vary from system to system. Browse through the rows in the Results tab to gain an understanding of the types of plans currently in memory.
Exercise 2: Identify Common Plan Elements Task 1: Load the test script 1.
In Solution Explorer, double-click the file
2.
61 – Lab Exercise 2.sql. Highlight the query as shown in the script below: USE AdventureWorks2008R2; GO
3.
In the toolbar, click Execute.
4.
From the Query menu click Include Actual Execution Plan.
Task 2: Explain the actual execution plan from script 7.6 1.
Highlight script 7.6 as shown in the code below SELECT * FROM Person.Person; GO
2.
In the toolbar, click Execute.
3.
Explain the results being returned. Note: The table has a clustered index and the entire table is being read.
Task 3: Explain the actual execution plan from script 7.7 1.
Highlight script 7.7 as shown in the code below SELECT * FROM Person.Person WHERE Person.BusinessEntityID = 12; GO
2.
In the toolbar, click Execute.
3.
Explain the results being returned. Note: The table has a clustered index and a specific clustering key is being used to seek.
Task 4: Explain the actual execution plan from script 7.8 1.
Highlight script 7.8 as shown in the code below SELECT * FROM Production.ProductInventory ORDER BY Shelf; GO
5
6
Lab Answer Key: Reading SQL Server 2008 R2 Execution Plans
2.
In the toolbar, click Execute.
3.
Explain the results being returned. Note: The table has a clustered index and the entire table is being read. The rows are then sorted by Shelf.
Task 5: Explain the actual execution plan from script 7.9 1.
Highlight script 7.9 as shown in the code below SELECT * FROM dbo.DatabaseLog WHERE DatabaseLogID = 1; GO
2.
In the toolbar, click Execute.
3.
Explain the results being returned. Note: The table is structured as a heap. The table has a nonclustered index on DatabaseLogID that is being used to locate the row required. Because the entire row is then required, a lookup is being made into the heap.
Task 6: Explain the actual execution plan from script 7.10 1.
Highlight script 7.10 as shown in the code below SELECT AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode FROM Person.Address WHERE StateProvinceID = 32; GO
2.
In the toolbar, click Execute.
3.
Explain the results being returned. Note: The table has a clustered index. There is a nonclustered index that is sufficiently selective to be useful in locating entries for the given StateProvinceID. After the rows are located by that index, lookups to the clustered index need to be performed because not all columns that are required for the query are contained within the nonclustered index.
Task 7: Explain the actual execution plan from script 7.11 1.
Highlight script 7.11 as shown in the code below SELECT AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode FROM Person.Address WHERE StateProvinceID = 79; GO
2.
In the toolbar, click Execute.
3.
Explain the results being returned. Compare this plan to the one returned in script 7.10.
Lab Answer Key: Reading SQL Server 2008 R2 Execution Plans
4.
The reason why the plans are different can be attributed to Note: SQL Server has chosen to scan an entire index as it covers the query. The rows with the correct StateProvinceID are being selected from that index. Note the Missing Index hint that appears.
Task 8: Explain the actual execution plan from script 7.12 1.
Highlight script 7.12 as shown in the code below SELECT City, COUNT(City) AS CityCount FROM Person.Address GROUP BY City; GO
2.
In the toolbar, click Execute.
3.
Explain the results being returned. Note: SQL Server has decided to read an entire index that contains the City column rather than reading the entire table. As the index is not in City order, it then needs to execute a hash match aggregate operation to calculate the required aggregate.
Task 9: Explain the actual execution plan from script 7.13 1.
Highlight script 7.13 as shown in the code below SELECT StateProvinceID, COUNT(1) AS StateProvinceCount FROM Person.Address GROUP BY StateProvinceID; GO
2.
In the toolbar, click Execute.
3.
Explain the results being returned. Compare this plan to the one returned in script 7.12.
4.
The reason why the plans are different can be attributed to Note: There is a nonclustered index that is in StateProvinceID order. SQL Server has chosen to scan that index. As it is already in StateProvinceID order, a stream aggregate operation was able to be performed.
Task 10: Explain the actual execution plan from script 7.14 1.
Highlight script 7.14 as shown in the code below SELECT City, COUNT(City) AS CityCount FROM Person.Address GROUP BY City HAVING COUNT(1) > 5; GO
2.
In the toolbar, click Execute.
3.
Note the difference in this plan from the plan for script 7.12.
7
8
Lab Answer Key: Reading SQL Server 2008 R2 Execution Plans
Note: This query is identical to the one in script 7.12 with the addition of the HAVING clause. That clause has caused SQL Server to add a filter operation.
Challenge Exercise 3: Query Cost Comparison (Only if time permits) Task 1: Load the test script 1.
In Solution Explorer, double-click the file
2.
71 – Lab Exercise 3.sql. Highlight the query as shown in the script below: USE AdventureWorks2008R2; GO
3.
In the toolbar, click Execute.
4.
From the Query menu click Include Actual Execution Plan.
Task 2: Explain the actual execution plan from script 7.15 1.
Highlight script 7.15 as shown in the code below SELECT DISTINCT a.City FROM Person.Address AS a ORDER BY a.City; SELECT a.City FROM Person.Address AS a GROUP BY a.City ORDER BY a.City; GO
2.
In the toolbar, click Execute.
3.
Explain the results being returned. Note: Even though the two queries are structured quite differently, they are requesting exactly the same results in the same order. It is then logical that SQL Server executes the same plan for both queries.
Lab Answer Key: Improving Performance through Nonclustered Indexes
Module 8 Lab Answer Key: Improving Performance through Nonclustered Indexes Contents: Exercise 1: Nonclustered index usage review
2
Exercise 2: Improving nonclustered index designs
3
Exercise 3: SQL Server Profiler and Database Engine Tuning Advisor
4
Challenge Exercise 4: Nonclustered index design (Only if time permits)
6
1
2
Lab Answer Key: Improving Performance through Nonclustered Indexes
Lab: Improving Performance through Nonclustered Indexes Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect.
•
4.
5. 6. 7.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator
ii. Password: Pa$$w0rd From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 8. 9.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_08_PRJ\6232B_08_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Nonclustered index usage review Task 1: Review the query 1.
Review the supporting documentation for Query 1.
Lab Answer Key: Improving Performance through Nonclustered Indexes
3
Task 2: Review the existing Index and Table structures 1.
In Object Explorer, expand Proseware, expand Databases, expand MarketDev, expand Tables, and expand Marketing.WebLog.
2.
Review existing indexes and table structures for this table. Note: Clustered Primary Key on WebLogID and Non-Clustered index on SessionStart.
Task 3: Design a more appropriate index 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE INDEX IX_WebLog_Perf_20100830_A ON Marketing.WebLog (SessionStart) INCLUDE (SessionID, ServerID, UserName); GO
3.
In the toolbar, click Execute.
Task 4: Test your design 1.
Right-click the MarketDev database and click New Query.
2.
From the Query menu, click Include Actual Execution Plan.
3.
Type the query below in the query pane: DECLARE @StartTime datetime2 = '2010-08-30 16:27'; SELECT TOP(5000) wl.SessionID, wl.ServerID, wl.UserName FROM Marketing.WebLog AS wl WHERE wl.SessionStart >= @StartTime ORDER BY wl.SessionStart, wl.ServerID;
4.
In the toolbar, click Execute.
Exercise 2: Improving nonclustered index designs Task 1: Review the index design 1.
Review the supporting documentation for Query 2.
Task 2: Implement the index 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE INDEX IX_WebLog_Perf_20100830_B ON Marketing.WebLog (ServerID, SessionStart) INCLUDE (SessionID, UserName); GO
3.
In the toolbar, click Execute.
4
Lab Answer Key: Improving Performance through Nonclustered Indexes
Task 3: Test the design and explain why the index was not used 1.
Right-click the MarketDev database and click New Query.
2.
From the Query menu, click Include Actual Execution Plan.
3.
Type the query below in the query pane: DECLARE @StartTime datetime2 = '2010-08-30 16:27'; SELECT TOP(5000) wl.SessionID, wl.ServerID, wl.UserName FROM Marketing.WebLog AS wl WHERE wl.SessionStart >= @StartTime ORDER BY wl.SessionStart, wl.ServerID; GO
4.
In the toolbar, click Execute. Note: The structure of the index does not support efficient selection of rows or ordering of results.
Exercise 3: SQL Server Profiler and Database Engine Tuning Advisor Task 1: Review the query 1.
Review the supporting documentation for Query 3.
Task 2: Review the existing Index and Table structures 1.
In Object Explorer, expand Proseware, expand Databases, expand MarketDev, expand Tables, and expand Marketing.PostalCode.
2.
Review existing indexes and table structures for this table. Note: Clustered index on Country, PostalCode.
Task 3: Design a more appropriate index by following the Missing Index suggestion 1.
Right-click the MarketDev database and click New Query.
2.
From the Query menu, click Include Actual Execution Plan.
3.
Type the query below in the query pane: SELECT PostalCode, Country FROM Marketing.PostalCode WHERE StateCode = 'KY' ORDER BY StateCode, PostalCode; GO
4.
In the toolbar, click Execute.
5.
In the Execution Plan tab, note the missing index suggestion in green.
6.
Right-click in whitespace in the middle of the graphical execution plan and choose Missing Index Details.
Lab Answer Key: Improving Performance through Nonclustered Indexes
Note: Review the suggested index design. 7.
Close the Missing Index Details query window.
8.
Type the query below in the previous query pane: CREATE NONCLUSTERED INDEX IX_PostalCode_Perf_20100830_A ON Marketing.PostalCode (StateCode) INCLUDE (Country,PostalCode); GO
9.
Highlight the above query, click Execute.
10. Highlight the previous query as shown below in the query pane: SELECT PostalCode, Country FROM Marketing.PostalCode WHERE StateCode = 'KY' ORDER BY StateCode, PostalCode; GO
11. In the toolbar, click Execute. 12. In the Execution Plan tab, note that the missing index suggestion has now disappeared. Note: The query is now utilizing the new index. 13. Leave the query pane open for the next task.
Task 4: Create a better index that removes the sort operation. If you create another index, confirm that SQL Server selects it 1.
Type the query below in the query pane from the previous task: CREATE NONCLUSTERED INDEX IX_PostalCode_Perf_20100830_B ON Marketing.PostalCode (StateCode, PostalCode) INCLUDE (Country); GO
2.
Highlight the above query, click Execute.
3.
Highlight the previous query as shown below in the query pane: SELECT PostalCode, Country FROM Marketing.PostalCode WHERE StateCode = 'KY' ORDER BY StateCode, PostalCode; GO
4.
In the toolbar, click Execute.
5.
Click on the Execution Plan tab. Note: The sort operation is now unnecessary and the new index is now utilized.
5
6
Lab Answer Key: Improving Performance through Nonclustered Indexes
Challenge Exercise 4: Nonclustered index design (Only if time permits) Task 1: Open SQL Server Profiler and configure and start a trace 1.
In SQL Server Management Studio, in the Tools menu, click SQL Server Profiler.
2.
In the Connect to Server window, type Proseware in the Server name text box.
3.
In the Authentication drop-down list box, select Windows Authentication and click Connect.
4.
In the Trace Properties window, type Module08Trace in the Trace Name textbox.
5.
In the Use the Template drop down list box, select Tuning.
6.
Click Save to file checkbox.
7.
In the Save As window, click Desktop in the left-hand pane under Favorites and click Save.
8.
Uncheck the Enable File Rollover checkbox.
9.
In the Set maximum file size(MB) textbox, type 500.
10. Click Events Selection tab, and click Column Filters. 11. In the Edit Filter pane, click the DatabaseName column header. 12. In the DatabaseName pane, expand the Like node, type MarketDev, and click OK. 13. In the Trace Properties window, click Run. 14. In the Window menu, ensure that AutoScroll is not selected.
Task 2: Load and execute the workload file 1.
In SQL Server Management Studio, double-click 81 – Lab Exercise 4.sql in Solution Explorer.
2.
In the toolbar, click Execute.
3.
Wait for the query to complete. Note: The query will take approximately 3 minutes to execute, depending upon the available hardware resources.
Task 3: Stop and analyze the trace using DTA 1.
In SQL Server Profiler, from the File menu, click Stop Trace.
2.
From the Tools menu, click Database Engine Tuning Advisor.
3.
In the Connect to Server window, click Connect.
4.
Maximize the Database Engine Tuning Advisor window.
5.
In the Workload group box, ensure that File is selected and click the Browse for a Workload File icon (the icon is the first icon to the right of the text box in the Workload group box).
6.
Browse to the Desktop folder, select the Module08Trace.trc file and click Open.
Lab Answer Key: Improving Performance through Nonclustered Indexes
7.
In the Database for workload analysis dropdown, select MarketDev.
8.
In the Select databases and tables to tune list, check the MarketDev database.
9.
Click the Tuning Options tab and click Advanced Options.
10. In the Advanced Tuning Options window, check the Define max space for recommendations (MB) checkbox and enter 500 for the size and click OK. 11. From the toolbar, click the Start Analysis. Note: Observe the tuning progress and when the analysis is complete, note the recommendations. The exact recommendations you see will depend upon how long you ran the query, etc. but it is likely you will see a recommendation for two new indexes and a substantial estimated performance improvement. 12. On the Recommendations tab, note the Estimated improvement. 13. Scroll the Index Recommendations output to the right and note the hyperlinks to sample code. 14. Click each of the recommendations in the Definition column turn and note the suggested index structures. 15. Close DTA. 16. Close SQL Server Profiler.
7
Lab Answer Key: Designing and Implementing Stored Procedures
Module 9 Lab Answer Key: Designing and Implementing Stored Procedures Contents: Exercise 1: Create stored procedures
2
Exercise 2: Create a parameterized stored procedure
4
Challenge Exercise 3: Alter the execution context of stored procedures (Only if time permits)
4
1
2
Lab Answer Key: Designing and Implementing Stored Procedures
Lab: Designing and Implementing Stored Procedures Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator
ii. Password: Pa$$w0rd From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 8. 9.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_09_PRJ\6232B_09_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Create stored procedures Task 1: Review the Reports.GetProductColors stored procedure specification 1.
Review the supplied design requirements in the supporting documentation for Reports.GetProductColors.
Lab Answer Key: Designing and Implementing Stored Procedures
Task 2: Design, create and test the Reports.GetProductColors stored procedure 1.
In Object Explorer, expand the Proseware server, expand Databases, right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE PROCEDURE Reports.GetProductColors AS SELECT DISTINCT p.Color FROM Marketing.Product AS p WHERE p.Color IS NOT NULL ORDER BY p.Color; GO EXEC Reports.GetProductColors; GO
3.
In the toolbar, click Execute. Note: Ensure that approximately 9 colors are returned and that no NULL row is returned.
Task 3: Review the Reports.GetProductsAndModels stored procedure specification 1.
Review the supplied design requirements in the supporting documentation for Reports.GetProductsAndModels.
Task 4: Design, create and test the Reports.GetProductsAndModels stored procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE PROCEDURE Reports.GetProductsAndModels AS SELECT p.ProductID, p.ProductName, p.ProductNumber, p.SellStartDate, p.SellEndDate, p.Color, pm.ProductModelID, COALESCE(ed.Description,id.Description,p.ProductName) AS EnglishDescription, COALESCE(fd.Description,id.Description,p.ProductName) AS FrenchDescription, COALESCE(cd.Description,id.Description,p.ProductName) AS ChineseDescription FROM Marketing.Product AS p LEFT OUTER JOIN Marketing.ProductModel AS pm ON p.ProductModelID = pm.ProductModelID LEFT OUTER JOIN Marketing.ProductDescription AS ed ON pm.ProductModelID = ed.ProductModelID AND ed.LanguageID = 'en' LEFT OUTER JOIN Marketing.ProductDescription AS fd ON pm.ProductModelID = fd.ProductModelID AND fd.LanguageID = 'fr' LEFT OUTER JOIN Marketing.ProductDescription AS cd ON pm.ProductModelID = cd.ProductModelID AND cd.LanguageID = 'zh-cht' LEFT OUTER JOIN Marketing.ProductDescription AS id
3
4
Lab Answer Key: Designing and Implementing Stored Procedures
ON pm.ProductModelID = id.ProductModelID AND id.LanguageID = '' ORDER BY p.ProductID,pm.ProductModelID; GO EXEC Reports.GetProductsAndModels; GO
3.
In the toolbar, click Execute. Note: Ensure that approximately 504 rows are returned. Check that appropriate language descriptions appear. ProductID 1 should have the same description for all three languages. ProductID 776 should have different descriptions for each language.
Exercise 2: Create a parameterized stored procedure Task 1: Review the Reports.GetProductsByColor stored procedure specification 1.
Review the supplied design requirements in the supporting documentation for Reports.GetProductsByColor.
Task 2: Design, create and test the Reports.GetProductsByColor stored procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE PROCEDURE Reports.GetProductsByColor @Color nvarchar(16) AS SELECT p.ProductID, p.ProductName, p.ListPrice AS Price, p.Color, p.Size, p.SizeUnitMeasureCode AS UnitOfMeasure FROM Marketing.Product AS p WHERE (p.Color = @Color) OR (p.Color IS NULL AND @Color IS NULL) ORDER BY ProductName; GO EXEC Reports.GetProductsByColor 'Blue'; GO EXEC Reports.GetProductsByColor NULL; GO
3.
In the toolbar, click Execute. Note: Ensure that approximately 26 rows are returned for blue products. Ensure that approximately 248 rows are returned for products with no color.
Challenge Exercise 3: Alter the execution context of stored procedures (Only if time permits) Task 1: Alter the Reports.GetProductColors stored procedure to execute as OWNER 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane:
Lab Answer Key: Designing and Implementing Stored Procedures
ALTER PROCEDURE Reports.GetProductColors WITH EXECUTE AS OWNER AS SELECT DISTINCT p.Color FROM Marketing.Product AS p WHERE p.Color IS NOT NULL ORDER BY p.Color; GO EXEC Reports.GetProductColors; GO
3.
In the toolbar, click Execute. Check that the procedure still executes without error.
Task 2: Alter the Reports.GetProductsAndModels stored procedure to execute as OWNER 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: ALTER PROCEDURE Reports.GetProductsAndModels WITH EXECUTE AS OWNER AS SELECT p.ProductID, p.ProductName, p.ProductNumber, p.SellStartDate, p.SellEndDate, p.Color, pm.ProductModelID, COALESCE(ed.Description,id.Description,p.ProductName) AS EnglishDescription, COALESCE(fd.Description,id.Description,p.ProductName) AS FrenchDescription, COALESCE(cd.Description,id.Description,p.ProductName) AS ChineseDescription FROM Marketing.Product AS p LEFT OUTER JOIN Marketing.ProductModel AS pm ON p.ProductModelID = pm.ProductModelID LEFT OUTER JOIN Marketing.ProductDescription AS ed ON pm.ProductModelID = ed.ProductModelID AND ed.LanguageID = 'en' LEFT OUTER JOIN Marketing.ProductDescription AS fd ON pm.ProductModelID = fd.ProductModelID AND fd.LanguageID = 'fr' LEFT OUTER JOIN Marketing.ProductDescription AS cd ON pm.ProductModelID = cd.ProductModelID AND cd.LanguageID = 'zh-cht' LEFT OUTER JOIN Marketing.ProductDescription AS id ON pm.ProductModelID = id.ProductModelID AND id.LanguageID = '' ORDER BY p.ProductID,pm.ProductModelID; GO EXEC Reports.GetProductsAndModels; GO
3.
In the toolbar, click Execute. Check that the procedure still executes without error.
Task 3: Alter the Reports.GetProductsByColor stored procedure to execute as OWNER 1.
Right-click the MarketDev database and click New Query.
5
6
Lab Answer Key: Designing and Implementing Stored Procedures
2.
Type the query below in the query pane: ALTER PROCEDURE Reports.GetProductsByColor @Color nvarchar(16) WITH EXECUTE AS OWNER AS SELECT p.ProductID, p.ProductName, p.ListPrice AS Price, p.Color, p.Size, p.SizeUnitMeasureCode AS UnitOfMeasure FROM Marketing.Product AS p WHERE (p.Color = @Color) OR (p.Color IS NULL AND @Color IS NULL) ORDER BY ProductName; GO EXEC Reports.GetProductsByColor 'Blue'; GO EXEC Reports.GetProductsByColor NULL; GO
3.
In the toolbar, click Execute. Check that the procedure still executes without error.
Lab Answer Key: Merging Data and Passing Tables
Module 10 Lab Answer Key: Merging Data and Passing Tables Contents: Exercise 1: Create a Table Type
2
Exercise 2: Use a Table Type Parameter
3
Challenge Exercise 3: Use a Table Type with MERGE (Only if time permits)
4
1
2
Lab Answer Key: Merging Data and Passing Tables
Lab: Passing Tables and Merging Data Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window.
3. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • • •
Right-click 623XB-MIA-DC and click Start. Right-click 623XB-MIA-DC and click Connect. In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. 4. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. 5. In Virtual Machine Connection window, click on the Revert toolbar icon. 6. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. 7. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_10_PRJ\6232B_10_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Create a Table Type Task 1: Review the parameters of a stored procedure 1.
Review the supplied design requirements in the supporting documentation for Reports.GetProductsByColorList.
Lab Answer Key: Merging Data and Passing Tables
Task 2: Review the existing function 1.
In Object Explorer, expand Proseware, expand Databases, expand MarketDev, expand Programmability, expand Functions, and expand Table-valued Functions.
2.
Right-click dbo.StringListToTable, click Script Function as, click CREATE To, select New Query Editor Window.
3.
Review the design of the existing function.
Task 3: Create a new table type 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE TYPE StringList AS TABLE(StringValue nvarchar(1000) NOT NULL); GO
3.
In the toolbar, click Execute.
Exercise 2: Use a Table Type Parameter Task 1: Create the stored procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE PROCEDURE Reports.GetProductsByColorList_Test (@ColorList StringList READONLY) AS SELECT p.ProductID, p.ProductName, p.ListPrice AS Price, p.Color, p.Size, p.SizeUnitMeasureCode AS UnitOfMeasure FROM Marketing.Product AS p INNER JOIN @ColorList AS cl ON p.Color = cl.StringValue ORDER BY p.ProductName; GO
3.
In the toolbar, click Execute.
Task 2: Test the new procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: DECLARE @ColorList StringList; INSERT INTO @ColorList VALUES('Red'),('Blue'),('Silver'); EXEC Reports.GetProductsByColorList_Test @ColorList; GO
3.
In the toolbar, click Execute.
3
4
Lab Answer Key: Merging Data and Passing Tables
Note: Approximately 107 rows should be returned.
Challenge Exercise 3: Use a Table Type with MERGE (Only if time permits) Task 1: Create a new table type 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE TYPE dbo.SalespersonList AS TABLE (SalespersonID int NOT NULL, FirstName nvarchar(50) NULL, MiddleName nvarchar(50) NULL, LastName nvarchar(50) NULL, BadgeNumber nvarchar(15) NULL, EmailAlias nvarchar(256) NULL, SalesTerritoryID int NULL); GO
3.
In the toolbar, click Execute.
Task 2: Create a replacement stored procedure 1.
Review the supplied design in the supporting documentation for the Marketing.SalespersonMerge Stored Procedure.
2.
Right-click the MarketDev database and click New Query.
3.
Type the query below in the query pane: CREATE PROCEDURE Marketing.SalespersonMerge (@SalespersonDetails SalespersonList READONLY) AS BEGIN MERGE INTO Marketing.Salesperson AS sp USING @SalespersonDetails AS spd ON sp.SalespersonID = spd.SalespersonID WHEN MATCHED THEN UPDATE SET sp.FirstName = COALESCE(spd.FirstName, sp.FirstName), sp.MiddleName = COALESCE(spd.MiddleName, sp.MiddleName), sp.LastName = COALESCE(spd.LastName, sp.LastName), sp.BadgeNumber = COALESCE(spd.BadgeNumber, sp.BadgeNumber), sp.EmailAlias = COALESCE(spd.EmailAlias, sp.EmailAlias), sp.SalesTerritoryID = COALESCE(spd.SalesTerritoryID, sp.SalesTerritoryID) WHEN NOT MATCHED THEN INSERT (SalespersonID, FirstName, MiddleName, LastName, BadgeNumber, EmailAlias, SalesTerritoryID) VALUES (spd.SalespersonID, spd.FirstName, spd.MiddleName, spd.LastName, spd.BadgeNumber, spd.EmailAlias, spd.SalesTerritoryID)
Lab Answer Key: Merging Data and Passing Tables
OUTPUT $action AS Action, inserted.SalespersonID; END; GO
4.
In the toolbar, click Execute.
Task 3: Test the replacement procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: DECLARE @Salespeople SalespersonList; INSERT INTO @Salespeople (SalespersonID, FirstName, MiddleName, LastName, BadgeNumber, EmailAlias, SalesTerritoryID) VALUES (276,'Michael','','Wong',NULL,'adventure-works\mwong',NULL), (601,'John','','Wong','234232','adventure-works\jwong',3); EXEC Marketing.SalespersonMerge @Salespeople; GO
3.
In the toolbar, click Execute. Note: The output should show UPDATE for SalesPersonID 276 and should show INSERT for SalesPersonID 601.
5
Lab Answer Key: Creating Highly Concurrent SQL Server 2008 R2 Applications
Module 11 Lab Answer Key: Creating Highly Concurrent SQL Server 2008 R2 Applications Contents: Exercise 1: Detecting Deadlocks
3
1
2
Lab Answer Key: Creating Highly Concurrent SQL Server 2008 R2 Applications
Lab: Creating Highly Concurrent SQL Server Applications Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager.
2.
Maximize the Hyper-V Manager window.
3.
In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started:
4.
•
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
5.
In Virtual Machine Connection window, click on the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete.
7.
In the Virtual Machine Connection window, if the user is not already logged on: •
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
•
Log on using the following credentials: i. ii.
User name: AdventureWorks\Administrator Password: Pa$$w0rd
8.
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode.
9.
If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window.
10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_11_PRJ\6232B_11_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Answer Key: Creating Highly Concurrent SQL Server 2008 R2 Applications
3
Exercise 1: Detecting Deadlocks Task 1: Start and configure SQL Server Profiler 1.
In SQL Server Management Studio, from Tools menu, click SQL Server Profiler.
2.
In Connect to Server window, type Proseware in the Server name text box.
3.
In the Authentication drop-down list box, select Windows Authentication and click Connect.
4.
In the Trace Properties window, type Deadlock Detection in the Trace Name textbox.
5.
Click Events Selection tab.
6.
Check the Show all events checkbox.
7.
Expand Locks, check Deadlock graph.
8.
Uncheck the Show all events checkbox.
9.
De-select all events except the Deadlock graph. You may need to scroll the window down to do this.
10. Click Run to start the trace.
Task 2: Load and execute the test scripts 1.
In SQL Server Management Studio, double-click 51 – Lab Exercise 1.sql in Solution Explorer.
2.
Review the script.
3.
In SQL Server Management Studio, double-click 52 – Lab Exercise 1 2nd Window.sql in Solution Explorer.
4.
Review the script.
5.
Execute 51 – Lab Exercise 1.sql and then immediately execute 52 – Lab Exercise 1 2nd Window.sql. Wait for both to complete Note: One of the scripts will return a deadlock error.
Task 3: Stop the trace and review the deadlock graph 1.
In SQL Server Profiler, from File menu, click Stop Trace.
2.
Review the results from the Deadlock Trace.
3.
Maximize the Trace window inside SQL Server Profiler.
4.
Click on the event line that contains the Deadlock graph.
5.
Review the graph that appears at the bottom of the screen.
6.
Hover your mouse over each of the ellipses that represent processes.
4
Lab Answer Key: Creating Highly Concurrent SQL Server 2008 R2 Applications
Note: When you hover your mouse over a process, you will see a pop-up window displaying the TSQL statement that was executed. 7.
Close SQL Server Profiler.
Challenge Exercise 2: Investigating Transaction Isolation Levels (Only if time permits) Task 1: Load the scripts 1.
In SQL Server Management Studio, double-click 62 – Lab Exercise 2 2nd Window.sql in Solution Explorer.
2.
Right-click the tab at the top of the window that opens and click Close All But This.
3.
In SQL Server Management Studio, double-click 61 – Lab Exercise 2.sql in Solution Explorer.
Task 2: Execute the code 1.
Execute the code step by step by following the instructions in the script files. Note: This exercise involves two script windows. Make sure that you execute only the code for each step and in the correct window as directed by the scripts.
Lab Answer Key: Handling Errors in T-SQL Code
Module 12 Lab Answer Key: Handling Errors in T-SQL Code Contents: Exercise 1: Replace @@ERROR based error handling with structured exception handling
3
Challenge Exercise 2: Add deadlock retry logic to the stored procedure (Only if time permits)
4
1
2
Lab Answer Key: Handling Errors in T-SQL Code
Lab: Handling Errors in T-SQL Code Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager.
2.
Maximize the Hyper-V Manager window.
3.
In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started:
4.
•
Right-click 623XB-MIA-DC and click Start.
•
Right-click 623XB-MIA-DC and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window.
In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: •
Right-click 623XB-MIA-SQL and click Start.
•
Right-click 623XB-MIA-SQL and click Connect.
•
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears.
5.
In Virtual Machine Connection window, click on the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete.
7.
In the Virtual Machine Connection window, if the user is not already logged on:
8. 9.
•
On the Action menu, click the Ctrl-Alt-Delete menu item.
•
Click Switch User, and then click Other User.
•
Log on using the following credentials:
i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window.
10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_12_PRJ\6232B_12_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Answer Key: Handling Errors in T-SQL Code
Exercise 1: Replace @@ERROR based error handling with structured exception handling Task 1: Review the existing code 1.
In Object Explorer, expand Proseware, expand Databases, expand MarketDev, expand Programmability, expand Stored Procedures, right-click Marketing.MoveCampaignBalance, and click Modify.
2.
Review the existing stored procedure.
Task 2: Rewrite the stored procedure to use structured exception handling 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE PROCEDURE Marketing.MoveCampaignBalance_Test ( @FromCampaignID int, @ToCampaignID int, @BalanceToMove decimal(18,2) ) AS BEGIN SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION UPDATE Marketing.CampaignBalance SET RemainingBalance -= @BalanceToMove WHERE CampaignID = @FromCampaignID; UPDATE Marketing.CampaignBalance SET RemainingBalance += @BalanceToMove WHERE CampaignID = @ToCampaignID; COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; PRINT 'Unable to move balance'; RETURN 1; -- 7 END CATCH; END; GO
3.
In the toolbar, click Execute.
4.
Leave the query window open for use in the next exercise.
Task 3: Test the stored procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: EXEC Marketing.MoveCampaignBalance_Test 3,2,51.50; GO
3.
In the toolbar, click Execute. Note: You should see two messages each indicating that a single row has been updated.
3
4
Lab Answer Key: Handling Errors in T-SQL Code
4.
Leave the query window open for use in the next exercise.
Challenge Exercise 2: Add deadlock retry logic to the stored procedure (Only if time permits) Task 1: Modify the code to re-try on deadlock 1.
In SQL Server Management Studio, return to the query window with the Marketing.MoveCampaignBalance_Test stored procedure code.
2.
Modify the query as per the code shown below: ALTER PROCEDURE Marketing.MoveCampaignBalance_Test ( @FromCampaignID int, @ToCampaignID int, @BalanceToMove decimal(18,2) ) AS BEGIN DECLARE @RetriesRemaining int = 5; SET XACT_ABORT ON; WHILE (@RetriesRemaining > 0) BEGIN BEGIN TRY BEGIN TRANSACTION UPDATE Marketing.CampaignBalance SET RemainingBalance -= @BalanceToMove WHERE CampaignID = @FromCampaignID; UPDATE Marketing.CampaignBalance SET RemainingBalance += @BalanceToMove WHERE CampaignID = @ToCampaignID; COMMIT TRANSACTION; SET @RetriesRemaining = 0; END TRY BEGIN CATCH IF (ERROR_NUMBER() = 1205) BEGIN SET @RetriesRemaining -=1; PRINT 'Warning: deadlock retry'; WAITFOR DELAY '00:00:05'; END ELSE BEGIN SET @RetriesRemaining = 0; -- exit the retry loop IF XACT_STATE() 0 ROLLBACK TRANSACTION; PRINT 'Unable to move balance'; RETURN 1; END; END CATCH; END; END; GO
3.
In the toolbar, click Execute.
Task 2: Test the stored procedure 1.
In SQL Server Management Studio, return to the query window with the EXEC Marketing.MoveCampaignBalance_Test code as shown in the query pane: EXEC Marketing.MoveCampaignBalance_Test 3,2,51.50; GO
2.
In the toolbar, click Execute.
Lab Answer Key: Handling Errors in T-SQL Code
Note: You should see two messages each indicating that a single row has been updated.
5
Lab Answer Key: Designing and Implementing User-Defined Functions
Module 13 Lab Answer Key: Designing and Implementing User-Defined Functions Contents: Exercise 1: Formatting Phone Numbers
3
Exercise 2: Modifying an Existing Function
4
Challenge Exercise 3: Resolve a Function-related Performance Issue (Only if time permits)
5
1
2
Lab Answer Key: Designing and Implementing User-Defined Functions
Lab: Designing and Implementing User-Defined Functions Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. 4. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. 5. In Virtual Machine Connection window, click on the Revert toolbar icon. 6. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. 7. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 1. 2. 3.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_13_PRJ\6232B_13_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Answer Key: Designing and Implementing User-Defined Functions
3
Exercise 1: Formatting Phone Numbers Task 1: Review the design requirements 1.
Review the supplied function specifications in the supporting documentation in the Lab section of the Student Manual, for the Phone Number.
Task 2: Design and create the function 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE FUNCTION dbo.FormatPhoneNumber ( @PhoneNumberToFormat nvarchar(16) ) RETURNS nvarchar(16) AS BEGIN DECLARE @Digits nvarchar(16) = ''; DECLARE @Remaining nvarchar(16) = @PhoneNumberToFormat; DECLARE @Character nchar(1); IF LEFT(@Remaining,1) = N'+' RETURN @Remaining; WHILE (LEN(@Remaining) > 0) BEGIN SET @Character = LEFT(@Remaining,1); SET @Remaining = SUBSTRING(@Remaining,2,LEN(@Remaining)); IF (@Character >= N'0') AND (@Character 0 BEGIN SET @DelimiterPosition = CHARINDEX(@Delimiter, @RemainingString); IF (@DelimiterPosition = 0) SET @DelimiterPosition = LEN(@RemainingString) + 1; IF (@DelimiterPosition > 8) SET @DelimiterPosition = 8; SET @CurrentToken = SUBSTRING(@RemainingString,1,@DelimiterPosition - 1); INSERT INTO @OutputTable (IntegerValue) VALUES(CAST(@CurrentToken AS int)); SET @RemainingString = SUBSTRING(@RemainingString, @DelimiterPosition + 1, LEN(@RemainingString)); END; RETURN; END; GO
3.
In the toolbar, click Execute.
Task 3: Test the function 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane:
Lab Answer Key: Designing and Implementing User-Defined Functions
5
SELECT * FROM dbo.IntegerListToTable('234,354253,3242,2',','); GO
3.
In the toolbar, click Execute. Note: The output should resemble the following:
Task 4: Test the function with an alternate delimiter such as the pipe | character 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT * FROM dbo.IntegerListToTable('234|354253|3242|2','|'); GO
3.
In the toolbar, click Execute. Note: The output should resemble the following:
Challenge Exercise 3: Resolve a Function-related Performance Issue (Only if time permits) Task 1: Review the query 1.
Review the supplied problematic query in the supporting documentation in the Lab section of the Student Manual.
Task 2: Design an alternate query 1.
Right-click the MarketDev database and click New Query.
2.
Type the problem query from the supporting documentation and the replacement query in the query pane: SET STATISTICS TIME ON; GO SELECT dbo.JoinNames(FirstName,MiddleName,LastName) AS FullName FROM Marketing.Prospect
6
Lab Answer Key: Designing and Implementing User-Defined Functions
ORDER BY FullName; SELECT FirstName + N' ' + CASE WHEN LEN(MiddleName) > 0 THEN MiddleName + N' ' ELSE N'' END + LastName AS FullName FROM Marketing.Prospect ORDER BY FullName; GO
3.
In the toolbar, click Execute.
Task 3: Use SET STATISTICS TIME ON to compare the performance of the new and old queries 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SET STATISTICS TIME ON; GO SELECT dbo.JoinNames(FirstName,MiddleName,LastName) AS FullName FROM Marketing.Prospect ORDER BY FullName; GO SELECT FirstName + N' ' + CASE WHEN LEN(MiddleName) > 0 THEN MiddleName + N' ' ELSE N'' END + LastName AS FullName FROM Marketing.Prospect ORDER BY FullName; GO SET STATISTICS TIME OFF; GO
3.
In the toolbar, click Execute.
Lab Answer Key: Designing and Implementing User-Defined Functions
Note: The execution time for the alternate query should be shorter than the time for the original query. The actual times will vary from computer to computer. The output should resemble the following:
7
Lab Answer Key: Ensuring Data Integrity through Constraints
1
Module 14 Lab Answer Key: Ensuring Data Integrity through Constraints Contents: Exercise 1: Constraint Design
2
Challenge Exercise 2: Test the constraints (Only if time permits)
4
2
Lab Answer Key: Ensuring Data Integrity through Constraints
Lab: Ensuring Data Integrity through Constraints Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials:
i. ii.
User name: AdventureWorks\Administrator Password: Pa$$w0rd
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 8. 9.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_14_PRJ\6232B_14_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Constraint Design Task 1: Review the supporting documentation 1.
Review the supplied table design requirements in the supporting documentation.
Lab Answer Key: Ensuring Data Integrity through Constraints
Task 2: Alter the Marketing.Yield table 1.
In Object Explorer, expand the Proseware server, expand Databases, right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: ALTER TABLE Marketing.Yield ALTER COLUMN ProspectID int NOT NULL; GO ALTER TABLE Marketing.Yield ALTER COLUMN LanguageID nchar(6) NOT NULL; GO ALTER TABLE Marketing.Yield ALTER COLUMN YieldOutcome int NOT NULL; GO ALTER TABLE Marketing.Yield ALTER COLUMN RowID uniqueidentifier NOT NULL; GO ALTER TABLE Marketing.Yield ALTER COLUMN LastUpdate datetime2 NOT NULL; GO ALTER TABLE Marketing.Yield ADD CONSTRAINT PK_Yield PRIMARY KEY CLUSTERED (ProspectID, LanguageID); GO ALTER TABLE Marketing.Yield ADD CONSTRAINT FK_Yield_Prospect FOREIGN KEY (ProspectID) REFERENCES Marketing.Prospect(ProspectID); GO ALTER TABLE Marketing.Yield ADD CONSTRAINT FK_Yield_Language FOREIGN KEY (LanguageID) REFERENCES Marketing.Language(LanguageID); GO ALTER TABLE Marketing.Yield ADD CONSTRAINT DF_Yield_YieldOutcome DEFAULT (0) FOR YieldOutcome; GO ALTER TABLE Marketing.Yield ADD CONSTRAINT DF_Yield_RowID DEFAULT (NEWID()) FOR RowID; GO ALTER TABLE Marketing.Yield ADD CONSTRAINT DF_Yield_LastUpdate DEFAULT (SYSDATETIME()) FOR LastUpdate; GO
3.
In the toolbar, click Execute.
3
4
Lab Answer Key: Ensuring Data Integrity through Constraints
Challenge Exercise 2: Test the constraints (Only if time permits) Task 1: Test the default values and data types 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: INSERT INTO Marketing.Yield (ProspectID, LanguageID, Notes) VALUES (1,'en', 'Excellent outcome'); SELECT * FROM Marketing.Yield; GO
3.
In the toolbar, click Execute. Note: This should execute without errors.
Task 2: Test the primary key 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: INSERT INTO Marketing.Yield (ProspectID, LanguageID, Notes) VALUES (1,'en', 'Another excellent outcome'); GO
3. In the toolbar, click Execute. Note: This should fail due to the primary key constraint.
Task 3: Test the foreign key reference on language 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: INSERT INTO Marketing.Yield (ProspectID, LanguageID, Notes) VALUES (2,'ex', 'Excellent outcome'); GO
3. In the toolbar, click Execute. Note: This should fail due to the foreign key reference.
Task 4: Test the foreign key reference on prospect 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: INSERT INTO Marketing.Yield (ProspectID, LanguageID, Notes) VALUES (29292,'en', 'Excellent outcome');
Lab Answer Key: Ensuring Data Integrity through Constraints
GO
3. In the toolbar, click Execute. Note: This should fail due to the foreign key reference.
5
Lab Answer Key: Responding to Data Manipulation via Triggers
Module 15 Lab Answer Key: Responding to Data Manipulation via Triggers Contents: Exercise 1: Creating and Testing the Audit Trigger
3
Challenge Exercise 2: Improve the Audit Trigger (Only if time permits)
4
1
2
Lab Answer Key: Responding to Data Manipulation via Triggers
Lab: Responding to Data Manipulation via Triggers Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. 4. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. 5. In Virtual Machine Connection window, click on the Revert toolbar icon. 6. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. 7. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 1. 2. 3.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_15_PRJ\6232B_15_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Answer Key: Responding to Data Manipulation via Triggers
Exercise 1: Creating and Testing the Audit Trigger Task 1: Review the supporting documentation and existing system 1.
Review the supplied table requirements in the supporting documentation for Marketing.CampaignAudit table.
2.
In Object Explorer, expand Proseware, expand Databases, expand MarketDev, expand Tables, expand Marketing.CampaignBalance, and expand Columns.
3.
Review the table design.
Task 2: Design a trigger to meet the requirements as stated in the scenario for this exercise 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE TRIGGER Marketing.TR_CampaignBalance_Update ON Marketing.CampaignBalance AFTER UPDATE AS BEGIN SET NOCOUNT ON; INSERT Marketing.CampaignAudit (AuditTime, ModifyingUser, RemainingBalance) SELECT SYSDATETIME(), ORIGINAL_LOGIN(), inserted.RemainingBalance FROM deleted INNER JOIN inserted ON deleted.CampaignID = inserted.CampaignID WHERE deleted.RemainingBalance > 10000 OR inserted.RemainingBalance > 10000; END; GO
3.
In the toolbar, click Execute.
Task 3: Write code to test the behavior of the trigger 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: EXEC Marketing.MoveCampaignBalance 3,2,101000; GO SELECT * FROM Marketing.CampaignBalance; GO SELECT * FROM Marketing.CampaignAudit; GO
3.
In the toolbar, click Execute. Note: The output should be similar to the following:
3
4
Lab Answer Key: Responding to Data Manipulation via Triggers
Note: The dates and times will depend upon when you execute the code.
Challenge Exercise 2: Improve the Audit Trigger (Only if time permits) Task 1: Modify the trigger based on the updated requirements 1.
In Object Explorer, expand Proseware, expand Databases, expand MarketDev, expand Tables, expand Marketing.CampaignAudit, and expand Triggers.
2.
Review the trigger design.
3.
Right-click the MarketDev database and click New Query.
4.
Type the query below in the query pane: ALTER TRIGGER Marketing.TR_CampaignBalance_Update ON Marketing.CampaignBalance AFTER UPDATE AS BEGIN SET NOCOUNT ON; INSERT Marketing.CampaignAudit (AuditTime, ModifyingUser, RemainingBalance) SELECT SYSDATETIME(), ORIGINAL_LOGIN(), inserted.RemainingBalance FROM deleted INNER JOIN inserted ON deleted.CampaignID = inserted.CampaignID WHERE ABS(deleted.RemainingBalance - inserted.RemainingBalance) > 10000; END; GO
5.
In the toolbar, click Execute.
Task 2: Delete all rows from the Marketing.CampaignAudit table 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane:
Lab Answer Key: Responding to Data Manipulation via Triggers
DELETE FROM Marketing.CampaignAudit; GO
3.
In the toolbar, click Execute.
Task 3: Test the modified trigger 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT * FROM Marketing.CampaignBalance; GO EXEC Marketing.MoveCampaignBalance 3,2,10100; GO EXEC Marketing.MoveCampaignBalance 3,2,1010; GO SELECT * FROM Marketing.CampaignAudit; GO
3.
In the toolbar, click Execute. Note: The output should be similar to the following:
Note: The dates and times will depend upon when you execute the code.
5
Lab Answer Key: Implementing Managed Code in SQL Server 2008 R2
1
Module 16 Lab Answer Key: Implementing Managed Code in SQL Server 2008 R2 Contents: Exercise 1: Assess Proposed CLR Code
2
Exercise 2: Implement a CLR Assembly
3
Challenge Exercise 3: Implement a CLR User-defined Aggregate and CLR User-defined Data Type (Only if time permits)
5
2
Lab Answer Key: Implementing Managed Code in SQL Server 2008 R2
Lab: Implementing Managed Code in SQL Server Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. 4. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. 5. In Virtual Machine Connection window, click on the Revert toolbar icon. 6. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. 7. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 1. 2. 3.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_16_PRJ\6232B_16_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Assess Proposed CLR Code Task 1: Review the supporting documentation 1.
Review the supplied proposed SQLCLR functionality list in the supporting documentation.
Lab Answer Key: Implementing Managed Code in SQL Server 2008 R2
3
Task 2: For each object listed, determine if implementing it in managed code is appropriate or not 1.
The following components should be included in SQL CLR. Proposed SQLCLR Functionality
Should be implemented
Table-valued function that returns a list of files Yes, good use of external access. in a particular folder. Function that formats phone numbers as strings.
Yes, good use of string handling.
Trigger that records balance movements with a value of more than 1000.
No, only involves data access.
Stored procedure that writes an XML file for a given XML parameter.
Yes, good use of external access.
Function that counts rows in a table.
No, only involves data access.
A new Customer data type.
No, not an object oriented database.
Exercise 2: Implement a CLR Assembly Task 1: Ensure the database is configured appropriately to support an EXTERNAL_ACCESS assembly 1.
In Object Explorer, under Proseware expand Databases, expand System Databases, and right-click the master database and click New Query.
2.
Type the query below in the query pane: EXEC sp_configure 'clr enabled',1; GO RECONFIGURE; GO ALTER DATABASE MarketDev SET TRUSTWORTHY ON; GO
3.
In the toolbar, click Execute.
Task 2: Catalog the assembly and the functions contained within it 1.
In Object Explorer, under Proseware expand Databases, and right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE ASSEMBLY SQLCLRDemo FROM 'D:\6232B_Labs\6232B_16_PRJ\6232B_16_PRJ\SQLCLRDemo.DLL' WITH PERMISSION_SET = EXTERNAL_ACCESS; GO
4
Lab Answer Key: Implementing Managed Code in SQL Server 2008 R2
3.
In the toolbar, click Execute.
4.
Type the query below in the query pane: SELECT * FROM sys.assemblies; SELECT * FROM sys.assembly_files; GO
5.
Highlight the query above and in the toolbar click Execute.
6.
Type the query below in the query pane: CREATE FUNCTION dbo.IsValidEmailAddress(@email NVARCHAR(4000)) RETURNS BIT AS EXTERNAL NAME SQLCLRDemo.[SQLCLRDemo.CLRDemoClass].IsValidEmailAddress; GO
7.
Highlight the query above and in the toolbar click Execute.
8.
Type the query below in the query pane: CREATE FUNCTION dbo.FormatAustralianPhoneNumber(@PhoneNumber NVARCHAR(4000)) RETURNS NVARCHAR(4000) AS EXTERNAL NAME SQLCLRDemo.[SQLCLRDemo.CLRDemoClass].FormatAustralianPhoneNumber; GO
9.
Highlight the query above and in the toolbar click Execute.
10. Type the query below in the query pane: CREATE FUNCTION dbo.FolderList(@RequiredPath NVARCHAR(4000),@FileMask NVARCHAR(4000)) RETURNS TABLE (FileName NVARCHAR(4000)) AS EXTERNAL NAME SQLCLRDemo.[SQLCLRDemo.CLRDemoClass].FolderList; GO
11. Highlight the query above and in the toolbar click Execute.
Task 3: Test the functions contained within the assembly 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT dbo.IsValidEmailAddress('
[email protected]'); GO SELECT dbo.IsValidEmailAddress('test.somewhere.com'); GO SELECT dbo.FormatAustralianPhoneNumber('0419201410'); SELECT dbo.FormatAustralianPhoneNumber('9 87 2 41 23'); SELECT dbo.FormatAustralianPhoneNumber('039 87 2 41 23'); GO SELECT * FROM dbo.FolderList( 'D:\6232B_Labs\6232B_16_PRJ\6232B_16_PRJ','*.txt'); GO
3.
In the toolbar, click Execute.
Lab Answer Key: Implementing Managed Code in SQL Server 2008 R2
Challenge Exercise 3: Implement a CLR User-defined Aggregate and CLR Userdefined Data Type (Only if time permits) Task 1: Catalog and test the user-defined aggregate 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE AGGREGATE dbo.AggString(@s nvarchar(4000)) RETURNS nvarchar(4000) EXTERNAL NAME SQLCLRDemo.[SQLCLRDemo.AggString]; GO SELECT dbo.AggString(DISTINCT ProductNumber) FROM Marketing.Product WHERE Color = 'Black'; GO
3.
In the toolbar, click Execute.
Task 2: Catalog and test the user-defined data type 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE TYPE dbo.zVarChar EXTERNAL NAME SQLCLRDemo.[SQLCLRDemo.zVarChar]; GO
3.
In the toolbar, click Execute.
4.
Right-click the MarketDev database and click New Query.
5.
Type the query below in the query pane: CREATE TABLE dbo.TestTable ( RecID int IDENTITY(1,1), TextValue zVarchar ); GO
6.
In the toolbar, click Execute.
7.
Right-click the MarketDev database and click New Query.
8.
Type the query below in the query pane: INSERT INTO dbo.TestTable VALUES('Some GO SELECT TextValue.ToString(), TextValue.CompressedLength(), TextValue.CompressionPercentage() FROM dbo.TestTable; GO
compressible
data');
5
6
Lab Answer Key: Implementing Managed Code in SQL Server 2008 R2
SELECT AVG(TextValue.CompressionPercentage()) FROM dbo.TestTable; GO DROP TABLE dbo.TestTable; GO
9.
In the toolbar, click Execute.
10. Right-click the MarketDev database and click New Query. 11. Type the query below in the query pane: DECLARE @TestString zVarChar; SET @TestString = 'Some compressible PRINT @TestString.CompressedValue(); GO PRINT zVarChar::Compress( 'Some compressible GO
12. In the toolbar, click Execute.
value');
value';
Lab Answer Key: Storing XML Data in SQL Server 2008 R2
Module 17 Lab Answer Key: Storing XML Data in SQL Server 2008 R2 Contents: Exercise 1: Appropriate Usage of XML Data Storage in SQL Server
3
Exercise 2: Investigate the Storage of XML Data in Variables
3
Exercise 3: Investigate the use of XML Schema Collections
5
Challenge Exercise 4: Investigate the Creation of Database Columns Based on XML (Only if time permits)
7
1
2
Lab Answer Key: Storing XML Data in SQL Server 2008 R2
Lab: Storing XML Data in SQL Server Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: 1. 2. 3.
4.
5. 6. 7.
On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. In Virtual Machine Connection window, click on the Revert toolbar icon. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials:
i. ii.
User name: AdventureWorks\Administrator Password: Pa$$w0rd
From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 8. 9.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_17_PRJ\6232B_17_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Lab Answer Key: Storing XML Data in SQL Server 2008 R2
Exercise 1: Appropriate Usage of XML Data Storage in SQL Server Task 1: Review the list of Use Cases 1.
Review the supplied Use Cases requirements in the supporting documentation.
Task 2: Determine whether each is appropriate for XML 1.
The following components should be implemented Use Cases Requirements
Should be implemented
Reason
Existing XML data that is stored but not processed.
Yes
Data is already XML and does not need to be processed.
Storing attributes for a customer.
No
Should be database columns.
Relational data that is being passed through a system but not processed within it.
Perhaps
Only if the data is being sent and received as XML.
Storing attributes that are nested (that is attributes stored within attributes).
Yes
Not standard relational data.
Exercise 2: Investigate the Storage of XML Data in Variables Task 1: Review and execute the queries, and review the results 1.
Double-click the script 61 – Lab Exercise 2.sql, in Solution Explorer.
2.
Highlight and execute the first command block as shown:
USE tempdb; GO
3.
Highlight and execute scripts 17.1 to 17.9 separately, comparing the results of each script with the script comment. -- Script 17.1 Create a table with an xml column CREATE TABLE #Invoices ( InvoiceID int, SalesDate datetime, CustomerID int, ItemList xml); GO -- Script 17.2 Use implicit casting to assign an xml variable DECLARE @itemString nvarchar(2000); SET @itemString = ' '; DECLARE @itemDoc xml; SET @itemDoc = @itemString;
3
4
Lab Answer Key: Storing XML Data in SQL Server 2008 R2
INSERT INTO #Invoices VALUES (1, GetDate(), 2, @itemDoc); SELECT * FROM #Invoices; GO -- Script 17.3 Use implicit casting to assign a string constant INSERT INTO #Invoices VALUES (1, GetDate(), 2, ' '); SELECT * FROM #Invoices; GO -- Script 17.4 Explicitly cast string to xml DECLARE @varToCast nvarchar(2000); SET @varToCast = ' '; DECLARE @castedDoc xml; SET @castedDoc = CAST(@varToCast AS xml); INSERT INTO #Invoices VALUES(1, GetDate(), 2, @castedDoc); SELECT * FROM #Invoices; GO -- Script 17.5 Explicitly convert string to xml DECLARE @varToConvert nvarchar(2000); SET @varToConvert = ' '; DECLARE @convertedDoc xml; SET @convertedDoc = CONVERT(xml, @varToConvert); INSERT INTO #Invoices VALUES(1, GetDate(), 2, @convertedDoc); SELECT * FROM #Invoices; GO -- Script 17.6 Well-formed document. This will succeed INSERT INTO #Invoices VALUES (1, GetDate(), 2, ' '); SELECT * FROM #Invoices; GO -- Script 17.7 Well-formed fragment. This will succeed INSERT INTO #Invoices VALUES (1, GetDate(), 2, ' ');
Lab Answer Key: Storing XML Data in SQL Server 2008 R2
SELECT * FROM #Invoices; GO -- Script 17.8 Not well-formed. This will fail INSERT INTO #Invoices VALUES (1, GetDate(), 2, ' '); SELECT * FROM #Invoices; GO -- Script 17.9 Clean up the temporary table DROP TABLE #Invoices; GO
4.
Review the results from each script. Query Number
Query Title
Output
17.1
Create a table with an xml column.
Command completed successfully.
17.2
Use implicit casting to assign an xml variable.
One row is inserted.
17.3
Use implicit casting to assign a string constant.
One row is inserted.
17.4
Explicitly cast string to xml.
One row is inserted.
17.5
Explicitly convert string to xml.
One row is inserted.
17.6
Well-formed document. This will succeed.
One row is inserted.
17.7
Well-formed fragment. This will succeed. One row is inserted.
17.8
Not well-formed. This will fail.
Command will fail due to incorrect XML format.
17.9
Clean up the temporary table.
Command completed successfully.
Exercise 3: Investigate the use of XML Schema Collections Task 1: Review and execute the queries, and review the results 1.
Double-click the script 71 – Lab Exercise 3.sql, in Solution Explorer.
2.
Highlight and execute the first command block as shown:
USE tempdb; GO
5
6
Lab Answer Key: Storing XML Data in SQL Server 2008 R2
3.
Highlight and execute scripts 17.10 to 17.11 separately, comparing the results of each script with the script comment. -- Script 17.10 Create an xml schema collection for resumes CREATE XML SCHEMA COLLECTION ResumeSchemaCollection AS N' '; GO -- Script 17.11 Retrieve information about the components in the schema collection -Note that schemas are stored as components, not as the original -schema text SELECT cp.* FROM sys.xml_schema_components AS cp JOIN sys.xml_schema_collections AS c ON cp.xml_collection_id = c.xml_collection_id WHERE c.name = 'ResumeSchemaCollection'; GO
4.
Review the results from each script. Query Number
Query Title
Output
17.10
Create an xml schema collection for resumes.
Schema collection is created.
17.11
Retrieve information about Ten rows showing how the individual components of the XML schema collection are stored. Note the the components in the schema collection values in the kind_desc column.
Lab Answer Key: Storing XML Data in SQL Server 2008 R2
Challenge Exercise 4: Investigate the Creation of Database Columns Based on XML (Only if time permits) Task 1: Review and execute the queries, and review the results 1.
Double-click the script 81 – Lab Exercise 4.sql, in Solution Explorer.
2.
Highlight and execute the first command block as shown:
USE tempdb; GO
3.
Highlight and execute scripts 17.12 to 17.20 separately, comparing the results of each script with the script comment. -- Script 17.12 Create a table with a typed xml column for documents CREATE TABLE #EmployeeResume ( EmployeeID int, Resume xml (ResumeSchemaCollection)); GO -- Script 17.13 Insert valid XML INSERT INTO #EmployeeResume VALUES (1, ' Guy Gilbert Northwind Traders Adventure Works '); SELECT * FROM #EmployeeResume; GO -- Script 17.14 Try to insert invalid XML (this will fail) INSERT INTO #EmployeeResume VALUES (1, ' Guy Gilbert '); GO -- Script 17.15 Use CONTENT keyword CREATE TABLE #EmployeeResumeContent ( EmployeeID int, Resume xml (CONTENT ResumeSchemaCollection)); GO -- Script 17.16 Insert a single document into a CONTENT column INSERT INTO #EmployeeResumeContent VALUES (1, ' Guy Gilbert Northwind Traders Adventure Works ');
7
8
Lab Answer Key: Storing XML Data in SQL Server 2008 R2
SELECT * FROM #EmployeeResumeContent; GO -- Script 17.17 Insert multiple valid elements into a CONTENT column INSERT INTO #EmployeeResumeContent VALUES (1, ' Guy Gilbert Northwind Traders Guy Gilbert Northwind Traders Adventure Works '); SELECT * FROM #EmployeeResumeContent; GO -- Script 17.18 Use DOCUMENT keyword CREATE TABLE #EmployeeResumeDocument ( EmployeeID int, Resume xml (DOCUMENT ResumeSchemaCollection)); GO -- Script 17.19 Insert a single document into a DOCUMENT column INSERT INTO #EmployeeResumeDocument VALUES (1, ' Guy Gilbert Northwind Traders Adventure Works '); SELECT * FROM #EmployeeResumeDocument; GO -- Script 17.20 Insert multiple valid elements into a DOCUMENT column. This will fail. INSERT INTO #EmployeeResumeDocument VALUES (1, ' Guy Gilbert Northwind Traders Guy Gilbert Northwind Traders Adventure Works ');
Lab Answer Key: Storing XML Data in SQL Server 2008 R2
SELECT * FROM #EmployeeResumeDocument; GO
4.
Review the results from each script. Query Number
Query Title
Output
17.12
Create a table with a typed xml column Command completed successfully. for documents.
17.13
Insert valid XML.
One row is inserted.
17.14
Try to insert invalid XML (this will fail).
No rows are inserted. Even though wellformed XML was supplied, it did not meet the required schema.
17.15
Use CONTENT keyword.
Command completed successfully.
17.16
Insert a single document into a CONTENT column.
One row is inserted.
17.17
Insert multiple valid elements into a CONTENT column.
One row is inserted. Note the row is inserted, even though no root element is present.
17.18
Use DOCUMENT keyword.
Command completed successfully.
17.19
Insert a single document into a DOCUMENT column.
One row is inserted.
17.20
Insert multiple valid elements into a DOCUMENT column. This will fail.
No row is inserted as no root element is present.
9
Lab Answer Key: Querying XML Data in SQL Server
Module 18 Lab Answer Key: Querying XML Data in SQL Server Contents: Exercise 1: Learn to query SQL Server data as XML
2
Exercise 2: Write a stored procedure returning XML
4
Challenge Exercise 3: Write a stored procedure that updates using XML (Only if time permits)
5
1
2
Lab Answer Key: Querying XML Data in SQL Server
Lab: Querying XML Data in SQL Server Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. 4. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. 5. In Virtual Machine Connection window, click on the Revert toolbar icon. 6. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. 7. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 1. 2. 3.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_18_PRJ\6232B_18_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Learn to query SQL Server data as XML Task 1: Review and execute the query and review the results 1.
Double-click the script 51 – Lab Exercise 1.sql, in Solution Explorer.
In the query below, highlight and execute scripts 18.1 to 18.9 separately, comparing the results of each script with the script comment.
Lab Answer Key: Querying XML Data in SQL Server
-- Script 18.1 FOR XML AUTO Use MarketDev GO SELECT ProductID, ProductName, ListPrice FROM Marketing.Product FOR XML AUTO; GO -- Script 18.2 ELEMENTS with RAW mode SELECT ProductID, ProductName, ListPrice FROM Marketing.Product FOR XML RAW, ELEMENTS; GO -- Script 18.3 NULL columns with ELEMENTS SELECT ProductID, ProductName, Color FROM Marketing.Product FOR XML AUTO, ELEMENTS XSINIL; GO -- Script 18.4 Note the effect of the column alias compared to 18.3 SELECT ProductID, ProductName, Color FROM Marketing.Product AS Product FOR XML AUTO, ELEMENTS XSINIL; GO -- Script 18.5 Inline XSD schema SELECT ProductID, ProductName, ListPrice FROM Marketing.Product AS Product FOR XML AUTO, XMLSCHEMA; GO -- Script 18.6 Nested XML with TYPE SELECT ProductID, ProductName, ListPrice, (SELECT Description.ProductModelID, Description.Description FROM Marketing.ProductDescription AS Description WHERE Description.ProductModelID = Product.ProductModelID FOR XML AUTO, ELEMENTS, TYPE) AS Description FROM Marketing.Product AS Product; GO -- Script 18.7 PATH mode SELECT ProductID AS "@ProductID", ProductName AS "*", Size AS "Description/@Size", Color AS "Description/text()" FROM Marketing.Product AS Product FOR XML PATH; GO -- Script 18.8 ROOT directive SELECT ProductID, ProductName, ListPrice FROM Marketing.Product AS Product FOR XML AUTO, ROOT('AvailableItems'); GO -- Script 18.9 Named element in RAW modes SELECT ProductID, ProductName, ListPrice FROM Marketing.Product AS Product FOR XML RAW('AvailableItem');
3
4
Lab Answer Key: Querying XML Data in SQL Server
GO
Query Number
Query Title
Output
18.1
FOR XML AUTO.
One row per product. Note the element name is based on the table name.
18.2
ELEMENTS with RAW mode.
Note the element centric output and the element name is “row”.
18.3
NULL columns with ELEMENTS.
Products without color show xsi:nil=”true”. Other products show the color.
18.4
Note the effect of the Note the element name is now Product, based on the alias column alias name of the table. compared to 18.3.
18.5
Inline XSD schema.
Note the inclusion of an XSD schema.
18.6
Nested XML with TYPE.
Note that rows with a value in the Description column show that value as XML.
18.7
PATH Mode.
Note how the output can be constructed with a PATH query. In the output locate each of the elements in the SELECT clause.
18.8
ROOT directive.
Note the “AvailableItems” root node.
18.9
Named element in RAW modes.
Note the “AvailableItem” ElementName.
Exercise 2: Write a stored procedure returning XML Task 1: Review the requirements 1.
Review the supplied stored procedure specification in the supporting documentation for WebStock.GetAvailableModelsAsXML.
Task 2: Create the stored procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE PROCEDURE WebStock.GetAvailableModelsAsXML AS BEGIN SELECT p.ProductID, p.ProductName, p.ListPrice, p.Color, p.SellStartDate, pm.ProductModelID, pm.ProductModel FROM Marketing.Product AS p
Lab Answer Key: Querying XML Data in SQL Server
INNER JOIN Marketing.ProductModel AS pm ON p.ProductModelID = pm.ProductModelID WHERE p.SellStartDate IS NOT NULL AND p.SellEndDate IS NULL ORDER BY p.SellStartDate, p.ProductName FOR XML RAW('AvailableModel'), ROOT('AvailableModels'); END; GO
3.
In the toolbar, click Execute.
Task 3: Test the stored procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: EXEC WebStock.GetAvailableModelsAsXML; GO
3.
In the toolbar, click Execute.
Challenge Exercise 3: Write a stored procedure that updates using XML (Only if time permits) Task 1: Review the requirements 1.
Review the supplied stored procedure specification in the supporting documentation for Marketing.UpdateSalesTerritoriesByXML.
Task 2: Create the stored procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE PROCEDURE Marketing.UpdateSalesTerritoriesByXML ( @SalespersonMods xml ) AS BEGIN DECLARE @Handle int; EXEC master.dbo.sp_xml_preparedocument @Handle OUTPUT, @SalespersonMods; UPDATE sp SET sp.SalesTerritoryID = spm.SalesTerritoryID FROM Marketing.Salesperson AS sp INNER JOIN OPENXML(@Handle,'SalespersonMods/SalespersonMod/Mods/Mod',1) WITH (SalesTerritoryID int '@SalesTerritoryID', SalespersonID int '../../@SalespersonID') AS spm ON sp.SalespersonID = spm.SalespersonID; EXEC master.dbo.sp_xml_removedocument @Handle; END; GO
3.
In the toolbar, click Execute.
Task 3: Test the stored procedure 1.
Right-click the MarketDev database and click New Query.
5
6
Lab Answer Key: Querying XML Data in SQL Server
2.
Type the query below in the query pane: DECLARE @SalespersonMods xml; SET @SalespersonMods = ' '; EXEC Marketing.UpdateSalesTerritoriesByXML @SalespersonMods; GO SELECT * FROM Marketing.Salesperson; GO
3.
In the toolbar, click Execute.
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
Module 19 Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data Contents: Exercise 1: Familiarity With Geometry Data Type Exercise 2: Adding Spatial Data to an Existing Table
2 10
Challenge Exercise 3: Business Application of Spatial Data (Only if time permits)
11
1
2
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
Lab: Working with SQL Server Spatial Data Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. 4. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. 5. In Virtual Machine Connection window, click on the Revert toolbar icon. 6. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. 7. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 1. 2. 3.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_19_PRJ\6232B_19_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Familiarity With Geometry Data Type Task 1: Review and execute the sample script 1.
Double-click the script 51 – Lab Exercise 1.sql, in Solution Explorer.
2.
In the query below, highlight and execute scripts 19.1 to 19.9 separately, comparing the results of each script with the script comment.
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
-- Script 19.1 Draw a square USE tempdb; GO DECLARE @Shape GEOMETRY; SET @Shape = GEOMETRY::STGeomFromText('POLYGON ((10 10, 10 40, 40 40, 40 10, 10 10))',0); SELECT @Shape AS Square; GO -- Script 19.2 Try an invalid value - note the 6522 error and the wrapped error message BEGIN TRY DECLARE @Shape GEOMETRY; SET @Shape = GEOMETRY::STPolyFromText('POLYGON((0 0,20 15,10 10,0 15,5 5))', 0); END TRY BEGIN CATCH SELECT ERROR_MESSAGE(); END CATCH; GO -- Script 19.3 Draw a more complex shape DECLARE @Shape GEOMETRY; SET @Shape = GEOMETRY::STGeomFromText('POLYGON ((10 10, 5 5,10 40, 40 40, 45 45,40 10, 10 10))',0); SELECT @Shape AS ColoredArea; GO -- Script 19.4 Multiple shapes DECLARE @Shape1 GEOMETRY, @Shape2 GEOMETRY; SET @Shape1 = GEOMETRY::STGeomFromText('POLYGON ((10 10, 5 5,10 40, 40 40, 45 45,40 10, 10 10))',0); SET @Shape2 = GEOMETRY::STGeomFromText('POLYGON ((60 25, 55 5,60 40, 90 40, 95 45,90 10, 60 25))',0); SELECT @Shape1 AS Multishapes UNION ALL SELECT @Shape2; GO -- Script 19.5 Intersecting shapes DECLARE @Shape1 GEOMETRY, @Shape2 GEOMETRY; SET @Shape1 = GEOMETRY::STGeomFromText('POLYGON ((10 10, 5 5,10 40, 40 40, 45 45,40 10, 10 10))',0); SET @Shape2 = GEOMETRY::STGeomFromText('POLYGON ((30 25, 25 5,30 40, 60 40, 65 45,60 20, 30 25))',0); SELECT @Shape1 AS Overlapping UNION ALL SELECT @Shape2; GO -- Script 19.6 Union of the two shapes DECLARE @Shape1 GEOMETRY, @Shape2 GEOMETRY; SET @Shape1 = GEOMETRY::STGeomFromText('POLYGON ((10 10, 5 5,10 40, 40 40, 45 45,40 10, 10 10))',0); SET @Shape2 = GEOMETRY::STGeomFromText('POLYGON ((30 25, 25 5,30 40, 60 40, 65 45,60 20, 30 25))',0); SELECT @Shape1.STUnion(@Shape2) AS Unioned; GO -- Script 19.7 Intersection of shapes DECLARE @Shape1 GEOMETRY, @Shape2 GEOMETRY; SET @Shape1 = GEOMETRY::STGeomFromText('POLYGON ((10 10, 5 5,10 40, 40 40, 45 45,40 10, 10 10))',0);
3
4
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
SET @Shape2 = GEOMETRY::STGeomFromText('POLYGON ((30 25, 25 5,30 40, 60 40, 65 45,60 20, 30 25))',0); SELECT @Shape1 AS Intersected UNION ALL SELECT @Shape2 UNION ALL SELECT @Shape1.STIntersection(@Shape2); GO -- Script 19.8 Draw Australia DECLARE @Australia geometry; SET @Australia = geometry::STGeomFromText('MULTIPOLYGON (((146.91639709472656 43.704837799072266, 145.92631530761719 -43.223194122314453, 145.4830322265625 -42.650703430175781, 145.38616943359375 -42.464195251464844, 145.82795715332031 -42.501945495605469, 145.07258605957031 -41.577156066894531, 144.90054321289063 -40.845218658447266, 146.58587646484375 -41.291374206542969, 147.80001831054688 -41.147617340087891, 148.25955200195313 -40.902679443359375, 148.47319030761719 -41.075611114501953, 148.52291870117188 -41.788833618164062, 148.12933349609375 -42.806430816650391, 147.34538269042969 -43.239715576171875, 146.91639709472656 -43.704837799072266)), ((142.54121398925781 -10.898410797119141, 142.84793090820313 -11.07573413848877, 143.02713012695313 -11.901708602905273, 143.37675476074219 -12.104367256164551, 143.17445373535156 -12.517995834350586, 143.57481384277344 -12.796828269958496, 143.94683837890625 -14.448851585388184, 144.26838684082031 -14.575309753417969, 144.58901977539063 -14.352115631103516, 144.73284912109375 -14.650595664978027, 145.38800048828125 -15.030624389648438, 145.52920532226563 -16.473457336425781, 146.19784545898438 -17.49560546875, 146.12821960449219 -18.239631652832031, 146.52542114257813 -19.065319061279297, 147.61637878417969 -19.546205520629883, 148.17660522460938 20.079109191894531, 148.90739440917969 -20.185646057128906, 148.77070617675781 -20.637355804443359, 149.30514526367188 -21.04315185546875, 149.76042175292969 -22.415578842163086, 150.22093200683594 22.713462829589844, 150.1484375 -22.306673049926758, 150.89729309082031 -22.66114616394043, 151.00613403320313 -23.582916259765625, 151.93380737304688 -24.14051628112793, 152.27639770507813 24.724786758422852, 152.52163696289063 -24.800580978393555, 152.68940734863281 -25.296232223510742, 153.03501892089844 -25.454490661621094, 153.27273559570313 -26.210777282714844, 153.29037475585938 26.993001937866211, 153.14988708496094 -27.324020385742188, 153.60006713867188 -28.111736297607422, 153.69715881347656 -28.823896408081055, 153.14321899414063 -30.682428359985352, 153.17552185058594 31.401203155517578, 152.37985229492188 -32.859695434570312, 151.96830749511719 -32.987766265869141, 151.56816101074219 -33.503929138183594, 150.96563720703125 -35.236156463623047, 150.4251708984375 35.6771354675293, 150.18850708007813 -37.329910278320312, 149.91603088378906 -37.777362823486328, 148.24066162109375 -38.080898284912109, 147.02470397949219 -38.824363708496094, 146.50288391113281 38.876548767089844, 146.72958374023438 -39.163665771484375, 145.73957824707031 -38.70556640625, 145.70791625976563 -38.424533843994141, 145.17349243164063 -38.048786163330078, 144.61477661132813 38.228744506835938, 144.95124816894531 -38.438838958740234, 143.56846618652344 -38.9527587890625, 142.10769653320313 -38.41265869140625, 141.42892456054688 -38.439579010009766, 140.21568298339844 37.624660491943359, 140.02616882324219 -37.229286193847656, 140.02934265136719 -36.578449249267578, 139.64814758300781
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
-36.024333953857422, 139.57157897949219 -35.5973014831543, 138.5560302734375 35.8480339050293, 138.40083312988281 -35.739955902099609, 138.63349914550781 -35.535476684570312, 138.77128601074219 -34.910751342773438, 138.30516052246094 -34.339134216308594, 137.88699340820313 35.313907623291016, 137.15071105957031 -35.455646514892578, 137.31202697753906 -35.053794860839844, 137.70594787597656 -35.008373260498047, 137.71554565429688 -34.442138671875, 138.11616516113281 33.499790191650391, 138.39241027832031 -33.276569366455078, 138.0718994140625 -32.730152130126953, 138.02352905273438 -33.048313140869141, 137.66084289550781 -33.297161102294922, 137.27078247070313 33.934112548828125, 136.30978393554688 -34.514995574951172, 135.91609191894531 -35.190467834472656, 134.91389465332031 -33.347339630126953, 134.41212463378906 -33.240230560302734, 134.31146240234375 33.011432647705078, 134.44065856933594 -32.724918365478516, 134.04069519042969 -32.325000762939453, 133.32875061035156 -32.320106506347656, 132.90133666992188 -32.072917938232422, 132.38589477539063 32.131229400634766, 131.48382568359375 -31.617877960205078, 129.38905334472656 -31.781232833862305, 127.46874237060547 -32.364013671875, 126.26261138916016 -32.417392730712891, 124.45204925537109 33.127155303955078, 123.56221771240234 -34.025871276855469, 122.37458801269531 -34.161888122558594, 121.61318206787109 -33.953533172607422, 119.97515106201172 -34.103199005126953, 119.53349304199219 34.562080383300781, 118.87643432617188 -34.588871002197266, 118.41074371337891 -35.073368072509766, 117.99697875976563 -35.195281982421875, 116.45505523681641 -35.0837516784668, 115.25058746337891 34.405483245849609, 115.189453125 -33.737743377685547, 115.61216735839844 -33.749591827392578, 115.76168060302734 -33.558475494384766, 115.950927734375 -32.190639495849609, 115.26192474365234 30.786540985107422, 115.11587524414063 -29.680290222167969, 114.83406066894531 -28.885986328125, 114.16830444335938 -28.016206741333008, 114.12889099121094 -27.367696762084961, 113.23877716064453 26.145689010620117, 113.29524993896484 -26.040727615356445, 113.63665008544922 -26.555038452148438, 113.94303894042969 -26.649562835693359, 113.52482604980469 -25.673364639282227, 114.08432006835938 26.4920597076416, 114.29875946044922 -26.521648406982422, 114.25409698486328 -26.271642684936523, 114.38450622558594 -26.254417419433594, 114.38618469238281 -26.000068664550781, 113.58287048339844 24.596633911132812, 113.56556701660156 -24.26606559753418, 113.96568298339844 -23.464162826538086, 113.82909393310547 -22.548957824707031, 114.10423278808594 -22.02070426940918, 114.23101043701172 22.503608703613281, 114.43566131591797 -22.598531723022461, 114.66001129150391 -22.076744079589844, 116.62245941162109 -20.820150375366211, 117.80059051513672 -20.741361618041992, 118.17213439941406 20.454486846923828, 118.93798065185547 -20.300273895263672, 119.13991546630859 -20.007402420043945, 120.01618194580078 -20.100902557373047, 121.07581329345703 -19.659969329833984, 121.77999877929688 18.685775756835938, 122.40011596679688 -18.226715087890625, 122.28913879394531 -17.381425857543945, 122.94200134277344
5
6
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
-16.566312789916992, 123.62236785888672 -17.562946319580078, 123.71196746826172 17.092321395874023, 124.03050994873047 -17.231498718261719, 124.11570739746094 -16.957944869995117, 123.75660705566406 -16.678993225097656, 123.77950286865234 -16.373815536499023, 125.05001068115234 16.513069152832031, 124.54142761230469 -16.245037078857422, 124.62816619873047 -15.640926361083984, 124.89231109619141 -15.449649810791016, 125.31279754638672 -15.607682228088379, 125.35932159423828 14.7875337600708, 126.06686401367188 -14.677942276000977, 126.47202301025391 -14.054102897644043, 126.74971008300781 -14.333209991455078, 127.05709075927734 -13.766800880432129, 127.73275756835938 14.178533554077148, 128.16622924804688 -14.686156272888184, 128.26980590820313 -15.359770774841309, 128.66252136230469 -14.869436264038086, 129.40008544921875 -14.975923538208008, 129.8359375 15.261057853698731, 130.05227661132813 -14.802887916564941, 129.63021850585938 -14.288888931274414, 130.40965270996094 -13.467185020446777, 130.66716003417969 -12.741966247558594, 131.15493774414063 12.613780975341797, 131.06253051757813 -12.429638862609863, 131.23200988769531 -12.251283645629883, 131.74357604980469 -12.383670806884766, 132.95478820800781 -12.152227401733398, 132.71826171875 11.606078147888184, 132.22822570800781 -11.460931777954102, 132.40925598144531 -11.301631927490234, 133.13264465332031 -11.516201972961426, 133.76284790039063 -11.934375762939453, 134.96577453613281 12.0462007522583, 135.33218383789063 -12.369593620300293, 135.85267639160156 -12.171424865722656, 136.29487609863281 -12.558222770690918, 136.50227355957031 -12.494205474853516, 136.63816833496094 12.099803924560547, 136.9947509765625 -12.353300094604492, 136.67274475097656 -13.20579719543457, 136.03456115722656 -13.467802047729492, 136.168212890625 -13.874351501464844, 136.08430480957031 14.186027526855469, 135.66123962402344 -14.66370964050293, 135.62026977539063 -14.956218719482422, 136.89080810546875 -16.008792877197266, 137.27737426757813 -16.014326095581055, 138.4639892578125 16.825523376464844, 139.16641235351563 -16.976600646972656, 139.45198059082031 -17.414670944213867, 140.02192687988281 -17.744054794311523, 140.26387023925781 -17.813465118408203, 140.91120910644531 17.614999771118164, 141.33607482910156 -16.940177917480469, 141.91496276855469 -15.209720611572266, 141.67181396484375 -14.7398042678833, 141.87205505371094 -14.097315788269043, 141.71598815917969 13.792503356933594, 141.802490234375 -13.029497146606445, 142.12260437011719 -12.685539245605469, 141.88218688964844 -12.450845718383789, 142.32931518554688 -11.140419960021973, 142.54121398925781 10.898410797119141 )))',0); SELECT @Australia AS Australia; GO -- Script 19.9 Draw Australia with a buffer around it DECLARE @Australia geometry; SET @Australia = geometry::STGeomFromText('MULTIPOLYGON (((146.91639709472656 43.704837799072266, 145.92631530761719 -43.223194122314453, 145.4830322265625 -42.650703430175781, 145.38616943359375 -42.464195251464844, 145.82795715332031 -42.501945495605469,
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
145.07258605957031 -41.577156066894531, 144.90054321289063 -40.845218658447266, 146.58587646484375 -41.291374206542969, 147.80001831054688 -41.147617340087891, 148.25955200195313 -40.902679443359375, 148.47319030761719 -41.075611114501953, 148.52291870117188 -41.788833618164062, 148.12933349609375 -42.806430816650391, 147.34538269042969 -43.239715576171875, 146.91639709472656 -43.704837799072266)), ((142.54121398925781 -10.898410797119141, 142.84793090820313 -11.07573413848877, 143.02713012695313 -11.901708602905273, 143.37675476074219 -12.104367256164551, 143.17445373535156 -12.517995834350586, 143.57481384277344 -12.796828269958496, 143.94683837890625 -14.448851585388184, 144.26838684082031 -14.575309753417969, 144.58901977539063 -14.352115631103516, 144.73284912109375 -14.650595664978027, 145.38800048828125 -15.030624389648438, 145.52920532226563 -16.473457336425781, 146.19784545898438 -17.49560546875, 146.12821960449219 -18.239631652832031, 146.52542114257813 -19.065319061279297, 147.61637878417969 -19.546205520629883, 148.17660522460938 20.079109191894531, 148.90739440917969 -20.185646057128906, 148.77070617675781 -20.637355804443359, 149.30514526367188 -21.04315185546875, 149.76042175292969 -22.415578842163086, 150.22093200683594 22.713462829589844, 150.1484375 -22.306673049926758, 150.89729309082031 -22.66114616394043, 151.00613403320313 -23.582916259765625, 151.93380737304688 -24.14051628112793, 152.27639770507813 24.724786758422852, 152.52163696289063 -24.800580978393555, 152.68940734863281 -25.296232223510742, 153.03501892089844 -25.454490661621094, 153.27273559570313 -26.210777282714844, 153.29037475585938 26.993001937866211, 153.14988708496094 -27.324020385742188, 153.60006713867188 -28.111736297607422, 153.69715881347656 -28.823896408081055, 153.14321899414063 -30.682428359985352, 153.17552185058594 31.401203155517578, 152.37985229492188 -32.859695434570312, 151.96830749511719 -32.987766265869141, 151.56816101074219 -33.503929138183594, 150.96563720703125 -35.236156463623047, 150.4251708984375 35.6771354675293, 150.18850708007813 -37.329910278320312, 149.91603088378906 -37.777362823486328, 148.24066162109375 -38.080898284912109, 147.02470397949219 -38.824363708496094, 146.50288391113281 38.876548767089844, 146.72958374023438 -39.163665771484375, 145.73957824707031 -38.70556640625, 145.70791625976563 -38.424533843994141, 145.17349243164063 -38.048786163330078, 144.61477661132813 38.228744506835938, 144.95124816894531 -38.438838958740234, 143.56846618652344 -38.9527587890625, 142.10769653320313 -38.41265869140625, 141.42892456054688 -38.439579010009766, 140.21568298339844 37.624660491943359, 140.02616882324219 -37.229286193847656, 140.02934265136719 -36.578449249267578, 139.64814758300781 -36.024333953857422, 139.57157897949219 -35.5973014831543, 138.5560302734375 35.8480339050293, 138.40083312988281 -35.739955902099609, 138.63349914550781 -35.535476684570312, 138.77128601074219 -34.910751342773438, 138.30516052246094 -34.339134216308594, 137.88699340820313 35.313907623291016, 137.15071105957031 -35.455646514892578, 137.31202697753906 -35.053794860839844, 137.70594787597656 -35.008373260498047, 137.71554565429688 -34.442138671875, 138.11616516113281 33.499790191650391, 138.39241027832031 -33.276569366455078, 138.0718994140625 -32.730152130126953, 138.02352905273438 -33.048313140869141, 137.66084289550781 -33.297161102294922, 137.27078247070313 33.934112548828125,
7
8
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
136.30978393554688 -34.514995574951172, 135.91609191894531 -35.190467834472656, 134.91389465332031 -33.347339630126953, 134.41212463378906 -33.240230560302734, 134.31146240234375 33.011432647705078, 134.44065856933594 -32.724918365478516, 134.04069519042969 -32.325000762939453, 133.32875061035156 -32.320106506347656, 132.90133666992188 -32.072917938232422, 132.38589477539063 32.131229400634766, 131.48382568359375 -31.617877960205078, 129.38905334472656 -31.781232833862305, 127.46874237060547 -32.364013671875, 126.26261138916016 -32.417392730712891, 124.45204925537109 33.127155303955078, 123.56221771240234 -34.025871276855469, 122.37458801269531 -34.161888122558594, 121.61318206787109 -33.953533172607422, 119.97515106201172 -34.103199005126953, 119.53349304199219 34.562080383300781, 118.87643432617188 -34.588871002197266, 118.41074371337891 -35.073368072509766, 117.99697875976563 -35.195281982421875, 116.45505523681641 -35.0837516784668, 115.25058746337891 34.405483245849609, 115.189453125 -33.737743377685547, 115.61216735839844 -33.749591827392578, 115.76168060302734 -33.558475494384766, 115.950927734375 -32.190639495849609, 115.26192474365234 30.786540985107422, 115.11587524414063 -29.680290222167969, 114.83406066894531 -28.885986328125, 114.16830444335938 -28.016206741333008, 114.12889099121094 -27.367696762084961, 113.23877716064453 26.145689010620117, 113.29524993896484 -26.040727615356445, 113.63665008544922 -26.555038452148438, 113.94303894042969 -26.649562835693359, 113.52482604980469 -25.673364639282227, 114.08432006835938 26.4920597076416, 114.29875946044922 -26.521648406982422, 114.25409698486328 -26.271642684936523, 114.38450622558594 -26.254417419433594, 114.38618469238281 -26.000068664550781, 113.58287048339844 24.596633911132812, 113.56556701660156 -24.26606559753418, 113.96568298339844 -23.464162826538086, 113.82909393310547 -22.548957824707031, 114.10423278808594 -22.02070426940918, 114.23101043701172 22.503608703613281, 114.43566131591797 -22.598531723022461, 114.66001129150391 -22.076744079589844, 116.62245941162109 -20.820150375366211, 117.80059051513672 -20.741361618041992, 118.17213439941406 20.454486846923828, 118.93798065185547 -20.300273895263672, 119.13991546630859 -20.007402420043945, 120.01618194580078 -20.100902557373047, 121.07581329345703 -19.659969329833984, 121.77999877929688 18.685775756835938, 122.40011596679688 -18.226715087890625, 122.28913879394531 -17.381425857543945, 122.94200134277344 -16.566312789916992, 123.62236785888672 -17.562946319580078, 123.71196746826172 17.092321395874023, 124.03050994873047 -17.231498718261719, 124.11570739746094 -16.957944869995117, 123.75660705566406 -16.678993225097656, 123.77950286865234 -16.373815536499023, 125.05001068115234 16.513069152832031, 124.54142761230469 -16.245037078857422, 124.62816619873047 -15.640926361083984, 124.89231109619141 -15.449649810791016, 125.31279754638672 -15.607682228088379, 125.35932159423828 14.7875337600708, 126.06686401367188 -14.677942276000977, 126.47202301025391 -14.054102897644043, 126.74971008300781 -14.333209991455078, 127.05709075927734 -13.766800880432129, 127.73275756835938 14.178533554077148,
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
128.16622924804688 -14.686156272888184, 128.26980590820313 -15.359770774841309, 128.66252136230469 -14.869436264038086, 129.40008544921875 -14.975923538208008, 129.8359375 15.261057853698731, 130.05227661132813 -14.802887916564941, 129.63021850585938 -14.288888931274414, 130.40965270996094 -13.467185020446777, 130.66716003417969 -12.741966247558594, 131.15493774414063 12.613780975341797, 131.06253051757813 -12.429638862609863, 131.23200988769531 -12.251283645629883, 131.74357604980469 -12.383670806884766, 132.95478820800781 -12.152227401733398, 132.71826171875 11.606078147888184, 132.22822570800781 -11.460931777954102, 132.40925598144531 -11.301631927490234, 133.13264465332031 -11.516201972961426, 133.76284790039063 -11.934375762939453, 134.96577453613281 12.0462007522583, 135.33218383789063 -12.369593620300293, 135.85267639160156 -12.171424865722656, 136.29487609863281 -12.558222770690918, 136.50227355957031 -12.494205474853516, 136.63816833496094 12.099803924560547, 136.9947509765625 -12.353300094604492, 136.67274475097656 -13.20579719543457, 136.03456115722656 -13.467802047729492, 136.168212890625 -13.874351501464844, 136.08430480957031 14.186027526855469, 135.66123962402344 -14.66370964050293, 135.62026977539063 -14.956218719482422, 136.89080810546875 -16.008792877197266, 137.27737426757813 -16.014326095581055, 138.4639892578125 16.825523376464844, 139.16641235351563 -16.976600646972656, 139.45198059082031 -17.414670944213867, 140.02192687988281 -17.744054794311523, 140.26387023925781 -17.813465118408203, 140.91120910644531 17.614999771118164, 141.33607482910156 -16.940177917480469, 141.91496276855469 -15.209720611572266, 141.67181396484375 -14.7398042678833, 141.87205505371094 -14.097315788269043, 141.71598815917969 13.792503356933594, 141.802490234375 -13.029497146606445, 142.12260437011719 -12.685539245605469, 141.88218688964844 -12.450845718383789, 142.32931518554688 -11.140419960021973, 142.54121398925781 10.898410797119141 )))',0); SELECT @Australia.STBuffer(1) UNION ALL SELECT @Australia; GO
3.
Review the results from each script. Remember to click on the Spatial results tab to see the output. Query Number
Query Title
Output
19.1
Draw a square.
A square is drawn.
19.2
Try an invalid value - note the 6522 error and the wrapped error message.
A .NET error is returned. Scroll to the right to read the full message. Note how specific the message is.
19.3
Draw a more complex shape.
The shape is drawn. Note how a polygon is represented in text in the query.
9
10
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
Query Number
Query Title
Output
19.4
Multiple shapes.
Two shapes are drawn.
19.5
Intersecting shapes.
The shapes are moved to intersect.
19.6
Union of two shapes.
A combined shape is drawn.
19.7
Intersection of shapes.
The two intersecting shapes and the intersection are drawn.
19.8
Draw Australia.
Australian map is drawn.
19.9
Draw Australia with a buffer around it.
Note the calculation of a buffer region around the map.
Exercise 2: Adding Spatial Data to an Existing Table Task 1: Add a Location column 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: ALTER TABLE Marketing.ProspectLocation ADD Location GEOGRAPHY NULL; GO
3.
In the toolbar, click Execute.
Task 2: Write code to assign values to this column 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: UPDATE Marketing.ProspectLocation SET Location = GEOGRAPHY::STGeomFromText( 'POINT(' + CAST(Longitude AS varchar(20)) + ' ' + CAST(Latitude AS varchar(20)) + ')',4326); GO
3.
In the toolbar, click Execute.
Task 3: Drop the existing Latitude and Longitude columns 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: ALTER TABLE Marketing.ProspectLocation DROP COLUMN Latitude; GO
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
ALTER TABLE Marketing.ProspectLocation DROP COLUMN Longitude; GO
3.
In the toolbar, click Execute.
Challenge Exercise 3: Business Application of Spatial Data (Only if time permits) Task 1: Review the requirements 1.
Review the supporting documentation for details of the required stored procedure.
Task 2: Create a spatial index 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE SPATIAL INDEX IX_ProspectLocation_Location ON Marketing.ProspectLocation (Location); GO
3.
In the toolbar, click Execute.
Task 3: Design and implement the Stored Procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE PROCEDURE Marketing.GetNearbyProspects ( @ProspectID int, @DistanceInKms int ) AS BEGIN DECLARE @LocationToTest GEOGRAPHY; SET @LocationToTest = (SELECT pl.Location FROM Marketing.ProspectLocation AS pl WHERE pl.ProspectID = @ProspectID); SELECT pl.Location.STDistance(@LocationToTest) / 1000 AS Distance, p.ProspectID, p.LastName, p.FirstName, p.WorkPhoneNumber, p.CellPhoneNumber, pl.AddressLine1, pl.AddressLine2, pl.City, pl.Location.Long AS Longitude, pl.Location.Lat AS Latitude FROM Marketing.Prospect AS p INNER JOIN Marketing.ProspectLocation AS pl ON p.ProspectID = pl.ProspectID WHERE pl.Location.STDistance(@LocationToTest) < (@DistanceInKms * 1000) AND p.ProspectID @ProspectID ORDER BY Distance;
11
12
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data
END; GO
3.
In the toolbar, click Execute.
Task 4: Test the procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: EXEC Marketing.GetNearbyProspects 2,50;
3.
In the toolbar, click Execute.
Lab Answer Key: Working with Full-Text Indexes and Queries
1
Module 20 Lab Answer Key: Working with Full-Text Indexes and Queries Contents: Exercise 1: Implement a full-text index
2
Exercise 2: Implement a stoplist
4
Challenge Exercise 3: Create a stored procedure to implement a full-text search (Only if time permits)
5
2
Lab Answer Key: Working with Full-Text Indexes and Queries
Lab: Working with Full -Text Indexes and Queries Lab Setup For this lab, you will use the available virtual machine environment. Before you begin the lab, you must complete the following steps: On the host computer, click Start, point to Administrative Tools, and then click Hyper-V Manager. Maximize the Hyper-V Manager window. In the Virtual Machines list, if the virtual machine 623XB-MIA-DC is not started: • Right-click 623XB-MIA-DC and click Start. • Right-click 623XB-MIA-DC and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears, and then close the Virtual Machine Connection window. 4. In the Virtual Machines list, if the virtual machine 623XB-MIA-SQL is not started: • Right-click 623XB-MIA-SQL and click Start. • Right-click 623XB-MIA-SQL and click Connect. • In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on message appears. 5. In Virtual Machine Connection window, click on the Revert toolbar icon. 6. If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to complete. 7. In the Virtual Machine Connection window, if the user is not already logged on: • On the Action menu, click the Ctrl-Alt-Delete menu item. • Click Switch User, and then click Other User. • Log on using the following credentials: i. User name: AdventureWorks\Administrator ii. Password: Pa$$w0rd 8. From the View menu, in the Virtual Machine Connection window, click Full Screen Mode. 9. If the Server Manager window appears, check the Do not show me this console at logon check box and close the Server Manager window. 10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2008 R2, and click SQL Server Management Studio. 1. 2. 3.
11. In Connect to Server window, type Proseware in the Server name text box. 12. In the Authentication drop-down list box, select Windows Authentication and click Connect. 13. In the File menu, click Open, and click Project/Solution. 14. In the Open Project window, open the project D:\6232B_Labs\6232B_20_PRJ\6232B_20_PRJ.ssmssln. 15. In Solution Explorer, double-click the query 00-Setup.sql. When the query window opens, click Execute on the toolbar.
Exercise 1: Implement a full-text index Task 1: Create a full-text catalog 1.
In Object Explorer, expand the Proseware server, expand Databases, right-click the MarketDev database and click New Query.
Lab Answer Key: Working with Full-Text Indexes and Queries
2.
Type the query below in the query pane: CREATE FULLTEXT CATALOG DefaultFullTextCatalog AS DEFAULT; GO
3.
In the toolbar, click Execute.
Task 2: Create a full-text index on the Description column of the Marketing.ProductDescription table 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE FULLTEXT INDEX ON Marketing.ProductDescription(Description) KEY INDEX PK_ProductDescription WITH CHANGE_TRACKING OFF, NO POPULATION; GO
3.
In the toolbar, click Execute.
Task 3: Enable automatic change tracking for the index 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: ALTER FULLTEXT INDEX ON Marketing.ProductDescription SET CHANGE_TRACKING AUTO; GO
3.
In the toolbar, click Execute. Note that a warning might be returned when you execute the above command, even though the command executed successfully. If a warning appears, it can be ignored.
Task 4: Check to see that the population of the index is complete 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT is_enabled, has_crawl_completed, crawl_type, crawl_start_date, crawl_end_date FROM sys.fulltext_indexes; GO
3.
In the toolbar, click Execute.
4.
Re-execute the query until the has_crawl_completed column shows the value 1.
Task 5: Write a simple CONTAINS query 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT d.ProductDescriptionID, d.Description FROM Marketing.ProductDescription AS d
3
4
Lab Answer Key: Working with Full-Text Indexes and Queries
WHERE CONTAINS(d.Description,'bottle') ORDER BY d.ProductDescriptionID; GO
3.
In the toolbar, click Execute. Note: Three rows are returned.
Task 6: Write a CONTAINS query that searches for two words 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT d.ProductDescriptionID, d.Description FROM Marketing.ProductDescription AS d WHERE CONTAINS(d.Description,'elastic AND lycra') ORDER BY d.ProductDescriptionID; GO
3.
In the toolbar, click Execute. Note: One row is returned.
Task 7: Write a CONTAINS query that uses INFLECTIONAL forms 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT d.ProductDescriptionID, d.Description FROM Marketing.ProductDescription AS d WHERE CONTAINS(d.Description,'FORMSOF(INFLECTIONAL,wash)') ORDER BY d.ProductDescriptionID; GO
3.
In the toolbar, click Execute. Note: One row is returned.
Exercise 2: Implement a stoplist Task 1: Review the existing system stopwords 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT * FROM sys.fulltext_system_stopwords WHERE language_id = 1033; GO
3.
In the toolbar, click Execute. A list of system stop words for the English language is returned.
Lab Answer Key: Working with Full-Text Indexes and Queries
Task 2: Create a full-text stoplist 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE FULLTEXT STOPLIST CommonWords; GO
3.
In the toolbar, click Execute.
Task 3: Add words to the stoplist 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: ALTER ADD ALTER ADD GO
3.
FULLTEXT STOPLIST CommonWords 'Bike' LANGUAGE 1033; FULLTEXT STOPLIST CommonWords 'AdventureWorks' LANGUAGE 1033;
In the toolbar, click Execute.
Task 4: View the stoplist 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: SELECT * FROM sys.fulltext_stoplists; GO SELECT * FROM sys.fulltext_stopwords; GO
3.
In the toolbar, click Execute. A list of stoplists and stopwords is returned.
Challenge Exercise 3: Create a stored procedure to implement a full-text search (Only if time permits) Task 1: Review the supporting documentation 1.
Review the supporting documentation for specification for a new stored procedure.
Task 2: Design, implement, and test the stored procedure 1.
Right-click the MarketDev database and click New Query.
2.
Type the query below in the query pane: CREATE PROCEDURE Marketing.GetRelevantDescriptions ( @PhraseToSearch nvarchar(1000) ) AS BEGIN SELECT d.ProductDescriptionID, d.Description,
5
6
Lab Answer Key: Working with Full-Text Indexes and Queries
ft.RANK AS Ranking FROM Marketing.ProductDescription AS d INNER JOIN FREETEXTTABLE(Marketing.ProductDescription, Description, @PhraseToSearch) AS ft ON d.ProductDescriptionID = ft.[KEY] WHERE d.LanguageID = 'en' ORDER BY ft.RANK DESC; END; GO EXEC Marketing.GetRelevantDescriptions 'strong frame'; GO EXEC Marketing.GetRelevantDescriptions 'serious competition'; GO
3.
In the toolbar, click Execute. Note: The first query returns twenty rows. The second query returns five rows.
