• Home
  • Troubleshoot
  • #Example
    • C
    • C++
    • Python
    • R-Programming
  • DSA
  • Quiz
  • Tutorial Videos
  • Home
  • Troubleshoot
  • #Example
    • C
    • C++
    • Python
    • R-Programming
  • DSA
  • Quiz
  • Tutorial Videos
  • #Deals
  • #News
  • #WiKi
  • #APPS
  • #Events
    • #WWDC
    • #I/O
    • #Ignite
  • #Let’s Talk

MyCodeTips mycodetips-newlogocopy1

  • Home
  • Troubleshoot
  • #Example
    • C
    • C++
    • Python
    • R-Programming
  • DSA
  • Quiz
  • Tutorial Videos
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)

 

 

  • Click to share on Reddit (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pocket (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
Written by Ranjan - 1355 Views
Tags | SQL
AUTHOR
Ranjan

I m Ranjan and Sharing my years of experience in Software Development. Love to code in Mobile apps (IOS, Android, Power Apps, Xamarin, Flutter), Machine Learning ( Beginner ), Dot Net, Databases ( SQL Server, MySql, SQLite), WordPress, Cloud Computing ( AWS, Azure, Google, MongoDB) and many more as required on project-specific. Besides this love to travel and cook.

You Might Also Like

mycodetips-newlogo2

Tips to protect your site from hackers

November 8, 2013
mycodetips database

How to start Sqlserver from Command prompt

June 11, 2013
mycodetips-newlogo2

How to update specific column in entire database using SQL

June 11, 2013
Next Post
Previous Post

Subscribe for updates

Join 5,734 other subscribers

whiteboard

Whiteboard(PRO)

whiteboard

Whiteboard(lite)

alphabets

Kids Alphabet

techlynk

Techlynk

techbyte

Do2Day

techbyte

Techbyte

Latest Posts

  • Frameworks of IOS
    Frameworks of IOS – Part ( I )
  • NSFileManager or NSPathUtilities
    NSFileManager or NSPathUtilities in Objective-C
  • Passing data between view controllers in Objective-C
    Passing data between view controllers in Objective-C
  • structures-classes-enum
    Structures and Classes in swift !
  • control-system-swift
    Control Flow in Swift
  • swift-concurrency-await
    Concurrency in Swift
  • time-complexity-dsa
    The Term Time Complexity in DSA
  • objective-c-datatypes1
    Objective-C Data Types
  • Convert-jpeg-word
    Convert JPG to Word – Tips You Should Try!
  • objective-c-control-statements2
    Objective-C control statements and loops !

Quick Links

  • #about
  • #myapps
  • #contact
  • #privacy

Other Websites

  • #myQuestions
  • #myBhojanalaya
  • #gadgetFacts
  • #ifscCodesDB

Tag Cloud

Android Android Studio API APP Programming Apps ARC asp.net blogging Browser Config CSS DATABASE DFD error Features GUI HTML HTML5 IDE IIS installation Interview Questions IOS iPhone javascript Mac objective-c OneDrive OS Programming quicktips SDK SEO Settings SMO SQL swift swiftUI Teams Tips & Tricks Tools UI Web Wordpress Xcode

©mycodetips.com