All posts by Basavaraj Biradar

How to Calculate Age in Sql Server

Recently, while filling some form needed to provide my age in years. Thought of calculating it in sql server and as usual used the DATEDIFF function like below, but it gave me wrong answer:

Script which gives Wrong Answer:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth',
     GETDATE() 'Current Date',
 DATEDIFF(YEAR,@DOB,GETDATE()) 'Age in Years'

Incorrect Result:

Date of Birth   Current Date                Age in Years
-------------   -----------------------     ------------
1980-12-29      2012-04-26 09:46:21.833     32
(1 row(s) affected)

Answer returned by the above query is 32 years which is 1 year more than the actual age. Reason for this mistake is, we are taking into consideration just the year part and not the day and month. So, we will get 1 year extra if the birth day has not yet crossed in the current year. So it means the above query will return correct age in years if the birthday has already passed in the current year.

To solve this issue we need to subtract 1 year if the birth date has not yet crossed in the current year:

Script which gives Correct Answer:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth', GETDATE() 'Current Date',
   DATEDIFF(YEAR,@DOB,GETDATE())
     -
   (CASE
      WHEN DATEADD(YY,DATEDIFF(YEAR,@DOB,GETDATE()),@DOB)
            >  GETDATE() THEN 1
      ELSE 0 END)
   'Age in Years'

Correct Result:

Date of Birth   Current Date              Age in Years
-------------   -----------------------   ------------
1980-12-29      2012-04-26 10:23:05.690   31
(1 row(s) affected)

Please correct me, if this is not the correct way of calculating the age. Comments are always welcome.

Simple Quiz On Sql Server Select statement

If we execute a statement like below, it will throw error:

SELECT xyz

Result:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'xyz'.

Can you guess what the below statement execution will result and reason for the same? Be frank don’t execute the statement and answer. By the way Today, I came to know about this behaviour only after executing this statement 🙂

SELECT 111xyz

INSERT/UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’ …

In this article we will discuss on when we get the errors like below in Sql Server and how to resolve them.

Msg 1934, Level 16, State 1, Procedure AddEmployee, Line 5

INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

First to reproduce this scenario we will create a demo db, table, populate sample data in the table and create a stored procedure to add the data to table:

CREATE DATABASE DemoSQLHints
GO
USE DemoSQLHints
GO
SET ANSI_NULLS ON
GO
CREATE TABLE dbo.Employee(EmployeeId int identity(1,1),
                   FirstName VARCHAR(50),LastName  VARCHAR(50))
GO
--Insert 1k records using GO statement as below
INSERT INTO dbo.Employee(FirstName,LastName) VALUES(NEWID(),NEWID())
GO 1000

GO
--Create Stored Procedure to insert record in Employee Table with
--QUOTED_IDENTIFIER setting set to "OFF"

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE DBO.AddEmployee(@FirstName VARCHAR(50),@LastName VARCHAR(50))
AS
BEGIN
 SET NOCOUNT ON 
 INSERT INTO dbo.Employee(FirstName,LastName)
 VALUES (@FirstName, @LastName )
END
GO

Below statement to insert record in the Employee table, successfully executes and inserts a record in the employee table:

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

Now try to create a filtered index (New Feature introduced in Sql Server 2008) on the employee table as shown below. To create filtered index, sql server requires it to be created with SET QUOTED_IDENTIFIER setting as ON.

SET QUOTED_IDENTIFIER ON
GO
CREATE NONCLUSTERED INDEX IX_Emplyoee_EmployeeId
 ON Employee(EmployeeId) WHERE EmployeeId > 500
GO

After creating the above filtered index, try executing the below statement which was executed successfully prior to the creation of the filtered index.

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

This time the execution of the above statement returns the below error:

Msg 1934, Level 16, State 1, Procedure AddEmployee, Line 5

INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Reason for this error is:  Employee table has filtered index and due to this any DML statement on this table which is executed with SET QUOTED_IDENTIFIER setting as OFF will result in failure. Here as the stored procedure AddEmployee is created with SET QUOTED_IDENTIFIER setting as OFF, so whenever this sp is executed it will use this setting stored in the meta data.

To solve this issue, we need to re-create the SP AddEmployee  with QUOTED_IDENTIFIER setting as ON as shown below:

DROP PROCEDURE dbo.AddEmployee
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE DBO.AddEmployee(@FirstName VARCHAR(50),@LastName VARCHAR(50))
AS
BEGIN
 SET NOCOUNT ON 
 INSERT INTO dbo.Employee(FirstName,LastName)
 VALUES (@FirstName, @LastName )
END
GO

Now, try executing the below statement:

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

Now, the above statement executes successfully and inserts a record in the employee table 🙂

You would also like to gothrough the article SET Options with their setting values required while working with filtered index

Please go through the article SET QUOTED_IDENTIFIER ON/OFF Setting in Sql Server to have detailed information on this setting. It is better practice to use SET QUOTED_IDENTIFIERS ON setting.

Please correct me, if my understanding is wrong. Comments are always welcome.

