[Database] [MSSQL] SELECT구문에서 DECLARE 변수에 바로 값 넣어서 사용하기.

* 사원테이블(Employee_Table)은 Employee_ID(PK)를 통해 관리되고 있다고 가정하고,
 영업테이블(Sales_Table)의 실적은 Employee_ID별로 Sales_Amonut 컬럼에 기록된다고 가정하자.
 이때, 영업실적이 가장 좋은 1등 사원에 대한 정보를 조회하고 싶으면 아래와 같은 구문을 사용할 것이다.

-- Sales_Table에서 영업실적이 가장 좋은 사원의 ID를 하나 가져와 Employee_Table에서 찾는다.
SELECT *
FROM Employee_Table
WHERE Employee_ID = (SELECT TOP 1 Employee_ID
                     FROM Sales_Table
                     ORDER BY Sales_Amount DESC)

* 그런데, 이 1등 사원의 Employee_ID를 여기저기에서 계속 사용해야 한다면, DECLARE로 변수를 선언해 값을 넣어두고 사용하면 추가적인 비용을 줄일 수 있을것이다. 이때는 아래와 같이 SELECT 구문에서 바로 변수에 값을 대입할 수 있다.

-- 먼저 변수 @TOP_1_ID를 선언하여 Sales_Table에서 영업실적이 가장 좋은 사원의 ID를 하나 저장한다.
DECLARE @TOP_1_ID VARCHAR(8)
SELECT TOP 1 @TOP_1_ID = Employee_ID
FROM Sales_Table
ORDER BY Sales_Amount DESC

-- @TOP_1_ID 변수를 이용해 Employee_Table에서 사원 정보를 조회한다.
SELECT *
FROM Employee_Table
WHERE Employee_ID = @TOP_1_ID

[Database] [MSSQL] tempdb 공간 관리하기.

* 데이터베이스에서 연산을 할때 tempdb를 사용한다. 때로는 tempdb의 자원이 너무 부족하여 성능저하가 발생할 수도 있다. 이때에는 공간을 늘리는 등의 작업을 통해 해결할 수 있다.

#1. tempdb의 정보를 확인해보자.
 참조1 : https://technet.microsoft.com/ko-kr/library/ms175527(v=sql.105).aspx
 참조2 : https://msdn.microsoft.com/ko-kr/library/ms174397(v=sql.120).aspx

-- FileName : tempdb의 이름
-- PhysicalName : tempdb의 논리적 파일 이름
-- state_desc : tempdb의 현재 상태
-- FileSizeinMB : tempdb에 할당된 공간(MB)
-- AutoGrowth : tempdb에 할당된 공간을 모두 사용 후, 자동 증가 여부
-- GrowthIncrement : tempdb의 자동 증가 방식
SELECT 
    name AS FileName,
 physical_name AS PhysicalName,
 state_desc,
    size*1.0/128 AS FileSizeinMB,
    CASE max_size 
        WHEN 0 THEN 'OFF'
        WHEN -1 THEN 'ON'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END AutoGrowth,
    'GrowthIncrement' = 
        CASE
            WHEN growth = 0 THEN 'fixed / will not grow'
            WHEN growth > 0 AND is_percent_growth = 0 
                THEN 'in 8-KB pages'
            ELSE 'percentage'
        END
FROM tempdb.sys.database_files;

#2. tempdb의 사용 내역에 대해 확인해보자.
 참조 : https://technet.microsoft.com/ko-kr/library/ms176029(v=sql.105).aspx

-- free pages : tempdb의 모든 파일에서 사용 가능한 전체 빈 페이지 수
-- free space in MB : tempdb의 모든 파일에서 사용 가능한 전체 빈 공간(MB)
-- version store pages used : tempdb에서 버전 저장소에 의해 사용되는 전체 페이지 수
-- version store space in MB : tempdb에서 버전 저장소에 의해 사용되는 전체 공간(MB)
-- internal object pages used : tempdb에서 내부 개체에 의해 사용되는 전체 페이지 수와 공간(MB)
-- internal object space in MB : tempdb에서 내부 개체에 의해 사용되는 전체 공간(MB)
-- user object pages used : tempdb에서 사용자 개체에 의해 사용되는 전체 페이지 수와 공간(MB)
-- user object space in MB : tempdb에서 사용자 개체에 의해 사용되는 전체 공간(MB)
SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB],
SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB],
SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB],
SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

-- 버전 저장소가 tempdb에서 많은 공간을 사용 중인 경우 가장 오랫동안 실행되는 트랜잭션을 확인해야 한다.
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

#3. tempdb의 공간을 늘려보자.
 참조 : https://msdn.microsoft.com/ko-kr/library/bb522469(v=sql.120).aspx

-- tempdb의 dev 공간을 200MB로 늘리고, 자동 증가를 10MB단위로 증가
ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev', SIZE = 200MB, FILEGROWTH = 10MB)

#4. tempdb의 물리적 공간을 변경하거나 추가해보자.
 참조 : https://codeclassic.wordpress.com/tag/tempdb/

-- tempdb의 dev 파일을 d:\mssql\tempdb.mdf 로 변경
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'd:\mssql\tempdb.mdf')

-- tempdev2라는 새로운 파일을 추가
ALTER DATABASE tempdb ADD FILE (NAME = N'tempdev2', FILENAME = N'd:\mssql\tempdev2.ndf', SIZE = 200MB, FILEGROWTH = 10MB)

[Database] [MSSQL] 데이터베이스를 READ_COMMITTED_SNAPSHOT 모드로 변경하기.

* 예를 들어, DDDD라는 데이터베이스를 사용하는 두 사용자가 있다. Martin과 Oskar로 하자.

#1. Martin은 A테이블에 트랜잭션을 열어서 Insert 혹은 Update 작업을 한다. (아직 Commit이나 Rollback을 하지 않은 상태)
#2. 이때, Oskar가 A테이블을 Select하려고 하면, A테이블에는 Lock이 걸려있다. (아직 Martin의 작업이 끝나지 않았으므로) 그래서 Oskar의 쿼리는 Blocking상태에 빠지게 되고, Martin의 쿼리가 끝나길 하염없이 기다리는 상태가 된다.

* 여기에서 만약, Oskar는 Martin의 작업과 관계없이, 항상 가장 마지막에 Commit된 A테이블의 데이터만 가져와도 된다. 라고 한다면 데이터베이스를 다음과 같이 설정하면 위와같은 블락에 걸리지 않게된다.
 참조 : https://technet.microsoft.com/ko-kr/library/ms175095(v=sql.105).aspx

-- 데이터베이스 정보 확인 (0은 OFF, 1은 ON상태를 의미)
SELECT NAME, SNAPSHOT_ISOLATION_STATE,
SNAPSHOT_ISOLATION_STATE_DESC, IS_READ_COMMITTED_SNAPSHOT_ON
FROM SYS.DATABASES

-- READ_COMMITTED_SNAPSHOT모드 ON
ALTER DATABASE DDDD SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

-- READ_COMMITTED_SNAPSHOT모드 OFF
ALTER DATABASE DDDD SET READ_COMMITTED_SNAPSHOT OFF

* 추가적으로, Oskar가 만약 Martin의 트랜젝션이 Commit 되건 Rollback이 되건 그냥 지금 현재 변화하는 데이터를 보고싶다면? 현재의 Oskar가 열어놓은 세션에 다음과 같은 명령어로 확인할 수 있게된다.
 (총 4가지 옵션이 있고, 추가 설명들은 아래의 참조사이트를 참고하자.)
참조 : https://support.microsoft.com/ko-kr/kb/601430

-- SET옵션 확인
DBCC USEROPTIONS

-- READ UNCOMMITTED 모드로 변경.
-- 주의 : SQL Server가 Default로 사용하는 Isolation Level은 READ COMMITTED.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Database] [MSSQL] 데이터베이스 내 Lock Timeout 설정하기.

* 하나의 트랜잭션이 Lock을 오래도록 잡고 있으면(데드락 같은 경우를 제외하고) Blocking의 시간이 길어지게 된다. 이로 인해 추후의 트랜잭션들에게도 Blocking을 계속 유발할 수도 있다.
 그래서 Lock Timeout을 제한하여 일정 시간동안 Blocking 상태에 빠지면, 스스로 트랜잭션을 중단하게 만들 수 있다.

* 먼저 아래의 쿼리를 통해 데이터베이스의 Lock Timeout 시간을 확인해보자.
  -1 이라는 값은, Lock Timeout에 대한 제한이 없음을 의미한다(Default 값임).
 참조 : https://msdn.microsoft.com/ko-kr/library/ms182729(v=sql.120).aspx

SELECT @@LOCK_TIMEOUT AS Lock Timeout

* 설정은 다음과 같이 한다.
 참조 : https://msdn.microsoft.com/en-us/library/ms189470.aspx

-- 밀리초 단위로 지정해야 한다. (1초 = 1000밀리초)
-- Lock Timeout을 3초로 한다면,
SET LOCK_TIMEOUT 3000

-- Lock Timeout을 Default 값으로 되돌리려면,
SET LOCK_TIMEOUT -1

[Database] [MSSQL] 데이터베이스 내에 걸린 Lock, Blocking 상세 정보 확인하기.

* 지난번 Lock확인하기의 내용에 이어서 진행한다.
  참조 : http://oskardevelopers.blogspot.kr/2015/12/database-mssql-lock_8.html

* sp_lock 명령어를 통해 어느 spid에서 lock이 발생되는지를 확인했다면, 어떤 쿼리를 요청했는지도 알아야 할 필요가 있다.
  이때는, 아래의 @spid에다가 문제가 되는 spid를 넣어 명령어를 실행시키면 된다.

-- 00에다가 lock이 잡힌 spid를 넣자.
declare @spid int = 00

dbcc inputbuffer(@spid)
exec sp_who @spid
exec sp_who2 @spid

-- Lock이 잡힌 세션에 대한 상세 정보
SELECT *
FROM sys.sysprocesses
CROSS APPLY sys.dm_exec_sql_text (sql_handle) as sql_text
WHERE spid = @spid

-- Blocking으로, 대기중인 세션들에 대한 상세 정보 
SELECT *
FROM sys.sysprocesses
CROSS APPLY sys.dm_exec_sql_text (sql_handle) as sql_text
WHERE blocked > 0 

[Database] [MSSQL] MERGE 구문 사용하기. (중복항목에는 UPDATE, 신규항목에는 INSERT)

* INSERT 혹은 UPDATE에 대한 트리거를 설정하다보면,
 기존 Key를 대상으로 중복항목이 들어오면 변경사항을 UPDATE해줘야 하고,
 신규 항목이 들어오면 INSERT를 해줘야 하는 경우가 있다.

* 먼저, MERGE 구문의 동작을 이해하기 위해 예제를 하나 준비했다.
* Item_A 테이블과 Item_B 테이블이 있는데, 그 구조가 동일하다고 가정해보자.
  (테이블에는 두 컬럼 Item_Code(PK)와 Item_Name이 있다고 가정하자.)
* 우리는 지금 두 테이블 Item_A와 Item_B를 병합하려 한다. 기준은 Item_A테이블로 잡았다. (즉, Item_Code 값이 중복되면, Item_A테이블의 Item_Name이 우선순위이다. Item_A테이블에 없는 Item_Code가 Item_B테이블에 있다면, Item_A테이블에 추가한다.)

-- MERGE구문을 이용한 코드는 다음과 같다.

MERGE Item_A
USING Item_B
ON (Item_A.Item_Code = Item_B.Item_Code)

WHEN MATCHED THEN
UPDATE SET Item_A.Item_Name = Item_B.Item_Name

WHEN NOT MATCHED THEN
INSERT (Item_A.Item_Code, Item_A.Item_Name) Values (Item_B.Item_Code, Item_B.Item_Name);

