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

SQLAuthority News – SQL Server 2012 Upgrade Technical Guide – A Comprehensive Whitepaper – (454 pages – 9 MB)

Microsoft has just released SQL Server 2012 Upgrade Technical Guide. This guide is very comprehensive and covers the subject of upgrade in-depth. This is indeed a helpful detailed white paper. Even writing a summary of this white paper would take over 100 pages. This further proves that SQL Server 2012 is quite an important release from Microsoft. This white paper discusses how to upgrade from SQL Server 2008/R2 to SQL Server 2012. I love how it starts with the most interesting and basic discussion of upgrade strategies: 1) In-place upgrades, 2) Side by side upgrade, 3) One-server, and 4) Two-server. This whitepaper is not just pure theory but is also an excellent source for some tips and tricks. Here is an example of a good tip from the paper:

“If you want to upgrade just one database from a legacy instance of SQL Server and not upgrade the other databases on the server, use the side-by-side upgrade method instead of the in-place method.”

There are so many trivia, tips and tricks that make creating the list seems humanly impossible given a short period of time. My friend Vinod Kumar, an SQL Server expert, wrote a very interesting article on SQL Server 2012 Upgrade before. In that article, Vinod addressed the most interesting and practical questions related to upgrades. He started with the fundamentals of how to start backup before upgrade and ended with fail-safe strategies after the upgrade is over. He covered end-to-end concepts in his blog posts in simple words in extremely precise statements.

A successful upgrade uses a cycle of: planning, document process, testing, refine process, testing, planning upgrade window, execution, verifying of upgrade and opening for business. If you are at Vinod’s blog post, I suggest you go all the way down and collect the gold mine of most important links. I have bookmarked the blog by blogging about it and I suggest that you bookmark it as well with the way you prefer.

Vinod Kumar’s blog post on SQL Server 2012 Upgrade Technical Guide

SQL Server 2012 Upgrade Technical Guide is a detailed resource that’s also available online for free. Each chapter was carefully crafted and explained in detail. Here is a quick list of the chapters included in the whitepaper. Before downloading the guide, beware of its size of 9 MB and 454 pages.

Here’s the list of chapters:

Chapter 1: Upgrade Planning and Deployment
Chapter 2: Management Tools
Chapter 3: Relational Databases
Chapter 4: High Availability
Chapter 5: Database Security
Chapter 6: Full-Text Search
Chapter 7: Service Broker
Chapter 8: SQL Server Express
Chapter 9: SQL Server Data Tools
Chapter 10: Transact-SQL Queries
Chapter 11: Spatial Data
Chapter 12: XML and XQuery
Chapter 13: CLR
Chapter 14: SQL Server Management Objects
Chapter 15: Business Intelligence Tools
Chapter 16: Analysis Services
Chapter 17: Integration Services
Chapter 18: Reporting Services
Chapter 19: Data Mining
Chapter 20: Other Microsoft Applications and Platforms
Appendix 1: Version and Edition Upgrade Paths
Appendix 2: SQL Server 2012: Upgrade Planning Checklist

Download SQL Server 2012 Upgrade Technical Guide [454 pages and 9 MB]

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

SQL SERVER – A Brief Introduction to expressor Studio 3.6

Data is powerful. Data drives businesses. Data supports decision making and fuels progress. But managing data—making data work for us—isn’t inherently easy. And if there is one thing that is certain, it’s change—meaning that data systems created yesterday will need to be adapted to fit ever evolving needs. Since we can’t design our databases, data warehouses, and BI systems with every possible contingency in mind, we need data integration software that is smart enough to simplify the process and allow us to build more flexible solutions that can adapt to change and can let us focus on the value of our data—and not on the complexity of managing it.

I encourage you to watch this brief YouTube video to learn how easy it is to create your first data integration application with expressor Studio.

Next action is to download and test-drive expressor Studio if you haven’t done so.

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

SQLAuthority News – Select the Best SQL in Sixty Seconds Episode – Help us Improve

It has been more than 3 months since we have started experimenting with a new concept in  SQL in Sixty Seconds. Every Wednesday, we putt a fresh new interesting concept out via video. Rick MorelanVinod Kumar and myself – the three of us decided to do something new and something exciting. We decided to create a short video which will consider the attention span of the viewer, keep them focused and help them learn something new through our teaching.

We all liked the idea of SQL in Sixty Seconds. As the name suggests, you will not watch content for more than a minute. We did our best to keep the video down to single minute, well, in most of the cases. There are cases when we could not accommodate the content in sixty seconds so the video had to roll for over few more seconds. In the end, we try to come up with an extremely short video which is able to teach some learning on SQL Server. So far we have 10 videos out for the SQL in Sixty Seconds series.

SQL in Sixty Seconds

Here are the details to our SQL in Sixty Seconds series. It has a total of 10 videos and a total cumulative time of around 15 minutes for all the videos.

Link to watch all the Episodes

Episode 01 – Subquery to CTE
Episode 02 – T-SQL Errors and Reactions
Episode 03 – T-SQL Constructs – Declaration and Initialization
Episode 04 – Function: Is Leap Year
Episode 05 – T-SQL Errors and Reactions Demo
Episode 06 – SQL Server Performance: Indexing Basics
Episode 07 – Table Variables and Transactions
Episode 08 – Using MAXDOP 1 for Single Processor Query
Episode 09 – T-SQL Constructs – += and *=
Episode 10 – Installing AdventureWorks Sample Database

We need your help

We seriously need your help by means of rating our videos. All you have to do is to answer these three simple questions:

