SQL SERVER – Puzzle SET ANSI_NULLS and Resultset – SQL in Sixty Seconds #052

Earlier I have posted a puzzle where I was receiving different results when I executed two different queries. I encourage all of you to read the original puzzle here, the puzzle had received many fantastic responses and I have later blogged about the solution of the puzzle over here.

Now I have decided to extend the same puzzle and take it to the next level. In earlier puzzle I had value of the ANSI_NULLS was set to ON. Now in this puzzle let us set the value of the ANSI_NULLS to OFF. When the value of ANSI_NULLS was off at that time, the result of the both the queries is almost identical. The key reason behind this behavior is ANSI_NULLS setting.

However, the puzzle is to answer that why the different queries are producing almost same results when there is a different setting of ANSI_NULL.

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

Here is the script used in this episode:

-- Original Puzzle
http://bit.ly/sql-puzzle-ansi
-- 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);
-- Puzzle Solution
http://bit.ly/sql-puzzle-ansi-sol
------------------------------------------------------
-- New puzzle
SET ANSI_NULLS OFF;
-- Query3
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 4
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

Related Tips in SQL in Sixty Seconds:

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

Click to Download Scripts

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

About these ads

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?

Click to Download Scripts

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

SQL SERVER – Negative Identity Seed Value and Negative Increment Interval

I just had interesting conversation with one of my friend who said identity value can not start from Zero. I told him that it can even start from negative value. He did not believe it. I quickly come with example and he was surprised to see it.

USE [AdventureWorks]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[IdentityNegative]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[IdentityNegative]
GO
CREATE TABLE dbo.IdentityNegative
(
ID INT NOT NULL IDENTITY (-100, -1),
Firstcol VARCHAR(100) NULL
)
GO
INSERT INTO dbo.IdentityNegative (Firstcol)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth'
GO
SELECT *
FROM dbo.IdentityNegative
GO
DROP TABLE dbo.IdentityNegative
GO

Now let us see the resultset. Here we have specified negative identity Seed value as well negative Increment Interval.

Did you know that Identity Value can have negative value? If yes, can you come up with proper business need of the same?

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

SQL SERVER – Find Gaps in The Sequence

I have previously written two articles on the subject of missing identity and both are very well received by community. I had great fun to write article as many SQL Server expert participated in both the articles. Expert Imran Mohammed had provided excellent script to find missing identity. Please read both the articles for additional information.

SQL SERVER – Discussion – Effect of Missing Identity on System – Real World Scenario

SQL SERVER – List All Missing Identity Values of Table in Database

Well what really interesting is that another user asked similar question to find gaps in sequence. Two different expert Brian and Tejas responded with excellent solution. Let us go over questions and their solutions.

Download script used in this article

Question by Enrico

Hi Pinal/Folks,

Can anyone please help me? I am stunned at this one.

Let’s say I have the following records:

INV0096
INV0097
INV0099
INV0100
INV0102
INV0103

How can I generate a SQL Script that will show me the gaps in the sequence?

In such a way that the results will give me INV0098 and INV0101.
Or even just the number 98, and 101.

Thank you so much for your time.

Best regards,

Enrico

First Solution by Brian Tkatch

