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:
) 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.