Dynamic Query in SQL

 Dynamic SQL:

Dynamic SQL is a programming technique that allows you to construct SQL statements dynamically at runtime.

Creating a dynamic SQL is simple, you just need to make it a string as follows:

'SELECT * FROM production.products';
Code language: SQL (Structured Query Language) (sql)

To execute a dynamic SQL statement, you call the stored procedure sp_executesql as shown in the following statement:

EXEC sp_executesql N'SELECT * FROM production.products';
Code language: SQL (Structured Query Language) (sql)

Because the sp_executesql accepts the dynamic SQL as a Unicode string, you need to prefix it with an N.

Example:

DECLARE 

    @table NVARCHAR(128),

    @sql NVARCHAR(MAX);

SET @table = N'production.products';

SET @sql = N'SELECT * FROM ' + @table;

EXEC sp_executesql @sql;

Why do we need Dynamic SQL?

• We need to use Dynamic SQL for the following use cases:
• When we need to run dynamic queries on our database, mainly DML queries.
• When we need to access an object which is not in existence during the compile time.
• Whenever we need to optimize the run time of our queries.
• When we need to instantiate the created logic blocks.
• When we need to perform operations on application fed data using invoker rights.

Improve SQL Query Performance

 Select Appropriate Data type

 

I think the selection of the data type for a column can have the most important role in database performance. If we select an appropriate data type then it will reduce the space and enhance the performance otherwise it generates the worst effect. So select an appropriate data type according to the requirements. SQL contains many data types that can store the same type of data but select an appropriate data type because each data type has some limitations and advantages upon another data type.

 

The following are some guidelines about the selection of data type:

Never use a nvarchar or nchar, instead use a varchar or char because nvarchar and nchar takes a double amount of memory compared to varchar and char. Use nchar and nvarchar when we must store Unicode or 16-bit character data such as Hindi characters.

Avoid use of the text data type instead of varchar because the performance of a varchar is much better than text. Use the text data type when we must store text data of more than 8000 characters.

Never use Select * Statement

 When we require all the columns of a table then we usually use a “Select *” statement but this is not a good approach because when we use the “select *” statement then SQL Server convert * into all column names before executing the query and this approach takes some extra time and effort. So always provide all the column names in the query instead of “select *”. 

Select Appropriate choice between Exist and IN

 

I find that many programmers often suggest use Exist instead of IN but I am not satisfied with those people because it is not always correct that we use Exist instead of IN query. It mainly depends upon the amount of data. EXISTS provides the answer in the form of TRUE or FALSE & IN return values.

 

Now the question develop of which is faster, IN or EXISTS?

 

It totally depends upon the query. For some queries Exist is efficient and for some IN is efficient.

 

Exits is a correlated sub-query in which the outer query runs first and for each outer query an inner query is probed.

 

Whereas in IN the sub-query is evaluated, distinct, indexed and then joined to the original table. So consider a one big table (say 1 million rows) and one small table containing relatively fewer rows.

 

So if the outer table is small then it will be probed a fewer number of times and with the inner query (a big table) Exist will be faster.

 

If the inner query table (small table) is giving a smaller result set than IN will be faster. EXISTS will find the first row faster in general than the IN will and the IN will get the LAST row (all rows) faster then the where exists.

 

The recommendation at that time was:

If the majority of the filtering criteria is in the subquery, use IN.

If the majority of the filtering criteria is in the main query, use EXISTS.

In other words:

IN for a large outer query and small inner query.

EXISTS for small outer query and big inner query.

Never use Count(*) or Count(1)

We should never use Count(*) or Count(1) in SQL . Instead of this we should use Count (Col_Name) .

Exception plan of Count(*), Count(1), Count (Col_Name) are the same. 

SELECT COUNT( *) FROM Employee  

SELECT COUNT( 1) FROM Employee  

SELECT COUNT(Emp_IId)  FROM Employee 

Never Use ” Sp_” for User Define Stored Procedure

 

Most programmers use “sp_” for user-defined Stored Procedures. I suggest to never use “sp_” for user-defined Stored Procedure because In SQL Server, the master database has a Stored Procedure with the "sp_" prefix, so when we create a Stored Procedure with the "sp_" prefix then SQL Server always looks first in the master database then in the user-defined database so it takes some extra time.

So we use another prefix for the user-defined Stored Procedure like “usp_” as in the following:

sp_Employees_Insert   /* Bad Practice   */  

usp_Employees_Insert  /* Good Practice */  

Avoid Cursors

 

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor is a set of rows together with a pointer that identifies a current row. It is a database object to retrieve data from a result set one row at a time. But use of a cursor is not good because it takes a long time because it fetches data row by row.

 

So we can use a replacement of cursors. A Temporary table, For or While loop may be a replacement of a cursor in some case.

 

Use Normalization

In creating a database, normalization is the process of organizing it into tables in such a way that the results of using the database are always unambiguous and as intended. It consists of decomposing tables to eliminate data redundancy and undesirable characteristics like insert, update and delete anamolies.

 We should use the following 4 normalization rules in the creation of a database:

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Boyce & Codd Normal Form (BCNF)

Use Try–Catch

 

In T-SQL a Try-Catch block is very important for exception handling. A best practice and use of a Try-Catch block in SQL can save our data from undesired changes. We can put all T-SQL statements in a TRY BLOCK and the code for exception handling can be put into a CATCH block.

 

Use Exception Handling for the following:

In Transaction Management for Rollback the transaction

When using Cursor in SQL Server

When implement DML Query (Insert,Update,Delete) for checking of error and handle them.

Example

Begin Transaction Trans  

Begin Try  

Delete From Employee Where Employee.Emp_IID<3  

Update Employee Set Employee.First_Name='Pankaj kumar' Where Employee.Emp_IID='6th' /* Error Will Occur Here */  

If @@TranCount>0  

begin Commit Transaction Trans  

End  

End Try  

Begin Catch  

if @@TranCount>0  

Print 'Error Is Occur in Transaction'  

begin Rollback Transaction Trans   /* RollBack Occur  */  

End  

End Catch

Avoid Multiple Joins

 

Try to avoid writing a SQL query using multiple joins that includes outer joins, cross apply, outer apply . It reduce the speed of execution and reduces the choices for Optimizer to decide the join order and join type. We can use temp table or temp variables instead of Multiple Joins.

 

Create and Use the Index

 

An index is a data structure to retrieve fast data. Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. Mainly an index increases the speed of data retrieval.

 

There are the following two types of indexes in SQL.

Clustered Index

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore a table can have only one clustered index and this is usually made on the primary key. The leaf nodes of a clustered index contain the data pages.

Non-Clustered Index

A non-clustered index, on the other hand, does not alter the way the rows are stored in the table. It creates a completely different object within the table that contains the column(s) selected for indexing and a pointer back to the table's rows containing the data. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

Create Nonclustered Index NonClusteredIndex  

on Employee_Detail(Emp_Name,Emp_Age)       /* Non Clustered Index  */  

  

Create Clustered Index My_ClusteredIndex      

on Employee_Detail(Emp_IId)                   /* Clsutered Index   */

We should use the index in the table.

Use Primary Key

 

We should create a primary key on each table. It has two benefits. The first is that it uniquely identifies each record in the table and second it creates a clustered index that stores the data in the form of a B-Tree. So due to this approach the retrieval of the data is very fast.

 

Column Level

Create Table Table_Name  

(  

Column_Name Datatype Constraint Constraint_Name Primary Key,  

) 

Example

Create Table Employee  

(  

IId int constraint Const_primary_IId primary key,  

Name nvarchar(50)  

) 

Table Level

Alter Table Table_Name  

Add constraint Constraint_Name Primary Key(Column_Name)

Example

Alter Table Employee  

Add constraint Constraint_Name Primary Key(Emp_No,Salary)

Use Alias NameAliasing renames a table or a column temporarily by giving another name. The use of table aliases means to rename a table in a specific SQL statement. Using aliasing, we can provide a small name to large name that will save our time. 

SELECT Employee.Emp_IId,Employee.First_Name,Employee.Last_Name ,Employee.Salary FROM Employee   /*Bad Practice    */  

SELECT e.Emp_IId,e.First_Name,e.Last_Name,e.Salary FROM Employee e   /* Good Practice    */  

Use Stored Procedure

 We should be use a Stored Procedure for repeated data. Stored Procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Executable code is automatically cached and shared among users. The code for any repetitious database operation is the perfect candidate for encapsulation in procedures. This eliminates needless rewrites of the same code, decreases code inconsistency and allows the code to be accessed and executed by any user or application possessing the necessary permissions.

 

Use Transaction Management

A transaction is a unit of work performed against the database. A transaction is a set of work (T-SQL statements) that execute together like a single unit in a specific logical order as a single unit. If all the statements are executed successfully then the transaction is complete and the transaction is committed and the data will be saved in the database permanently. If any single statement fails then the entire transaction will fail and then the complete transaction is either cancelled or rolled back.

 

A transaction mainly contains 4 properties that are also known as ACID rules.

Atomicity: Atomic means that all the work in the transaction is treated as a single unit.

Consistency: Transaction ensures that the database properly changes states upon a successfully committed transaction.

Isolation: It ensure that transactions to operate independently and transparent to each other.

Durability: It ensures that the effect of a committed transaction will be saved in the database permanently and should persist no matter what (like due to power failure or something).

Begin Transaction in SQL Server

In the next article we will learn some other concepts to increase the performance of a SQL Server database.

Query Question in SQL

1. Find nth salary form Employee:

TO FIND NTH HIGHEST SALARY USING CTE

1. SELECT*FROM  [DBO].[EMPLOYEE] ORDER BY SALARY DESC  
2. GO  
3.   
4. WITH RESULT AS  
5. (  
6.     SELECT SALARY,  
7.            DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK  
8.     FROM EMPLOYEE  
9. )  
10. SELECT TOP 1 SALARY  
11. FROM RESULT  
12. WHERE DENSERANK = 3  

duplicate records

SELECT username, email, COUNT(*)
FROM users
GROUP BY username, email
HAVING COUNT(*) > 1

If you want to delete the duplicates, here's a much simpler way to do it than having to find even/odd rows into a triple sub-select:

SELECT id, name, email 
FROM users u, users u2
WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id

And so to delete:

DELETE FROM users
WHERE id IN (
    SELECT id/*, name, email*/
    FROM users u, users u2
    WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
)

If you want to delete duplicate record with contains one record.

with TEST as(
 select ProductId,city, ROW_NUMBER()  OVER(PARTITION BY city ORDER BY city) AS T from PRODUCTDETAILS
)
SELECT * FROM TEST WHERE T>1

Transaction

What is Transaction.

A transaction is a set of operations performed so all operations are guaranteed to succeed or fail as one unit.


When to Use Transactions

You should use transactions when several operations must succeed or fail as a unit.

The following are some frequent scenarios where use of transactions is recommended:

In batch processing, where multiple rows must be inserted, updated, or deleted as a single unit

Whenever a change to one table requires that other tables be kept consistent

When modifying data in two or more databases concurrently

In distributed transactions, where data is manipulated in databases on various servers.


Understanding ACID Properties.

1. Atomicity:

2. Consistency

3. Isolation

4. Durability




Example:

1. 1. create procedure sp_Trans_Test  
   2. @newpersonid nvarchar(5),  
   3. @newfirstname nvarchar(10)  
   4. @newcompanyname nvarchar(15),  
   5. @oldpersonid nvarchar(5)  
   6. as  
   7. declare @inserr int  
   8. declare @delerr int  
   9. declare @maxerr int  
   10. set @maxerr = 0  
   11. BEGIN TRANSACTION  
   12.   
   13. -- Add a person  
   14. insert into person (personid, firstname, company)  
   15. values(@newpersonid, @newfirstname, @newcompanyname)  
   16.   
   17. -- Save error number returned from Insert statement  
   18. set @inserr = @@error  
   19. if @inserr > @maxerr  
   20. set @maxerr = @inserr  
   21.   
   22. -- Delete a person  
   23. delete from person  
   24. where personid = @oldpersonid  
   25.   
   26. -- Save error number returned from Delete statement  
   27. set @delerr = @@error  
   28. if @delerr > @maxerr  
   29. set @maxerr = @delerr  
   30.   
   31. -- If an error occurred, roll back  
   32. if @maxerr <> 0  
   33. begin  
   34. ROLLBACK  
   35. print 'Transaction rolled back'  
   36. end  
   37. else  
   38. begin  
   39. COMMIT  
   40. print 'Transaction committed'  
   41. end  
   42. print 'INSERT error number:'+ cast(@inserras nvarchar(8))  
   43. print 'DELETE error number:'+ cast(@delerras nvarchar(8))  
   44. return @maxerr

Types Of Keys In Database

Database supports the following types of keys.

• Super Key
• Minimal Super Key
• Candidate Key
• Primary Key
• Unique Key
• Alternate Key
• Composite Key
• Foreign Key
• Natural Key
• Surrogate Key

Key In Sql:

Composite Key:

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

Columns that make up the composite key can be of different data types.

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, 

but when it taken individually it does not guarantee uniqueness.

A primary key that is made by the combination of more than one attribute is known as a composite key.

CREATE TABLE SAMPLE_TABLE  

(COL1 integer,  

COL2 nvarchar(30),  

COL3 nvarchar(50),  

PRIMARY KEY (COL1, COL2)); (Composite Key)

Candidate Key:

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In the below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

AlterNate key:

Alternate key is a secondary key it can be simple to understand by an example:

Let's take an example of student it can contain NAME, ROLL NO., ID and CLASS.

Here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate keys.

If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys.

In simple words, you can say that any of the candidate key which is not part of primary key is called an alternate key. 

So when we talk about alternate key, 

the column may not be primary key but still it is a unique key in the column.

Super Key:

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. 

Example: Primary key, Unique key, Alternate key are a subset of Super Keys

TUPLE

A tuple is a data structure that contains a sequence of elements of different data types. 

It can be used where you want to have a data structure to hold an object with properties, but you don't want to create a separate type for it.

Tuple<T1, T2, T3, T4, T5, T6, T7, TRest>

Tuple<int, string, string> person = 
                        new Tuple <int, string, string>(1, "Steve", "Jobs");

Example: Accessing Tuple Elements

var person = Tuple.Create(1, "Steve", "Jobs");
person.Item1; // returns 1
person.Item2; // returns "Steve"
person.Item3; // returns "Jobs"


var numbers = Tuple.Create("One", 2, 3, "Four", 5, "Six", 7, 8);
numbers.Item1; // returns "One"
numbers.Item2; // returns 2
numbers.Item3; // returns 3
numbers.Item4; // returns "Four"
numbers.Item5; // returns 5
numbers.Item6; // returns "Six"
numbers.Item7; // returns 7
numbers.Rest; // returns (8)
numbers.Rest.Item1; // returns 8





Usage of Tuple

Tuples can be used in the following scenarios:

When you want to return multiple values from a method without using ref or outparameters.
When you want to pass multiple values to a method through a single parameter.
When you want to hold a database record or some values temporarily without creating a separate class.


Tuple Limitations:

Tuple is a reference type and not a value type. It allocates on heap and could result in CPU intensive operations.
Tuple is limited to include 8 elements. You need to use nested tuples if you need to store more elements. However, this may result in ambiguity.
Tuple elements can be accessed using properties with a name pattern Item<elementNumber> which does not make sense.