When it comes to optimizing query performance in SQL Server, there are several concepts and techniques that can be employed. One such concept is the difference between Seek Predicate and Predicate.

Seek Predicate refers to the operation that describes the b-tree portion of the Seek, while Predicate refers to the operation that describes the additional filter using non-key columns. In simple terms, Seek Predicate is better than Predicate because it searches indexes, whereas Predicate searches the data in page files itself.

Let’s consider an example to understand this concept better. Suppose we have a SELECT statement that retrieves data from the “HumanResources.Employee” table based on the “NationalIDNumber” column:

SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807

Upon analyzing the execution plan for this query, we observe that it includes a key lookup operation. This is because the columns we are trying to retrieve in the SELECT statement are also present in the WHERE clause.

To improve the performance of this query, we can create a covering index on the “HumanResources.Employee” table:

CREATE NONCLUSTERED INDEX [IX_HumanResources_Employee_Example] ON HumanResources.Employee (NationalIDNumber ASC, HireDate, MaritalStatus) ON [PRIMARY]

After creating the covering index, we rerun the SELECT statement and notice that the key lookup operation is removed. However, an index scan operation is still present, which is not ideal for performance.

Upon further investigation, we discover that the datatype of the “NationalIDNumber” column is “nvarchar(15)”. In our original SELECT statement, we were comparing the “NationalIDNumber” column (nvarchar) to an integer value. This forced a predicate operation, which resulted in an index scan instead of an index seek.

To resolve this issue, we modify the WHERE clause to pass the “NationalIDNumber” value as a string:

SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = '14417807'

By making this change, the index scan is converted to an index seek, resulting in improved performance.

It’s important to note that when working with datatypes, it’s crucial to use them wisely. In this case, using the appropriate datatype in the WHERE clause allowed the query optimizer to make the most efficient use of the index.

In summary, understanding concepts like Seek Predicate and Predicate, as well as utilizing covering indexes and choosing the right datatypes, can greatly improve query performance in SQL Server.