SQL SERVER – Puzzle with Miliseconds – Win USD 50 Amazon Gift Card

Last contest, which we had ran was in May and it had received amazing responses. I once again reached out to kind team of Embarcadero and they agreed to support the next contest. The contest has two steps and they are very simple. It took me a while to build contest, but it is a really fun one. I am very confident that once you try out the contest, you will love it.

Two Giveaways:

amazon gift cards SQL SERVER   Puzzle with Miliseconds   Win USD 50 Amazon Gift Card

(USA) USD 50 Amazon Gift Card to 1 Individual

(India) INR 2500 Amazon.in Gift Card to 1 Individual

(Rest of the world) USD 50 Amazon Gift Card to 1 Individual

Contest 1: T-SQL

Run following T-SQL script and observe the answer:

CAST(‘2015-01-01 14:48:34.69’ AS DATETIME) FirstVal,
CAST(‘2015-01-01 14:48:34:69’ AS DATETIME) SecondVal

When we look at the answer, there is a difference between the milliseconds part in the resutlset, whereas you can notice that in the SELECT statement I have specified different milliseconds part. The question is why there is a difference in the millisecond part even though I have same value selected?

milisecond SQL SERVER   Puzzle with Miliseconds   Win USD 50 Amazon Gift Card

Contest 2: Download and Install DBArtisan

This is the easy part of the contest. You just have to download and install DBArtisan. You can download DBArtisan from here.

How to Participate?

  • Leave an answer for contest 1 in the comment section of the blog.
  • Leave a comment with the same email address which you have used to download DBArtisan.
  • The contest is open till December 1st, 2015 Midnight PST.
  • The winner will be announced on December 10th, 2015.
  • There will be three winners 1) USA 2) India 3) Rest of the World
  • Please do not forget to indicate which country do you belong to qualify for the contest.
  • All the correct answer to the contest will be hidden till the winner is announced.

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

SQL SERVER – Puzzle – Inside Working of Datatype smalldatetime

In the recent past, I have been bringing few puzzles around using date and time functions. All these have brought some awesome feedbacks and you all have been sharing some awesome answers. Today’s question comes from an unusual place wherein one of a developer said he was getting unexpected results with working with datetime. So here is how the conversation went:

Dev: Hi Pinal.

Pinal: Yes, buddy. How can I be of help?

Dev: I have a problem working with datetime datatype.

Pinal: Oh is it? Tell me more about it.

Dev: I am trying to CAST a string coming from a front end app to SQL Server.

Pinal: Ok, where is the problem?

Dev: Every now and then, it gives different results than what I try to send via the wire.

Pinal: I am not able to understand. Can you send me an example to understand this better?

Dev: Sure, I will send via email.

After I received the actual code and email, I resumed the conversation.

Pinal: Hey.

Dev: Yes, Sir. Did you find the reason?

Pinal: I found the reason and I am going to blog about it tomorrow. It is a simplified version of the query you sent me. So you are about to find the answers there. The hint is: you are doing a convert to smalldatetime

Dev: Well, thanks. I think I will do my homework and try to answer it in your blog too.

This concept was interesting and I planned to share this experience with you folks like a small puzzle. So what are we talking?

Look at the below query and guess what would be the output:

CAST('2015-01-01 12:45:29.755' AS SMALLDATETIME),
CAST('2015-01-01 12:45:35.755' AS SMALLDATETIME)

This is a simple conversion but the two values are different. Why is that? Do you know the reason? Use the comments section to explain this. I may send surprise gift to 2 of the winners.

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


The technique used in this blog is explained in earlier blog SQL SERVER – Creating Dataset Using VALUES Clause Without Creating Table. To read more about Collation and Unicode refer to MSDN. This topic in general is very deep and complicated sometimes that you get to learn something every single time.

While playing with SSIS package, I was getting unique constraint error while data were getting inserted in SQL Server. Later I did further digging and found that it was due to German characters. Here is a simplified version of the problem. Will you be able to crack the puzzle?

You can use the SQL Server Management studio and run below three queries:

columny COLLATE SQL_Latin1_General_CP1_CI_AS AS 'First'
FROM (VALUES ('ß'),('SS') ) tablex(columny) ;
SELECT DISTINCT columny COLLATE Latin1_General_CI_AS AS 'Second'
FROM (VALUES ('ß'),('SS') ) tablex(columny) ;
FROM (VALUES (N'ß'),(N'SS') ) tablex(columny) ;

If you are not having access to SQL Server handy – here is a sample output for them:

ss puzzle 01 SQL SERVER   Puzzle   DISTINCT Values in NVARCHAR

Do you know the reason why ‘ß’ and ‘SS’ are same in nvarchar? Please comment and let me know. I will share surprise learning voucher with you.

Hope we will learn something together.

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

SQL SERVER – Process ID X was killed by hostname ABC, host process ID Y

