Drawing the world RSS FEED

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

USE master;
GO

SET NOCOUNT ON;

DECLARE @FilePath nvarchar(4000);
SET @FilePath = '[CSV 파일 경로]';

IF OBJECT_ID('tempdb.dbo.FirstRow', 'U') IS NOT NULL
	DROP TABLE tempdb.dbo.FirstRow;

CREATE TABLE tempdb.dbo.FirstRow
(
	CSV nvarchar(100)
);

DECLARE @Query nvarchar(4000);

SET @Query =
'
BULK INSERT tempdb.dbo.FirstRow FROM ''' + @FilePath + '''
WITH
(
	  FIRSTROW = 1
	 ,LASTROW = 1
);
';

EXECUTE sp_executesql @Query;


SELECT @Query =
'
IF OBJECT_ID(''tempdb.dbo.CSV'', ''U'') IS NOT NULL
	DROP TABLE tempdb.dbo.CSV;
CREATE TABLE tempdb.dbo.CSV
(
	' + REPLACE(CSV, ',', ' nvarchar(4000),') + ' nvarchar(4000)
)'
FROM tempdb.dbo.FirstRow;

EXECUTE sp_executesql @Query;




SET @Query =
'
BULK INSERT tempdb.dbo.CSV FROM ''' + @FilePath + '''
WITH
(
	   FIRSTROW = 2
	  ,FIELDTERMINATOR = '',''
	  ,ROWTERMINATOR = ''\n''
);
';

EXECUTE sp_executesql @Query;


IF OBJECT_ID('tempdb.dbo.#CSV', 'U') IS NOT NULL
	DROP TABLE #CSV;

SELECT *
INTO #CSV
FROM tempdb.dbo.CSV;

IF OBJECT_ID('tempdb.dbo.FirstRow', 'U') IS NOT NULL
	DROP TABLE tempdb.dbo.FirstRow;
IF OBJECT_ID('tempdb.dbo.CSV', 'U') IS NOT NULL
	DROP TABLE tempdb.dbo.CSV;
GO

SELECT * FROM #CSV;

SQL Server 2000 버젼용

SELECT
     SPID
    ,Status
    ,LogiName AS Login
    ,HostName
    ,blocked AS BlkBy
    ,DB_NAME(dbid) AS DBName
    ,cmd AS Command
    ,cpu AS CPUTime
    ,physical_io AS DiskIO
    ,last_batch AS LastBatch
    ,program_name AS ProgramName
    ,DATEDIFF(MI, last_batch, GETDATE())
FROM master..sysprocesses
WHERE SPID <> @@SPID
  AND DATEDIFF(SS, last_batch, GETDATE()) >= 5
  AND (
		   Blocked <> 0
		OR SPID IN
			(
				SELECT blocked
				FROM master..sysprocesses
				WHERE blocked <> 0
				  AND blocked <> @@SPID
			)
	  )
ORDER BY SPID;

SELECT
     a.SPID
    ,a.Status
    ,a.LogiName AS Login
    ,a.HostName
    ,a.Blocked AS BlkBy
    ,DB_NAME(a.dbid) AS DBName
    ,a.Cmd AS Command
    ,a.Cpu AS CPUTime
    ,a.Physical_io AS DiskIO
    ,a.Last_Batch AS LastBatch
    ,a.Program_Name AS ProgramName
    ,b.Text
FROM master.sys.sysprocesses AS a WITH(NOLOCK)
    CROSS APPLY master.sys.dm_exec_sql_text(a.Sql_Handle) AS b
WHERE a.SPID <> @@SPID
  AND
  (
       DATEDIFF(SS, a.Last_Batch, GETDATE()) >= 5
    OR 
	(
		   a.Blocked <> 0
		OR a.SPID IN
			(
				SELECT a.Blocked
				FROM master.sys.sysprocesses
				WHERE a.Blocked <> 0
				  AND a.Blocked <> @@SPID
			)
	)
  )
ORDER BY a.SPID

SELECT
	 a.Session_ID
	,a.Blocking_Session_ID
	,a.Status
	,c.Login_Name
	,c.Host_Name
	,d.client_net_address
	,d.local_tcp_port
	,c.last_request_start_time
	,DB_NAME(a.Database_ID) AS DBName
	,a.Command
	,a.Reads
	,a.Writes
	,a.CPU_Time
	,a.Logical_Reads
	,c.program_name AS ProgramName
	,b.text
FROM master.sys.dm_exec_requests AS a WITH(NOLOCK)
	CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) AS b
	INNER JOIN sys.dm_exec_sessions AS c WITH(NOLOCK) ON a.session_id = c.session_id
	INNER JOIN sys.dm_exec_connections AS d WITH(NOLOCK) ON a.session_id = d.session_id
WHERE a.session_id <> @@SPID
ORDER BY a.session_id

SELECT request_session_id, COUNT(*) num_locks
FROM sys.dm_tran_locks WITH(NOLOCK)
GROUP BY request_session_id
ORDER BY COUNT(*) desc