* 그렇다면, 추가로 트리거까지 생각을 해보자.
* 테이블에 INSERT되거나 UPDATE된 내용에 대해서 기존의 테이블에 있는 내용이라면 UPDATE를, 신규항목이라면 INSERT를 동작하게 하려면 어떻게 해야할까?
 예를 들어, Item_Table에는 Item_Code(PK)와 Item_Name을 사용해 제품을 관리중이다.
 여기에 Stock_Table(재고) 테이블이 존재하는데, 이 테이블에 매일 INSERT 혹은 UPDATE 작업을 통해 남아있는 아이템과 재고들을 관리해야하는 상황이다.
* 상품은 새로 추가될수도 있고, Item_Code에 대한 제품명이 바뀔수도 있는 상황이다.
 이 상황에서 Stock_Table에 데이터를 넣으면, 자동으로 Item_Table을 관리되게끔 만드는 트리거는 다음과 같이 작성할 수 있다.

CREATE TRIGGER tr_updateItemCode
ON [dbo].[Stock_Table]

/* 내용 : 재고테이블(Stock_Table)의 아이템에 대한 정보를
          Item_Code를 기반으로 Item_Table 테이블에 자동으로 등록 */

AFTER INSERT, UPDATE
AS
  BEGIN

   MERGE Item_Table i
   USING (SELECT DISTINCT Item_Code, Item_Name FROM Inserted) n
   ON (i.Item_Code = n.Item_Code)

   WHEN MATCHED THEN
    UPDATE SET i.Item_Name = n.Item_Name,

   WHEN NOT MATCHED THEN
    INSERT (i.Item_Code, i.Item_Name)
    VALUES (n.Item_Code, n.Item_Name);
  END

[Database] [MSSQL] 트리거로 다른 테이블 업데이트 하기. (변경된 사항만)

* 예를 들어, Item 테이블과, Sales 라는 테이블이 있다고 가정하자.
* Item 테이블은 Group_Code와 Item_Code로 이루어져있고, Sales에서는 Item테이블의 두 컬럼에 대한 정보를 모두 가지고 있다. (단, Item테이블의 Item_Code는 PK이다.)

* 어느 날, Item테이블의 Item_Code(10001) 상품의 Group_Code를 AAA에서 BBB로 변경해야만 했다. 그러면, Sales라는 테이블의 Item_Code 컬럼에서 기존에 AAA라는 값을 가진 모든 항목을 BBB로 변경하는 UPDATE문을 작성해야 한다.

-- 예를 들자면, 
UPDATE Sales SET Group_Code = 'BBB' WHERE Item_Code = '10001'

* 하지만, Group_Code가 빈번하게 변경 된다면? 혹은, Item 테이블의 Group_Code 변경 시점에 Sales테이블에도 항상 자동으로 변경 사항이 반영되어야만 한다면?
* 이런 상황에서는 다음과 같은 트리거를 사용해 해결할 수 있다.

CREATE TRIGGER tr_updateGroupCode

ON Item_Table



/* 내용 : 아이템의 그룹코드가 변경되면, 판매(Sales)테이블의 그룹 코드도 변경 */



AFTER INSERT, UPDATE

AS

  BEGIN

      UPDATE Sales_Table

      SET

            Group_Code = Item_Table.Group_Code

      FROM Item_Table

      WHERE

   Item_Table.Item_Code = Sales_Table.Item_Code

  and Item_Table.Item_Code IN (SELECT DISTINCT Item_Code FROM Inserted)

  END

[Database] [MSSQL] 달력 날짜 계산하기. (DATEADD)

* 달력의 날짜에 대한 계산을 해야할 경우에는 DATEADD함수를 사용한다.
* 예를 들어, 2015년 12월 30일의 3일 후,3달 후, 혹은 3년 후를 계산하여 출력하려면 다음과 같다.

-- 3일 후
select dateadd(dd, 3, '20151230') -- result : 2016-01-02 00:00:00.000

-- 3달 후
select dateadd(mm, 3, '20151230') -- result : 2016-03-30 00:00:00.000

-- 3년 후
select dateadd(yy, 3, '20151230') -- result : 2018-12-30 00:00:00.000

* convert함수와 함께 다양한 포멧으로도 변경할 수 있다.

