donderdag 2 juni 2011

SQL Server Query best practices

This post is one in a series of posts about SQL Server best practices. I've posted best practices about the following subjects, so far:
This post is about "query" best practices:
  • I always write my SQL statements (SELECT, WHERE, etc) in upper case and fields and tables in according to the way they're created.
  • Write comments when something is not clear. This will aid other developers understanding your code.
  • Always write case consistent code. This will enhance transitions from Case Insensitive (CI) server/database to Case Sensitive (CS) server/database.
  • Don't use columnnumbers in ORDER BY. This will enhance readability.
  • Use ANSI joining (INNER, LEFT OUTER, RIGHT OUTER) in stead of old school joining  (*=, =*,=).
  • Don't use SELECT * in queries. Always use columnnames in SELECT statements. This will improve Disk I/O and performance. Another advantage is that when you execute a SELECT INTO it is more error prone because when a column is added it doesn't effect the insert.
  • Don't use a % at start of a LIKE expression. This result in a index scan and the index is not fully used.
  • Use proper size for variables in queries. This will lower the change of SQL injection and will improve performance
  • Use WHERE whenever possible. This reduces the dataset.
  • Don't use the trick of TOP 100 PERCENT and ORDER BY in a view. This trick is not applicable in 2005 and 2008 (and further). e.g. . CREATE VIEW xxx AS SELECT TOP (100) PERCENT ....
    (...)
    ORDER BY SortCol;
  • Don't use NOLOCK hint. This is very important because it can cause datainconsistency like dirty reads, phantom reads and lost updates.
  • Use as many "derived tables" as you can. These are faster. For example:
    SELECT FROM
    (
    SELECT 
    FROM 
    ) AS A
  • Limit the usage of scalar functions in SELECT statements. Never use a scalar function in a SELECT when a lot of rows are queried. Scalar functions acts as cursor when a lot of rowd are retrieved. Change the function into an inline view.
  • Use as less possible HINTS. HINTS prohibits the SQL engine using the automated optimization. At first it could be a performence improvement but when data grows it could be slower
  • Use as less possible negative operators like <>, NOT IN and NOT EXISTS.
Greetz,
Hennie

Geen opmerkingen:

Een reactie plaatsen