Database Reference
In-Depth Information
which changes the I/O, execution time, and execution plan results yet again (Figure
8-13
), you end up with these
results:
Table 'Employee'. Scan count 1, logical reads 414
CPU time = 0 ms, elapsed time = 90 ms.
Figure 8-13.
Forcing a
Seek
operation using
FORCESEEK
query hint
Limiting the options of the optimizer and forcing behaviors can in some situations help, but frequently, as shown
with the results here, an increase in execution time and the number of reads is not helpful.
Before moving on, be sure to drop the test index from the table.
DROP INDEX HumanResources.Employee.IX_Employee_Test;
Examine the Column Data Type
The data type of an index matters. For example, an index search on integer keys is fast because of the small size and
easy arithmetic manipulation of the
INTEGER
(or
INT
) data type. You can also use other variations of integer data types
(
BIGINT
,
SMALLINT
, and
TINYINT
) for index columns, whereas string data types (
CHAR
,
VARCHAR
,
NCHAR
, and
NVARCHAR
)
require a string match operation, which is usually costlier than an integer match operation.
Suppose you want to create an index on one column and you have two candidate columns—one with an
INTEGER
data type and the other with a
CHAR(4)
data type. Even though the size of both data types is 4 bytes in SQL Server 2014,
you should still prefer the
INTEGER
data type index. Look at arithmetic operations as an example. The value
1
in the
CHAR(4)
data type is actually stored as
1
followed by three spaces, a combination of the following four bytes:
0x35
,
0x20
,
0x20
, and
0x20
. The CPU doesn't understand how to perform arithmetic operations on this data, and therefore
it converts to an
integer
data type before the arithmetic operations, whereas the value
1
in an
integer
data type is
saved as
0x00000001
. The CPU can easily perform arithmetic operations on this data.
Of course, most of the time, you won't have the simple choice between identically sized data types, allowing you
to choose the more optimal type. Keep this information in mind when designing and building your indexes.
Consider Column Order
An index key is sorted on the first column of the index and then subsorted on the next column within each value of
the previous column. The first column in a compound index is frequently referred to as the
leading edge
of the index.
For example, consider Table
8-2
.
