“Hey DBA’s, I have a thought about a really creative solution to this really hard problem. I have two tables in my database and each has an identity column, each of which is currently incrementing by 1. I’d like to change each to increment by 2. Also, one of the tables will need to only contain ODD numbered values, and the other will need to only contain EVEN numbered values. This will help me solve my really hard problem."
So the first question I had to answer was can you change the increment value of an identity column? Short answer, yes. Caveat, it requires you to rebuild the table to do so. Not a big deal.
I could have replied with that and sent my developer on his way, but I got to thinking about the other part of his question. Was the whole EVEN/ODD thing a good design idea? And this is the question I am trying to answer in this post.
So what I wanted to determine if there was a way to ensure that only ODD values were inserted into an identity column. Sounds simple enough, right? Create the table with a starting value of 1, with an increment value of 2. That should do exactly my developer needed…right?
USE [mysandbox] ;
/****** Object: Table [dbo].[IncrementTest] Script Date: 2015-11-11 19:12:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IncrementTest](
[ID] [int] IDENTITY(1,2) NOT NULL,
[TestData] [varchar](50) NOT NULL
) ON [PRIMARY] ;
SET ANSI_PADDING OFF
GO
/****** Script for SelectTopNRows command from SSMS ******/
TRUNCATE TABLE [mysandbox].[dbo].[IncrementTest];
-- Load up 10 records. Data doesnt matter, just want to see the identity column.
DECLARE @cnt INT = 0
WHILE @cnt < 10
BEGIN
INSERT INTO [mysandbox].[dbo].[IncrementTest] (TestData)
VALUES ('Testdata');
SET @cnt = @cnt + 1;
END
SELECT *
FROM [mysandbox].[dbo].[IncrementTest];
The following script determines the MAX value in the table (19), it adds 1 to that number (20), and then it inserts a few more records after that just to prove my point. Hey wait, 20 is an even number! Will that even work? Not sure. I’m trying to punch holes in the EVEN/ODD design that my developer wants to use, remember.
-- MAX ID Should be 19 at this point
DECLARE @maxid INT = (
SELECT MAX(ID)
FROM [mysandbox].[dbo].[IncrementTest]
);
SET IDENTITY_INSERT [mysandbox].[dbo].[IncrementTest] ON;
INSERT INTO [mysandbox].[dbo].[IncrementTest] (
ID,
TestData
)
VALUES (
(@maxid + 1),
'Testdata with identity insert on'
);
SET IDENTITY_INSERT [mysandbox].[dbo].[IncrementTest] OFF;
-- Load up 10 records. Data doesn't matter, just want to see the identity column.
SET @cnt = 0;
WHILE @cnt < 10
BEGIN
INSERT INTO [mysandbox].[dbo].[IncrementTest] (TestData)
VALUES ('Testdata');
SET @cnt = @cnt + 1;
END
SELECT *
FROM [mysandbox].[dbo].[IncrementTest];
Is this a big deal? In my best DBA voice I think I have to give it an “it depends”. If it’s a temporary thing then you might be able to get away with it, but if it’s going to be a permanent solution the potential risk of it happening goes up. How bad will it break the application if it is expecting EVEN values and it finds ODD ones? How well is this design documented and how well would it be understood by future developers? All questions you should really consider when coming up with creative solutions like this. It’s always better to find the gaps in the design before it goes into production.