SQL Server How to the next value of identity(1,1) before insert?

Question

Folks,

How to the next value of identity(1,1) before insert?

SELECT IDENT_CURRENT('ExampleTable') + IDENT_INCR('ExampleTable');    
--does not work when the table empty!

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
DROP DATABASE [ExampleDatabase];
GO

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable')
DROP TABLE dbo.ExampleTable;
GO

CREATE TABLE
dbo.ExampleTable
(
ID INT IDENTITY(1,1) NOT NULL,
Name NVARCHAR(50) NULL
);
GO

SELECT * FROM dbo.ExampleTable;
GO

--FAILS IN THIS CASE:
SELECT IDENT_CURRENT('ExampleTable') + IDENT_INCR('ExampleTable');
GO

INSERT INTO ExampleTable
VALUES
(
'Bill'
);
GO

SELECT * FROM dbo.ExampleTable;
GO

--WORKS IN THIS CASE:
SELECT IDENT_CURRENT('ExampleTable') + IDENT_INCR('ExampleTable');
GO

INSERT INTO ExampleTable
VALUES
(
'Steve'
);
GO

SELECT * FROM dbo.ExampleTable;
GO

--WORKS IN THIS CASE:
SELECT IDENT_CURRENT('ExampleTable') + IDENT_INCR('ExampleTable');
GO

Answer 1

--FAILS IN THIS CASE:
SELECT IDENT_CURRENT('ExampleTable') + IDENT_INCR('ExampleTable');
GO


When table is empty use just SELECT IDENT_CURRENT('ExampleTable')  it should return SEED value

I tried it on SQL 2008

 

CREATE TABLE Test_Ident

(

      Col1  int IDENTITY(100,1)

)

 

GO

 

SELECT IDENT_CURRENT('Test_Ident')

GO

 

It should returns 100 seed value

more info....


When you use the IDENT_CURRENT Transact-SQL statement to retrieve the last identity value that is generated for an empty table, the IDENT_CURRENT Transact-SQL statement returns the seed. The seed is the value that is used for the first row that is loaded into the table

The IDENT_CURRENT Transact-SQL statement returns an unexpected value for an empty table that has an identity column

 

Answer 2

So what's the solution?

Answer 3

The following works.

USE [master]
GO

IF DB_ID('ExampleDatabase') IS NOT NULL
DROP DATABASE [ExampleDatabase];
GO

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

IF OBJECT_ID('dbo.ExampleTable') IS NOT NULL
DROP TABLE dbo.ExampleTable;
GO

CREATE TABLE
dbo.ExampleTable
(
ID INT IDENTITY(1,1) NOT NULL,
Name NVARCHAR(50) NULL
);
GO

SELECT * FROM dbo.ExampleTable;
GO

--it works
SELECT IDENT_CURRENT('ExampleTable')
GO

INSERT INTO ExampleTable
VALUES
(
'Bill'
);
GO

SELECT * FROM dbo.ExampleTable;
GO

--WORKS IN THIS CASE:
SELECT IDENT_CURRENT('ExampleTable') + IDENT_INCR('ExampleTable');
GO

INSERT INTO ExampleTable
VALUES
(
'Steve'
);
GO

SELECT * FROM dbo.ExampleTable;
GO

--WORKS IN THIS CASE:
SELECT IDENT_CURRENT('ExampleTable') + IDENT_INCR('ExampleTable');
GO

Answer 4

Hi!

