In the following exercise, we ll create an index on the last name in the PersonContact table in .NET

Encode QR Code ISO/IEC18004 in .NET In the following exercise, we ll create an index on the last name in the PersonContact table

In the following exercise, we ll create an index on the last name in the PersonContact table
Drawing QR Code ISO/IEC18004 In .NET Framework
Using Barcode printer for Visual Studio .NET Control to generate, create QR Code image in Visual Studio .NET applications.
QR Code ISO/IEC18004 Reader In .NET
Using Barcode recognizer for .NET Control to read, scan read, scan image in .NET framework applications.
MCITP SQL Server 2005 Database Administration All-in-One Exam Guide
Draw Barcode In VS .NET
Using Barcode printer for VS .NET Control to generate, create barcode image in .NET applications.
Barcode Decoder In VS .NET
Using Barcode reader for VS .NET Control to read, scan read, scan image in .NET framework applications.
Exercise 71: Creating an Index in SSMS 1 Launch SSMS and access the AdventureWorks database 2 Double-click Tables within the AdventureWorks database Double-click the Person Contact table Double-click Indexes Your display should look similar to Figure 7-3
Drawing QR Code In Visual C#
Using Barcode maker for .NET Control to generate, create QR Code ISO/IEC18004 image in VS .NET applications.
QR Code ISO/IEC18004 Creation In .NET Framework
Using Barcode maker for ASP.NET Control to generate, create Quick Response Code image in ASP.NET applications.
Figure 7-3 Object Explorer looking at indexes in the PersonContact table
Generating QR Code In Visual Basic .NET
Using Barcode creation for Visual Studio .NET Control to generate, create Quick Response Code image in VS .NET applications.
Matrix 2D Barcode Generator In Visual Studio .NET
Using Barcode creation for .NET Control to generate, create Matrix Barcode image in .NET framework applications.
Notice that four indexes have been created: one clustered, two nonclustered, and one Primary XML 3 Right-click Indexes and select New Index 4 In the Index name, enter idx_LastName Ensure the Index Type is set as Nonclustered 5 Click Add In the Select Columns from PersonContact dialog box, check the box next to LastName Your display should look similar to Figure 7-4 Click OK
ANSI/AIM Code 128 Maker In Visual Studio .NET
Using Barcode generator for .NET Control to generate, create ANSI/AIM Code 128 image in VS .NET applications.
Print Barcode In .NET
Using Barcode encoder for .NET Control to generate, create bar code image in .NET framework applications.
7: Optimizing Databases
Barcode Drawer In VS .NET
Using Barcode maker for .NET framework Control to generate, create barcode image in Visual Studio .NET applications.
Print USPS PLANET Barcode In Visual Studio .NET
Using Barcode printer for Visual Studio .NET Control to generate, create USPS PLANET Barcode image in VS .NET applications.
Figure 7-4 Adding a column to the index
Making UPC - 13 In None
Using Barcode generator for Software Control to generate, create EAN-13 Supplement 5 image in Software applications.
Printing Code 3 Of 9 In None
Using Barcode printer for Office Word Control to generate, create Code39 image in Microsoft Word applications.
6 Click on Options to access the Options properties page We ll accept the defaults, but I want to point out a couple of these settings You can also view them in Figure 7-5
Data Matrix Decoder In VB.NET
Using Barcode decoder for .NET Control to read, scan read, scan image in Visual Studio .NET applications.
Generating Code 39 Extended In Objective-C
Using Barcode creation for iPhone Control to generate, create Code 3 of 9 image in iPhone applications.
Automatically Recompute Statistics is checked Store intermediate sort results in tempdb is checked
Generate Bar Code In Visual Basic .NET
Using Barcode generator for .NET Control to generate, create bar code image in .NET framework applications.
Code 39 Maker In Java
Using Barcode drawer for Android Control to generate, create Code39 image in Android applications.
Figure 7-5 Index options
Code 3/9 Printer In None
Using Barcode printer for Office Excel Control to generate, create Code39 image in Excel applications.
Generate UPC-A In Java
Using Barcode maker for BIRT Control to generate, create Universal Product Code version A image in BIRT applications.
MCITP SQL Server 2005 Database Administration All-in-One Exam Guide
282 Covering a Query
Covering a query with an index is done to increase the performance of a specific query Our index will include all the referenced columns of the covered query For example, consider a Customer table that s 800MB in size Salespeople frequently run a query that pulls a contact name (first name and last name) and phone number from the Customer table based on the CustomerID Let s say the Customer table is actually 15 columns wide However, we are referencing only four columns in the query: CustomerID, FirstName, LastName, and Phone We could create a composite index of all four columns referenced in the query Now whenever this query is run, instead of searching through the entire 800MB table, the smaller composite index could be searched Covering a query can provide significant performance gains for specific frequently run queries
Set Fill Factor is not checked Use Index is checked, indicating this index will be enabled
7 Click OK to create the index
Composite Index
Indexes can be composed of more than one column When it does use more than one column, we refer to it as a composite index We might use a composite index to increase the selectivity of the index (make the entries more unique) or to cover a query When creating a composite index, one column is referred to as the key column, and the other columns are referred to as the included columns When creating a composite index, it is best to use the less common column as the key column because statistics are only derived and maintained on this column Statistics aren t maintained on included columns In this next exercise, we ll create a composite index using T-SQL statements The two columns we ll include in the index are LastName and FirstName Since LastName is less common than FirstName, the LastName column will be the key column Exercise 72: Creating a Composite Index with T-SQL 1 Launch a New Query window in SSMS 2 Use the following script to drop the existing index created in Exercise 71:
USE AdventureWorks; GO DROP INDEX idx_LastName ON PersonContact;
3 Use the following script to create the new composite index:
USE AdventureWorks; GO
7: Optimizing Databases
CREATE NONCLUSTERED INDEX idx_LastFirstName ON [Person][Contact] (LastName) INCLUDE ( [FirstName]);
We could specify different options in the query, but for this example, we ll accept the default options 4 In SSMS, select Indexes under the PersonContact table, and press F5 to refresh the display You should see the idx_LastFirstName index has been created 5 Right-click the index and select Properties Notice on an existing index, additional properties pages are created We ll explore the fragmentation page later
Analyzing Queries
TIP Analysis of queries can fill a book by itself For this chapter, and to help you prepare for the tests, we are keeping it simple In the statistics section, we ll present some basics on viewing the execution plan, an integral tool when analyzing queries in depth By looking at queries frequently used on our database, we can identify the best indexes to create In our earlier example of the Parts table, the PK (and the clustered index) is on the PartID column For the following query, the index is perfect:
SELECT Price from Parts WHERE PartID = 101;
As another example, let s say that the company runs a report every Monday from the following query:
Copyright © OnBarcode.com . All rights reserved.