qr code generator c# mvc FiGURe 6-11 The actual execution plan of the SELECT statement without a SARG in C#

Generation QR-Code in C# FiGURe 6-11 The actual execution plan of the SELECT statement without a SARG

Because the query did not use a valid SARG (the column in the WHERE clause is used in an expression), the OrderDateIndex index can be used only for scanning and not for seeking. To be able to produce an index seek, SQL Server must maintain an index of the result of the function call, in this case, MONTH(OrderDate). You can do this by adding a computed column to the table and indexing that column as follows (the query s execution plan is shown in Figure 6-12):

-- Add the column. ALTER TABLE Sales.SalesOrderHeader ADD OrderMonth AS MONTH(OrderDate);

-- Create an index on the computed column. CREATE NONCLUSTERED INDEX OrderMonthIndex ON Sales.SalesOrderHeader (OrderMonth); GO

SET STATISTICS IO ON;

-- Run the query and reference the new column. SELECT COUNT(*) FROM Sales.SalesOrderHeader WHERE OrderMonth = 5;

This time, the query performs a seek operation on the index of the computed column, resulting in only eight page reads. Depending on the complexity of your query and computed column definition, the optimizer automatically uses the index of the computed column without the computed column being referenced in the query. The following query, for example, also generates the execution plan previously shown in Figure 6-12:

SET STATISTICS IO ON;

-- Run the query without referencing the computed column. SELECT COUNT(*) FROM Sales.SalesOrderHeader WHERE MONTH(OrderDate) = 5;

As you can see, SQL Server used the index of the computed column without having a reference to it in the query. This is a great feature because it makes it possible to add computed columns and index them without having to change the queries in applications or stored procedures to use the new index. Besides using indexed computed columns with function calls, you can also use indexed computed columns to provide indexes in different collations. Consider that you have the table Test.Person with the column Name using the Latin1_General_CI_AI collation. Now you want to find all rows starting with the character . In Latin1_General, the dots over the O are just considered accents, but in other languages, such as German and Swedish, is a different character than O. Consider that the table is typically queried by English-speaking customers who expect to get both O and back from a search such as LIKE % and occasionally by Swedish customers who expect to get only back from that same search. Because the

table is typically queried by English-speaking customers, it makes sense to keep the Latin1_General_CI_AI collation, and, when Swedish customers query the table, to use the COLLATE keyword to use the Finnish_Swedish_CI_AI collation explicitly. Review the following script and queries. The execution plans for the two queries in the following script are shown in Figures 6-13 and 6-14:

-- Create and populate the table CREATE TABLE Test.ProductNames ( Name NVARCHAR(50) COLLATE Latin1_General_CI_AI );

INSERT Test.ProductNames (Name) VALUES (' l'); INSERT Test.ProductNames (Name) VALUES ('Olja'); INSERT Test.ProductNames (Name) VALUES ('Beer'); INSERT Test.ProductNames (Name) VALUES ('Oil');

-- Query 1 -- Query for all product names that begin with the letter -- using the default collation. SELECT Name FROM Test.ProductNames WHERE Name LIKE ' %';

Here is the result of Query 1:

Query 2 looks like this:

-- Query 2 -- Query for all product names that begin with the letter -- using the Finnish_Swedish_CI_AI collation. SELECT Name FROM Test.ProductNames WHERE Name LIKE ' %' COLLATE Finnish_Swedish_CI_AI;

Here is the result of Query 2:

Comparing the execution plans of Query 1 (Figure 6-13) and Query 2 (Figure 6-14), you can see that in Query 2, because the comparison needs to use a collation other than that of the column (and therefore, the index), a clustered index scan is used instead of an index seek, as in Query 1. By adding an indexed computed column to this table and specifying the Finnish_Swedish_CI_AS collation for this column (as shown in the next code example), SQL Server can automatically use that index instead. Note that the query itself need not change, and that this is a viable solution only if you are using a relatively low number of collations because these indexes need to be both stored and maintained, like all other indexes. The execution plan for the query in the following script is shown in Figure 6-15:

-- Add a computed column with another collation. ALTER TABLE Test.ProductNames ADD Name_Finnish_Swedish_CI_AI AS Name COLLATE Finnish_Swedish_CI_AI;

-- Create an index on the computed column. CREATE NONCLUSTERED INDEX NameIndex2 ON Test.ProductNames (Name_Finnish_Swedish_CI_AI); GO

-- Query for all product names that begin with the letter -- using the Finnish_Swedish_CI_AI collation without specifying -- the computed column. SELECT Name FROM Test.ProductNames WHERE Name LIKE ' %' COLLATE Finnish_Swedish_CI_AI;

Here is the result of this query: