IT 이야기

1.  [user_id],[account_seq] 필드에서 중복행 찾기

SELECT [user_id],[account_seq], count(*) AS insertCount
FROM [dbo].[tbl_Assign]
GROUP BY [user_id],[account_seq]
HAVING count(*) > 1

2.  [user_id],[account_seq] 필드에서 가장 큰 seq 값 찾기

SELECT MAX(seq), [user_id],[account_seq], count(*) AS insertCount
FROM [dbo].[tbl_Assign]
GROUP BY [user_id],[account_seq]
HAVING count(*) > 1

3.  중복된 행 중 seq 가 가장 큰 값을 제외한 값 select

SELECT a.seq AS seq
FROM [dbo].[tbl_Assign] A 
INNER JOIN ( SELECT MAX(seq) as seq, [user_id],[account_seq], count(*) AS insertCount
		    FROM [dbo].[tbl_Assign]
		    GROUP BY [user_id],[account_seq]
                    HAVING count(*) >1 ) B
ON A.[user_id] = B.[user_id] and A.[account_seq] = B.[account_seq] and A.seq <> B.seq

4.  중복된 행 중 seq 가 가장 큰 값을 제외한 값 delete

DELETE FROM [dbo].[tbl_Assign]
WHERE seq IN (SELECT a.seq AS seq
                       FROM [dbo].[tbl_Assign] A 
                       INNER JOIN ( SELECT MAX(seq) as seq, [user_id],[account_seq], count(*) AS insertCount
                                           FROM [dbo].[tbl_Assign]
                                           GROUP BY [user_id],[account_seq]
                                           HAVING count(*) > 1) B
ON A.[user_id] = B.[user_id] and A.[account_seq] = B.[account_seq] and A.seq <> B.seq )

/*Perform a 'USE ' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;

DECLARE @tablename varchar(255);
DECLARE @execstr varchar(400);
DECLARE @objectid int;
DECLARE @indexid  int;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
                         ObjectName char(255),
			ObjectId int,
			IndexName char(255),
			IndexId int,
			Lvl int,
			CountPages int,
			CountRows int,
			MinRecSize int,
			MaxRecSize int,
			AvgRecSize int,
			ForRecCount int,
			Extents int,
			ExtentSwitches int,
			AvgFreeBytes int,
			AvgPageDensity int,
			ScanDensity decimal,
			BestCount int,
			ActualCount int,
			LogicalFrag decimal,
			ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%';
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')';
EXEC (@execstr);

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

insert 구문으로 데이타를 텍스트 파일로 저장해서 이동하는 경우나 외부에 데이타를 전달하기 위한 방법으로 다음과 같이 저장하는 경우가 있다.

실제 select 구문으로 쿼리되는 결과값은 아래와 같다.

위 데이타를 아래와 같은 스크립트로 이용하면 텍스트 파일에 저장할 수 있는 스크립을 뽑아 낼 수 있다.

6자리 숫자로 된 쿠폰번호 만드는 sql문.

DECLARE @L_S_LIMIT INT,
		@L_E_LIMIT INT,
		@INDEX INT,
		@RND INT
SET @L_S_LIMIT = 100000
SET @L_E_LIMIT = 999999
SET @INDEX = 1

WHILE @INDEX <= 2
BEGIN

	SET @RND = ROUND((@l_E_LIMIT - @l_S_LIMIT + 1) * RAND() + @l_S_LIMIT , 0, 1)
	
	INSERT INTO dbo.tbl_rnd VALUES (@RND)
	
	SET @INDEX = @INDEX + 1
END

결과값은 아래와 같음.....

데이타베이스 전체를 암호화해서 이동해서 복원하는 절차를 인터넷에서 찾았는데 여기에 내 스타일(?)에 맞게 다시 정리하여 올림....(자료 정리 차원에서)

/***************************************************************
***                   SOURCE 데이타베이스                    ***
***************************************************************/

-- 1. 마스터 키, 인증서 생성
USE master ;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*****';
GO

CREATE CERTIFICATE TestSQLServerCert2 WITH SUBJECT = 'Certificate to protect TDE key'
GO

---- 인증서 확인
SELECT * FROM sys.certificates

--------------------------------------------------------
-- 2. 인증서 백업
BACKUP CERTIFICATE TestSQLServerCert2 
TO FILE = 'TestSQLServerCert2'
WITH PRIVATE KEY
(
    FILE = 'C:\wmpub\CustRecords\SQLPrivateKeyFile',
   ENCRYPTION BY PASSWORD = 'BITOM8115'
);
GO

--------------------------------------------------------
-- 3. 데이타베이스 생성
CREATE DATABASE CustRecords2 ;
GO

--------------------------------------------------------

-- 4. 데이타베이스에 암호화 적용
USE CustRecords2;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert2;
GO

ALTER DATABASE CustRecords2
SET ENCRYPTION ON;
GO

--------------------------------------------------------
-- 5. 데이타베이스에 데이타 입력
CREATE TABLE CustomerRecord  (CustomerID int PRIMARY KEY, SecretData nvarchar(100) NOT NULL) ;
GO

INSERT CustomerRecord (CustomerID,SecretData)
VALUES (23997, 'Plain text data') ;
GO

SELECT CustomerID, SecretData FROM CustomerRecord ;
GO

--------------------------------------------------------
-- 6. 데이타베이스 분리
USE master ;
GO

EXEC master.dbo.sp_detach_db @dbname = N'CustRecords2';
GO

--------------------------------------------------------
-- 7. 마스터키와 인증서 삭제
USE master
GO

DROP CERTIFICATE TestSQLServerCert2;
DROP MASTER KEY;

/*******************************************************************
***                     TARGET 데이타베이스                      ***
*******************************************************************/

-- 1. 마스터키 생성
USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '********';
GO

---- 인증서 확인
SELECT * FROM sys.certificates

--------------------------------------------------------
--2. 인증서 복원
CREATE CERTIFICATE TestSQLServerCert2
FROM FILE = 'TestSQLServerCert2'
WITH PRIVATE KEY
(
FILE = 'C:\wmpub\CustRecords\SQLPrivateKeyFile',
DECRYPTION BY PASSWORD = 'Certificate to protect TDE key'
);
GO

--------------------------------------------------------
--3. 데이타베이스 복원
CREATE DATABASE [CustRecords2] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CustRecords2.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CustRecords2_log.LDF' )
FOR ATTACH ;
GO

--------------------------------------------------------
--4. 데이타베이스 테스트
USE CustRecords2 ;
GO

SELECT CustomerID, SecretData FROM CustomerRecord ;
GO

출처 : http://msdn.microsoft.com/ko-kr/library/ff773063.aspx#create

SQL 작업을 하다보면 테이블이 언제 생성 되었으며, 마지막으로 수정된 날짜 및 인덱스 사이즈을 알고 싶을 때가 있다.

적용 버전은 MSSQL2005, MSSQL2008 버전임.
2000 버전은 프로파일러를 돌려서 방법 외에는 없는 듯함.

--1. 특정 테이블의 정보를 간단하게 정보 DISPLAY
SELECT * FROM sys.dm_db_index_usage_stats WHERE object_id = OBJECT_ID('dbo.TN_KOREAN_WORD');

출처 : http://blog.naver.com/PostView.nhn?blogId=nebadak&logNo=90085971368&redirect=Dlog&widgetTypeCall=true

-- 2. 테이블별 데이터 개수, 인덱스 사이즈, 생성일, 마지막 수정일 DISPLAY
SELECT Schema_name(tbl.schema_id)                                 AS [Schema], 
       tbl.name, 
       Coalesce((SELECT pr.name 
                 FROM   sys.database_principals pr WITH (nolock) 
                 WHERE  pr.principal_id = tbl.principal_id), 
       Schema_name(tbl.schema_id))                                AS [Owner], 
       tbl.max_column_id_used                                     AS [Columns], 
       CAST(CASE idx.index_id 
              WHEN 1 THEN 1 
              ELSE 0 
            END AS BIT)                                           AS 
       [HasClusIdx], 
       Coalesce((SELECT SUM (spart.ROWS) 
                 FROM   sys.partitions spart WITH (nolock) 
                 WHERE  spart.object_id = tbl.object_id 
                        AND spart.index_id < 2), 0)               AS [RowCount], 
       Coalesce((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(a.used_pages - CASE 
                                         WHEN a.TYPE <> 1 THEN a.used_pages 
                                         WHEN p.index_id < 2 THEN a.data_pages 
                                         ELSE 0 
                                                                           END) 
                 FROM   sys.indexes AS i WITH (nolock) 
                        JOIN sys.partitions AS p WITH (nolock) 
                          ON p.object_id = i.object_id 
                             AND p.index_id = i.index_id 
                        JOIN sys.allocation_units AS a WITH (nolock) 
                          ON a.container_id = p.partition_id 
                 WHERE  i.object_id = tbl.object_id), 0.0) / 1024 AS [IndexMB], 
       Coalesce((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(CASE 
                        WHEN a.TYPE <> 1 THEN a.used_pages 
                                 WHEN p.index_id < 2 THEN a.data_pages 
                                 ELSE 0 
                                                            END) 
                 FROM   sys.indexes AS i WITH (nolock) 
                        JOIN sys.partitions AS p WITH (nolock) 
                          ON p.object_id = i.object_id 
                             AND p.index_id = i.index_id 
                        JOIN sys.allocation_units AS a WITH (nolock) 
                          ON a.container_id = p.partition_id 
                 WHERE  i.object_id = tbl.object_id), 0.0) / 1024 AS [DataMB], 
       tbl.create_date, 
       tbl.modify_date 
FROM   sys.tables AS tbl WITH (nolock) 
       INNER JOIN sys.indexes AS idx WITH (nolock) 
         ON ( idx.object_id = tbl.object_id 
              AND idx.index_id < 2 ) 
       INNER JOIN MASTER.dbo.spt_values v WITH (nolock) 
         ON ( v.NUMBER = 1 
              AND v.TYPE = 'E' ) 
--WHERE tbl.Name like '%tablename%'  
ORDER  BY 8 DESC 

출처 : http://www.sqler.com/266501

데이타베이스 작업을 하다보면 외부 사용자에게 데이타베이스를 열어주고 작업을 할때가 있는데 이런 경우 내부 데이타베이스는 숨기고 싶을때가 있다. 내부 정보를 외부 사람이 보게 만들 필요가 없으니.

이런 경우 외부사용자가 사용하는 데이타베이스만 보이게 하는 기능이 있어서 포스팅 한다.

1.  새로운 사용자 'hide1' 을 만든다. (이때 데이타베이스와 매핑을 하지 않는다)

2. 최상위 레벨에서 SQL (SQLSERVER Name)>[속성]>[사용권한]>[모든 데이타베이스 보이기]
   를 [거부] 체크한다.

3. 새로 데이타베이스를 생성하고, [속성]>[파일]>[소유자]를 새로 생성한 사용자 [hide1]으로 설정
   한다. 

1. 랜덤 쿠폰 번호 발행하기

NEWID() 함수를 이용한 방법을 소개하도록 하겠다.

먼저 NEWID() 함수의 속성부터 알아보도록 하자.

1. NEWID() 는 uniqueidentifier 데이타 형식을 가진다.
2. GUID(전역 고유 식별자) 역할을 하는 16바이트 이진 값을 저장한다.
3. GUID는 고유한 이진 숫자 값이다.
4. 전 세계 모든 컴퓨터는 고유한 GUID 값을 생성한다.
5. GUID의 주된 용도는 여러 사이트에 많은 컴퓨터가 있는 네트워크에서 고유한 식별자를 지정하는 것이다.

실제 SQL 문에서 실행되는 예제를 보도록 하자.

DECLARE @NEWID_TEMP VARCHAR(255)
SET @NEWID_TEMP = NEWID()

SELECT @NEWID_TEMP 
SELECT CONVERT(VARBINARY(255), @NEWID_TEMP)

/*****************************************************************************************
문자열 형식 : ABD26C1E-3781-405E-9D77-4B947DD7CBD0
이진 형식 : 0x41424432364331452D333738312D343035452D394437372D344239343744443743424430
*******************************************************************************************/ 

NEWID 를 이용한 전체 소스 코드임...

SET NOCOUNT ON

DECLARE @MAX_COUNT INT
DECLARE @INDEX INT
DECLARE @COUPON_NUM VARCHAR(10)
DECLARE @COUPON_TYPE VARCHAR(10)
DECLARE @COUPON_PREFIX VARCHAR(10)

SET @MAX_COUNT = 1000
SET @INDEX = 1
SET @COUPON_PREFIX = 'L'
SET @COUPON_TYPE = 'C_TYPE'

WHILE @INDEX <= @MAX_COUNT
BEGIN

	SET @COUPON_NUM = LEFT(REPLACE(NEWID(),'-',''), 10)
						
	INSERT INTO dbo.Dior_Coupon_201104 (COUPON_TYPE, COUPON_NUM)
	VALUES (@COUPON_TYPE, @COUPON_NUM)
	
	SET @INDEX = @INDEX + 1
END

2. 순차적 쿠폰번호 발행하기 

쿠폰 번호를 발행하는데 순자적으로, 즉 ETP0001, ETP0002 ,ETP0003 식으로 발행하는 것을 해 보자.

키 포인트는 숫자를 문자열로 변환하면서 자릿수를 맞추어줘야 한다. 이때 사용하는 함수가 REPLCATE 함수를 이용하면 된다.
전제 소스는 아래와 같다.

SET NOCOUNT ON

DECLARE @MAX_COUNT INT
DECLARE @INDEX INT
DECLARE @COUPON_NUM VARCHAR(10)
DECLARE @COUPON_TYPE VARCHAR(10)
DECLARE @COUPON_PREFIX VARCHAR(10)

SET @MAX_COUNT = 1000
SET @INDEX = 1
SET @COUPON_PREFIX = 'L'
SET @COUPON_TYPE = 'C_TYPE'

WHILE @INDEX <= @MAX_COUNT
BEGIN

	SET @COUPON_NUM = @COUPON_PREFIX + 
                          REPLICATE('0', 4 - DATALENGTH(CONVERT(VARCHAR(4),@INDEX))) + 
                          CONVERT(VARCHAR(4),@INDEX)
						
	INSERT INTO dbo.Dior_Coupon_201104 (COUPON_TYPE, COUPON_NUM)
	VALUES (@COUPON_TYPE, @COUPON_NUM)
	
	SET @INDEX = @INDEX + 1
END

테이블에 랜덤 문자열을 저장하는 절차

CREATE TABLE [dbo].[Table_1](
	[SEQ] [int] NOT NULL,
	[USERID] [varchar](10) NULL
) 

데이타 100,000 건 입력하기

DECLARE @outer_index int
SET @outer_index = 1

WHILE @outer_index <= 100000
BEGIN

	DECLARE @rnd_num tinyint, @index int, @char char(1), @rndchar VARCHAR(10)
	SET @index = 1
	SET @rndchar = ''

	WHILE @index < 6
	BEGIN
		SET @rnd_num = convert( int, (rand() * 52)+1  )

		SET @char = SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',@rnd_num ,1)
		SET @rndchar = @rndchar + @char
			
		SET @index = @index + 1
	END

	INSERT INTO dbo.Table_1(SEQ, USERID) VALUES(@outer_index, @rndchar)
	
	SET @outer_index = @outer_index + 1
END