Q
Problem solve Get help with specific problems with your technologies, process and projects.

Routine to push existing records up or down

I need to ask help in writing a routine which will push existing records up or down. What I mean is if I have a table with following columns/data:
Col1
-------
1
2
3
6
If user sends 4, I just add it in without pushing 6 down, but if user sends 3, then 3 becomes 4, but 6 remains as is.

As opposed to creating code to manage the process, I would consider creating the column with a numeric data type, i.e. integer, and subsequently with a unique clustered index. A clustered index physically sorts the data based on the sequential data values. Adding a unique clause ensures duplicate values are not stored in the column. SQL Server manages the functionality itself and you do not have to write any additional code to maintain the order and uniqueness of the data. I believe the syntax you would need to create this functionality on the existing table and column would be:

USE DatabaseName
GO
CREATE UNIQUE CLUSTERED INDEX IndexName
   ON TableName (ColumnName)
GO
For additional information reference the CREATE INDEX article in SQL Server 2000 Books Online.

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close