Note: All the examples in this article are tested on Sql Server 2008 version

[ALSO READ] Difference Between SET QUOTED_IDENTIFIER ON and OFF setting

Sql Server’s DATEADD function may return different result in a scenario where we are adding one month at a time from adding multiple months at a time to the date.

Today, I came across a scenario were observed that DATEADD function is returning different result if we add 1 month at time from adding multiple months at a time. At the first instance results were surprising to me, later found it to be logically correct. May be for most of you these  results were obvious, but at first look it surprised me 🙂

Let us understand this with an example:

Declare @ExDate DATE = '2012-01-31'
SELECT @ExDate AS ExDate,
DATEADD(MONTH,2,@ExDate) AS AddTwoMonths,
DATEADD(MONTH,1,DATEADD(MONTH,1,@ExDate)) AddOneMonthThenOneMoreMonth

RESULT:

ExDate     AddTwoMonths AddOneMonthThenOneMoreMonth
---------- ------------ ---------------------------
2012-01-31 2012-03-31   2012-03-29
(1 row(s) affected)

In the above example I was expecting DATEADD(MONTH,2,@ExDate) same as DATEADD(MONTH,1,DATEADD(MONTH,1,@ExDate)), as at the end of the day we are adding two months in both the cases. Finally, after seeing results of this example we can conclude that adding one month shouldn’t go from Jan to March or shouldn’t bring a non existing date in  Feb i.e. 31st Feb.

This behaviour is not specific to Sql Server, same behaviour can be observed in C# too.

Please correct me, if my understanding is wrong. Comments are always welcome.

Note: All the examples in this article are tested on Sql Server 2008 version. And in this article using the new Date data type introduced in Sql Server 2008.

SET ANSI_NULLS ON/OFF Setting in Sql Server

While creating Stored Procedures, User Defined Functions etc, most of us use the SET ANSI_NULLS ON/OFF and SET QUOTED_IDENTIFIER ON/OFF Settings. In this article will discuss on the SET ANSI_NULLS  { ON | OFF } Setting.

To understand this with an example, let us create table Name and insert three records in this table as below:

CREATE TABLE dbo.Name(FirstName VARCHAR(50),LastName Varchar(50)) GO 
INSERT INTO dbo.Name VALUES('BASAVARAJ','BIRADAR'),
 ('KALPANA','PATIL'),
 ('MONTY', NULL) GO

Depending on the ANSI_NULLS setting value either ON or OFF, SQL Server behaves differently while comparing with NULL value.

SET ANSI_NULLS  ON

When this setting value is ON (i.e. SET ANSI_NULLS ON) then comparison with NULL value using = and <> comparison operator will return false. Below script demonstrates this fact. 

SET ANSI_NULLS ON 
GO
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName = NULL
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName <> NULL
Result:
FirstName               LastName 
----------------------- --------------------------
(0 row(s) affected)
FirstName               LastName
----------------------- --------------------------
(0 row(s) affected)

So, when this setting value is ON we need to use IS NULL or IS NOT NULL instead of comparison operator = and <>. Below script demonstrates this fact. 

SET ANSI_NULLS ON 
GO 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName IS NULL 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName IS NOT NULL 
Result:
FirstName                LastName 
------------------------ ------------------------ 
MONTY                    NULL 
(1 row(s) affected) 
FirstName                LastName 
------------------------ ------------------------ 
BASAVARAJ                BIRADAR 
KALPANA                  PATIL 
(2 row(s) affected)

 

SET ANSI_NULLS  OFF

On the other hand if this setting value is OFF (i.e. SET ANSI_NULLS OFF) then comparison with NULL value using = and <> comparison operator returns TRUE if the value to be compared is NULL and NON NULL value respectively. Below Script demonstrates this fact.

SET ANSI_NULLS OFF 
GO 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName = NULL 
SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName <> NULL 
Result:
FirstName               LastName 
----------------------- --------------------- 
MONTY                   NULL 
(1 row(s) affected) 
FirstName               LastName 
----------------------- --------------------- 
BASAVARAJ               BIRADAR 
KALPANA                 PATIL 
(2 row(s) affected)

 

Another important point to note is that, the SET ANSI_NULLS { ON| OFF } setting with which we create Stored Procedure/UDF etc will be stored in the meta data. So, whenever Stored Procedure/UDF executes, it will use these setting stored in the meta data. It will ignore the settings of the client or the calling application.

 Below query can be used to find the objects which are created with SET ANSI_NULLS setting as OFF.

SELECT OBJECT_NAME (object_id) FROM sys.sql_modules
WHERE uses_ansi_nulls = 0 -- 0 means OFF and 1 means ON

As per BOL for SQL Server 2008 or 2012, in future versions this setting value will always be ON and explicitly setting it to OFF will result in error. So, it is better avoid explicitly setting  this in future development work.

Please correct me, if my understanding is wrong. Comments are always welcome.

Note: All the examples in this article are tested on Sql Server 2008 version