Optimizable SARGs

SQL Server’s Query Optimizer examines the conditions within the query’s where clause to determine which indexes are actually useful. If SQL Server can optimize the where condition using an index, the condition is referred to as a search argument or SARG for short. However, not every condition is a “sargable” search argument:

✦ Multiple conditions that are ANDed together are SARGs, but ORed conditions are
not SARGs.
✦ Negative search conditions (<>, !>, !<, Not Exists, Not In, Not Like) are not
optimizable.
It’s easy to prove that a row exists, but to prove it doesn’t exist
requires examining every row.
✦ Conditions that begin with wildcards don’t use indexes. An index can quickly
locate Smith, but must scan every row to find any rows with ith anywhere in the
string.
✦ Conditions with expressions are not SQL Server compliant, so these expressions
will be broken down with the use of algebra to aide with the procurement of
valid input data.
✦ If the where clause includes a function, such as a string function, a table
scan is required so every row can be tested with the function applied to the
data.

Bookmark Lookup Logical Operation removed from SQL Server 2005

The Bookmark Lookup logical operation was removed in SQL Server 2005 and replaced with clustered index seek and RID lookup. Previous versions of SQL Server displayed the Bookmark Lookup icon but actually performed the clustered index seek and RID lookup.

Interpreting the Query Execution Plan

A common misunderstanding is that seeks are always good and scans are always bad. This is not completely true. Because SQL Server is optimized to retrieve data sequentially, scans are excellent for gathering large blocks of data, but poor at retrieving a few isolated rows. Index seeks are great at retrieving a few rows here and a few rows there, but retrieving a large percentageof a table using index seeks performs extremely poorly compared to a scan.

Query Execution Plan Operators

Clustered index scan In a clustered index scan, SQL Server sequentially
reads the entire clustered index or a range within
the clustered index. SQL Server chooses this operation when
the set of rows requested by the where clause can be
determined as a range within the order of the clustered
index, or a large percentage

Clustered index seek In a clustered index seek, SQL Server rapidly
navigates the clustered index b-tree to retrieve
specific rows. The benefit of the clustered index seek is
that when the row(s) are determined all the columns are
immediately available.

Filter In some situations, SQL Server retrieves all the data
from a table and then uses filter operations to select
the correct rows. Sometimes the Query Optimizer will use a
Filter for performance reasons, but it’s more often due to
the lack of a useful index.


Hash match A hash match is an unordered join method that
builds a temp table and iteratively matches with
data from another table. A hash match is more
efficient if one table is significantly larger than the
other table.
This is the worst-case join method and is used when
no suitable index is available.

Merge join The merge join is the fastest method of comparing
two tables if both tables are pre-sorted.

Nested loop A nested loop iterates through two tables and
identifies matches.
Typically, nested-loop joins are best when a large
index is joined with a small table.


Nonclustered index scan In a nonclustered index scan, SQL Server reads
through all or part of the index sequentially looking
for the data.
A nonclustered index seek navigates the b-tree index
from the root node, through the intermediate node,
to the leaf node, and finally to the row. For selecting
a few individual rows this can be a very fast
operation. The benefit of a nonclustered index seek
is that it tends to be narrow (have few columns), so
more rows can fit on a page.
Once the correct row is identified, if all the required
columns are found in the index, then the seek is
complete because the index covers the needs of
the query. Conversely, if additional columns are
needed, SQL Server will need to fetch them from
the data pages.

Sort In some situations SQL Server retrieves all the data
from a table and then sorts the data to prepare it for
operations to perform the order by. Filters and
sorts are slow operations and indicate a lack of
useful indexes.

RID lookup The RID lookup locates rows in the data pages.
Typically, a RID lookup works with a nested loop to
locate the data pages following a clustered index
seek or scan.

Spool In a spool operation, SQL Server has to save off a
temporary set of data.

Table scan In a table scan, SQL Server sequentially
reads the entire table to select the rows that meet the criteria.
Depending on the number of rows needed, this
might be the fastest method of retrieving the data.
For instance, if SQL Server needs to select 80% of
the table, then it’s far faster to read the entire table
sequentially than to seek each row individually.
However, if fewer rows are needed and the Query
Optimizer still chooses a table scan, it may be
because there’s no suitable index to use instead of
the table scan, the index statistics are wrong, or the
table is so small that it doesn’t matter.

Next version of BPA forSQL Server 2005

