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Ā facebook,Ā twitterĀ 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:

SQL SERVER - SmallDateTime and Precision - A Continuous Confusion smalldt1

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.

SQL SERVER - SmallDateTime and Precision - A Continuous Confusion smalldt2

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

SQL SERVER - SmallDateTime and Precision - A Continuous Confusion smalldt3

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 (https://blog.sqlauthority.com)

SQL DateTime, SQL Scripts, SQL Training
Previous Post
SQL SERVER – Renaming Index – Index Naming Conventions
Next Post
SQL SERVER – Standard Reports from SQL Server Management Studio – SQL in Sixty Seconds #016 – Video

Related Posts

13 Comments. Leave new

  • if primary key is created at the time of creating table then result is again same.

    CREATE TABLE #temp
    (name VARCHAR(100) NOT NULL primary key,registered smalldatetime)
    GO

    Why ?

    Reply
  • Hi Pinal,

    Thanks for posting.. how abt below solution?

    create table #temp
    (
    name varchar(100),
    registered smalldatetime
    )

    declare @test smalldatetime
    set @test=getdate()

    insert into #temp
    values (‘Raghav1’,@test)

    insert into #temp
    values (‘Raghav2’,@test)

    Select IDENTITY(int,1,1) AS [Row Number]
    , name,registered
    INTO #Temp2
    from #temp
    — [edited]

    SELECT *
    FROM #Temp2 order by registered desc,[Row Number] Desc

    Regards,
    Raghavendra SK

    Reply
  • Amit Chauhan
    May 22, 2012 11:22 am

    SmallDatetime converts seconds value to its nearest Minute value. As a Result seconds value is always 0. Without changing the schema, i don’t think this will be shown as required output.

    Reply
  • Yesterday Raghavendra called me and asked this question. My initial thought was also to make schema changes but the requirement is without making schema changes how can you do this. He was telling the database is in SQL Server 2000. I couldn’t find any solution without making any schema changes.

    Reply
  • Mike Michalicek
    May 22, 2012 6:28 pm

    Nice post and tricky. below is the only solution I could come up with.

    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 (‘Value0’,@test)
    INSERT INTO #temp
    VALUES (‘Value2’,@test)
    GO
    ;With CTE AS (
    Select name, registered, ROW_NUMBER() OVER(ORDER BY registered DESC) AS RowNumber
    From #temp)
    SELECT name, registered
    FROM CTE a
    ORDER BY RowNumber Desc
    GO
    DROP TABLE #temp
    GO

    Reply
  • SQL Addictor
    May 31, 2012 6:59 pm

    I figured one thing that the ordering of a field having same value, gets the results of the order the first column having diff valeu

    Reply
  • Can anyone help query optimizing techniques pdf with examples…..
    Thanks

    Reply
  • Hi pinal,

    İ cant see pics when i use my smartphone.

    Reply
  • hi Pinal ,

    I am btech 2011 passout. I am a fresher and wanted to have a great command in sql server . I know the basic commands. But could you please suggest me that from where i should start…

    Reply
  • NO — you are ALL wrong.

    The @test only gets evaluated ONCE. And thus the date / time is ALWAYS the same.

    SmallDateTime may not have large precision, but that is NOT the issue you are seeing.

    Reply

Leave a Reply