select convert(varchar,dateadd(yy, 5, '20151201'),112) -- result : 20201201
select convert(varchar,dateadd(mm, 5, '20151201'),112) -- result : 20160501
select convert(varchar,dateadd(dd, 5, '20151201'),112) -- result : 20151206

[Database] [MSSQL] 트리거를 이용해 업데이트 시간 기록하기.

* 컬럼의 Default 값에 getDate()함수로 생성 시간을 자동으로 입력하는 것은 이미 예전 포스팅을 통해 다루어봤다. (https://oskardevelopers.blogspot.kr/2015/10/it-database-sql-auto-incrementai-date_14.html)

* INSERT가 아닌, UPDATE시점의 시간을 기록하기 위해서는 어떻게 해야할까?

#1. 먼저 대상이 되는 테이블(A_Table)에 update_datetime이라는 컬럼을 하나 추가하자.

ALTER TABLE A_Table ADD update_datetime DATETIME NULL;

#2. UPDATE시에만 동작하는 트리거를 다음과 같이 생성해주자. (아래는 Column_1과 Column_2를 모두 기본값으로 갖는 경우를 예로 들었다.)

CREATE TRIGGER tr_update_datetime
ON A_Table
AFTER UPDATE
AS
  BEGIN
      UPDATE A_Table
      SET     update_datetime = getDate()
      WHERE Column_1 + Column_2 IN (SELECT DISTINCT Column_1 + Column_2 FROM Inserted)

  END

[Database] [MSSQL] DB에서 사용중인 모든 테이블, 함수, 프로시져, 뷰 확인하기.

* 데이터베이스에서 사용중인 모든 테이블, 함수, 프로시져, 뷰를 한번에 확인하는 쿼리이다.

SELECT TABLE_TYPE as [Type], TABLE_CATALOG as [Catalog], TABLE_SCHEMA as [Schema], TABLE_NAME as [Name]
FROM INFORMATION_SCHEMA.TABLES
UNION
SELECT ROUTINE_TYPE, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
ORDER BY TABLE_TYPE

[Database] [MSSQL] 문자와 숫자가 혼용된 컬럼에서 숫자만 뽑아 INDEX 걸기.

* 회원의 전화번호와 같이 숫자만 뽑아서 인덱스를 걸어야 하는 컬럼들이 있다.
* 예를 들어, 아래와 같은 고객 테이블을 기존에 쓰고 있다고 생각해보자.
* 여기에서 왜 Telephone 컬럼에 전화번호 이외의 문자들이 들어갔는지는 묻지 말기 바란다.. 아직도 많은 운영계 데이터베이스의 테이블에는 저런 형식으로 저장된 값들이 많다..

Customer_Table

Name   |   Telephone
A         |   010-0000-0000
B         |   01011111111(Mother)
C         |   +82-10-2222-2222


# 1. 숫자만을 저장시키기 위한 새 컬럼(Telephone_Numeric)을 하나 만든다.

ALTER TABLE Customer_Table ADD Telephone_Numeric VARCHAR(20) NULL;

# 2. 숫자만을 추출하는 함수를 하나 만든다.
* 이 블로그에서 이미 작성했던 글을 참조하자 : http://oskardevelopers.blogspot.kr/2015/12/database-mssql_15.html


# 3. Telephone컬럼의 숫자만 뽑아서 새로 만든 컬럼(Telephone_Numeric)에 넣어주자.

UPDATE Customer_Table
SET Telephone_Numeric = dbo.func_getNumeric(Telephone);

# 4. 앞으로 새로운 고객이 등록된다던지, 기존 고객의 전화번호가 변경되는 상황을 고려하여 트리거를 만들어줘야 한다.

CREATE TRIGGER tr_fillOnlyNumTel
ON Customer_Table
AFTER INSERT, UPDATE -- 혹은 FOR INSERT, UPDATE로 써도 무방하다.

AS
  BEGIN
      UPDATE Customer_Table
      SET   Telephone_Numeric = dbo.func_getNumeric(Telephone)
      WHERE  Customer_ID IN (SELECT DISTINCT Name FROM Inserted)
  END

# 5. Telephone_Numeric컬럼에 인덱스를 걸어주자. (여기서는 비클러스터형, 단일컬럼 방식의 인덱스를 예로 들었다).

CREATE INDEX Customer_Table_INDEX_01
ON Customer_Table(Telephone_Numeric);

[Database] [MSSQL] 컴마(혹은 특수 문자)로 구분된 값을 나누어서 행단위로 만들기.

* 특정 문자로 구분된 값을 나누어서 집계를 해야하는 경우가 있다. 아래의 사이트들에 좋은 예제가 있어서 가져왔다.

* 방법 1. XML 파싱
* 출처 : http://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/

-- 예제를 위한 임시 테이블 생성
DECLARE @t TABLE (
    EmployeeID INT,
    Certs VARCHAR(8000)
)
INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')

-- 방법 1. XML 이용
SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
        (
        SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM   @t
        )t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

* 방법 2. 쿼리 파싱
* 출처 : http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

-- 컴마로 구분된 값이 있는 임시테이블
DECLARE @Quotes table(
Author varchar(50),
Phrase varchar(500)
)
insert into @Quotes values ('Shakespeare', 'A,rose,by,any,other,name,smells,just,as,sweet')
insert into @Quotes values ('Kipling', 'Across,the,valley,of,death,rode,the,six,hundred')
insert into @Quotes values ('Coleridge', 'In,Xanadu,did,Kubla,Khan,...,,,,damn,I,forgot,the,rest,of,it')
insert into @Quotes values ('Descartes', 'I,think,therefore,I,am')
insert into @Quotes values ('Volk', 'I,think,therefore,I,need,another,beer')
insert into @Quotes values ('Feldman', 'No,it,is,pronounced,I,gor')
insert into @Quotes values ('Simpson', 'Mmmmmm,donuts')
insert into @Quotes values ('Fudd', 'Be,vewwy,vewwy,quiet,I,am,hunting,wabbits')

-- 결과를 저장할 임시테이블
DECLARE @OnlyWords table(
Author varchar(50),
Word varchar(50)
)

-- 연산에 필요한 임시테이블
DECLARE @Tally table(
ID int
)
DECLARE @idx int
SET @idx = 1
WHILE (@idx <= 8000)
BEGIN
    insert into @Tally (ID) values (@idx)
    SELECT @idx = @idx + 1
END

-- 방법 2. SELECT 쿼리
SELECT Author, NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '') AS Word
FROM @Tally, @Quotes
WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0 -- Null값을 표시하려면 이 열을 주석처리

