Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL script to LOOP in and find sequence gap on Unique column Expand / Collapse
Author
Message
Posted Tuesday, April 15, 2014 2:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:19 AM
Points: 7, Visits: 49
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:
TableName Note MaintenanceOrder
tablename1 NULL 101
tablename2 NULL 105
tablename3 NULL 106
tablename4 NULL 110
tablename5 NULL 150

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 ?
Post #1562047
Posted Tuesday, April 15, 2014 3:49 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:16 PM
Points: 1,970, Visits: 2,911
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1562070
Posted Tuesday, April 15, 2014 6:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
My question, why do you need to eliminate gaps in the sequence numbering?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1562089
Posted Tuesday, April 15, 2014 8:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:19 AM
Points: 7, Visits: 49
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.
Post #1562095
Posted Tuesday, April 15, 2014 8:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1562104
Posted Tuesday, April 15, 2014 9:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:19 AM
Points: 7, Visits: 49
Sorry for not answering clearly. There are no issues, I just prefer to have maintenanceorder column to be in sequence.
Post #1562105
Posted Tuesday, April 15, 2014 9:51 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
If it isn't a problem I wouldn't worry about it.
Just mho.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1562109
Posted Tuesday, April 15, 2014 11:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:58 AM
Points: 213, Visits: 827
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.
Post #1562114
Posted Wednesday, April 16, 2014 6:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:48 AM
Points: 7,131, Visits: 6,292
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1562256
Posted Saturday, April 19, 2014 3:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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:
TableName Note MaintenanceOrder
tablename1 NULL 101
tablename2 NULL 105
tablename3 NULL 106
tablename4 NULL 110
tablename5 NULL 150

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]
) 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563294
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse