SQL SERVER – SmallDateTime and Precision – A Continuous Confusion

Some kinds of confusion never go away. Here is one of the ancient confusing things in SQL. The precision of the SmallDateTime is one concept that confuses a lot of people, proven by the many messages I receive everyday relating to this subject.

Let me start with the question: What is the precision of the SMALLDATETIME datatypes?

What is your answer? Write it down on your notepad.

Now if you do not want to continue reading the blog post, head to my previous blog post over here: SQL SERVER – Precision of SMALLDATETIME.

A Social Media Question

Since the increase of social media conversations, I noticed that the amount of the comments I receive on this blog is a bit staggering. I receive lots of questions on facebooktwitter or Google+. One of the very interesting questions yesterday was asked on Facebook by Raghavendra. I am re-organizing his script and asking all of the questions he has asked me. Let us see if we could help him with his question:

CREATE TABLE #temp
(name VARCHAR(100),registered smalldatetime)
GO
DECLARE @test smalldatetime
SET @test=GETDATE()
INSERT INTO #temp
VALUES ('Value1',@test)
INSERT INTO #temp
VALUES ('Value2',@test)
GO
SELECT *
FROM #temp
ORDER BY registered DESC
GO
DROP TABLE #temp
GO

Now when the above script is ran, we will get the following result:

Well, the expectation of the query was to have the following result. The row which was inserted last was expected to return as first row in result set as the ORDER BY descending.

Side note: Because the requirement is to get the latest data, we can’t use any  column other than smalldatetime column in order by. If we use name column in the order by, we will get an incorrect result as it can be any name.

My Initial Reaction

My initial reaction was as follows:

1) DataType DateTime2: If file precision of the column is expected from the column which store date and time, it should not be smalldatetime. The precision of the column smalldatetime is One Minute (Read Here) for finer precision use DateTime or DateTime2 data type.

Here is the code which includes above suggestion:

CREATE TABLE #temp
(name VARCHAR(100), registered datetime2)
GO
DECLARE @test datetime2
SET @test=GETDATE()
INSERT INTO #temp
VALUES ('Value1',@test)
INSERT INTO #temp
VALUES ('Value2',@test)
GO
SELECT *
FROM #temp
ORDER BY registered DESC
GO
DROP TABLE #temp
GO

2) Tie Breaker Identity: There are always possibilities that two rows were inserted at the same time. In that case, you may need a tie breaker. If you have an increasing identity column, you can use that as a tie breaker as well.


CREATE TABLE #temp
(ID INT IDENTITY(1,1), name VARCHAR(100),registered datetime2)
GO
DECLARE @test datetime2
SET @test=GETDATE()
INSERT INTO #temp
VALUES ('Value1',@test)
INSERT INTO #temp
VALUES ('Value2',@test)
GO
SELECT *
FROM #temp
ORDER BY ID DESC
GO
DROP TABLE #temp
GO

Those two were the quick suggestions I provided. It is not necessary that you should use both advices. It is possible that one can use only DATETIME datatype or Identity column can have datatype of BIGINT or have another tie breaker.

An Alternate NO Solution

In the facebook thread this was also discussed as one of the solutions:

CREATE TABLE #temp
(name VARCHAR(100),registered smalldatetime)
GO
DECLARE @test smalldatetime
SET @test=GETDATE()
INSERT INTO #temp
VALUES ('Value1',@test)
INSERT INTO #temp
VALUES ('Value2',@test)
GO
SELECT name, registered,
ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number"
FROM #temp ORDER BY 3 DESC
GO
DROP TABLE #temp
GO

However, I believe it is not the solution and can be further misleading if used in a production server. Here is the example of why it is not a good solution:

