Following are best practices to be used while coding T-SQL on SQL server:
Do not use SELECT * in your queries. Always write the required column names after the SELECT statement. This technique results in reduced disk I/O and better performance.
Always use table aliases when your SQL statement involves more than one source. If more than one table is involved in a from clause, each column name must be qualified using either the complete table name or an alias. The alias is preferred.
It is more human readable to use aliases instead of writing columns with no table information.
Use the more readable ANSI-Standard Join clauses(FROM M1 INNER JOIN M2 ON M1.keycol = M2.keycol) instead of the old style joins(FROM M1, M2 WHERE M1.keycol = M2.keycol).
For consistency; it simply doesn’t look good when you try mixing the different styles. Another is the fact that if you use the old-style syntax and forget to indicate the WHERE clause with the join predicate, you get an unintentional cross join. With the newer syntax, if you use an inner join and forget the ON clause, you get a parsing error that forces you to fix the code.With ANSI joins, the WHERE clause is used only for filtering data, whereas with older style joins, the WHERE clause handles both the join condition and filtering data. Furthermore ANSI join syntax supports the full outer join.
Do not use column numbers in the ORDER BY clause.
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).
Do not prefix your stored procedure names with “sp_”. The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner.
So you can really save time in locating the stored procedure by avoiding the “sp_” prefix.
Always check for the object before executing any DDL statements dynamically.
Before dropping any TABLE check if table exists using:
IF OBJECT_ID(‘dbo.mytable’) IS NOT NULL
DROP TABLE dbo.mytable
Before dropping any table CONSTRAINT check if it exists using:
IF EXISTS (SELECT * FROM sysobjects where xtype = ‘F’ and name = ‘FK_FactTable_DimTable’)
ALTER TABLE dbo.FactTable DROP CONSTRAINT [FK_FactTable_DimTable]
Before dropping any table INDEX check if it exists using:
IF EXISTS (SELECT name FROM sysindexes WHERE name = ‘ix_My_Index’)
drop index ix_My_Index on dbo.mytable
Use CONCAT versus + for merging or adding or concatenating the text or strings.
If we use + the null or empty records make the results empty or null. If we use CONCAT the the nulls are ignored.