Today's question (by Steve Jones - SSC Editor):

Who am I?

create procedure AddNewCustomer
  @customername varchar(200)
AS
BEGIN
    DECLARE @added VARCHAR(100)
    SELECT @added = xxx

    IF @customername IS NOT NULL
      INSERT dbo.Customer
      (
          CustomerName,
          AddedBy 
      )
      VALUES
      (@customername, @added)
END

Think you know the answer? Click here, and find out if you are right.

The Maximum Value in the Identity Column

I have a table with this data:

TravelLogID CityID StartDate  EndDate
1           1      2025-01-01 2025-01-06
2           2      2025-01-01 2025-01-06
3           3      2025-01-01 2025-01-06
4           4      2025-01-01 2025-01-06
5           5      2025-01-01 2025-01-06

I run this code:

SELECT IDENT_CURRENT('TravelLog')

I get the value 5 back. Now I do this:

SET IDENTITY_INSERT dbo.TravelLog ON
INSERT dbo.TravelLog
(
    TravelLogID,
CityID,
    StartDate,
    EndDate
)
VALUES
(25, 5, '2025-09-12', '2025-09-17')
SET IDENTITY_INSERT dbo.TravelLog OFF

I now run this code.

DBCC CHECKIDENT(TravelLog)
GO
INSERT dbo.TravelLog
(
    CityID,
    StartDate,
    EndDate
)
VALUES
(4, '2025-10-14', '2025-10-17')
GO

What is the value for TravelLogID for the row I inserted for CityID 4 and dates starting on 14 Oct 2025?

Answer: 26

Explanation: Since the max value in the column TravelLogID is greater than the identity value, when I run the DBCC CHECKIDENT command, this value (25) is inserted into the metadata storage for identity values. When the next row is inserted, it gets the value 26. Ref: DBCC CHECKIDENT - https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver17

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.