code 128 generator c# Finding a specific Customer row by First_Name, Last_Name in Visual C#.NET

Generating Code128 in Visual C#.NET Finding a specific Customer row by First_Name, Last_Name

Listing 4 Finding a specific Customer row by First_Name, Last_Name
Code 128 Code Set A Creation In C#
Using Barcode printer for .NET framework Control to generate, create ANSI/AIM Code 128 image in Visual Studio .NET applications.
www.OnBarcode.com
Decode Code-128 In Visual C#
Using Barcode decoder for .NET Control to read, scan read, scan image in Visual Studio .NET applications.
www.OnBarcode.com
SELECT * FROM Customers WHERE First_Name = 'Jake' AND Last_Name = 'Smith';
Data Matrix ECC200 Drawer In Visual C#.NET
Using Barcode creator for .NET framework Control to generate, create Data Matrix image in VS .NET applications.
www.OnBarcode.com
Matrix Generator In Visual C#
Using Barcode creator for VS .NET Control to generate, create 2D Barcode image in VS .NET applications.
www.OnBarcode.com
Issuing the query produces the query execution plan found in the figure 2 query execution plan for listing 4. The plan is identical to the prior example in which the WHERE clause listed the columns in order.
Draw Code 3/9 In C#.NET
Using Barcode printer for .NET Control to generate, create Code 39 Full ASCII image in VS .NET applications.
www.OnBarcode.com
UCC - 12 Drawer In C#.NET
Using Barcode encoder for Visual Studio .NET Control to generate, create UPC Symbol image in .NET applications.
www.OnBarcode.com
Finding a last name
Printing PDF-417 2d Barcode In Visual C#.NET
Using Barcode generation for .NET framework Control to generate, create PDF417 image in .NET applications.
www.OnBarcode.com
Standard 2 Of 5 Drawer In Visual C#
Using Barcode printer for VS .NET Control to generate, create 2 of 5 Standard image in .NET applications.
www.OnBarcode.com
Query execution plan for listing 4
Making Code 128B In Visual Studio .NET
Using Barcode generation for Reporting Service Control to generate, create ANSI/AIM Code 128 image in Reporting Service applications.
www.OnBarcode.com
Encoding Code 128 In Objective-C
Using Barcode maker for iPhone Control to generate, create Code 128 image in iPhone applications.
www.OnBarcode.com
Finding a last name
Decoding QR-Code In Visual Basic .NET
Using Barcode scanner for .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Paint Barcode In Objective-C
Using Barcode encoder for iPhone Control to generate, create Barcode image in iPhone applications.
www.OnBarcode.com
In the prior example, we supplied values in the WHERE clause for both columns defined in the ix_Customer_Name index. The index is based on Last_Name, First_Name, and our query limited the results by providing a value for the Last_Name column and for the First_Name column. Although this makes intuitive sense, a composite index s usefulness is not limited to only those instances where this is true. SQL Server can use a composite index when only some of the index s columns are provided. For example, consider the query depicted in listing 5.
Draw QR Code In .NET Framework
Using Barcode printer for Reporting Service Control to generate, create Quick Response Code image in Reporting Service applications.
www.OnBarcode.com
Draw Code 128B In Java
Using Barcode generator for Android Control to generate, create USS Code 128 image in Android applications.
www.OnBarcode.com
Listing 5 Finding customers by Last_Name
Recognize Barcode In Java
Using Barcode Control SDK for Java Control to generate, create, read, scan barcode image in Java applications.
www.OnBarcode.com
PDF 417 Creation In None
Using Barcode creation for Online Control to generate, create PDF-417 2d barcode image in Online applications.
www.OnBarcode.com
SELECT Last_Name ,First_Name ,Email_Address FROM Customers WHERE Last_Name = 'Smith';
Data Matrix Printer In Objective-C
Using Barcode printer for iPhone Control to generate, create DataMatrix image in iPhone applications.
www.OnBarcode.com
Creating Barcode In .NET Framework
Using Barcode drawer for Reporting Service Control to generate, create Barcode image in Reporting Service applications.
www.OnBarcode.com
This query is similar to our prior example; however, notice that the WHERE clause now specifies only a Last_Name of Smith. We want to find all Smiths in our Customers table. Looking at the query execution plan in figure 3 for the query specified in listing 5, we see that SQL Server did indeed use the ix_Customer_Name composite index. It
GTIN - 13 Decoder In .NET Framework
Using Barcode reader for .NET framework Control to read, scan read, scan image in Visual Studio .NET applications.
www.OnBarcode.com
GS1 DataBar Limited Maker In VS .NET
Using Barcode creation for .NET framework Control to generate, create GS1 DataBar Limited image in Visual Studio .NET applications.
www.OnBarcode.com
Does the order of columns in an index matter
Query execution plan for listing 5
performed an Index Seek operation to find the rows that satisfied the query. Then a Key Lookup operation was used to retrieve the non-indexed column information for each row that satisfied the query. Returning to our telephone book analogy, we can see why this index was deemed efficient by the Query Optimizer. To find all of the Smiths in the telephone book, we d navigate to the page that contains the first Smith and keep moving forward until we found the entry after Smith. SQL Server resolved the query in a similar manner using the ix_Customer_Name index.
Finding a first name
The prior demonstration proved that SQL Server can use a composite index even though only some of the columns of the index are specified in the WHERE clause. But does it matter which columns are specified To find out, let s consider another example. In this example, we ll change what we are specifying in our search criteria. We ll no longer look for all Customers with a last name of Smith. Instead, we ll search for all Customers with a first name of Jake. Listing 6 shows our new query.
Listing 6 Finding Customers with a first name of Jake
SELECT Last_Name ,First_Name ,Email_Address FROM
Finding a first name
Customers WHERE First_Name = 'Jake';
When we run this query in SQL Server Management Studio and examine the query execution plan shown in figure 4, we can see that SQL Server no longer is able to use an Index Seek operation to specifically locate the rows that satisfy the query. Instead it must resort to an Index Scan operation. Why is that Let s consider our telephone book analogy again. How useful would the telephone book be if you need to find everyone with a first name of Jake Not very. You d have to start on the first page of the book and look through every entry to see if the person s first name is Jake. Why Because the telephone book is not organized by first name; it s organized by last name, first name. The same holds true for SQL Server. An index cannot be used to seek rows of data when the first column of the index is not specified in the WHERE clause. That s not to say that the index is completely worthless for queries such as the one defined in listing 6. One the contrary, it can still improve performance significantly. Looking again at figure 4, we see that a nonclustered index scan on ix_Customer_ Name was used to resolve the query. In an index scan SQL Server examines every entry in the index to see if it matches the specified criteria. This may sound like an expensive operation, but it s much better than the alternative, a clustered index scan, also known as a table scan. To see that this is the case, let s look again at the properties of the nonclustered index scan from our last example. Let s run the query again. This time we ll turn on STATISTICS IO so that we can measure the logical and physical reads required to satisfy the query. Listing 7 shows how to turn the setting on.
Copyright © OnBarcode.com . All rights reserved.