Best Practice: TSql Coding conventions

·        Do not use undocumented sql functionality

·         Try not to use system tables directly. System table structures may change in a future release. Wherever possible use the sp_help stored procedures or lNFORMATlON_SCHEMA views. There will be situations where you cannot avoid accessing system tables though.

·         Do not use SELECT * in your queries. Always use column names.

·         Try not to use SELECT * INTO #temp. Create #temp table first.

·         Try to avoid cursors as much as possible.

·         Avoid the creation of temporary tables while processing data, because temp tables mean more disk IO. Use advanced sql, derived tables, table variables or views instead. Keep in mind that, in some cases, using a temporary table performs better than a highly complicated query.

·         Avoid “LIKE ‘%string’ ”, where % is at the beginning.  (Index Seek Vs. Index Scan (Table Scan))

·         Avoid “NOT IN”.

·         Use derived table cautiously. They perform very well, but are hard to read, if not used in a simple form.

·         Use “SET COUNT ON” at the beginning of the stored procedures. It prevents messages like “10 row(s) affected” to be displayed.

·         Avoid dynamic Sql. It is hard to troubleshoot and read. It is runtime functionality, not pre-compiled.

·         Use COALESCE to eliminate NULLs.

·         Always use column list with INSERT statement. Statement without the list will break after change of the column order, adding column, removing column, etc.

·         Avoid triggers. Use only if necessary.

·         If using INSERT, UPDATE or DELETE on tables more than once in stored procedure, consider using transaction and error handling.

·         For scalar stored procedures make sure to return a value indicating the status.

·         Do not use column numbers in “ORDER BY”. It will not work correctly, if somebody will change the order of the columns in the select statement.

·         In object names, do not use prefix to indicate type of an object, like “sp_” for stored procedure.

·         In object names, never use prefixes used by system objects, like “sp_”, “xp_”.

·         Do not use spaces in object names

·         Stored procedure, view, table and function should begin with the upper case. Triggers and constrains should begin with the lower case.

·         Do not use reserved words in sql object names.

·         Tables, generally should have singular name

·         Identity column should be constructed with “table” name and “Id”. [TableId]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s