1. Which is your most favorite SQL in Sixty Seconds Episode? and Why?
2. Which is your least favorite SQL in Sixty Seconds Episode? and Why?
3. What next you would like to see in SQL in Sixty Seconds?

Just leave your answers in comment area.

Selection of Winner

On May 1st, we will go over the complete list. Rick, Vinod and I have also picked our most favorite episode and least favorite episode. You may call it a co-incidence or an accident, but our choice was exactly the same. Yes, three of us have selected the same video for best and least favorite videos. We will select the winner who has exactly the same selection as our selection. In case of a tie, we will look at the third question and as well as your answers to the “Why” follow-up question asked in the first and second questions.

Prizes

We have decided to have more than one prize. This time, we’re giving away three prizes for the winners:

If you stay in a country where we cannot ship these books, we will send you the appropriate Pluralsight FREE learning code.

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

SQL SERVER – Cheatsheet – Released for SQL Server 2012 Edition

SQL Server Cheatsheet has been extremely popular download from my blog. There are plenty of request for me to update it with SQL Server 2012 features. I have finally upgraded the cheatsheet with SQL Server 2012 features. The new cheatsheet has following updates

  • SSMS Shortcuts
  • Columnstore Index
  • SQL Server 2012 Datetime Functions
  • SQL Server Ranking Functions
  • SQL Server 2012 Analytic functions
It has been excellent experience to upgrade the cheatsheet. However, as the new cheatsheet contains way more information than earlier version of the cheatsheet, the size of the page is also increased. The page size is now no more A4 size, it has been increased to A3 size. When printed on A3 page it looks very clean and easy to read. If you print that on A4, you will find the pages are bit small.
Cheatsheet is free for anybody and everybody to download and print it, as long as you do not modify it. However, it is now allowed to store, host or upload anywhere without prior permission. 
Previous cheatsheet was very popular and I have received many good comments. I am eagerly waiting for your feedback about this new updated cheatsheet.

Download SQL Server Cheatsheet

I have printed limited copies of the cheatsheet on photo quality laminated A3 size page. This looks AWESOME!

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

SQLAuthority News – Social Media Series – LinkedIn and Professional Profile

Pinal Dave on LinkedIn!

It seems like a few year ago, there was a big “boom” in social media websites.  All of a sudden there were so many sites to choose from.  MySpace or Orkut?  Blogging websites for your business or a LinkedIn account?  The nature of the internet is to always be changing, but I believe that out of this huge growth of websites, a few have come to stay.  Facebook is obviously the leader in social media networking, especially for your personal life.  Blogging is great, but it can be more of a way to get your ideas out there, rather than a place for people to connect to you professionally.  If you want to have a professional “face” on the internet, LinkedIn is the way to go.

LinkedIn is best explained as “professional Facebook.”  This is simplifying things a little bit too much, but it is certainly a website where you link up with professional contacts, so that others can see where you have worked, who you have worked with, and what projects you have done.  This is a much better place for professional contacts to find you than someplace like Facebook, where all they will see is your face and maybe picture of you at a birthday party or something like that!

Because so much of my SQL Server life is conducted on the internet, especially on my blog, I felt that it would be a good idea to have a well-maintained LinkedIn web page as well, so that if anyone is curious about me and my credentials they can quickly and easily find me and see that I am for real, and not someone pretending to know a lot about SQL Server.

My linked in profile is www.linkedin.com/in/pinaldave.  I keep all my professional information here, and I update it as often as possible.  Feel free to come find me, especially if you would like to “link up” and share professional information.  The technology world is becoming more and more interconnected, and more and more international.  I feel that it is very important to stay linked up virtually, because so many of us are so far apart physically.

I try to keep very connected with my LinkedIn profile.  I let anyone connect with me, and I read updates from the professional world very often.  I keep this profile updated, but do not post things about my personal life or anything that I might put on Twitter, for example.  I also include my e-mail address here, if you would like to contact me professionally.  This is the best place for me to conduct business.

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

SQLAuthority News – Social Media Series – YouTube and Movies

Pinal Dave on Youtube!

Some people might not know it, but YouTube is actually more than a place to watch funny cat videos and people singing their favorite pop songs – it’s actually a social media site.  When you are a member of YouTube you can follow people who regularly post videos, post video responses of your own, and even gain a following for your own videos.  I myself was not aware of YouTube’s potential until recently, when I started to make SQL Server in Sixty Seconds videos.

YouTube is very different than other types of social media, and a big factor is that anyone can look at videos without being a member.  Unlike other social media sites, like Twitter and Facebook, you have to have an account in order to participate.  But on YouTube you are even more anonymous.  To make and post videos you need an account, but anyone who comes to the site can look at what you’ve made without signing in or leaving any trace of having seen your material.  This makes YouTube very anonymous and hard to track.

However, we should not overlook the power of video on the internet.  Over the past few months I have been making SQL Server in Sixty Second videos and have come to love it.  It is very exciting to be able to talk about a subject that mostly I write about, and for many people video is far more accessible and easy to understand.   I have really enjoyed diving into something new, and would love to have more people check out these videos and give me feedback.  You can find me at www.youtube.com/user/pinaldave.

I am very excited with all the possibilities on YouTube and it might just be the technology evangelist in me, but I would love for other people to discover how fun and exciting this site can be, too.  Don’t think of it as just a place to find funny videos and waste a few minutes of your time, think of it as a place to learn and interact with interesting people.  Come watch a few of my videos, while you’re there.  Remember, everything is free and there are no contracts to sign, but I hope that you get as excited as I am and join up.  We need more people creating good content on this site!

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