# SQL SERVER- Solution – SQL Puzzle of SET ANSI_NULL

Earlier I have posted a puzzle which received so many valid responses and got a fantastic explanation to the questions as well. I encourage all of you to read the original puzzle here.

First run following script:

```SET ANSI_NULLS ON; -- Query1 SELECT 'SQLAuthority' AS Statement11 WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL); -- Query 2 SELECT 'SQLAuthority' AS Statement12 WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);```

You will get following result:

You can clearly see that in the first case we are getting different results.

Question: Why do Query 1 return results but Query 2 does not return any result?

The answer is very simple and easy. Let us see the answer

Solution:

In case of the Query1
SELECT ‘SQLAuthority’ AS Statement11
WHERE ‘Authority’ IN (‘S’,’Q’, ‘L’, ‘Authority’, NULL);

Explanation:
IN Operator is equivalent to
Condition = ‘S’ OR Condition = ‘Q’ OR Condition = ‘L’ OR Condition = ‘Authority’ OR Condition = NULL

Above query represents in our case as

‘Authority’ = ‘S’ OR ‘Authority’ = ‘Q’ OR ‘Authority’ = ‘L’ OR ‘Authority’ = ‘Authority’ OR ‘Authority’ = NULL

Returns FALSE OR FALSE OR FALSE OR TRUE OR FALSE so the final result is TRUE.

In this case if ANY of the above condition returns true it the query will return value ‘SQLAuthority’

In case of the Query2
SELECT ‘SQLAuthority’ AS Statement12
WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL);

Explanation:
NOT IN Operator is equivalent to
Condition != ‘S’ AND Condition != ‘Q’ AND Condition != ‘L’ AND Condition != NULL

Above query represents in our case as

‘Authority’ != ‘S’ AND ‘Authority’ != ‘Q’ AND ‘Authority’ != ‘L’ AND ‘Authority’ != NULL

Returns TRUE AND TRUE AND TRUE AND FALSE so the final result is FALSE.

In this case if ALL of the above condition returns true it the query will return value ‘SQLAuthority’

I guess this solves the puzzle. However do not forget to note that in my query I have ANSI_NULL ON. When ANSI_NULLS is ON, any comparison operation with one of the operands is NULL will evaluate to UNKNOWN. For the result to include a record, all the operands for that record should evaluate to TRUE. We can absolutely get different results if we have ANSI_NULLS OFF.

We will continue the discussion in future blog posts. Additionally, the winners of the contests will be announced in the original blog posts.

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

# SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051

This is the 51th episode of SQL in Sixty Seconds Video and we will see in this episode how to RESEED identity of the table column. Identity column is every increasing (or decreasing) value based on the interval specified in its property. In today’s SQL in Sixty Seconds video we will see that how we can reseed the identity value to any other value. In the video I demonstrate that we can set the value to any value which is greater than the current column value however, you can also set the identity value to any value lower than the current column as well.

In the real world, developers sometimes delete few rows from the table and due to any reasons they want to reseed the identity value to a lower value, this feature can be very much useful. However, there is one question which keeps on ringing in my mind that is why would anyone need to reseed identity. Anyway here is the SQL in Sixty Seconds Video which explains how we can reseed identity value.

Let us see the same concept in following SQL in Sixty Seconds Video:

Here is the script used in this episode:

```USE tempdb GO -- Create Table CREATE TABLE TestTable (ID INT IDENTITY(1,1), Col1 VARCHAR(100)); -- Insert Table INSERT INTO TestTable (Col1) SELECT 'First' UNION ALL SELECT 'Second' UNION ALL SELECT 'Third' UNION ALL SELECT 'Fourth' UNION ALL SELECT 'Fifth'; -- Select data SELECT * FROM TestTable GO -- Returns current Identity Value DBCC CHECKIDENT ('TestTable', NORESEED); -- Resets the current Identity value to maximum value DBCC CHECKIDENT ('TestTable', RESEED, 11); -- Insert Table INSERT INTO TestTable (Col1) SELECT 'First' UNION ALL SELECT 'Second' UNION ALL SELECT 'Third' UNION ALL SELECT 'Fourth' UNION ALL SELECT 'Fifth'; -- Select Table SELECT * FROM TestTable ORDER BY ID; -- Drop Table DROP TABLE TestTable;```

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

# SQL SERVER – Return Specific Row to at the Bottom of the Resultset – T-SQL Script – Part 2

“How do I return a  few of my resultset rows at the bottom of the entire resultset?”

I was previously asked this question and my response was that we can do this by using the CASE statement in the ORDER BY clause and I wrote a blog post describing the same over here SQL SERVER – Return Specific Row to at the Bottom of the Resultset – T-SQL Script. In the blog post I had mentioned that there is an alternative method of UNION ALL. There have been few emails and comments regarding how to use UNION ALL in this situation hence I decided to write this blog post demonstrating the same.

Let us see the scenario one more time. In this following diagram you can notice that there are two rows which are with ProductID 712 and 715. The entire resultset is ordered by column ProductID. Now our final requirement is that we want row 715 to be the second last row in the resultset and 712 as a very last row in the resultset. Looking from outside the entire thing looks very simple however, in reality it is not as simple as it looks.

First look at the image below and see if you can come up with the solution to this problem.

Here is the script on AdventureWorks database which I have written generates the result as we have displayed in the image below.

```USE AdventureWorks2012 GO SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE ProductID BETWEEN 707 AND 716 GROUP BY [ProductID] GO ```

Now instead of writing CASE statement in ORDER BY clause we will now write UNION ALL clause. In this case if you see there are two different values which we want at the bottom of the resultset.

```USE AdventureWorks2012 GO SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE (ProductID BETWEEN 707 AND 716) AND ProductID <> 715 AND ProductID <> 712 GROUP BY [ProductID] UNION ALL SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE ProductID = 715 GROUP BY [ProductID] UNION ALL SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE ProductID = 712 GROUP BY [ProductID] GO```

Here is the resultset which we expected. We have to use two different ORDER BY clause to get the desired result. In our case if we have more than 2 special cases like these we will need keep on adding more and more UINON clauses and that will make this script more confusing and not easy to read at all.

Now let us compare the performance of the two different queries one from earlier blog post and one from current blog post. Execute following two queries together and check their execution plan. In the execution plan can be enabled by using CTRL + M keyword.

```-- Method 1 - CASE and ORDER BY USE AdventureWorks2012 GO SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE ProductID BETWEEN 707 AND 716 GROUP BY [ProductID] ORDER BY CASE WHEN [ProductID] = 715 THEN 1 WHEN [ProductID] = 712 THEN 2 ELSE 0 END GO -- Method 2 - UNION ALL USE AdventureWorks2012 GO SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE (ProductID BETWEEN 707 AND 716) AND ProductID <> 715 AND ProductID <> 712 GROUP BY [ProductID] UNION ALL SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE ProductID = 715 GROUP BY [ProductID] UNION ALL SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE ProductID = 712 GROUP BY [ProductID] GO```

You will clearly notice that the solution with CASE and ORDER BY is a much better scenario than using UNION ALL clause.

So far we have seen two examples 1) CASE and ORDER BY clause and 2) UNION ALL clause. If you know any other trick to get the similar result, please leave a comment and I will post this on my blog with due credit.

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

# SQL SERVER – Interview Questions and Answers Sample Chapter Free Download – SQL in Sixty Seconds #050

This journey of SQL in Sixty Seconds we started almost a year ago and today we are at very interesting milestone where I am recording 50th episode. Thought I wanted to keep the length of each video to sixty seconds, sometimes it went up by a few seconds. Due to this we are also at very interesting milestone as well – today’s 50th episode also accumulates the play time for entire playlist to 60 minutes (complete 1 hour).

There are two different milestones to celebrate today.

1. This is the 50th Episode of SQL in Sixty Seconds
2. Total play time for SQL in Sixty Seconds is One hour complete

This journey of SQL in Sixty Seconds we started almost a year ago and today we are at very interesting milestone where I am recording 50th episode. Thought I wanted to keep the length of each video to sixty seconds, sometimes it went up by a few seconds. Due to this we are also at very interesting milestone as well – today’s 50th episode also accumulates the play time for entire playlist to 60 minutes (complete 1 hour).

You can watch the entire SQL in Sixty Seconds series over here

On SQLAuthority.com one of the most viewed and popular articles are SQL Server Interview Questions and Answers. It has been consistently asked and referred again and again. Earlier I also had a book released on this subject which is also very popular in the industry. Here in today’s SQL in Sixty second I explain how you can download the sample chapters of the popular SQL Server Interview Questions and Answers book in PDF for FREE.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

# SQL SERVER – Return Specific Row to at the Bottom of the Resultset – T-SQL Script

“How do I return a  few of my resultset rows at the bottom of the entire resultset?”

This is a very simple question – let me explain this with following diagram. In this following diagram you can notice that there are two rows which are with ProductID 712 and 715. The entire resultset is ordered by column ProductID. Now our final requirement is that we want row 715 to be the second last row in the resultset and 712 as a very last row in the resultset. Looking from outside the entire thing looks very simple however, in reality it is not as simple as it looks.

First look at the image below and see if you can come up with the solution to this problem.

Here is the script on AdventureWorks database which I have written generates the result as we have displayed in the image below.

```USE AdventureWorks2012 GO SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE ProductID BETWEEN 707 AND 716 GROUP BY [ProductID] GO ```

Now what we want is that we want only two records which are 715 and 712 at the bottom of the resultset. There are two different ways we can achieve that, one with the method which I have demonstrated below where I write ORDER BY clause and include the CASE statement there and second method is where I use UNION clause. I prefer to use the method displayed below as it always works efficiently and consistantly.

```USE AdventureWorks2012 GO SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE ProductID BETWEEN 707 AND 716 GROUP BY [ProductID] ORDER BY CASE WHEN [ProductID] = 715 THEN 1 WHEN [ProductID] = 712 THEN 2 ELSE 0 END GO```

Here is the resultset which we expected. In the ORDER BY clause we have use CASE statement to achieve following result. We can add more case statement if we have more than one rows to treat differently.

Let me know what other solution which you would like to propose.

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

# SQL SERVER – Remove Cached Login from SSMS Connect Dialog – SQL in Sixty Seconds #049

One of the most annoying thing which I have personally come across is drop down list of Server Lists on Connect dialog in SQL Server Management Studio. Here are two of the cases when I want to delete something from SSMS Connect Screen: 1) Incorrect server name typed 2) Server does not require in the future. When I see a name of the server which is there for a long time and I know that I am not going to use it, I feel like deleting it right away so I do not have to see it again.

In SQL Server 2008 and earlier version there was a file in the installation folder once deleted it would remove all the cached login from the Connect drop down of SQL Server Management Studio. Here is the direction for SQL Server 2008 and earlier version. However, in SQL Server 2012 product team has made it very easy for all of us. We can just select the connection name which we want to do remove and click on delete and it will immediately remove the entry. This way, instead of removing every single entry, you can pick and select the entry which you want to delete.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

# SQL SERVER – Enable SQLCMD Mode in SSMS – SQL in Sixty Seconds #048

The sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks. Often a developer believes that sqlcmd works with only command prompt, however that is not true. sqlcmd can also work with SQL Server Management Studio. There are lots of cool tricks we can do with sqlcmd while we are using it along with T-SQL.

One of the tricks which I often use it that I use it to connect to different servers from SQL Server Management Studio while I work with multiple SQL Server instances. This way I do not have to keep on connecting/disconnecting the server using SSMS prompt. This makes things very easy as well it is less distracting. The code which you can use to connect to another server is as follows:

```SELECT @@SERVERNAME
GO
:Connect localhost
SELECT @@SERVERNAME
GO```

In this SQL in Sixty Seconds video I have demonstrated how we can list Windows Directory while we are working with SSMS. The display of the SQLCMD is visible in the message area of the SSMS result pane. The code of the same is as follows:

```USE AdventureWorks2012
GO
SELECT DISTINCT Type FROM Sales.SpecialOffer;
GO
!!DIR
GO
SELECT ProductCategoryID, Name FROM Production.ProductCategory;
GO```

This trick is described over here.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

# SQL SERVER – Resolve Cannot Resolve Collation Conflict Error – SQL in Sixty Seconds #047

One of the most common errors database developer’s receives when they start working with database where there are different collation used. Collation is a very important concept but it is often ignored. First use the method displayed in this video to resolve your error and right away put your efforts to understand what collation stands for.

Language is the most important part of communication. We all communicate with each other through language which both persons to understand. If we do not talk in the language which the other person cannot understand, the end result is not fruitful. In a similar way, collation is very important to any database. I have often seen situations when the collation changes from case sensitive and case insensitive turning the query result hey-wire and creates chaos on the server.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

# SQL SERVER – Identify Last User Access of Table using T-SQL Script

During the TechEd India 2013 presentations I received a question how to identify when any table is accessed by any of the user. It seems people would like to know if the table was used in any part of query by any user. The best possible solution is to create database audit task and watch the database table access. However, sometime we all want shortcut even thought it is not accurate. Here is how you can use DMV to do so. However, please note that this DMV will get reset when database services or servers are restart. Let me know if you think I should modify this DMV and have some better alternatives.

```SELECT DB_NAME(ius.[database_id]) AS [Database], OBJECT_NAME(ius.[object_id]) AS [TableName], MAX(ius.[last_user_lookup]) AS [last_user_lookup], MAX(ius.[last_user_scan]) AS [last_user_scan], MAX(ius.[last_user_seek]) AS [last_user_seek] FROM sys.dm_db_index_usage_stats AS ius WHERE ius.[database_id] = DB_ID() AND ius.[object_id] = OBJECT_ID('YourTableName') GROUP BY ius.[database_id], ius.[object_id];```

Remember to change your database context to your current database as well make sure that you insert your table name in the object_id condition.

DMV sys.dm_db_index_usage_stats has columns related to last user lookup, last user scan and last user seek. Any table which is accessed will either go for seek or scan. We can watch these columns and figure out when the table was used last. Which ever value among the last_user_lookup, last_user_scan and last_user_seek is latest is the last user access of the table.

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

# SQL SERVER – Shortcut to SELECT Single Row from Table – SQL in Sixty Seconds #046 – Video

Earlier I have blogged about the same subject and in very short time I received lots of good comments about this blog post as well lots of email from users who faced issues to make this work. Thought, the instructions are very simple in the blog post, every user read it differently and they have a different interpretation. I finally decided to do convert the same blog post in the video. I hope now it will be much easier to understand it.

If you watch any SQL Server Developer, you will notice one particular task them doing every day frequently. It is they select the row from the table to see what are the various kinds of data it contains. It is very cumbersome for developers to continuously write following code to retrieve a single row to see what the table contains. There is a shortcut how we can make our life easier if we use SQL Server Management Studio Shortcut. In this quick video we will see how we can create a shortcut which will select a single row from a table.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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