-- 결과테이블에 저장하는 쿼리
INSERT INTO @OnlyWords
SELECT Author, NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '') AS Word
FROM @Tally, @Quotes
WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0 -- Null값을 표시하려면 이 열을 주석처리

-- 결과 확인
select * from @OnlyWords

[Database] [MSSQL] 특정 문자를 값으로 가지고있는 모든 컬럼의 모든 행 찾기 (테이블의 모든 컬럼을 대상으로)

* 개발을 하다보면, 데이터베이스에 저장된 ", ', < 등의 값에서 문제가 발생하는 경우가 허다하다.
* 이 때, 테이블의 모든 컬럼을 대상으로 특수 문자를 찾아주는 프로시져를 만들어서 사용하면 편리하다.
* 참조 : https://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/


* 프로시져의 생성

CREATE PROCEDURE sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname 
AS 

DECLARE @sqlCommand VARCHAR(8000) 
DECLARE @where VARCHAR(8000) 
DECLARE @columnName sysname 
DECLARE @cursor VARCHAR(8000) 

BEGIN TRY 
   SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE' 
   SET @where = '' 

   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME 
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = ''' + @schema + ''' 
   AND TABLE_NAME = ''' + @table + ''' 
   AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')' 

   EXEC (@cursor) 

   OPEN col_cursor    
   FETCH NEXT FROM col_cursor INTO @columnName    

   WHILE @@FETCH_STATUS = 0    
   BEGIN    
       IF @where <> '' 
           SET @where = @where + ' OR' 

       SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + '''' 
       FETCH NEXT FROM col_cursor INTO @columnName    
   END    

   CLOSE col_cursor    
   DEALLOCATE col_cursor  

   SET @sqlCommand = @sqlCommand + @where 
   --PRINT @sqlCommand 
   EXEC (@sqlCommand)  
END TRY 
BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.' 
   IF CURSOR_STATUS('variable', 'col_cursor') <> -3 
   BEGIN 
       CLOSE col_cursor    
       DEALLOCATE col_cursor  
   END 
END CATCH

* 사용방법

 -- " 를 값으로 가지고 있는 A_Table의 모든 row 찾기.
EXEC sp_FindStringInTable '%"%', 'dbo', 'A_Table'

 -- 2015로 시작하는 값을 가지고 있는 A_Table의 모든 row 찾기.
EXEC sp_FindStringInTable '2015%', 'dbo', 'A_Table'

[Database] [MSSQL] 특정 문자(열)의 시작 위치 찾기 (CHARINDEX)

* 문자열의 집합에서 특정 문자열의 시작 위치, 혹은 특정 문자의 위치가 필요한 경우에는 아래와 같은 쿼리를 이용한다.

SELECT CHARINDEX('P','MartinPark')
-- > 결과 : 7

[Database] [MSSQL] 특정 캡션을 사용하는 테이블 설명, 컬럼 설명 찾기, 변경하기.

* 테이블과 컬럼에 추가된 캡션을 일괄적으로 수정해야 하는 경우가 있는데, 특정 캡션을 사용하는 모든 테이블을 추출할 때 편리한 쿼리이다.
* 예를 들어, 'MS_Description' 라고 이름 붙은 설명을 사용하는 모든 테이블을 추출하는 쿼리는 다음과 같다.

SELECT  * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'table', default, default, default) where name = 'MS_Description'

* 찾은 테이블(A_Table)의 설명을 제거하기 위해서는 아래와 같은 쿼리를 사용한다.

EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name='A_Table'

* 테이블이나 컬럼에 일괄적으로 설명을 추가할때는 아래와 같이 IF문과 활용하면 더욱 편리하다.

-- A테이블의 'MS_Description'로 지정된 캡션이 존재하면 갱신, 그렇지 않으면 새로 생성하는 쿼리.
IF NOT EXISTS (SELECT  objname FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'table', 'A_Table', default, default))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A테이블입니다', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'A_Table'
EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'A테이블입니다', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'A_Table'

-- A테이블의 컬럼1에 'MS_Description'로 지정된 캡션이 존재하면 갱신, 그렇지 않으면 새로 생성하는 쿼리.
IF NOT EXISTS (select * from (SELECT  objname FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'table', 'A_Table', 'column', default)) as t where t.objname = 'Column_1')
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A테이블의 1번 컬럼입니다', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'A_Table', @level2type=N'COLUMN', @level2name=N'Column_1'
EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'A테이블의 1번 컬럼입니다', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'A_Table', @level2type=N'COLUMN', @level2name=N'Column_1'

[Database] [MSSQL] 숫자가 아닌 첫 번째 스트링 문자를 찾아서 추출하기.

* 컬럼에서 숫자 값만 필요한데, 어느 행에 스트링이 포함되어 있어서 골치아플 때가 있다.
* 그럴 경우, 첫 번째 스트링이 있는 문자열을 추출해, 값이 있는 행만 다시 추려내면 편하다.
* 참조 : http://blog.sqlauthority.com/2012/10/14/sql-server-find-first-non-numeric-character-from-string/


* 컬럼값에 대한 첫번째 스트링을 찾아서 보여주는 쿼리는 다음과 같다. 예를 들어, A_Table에 Column_1을 대상으로 찾아보자.

SELECT PATINDEX('%[^0-9]%',Column_1) 'Position of NonNumeric Character',
       SUBSTRING(Column_1,PATINDEX('%[^0-9]%',Column_1),1) 'NonNumeric Character',
       Column_1 'Original Character'
FROM A_Table

* 조금 응용하여, 고객 테이블(Customer)에 전화번호(Telephone_NO)와 같은 컬럼이 있는데, int나 numeric형이 아니어서 간혹 숫자가 아닌 값이 들어가게 된 경우를 생각해보자.
* 그 때 숫자 외의 값이 하나라도 들어간 행을 모두 추출하는 쿼리는 다음과 같다.

SELECT t.Telephone_NO
FROM (
      SELECT Telephone_NO,
          PATINDEX('%[^0-9]%',Telephone_NO) as StringFound
      FROM Customer
      ) as t
WHERE StringFound is not NULL

[Database] [MSSQL] 데이터베이스에 있는 모든 트리거(Trigger) 정보 보기.

* 현재 데이터베이스의 모든 트리거에 대한 정보를 가져오는 쿼리이다.
* 출처 : http://stackoverflow.com/questions/4305691/need-to-list-all-triggers-in-sql-server-database-with-table-name-and-tables-sch

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY(id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY(id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY(id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY(id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY(id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR' 

[Database] [MSSQL] 컬럼 값에서 숫자만 추출하기.

* 전화번호와 같은 스트링 컬럼에서 숫자만을 가져와야 하는데, 여러 불필요한 문자가 섞여있는 경우에 아래와 같이 함수를 만들어 사용하면 편리하다.
* 출처 : http://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string

CREATE FUNCTION func_getNumeric (@strAlphaNumeric VARCHAR(256)) 
returns VARCHAR(256) 
AS 
  BEGIN 
      DECLARE @intAlpha INT 

      SET @intAlpha = Patindex('%[^0-9]%', @strAlphaNumeric) 

      BEGIN 
          WHILE @intAlpha > 0 
            BEGIN 
                SET @strAlphaNumeric = Stuff(@strAlphaNumeric, @intAlpha, 1, '') 
                SET @intAlpha = Patindex('%[^0-9]%', @strAlphaNumeric) 
            END 
      END 

      RETURN Isnull(@strAlphaNumeric, 0) 
  END 

GO 

* 위와 같이 func_getNumeric이란 함수를 만들고 나면 아래와 같이 사용하면 된다.

 -- A_Table의 Column_1을 대상으로 숫자만 추출하기.
SELECT func_getNumeric(Column_1) 
from A_Table

[Database] [MSSQL] 데이터 혹은 컬럼 값의 형(TYPE) 변환 (CONVERT, CAST)

* 연산을 위해 중간중간 컬럼의 값을 형변환 해야 할 필요가 있다.
* 그럴 경우에는 아래와 같이 CONVERT 혹은 CAST함수를 사용한다.

-- A_Column을 int로 형변환.
CONVERT(INT, A_Column)
CAST(A_Column AS INT)

-- A_Column을 varchar(10)로 형변환.
CONVERT(VARCHAR(10), A_Column)
CAST(A_Column AS VARCHAR(10))

* CAST의 경우에는 간단한 형변환을 하지만, CONVERT의 경우에는 아래와 같이 형태(Style)를 지정할 수도 있다.
* 더 많은 형식에 대한 참고는 : http://lab.cliel.com/entry/SQL-%EC%8B%9C%EA%B0%84%EA%B4%80%EB%A0%A8-%ED%98%95%EC%8B%9D-%EB%B3%80%ED%99%98

--현재 날짜를 20151215 형식으로 표현
Select CONVERT(CHAR(08), GetDate(), 112)

--현재 날짜를 2015-12-15 형식으로 표현
Select CONVERT(CHAR(10), GetDate(), 120)

[Database] [MSSQL] 글자수 자르기, 추출하기 (LEFT, RIGHT, SUBSTRING)

* 쿼리의 결과에 대해 특정 부분만 추출하거나, 필요 없는 부분을 제외하기 위해 아래와 같은 함수를 사용한다.

-- A_Column의 값에서 좌측부터 5글자만 추출.
LEFT(A_Column, 5)

-- A_Column의 값에서 우측부터 7글자만 추출.
RIGHT(A_Column, 7)

-- A_Column의 값에서 좌측부터 3번째 글자부터 시작해서 총 5글자만 추출.
SUBSTRING(A_Column, 3, 5)