• Home
  • Basics
  • DSA
  • MAD
  • Concept
  • Practice
  • Misc
    • Tips
    • QA’s
    • Misc
  • Course
  • Home
  • Basics
  • DSA
  • MAD
  • Concept
  • Practice
  • Misc
    • Tips
    • QA’s
    • Misc
  • Course
  • APPS |
  • News |
  • WWDC |
  • I/O |
  • Ignite |
  • Advertise |
  • Let’s Talk

MyCodeTips mycodetips-newlogocopy1

  • Home
  • Basics
  • DSA
  • MAD
  • Concept
  • Practice
  • Misc
    • Tips
    • QA’s
    • Misc
  • Course
Database

How to Add Identity Column to Table Based on Order of Another Column

How to Add Identity Column to Table Based on Order of Another Column

USE tempdb

GO
-- Create Table
CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
GO
-- Insert Data
INSERT INTO TestTable (Col1, Col2)
VALUES (33, 'Ranjan');
INSERT INTO TestTable (Col1, Col2)
VALUES (22, 'shyam');
INSERT INTO TestTable (Col1, Col2)
VALUES (11, 'Tester');
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Add Identity Column
ALTER TABLE TestTable
ADD ID INT IDENTITY(1, 1)
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Clean up
DROP TABLE TestTable
GO

OR

USE tempdb

GO
-- Create Table
CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
GO
-- Insert Data
INSERT INTO TestTable (Col1, Col2)
VALUES (33, 'Ranjan');
INSERT INTO TestTable (Col1, Col2)
VALUES (22, 'Shyam');
INSERT INTO TestTable (Col1, Col2)
VALUES (11, 'Tester');
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Create Clustered Index on Column ID
CREATE CLUSTERED INDEX IX_TestTable ON dbo.TestTable
(Col1 ASC)
GO
-- Add Identity Column
ALTER TABLE TestTable
ADD ID INT IDENTITY(1, 1)
GO
-- Select Data
SELECT *
FROM TestTable
GO
-- Clean up
DROP TABLE TestTable
GO
492788f0a7 b gif host blog sqlauthority

 

Is there any workaround to do the same?”

I quickly created clustered index in ASC order on Col1 and it ordered the table as expected and later added the identity column there. Let us see the script to get the desired result.

 

USE tempdb
GO

 

-- Create Table
CREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))
GO

 

-- Insert Data
INSERT INTO TestTable (Col1, Col2)
VALUES (33, 'Ranjan');
INSERT INTO TestTable (Col1, Col2)
VALUES (22, 'Shyam');
INSERT INTO TestTable (Col1, Col2)
VALUES (11, 'Tester');
GO

 

-- Select Data
SELECT *
FROM TestTable
GO

 

-- Create Clustered Index on Column ID
CREATE CLUSTERED INDEX IX_TestTable ON dbo.TestTable
(Col1 ASC)
GO

 

-- Add Identity Column
ALTER TABLE TestTable
ADD ID INT IDENTITY(1, 1)
GO

 

-- Select Data
SELECT *
FROM TestTable
GO

 

-- Clean up
DROP TABLE TestTable
GO

 

 

Here is one more idea:

Step1 – Create temporary clustered index on “date time column in descending order”

CREATE CLUSTERED INDEX ix_YourTableTEMP ON YourTable (DateTimeColumn DESC)

Step2 – Add identity column. Now the IDs should be in order of previously created index – although I don’t think there is 100% guarantee on this.

ALTER TABLE YourTable ADD IdColumn INT IDENTITY(1,1)

Step3 – Drop temporary index

DROP INDEX ix_YourTableTEMP ON YourTable

Step4 – Create new clustered PK on new column

ALTER TABLE YourTable
ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (IdColumn)

 

 

Written by Ranjan - June 11, 2013 - 2454 Views
Tags | SQL
AUTHOR
Ranjan

This website is basically about of what we learnt from my years of experience as a software engineer on software development specifically on mobile application development, design patterns/architectures and its changing scenarios, security, troubleshooting, tools, tips&tricks and many more.

You Might Also Like

1386376017 database

Tips for SQL Query Optimization

December 5, 2013
mycodetips dotnet

Tips for Database ConnectionStrings of Various Provider

December 3, 2013
mycodetips-newlogo2

Tips for performance tuning of databases

November 11, 2013
Next Post
Previous Post

Support us

  • Exploring Single Point Failures: Causes and Impacts
  • Exploring the Pros and Cons of Primitive Data Types
  • Essential Coding Standards and Best Practices for Clean Code
  • What Business Problems Solves in Low Level Design (LLD)
  • SRP : Single Responsibility Principle in Swift and Objective-C
whiteboard

Whiteboard(PRO)

whiteboard

Whiteboard(lite)

alphabets

Kids Alphabet

do2day

Do2Day

MyCodeTips

©mycodetips.com