How to find missing Identity/Sequence numbers using T-SQL?

SQL Developers, did you ever come across a situation where you need to find missing identity or sequence number for a given table? For instance, someone deleted few records from a table which has an IDENTITY column. Have you wondered how to find those missing rows? In this blogpost, I am going to explain different ways to find missing identity or sequence numbers.

First I will create some sample data for demonstration purpose.

-- Prepare Test data --
SET NOCOUNT ON
IF OBJECT_ID('dbo.TestData') IS NOT NULL    DROP TABLE dbo.TestData

CREATE TABLE dbo.TestData (

    ID INT IDENTITY(1, 1) NOT NULL
)

DECLARE @Counter INT = 1
WHILE @Counter <= 15
BEGIN
    INSERT dbo.TestData DEFAULT VALUES
    SET @Counter += 1
END
 

SELECT ID FROM dbo.TestData
 

 

 

 

  

Now lets delete some rows manualy:
-- Now delete some records (IDs)
DELETE dbo.TestData WHERE ID IN (3,7,8,10,12,13)
 
--Verifiy the data
SELECT ID FROM dbo.TestData

 

Below are three different ways to identity missing values.

 

Find missing sequence numbers using Ranking Function:

-------------------------------------------

-- Option 1: Using Ranking function

-------------------------------------------
DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

SELECT SeqID AS MissingSeqID

FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns) LkUp
LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID

WHERE t.ID is null and SeqID < @MaxID

Here is the output:

 

 -- If there are less records in sys.columns and

-- you need need larger result then use CROSS JOIN

SELECT SeqID AS MissingSeqID

FROM (SELECT ROW_NUMBER() OVER (ORDER BY c1.column_id) SeqID

FROM sys.columns c1

CROSS JOIN sys.columns c2) LkUp

LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID

WHERE t.ID is null and SeqID < @MaxID

 

Find missing sequence numbers using CTE:

-------------------------------------------

-- Option 2: Using Common Table Expression

-------------------------------------------

DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

;WITH CTE (MissingSeqID, MaxID)

AS (

    SELECT 1 AS MissingSeqID, @MaxID

    UNION ALL

    SELECT MissingSeqID + 1, MaxID FROM CTE WHERE MissingSeqID < MaxID

    )

SELECT MissingSeqID FROM CTE

LEFT JOIN dbo.TestData t on t.ID = CTE.MissingSeqID

WHERE t.ID is NULL

GO

 

 

Find missing sequence numbers using Tally Table:
This is the most prefered way out of all the above options.

-------------------------------------------

-- Option 3: Using Tally Table

-------------------------------------------

DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

SELECT t.ID  MissingSeqID FROM dbo.Tally t

LEFT JOIN dbo.TestData td

ON td.ID = t.ID

WHERE td.ID IS NULL

AND t.ID < @MaxID

 



 

Introducing IIF() To  SQL Server Family

IIF() is a brand new logical function introduced with SQL Server 2012 that allows you to perform IF...THEN...ELSE conditional statements within a single function. Behavior of this function is similar to any other programming language IIF() function!

In earlier versions of SQL Server, we have used IF...ELSE and CASE WHEN...THEN...ELSE...END to perform logical conditional operations.

IIF() can be handy for writing conditional CASE statements in a single function. It evaluates the expression passed in the first parameter and returns either TRUE or FALSE.

--Example1: Repalcement of simple IF...ELSE statement

DECLARE @GradeCHAR(1) = 'A'

SELECT IIF(@Grade = 'F', 'Failed', 'Passed') AS Result

GO

Here is the output of above T-SQL code:
 

--Example2: Nested IIF()

DECLARE @PercentDECIMAL(5, 2) = '50'

SELECT IIF(@Percent > 80, 'A',

IIF(@Percent > 60, 'B', 'C'))

GO;

Here is the output of above T-SQL code:

----

C

(1 row(s) affected)

-- Example3: In this example, we will evaluate the marks obtained
-- by Smith and Hari and identify who got higer marks between them.

DECLARE @Smith_Marks INT= 72

,@Hari_Marks INT = 86

SELECT IIF(@Smith_Marks > @Hari_Marks

,CONCAT('Smith got ',@Smith_Marks-@Hari_Marks,' marks higher than Hari')

,CONCAT('Hari got ' ,@Hari_Marks-@Smith_Marks,' marks higher than Smith')

)

GO

Here is the output:

---------------------------------------------

Hari got 14 marks higher than Smith

(1 row(s) affected)

 

Table Partition

Table partitioning is a data organization scheme in which table data is divided across multiple data partitions or ranges according to values in a table column.

Benefits of table partitioning

There are numerous benefits of table partitioning:

• To improve the scalability and manageability of large tables and tables in Database and Data Warehouse
• Database and Data Warehouse that would benefit from easier roll-in and roll-out of table data
• Furthermore, if a large table exists on a system with multiple CPUs, partitioning the table can lead to better performance through parallel operations.
• A table with varying access patterns might be a concern for performance and availability when different sets of rows within the table have different usage patterns.

 The steps for creating a partitioned table include the following:

1. Create a partition function to specify how a table or index that uses the function can be partitioned.
2. Create a partition scheme to specify the placement of the partitions of a partition function on filegroups.
3. Create a table or index using the partition scheme.

 
Below are the steps to creation Horizontal Table Partition

-- Create the partition function

CREATE PARTITION FUNCTION PartitionFunctionMonthly (int)
AS RANGE RIGHT
FOR VALUES (20100101, 20100201, 20100301)
GO

-- Add the partition scheme
CREATE PARTITION SCHEME PartitionSchemaMonthly
AS PARTITION PartitionFunctionMonthly
ALL TO ( [PRIMARY] )
GO

 -- Create a simple table
CREATE TABLE PartitionTable (
   DateKey int NOT NULL,
   CustomerKey int NOT NULL,
   SalesAmt money,
CONSTRAINT PKPartitionTable PRIMARY KEY NONCLUSTERED
   (DateKey, CustomerKey)
)
ON PartitionSchemaMonthly(DateKey)
GO

------------------------------------
-- Unit Testing of Partitions
------------------------------------
-- Add some rows
INSERT INTO PartitionTable (DateKey, CustomerKey, SalesAmt)
SELECT 20091201, 1, 5000 UNION ALL
SELECT 20100101, 2, 3000 UNION ALL
SELECT 20100215, 7, 6000 UNION ALL
SELECT 20100331, 5, 3000 UNION ALL
SELECT 20100415, 8, 6000
GO

-- A query accesses the entire table, exactly as you'd expect.
SELECT * FROM PartitionTable
GO