It doesn't help, because I want to query from C# code (eapecially whenever I want to add a row) like:

        private int GetNextRowID()
        {
            int NextRowID =0; 

            try
            {              

                using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
                {
                    StringBuilder SqlQuery = new StringBuilder();

                    SqlQuery.Append("USE [ExampleDatabase]; ");

                    SqlQuery.Append("SELECT IDENT_CURRENT('ExampleTable');"); // Works first time, what about next time?

                    SqlCommand CommandSql = new SqlCommand(SqlQuery.ToString(), ConnectionSql);

                    ConnectionSql.Open();

                    SqlDataReader DataReaderSql = CommandSql.ExecuteReader();

                    while (DataReaderSql.Read())
                    {
                        NextRowID = int.Parse(DataReaderSql.GetValue(0).ToString());
                    }

                    ConnectionSql.Close();

                    MessageBox.Show("Add Row.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return NextRowID;
        }

Thanks

Answer 5

in C# code just use

private int GetNextRowID()
        {
            try
            {
                int NextRowID =0; 

                using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
                {
                    StringBuilder SqlQuery = new StringBuilder();

                    SqlQuery.Append("USE [ExampleDatabase]; ");

                    SqlQuery.Append("SELECT IDENT_CURRENT('ExampleTable');"); // Works first time, what about next time?

                    SqlCommand CommandSql = new SqlCommand(SqlQuery.ToString(), ConnectionSql);

                    ConnectionSql.Open();

                    SqlDataReader DataReaderSql = CommandSql.ExecuteReader();

                    while (DataReaderSql.Read())
                    {
                        NextRowID = int.Parse(DataReaderSql.GetValue(0).ToString());
                        NextRowID = NextRowID + 1;
                    }

                    ConnectionSql.Close();

                    MessageBox.Show("Add Row.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return NextRowID;
        }

Answer 6

       Every time when I want add a row, I call this method to get the next identity value, however
       
        // THIS WILL RETURN 2 IF THE TABLE IS EMPTY (THAT'S WRONG!):
       private int GetNextRowID()
        {
            try
            {
                int NextRowID =0; 

                using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
                {
                    StringBuilder SqlQuery = new StringBuilder();

                    SqlQuery.Append("USE [ExampleDatabase]; ");

                    SqlQuery.Append("SELECT IDENT_CURRENT('ExampleTable');"); // Works first time, what about next time?

                    SqlCommand CommandSql = new SqlCommand(SqlQuery.ToString(), ConnectionSql);

                    ConnectionSql.Open();

                    SqlDataReader DataReaderSql = CommandSql.ExecuteReader();

                    while (DataReaderSql.Read())
                    {
                        NextRowID = int.Parse(DataReaderSql.GetValue(0).ToString());
                        NextRowID = NextRowID + 1;  // WIL RETURN 2 IF THE TABLE IS EMPTY (THAT'S WRONG!)
                    }

                    ConnectionSql.Close();

                    MessageBox.Show("Add Row.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return NextRowID;
        }

Answer 7

Hola!

There's a lot of (programmatic) workaround, that's fine. You see as a C# programmer (and not really a database expert), I want an SQL expert solution (SQL QUERY), that is why I posted this in SQL Server forum!

Thanks

Answer 8

Do you know how to call a stored procedure from C#?

LEt create a proc

CREATE PROCEDURE dbo.uspGetNextID

AS

BEGIN

declare @nextID int
 
IF EXISTS (SELECT TOP 1 * FROM EXAMPLETABLE)

BEGIN

  SELECT @nextID = IDENT_CURRENT('[ExampleTable]') + IDENT_INCR('[ExampleTable]')

END

ELSE

BEGIN

SELECT @nextID = 1

END

END

then change your C# code to

  private int GetNextRowID()
        {
            try
            {
                int NextRowID =0;

                using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
                {
                  
                
     

                    SqlCommand CommandSql = new SqlCommand(ConnectionSql);
      CommandSql.CommandType = CommandType.StoredProcedure;
                   CommandSql.CommandText = "dbo.uspGetNextID";

                    ConnectionSql.Open();

                    SqlDataReader DataReaderSql = CommandSql.ExecuteReader();

                    while (DataReaderSql.Read())
                    {
                        NextRowID = int.Parse(DataReaderSql.GetValue(0).ToString());
                    }

                                          ConnectionSql.Close();

                    MessageBox.Show("Add Row.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return NextRowID;
        }