/*
Brian Tkatch Solution 1
*/
WITH
Data(Datum)
AS
(
SELECT 'INV0096' UNION ALL
SELECT 'INV0097' UNION ALL
SELECT 'INV0099' UNION ALL
SELECT 'INV0100' UNION ALL
SELECT 'INV0102' UNION ALL
SELECT 'INV0103'
),
CTE
AS
(
SELECT
CAST(SUBSTRING(MIN(Datum), 4, 4) AS INT) Start,
CAST(SUBSTRING(MAX(Datum), 4, 4) AS INT) Finish
FROM
Data
UNION ALL
SELECT
Start + 1,
Finish
FROM
CTE
WHERE
Start < Finish
)
SELECT
Common.Formatted
FROM
CTE
CROSS APPLY(SELECT 'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4)) Common(Formatted)
WHERE
NOT EXISTS
(
SELECT
*
FROM
Data
WHERE
Data.Datum = Common.Formatted
)
OPTION
(MAXRECURSION 0);

Second Solution by Brian Tkatch

/*
Brian Tkatch Solution 2
The CROSS APPLY is just nice, but not required. Without it, the query is very similar:
*/
WITH
Data(Datum)
AS
(
SELECT 'INV0096' UNION ALL
SELECT 'INV0097' UNION ALL
SELECT 'INV0099' UNION ALL
SELECT 'INV0100' UNION ALL
SELECT 'INV0102' UNION ALL
SELECT 'INV0103'
),
CTE
AS
(
SELECT
CAST(SUBSTRING(MIN(Datum), 4, 4) AS INT) Start,
CAST(SUBSTRING(MAX(Datum), 4, 4) AS INT) Finish
FROM
Data
UNION ALL
SELECT
Start + 1,
Finish
FROM
CTE
WHERE
Start < Finish
)
SELECT
'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4)
FROM
CTE
WHERE
NOT EXISTS
(
SELECT
*
FROM
Data
WHERE
Data.Datum = 'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4)
)
OPTION
(MAXRECURSION 0);

Third Solution by Tejas Shah

/*
Tejas Shah Solution 3
*/
DECLARE @Test TABLE (Data VARCHAR(10))
INSERT INTO @test
SELECT 'INV0096'
UNION ALL
SELECT 'INV0097'
UNION ALL
SELECT 'INV0099'
UNION ALL
SELECT 'INV0100'
UNION ALL
SELECT 'INV0102'
UNION ALL
SELECT 'INV0103'
UNION ALL
SELECT 'INV0106'
UNION ALL
SELECT 'INV0110'
;WITH cte1 AS(
SELECT CAST(RIGHT(Data,4) AS INT) AS RowID
FROM @Test
), Missing AS(
SELECT MIN(RowID) AS MissNum,
MAX(RowID) AS MaxID
FROM Cte1
UNION ALL
SELECT MissNum + 1,
MaxID
FROM Missing
WHERE MissNum < MaxID
)
SELECT missnum
FROM Missing
LEFT JOIN cte1 tt ON tt.Rowid = Missing.MissNum
WHERE tt.Rowid IS NULL
OPTION (MAXRECURSION 0);

Click to Download Scripts

Let me know if you know any other trick to achieve the same.

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

SQL SERVER – List All Missing Identity Values of Table in Database

The best part of any blog is when readers ask each other questions. Better still, is when a reader takes the time to provide a detailed response.

A few days ago, one of my readers, Yasmin, asked a very interesting question:

How we can find the list of tables whose identity was missed (not is sequential order) within the entire database?

A big thank you to SQL Server Expert, Imran Mohammed, for his excellent response to this question. He also provided an extremely impressive script, which is well described and contains inline comments.

This script is well worth keeping in your archive for future reference.

Click to Download Scripts

If you and use the script, please comment here and let me know about your experience.

If you know any other way that this can be accomplished or have a different solution, please post your comment here, or send me an email and I will publish it with due credit.

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

SQL SERVER – Add or Remove Identity Property on Column

This article contribution from one of my favorite SQL Expert Imran Mohammed. He is one man who has lots of ideas and helps people from all over the world with passion using this community as platform. His constant zeal to learn more about SQL Server keeps him engaging him to do new SQL Server related activity every time.

1. Adding Identity Property to an existing column in a table.

How difficult is it to add an Identity property to an existing column in a table? Is there any T-SQL that can perform this action?

For most, the answer to the above two questions is an absolute NO! There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an Identity Property to an existing column in a table.

However, there is an easy way to accomplish this action. It can be done through SSMS.

Are you finding my answer difficult to believe? Let me explain.

Let’s first see what SSMS does in backend when you add Identity property on an existing column in any table.

Now, let’s create an example table for better understanding.

This table is vacant, with no records, as you can see in the following screenshot.

Take a look at the design of this table in SSMS.

Now let us make eid, an Identity column.

This is very easy.  All you have to do is just select Yes from the drop down list and you are done!

But before moving further let’s see what T-SQL SQL Server is using to make this change.

You will notice that T-SQL is used by SQL Server to make this change.

After you make the change for Identity property from No to Yes, on top in tools box, you will see Generate Change Script. This is the T-SQL Script that SQL Server will use to make this change.

Unfortunately, I cannot expand this dialogue box further to show you the complete script, so I have copied this script below.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN
TRANSACTION
SET QUOTED_IDENTIFIER ON
SET
ARITHABORT ON
SET
NUMERIC_ROUNDABORT OFF
SET
CONCAT_NULL_YIELDS_NULL ON
SET
ANSI_NULLS ON
SET
ANSI_PADDING ON
SET
ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_example1
(
eid INT NOT NULL IDENTITY (1, 1)
)
ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_example1 ON
GO
IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERT INTO dbo.Tmp_example1 (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)'
)
GO
SET IDENTITY_INSERT dbo.Tmp_example1 OFF
GO
DROP TABLE dbo.example1
GO
EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO
COMMIT

