How to check a sequence number in a column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to check a sequence number in a column?

Author

Topic

willianto
Starting Member

5 Posts

Posted - 2010-08-20 : 05:34:37

Greetings all...

I have Transaction table and there is a column with sequence number. Yet, there are times, when the front end application is canceling their transaction, and the number is simply skipped.

Yes, I know that it is not suppose to happen, and I should prevent it in the first place. Unfortunatelly this already happen. The data is already there, and -- obviously -- to re-number all transaction afterwards is out of the question.

Q: Can anybody help or give me a hint on how to create a T-SQL statement to locate invalid sequence number(s)? It is invalid if it skip a number(s) and it is also invalid if there is more than one number exist in the column.

Thanks in advance
:)
Regards,
Willianto

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-20 : 07:50:50

Can you give examples for what is wrong and what is right?

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 08:24:35

Search for "Find gaps" in this forum

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-20 : 09:11:29

[code]DECLARE @Sample TABLE
(
ID INT
)

INSERT @Sample
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 9

SELECT TOP(1) FirstMissingID
FROM (
SELECT ID + 1 AS FirstMissingID
FROM @Sample

EXCEPT

SELECT ID
FROM @Sample
) AS d
ORDER BY FirstMissingID[/code]

N 56°04'39.26"
E 12°55'05.63"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-20 : 10:14:51

How is the "sequence number" generated?

If it is an IDENTITY column, then skipping a number when a transaction fails is completely normal, and SQL Server is designed to work this way.

Why is it important to have an unbroken sequence?

CODO ERGO SUM

willianto
Starting Member

5 Posts

Posted - 2010-08-20 : 15:09:01

@Peso: Thanks for the code :) It does locate the first missing sequence number. But, it looks like you left me with homeworks to find the rest.

@michael: The sequence number is generated by a legacy DOS application (yup. DOS

). I agree that it is normal to have a gap if a transaction fail; problem is each number represent one sales, and some department (accounting and marketing) needs to know exactly which transaction was fail (for cross-checked) and why it failed (for evaluation).

@madhivanan: Thanks for the hint. I searched and found several helpful hint. I decided to modify michael's code in [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46495[/url]. And it works like a charm. Thanks you, and thanks michael. :)

@webfred: Sorry, I didn't have a chance to go online and explain my problem. But it is solved. :)

Regards,
Willianto

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources