T-SQLCoding Conventions, Best Practices, Tips and Programming Guidelines

BY-VISHAL PAWAR

T - SQL

Do not use SELECT *Use parametrized

queriesCreate Clustered and

Non-Clustered IndexesFor enumerated value create look up Table

Think SQL injectionSP-Stores Procedure

Comment well

Singular Name for table Avoid side cursors Test - Large databases Keep clustered index small

Store image paths or URLs

Think usage of functions in WHERE clauses

SP-Faster than CRUD

Consistent Names Use SET NOCOUNT ON Timeouts Awareness Avoid Cursors Use LIKE clause properly Use set-based solutions SP-Easy Maintenance

Don’t use space for names

Un normalized – Not Broken Business Entity

a query execution plan Use Table variable inplace of Temp table

SQL keyword in capital letters

Use Computed columns for columns for function

SP- Easy Security

Prefixes Name – Think before use , Ask Why ?

Normalized and DE normalization

use referential integrity Use UNION ALL inplace of UNION

Join instead of sub or nested queries

Use Index hint SP- Test script

Try to use Identity ID column in all Table

Do not use reserved words

partitioning large fact tables

Use Schema name before SQL objects

Primary Key = Never enter by user

Stop Waiting AroundMinimize tempdb

Contention

All Passwords should be Encrypted

Use number in name only if necessary

partition grain

Keep Transaction small

Fill Factor to 70 percent Locate I/O BottlenecksNo to Shrinking Data

Files

Standardize Naming Conventions

Use Custom Schema Manage statistics

manually SET NOCOUNT ONupdate statistics for

large DatabaseRoot Out Problem

QueriesAutomation

Normalization of data Use Constraints Appropriate Data Type Use TRY-CatchUse WITH RECOMPILE if

required Plan To Reuse SQL Job Monitoring

No wildcard characters in Any Names

create an index on Proper Column

Not to overuse ncharand nvarchar Avoid prefix "sp_" Avoid using cursors Monitor Index Usage Database of Database

Avoid search <> and NOT

use transactionsAvoid NULL in fixed-

length fieldProper design and

planningcache redundant data

where appropriateSeparate Data and Log

FilesSQL Script Formatting

Use Derived tables Parallel processing Use EXISTS instead of IN Small Database documentation

Don’t use triggersUse Separate Staging

DatabasesUse NOLOCK properly

Performance highest priority while design

Deadlock thought process

Avoid Having Clause Use Configuration table batch together multiple concurrent sql queries

avoid numerous round trips to Database

Write less think more on TSQL Scripting

• BI Solution Architect • Blogging @ http://bimentalist.com , http://sqlmentalist.com• Knowledge sharing - 350+ BI Articles ,400 SQL Article , 350+ SQL Script • 4 Complex SQL tool innovator on Codeplex & open source all- https://goo.gl/OI3sB4• Slide share publication - http://www.slideshare.net/VishalPawar_BI• 2 LinkedIn Post - https://www.linkedin.com/today/author/120635304• Free Power BI Course publish on Udemy - https://goo.gl/ArL0qe , 1400 + Student • Leading Global Power BI User Group http://goo.gl/oV7IHz , 1200+ Member , 12 + Leaders • Professional Microsoft BI Trainer Mentalist Network • Founder & BI Consulting Partner of Right Click Info • NMUG Community Founder Leader , Speaker - http://nmug.org/• Microsoft Community Representative for Navi Mumbai region.• SQL PASS Chapter Lead for Mumbai & Navi Mumbai • MCTS: Microsoft® Certified Technology Specialist -SQL Server 2008 R2, BI • MCSA: Microsoft® Certified Solutions Associate• CSM: Certified Scrum Master from Scrum Alliance, US• SQL Server Expert in Architecting and Optimization • BE - Computer Science & Engineering , RAIT , Mumbai• MBA-Information System, Arianth College , Pune

About Me