Sunday, November 28, 2010

Basic SQL Code Review

PROBLEM:
You want to make SQL code review to yourself or to one of your team member

SOLUTION:
Use the following list for a basic SQL code review. Remember - recommendations are good for 90% of cases. Use "Execution plan" to validate your queries

  1. Always use indexes, even for small tables
  2. Try to avoid "OR"s - may cause full table scans (UNION can replace OR)
  3. Use UNION ALL instead of UNION if possible (does not sort)
  4. Do not use SQL functions around indexed columns (will suppress the use of that index)
  5. Try to avoid sorts (or use the same order by as the index)
  6. Try to provide the WHERE clause with as many host variables that match the index
  7. Avoid JOIN's with many tables
  8. Use an OUTER JOIN instead of the NOT IN clause
  9. Using col1 > 0 is better that col1 != 0
  10. Use col1 = 40 not col1+10 = 50
  11. Use EXISTS instead of DISTINCT and IN subquery
  12. Use NOT EXISTS instead of NOT IN
  13. Use UNION instead of OR
  14. When 2 indexes exists on the same table and both rank the same (by the optimizer), the first index on the WHERE clause will be used
  15. Try to use "index only" queries if possible. Consider adding columns to existing indexes
  16. OR's are processed from left to right
  17. AND's are processed from right to left
  18. Put expensive OR's at the end part (right)
  19. Put expensive AND's at beginning part (left)
  20. Make proper use of correlated sub-queries
  21. Break up complicated SQL statements. Do not attempt to solve all situations with single statement
  22. Beware of NULLS! - IS NULL and IS NOT NULL will not use index
  23. Use >= 0 to replace NULL expression 
If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Tuesday, November 23, 2010

Disable all constraints, clean all database

PROBLEM:
You need to disable all constraints, clean all database

SOLUTION:

-- Disable all constraints
EXEC sp_MSForEachTable @command1="print 'NOCHECK CONSTRAINT ?'", @command2="ALTER TABLE ? NOCHECK CONSTRAINT all"

When dealing with deleting data from tables which have foreign key relationships - which is basically the case with any properly designed database - we can disable all the constraints, delete all the data and then re-enable constraints

-- Delete data in all tables
EXEC sp_MSForEachTable @command1="print 'DELETE FROM ?'", @command2="DELETE FROM ?"

-- Enable all constraints
EXEC sp_MSForEachTable @command1="print 'CHECK CONSTRAINT ?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

-- If some of the tables have identity columns we may want to reseed them
EXEC sp_MSForEachTable @command1="print 'CHECKIDENT RESEED ?'", @command2="DBCC CHECKIDENT ( '?', RESEED, 0)"

Another option is to run the following code and save the results for both drop and recreate, then execute all the drops followed by your code and then recreate the constraints using the results from the second query

SELECT 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+' DROP CONSTRAINT ' + '[' + f.name + ']'
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

SELECT 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']' +' ADD CONSTRAINT ' + '[' + f.name +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'+'REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id, fc.referenced_column_id)+')' as Scripts
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

To handle table constraints:

-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL

To handle single constraint:

-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint


If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!