SQL script to LOOP in and find sequence gap on Unique column

Question

SQL script to LOOP in and find sequence gap on Unique column

LearnSQL!!!

SSC Eights!

Points: 825
More actions
April 15, 2014 at 2:22 pm

#290258

Need help !!!
Example of table structure :
CREATE TABLE [dbo].[MaintenanceTable](
[TableName] [varchar](50) NOT NULL,
[Note] [varchar](250) NULL,
[MaintenanceOrder] [int] NOT NULL,
CONSTRAINT [PK_MaintenanceTable] PRIMARY KEY CLUSTERED
(
[TableName] ASC,
)WITH ON [PRIMARY],
CONSTRAINT [UNQ_MaintenanceTable_MaintenanceOrder] UNIQUE NONCLUSTERED
(
[MaintenanceOrder] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Table results:
TableNameNote MaintenanceOrder
tablename1NULL101
tablename2NULL105
tablename3NULL106
tablename4NULL110
tablename5NULL150
I need a loop in script which checks the range of MaintenanceOrder number and updates the Maintenanceorder number in sequence if there is a gap between them. Is this possible ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100942 More actions · Answer 1

It's possible but it's huge overhead you should probably avoid.

You can use ROW_NUMBER() to sequentially number the data when you read it rather than having to physically update the values in the row.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

My question, why do you need to eliminate gaps in the sequence numbering?

LearnSQL!!! SSC Eights! Points: 825 More actions · Answer 3

We did some DBA maintenance and deletes some unnecessary row because of that there are gaps. I can update the rows on the table manually to arrange it in sequence but I wanted a looping script that would help when I have to work with large number of rows.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Doesn't answer the question. Is there a problem due to the gaps as a result of the deletes or do you want to do this for cosmetic reasons?

LearnSQL!!! SSC Eights! Points: 825 More actions · Answer 5

Sorry for not answering clearly. There are no issues, I just prefer to have maintenanceorder column to be in sequence.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

If it isn't a problem I wouldn't worry about it.

Just mho.

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 7

I'm struggling to understand the business case where re-ordering the numbers would be desirable. So if they want to look up the printed MaintenanceOrder 105, surprise, it's actually MaintenanceOrder 102 now?

Or are you just looking for the next available number in the sequence so you can use it when creating the next MaintenanceOrder record?

Select Top 1 MT.MaintenanceOrder + 1 As MaintenanceOrder

From MaintenanceTask MT

Where Not Exists (Select Top 1 0 From MaintenanceTask MT2 Where MT2.MaintenanceOrder = (MT.MaintenanceOrder + 1))

Order By MT.MaintenanceOrder

If that was the case, don't. It will fail the moment any two people enter an order at the same time. Microsoft doesn't try to guarantee it with their identity columns, or with their sequence data types, and so you shouldn't try to do it either.

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 8

Agreeing with Cody here.

I learned the hard way in my first job that trying to "fix" the data for purely cosmetic reasons can and does screw with the end users. Do not do it! It will get you into a lot of trouble when the users find a missing maintenance order that they are sure should still exist (i.e., 105 disappears because it's suddenly 102).

Cleaning up the database unnecessarily is bad. Clean up the things you know for a fact will cause performance problems (indexes, stats, bad schema / security issues). But cleaning up data without user buy-in? It leads to Bad Things. For the sake of your sanity and your job, Don't Do It!

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

LearnSQL!!! (4/15/2014)
Need help !!!
Example of table structure :
CREATE TABLE [dbo].[MaintenanceTable](
[TableName] [varchar](50) NOT NULL,
[Note] [varchar](250) NULL,
[MaintenanceOrder] [int] NOT NULL,
CONSTRAINT [PK_ReplicationTableMaintenance] PRIMARY KEY CLUSTERED
(
[TableName] ASC,
)WITH ON [PRIMARY],
CONSTRAINT [UNQ_MaintenanceTable_MaintenanceOrder] UNIQUE NONCLUSTERED
(
[MaintenanceOrder] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Table results:
TableNameNote MaintenanceOrder
tablename1NULL101
tablename2NULL105
tablename3NULL106
tablename4NULL110
tablename5NULL150
I need a loop in script which checks the range of MaintenanceOrder number and updates the Maintenanceorder number in sequence if there is a gap between them. Is this possible ?

Getting back to the original problem...

First, please be a bit more careful when posting your code. Test it before you post it. If it doesn't work for you, it won't work for us.

Second, post your data in a readily consumable format so people can concentrate on providing a solution to your problem.

For more information on both of those suggestions and for how to get much better answers much more quickly, please read and heed the article at the first link under "Helpful Links" in my signature line below.

Sorry for not answering clearly. There are no issues, I just prefer to have maintenanceorder column to be in sequence.

That would be the 3rd suggestion. Remember that we can't read minds. 😉

On to your problem...

If the MaintenanceOrder column is truly meaningless except to express an order of the rows, then you probably won't run into all the problems the others are talking about. With all that in mind, here's one solution and it sure doesn't need an explicit loop. The code speaks for itself. I hope this is what you were looking for.

CREATE TABLE [dbo].[MaintenanceTable](

[TableName] [varchar](50) NOT NULL,

[Note] [varchar](250) NULL,

[MaintenanceOrder] [int] NOT NULL,

CONSTRAINT [PK_ReplicationTableMaintenance] PRIMARY KEY CLUSTERED

(

[TableName] ASC

) ON [PRIMARY],

CONSTRAINT [UNQ_MaintenanceTable_MaintenanceOrder] UNIQUE NONCLUSTERED

(

[MaintenanceOrder] ASC

) ON [PRIMARY]

--===== Create some readily consumable data for the table.

INSERT INTO dbo.MaintenanceTable

(TableName,Note,MaintenanceOrder)

SELECT 'tablename1',NULL,101 UNION ALL

SELECT 'tablename2',NULL,105 UNION ALL

SELECT 'tablename3',NULL,106 UNION ALL

SELECT 'tablename4',NULL,110 UNION ALL

SELECT 'tablename5',NULL,150

;

--===== Show the original data

SELECT * FROM dbo.MaintenanceTable

;

--===== Do the update through an updateable CTE

WITH

cteEnumerate AS

(

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY MaintenanceOrder)+100

,MaintenanceOrder

FROM dbo.MaintenanceTable

)

UPDATE cteEnumerate

SET MaintenanceOrder = RowNum

;

--===== Show the original data

SELECT * FROM dbo.MaintenanceTable

;

Here are the results from the final SELECT...

TableName Note MaintenanceOrder

---------- ---- ----------------

tablename1 NULL 101

tablename2 NULL 102

tablename3 NULL 103

tablename4 NULL 104

tablename5 NULL 105

(5 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LearnSQL!!! SSC Eights! Points: 825 More actions · Answer 10

Thanks Jeff that is what I was looking for. I will definitely post detail issues and information from next time when I post any issues will data ready for the test.