Errorlogs are an awesome place to learn something new every time. It springs up with some new errors which you might have not seen ever. I am a big believer of the fact that one needs to monitor and take a look at the logs from time to time. It is the best way to mitigate problems before they occur. Recently I noticed the below error in SQL Server ERRORLOG. I wanted to know the exact meaning of each and every placeholder in error message. Since I found it, I am sharing with you.

Error number: 18100

SELECT TEXT, severity, is_event_logged
FROM  sys.messages
WHERE message_id = 18100
AND   language_id = 1033

kill 01 SQL SERVER   Process ID X was killed by hostname ABC, host process ID Y

Process ID %d was killed by hostname %.*ls, host process ID %d.
As per my understanding %d stands for number and %ls stands for string.

Whenever a KILL command is issued in SQL Server, it is logged into SQL Server Errorlog.

  1. First placeholder would be the SPID which was KILL’ed.
  2. Second placeholder is the machine name from where the KILL command was issued. Note that host name can be obfuscated and not guaranteed to be exact name of the machine. While making connection, we can pass our own host name.
  3. Third placeholder is the client Process ID which has issued the kill command. This can be seen via Task Manager.

If we look at Errorlog, we can also find the SPID which has issued the kill command.

2015-07-14 05:00:00.290 spid52       Process ID 51 was killed by hostname BIGPINAL, host process ID 10044.
Killed by : SPID52
Killed : 51
Host Name – BIGPINAL
PID in Task Manager – 10044

kill 02 SQL SERVER   Process ID X was killed by hostname ABC, host process ID Y

I have seen few DBA putting schedule job to monitor blocking and kill it. In that case you would see Process ID from SQL Agent. One of the interesting piece which I was not able to answer was the question asked by a blog reader on http://blog.sqlauthority.com/2007/06/26/sql-server-explanation-and-example-four-part-name/

I am getting messages in errorlog as Process ID 400 was killed by hostname , host process ID 0.
there is no hostname , and process ID 0, when i checked for process id in task manager , 0 is for system idle process.

This made me think and I spoke to few local SQL Server Experts and they asked me the exact message in ERRORLOG and I got below as reply

2015-07-01 01:22:45.600 spid21s      Process ID 51 was killed by hostname , host process ID 0.

This means that system SPID 21s has killed the process 51 and since this is a system SPID, we would not see host name and host process.

kill 03 SQL SERVER   Process ID X was killed by hostname ABC, host process ID Y

Can you think of a way to reproduce this behavior and get similar message in SQL ERRORLOG where hostname is empty and host process ID is zero? If you can write the same over comments, it would be of great help for others too.

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

SQL SERVER – Puzzle – Working with functions to Concatenate columns

Every now and then I get pulled into coding review sessions and those are some of the fun times I every have working with T-SQL of any sorts. This blog is inspired by one such time where I saw at a customer location a code that was lengthy and in one location I was seeing close to 15 fields which were concatenated to form a single column as part of output.

I generally don’t get into the reasoning of it, but what was interesting is they were using ISNULL in front of every column because they were not sure how whole column values to be returned as NULL. To give you an idea, here is a mini-repro to the same problem.

emp_name NVARCHAR(200) NOT NULL,
emp_middlename NVARCHAR(200) NULL,
emp_lastname NVARCHAR(200) NOT NULL
INSERT INTO #temp VALUES( 'SQL', NULL, 'Authority' );
INSERT INTO #temp VALUES( 'SQL', 'Server', 'Authority' );
SELECT emp_name + emp_middlename + emp_lastname AS Result
FROM #temp;

Now, you can see the output by default will look like:

concatenate quiz 01 SQL SERVER   Puzzle   Working with functions to Concatenate columns

As you can see, the problem is getting the first value as NULL. Customers sometimes don’t want this behavior.

Question for the Day

What would you do in this situation? Would you add an ISNULL in front of every column because you are not sure which of these might be NULL? Can you think of a solution without using the ISNULL function?

Give me your solution over comments. It is very easy – trust me. In my example the customer was also trying to use various Datatypes concatenating into a single column.

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

SQL SERVER – Puzzle with Year Function

Puzzle pieces SQL SERVER   Puzzle with Year FunctionA couple of weeks back, I ran a contest with MONTH () function and had close to 300 of you answer the puzzle. It was a great brain teaser and we had an awesome response from you. Most of you got the answer right and it is great to see you folks getting the basics right.

When I was on that step, I was wondering to run this second contest. Sometimes our basics can get tricked if we add a twist to the whole setup. Below are two sets of Queries and I would want you to guess what is the output and why?

Query 1:

What is the output for the below query? And why?

DECLARE @dt DATE = '0001-1-1'

Part of the answer is in the earlier puzzle I mentioned starting this blog. But what would the year value show? Take a guess and let me know.

Query 2:

I have made a small change in the default value for the second query. Now can you guess what the output from this query is?

DECLARE @dt DATE = '1-1-1'

