qr code generator c# e xercise 2 in Visual C#.NET

Generating QR-Code in Visual C#.NET e xercise 2

In this exercise, you optimize the query by modifying the primary key constraints to be nonclustered indexes and then creating appropriate clustered indexes.

1. 2.

Open SSMS, if necessary, and connect to the appropriate instance of SQL Server 2008. In a new query window, type and execute the following SQL statements to create the TestDB database, the Test schema, and the two tables that are used in this exercise:

ALTER TABLE Test.SalesOrderDetail ADD CONSTRAINT PKSalesOrderDetail PRIMARY KEY(SalesOrderDetailID);

In the existing query window, type, highlight, and execute the following SQL statements to modify the primary key constraint on the Test.SalesOrderHeader table to become a nonclustered index and then create an appropriate clustered index for the query:

-- Modify the PK to be a non-clustered index. ALTER TABLE Test.SalesOrderHeader DROP CONSTRAINT PKSalesOrderHeader;

ALTER TABLE Test.SalesOrderHeader ADD CONSTRAINT PKSalesOrderHeader PRIMARY KEY NONCLUSTERED (SalesOrderID);

-- Create the clustered index. CREATE CLUSTERED INDEX CluIdx ON Test.SalesOrderHeader (TerritoryID, CustomerID);

In the existing query window, type, highlight, and execute the following SQL statements to modify the primary key constraint on the Test.SalesOrderDetail table to become a nonclustered index and then create an appropriate clustered index for the query:

-- Modify the PK to be a non-clustered index. ALTER TABLE Test.SalesOrderDetail DROP CONSTRAINT PKSalesOrderDetail;

ALTER TABLE Test.SalesOrderDetail ADD CONSTRAINT PKSalesOrderDetail PRIMARY KEY NONCLUSTERED (SalesOrderDetailID);

-- Create the clustered index. CREATE CLUSTERED INDEX CluIdx ON Test.SalesOrderDetail (SalesOrderID);

5. 6.

Turn on the Actual Execution Plan feature in SSMS by pressing Ctrl+M or by selecting Include Actual Execution Plan from the Query menu. In the existing query window, type, highlight, and execute the following SQL statement to turn on the reporting of page reads:

SET STATISTICS IO ON;

In the existing query window, type, highlight, and execute the following SQL statement:

SELECT soh.CustomerID ,SUM(sod.OrderQty * sod.UnitPrice) AS TotalPurchases FROM Test.SalesOrderHeader AS soh INNER JOIN Test.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.TerritoryID = 1 GROUP BY soh.CustomerID;

In the existing query window, type, highlight, and execute the following SQL statement to clean up after this exercise:

USE master; DROP DATABASE TestDB;

1. 2.

Open SSMS, if necessary, and connect to the instance of SQL Server 2008 running on your machine. In a new query window, type and execute the following SQL statements to create the TestDB database, the Test schema, and the two tables that are used in this exercise:

ALTER TABLE Test.SalesOrderDetail ADD CONSTRAINT PKSalesOrderDetail PRIMARY KEY(SalesOrderDetailID);

In the existing query window, type, highlight, and execute the following SQL statement to create the covered nonclustered index that will be used by the query when accessing the Test.SalesOrderHeader table:

CREATE NONCLUSTERED INDEX TestIndex ON Test.SalesOrderHeader (TerritoryID, SalesOrderID) INCLUDE (CustomerID);

In the existing query window, type, highlight, and execute the following SQL statement to create the covered nonclustered index that will be used by the query when accessing the Test.SalesOrderDetail table:

CREATE NONCLUSTERED INDEX TestIndex ON Test.SalesOrderDetail (SalesOrderID) INCLUDE (OrderQty, UnitPrice);

5. 6.

Turn on the Actual Execution Plan feature in SSMS by pressing Ctrl+M or by selecting Include Actual Execution Plan from the Query menu. In the existing query window, type, highlight, and execute the following SQL statement to turn on the reporting of page reads:

SET STATISTICS IO ON;

In the existing query window, type, highlight, and execute the following SQL statement:

FROM Test.SalesOrderHeader AS soh INNER JOIN Test.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.TerritoryID = 1 GROUP BY soh.CustomerID;

In the existing query window, type, highlight, and execute the following SQL statement to clean up after this exercise:

USE master; DROP DATABASE TestDB;

1. 2.

Open SSMS, if necessary, and connect to the appropriate instance of SQL Server 2008. In a new query window, type and execute the following SQL statements to create the TestDB database, the Test schema, and the two tables that are used in this exercise: