SQL Server 2014 Transact-SQL Optimization Tips (Part 2)
Try to restrict the queries result set by using the WHERE clause.
This can results in good performance benefits, because SQL Server will return to client only
particular rows, not all rows from the table(s). This can reduce network traffic and boost
the overall performance of the query.
Consider using the CONTAINS MEMORY_OPTIMIZED_DATA option with the ALTER DATABASE
statement.
This option was first introduced in SQL Server 2014 and specifies that the filegroup stores
memory_optimized data in the file system.
Note. Only one MEMORY_OPTIMIZED_DATA filegroup is allowed per database.
Try to avoid using the DISTINCT clause, whenever possible.
The DISTINCT clause should only be used if you know that duplicate returned rows are a possibility.
Because using the DISTINCT clause will result in some performance degradation, you should use this
clause only when it is necessary.
Use table variables instead of temporary tables.
Table variables require less locking and logging resources than temporary tables, so table
variables should be used whenever possible.
Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can result in some performance degradation in comparison with select
statements. Try to use correlated subquery or derived tables, if you need to perform
row-by-row operations.
Consider using the MEMORY_OPTIMIZED option of the CREATE TABLE statement.
This option was first introduced in SQL Server 2014 and indicates whether the table is
memory optimized. Memory-optimized tables are in memory user tables, the schema of which
is persisted on disk similar to other user tables. Memory-optimized tables can be accessed
from natively compiled stored procedures.
Encapsulate your Transact-SQL code inside a transaction when you modify database,
not when perform only select statements.
If you encapsulate your Transact-SQL code inside a transaction when perform the select statements
only, the total queries performance may be decreased.
If you need to update VARCHAR columns, try to replace the contents with contents of the
same length.
By using so, you can increase the probability of in-place update instead of delete/insert or
deferred update. The in-place update is the most effective method of modification, when it is
used the data changed on its physical place. When the row’s size is changed, the delete/insert
modification method can be used. This results in some performance degradation.
Use the ONLINE option of the CREATE INDEX statement to create index online.
When you specify the ONLINE option during the index creation, users can still access the table
data and use other indexes on the table while this index is being created.
Note. Keep in mind that online index creation is available only in the Enterprise, Developer,
and Evaluation editions of SQL Server 2014.
If you need to return the total table’s row count, you can use alternative way
instead of SELECT COUNT(*) statement.
Because SELECT COUNT(*) statement make a full table scan to return the total table’s row count,
it can take very many time for the large table. There is another way to determine the total
row count in a table. You can use sysindexes system table, in this case. There is ROWS column
in the sysindexes table. This column contains the total row count for each table in your database.
So, you can use the following select statement instead of SELECT COUNT(*):
SELECT rows FROM sys.sysindexes WHERE id = OBJECT_ID(‘table_name’) AND indid < 2
So, you can improve the speed of such queries in several times.
Consider using the PIVOT option to create cross-tab queries.
In SQL Server 2000 there was not a simple way to create the cross-tab queries. The PIVOT option
makes the job of creating cross-tab queries much easier then in the past.
Use the INTERSECT operator to return all distinct rows from the left and right side of a query.
You can return the distinct matching rows from two tables by using the JOIN with WHERE clause.
When you use the JOIN with WHERE clause, the result set will be the same, but the execution plan
will be different. The INTERSECT operator provides the better performance and was optimized for
the intersect operation.
Try to restrict the queries result set by returning only the particular columns
from the table, not all table’s columns.
This can results in good performance benefits, because SQL Server will return to client only
particular columns, not all table’s columns. This can reduce network traffic and boost the
overall performance of the query.
Consider specifying the index locking granularity during creating or altering index.
SQL Server 2014 supports the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options in CREATE INDEX
and ALTER INDEX statements. These options can be used to control the level at which locking
occurs for the index.
Use the FAST number_rows table hint if you need to quickly return ‘number_rows’ rows.
You can quickly get the n rows and can work with them, when the query continues execution and
produces its full result set.