Let us understand above code in simple words:

Begin Transaction
Step 1: Create similar table structure as example1, say Tmp_Example1 with Identity Column.
Step 2: Set IDENTITY_INSERT ON on new table just created.Step 3: Insert all values from example1 to Tmp_Example1
Step 4: Set IDENTITY_INSERT OFF on Tmp_Example..
Step 5: Drop example1 (What is going on… dropping our original table???)
Step 6: Rename Tmp_Example1 to Example1.
Commit Transaction

What if you have millions and millions of records in your table?

Imagine how much time it would take to copy an entire table?

Just to make a column Identity do you really need to go through all these and take so much pain?

Certainly not!

Same is the case when you want to remove Identity column property from a column.

2. Removing Identity Property from an existing column in a table.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET
ARITHABORT ON
SET
NUMERIC_ROUNDABORT OFF
SET
CONCAT_NULL_YIELDS_NULL ON
SET
ANSI_NULLS ON
SET
ANSI_PADDING ON
SET
ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_example1
(
eid INT NOT NULL
)
ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERT INTO dbo.Tmp_example1 (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)'
)
GO
DROP TABLE dbo.example1
GO
EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO
COMMIT

Once again, I would like to congratulate Imran Mohammed for this wonderful article.

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

SQL SERVER – Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column

Some time ago I was helping one of my Junior Developers who presented me with an interesting situation. He had a table with Identity Column. Because of some reasons he was compelled to delete few rows from the table. On inserting new rows in the table he noticed that the rows started from the next identity value which created gap in the identity value. His application required all the identities to be in sequence, so this was certainly not a small issue for him.

The solution to this issue regarding gap in identity column is very simple. Let us first take a look at his application’s situation wherein there is missing identity and then we will move on to the solution.

Developers can easily deter the above issue by avoiding gap in sequence of identity column through two additional SQL Tricks of reseeding identity.

We will now see the same example with the solution to the above gap issue. On deleting records, table was reseeded with identity, which was deleted. Download complete SQL Script here.

USE AdventureWorks
GO
/* Create a table with one identity column */
CREATE TABLE TableID (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO
/* Insert 10 records with first value */
INSERT INTO TableID (Col)
VALUES ('First')
GO 10
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Delete last few records */
DELETE
FROM
TableID
WHERE ID IN (8,9,10)
GO
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Get current Max Value and reseed table */
DECLARE @MaxID INT
SELECT
@MaxID = MAX(ID)
FROM TableID
DBCC CHECKIDENT('TableID', RESEED, @MaxID)
GO
/* Insert 10 records with second value */
INSERT INTO TableID (Col)
VALUES ('Second')
GO 5
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Clean Database */
DROP TABLE TableID
GO

I hope is solution is clear to all my readers and they will use it to avoid problems related to gap in identity column. Do send me your feedback on this article and let me know if you all need further explanation.

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