- The query
should be written in upper case however, the constant / hard coded values
can be in lower case.
- Single space
should be used between the query clauses and after comma. It should also
be between column / table names in select, from, group by or order by
clauses.
- To make SQL
Statements more readable, start each clause on a new line and indent when
needed. Following is an example:
SELECT TITLE_ID, TITLE
FROM TITLES
WHERE TITLE = 'Computer’ - Sub queries
must be also be indented such that they can be identified from the main
query.
- Use column
names instead of asterisk (*) , even if all the columns are required.
- Query must use
alias names (instead of using table name or not using table name) for
referencing column name however; single table query can be an exception.
- Avoid using
DISTINCT keyword as much as possible.
- Always convert
the constant to match the table column.
- Do not use
trunc(), to_char(), upper(), lower() etc. functions on the table columns
in the where clause.
- Avoid using
UNION at all however, if required, use as minimum (sets of UNION) as
possible in a single query and use instead UNION ALL (UNION ALL can only
be used where resultant queries will return disjoint sets - most of the
time this is the case).
- Use minimum
table joins and always ensure that there exist n-1 joins in a query in
addition to other filter criteria.
- Avoid using ORDER
BY / GROUP BY clause until warranted for.
- Like operator
match should only be used when required and wild card should also be used
with caution and limited as per requirement (avoid using wild card on both
sides of the value).
- Do not forget
to use separator brackets especially when using OR operator however,
unnecessary use of brackets should be avoided.
- Use
conjunctive form instead of disjunctive form - use AND and IN instead of
OR or NOT IN.
- Try your level
best to write query which is in compliance with ANSI-92 SQL standards.
- Write comments
in your stored procedures, triggers and SQL batches generously, whenever
something is not very obvious. This helps other programmers understand
your code clearly. Don't worry about the length of the comments, as it
won't impact the performance, unlike interpreted languages like ASP 2.0.
- Try to avoid server side
cursors as much as possible. Always stick to a 'set-based approach'
instead of a 'procedural approach' for accessing and manipulating data.
Cursors can often be avoided by using SELECT statements instead.
- Every table
must have a primary or unique key.
- If you have a
choice, do not store binary or image files (Binary Large Objects or BLOBs)
inside the database. Instead, store the path to the binary or image file
in the database and use that as a pointer to the actual binary file stored
elsewhere on a server. Retrieving and manipulating these large binary
files is better performed outside the database, and after all, a database
is not meant for storing files.
- Always use a
column list in your INSERT statements. This helps in avoiding problems
when the table structure changes (like adding or dropping a column).
Here's an example which shows the problem.
- Perform all
your referential integrity checks and data validations using constraints
(foreign key and check constraints) instead of triggers, as they are
faster. Limit the use triggers only for auditing, custom tasks and
validations that can not be performed using constraints. Constraints save
you time as well, as you don't have to write code for these validations,
allowing the RDBMS to do all the work for you.
- Always access
tables in the same order, this helps in avoiding deadlocks. Other things
to keep in mind to avoid deadlocks are: Keep your transactions as short as
possible. Touch as few data as possible during a transaction. Never, ever
wait for user input in the middle of a transaction. Do not use higher
level locking hints or restrictive isolation levels unless they are
absolutely needed. Make your front-end applications deadlock-intelligent,
that is, these applications should be able to resubmit the transaction
incase the previous transaction fails. In your applications, process all
the results returned by database server immediately so that the locks on
the processed rows are released, hence no blocking.
- Offload tasks,
like string manipulations, concatenations, row numbering, case
conversions, type conversions etc., to the front-end applications if these
operations are going to consume more CPU cycles on the database server.
Also try to do basic validations in the front-end itself during data
entry. This saves unnecessary network roundtrips.
- SQL Server: Always
check the global variable @@ERROR immediately after executing a data
manipulation statement (like INSERT/UPDATE/DELETE), so that you can
rollback the transaction in case of an error (@@ERROR will be greater than
0 in case of an error). This is important, because, by default, SQL Server
will not rollback all the previous changes within a transaction if a
particular statement fails. This behavior can be changed by executing SET
XACT_ABORT ON. The @@ROWCOUNT variable also plays an important role in
determining how many rows were affected by a previous data manipulation
(also, retrieval) statement, and based on that you could choose to commit
or rollback a particular transaction.
- Using
variables instead of constant values within your queries improves
readability and maintainability of your code. Consider the following
example:
SELECT OrderID, OrderDate
FROM Orders
WHERE OrderStatus IN (5,6)
The same query can be re-written in a mode readable form as shown below:
DECLARE @ORDER_DELIVERED, @ORDER_PENDING
SELECT @ORDER_DELIVERED = 5, @ORDER_PENDING = 6
SELECT OrderID, OrderDate
FROM Orders
WHERE OrderStatus IN (@ORDER_DELIVERED, @ORDER_PENDING)
Note:
These tips apply for all queries used in Trigger, Procedures, Views etc.