As part of the hint I can tell you the MONTH function returns the same value. But there is something different for the YEAR function.

So here is the quiz – Why are the values different? What is the reason behind this?

Please leave correct answer in comment below the blog.

I will announce winner of this contest in 48 hours. Two people who give the right answer, I will share a learning resource worth USD 29 on June 11 via Email.

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

SQL SERVER – Trivia – Days in a Year

Every time I wrote a blog, I tend to get back to most of them via the search. Most of you ask me questions and I do few simple search back to make sure I am able to address them. Yesterday as I was searching for an interesting question, Now back to the question that got me there, one of a friend said he was asked in an interview on how to efficiently find the number of days in a given year.

The question was interesting but what baffled me was – do people still ask such questions? It was a challenge that I wanted to share with you. I wanted to take the challenge and immediately got to my SQL Server machine to solve. The instinctive behavior was to use some basic methods that involved DATEADD and DATEPART options.

The first solution that I got was:

--This method will work on SQL server 2005 and above.
SELECT DATEPART(dy,(DATEADD(YEAR,@year-1899,0)-1))

Immediately I sent the above option. Then he quizzed me asking, can you use some of the New Date time functions that were introduced with SQL Server 2012. Now, I was not prepared for the googly that came my way. But after getting my heads around it, I came up with the below query.

--This technique will work in SQL Server 2012 and above.

Woot !!! That was not simple as I had to search my blog for ideas.

Quiz: Can you come up with some solution which will have lesser number of functions involved? Can you use some of the new SQL Server 2012 Date functions in your solution? Let me know via comments.

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

SQL SERVER – Quiz with DATEADD Function

How many of us use some sort of date or datetime function when working with SQL Server? I cannot think of a single developer who would ever say they don’t work with datetime conversions in their application. Almost every application needs some manipulation of datetime datatypes. There are a number of pitfalls that can get into which we might not be aware. Here is a simple blog that I would love to hear your views on what the output would be:

Quick Trivia

Make your guess to what is the value for the below query? Guess before executing the same on SQL Server Management Studio.

SELECT DATEADD(MONTH, 1, '2015-01-28')
SELECT DATEADD(MONTH, 1, '2015-01-29')
SELECT DATEADD(MONTH, 1, '2015-01-30')
SELECT DATEADD(MONTH, 1, '2015-01-31')

Do you see something strange in the output? Will you be able to explain why we are getting this output? Why are the values so similar? I am sure once you execute the query in SSMS – the answer will be easy.

If the output is still confusing, hang on. Now what would be the values for the below query?

SELECT DATEADD(MONTH, 1, '2016-01-28')
SELECT DATEADD(MONTH, 1, '2016-01-29')
SELECT DATEADD(MONTH, -1, '2015-03-30')
SELECT DATEADD(MONTH, -1, '2015-03-31')

It is important to note is that DATEADD can either use a positive or negative integer as part of adding value. I am sure, I did trick you with the above query. Now the explanation becomes easy. Write your explanation in the comments and I will try to give you a special prize of one month free subscription to Pluralsight for five correct valid comments to this question.

Part 2

As I wrap up this blog, I would like to show how one of my friends made a small mistake while using the DATEADD function and how they got unexpected results. While using DATEADD, they accidentally wrote a YEAR function instead of “month”.

SELECT DATEADD(YEAR, 8000, '2015-01-31');

If you execute the above query, we will be presented with an Error. This is part II of the trivia. This will be an added bonus if you can explain.

Msg 517, Level 16, State 1, Line 9
Adding a value to a ‘datetime’ column caused an overflow.

Call to Action

1) Write the answer to first quiz – Why specific outcome when you execute queries with DateAdd and month?

2) Write the answer to second quiz – Why specific error on the screen?

Leave answer before February 20th, 2015 in comment section to eligible for price of free Pluralsight subscription.

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

SQL SERVER – Identify and Filter In-Memory Optimized Tables – SQL in Sixty Seconds #079

Earlier I had written a blog about SQL SERVER – Beginning In-Memory OLTP with Sample Example which covers the basics of working with In-Memory OLTP. Though that post gets you started, one of my colleague asked me if there was an easier way to identify In-Memory Tables when working with SQL Server Management Studio. As a follow up I wrote another blog post over here where I demonstrate the same with images and query over here: SQL SERVER – Filter In-Memory OLTP Tables in SSMS.

I have converted the same blog post in SQL in Sixty Seconds video over here.

Let me know your opinion about it.

Here are few other blog posts related to this concept, which I have written earlier.

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

SQL SERVER – Schema Change Reports – SQL in Sixty Seconds #078

Earlier, I wrote a blog post about Schema Change Reports and Finding Tables Created Last Week – DBA Tip. I received quite a few emails about the same. It seems like a quite a popular topic. As per few requests I have created a very short 60 second video on this subject.

Let me know your opinion about it.

Here are few other blog posts related to this concepts, which I have written earlier.

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