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:

• General best practices
• Naming conventions
• Tables
• Columns
• Stored procedures.
• Linked servers.

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