Tables and Indexes: Internal Structure and Access Methods - Pro SQL Server Internals - page 43

■ always clearly specify parameter data types in client applications. For example, in .net, use Parameters.Add
("@ParamName",SqlDbType.Varchar, <Size>).Value = stringVariable instead of Parameters.Add
("@ParamName").Value = stringVariable overload.

Tip

It is also worth mentioning that varchar parameters are SARGable for nvarchar unicode data columns.

Composite Indexes
Indexes with multiple key columns are called composite indexes . The data in the composite indexes is sorted on a per-
column basis from leftmost to rightmost columns. Figure 2-16 shows the structure of a composite index.

Figure 2-16. Composite index structure

The SARGability of a composite index depends on the SARGability of the predicates on the leftmost index
columns. Table 2-2 shows examples of SARGable and non-SARGable predicates using the index from Figure 2-16 as
the example.

Table 2-2. SARGable and non-SARGable predicates on a composite index

SARGable predicates Non-SARGable predicates

LastName = 'Clark' and FirstName = 'Steve' LastName <> 'Clark' and FirstName = 'Steve'

LastName = 'Clark' and FirstName <> 'Steve' LastName LIKE '%ar%' and FirstName = 'Steve'

LastName = 'Clark' FirstName = 'Steve'

LastName LIKE 'Cl%'