March 6, 2016 at 3:09 pm
bismsit.29 (3/3/2016)
Hi Alan,Thank you very much for replying back so early.
Its sort of a business requirement that's why i want to reuse them also this value can hold till 99999 which is why i need to reuse any inactive ids. hope that clarifies the doubt.
with regards,
Sounds like reusing deactivated car plate numbers for new car registrations.
Would be a bright idea, don't you think?
But they do not do it anywhere in the world (at least in more or less developed part of it).
Wonder why?
Do a research on the subject and you'll find the list of all the problems you'll have in future if you proceed with reusable ID's.
_____________
Code for TallyGenerator
March 6, 2016 at 3:33 pm
@bismit29,
I still need answers to the questions in my last post so that I or anyone else can help you wrap this up.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2016 at 3:35 pm
Sergiy (3/6/2016)
bismsit.29 (3/3/2016)
Hi Alan,Thank you very much for replying back so early.
Its sort of a business requirement that's why i want to reuse them also this value can hold till 99999 which is why i need to reuse any inactive ids. hope that clarifies the doubt.
with regards,
Sounds like reusing deactivated car plate numbers for new car registrations.
Would be a bright idea, don't you think?
But they do not do it anywhere in the world (at least in more or less developed part of it).
Wonder why?
Do a research on the subject and you'll find the list of all the problems you'll have in future if you proceed with reusable ID's.
My "favorites" are the people that think SSNs in the U.S. are recycled even though it clearly states they are not on the SSA website. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2016 at 2:51 pm
bismsit.29 (3/3/2016)
Hi Alan,Thank you very much for replying back so early.
Its sort of a business requirement that's why i want to reuse them also this value can hold till 99999 which is why i need to reuse any inactive ids. hope that clarifies the doubt.
with regards,
Keeping in mind Jeff's earlier comment about "Death by SQL" I would advocate for a different approach.
That said, and since people mentioned things like SEQUENCE and triggers, I thought this would be a fun little exercise to show how to put these things together in a solution. Here's what a solution would look like. The code below using a the SEQUENCE object and a trigger to do what you asked about, note my comments.
SET NOCOUNT ON;
USE tempdb
GO
/****************************************************************************************
0. Prep
****************************************************************************************/;
IF OBJECT_ID('dbo.TestNoDelete') IS NOT NULL DROP TRIGGER dbo.TestNoDelete;
IF OBJECT_ID('dbo.AddTestValue') IS NOT NULL DROP PROC dbo.AddTestValue;
IF OBJECT_ID('dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
IF OBJECT_ID('dbo.seq1To99999') IS NOT NULL DROP SEQUENCE dbo.seq1To99999;
/****************************************************************************************
1. How to get max(id) + 1 until you get to 99999
****************************************************************************************/;
-- You can use the SEQUENCE object to handle the the initial ID's from 1 to 99999
CREATE SEQUENCE dbo.seq1To99999
START WITH 1
INCREMENT BY 1
MAXVALUE 99999;
-- note that, because it's not set to "cycle" you can only do 99999 values and it's done
-- how to apply the SEQUENCE object to your table
CREATE TABLE dbo.Test
(
SomeID int DEFAULT(NEXT VALUE FOR dbo.seq1To99999) primary key,
SomeValue varchar(36),
IsActive bit NOT NULL DEFAULT(1)
);
-- Since we'll be using IsActive in the WHERE clause of future queries
CREATE INDEX nc_xxx ON dbo.Test(IsActive)
WHERE IsActive = 0;
GO
/****************************************************************************************
2. Test the Sequence object...
****************************************************************************************/
INSERT dbo.Test(SomeValue)
SELECT TOP(99998) NEWID() -- just some random value
FROM sys.all_columns a, sys.all_columns b
-- test, note that you get 99998 records with SomeID = 1 through 99998
SELECT * FROM dbo.Test;
/****************************************************************************************
3. Getting to lowest inactive record
****************************************************************************************/
-- Let's create some random inactive rows (low IDs so they're easy to find for this demo)
UPDATE dbo.test
SET IsActive = 0
WHERE SomeID IN (5,10); -- NOTE We're only doing 2 innactive ID's
-- note that the lowest (MIN) inactive ID (someID) is 5;
-- SELECT * FROM dbo.test
SELECT MIN(SomeID)
FROM dbo.Test
WHERE IsActive = 0;
GO
/****************************************************************************************
4. Dealing with "if we reach 99999 then it will start again with the least inactive id."
****************************************************************************************/;
-- First, for this to work you could not allow records to be deleted.
CREATE TRIGGER dbo.TestNoDelete ON dbo.Test INSTEAD OF DELETE AS BEGIN PRINT 'Nope'; END;
GO
-- Let's test the trigger..
DELETE FROM dbo.test WHERE SomeID = 1; -- this will just return the text, "Nope".
-- See, it's still there
SELECT * FROM dbo.Test WHERE SomeID = 1;
GO
-- Creating a stored proc that satisfies your request
-- For this solution to work you would need to use this stored proc
CREATE PROC dbo.AddTestValue(@NewValue varchar(30))
AS
BEGIN
DECLARE @NewID int;
-- If SomeID has not reached 99999 use the next SEQUENCE value
IF NOT EXISTS (SELECT SomeID FROM dbo.Test WHERE SomeID = 99999)
INSERT dbo.Test(SomeValue, IsActive) VALUES (@NewValue, 1)
ELSE -- otherwise find the lowest inactive ID and re-use it
BEGIN
SELECT @NewID = MIN(SomeID) FROM dbo.Test WHERE IsActive = 0;
IF @NewID IS NULL PRINT 'No Available ID values. Sorry :('
ELSE
UPDATE dbo.Test
SET SomeValue = @NewValue, IsActive = 1
WHERE SomeID = @NewID;
END
END
GO
-- let's test for when the sequence object has not reached 99999
EXEC dbo.AddTestValue 'SomeNewValue99999';
SELECT * FROM dbo.test WHERE SomeValue = 'SomeNewValue99999';
-- Next let's do another now that the SEQUENCE #'s are all used up
EXEC dbo.AddTestValue 'AnotherNewValue';
SELECT * FROM dbo.test;
-- Let's use up that final inactiveID
EXEC dbo.AddTestValue 'YetAnotherNewValue';
SELECT * FROM dbo.test;
-- For when SEQUENCE is all used up and there's no inactive IDs
EXEC dbo.AddTestValue 'Oops';
-- Itzik Ben-Gan 2001
March 9, 2016 at 7:55 am
I don't think anyone has actually managed to get the relevant information out of the OP or provide a solution. [difficult to do one without the other]
It seems reasonable without any further context that there is a downstream system that will only accept unique integer values less than 100,000 and that currently there is no business case where this system would need more records than that. I don't think it has been explicitly stated that the reference ID needs to be permanently tied to the record; that is just the way the OP has interpreted the requirements and capability of MSSQL.
Can I propose that the solution may be as simple as ROW_NUMBER() OVER(ORDER BY [fields to force a sequence])
This will generate a unique sequential number for each row in the results set FOR ANY GIVEN RUN but the numbers may (and in fact WILL) change as records move into and out of valid state.
If audit-ability is required then you could wrap this into a stored procedure and hold the real record key with the runID and rownumber in a separate reference table. This way you can prove all the records and their order in a particular output set and there is no need to mess around with ID numbers on the master table.
March 21, 2016 at 1:25 am
aaron.reese (3/9/2016)
I don't think anyone has actually managed to get the relevant information out of the OP or provide a solution. [difficult to do one without the other]It seems reasonable without any further context that there is a downstream system that will only accept unique integer values less than 100,000 and that currently there is no business case where this system would need more records than that. I don't think it has been explicitly stated that the reference ID needs to be permanently tied to the record; that is just the way the OP has interpreted the requirements and capability of MSSQL.
Can I propose that the solution may be as simple as ROW_NUMBER() OVER(ORDER BY [fields to force a sequence])
This will generate a unique sequential number for each row in the results set FOR ANY GIVEN RUN but the numbers may (and in fact WILL) change as records move into and out of valid state.
If audit-ability is required then you could wrap this into a stored procedure and hold the real record key with the runID and rownumber in a separate reference table. This way you can prove all the records and their order in a particular output set and there is no need to mess around with ID numbers on the master table.
From what I can see...
Jeff Moden (3/6/2016)
@bismit29,I still need answers to the questions in my last post so that I or anyone else can help you wrap this up.
... the OP has left the building. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply