CSharp

Angular

Javascript

WebAPI

Sql

Contributing

Licensing

T-SQL Development

Edit

IF you write bad SQL Code, be ready to face the performance issues. So SQL Code should be written by dedicated sql programmers. Good experienced sql developers can write very good sql code, which will definitely improve the performance of the database. So write your SQL code with SQL Experts.

Best Practices

  • Don´t use select * from tablename instead write selected column names.Example: Select col1, col2 from tablename.
  • Use comments for each block of code.
  • Don´t use SP_ while naming your Procedure (Learn From here).
  • Use CTE Common table Expression instead of temporary tables. CTE scope is limited to the next statement in the query. Learn From here.
  • Use SET NOCOUNT ON in the procedures. This will really improve the performance.
  • Avoid using cursors in Procedures, rather use case statement, while loop or Merge clause.
  • Don´t use Recompile option in stored procedure.
  • While working with complicated logics use parenthesis to improve the readability.
  • Try to avoid cross joins.
  • Use IF Exists rather than count (*) for data existence in table.
  • Don´t query data from Application side, instead use stored procedures.
  • Don´t use store Images in the database, rather use File streams.
  • Try to avoid dynamic SQL, since it is slower than Static SQL. Learn From here
  • Always use column names in insert statement.
  • Use Union only if required otherwise use Union all.
  • Use Except and Not Exists Instead of Left Join and Not In.
  • Use sp_executesql instead of Exec(@str) for the execution of dynamic sql.
  • Design Indexes effectively and make sure that created indexes are used by the queries. Don’t make too much indexes in a table.  - Use Merge operator for multiple DML operations Insert, Update, Delete. Learn From here.
  • Use policy management to enforce for configuring and managing SQL Server across the organization.
  • Use Try Catch for Error handling.

We recomend to visit this both links to learn more functionality about Querying SQL Server 2012.

Naming Conventions and Format

  • T-SQL Code has a format with indents. You can use below website to format queries. Formater.

Write like this

  SELECT 
      a.Column2, b.Column2 
  FROM 
      dbo.TableName1 a
          JOIN dbo.TableName2 b ON
              a.Column1 = b.Column1
  WHERE
      a.Column3 = 1

Not like this

  SELECT a.Column2, b.Column2 FROM dbo.TableName1 a JOIN dbo.TableName2 b ON a.Column1 = b.Column1 WHERE a.Column3 = 1
  • Avoid using spaces in the sql server objects. Example:

Use

  SELECT * FROM dbo.EmployeeDetails

Don´t use

  SELECT * FROM dbo.[Employee Details]
  • Use usp_ prefix for stored procedures.
  • Use fn_ prefix for functions.
  • Specify the schema name in the table names. Example:   
      SELECT * FROM dbo.EmployeeDetails

Script Comments

This is the most important act of the developer when is writting code, this helps to identify and understand the main activities of the script, you must comment everything that is important:

  • Single line comment: This represents a single line comment using --.

  • Multiple line comment: This represents block of lines of comments using /* . . . */.   There are some template to achieve this:

  • Store Procedures
  • Functions