--Query partition contents
SELECT
$partition.PartitionFunctionMonthly(DateKey) AS [Partition#],
COUNT(*) AS RowCount,
Min(DateKey) AS MinDate,
Max(DateKey) AS MaxDate
FROM PartitionTable
GROUP BY $partition.PartitionFunctionMonthly(DateKey)
ORDER BY [Partition#]
GO

Moving Database Files From One Drive to Another

In practice, database files grows everyday. Sometimes it occupy the complete disk and we may end up in unsufficeint mamroy - leading to unexpected results.

In this blog, I'll explain how to transfer database files from one drive to another. To explain, I'll create a test database TestDB. Here is the command:

USE [master]
GO

IF DB_ID(N'TestDB') IS NULL
BEGIN
CREATE DATABASE [TestDB] ON PRIMARY
(
   NAME = N'TestDB'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.mdf'
   ,SIZE = 3MB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 1024KB
)
LOG ON
(
   NAME = N'TestDB_log'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.ldf'
   ,SIZE = 3MB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 10%
)
END
GO

Now check existing files location:

USE TestDB
GO
SELECT name, physical_name, state_desc
FROM TestDB.sys.master_files
WHERE database_id = DB_ID(N'TestDB')
 

 
 
 
 
 
 
 
 
Now the database is created, you can create some tables and enter some data into the database, if you wish, Otherwise proceed like this:
 
Step1: Make the database OFFLINE.

USE [master]
GO
ALTER DATABASE TestDB SET OFFLINE

Step2: Move file Physically
Now you need to move that file physically from existing folder to the new the location. Open the parent folder (Here 'C:\MSSQL\DATA') , You can see both mdf and ldf files', make sure that you cut the appropriate file, in this example it is the Log file. Cut that "TestDB_log.LDF" file and paste it on "D:\MSSQL\Data"
 
Step3: Update the system reference
Once you move the file physically , you need to update the system reference using the ALTER DATABASE .. MODIFY FILE Command as shown below:
 
ALTER DATABASE TestDB
MODIFY FILE (
   NAME ='TestDB_log'
   ,FILENAME = 'D:\MSSQL\Data\TestDB.LDF'
)

Step5 : Make database ONLINE
Last step is to make database online as shown below:

ALTER DATABASE TestDB SET ONLINE
GO

We are done. Now check existing files location using query mentioned above.

Convert Decimal to ROMAN using T-SQL

Do you want to convert a number into Roman Equivalent using T-SQL code or function? If yes then here you go:

/**************************************************
CREATED BY : Hari Sharma
PURPOSE    : Convert Decimal to ROMAN Equivalent
HOW TO USE : SELECT dbo.GetRomanNo(18)
             --OUTPUT: XVIII
**************************************************/
CREATE Function GetRomanNo(@N as varchar(20))
RETURNS VARCHAR(100)
AS
BEGIN
  DECLARE @s varchar(100), @r varchar(100),
          @i bigint, @p int, @d bigint
  SET @s = ''
  SET @r = 'IVXLCDM' -- Roman Symbols

  /* There is no roman symbol for 0, but I don't
   want to return an empty string */
  IF @n=0
     SET @s = '0'
  ELSE
  BEGIN
     SELECT @p = 1, @i = ABS(@n)
     WHILE(@p<=5)
     BEGIN
       SET @d = @i % 10
       SET @i = @i / 10
       SELECT @s = CASE
         WHEN @d IN (0,1,2,3) THEN
           Replicate(SubString(@r,@p,1),@d) + @s
         WHEN @d IN (4) THEN
           SubString(@r,@p,2) + @s
         WHEN @d IN (5,6,7,8) THEN
           SubString(@r,@p+1,1) +
           Replicate(SubString(@r,@p,1),@d-5) + @s
         WHEN @d IN (9) THEN
           SubString(@r,@p,1) + SubString(@r,@p+2,1) + @s
         END
       SET @p = @p + 2
     END
     SET @s = Replicate('M',@i) + @s
     IF @n < 0
     SET @s = '-' + @s
   END


   RETURN @s
END
GO

Track SQL Database Growth

In this article I am sharing a simple T-SQL code to track database growth for specific database. This could be a very simple query for SMEs but it can really help newbies:

/*************************************************
Purpose : Track Database Growth for a specific DB
Create By : Hari Sharma
**************************************************/
SELECT
   BackupDate =
   CONVERT(VARCHAR(10),backup_start_date, 111)
   ,SizeInMBs=FLOOR(backup_size/1024000)
FROM msdb..backupset
WHERE
   database_name = DB_NAME() --Specify DB Name
   AND type = 'd'
ORDER BY
   backup_start_date desc

Function to convert a string in Camel Case (Proper Case)

Sometime, we need to display string data in proper case, i.e. first character of each word should be in UPPER case. SQL Server doesn't provide any built-in function for this requirement. In this article, I am putting T-SQL code to create this function. This is a generalised Function that will return any input string in Proper Case [Camel Case]. Means Afetr every space, first later will be in UPPER case.


Example:
Input String    :  'microSoft sql server'
Output String : 'Microsoft Sql Server'
 
/*************************************************
CREATED BY : HARI SHARMA ON MAR 30, 2007
PURPOSE : TO CONVERT INPUT STRING IN CAMEL CASE
**************************************************/
CREATE FUNCTION [dbo].[CamelCase]
(@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
  DECLARE @Result varchar(2000)
  SET @Str = LOWER(@Str) + ' '
  SET @Result = ''
  WHILE 1=1
  BEGIN
    IF PATINDEX('% %',@Str) = 0 BREAK
    SET @Result = @Result + UPPER(Left(@Str,1))+
    SubString  (@Str,2,CharIndex(' ',@Str)-1)
    SET @Str = SubString(@Str,
      CharIndex(' ',@Str)+1,Len(@Str))
  END
  SET @Result = Left(@Result,Len(@Result))
  RETURN @Result
END
---------------------------------------------------
-- HOW TO USE --
-- SELECT dbo.CamelCase('hARi nARAyan shARMa')
-- Output: Hari Narayan Sharma
---------------------------------------------------
GO

Find Largest Size Tables in a Database

Below is the stored procedure to return largest tables of a database.

IF OBJECT_ID('sp_LargestTables' ,'P') IS NOT NULL
DROP PROC sp_LargestTables
GO
/***************************************************************
CREATE BY : Hari Sharma
PURPOSE : To get a list of tables according to their size.
***************************************************************/
CREATE PROC sp_LargestTables
(
   @n int = NULL,
   @IsSystemAllowed bit = 0
)
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @LOW int
   SELECT @LOW = LOW
   FROM [master].[dbo].[spt_values] (NOLOCK)
   WHERE [number] = 1 AND [type] = 'E'

   IF @n > 0 SET ROWCOUNT @n
   SELECT TableName,[Row Count],[Size (KB)] FROM
   (
      SELECT QUOTENAME(USER_NAME(o.uid)) + '.' +
                     QUOTENAME(OBJECT_NAME(i.id))
         AS TableName
         ,SUM(i.rowcnt) [Row Count]
         ,CONVERT(numeric(15,2),
            (((CONVERT(numeric(15,2),SUM(i.reserved)) * @LOW) / 1024.0))) AS [Size (KB)]
      FROM sysindexes i (NOLOCK)
      INNER JOIN sysobjects o (NOLOCK)
         ON i.id = o.id AND
         ((@IsSystemAllowed = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
      WHERE indid IN (0, 1, 255)
      GROUP BY
         QUOTENAME(USER_NAME(o.uid)) + '.' +
         QUOTENAME(OBJECT_NAME(i.id))
   ) AS Z
   ORDER BY [Size (KB)] DESC

   SET ROWCOUNT 0
END
GO


How to use:

1. If you want all the user tables in the database with largest db size then:
EXEC sp_LargestTables [No Need to pass parameters]

2. If you want only 3 tables in the database with largest db size then:
EXEC sp_LargestTables 3

3. If you want only 20 tables in the database with largest db size including system tables then:
EXEC sp_LargestTables 20,1

Function to Split Multi-valued Parameters

How to split a comma seperated value?
Many times we need to write T-SQL statements to split a comma seperated value, however string is not necessarily to be comma seperated, it can be seperated by any delimiter e.g. comma (,), @, &, ; etc.

How to use Multi-valued Parameters of SSRS report in a Stored procedures?
One more question comes around, how to use a multi valued parameter of SSRS report in a Stored Procedure to filter report data? I am sure you can't use a multi valued parameter directly in T-SQL code without spliting multiple values, if you do so without spliting values, SPROC will throw an error.

To find the answer of above questions, you create a user defined function using below T-SQL code:

/**********************************************
CREATED BY HARI
PURPOSE : To split any multivalued string
seperated by any delimiter into multiple rows
***********************************************/
CREATE FUNCTION [dbo].[SplitMultivaluedString]
(
   @DelimittedString [varchar](max),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'')
                + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,
             CharIndex(@Delimiter,@sTemp)-1)
     
      SET @sTemp = RIGHT(@sTemp,
        LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END
GO

/* How to use this function:
-----------------------------------------
SELECT * FROM [dbo].[SplitMultivaluedString] ('1,2,3,4', ',')
SELECT * FROM [dbo].[SplitMultivaluedString] ('1;2;3;4', ';')
SELECT * FROM [dbo].[SplitMultivaluedString] ('Hari#Thomas','#')
*/

T-SQL script for BULK INSERT

Here is the T-SQL code to BULK insert data from text file to SQL Table:

BULK INSERT [Database]..[TableName]

FROM 'D:\test.txt' -- Path of text file
WITH
(
   FIRSTROW = 1
   ,BATCHSIZE = 10000
   ,FIELDTERMINATOR = ','
   ,ROWTERMINATOR = '\n'
   ,LASTROW = 20
)
GO

Here is the description of Keywords used:

FIRSTROW
Specifies the number of the first row to load. The default is the first row in the specified data file.

BATCHSIZE
Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. If this fails, SQL Server commits or rolls back the transaction for every batch. By default, all data in the specified data file is one batch.

FIELDTERMINATOR
Specifies the field terminator to be used for char and widechar data files. The default field terminator is \t.

ROWTERMINATOR
Specifies the row terminator to be used for char and widechar data files. The default row terminator is \r\n (newline character).

LASTROW
Specifies the number of the last row to load. The default is 0, which indicates the last row in the specified data file.

Attach and Detach a database using T-SQL

Attach & Detach a database -

Count Number of Rows for Tables of a Database

Easiest way to get an exact value of Number of Rows for all the tables in a SQL Server database

1. Use DBCC UPDATEUSAGE - this updates the values of rows for each partition in a table.
2. Use undocumented stored procedure sp_msForEachTable and store the result set in a table.

Below is the query to get required output:

USE [DatabaseName]
GO

DECLARE @DynSQL NVARCHAR(255)
SET @DynSQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC(@DynSQL)

IF OBJECT_ID('tempdb..#T','U') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T (TableName nvarchar(500),NumberOfRows int)
GO

INSERT INTO #T
EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1) as TableName,COUNT(*) as NumberOfRows FROM ?'
GO 
SELECT * FROM #T ORDER BY NumberOfRows DESC

T-SQL Query to Calculate Month End Date

Here is the easiest way to calculate Month End Date for any given date:

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)-1 AS MonthEndDate

Example:
If you replace GetDate() with any date, above query will return the Month End Date for that particular month.
If GetDate() value is '2010-01-25' then Output will be '2010-01-31'
If GetDate() value is '2010-02-20' then Output will be '2010-02-28'