CREATE TABLE #temp
(name VARCHAR(100) NOT NULL,registered smalldatetime)
GO
DECLARE @test smalldatetime
SET @test=GETDATE()
INSERT INTO #temp
VALUES ('Value1',@test)
INSERT INTO #temp
VALUES ('Value2',@test)
GO
-- Before Index
SELECT name, registered,
ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number"
FROM #temp ORDER BY 3 DESC
GO
-- Create Index
ALTER TABLE #temp
ADD CONSTRAINT [PK_#temp] PRIMARY KEY CLUSTERED
(name DESC)
GO
-- After Index
SELECT name, registered,
ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number"
FROM #temp ORDER BY 3 DESC
GO
DROP TABLE #temp
GO

Now let us examine the resultset. You will notice that an index which is created on the base table which is (indeed) schema change the table but can affect the resultset. As you can see, an index can change the resultset, so this method is not yet perfect to get the latest inserted resultset.

No Schema Change Requirement

After giving these two suggestions, I was waiting for the feedback of the asker. However, the requirement of the asker was there can’t be any schema change because the application was used by many other applications. I validated again, and of course, the requirement is no schema change at all. No addition of the column of change of datatypes of any other columns. There is no further help as well.

This is indeed an interesting question. I personally can’t think of any solution which I could provide him given the requirement of no schema change. Can you think of any other solution to this?

Need of Database Designer

This question once again brings up another ancient question:  “Do we need a database designer?” I often come across databases which are facing major performance problems or have redundant data. Normalization is often ignored when a database is built fast under a very tight deadline. Often I come across a database which has table with unnecessary columns and performance problems. While working as Developer Lead in my earlier jobs, I have seen developers adding columns to tables without anybody’s consent and retrieving them as SELECT *.  There is a lot to discuss on this subject in detail, but for now, let’s discuss the question first. Do you have any suggestions for the above question?

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Saturday Fun Puzzle with SQL Server DATETIME2 and CAST

Note: I have used SQL Server 2012 for this small fun experiment.

Here is what we are going to do. We will run the script one at time instead of running them all together and try to guess the answer. I am confident that many will get it correct but if you do not get correct, you learn something new.

Let us create database and sample table.

CREATE DATABASE DB2012
GO
USE DB2012
GO
CREATE TABLE TableDT
(DT1 VARCHAR(100), DT2 DATETIME2,
DT1C AS DT1, DT2C AS DT2);
INSERT INTO TableDT (DT1, DT2)
SELECT GETDATE(), GETDATE()
GO

There are four columns in the table. The first column DT1 is regular VARCHAR and second DT2 is DATETIME2. Both of the column are been populated with the same data as I have used the function GETDATE(). Now let us do the SELECT statement and get the result from both the columns.

Before running the query please guess the answer and write it down on the paper or notepad.

Question 1: Guess the resultset

SELECT DT1, DT2
FROM TableDT
GO

Now once again run the select statement on the same table but this time retrieve the computed columns only. Once again I suggest you write down the result on the notepad.

Question 2: Guess the resultset

SELECT DT1C, DT2C
FROM TableDT
GO

Now here is the best part. Let us use the CAST function over the computed columns. Here I do want you to stop and guess the answer for sure. If you have not done it so far, stop do it, believe me you will like it.

Question 3: Guess the resultset

SELECT CAST(DT1C AS DATETIME2) CDT1C,
CAST(DT2C AS DATETIME2) CDT1C
FROM TableDT
GO

Now let us inspect all the answers together and see how many of you got it correct.

Answer 1:

Answer 2:

Answer 3: 

If you have not tried to run the script so far, you can execute all the three of the above script together over here and see the result together.

SELECT CAST(DT1C AS DATETIME2) CDT1C,
CAST(DT2C AS DATETIME2) CDT1C
FROM TableDT
GO

Here is the Saturday Fun question to you – why do we get same result from both of the expressions in Question 3, where as in question 2 both the expression have different answer. I will publish the valid answer with explanation in future blog posts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Various Leap Year Logics

Earlier I wrote one article on Leap Year and created one video about Leap Year. My point of view was to demonstrate how we can use SQL Server 2012 features to identify Leap year. How ever during the conversation I had some really good conversation. Here are updates for those who have missed reading the excellent comments on the blog.

Incorrect Logic

There are so many people still think Leap Year is the event which is consistently happening at every four year and the way to find it is divide the year with 4 and if the remainder is 0. That year is leap year. Well, it is not correct.

Comment by David Bridge

Check out this excerpt from wikipedia page

http://en.wikipedia.org/wiki/Leap_year

“most years that are evenly divisible by 4 are leap years…”

“…Some exceptions to this rule are required since the duration of a solar year is slightly less than 365.25 days. Years that are evenly divisible by 100 are not leap years, unless they are also evenly divisible by 400, in which case they are leap years. For example, 1600 and 2000 were leap years, but 1700, 1800 and 1900 were not. Similarly, 2100, 2200, 2300, 2500, 2600, 2700, 2900 and 3000 will not be leap years, but 2400 and 2800 will be.”

If you use logic of divide by 4 and remainder is 0 to find leap year, you will may end up with inaccurate result. The correct way to identify the year is to figure out the days of February and if the count is 29, the year is for sure leap year.

Valid Alternate Solutions

Comment by sainswor99insworth

IIF((@Year%4=0 AND @Year%100 != 0) OR @Year%400=0, 1,0)

Comment by Madhivanan

Madhivanan has written a blog post about an year ago where he listed multiple ways to find leap year.

Comment by Jayan

DECLARE @year INT
SET
@year = 2012
IF (((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0))
PRINT ’1'
ELSE
print ’0'

Comment by David

DECLARE @Year INT = 2012
SELECT ISDATE('2/29/' + CAST(@Year AS CHAR(4)))

Comment by David Bridge

Incidentally – Another approach would be to take one day off March 1st and see if it is 29.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function

Note: Tomorrow is February 29th. This blog post is dedicated to coming tomorrow – a special day :)

Subu: “How can I find leap year in using SQL Server 2012?

Pinal: “Are you asking me how to year 2012 is leap year using T-SQL – search online and you will find many example of the same.”

Subu: “No. I am asking – How can I find leap year in using SQL Server 2012?

Pinal: “Oh so you are asking – How can I find leap year in using SQL Server 2012?

Subu: “Yeah - How can I find leap year in using SQL Server 2012?

Pinal: “Let me do that for you – How can you find leap year in using SQL Server 2012?

Indeed a fun conversation. Honestly, only reason I pasted our conversation here is – it was fun. What he was asking is that how to do it using new functions introduced in SQL Server 2012. Here is the article I have written which introduces all the new functions in SQL Server 2012 Summary of All the Analytic Functions – MSDN and SQLAuthority and 14 New Functions – A Quick Guide.

There are many functions written to figure out to figure out if any year is Leap Year or not. The same I have written using T-SQL function over here.

CREATE FUNCTION dbo.IsLeapYear (@year INT)
RETURNS INT
AS
BEGIN
RETURN
(IIF(DATEPART(dd,(EOMONTH(CONCAT(@year,'0201')))) = 29,1,0))
END
GO

What I really like is that I was able to use three newly introduced function in SQL Server 2012 in above script. You can read more about them here. IIF, EOMONTH and CONCAT.

You can validate above query by running following script.

SELECT dbo.IsLeapYear('2011') 'IsLeapYear';
SELECT dbo.IsLeapYear('2012') 'IsLeapYear';
GO

You will get result 1 if the year is leap year and 0 if year is not leap year.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – DATEDIFF – Accuracy of Various Dateparts

I recently received the following question through email and I found it very interesting so I want to share it with you.

“Hi Pinal,

In SQL statement below the time difference between two given dates is 3 sec, but when checked in terms of Min it says 1 Min (whereas the actual min is 0.05Min)

SELECT DATEDIFF(MI,'2011-10-14 02:18:58' , '2011-10-14 02:19:01') AS MIN_DIFF


Is this is a BUG in SQL Server ?”

Answer is NO.

It is not a bug; it is a feature that works like that. Let us understand that in a bit more detail. When you instruct SQL Server to find the time difference in minutes, it just looks at the minute section only and completely ignores hour, second, millisecond, etc. So in terms of difference in minutes, it is indeed 1.

The following will also clear how DATEDIFF works:

SELECT DATEDIFF(YEAR,'2011-12-31 23:59:59' , '2012-01-01 00:00:00') AS YEAR_DIFF

The difference between the above dates is just 1 second, but in terms of year difference it shows 1.

If you want to have accuracy in seconds, you need to use a different approach. In the first example, the accurate method is to find the number of seconds first and then divide it by 60 to convert it to minutes.

SELECT DATEDIFF(second,'2011-10-14 02:18:58' , '2011-10-14 02:19:01')/60.0 AS MIN_DIFF

Even though the concept is very simple it is always a good idea to refresh it. Please share your related experience with me through your comments.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Information Related to DATETIME and DATETIME2

I recently received interesting comment on the blog regarding workaround to overcome the precision issue while dealing with DATETIME and DATETIME2.

I have written over this subject earlier over here.

SQL SERVER – Difference Between GETDATE and SYSDATETIME

SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE

SQL SERVER – Difference Between DATETIME and DATETIME2

SQL Expert Jing Sheng Zhong has left following comment:

The issue you found in SQL server new datetime type is related time source function precision. Folks have found the root reason of the problem – when data time values are converted (implicit or explicit) between different data type, which would lose some precision, so the result cannot match each other as thought. Here I would like to gave a work around solution to solve the problem which the developers met.

-- Declare and loop
DECLARE @Intveral INT, @CurDate DATETIMEOFFSET;
CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2, GlobalDate DATETIMEOFFSET)
SET @Intveral = 10000
WHILE (@Intveral > 0)
BEGIN
----SET @CurDate = SYSDATETIMEOFFSET(); -- higher precision for future use only
SET @CurDate = TODATETIMEOFFSET(GETDATE(),DATEDIFF(N,GETUTCDATE(),GETDATE())); -- lower precision to match exited date process
INSERT #TimeTable (FirstDate, LastDate, GlobalDate)
VALUES (@CurDate, @CurDate, @CurDate)
SET @Intveral = @Intveral - 1
END
GO
-- Distinct Values
SELECT COUNT(DISTINCT FirstDate) D_DATETIME,
COUNT(DISTINCT LastDate) D_DATETIME2,
COUNT(DISTINCT GlobalDate) D_SYSGETDATE
FROM #TimeTable
GO
-- Join
SELECT DISTINCT a.FirstDate,b.LastDate, b.GlobalDate, CAST(b.GlobalDate AS DATETIME) GlobalDateASDateTime
FROM #TimeTable a
INNER JOIN #TimeTable b ON a.FirstDate = CAST(b.GlobalDate AS DATETIME)
GO
-- Select
SELECT *
FROM #TimeTable
GO
-- Clean up
DROP TABLE #TimeTable
GO

If you read my blog SQL SERVER – Difference Between DATETIME and DATETIME2 you will notice that I have achieved the same using GETDATE().

Are you using DATETIME2 in your production environment? If yes, I am interested to know the use case.

Reference: Pinal Dave (http://www.SQLAuthority.com)

SQL SERVER – Datetime Function TODATETIMEOFFSET Example

Earlier I wrote about SQL SERVER – Datetime Function SWITCHOFFSET Example. After reading this blog post, I got another quick reply that if I can explain the usage of TODATETIMEOFFSET as well.

Let us go over the definition of the TODATETIMEOFFSET  from BOL: Returns a datetimeoffset value that is translated from a datetime2 expression.

What essentially it does is that changes the current offset only  offset which we defined. Let us see the example of the same.

SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-04:00') 'GetCurrentOffSet-4';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-02:00') 'GetCurrentOffSet-2';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+00:00') 'GetCurrentOffSet+0';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+02:00') 'GetCurrentOffSet+2';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+04:00') 'GetCurrentOffSet+4';

Let us see the resultset below.

It is quite clear from the example that only offset changes in the time but unlike SWITCHOFFSET the datetime remains the same.

Reference : Pinal Dave (http://blog.SQLAuthority.com)