Best Practices And Programming Guidelines In SQL Server - Part One

Introduction 

Databases are the complete identity of any type of enterprise application or others, and it is extremely essential to give careful consideration to database programming. I've seen many events where database writing computer programs are neglected, suspecting that it's something simple that can be finished by anybody. Well, that's not right.
 
I have seen many of the developers having good knowledge in programming language and SQL Server but I think they are not applying best practices. That's why I have decided to write this article on "Best Practices and Programming Guidelines in SQL Server".

 

For a superior performing database, you require a genuine DBA and an expert database software engineer, let it be for Microsoft SQL Server, Oracle, Sybase, DB2 or whatever! On the off chance that you don't utilize database pros amidst your improvement cycle, databases frequently wind up turning into the execution bottleneck. I chose to compose this article, keeping in mind the end goal to assemble a portion of the database programming best practices, so that my kindred DBAs and database designers can profit!
 
Some important links for learning more about SQL Server. 
Points to be remembered, while applying the best practices of SQL Server
  1. Don't forget to write proper comments in your stored procedures, triggers, and SQL query, whenever something is not very obvious. It will help the other developers to understand your code easily. Don’t worry about the lines of the comments in your query.

  2. Make sure that you are applying the Normalization rules. Your data must be of  at least the 3rd normal form. At the same time, please do not try to compromise with your query performance. A little bit of de-normalization in your query, helps the system perform faster.


  3. Choose a database naming convention, institutionalize it over your association, and be reliable in tailing it. It makes your code more lucid and justifiable.For more details of naming conventions with example, click here.

  4. Once you write the "SELECT" command in SQL, don't use SELECT * in your queries. Always try to write the only needful column names after the SELECT statement. See the examples below. 
    1. Select * from tblName  --Wrong way    
    2.   
    3. Select firstName,lastName from tblName -- correct way    
    This technique will reduce your hard disk's I/O performance.

  5. You need to avoid to use the server side cursors as much as possible in an SQL query. Continuously adhere to a 'set-based approach' rather than a 'procedural approach' for getting to a controlling information. Cursors can regularly be avoided, by utilizing SELECT statement.

    On the off chance that a cursor is unavoidable, utilize a WHILE loop. I have, by and by, tried and reasoned that a WHILE loop is constantly speedier than a cursor. Yet, for some time, loop to supplant a cursor, you require a section (Primary key or unique key) to recognize every column. I, for one, trust that each table must have a Primary or Unique key.

  6. Keep away from the creation of temp tables while preparing information, however, as much as could be expected, as making a temp table means more circle I/O. Consider utilizing advanced SQL, sees, SQL Server 2000 table variable, or derived tables, rather than temp tables.

  7. Try to use "Derived tables" wherever possible, as they perform better than others. You can use the following query to get the 2nd highest salary from the Emp table in sql.see example.
    1. SELECT MIN(Salary)     
    2. FROM Emp    
    3. WHERE EmpID IN    
    4. (    
    5. SELECT TOP 2 EmpID     
    6. FROM Emp    
    7. ORDER BY Salary Desc    
    8. )    
    As per the above same query, you can write using a derived table (Subquery), as given below, and it will perform twice as fast as the above given SQL query. see the example.
    1. SELECT MIN(Salary)     
    2. FROM     
    3. (    
    4. SELECT TOP 2 Salary     
    5. FROM Emp    
    6. ORDER BY Salary DESC    
    7. AS xyz  
    This is only a case, and your results may contrast in various situations relying upon the database design, indexing, volume of information, and so on. In this way, test all the conceivable ways a SQL query could be written, and run with the most proficient one. 

  8. Utilize the more discernable ANSI-Standard Join provisions rather than the old style Joins. With ANSI joins, the WHERE statement is utilized just to filter the information. Whereas with more seasoned style joins, the WHERE provision handles both the join condition and the separating information. The first of the accompanying two questions demonstrates the old style join, while the second one demonstrates the new ANSI join syntax.
    1. SELECT a.au_id, t1.title     
    2. FROM table1 t1, table2 a, table3 ta    
    3. WHERE     
    4. a.au_id = ta.au_id AND    
    5. ta.title_id = t1.title_id AND     
    6. t1.title LIKE ‘%Computer%’    
    7.     
    8. SELECT a.au_id, t1.title    
    9. FROM table2 a     
    10. INNER JOIN    
    11. table3 ta     
    12. ON     
    13. a.au_id = ta.au_id    
    14. INNER JOIN    
    15. table1 t1    
    16. ON    
    17. ta.title_id = t1.title_id    
    18. WHERE t1.title LIKE ‘%xyz%’    
  9. Utilizing SET NOCOUNT ON towards the start of your SQL clusters, puts away systems, and triggers underway situations, as this stifles messages, like '(1 row(s) influenced)' in the wake of executing INSERT, UPDATE, DELETE and SELECT statement. This enhances the execution of put away techniques by lessening the system movement.
    1. USE DB_name;      
    2. GO      
    3. SET NOCOUNT OFF;      
    4. GO      
    5. --just you can show  count message.      
    6. SELECT TOP(5)Name      
    7. FROM xyz.xyz    
    8. WHERE NameLIKE 'A%';      
    9. GO      
    10. -- now you can  SET NOCOUNT to ON to no longer show the any message.      
    11. SET NOCOUNT ON;      
    12. GO      
    13. SELECT TOP(5) Name    
    14. FROM  xyz.xyz    
    15. WHERE name LIKE 'x%';      
    16. GO      
    17. -- now you can reset SET NOCOUNT to OFF       
    18. SET NOCOUNT OFF;      
    19. GO      
  10. Try not to give your front-end applications a chance to query/manipulate the information straightforwardly, utilizing SELECT or INSERT/UPDATE/DELETE statement. Rather, create a stored procedure and let your applications get to these put away systems. This keeps the information spotless and predictable over each of the modules of your application, and in the meantime, brings together the business rationale inside the database.

  11. You need to take care of these points once you execute the SELECT query.

    In this example, you can see that I have [ ] because SQL Server has some pre-defined keywords e.g User, Admin, Address. So, in that, if you are not using [ ], it may throw error. That's why I have used Table name and column name inside [ ].
    1. Select * from User ----Wrong way    
    2.     
    3. Select [firstName],[lastname] from [User--Correct way    
  12. Constraint
    This one is very important, once you go to create or alter any constraint in SQL Server. I have seen that most of the developers create constraint without constraint name. In that case, SQL will create the default key name for constraint, e.g., default_2908xyz.

    Syntax
    1. --incorrect way  
    2. ALTER TABLE tblName    
    3. ADD clmnName INT NOT NULL DEFAULT 0    
    4.     
    5. --Correct way    
    6. ALTER TABLE tblNmae     
    7. ADD ClmnName DataTypes {NULL|NOT NULL}     
    8. CONSTRAINT defult_xyz DEFAULT {DEFAULT_VALUE}    
    9. [WITH VALUES]   
    In this above syntax, you can see that I have written two queries. If you write the SQL query as the first one,you will face drop column and constraint in future from SQL table.

    It's not applicable only for default constraint. We need to apply this for all constraints. For more details about constraint, click here.
I hope, you have learned a lot of points from this article. Wait for the second part for more advanced best practice tips.