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 – Developer Training Kit for SQL Server 2012

Developer Training Kit is my favorite part of any product. The reason behind is very simple because it give the single resource which gives complete overview of the product in nutshell. A developer can learn from many places – books, webcasts, tutorials, blogs, etc. However, I have found that developer training kits are the best starting point for any product. Start with them first, see what are the new features as well what is the new message a product is coming up with. Once it is learned the very next step should be to identify the right learning material to explore the preferred topic.

The SQL Server 2012 Developer Training Kit includes technical content including labs, demos and presentations designed to help you learn how to develop SQL Server 2012 database and BI solutions. New and updated content will be released periodically and can be downloaded on-demand using the Web Installer.

Download SQL Server 2012 Developer Training Kit Web Installer.

This training kit was available earlier this year but it is never late to explore it if you have not referred it earlier. Additionally, if you do not want to download complete kit all together I suggest you refer to Wiki here. This wiki contains all the same presentations and demo notes which web installer contains.

Refer to SQL Server 2012 Developer Training Kit Wiki

Wiki contains following module and details about Hands On Labs
Module 1: Introduction to SQL Server 2012
Module 2: Introduction to SQL Server 2012 AlwaysOn
Module 3: Exploring and Managing SQL Server 2012 Database Engine Improvements
Module 4: SQL Server 2012 Database Server Programmability
Module 5: SQL Server 2012 Application Development
Module 6: SQL Server 2012 Enterprise Information Management
Module 7: SQL Server 2012 Business Intelligence
Hands-On Labs: SQL Server 2012 Database Engine
Hands-On Labs: Visual Studio 2010 and .NET 4.0
Hands-On Labs: SQL Server 2012 Enterprise Information Management
Hands-On Labs: SQL Server 2012 Business Intelligence
Hands-On LabsHands-On Labs: Windows Azure and SQL Azure

As I said, if you have not downloaded this so far, it is never late to explore it. Trust me you will atleast learn one thing if you just explore the content.

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

SQLAuthority News – SQL Server 2012 – Microsoft Learning Training and Certification

Here is the conversion I had right after I had posted my earlier blog post about Download Microsoft SQL Server 2012 RTM Now.

Rajesh: So SQL Server is available for me to download?
Pinal: Yes, sure check the link here.
Rajesh: It is trial do you know when it will be available for everybody?
Pinal: I think you mean General Availability (GA) which is on April 1st, 2012.
Rajesh: I want to have head start with SQL Server 2012 examination and I want to know every single

Exam 70-461: Querying Microsoft SQL Server 2012

This exam is intended for SQL Server database administrators, implementers, system engineers, and developers with two or more years of experience who are seeking to prove their skills and knowledge in writing queries.

Exam 70-462: Administering Microsoft SQL Server 2012 Databases

This exam is intended for Database Professionals who perform installation, maintenance, and configuration tasks as their primary areas of responsibility. They will often set up database systems and are responsible for making sure those systems operate efficiently.

Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012

The primary audience for this exam is Extract Transform Load (ETL) and Data Warehouse Developers.  They are most likely to focus on hands-on work creating business intelligence (BI) solutions including data cleansing, ETL, and Data Warehouse implementation.

Exam 70-464: Developing Microsoft SQL Server 2012 Databases

This exam is intended for database professionals who build and implement databases across an organization while ensuring high levels of data availability. They perform tasks including creating database files, creating data types and tables,  planning, creating, and optimizing indexes, implementing data integrity, implementing views, stored procedures, and functions, and managing transactions and locks.

Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012

This exam is intended for database professionals who design and build database solutions in an organization.  They are responsible for the creation of plans and designs for database structure, storage, objects, and servers.

Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012

The primary audience for this exam is BI Developers.  They are most likely to focus on hands-on work creating the BI solution including implementing multi-dimensional data models, implementing and maintaining OLAP cubes, and creating information displays used in business decision making

Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

The primary audience for this exam is the BI Architect.  BI Architects are responsible for the overall design of the BI infrastructure, including how it relates to other data systems in use.

Looking at Rajesh’s passion, I am motivated too! I may want to start attempting the exams in near future.

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

SQL SERVER – Free Download – SQL Server 2008 R2 Update for Developers Training Kit

SQL Server 2008 R2 is released and have been a stable product since the day it is released. I have not received any complains or rants from any of my customers who has upgraded to this version. The number one request is how one can learn about the new features of SQL Server or how one can get going in using SQL Server 2008. Microsoft has released SQL Server 2008 R2 Developers Training Kit. This is awesome kit and I just suggest to have a look at the content one time.

Here is what MS say for this kit:

SQL Server 2008 R2 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2008. The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

  • Learn how to build applications that exploit the unique features and capabilities of SQL Server 2008 and SQL Server 2008 R2.
  • Provides a comprehensive set of videos, presentations, demos and hands-on labs
  • Contains new content for developers who are new to SQL Server.
  • Contains new content for SQL Server 2008 R2.
  • Contains all of the existing content from the SQL Server 2008 Developer Training Kit.
  • Easy to download and install.

I suggest that you look at the content and use this free kit to upgrade yourself to SQL Server 2008 R2.

Download SQL Server 2008 R2 Update for Developers Training Kit

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

SQLAuthority News – SQL Server 2008 R2 Update for Developers Training Kit (March 2010 Update)

Note: Download SQL Server 2008 R2 Update for Developers Training Kit (March 2010 Update) Authored by Microsoft

SQL Server 2008 R2 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2008. The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

The training kit is designed for the following technical roles:

  • Developers who build applications for the Microsoft platform.
  • Microsoft evangelists, technical specialists and consultants.

Download SQL Server 2008 R2 Update for Developers Training Kit (March 2010 Update)

Abstract courtesy Microsoft

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

SQL SERVER – SQL Server 2008 R2 Update for Developers Training Kit

Note:   Download SQL Server 2008 R2 Update for Developers Training Kit by Microsoft

SQL Server 2008 R2 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2008. The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

Download SQL Server 2008 R2 Update for Developers Training Kit

Abstract courtesy : Microsoft

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

SQLAuthority News – Download – SQL Server 2008 Developer Training Kit

Note :   Download SQL Server 2008 Developer Training Kit by Microsoft

SQL Server 2008 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2005. The  SQL Server 2008 Developer Training Kit  will help you understand how to build web applications which deeply exploit the rich data types, programming models and new development paradigms in SQL Server 2008. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

The training kit includes the following content:

  • Presentations (6)
    • Filestream
    • Spatial
    • T-SQL
    • Date and Time Types
    • SQLCLR
    • Reporting Services
  • Demos (12)
    • AdventureWorks Racing All-Up SQL Server 2008 Demo
    • SQL Server 2008 All-Up Spatial Demo
    • Spatial Types Demo
    • Intro to Filestream Demo
    • SQL CLR Nullable Types Demo
    • Programming with Filestream Demo
    • Reporting Services Web Application Integration Demo
    • Date and Time Support in SQL Server 2008 Demo
    • T-SQL Table-Valued Parameters Demo
    • T-SQL Row Constructors Demo
    • T-SQL Grouping Sets Demo
    • T-SQL Merge Demo
  • Hands-on Labs (3)
    • Using Spatial Data in TSQL
    • Using Spatial Data in Managed Code
    • Using SQL CLR in SQL Server 2008

Download SQL Server 2008 Developer Training Kit

Abstract courtesy : Microsoft

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