re-numbering a table column - SQL Server Forums

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)

re-numbering a table column

Author

Topic

Omego2K
Starting Member

9 Posts

Posted - 2008-09-08 : 17:20:46

Hi, I haven't used SQL very often. However, I ran into a small programming problem that I think is best solved withing the stored proc that I am using instead of doing it through code.

I have a table(call it tableB), the table has a certain int column and a column to identify a row in another table(call it tableA) for each identification ID column for the row in TableA the int column cannot contain duplicate numbers and the numbers go in order 1, 2, 3, 4 etc.

I have a stored proc which deletes a certain row from that table based on the passed row ID which is an identity column. I need help with understanding how in the delete procedure I could renumber the rows for which the deleted row belonged to based on the ID of the other table.

So say I have 4 rows for that specific row in TableA. So the int column would be numbered 1-4 .

Now I delete a row which has the value 3 for it's int column. Now I have 3 columns for that specific tableA row numbered: 1,2,4.

I need to understand how to re-number those columns to be 1,2,3

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 17:23:22

Do not bother with renumbering the data. It is pointless to do this. It will also be hard if you've got foreign keys for that column.

And again it's pointless to do this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Omego2K
Starting Member

9 Posts

Posted - 2008-09-08 : 18:00:27

I need to do it because that column is used in my app. I plan to let users move around those rows and save them.

Also there are no foreign keys on that column. It's just data that I will need

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 18:01:16

That doesn't mean you need to renumber the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Omego2K
Starting Member

9 Posts

Posted - 2008-09-08 : 18:07:31

Well that's what my manager wants. How else would you do it? I mean the column data is outputed to the user. And also used in the app. I mean I can do it through the app itself with programming. But it seems much easier to do it on the server.

Thanks for the quick replies by the way.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 18:13:22

This will not be easy nor efficient. In fact, it will be very slow if you delete a row at the "beginning" of the table" and have to reorder millions of rows.

Does efficiency not matter to your manager?

How many rows are there going to be in this table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Omego2K
Starting Member

9 Posts

Posted - 2008-09-08 : 18:31:49

millions? it's unlikely that more than 10 rows will need to be re-ordered. Without going into detail this list represents items that a person will physically carry so millions or thousands or hundreds is out of the question. Like I said more than 10 is unlikely.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 18:37:13

But how many rows will there be in the table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Omego2K
Starting Member

9 Posts

Posted - 2008-09-08 : 22:24:33

obviously a lot, ranging in the 10s of thousands. However, I doubt that all of them will be updated at once and there will only be maybe 100s of users. So it won't be a huge hit. But in any event could you help me understand how I would approach this. We already insert these items one by one even when a user adds 20 of them at once the app hits the db 20 times for each item.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-08 : 22:51:42

One option instead of re-numbering is to use the ROW_NUMBER() function & display the ROW_NUMBER() to your users & sort by your int col in background.

Omego2K
Starting Member

9 Posts

Posted - 2008-09-08 : 23:18:27

That's what I was thinking was going to be done in the first place, however my manager wants every piece of info in the db

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 23:59:14

quote:
Originally posted by Omego2K

obviously a lot, ranging in the 10s of thousands. However, I doubt that all of them will be updated at once and there will only be maybe 100s of users. So it won't be a huge hit. But in any event could you help me understand how I would approach this. We already insert these items one by one even when a user adds 20 of them at once the app hits the db 20 times for each item.

So what would happen in this scenario:

Original data:
1
2
3
...
9999
10000
10001

Someone deletes row 2. Do you have to do n-1 for 3-10001?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 04:19:56

I'll give him what he want. None of the arguments are biting.
And Op states his boss wants this way, so maybe OP is safe?

DECLARE	@Sample TABLE
	(
		Data INT PRIMARY KEY CLUSTERED
	)

INSERT	@Sample
SELECT	Number
FROM	master..spt_values
WHERE	Type = 'P'

DELETE
FROM	@Sample
WHERE	Data = 4

SELECT	Data
FROM	@Sample		-- Record with Data ID 4 is now missing

DECLARE	@Data INT

UPDATE	@Sample
SET	@Data = Data =	CASE
				WHEN @Data IS NULL THEN 0
				ELSE @Data + 1
			END

SELECT	Data
FROM	@Sample		-- Record with Data ID 4 is not missing

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 04:27:07

When working on real table, use query hint for index


UPDATE	x
SET	@Data = Data =	CASE
				WHEN @Data IS NULL THEN 0
				ELSE @Data + 1
			END
--FROM	YourTable AS x WITH (INDEX (0))
FROM	YourTable AS x WITH (INDEX ({Name of clustered index here}))

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-09 : 04:52:12

<<
I mean I can do it through the app itself with programming.
>>

Do it there which would be more effecient

Madhivanan

Failing to plan is Planning to fail

Omego2K
Starting Member

9 Posts

Posted - 2008-09-09 : 11:07:25

quote:
Originally posted by Peso

When working on real table, use query hint for index

UPDATE x
SET @Data = Data = CASE
WHEN @Data IS NULL THEN 0
ELSE @Data + 1
END
--FROM YourTable AS x WITH (INDEX (0))
FROM YourTable AS x WITH (INDEX ({Name of clustered index here}))

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

thank you, I'll test this out. The reason I don't want to do it programmatically is because I would still need to update that columns number order. In any event the processing would be done, whether on the web server or the db server. From what needs to be done on the web server it would seem that the db server would take less of a tax.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-09-09 : 12:10:08

One minor flaw in the "replies" here is that they don't seem to be asking what purpose the seequence number seems to perform within the application? is it for some sort of ordering?

can this ordering be achieved in a less initrusive manner?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-09 : 12:30:56

How are you going to handle the foreign key data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 12:34:04

quote:
Originally posted by Omego2K 09/08/2008 : 18:00:27

Also there are no foreign keys on that column. It's just data that I will need

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-09 : 12:53:42

I'm confused why tableA and tableB were mentioned in the original post then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jordanam
Yak Posting Veteran

62 Posts

Posted - 2008-09-09 : 13:00:30

If the int column was made an identity, and it sounds like it basically is, you could reseed it after a delete/update, no? It's sloppy, but it doesn't sound to me like the integrity of that column matters to table B...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources