SQL Server - Deleting Duplicate Records

An easy method for deleting the duplicate records from a table is by using the Common table expressions.

Let us create a table - State with columns StateID and StateName of data types Int and Varchar(20) respectively.

Create table State (StateID Int,StateName varchar(10))
Go
Insert into State
Select 1, 'XXXX'
Union All
Select 2, 'YYYY'
Union All
Select 1, 'XXXX'
Union All
Select 3, 'ZZZZ'
Union All
Select 2, 'YYYY'
Union All
Select 1, 'XXXX'
Union All
Select 3, 'ZZZZ'
Union All
Select 1, 'XXXX'
Union All
Select 2, 'YYYY'
Union All
Select 2, 'YYYY'
Union All
Select 1, 'XXXX'
Union All
Select 3, 'ZZZZ'

Now if we check the data in the State table, it will return 12 records with duplicate values as shown below

Select * from State 

 

Now our intention is to delete the duplicate records so that only 3 distinct records must exist.

For this we can use the Common table expression as below.

With DistinctStates(StateID,StateName,RecCount) as
(Select StateId, StateName, row_number() over(Partition by StateId,StateName Order by StateId) RecCount from State)
Delete from DistinctStates where RecCount > 1

Once we execute the above script all the duplicate Records will be deleted from the table resulting 3 distinct records

Select * from State 

SQL SERVER - ISNULL() vs COALESCE()

Here is the Differences between ISNULL() and COALESCE()

ISNULL() is non-standard and provides less functionality.
COALESCE() is ANSI standard. and is more maintainable, more functional code

ISNULL() Replaces NULL with the specified replacement value
COALESCE() returns first non null value among a list of arguements

ISNULL() uses two arguements only
COALESCE() can use any number of arguements

ISNULL()  returns the same data type as of the first arguement.
COALESCE() is not guaranteed to retrun the same data type as of its first arguement.

Let us see this through an example. At first, Let us declare a variable @str of data type varchar(3). @Str will contain NULL value as we didn't assigned any value to it. Now,

ISNULL(@Str,'ABCDEFGHIJ') retruns 'ABC' , the first three characters of the replacement value as @Str is of datatype Varchar(3).

Where as COALESCE(@Str,'ABCDEFGHIJ') retruns the full string as it does not depend on the  data type of the first arguement.

Declare @str Varchar(3)
Select ISNULL(@STR,'ABCDEFGHIJ') 'ISNULL VALUE', COALESCE(@STR,'ABCDEFGHIJ')  'COALESCE VALUE'

Output :

SQL SERVER – Uses of COALESCE() Function

Coalesce()  can be used to return the first Non-Null argument value among a series of values specified. If all the parameter/arguement values are null, then this  function will return the NULL value.

Examples:

Select Coalesce(NULL,NULL,34,NULL,23)  'First Non-Null Value'

Output :

Select Coalesce('',NULL,'','XYZ')  'First Non-Null Value'

Output :

Declare @Str varchar(10)
Select Coalesce(NULL,@Str,NULL)  'First Non-Null Value'

Output :

Let us consider a table which contains the marks of all the students who belongs to either Engineering or Medical streams.

If the student belongs to the Engineering stream, then the last two columns will have NULL values  where as if he belongs to Medical streams then the Mathematics column will have a null value.

Now Let us see how the query follows.

Create Table  StudentMarks(Id int identity(1,1), GroupId int , Mathematics int , Physics int ,Chemistry int , Zoology int ,Botany int )

Insert Into StudentMarks (GroupId, Mathematics, Physics, Chemistry, Zoology, Botany)
Select 1,100,96,98,NULL,NULL
Union All
Select 1,90,95,94,NULL,NULL
Union All
Select 2,NULL,94,99,89,97
Union All
Select 2,NULL,99,99,99,97

Now here is the query to calculate the total marks that each student, by using the Coalesce() 

Select ID, GroupId, Coalesce(Mathematics,0) + Coalesce(Physics,0) + Coalesce(Chemistry,0) + Coalesce(Zoology,0) + Coalesce(Botany,0) 'Total Marks'
From StudentMarks

Output :

Again Coalesce() can be used to Build Comma-Delimited String.

Here is an example to Build a Comma-Delimited String that contains all the columns of a table using the Coalesce() function.

SQL SERVER – CASE Expression \ Case Statement with Example

Case statement in SQL Server checks a list of conditions
and returns one of the multiple possible result values. Either it compares a single expression or Evaluates a list of Boolean expressions in order to retrun the result

Case statement can be used either in the Data Retrieval Statement-SELECT or in the Data Manipulation Statements- UPDATE and DELETE or in the SET statements.

Syntax:

CASE expression
     WHEN value_expression1 THEN result_expression1 WHEN value_expression2 THEN result_expression2
     [ ELSE else_result_expression ]
END

Or

CASE
     WHEN Boolean_expression1 THEN result_expression1
     WHEN Boolean_expression2 THEN result_expression2
     [ ELSE else_result_expression ]
END

Let us see this through a simple example. Let us first create a table called Student with columns StudentId,  Exam1, Exam2 and Exam3 

Example :

Create table Student
(StudentId int, Exam1 int,Exam2 int,Exam3 int) 

Insert Into Student
Select 1,89,80,90
Union All
Select 2,69,83,95
Union All
Select 3,35,70,90
Union All
Select 4,49,82,60
Union All
Select 5,99,98,95

Now,let us have a glance on the data that we have inserted

Select * From Student

Now Let us see the gradings of the students depending upon the following criteria :

If Total Marks >= 250 then Grade = 'A'
If Total Marks between 200 and 250 then Grade = 'B'
If Total Marks between 100 and 200 then Grade = 'C'
If Total Marks < 100 then Grade = 'E'

Now In order to see the Grades, we can use the Case statement as follows:

Select StudentId,                                                           Case WHEN (Exam1+Exam2+Exam3) > 250 THEN 'A'
       WHEN (Exam1+Exam2+Exam3) between 200 and 250 THEN 'B'
       WHEN (Exam1+Exam2+Exam3) between 100 and 200 THEN 'C' 
        Else 'D'
END 'Grade'
From Student

SQL SERVER – NULLIF() vs ISNULL()

NULLIF() Returns a null value if the two specified expressions are equal. If the Two expressions are not equal then it will return the first expression's value. Whether the returned value is NULL or NOT NULL, NULLIF() will return the same data type as the first expression 

Syntax  : NULLIF(expression1,expression2)

Example 1 :

Select NULLIF(100,50*2)  'NULLIF Value'

OutPut :

Example 2 :

Select NULLIF(2*2,2*7)  'NULLIF Value'

OutPut :

Example 3 :

Select NULLIF(20-2,19)  'NULLIF Value'

OutPut :

ISNULL() Replaces the NULL value with the specified expression value.

Syntax : ISNULL(check expression,replacement value)

Example 1 :

Select ISNULL(null,12) 'ISNULL VALUE'

OutPut :

Example 2:

Select ISNULL(marks,0)  'Marks'  from Student

OutPut :

SQL SERVER – Difference between DML Triggers and DDL Triggers

As we discussed earlier in the previous  article,  DML Triggers will be fired automatically whenever Data Manipulation statements like INSERT,UPDATE and DELETE are executed where as DDL triggers will fire with the Data definition statements like CREATE, ALTER and DROP.       

Now let us see the differences between these two types of triggers. 

DML Triggers	DDL Triggers
1. Operate on INSERT, UPDATE and DELETE	1. Operate on CREATE, DROP and ALTER
2. Applied on Tables and views	2. Applied on Databases and servers
3. Can be used as INSTEAD OF TRIGGERS	3. Cannot be used as INSTEAD OF TRIGGERS
4. Creates INSERTED and DELETED tables	4. Cannot Create INSERTED and DELETED tables
5. DML triggers run either Before or After a T-SQL statement is completed	5. DDL triggers run only after a T-SQL statement is completed

SQL SERVER – DDL Triggers

We are all well known about the DML Triggers which will be fired automatically whenever Data Manipulation statements like INSERT,UPDATE and DELETE are executed.

DDL triggers are same as that of the DML triggers, but they will fire with the Data definition statements like CREATE, ALTER and DROP instead of INSERT, UPDATE and DELETE
statements.

In general DDL triggers can be used to prevent the changes to the tables like 'DROP TABLE' and 'ALTER TABLE'  and
to audit the changes in the database schema. These triggers will fire only after the transact SQL statements are completed.  

In case of DML triggers, they will be applied on either tables or Views. where as DDL triggers will be applied on either Databases or servers.

Database-scoped DDL triggers are stored in the database in which they are created where as the Server-scoped DDL triggers are stored in the Master database.

We can get the Database-scoped and Server-scoped triggers using sys.triggers and sys.server_triggers respectively.

Now Let us have a look at the syntax for creating a DDL trigger. 

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS
{ sql_statement }

Here is an example for creating a DDL trigger while altering the Emp table 

Create Trigger Trig_AviodChangeEmp
on Database
For alter_table
as
Begin
   Print 'You cannot drop the column in the Table Emp'
   Rollback
End 

SQL SERVER – Difference Between SCOPE_IDENTITY(), @@IDENTITY and CURRENT_IDENT()

Scope_identity() will return the identity value generated with current scope in either a trigger or a stored procedure.

@@Identity will return the identity value generated with current session in any table

Ident_Current() will return the identity value generated with a particular table specified

Let us see the difference by using an example :

For this, let us first create two  tables TableA and TableB

Create Table TableA(ID INT IDENTITY(1,1) PRIMARY KEY , NAME VARCHAR(25) NOT NULL)

Create Table TableB(ID INT IDENTITY(100,5) PRIMARY KEY , NAME VARCHAR(25) NOT NULL)

TableA is having an Identity column ID with starting value 1 and increment value 1

TableB is having an Identity column ID with starting value 100 and increment value 5

Now Inserting Records into these tables,

Insert into TableA values('AAA')
Insert into TableA values('BBB')
Insert into TableA values('CCC')

Insert into TableB values('AAA')
Insert into TableB values('BBB')
Insert into TableB values('CCC')

On Retrieving the values from TableA and TableB,

Select * From TableA

Select * From TableB

Now let us create a Trigger on TableA, Which inserts a new record into the Table TableB.

Create Trigger Trigger_A on TableA for Insert as
Declare @Name varchar(25)
Begin
     Select @Name =  Name from Inserted
     Insert into TableB Values(@Name)
End

Now let us insert a new record in TableA,

Insert into TableA values('DDD')

Now let us have a look at our tables, TableA and TableB

Select * From TableA

Select * From TableB

Now On checking the values of Scope_identity() , @@Identity

Select  Scope_identity() ScopeIdentity, @@Identity IdentityValue

Here Scope_identity()  is the identity value that is generated with in the current scope -  the trigger,  which is 4.

@@ Identity is the last identity value that is generated with in the current session, i.e. in this case 115.

Also, Ident_Current() is pertaining to a specific table.

Select Ident_Current('TableA') Last_identity

 
Select Ident_Current('TableB') Last_identity

SQL SERVER – MAGIC Tables – INSERTED and DELETED

Two Special temporary tables will play a vital role while creating a trigger on a table. Those two tables are called MAGIC Tables. they are – INSERTED and  DELETED.

These two tables are used to test the effects of certain data modifications and to set the conditions for trigger actions. these two tables contains the same data structure as that of the original table on  which the trigger will be created.

While inserting a new record, a new row will be inserted in the INSERTED table.

While updating a record, the old details will placed in the DELETED table as a new record and the changes will be updated in the Original table. At the same time a new record will be placed in the INSERTED table.

While deleting a record from the original table, a new record will be placed in the DELETED table and the corresponding record will be deleted from the original trigger table.

SQL SERVER – How To Find Out which Version of Microsoft SQL Server is running

Some times we have to find out the Version of SQL Server that is running.

There are many ways to find out the version of the SQL Server.

In Order to find out  the version of Microsoft SQL Server, First Of all connect to SQL Server by using SQL Server Management Studio, and then run the following Transact-SQL statement.

Select @@version  'Version'

This Results,

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)   Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

OutPut :

The Other Alternative is, using the system stored Procedure Sp_msgetversion

 Exec master..sp_msgetversion

OutPut :

As a third alternative, we can use the Extended stored procedure xp_msver. This Command gives a detailed information like ProductName, ProductVersion, Language etc.,

OutPut :

 

SQL SERVER – How To Prefix zeros to an Integer

There are some cases where we have to add zeros to an integer as prefix. This can be achieved  by the Right() string function.  Let us take a table – Employee, which is having an Identity column, EmpId with values 1,2,3 and so on.

Create Table EmpDetails(EmpId int identity(1,1), EmpName varchar(25), Salary Money)

INSERT INTO EmpDetails
SELECT 'AAA', 500000
UNION ALL
SELECT 'BBB', 400000
UNION ALL
SELECT 'CCC', 900000

Now on checking the data,

SELECT EMPID,EMPNAME ,SALARY FROM Empdetails

 

Now, In order to add zeros as prefix to the Empid,we will use  Right() string function as shown below.

SELECT RIGHT('00000'+CONVERT(VARCHAR,EMPID),5) EmpId, EMPNAME ,SALARY  FROM EmpDetails

Here we have converted EmpId to VARCHAR Data type,  concatenated zeros to the EmpId and applied Right() string function to the result set.

The Output is,

SQL SERVER – Information_Schema

SQL Server’s Information_Schema view Provides a detail Information about the meta data. Every database contains the Information schema View. This View is available under System views, which contains metadata for all data objects that are stored in the database.

Let us see all these views one by one.

1.  CHECK_CONSTRAINTS : Information about all the CHECK constraints in the database.

2.  COLUMN_DOMAIN_USAGE : Information about all columns that has an alias data type in the database.

3.  COLUMN_PRIVILEGES : Information about all columns that has a privilege that is either granted to or granted by the current user in the current database.

4.  COLUMNS : Information about all the columns of all the tables that can be accessed by the current user in the current database.

5.  CONSTRAINT_COLUMN_USAGE : Information about all the constraints that are defined on all columns in the current database.

6.  CONSTRAINT_TABLE_USAGE : Information about all the constraints that are defined on all tables in the current database.

7.  DOMAIN_CONSTRAINTS : Information about all the alias data types in the current database that has a rule bound to it by using sp_bindrule

8.  DOMAINS : Information about all the alias data types in the current database

9.  KEY_COLUMN_USAGE : Information about all columns that is constrained as a key in the current database. 

10. PARAMETERS : Information about all parameters of a user-defined function or stored procedure that can be accessed by the current user in the current database.

11. REFERENTIAL_CONSTRAINTS : Information about all the FOREIGN KEY constraint in the current database.

12. ROUTINES : Information about all the stored procedures and functions that can be accessed by the current user in the current database.

13. ROUTINE_COLUMNS : Information about all the columns that are returned by the table-valued functions that can be accessed by the current user in the current database.

14. SCHEMATA : Information about all the schema in the current database.

15. TABLE_CONSTRAINTS : Information about all the table constraints in the current database. 

16. TABLE_PRIVILEGES : Information about all the privileges on each table that is granted to or granted by the current user in the current database.

17. TABLES : Information about all the tables in the current database.

18. VIEW_COLUMN_USAGE : Returns all column details in the current database that is used in all view definitions.

19. VIEW_TABLE_USAGE : Returns all table details in the current database that is used in all view definitions.

20. VIEWS : Information about all the views that can be accessed by the current user in the current database.

SQL SERVER – String or Binary Data would be Truncated

In SQL Server,While inserting the data in to a table using the insert statement, Occasionally we will see the following error message:

“String or Binary Data would be Truncated”

We will get this error, when the length of the value entered into a CHAR / VARCHAR / NCHAR / NVARCHAR column is longer than the maximum length of the column.

Let us see this through an example. First we will create  a table Product :

Create Table Product (ProdId INT,ProdName VARCHAR(16), ProdDesc VARCHAR(25))

Now inserting records,

Insert Into Product Values (1,'Adjustable Race' ,'Adjustable Race' )                                                                      Go                                                                         Insert Into Product Values (2,'Chain Ring Bolts' ,'Chain Ring Bolts' )                                                                      Go                                                                        Insert Into Product Values (3,'Chain Ring Nuts','Chain Ring Nuts')

On selecting the Records from the Table,

Select * From Product

Now we will insert one more record whose Product name length is longer than the maximum length of the column

Insert Into Product Values (4,'Headset Ball Bearings', 'Headset Ball Bearings')

whenever, we try to insert this record , it will throw an error specifying that “String or Binary Data would be Truncated” as the length of the Product name column is greater than the maximum length of the column.

SQL SERVER – Deterministic and Non Deterministic Functions

A function is said to be Deterministic when it returns the same result any time with the same set of input values.

A function is said to be Non Deterministic when it returns the different results each time with the same set of input values.

Let us take an user defined function which takes two integers values as input parameters and returns their sum. In this case, if the input parameters are 2 and 3 then each time this function will return the same result 5 which is deterministic.

To see this through an example, Let us first create a function ADD_NUMBERS which has two input parameters Num1 and Num2 and returns their sum.

CREATE FUNCTION ADD_NUMBERS(Num1 INT,Num2 INT) RETURNS INT
AS RETURN (NUM1 + NUM2)

Now, whenever we execute this function with the same input values 2 & 3 it returns 5.

Similarly the DATEDIFF system function always returns the same result for any given set of argument values for its three parameters.

As shown in the below query, DATEDIFF function always returns 5 for the same set of input values.

All of the Aggregate and String Built-in functions are deterministic except the CHARINDEX and PATINDEX.

Again, GETDATE() is the best example for  Non Deterministic Function because each time it returns different values with the same input parameter. As shown in the below screen shot, GETDATE() function returns different time values.

All of the configuration, cursor, meta data, security, and system statistical functions are nondeterministic.Some more examples for Non deterministic functions are : PATINDEX,CHARINDEX, CURRENT_TIMESTAMP,@@ERROR.

SQL SERVER – Date Formats using CONVERT() Function

In the Previous article, we learned about the Convert() function,  which can be used to change the date format in what ever format we like. Now, In this article we will see How to get various date formats  using Convert() function.

One of the Most frequently asked questions in any of the Interview or in any of the SQL Server forums is - How to extract only Date value from DATETIME column or How to change the DATETIME value in to a specified format. The answer to all these queries is by using the Convert() function. The CONVERT function's third optional parameter  ‘style’ plays a vital role in formatting the value while  converting the  a datetime value to a VARCHAR.

Let us see various Standard Date Formats that are available in SQL Server.

SQL statement	Output
SELECT CONVERT(VARCHAR(8), GETDATE(), 1)  'MM/DD/YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 2)  'YY.MM.DD'
SELECT CONVERT (VARCHAR(8), GETDATE(), 3) 'DD/MM/YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 4) 'DD.MM.YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 5) 'DD-MM-YY'
SELECT CONVERT (VARCHAR(9), GETDATE(), 6)  'DD MON YY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 7)  'Mon DD, YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 8)  'HH:MM:SS'
SELECT CONVERT (VARCHAR(26), GETDATE(), 9)       'Mon DD YYYY HH:MM:SS:MMMAM/PM'
SELECT CONVERT (VARCHAR(8),  GETDATE(), 10)    'MM-DD-YY'
SELECT CONVERT (VARCHAR(8),  GETDATE(), 11) 'YY/MM/DD'
SELECT CONVERT (VARCHAR(6), GETDATE(), 12)  'YYMMDD'
SELECT CONVERT (VARCHAR(24), GETDATE(), 13)       'DD Mon YYYY HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(12), GETDATE(), 14)  'HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(19), GETDATE(), 20)  'YYYY-MM-DD HH:MM:SS'
SELECT CONVERT (VARCHAR(35), GETDATE(), 21)   'YYYY-MM-DD HH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(20), GETDATE(), 22)  'MM/DD/YY HH:MM:SS PM(AM)'
SELECT CONVERT (VARCHAR(10), GETDATE(), 23)  'YYYY-MM-DD'
SELECT CONVERT (VARCHAR(10), GETDATE(), 24)  'HH:MM:SS'
SELECT CONVERT (VARCHAR(10), GETDATE(), 25) 'YYYY-MM-DD'
SELECT CONVERT (VARCHAR(30), GETDATE(),100)  'Mon DD YYYY HH:MMAM/PM'
SELECT CONVERT (VARCHAR(10), GETDATE(), 101)  'MM/DD/YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 102)  'YYYY.MM.DD'
SELECT CONVERT (VARCHAR(10), GETDATE(), 103)  'DD/MM/YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 104) 'DD.MM.YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 105) 'DD-MM-YYYY'
SELECT CONVERT (VARCHAR(11), GETDATE(), 106) 'DD Mon YYYY'
SELECT CONVERT (VARCHAR(12), GETDATE(), 107)  'Mon DD, YYYY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 108)  'HH:MM:SS'
SELECT CONVERT (VARCHAR(11), GETDATE(), 109) 'Mon DD YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 110)  'MM-DD-YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 111) 'YYYY/MM/DD'
SELECT CONVERT (VARCHAR(10), GETDATE(), 112) 'YYYYMMDD'
SELECT CONVERT (VARCHAR(24), GETDATE(), 113)  'DD Mon YYYY HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(12), GETDATE(), 114) 'HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(19), GETDATE(), 120) 'YYYY-MM-DD HH:MM:SS'
SELECT CONVERT (VARCHAR(23), GETDATE(), 121) 'YYYY-MM-DD HH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(24), GETDATE(), 126) 'YYYY-MM-DDTHH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(110), GETDATE(), 127)   'YYYY-MM-DDTHH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(26), GETDATE(), 130)  'DD Mon YYYY HH:MM:SS:MMMAM/PM'
SELECT CONVERT (VARCHAR(25), GETDATE(), 131)  'DD/MM/YYYY HH:MM:SS:MMMAM/PM'