[MSSQL] 원하는 순서로 정렬된 Identity 컬럼 추가

Identity 컬럼이 없던 테이블에 Identity컬럼을 추가 할 경우

임의의(의도치 않은) 순서로 번호가 매겨지게 된다

이를 특정 순서로 정렬하기 위해 여러 테스트를 하던 도중

SELECT * FROM [대상테이블]

매번 할 때와 같은 순서로 번호가 매겨지는걸 발견했다.

따라서 Identity 컬럼을 추가하기 전에 Order by를 하지 않고도 원하는 순서로 정렬되어 출력하도록 하면 되는데

이를 유도하기 위해 해당 테이블에 모든 제약조건이나 인덱스를 삭제하고 원하는 정렬 순서로 Clustered 인덱스를 만들게 되면

(Clustered Index는 테이블내 데이터를 미리 정렬해 둠으로)

Order by없이 정렬된 순서로 출력되기를 유도 할 수 있게 된다(어디까지나 유도이기 때문에 전, 후 확인이 필요하다)

작업 후에 지웠던 제약조건이나 인덱스를 만들고 확인만 하면 끝!

다음은 예제 쿼리

--정렬된 Identity 컬럼 추가
If Object_ID('dbo.Test') Is Not Null
    Drop Table dbo.Test
Go


Create Table dbo.Test
(
     Data1 Int
    ,Data2 Int
    ,Data3 Int
)
Go


Insert Into dbo.Test(Data1, Data2, Data3) Values(43, 123, 42)
Go

Insert Into dbo.Test(Data1, Data2, Data3) Values(23, 5, 52)
Go

Insert Into dbo.Test(Data1, Data2, Data3) Values(154, 12, 11)
Go

Insert Into dbo.Test(Data1, Data2, Data3) Values(23, 23, 23)
Go

Insert Into dbo.Test(Data1, Data2, Data3) Values(42, 12, 53)
Go

Insert Into dbo.Test(Data1, Data2, Data3) Values(533, 21, 521)
Go


--기존에 인덱스가 있다면 인덱스를 삭제하고 원하는 순서로 인덱스 생성하고 작업 완료후 인덱스를 정리
Create Clustered Index IDX_Test_Data1_Data2_Data3 On dbo.Test(Data1 Asc, Data2 Asc, Data3 Asc)
Go


Alter Table dbo.Test Add A Int Identity(1,1)
Go

Select *
From dbo.Test
   
Select *
From
(
    Select *,ROW_NUMBER() Over (Order By Data1 Asc, Data2 Asc, Data3 Asc) As B
    From dbo.Test
) As Test
Where A<>B
Order By A Asc,B Asc
Go


If Object_ID('dbo.Test') Is Not Null
    Drop Table dbo.Test
Go