SQL SERVER – NuoDB in Sixty Seconds – SQL in Sixty Seconds #053

Earlier this week, I have done five part blog series on NuoDB and it was very well received by audienceNuoDB is an elastically scalable SQL database that can run on local hostdatacenter and cloud-based resources. t is an operational NewSQL database built on a patented emergent architecture with full support for SQL and ACID guarantees. In this blog post, I will explore how one can download and install NuoDB database.

In this video I explain how one can install NuoDB in very few seconds and set up the entire environment in additional few seconds. One can get going with installation of NuoDB and sample database in total of less than 60 seconds.

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

You can Download NuoDB and reproduce the same Sixty Seconds experience.

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)

About these ads

SQL SERVER – Add Identity Column to Table Based on Order of Another Column

After reading my earlier article on Identity Column, I received a very interesting question. The reason, I like to call it interesting is though, I have provided answers to him, I believe there should be another better alternative to this problem. Let us see the question first in his own words.

“Hi Pinal,

I already have existing table and the table already have fewer columns. The table does not have identity column and I would like to add an identity column to this table. The problem is that every time when I try to add an identity column to the table, it adds the value based on the default order of the table. I would like to add the identity value based on the order sequence of another column from the table. Do you have any alternative to it. To illustrate my problem here is the simple script based on my table schema. My table also do not have any index as of now.

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
GO
-- Insert Data
INSERT INTO TestTable (Col1, Col2)
VALUES (33, 'Pinal');
INSERT INTO TestTable (Col1, Col2)
VALUES (22, 'Nupur');
INSERT INTO TestTable (Col1, Col2)
VALUES (11, 'Shaivi');
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Add Identity Column
ALTER TABLE TestTable
ADD ID INT IDENTITY(1, 1)
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Clean up
DROP TABLE TestTable
GO

Here is the result set of the query above. Currently the result is ordered by the column Col1 DESC but ideally I would like to get the result ordered by Col1 but in ASC order.

Is there any workaround to do the same?”

As I said I find this question very interesting and I was able to come up with the solution as well fairly quickly as the user had not created an index on the table. I quickly created clustered index in ASC order on Col1 and it ordered the table as expected and later added the identity column there. Let us see the script to get the desired result.

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
GO
-- Insert Data
INSERT INTO TestTable (Col1, Col2)
VALUES (33, 'Pinal');
INSERT INTO TestTable (Col1, Col2)
VALUES (22, 'Nupur');
INSERT INTO TestTable (Col1, Col2)
VALUES (11, 'Shaivi');
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Create Clustered Index on Column ID
CREATE CLUSTERED INDEX IX_TestTable ON dbo.TestTable
(Col1 ASC)
GO
-- Add Identity Column
ALTER TABLE TestTable
ADD ID INT IDENTITY(1, 1)
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Clean up
DROP TABLE TestTable
GO

The above script will produce the following answer which user is expecting:

Now here is my question back to, this was fairly simple for me to do as there was no index created on the table. I was able to create clustered index on the column and get the desired result. However, what will be the alternative solution to this question if the clustered index is already created on the table and there was no option to modify the same. Another alternative solution would be to drop the table and do processing but that is never a good solution as well, it is not possible if there are foreign keys exists on the table.

What would you do in the situation mentioned above?

Click to Download Scripts

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

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)

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)