The new version of the Best Practices Analyzer from MSDN Downloads.

SQL Server 2005 - T-SQL Enhancements

SQL Server 2005 provides many enhancements to the T-SQL language that allow you to improve the performance of your code and extend your error-management capabilities. These enhancements include improved error handling, new recursive query capabilities, and support for new SQL Server database engine capabilities. Some of the T-SQL enhancements are as follows:

. Ranking functions—SQL Server 2005 introduces four new ranking functions:
ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These new functions allow you to efficiently analyze data and provide ranking values to result rows of a query.

. Common table expressions—A common table expression (CTE) is a temporary named result set that can be referred to within a query, similarly to a temporary table. CTEs can be thought of as an improved version of derived tables that more closely resemble a non-persistent type of view. You can also use CTEs to develop recursive queries that you can use to expand a hierarchy.

. PIVOT/UNPIVOT operator—The PIVOT operator allows you to generate crosstab reports for open-schema and other scenarios in which you rotate rows into columns, possibly calculating aggregations along the way and presenting the data in a useful form. The UNPIVOT operator allows you to normalize pre-pivoted data.

. APPLY—The APPLY relational operator allows you to invoke a specified table-valued function once per each row of an outer table expression.

. TOP enhancements—In SQL Server 2005, the TOP operator has been enhanced, and it now allows you to specify a numeric expression to return the number or percentage of rows to be affected by your query; you can optionally use variables and subqueries. You can also now use the TOP option in DELETE, UPDATE, and INSERT queries.

. DML with results—SQL Server 2005 introduces a new OUTPUT clause that allows you to return data from a modification statement (INSERT, UPDATE, or DELETE) to the processing application or into a table or table variable.

. Exception handling for transactions—Earlier versions of SQL Server required you to include error-handling code after every statement that you suspected could potentially generate an error. SQL Server 2005 addresses this by introducing a simple but powerful exception-handling mechanism in the form of a TRY...CATCH T-SQL construct.

SQL Server 2005 - Database Engine Enhancements

Several new database-specific enhancements have been added to SQL Server 2005. These changes are focused primarily on the database storage engine. The following are some of the most important enhancements:

. Instant file initialization—New or expanded database files are made available much faster now because the initialization of the file with binary zeros is deferred until data is written to the files.

. Partial availability—In the event of database file corruption, the database can still be brought online if the primary filegroup is available.

. Database file movement—You can now use the ALTER DATABASE command to move a database file. The physical file must be moved manually. This feature was available in SQL Server 2000, but it only worked on tempdb.In addition, many new table-oriented enhancements are available with SQL Server 2005. This includes features that define how the data in the tables will be stored in the database.

The following are two of the key enhancements:
. Large rows—SQL Server 2005 now allows for the storage of rows that are greater than 8060 bytes. The 8060-byte limitation that existed with SQL Server 2000 has been relaxed by allowing the storage of certain data types (such as varchar and nvarchar) on a row overflow data page.

. Stored computed columns—Computed columns that were calculated on-the-fly inprior versions can now be stored in the table structure. You accomplish this by specifying the PERSISTED keyword as part of the computed column definition.

New Features of SQL Server 2005

These are the few new fetures of sql server 2005. There are many but, we can consider these are the key features

. SQL Server Management Studio (SSMS)
. SQL Server Configuration Manager
. Common language runtime (CLR)/.NET Framework integration
. Dynamic management views (DMVs)
. System catalog views
. SQL Server Management Objects (SMO)
. Dedicated administrator connection (DAC)
. SQLCMD
. Database Mail
. Online index and restore operations
. Native encryption
. Database mirroring
. Database snapshots
. Service Broker
. SQL Server Integration Services
. Table and index partitioning
. Snapshot isolation
. Business Intelligence (BI) Development
. Query Notification
. Multiple active result sets
. New SQL Server data types
. SQL Server Management Studio (SSMS)
. SQL Server Configuration Manager
. Common language runtime (CLR)/.NET Framework integration
. Dynamic management views (DMVs)
. System catalog views
. SQL Server Management Objects (SMO)
. Dedicated administrator connection (DAC)
. SQLCMD
. Database Mail
. Online index and restore operations
. Native encryption
. Database mirroring
. Database snapshots
. Service Broker
. SQL Server Integration Services
. Table and index partitioning
. Snapshot isolation
. Business Intelligence (BI) Development
. Query Notification
. Multiple active result sets
. New SQL Server data types