Introduction

Databases are the backbone of every type of application and it is, to a great degree, basic to give cautious thought to database programming. I've seen numerous occasions where database projects are disregarded, suspecting that it's something straightforward that can be done by anyone. Indeed, that is wrong.

I have seen a large number of the engineers with a lot of knowledge in programming dialect and SQL Servers. However, I think they are not holding a candle to the current situation's best practices. That is the reason I have chosen to compose this article on "Best Practices, Coding Conventions, and Programming Guidelines in SQL Server ".
Coding norms and rules are critical for any engineer on the way to a fruitful profession. A coding standard is an arrangement of rules, tenants, and directions on the most proficient method to compose the code. Coding benchmarks ought to be sufficiently adaptable or ought to deal with the circumstance where they ought not forestall best practices for coding. These are the rules fundamentally that need to be looked after for better understanding.
Now, I have decided to share some important points to use "Best Practices,Coding Conventions and Programming Guidelines in SQL Server." We can see them below.
Avoid multiple Joins in a single SQL Query

Always try to abstain from composing a SQL query using multiple joins that incorporates outer joins, cross apply, outer joins apply, and other complex sub inquiries. It lessens the decisions for Optimizer to choose the join request and join sort. At some point, Optimizer is compelled to utilize the settled nested joins, independent of the execution outcomes for questions with unnecessarily complex cross apply or sub query. For e.g.
  1. SELECT
  2. T1.p_id,
  3. T1.k_id,
  4. MIN(case when T2.T2_id = 1 then T2.pos else null end) AS xyz,
  5. MIN(case when T2.T2_id = 2 then T2.pos else null end) AS xyz1
  6. FROM
  7. Table T1
  8. INNER JOIN Table T2 ON t2.p_id = T1.p_id
  9. AND T2.k_id = T1.k_id
  10. AND T2.t2_id =any ( 1,2)
  11. WHERE
  12. T1.p_id = 1
  13. AND T1.c_id = 1
  14. GROUP BY
  15. 1,
  16. 2
  17. LIMIT 1
As per the above query, you can write according to your requirement but it's not a good practice. We need to avoid multiple joins in a single query due to performance issues. Instead, create relational database and create View.
Try to avoid use of Non-correlated Scalar Sub Query

You can re-compose your query to evacuate a non-connected scalar sub query as a different query, rather than part of the principle query and store the yield in a variable, which can be alluded to, in the main query or later part of the batch. This will give better alternatives to the Optimizer, which may return exact cardinality that evaluates alongside a superior arrangement. e.g
  1. SELECT Name FROM Table WHERE Id = (SELECT City FROM table2 WHERE state='UP')
I think it's not the best way to write a SQL query. Instead, write it as the following-
  1. SELECT T1.name FROM table1 T1 WHERE T1.Id = (SELECT T2.City FROM Table T2 WHERE T2.city=T1.Id)
If you want to know about Non-correlated query and correlated query, click here.
Avoid using TEXT or NTEXT datatypes in SQL for storing the large textual data

Make an effort not to use TEXT or NTEXT datatypes for putting away expansive literary information. The TEXT datatype has some innate issues connected with it. For instance, you can't straightforwardly create or upgrade the content information using the INSERT or UPDATE statement. Rather, you need to use exceptional articulations like READTEXT, WRITETEXT, and UPDATETEXT.
There is, additionally, a considerable measure of bugs connected with reproducing tables containing content segments. In this way, on the off chance, you don't need to store more than 8 KB of content, using CHAR(8000) or VARCHAR(8000) datatypes.
Order By

Never use any column numbers in the ORDER BY clause in SQL statement. As per the given example below, the 2nd query is faster than the 1st query. e.g
  1. ------------Incorrect way------------
  2. SELECT Id, Name
  3. FROM Table1
  4. ORDER BY 3 -- never used number.
  5. ------------Correct way---------------
  6. SELECT id, Name
  7. FROM Table1
  8. ORDER BY Name
Creation of Indexes and use

We know that Index can drastically decrease the information recovery time yet reversely affect DML operations, which may corrupt query execution. With this reality, Indexing is a testing errand, yet could enhance the SQL query execution and give you best query reaction time.

Syntax
  1. CREATE NONCLUSTERED xyz INDEX <IndexName>
  2. ON <tbl_name>
  3. (
  4. Colmn1,
  5. Colmn2,
  6. .......
  7. ........
  8. )
As per the above query, we can put n number of columns.
Declare variable

In spite of the fact that T-SQL has no understanding of constants (like the ones in the C language), variable can fill a similar need. Using variables rather than steady values inside your query enhances comprehensibility and viability of your code. Now, we can see the example below.
  1. SELECT id, Name
  2. FROM table
  3. WHERE Status IN (5,6)
As per the above statement, the same query can be written with variables.
  1. DECLARE @Id, @status
  2. SELECT @Id = 5, @Status = 1
  4. SELECT id, name
  5. FROM Table
  6. WHERE Status IN (@status, @id)
@Debug parameter

Continuously add a @Debug parameter to your stored procedure. This can be of BIT information sort. At the point when a 1 is passed for this parameter, print all the moderate results, variable substance using SELECT or PRINT articulations and when 0 is passed, don't print anything. This aides in fast investigation of stored procedure systems, as you don't need to include and expel these PRINT/SELECT articulations prior and then after investigating issues.
Constraint in SQL server

I have seen many times in different projects that developers already create constraints in SQL table without any name. I think it will throw problems when you alter or remove the data. So, we need to create constraints with proper names. Refer the given image.
As per the image, we can see that all constraint names start form constraint key i.e pk,fk,uk etc.....So, you just need to write a query with constraint name.
  1. CREATE TABLE Table1
  2. (
  3. Id int NOT NULL,
  4. CONSTRAINT Name UNIQUE(id)
  5. )
Stored procedure

Ensure your stored procedure returns an answer demonstrating their status. Institutionalize on the arrival estimations of stored procedure for success and failure. The RETURN statement is implied for returning the execution status, not information.
Output parameter

If any stored procedure returns a single row data, consider returning the data using OUTPUT parameters in the place of a SELECT query and Output parameter is faster than the data returned by SELECT query.