GIS 개발자

SQL-Server의 테이블을 TXT 파일로 출력하는 방법에 대해 설명한다.

해당 기능을 사용하기 위해 bcp.exe(bulk copy program utility)가 필요하며, 이는 SSMS나 SQL-Server를 설치하면 자동으로 설치된다. 

먼저 본인의 pc에 bcp.exe가 존재하는 지 확인하기 위해서는 2가지 방법 중 한가지를 선택하여 확인한다.

• CMD 창에서 BCP입력

• 프로그램의 설치 경로에 접근하여 확인

설치가 되어 있다면, SSMS에서 실행하거나 cmd 창에서 실행을 통해 텍스트 파일을 추출할 수 있다

[SSMS에서 실행시]

-- xp_cmdshell 옵션을 활성화 (xp_cmdshell : SSMS에서 cmd창에 입력하듯이 사용하게 해주는 프로그램)
SP_CONFIGURE 'XP_CMDSHELL', 1
GO
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE
GO

-- BCP 실행
EXEC xp_cmdshell 'bcp "SELECT * FROM [인스턴스명].dbo.[테이블명]" queryout "D:\BCPTest.txt" -S -T -c'

[CMD 창에서 실행시 : 테이블을 txt 파일로 추출]

bcp [인스턴스명].dbo.[테이블명] out D:\BCPTest2.txt -T -c

[CMD 창에서 실행시 : 쿼리를 txt 파일로 추출]

// 기본 문법
bcp "SELECT * FROM [인스턴스명].dbo.[테이블명]" queryout "D:\BCPTest2.txt" -T -c -S [서버명] -U [아이디] -P [비밀번호] -t [필드종결자] -r [행종결자]

// CSV 파일 형태로 텍스트 파일을 추출할 때
bcp "SELECT * FROM [인스턴스명].dbo.[테이블명]" queryout "D:\BCPTest2.txt" -T -c -S [서버명] -U [아이디] -P [비밀번호] -t "," -r "\n"

// 탭으로 구분된 형태의 텍스트 파일 형태로 추출하고자 할때
bcp "SELECT * FROM [인스턴스명].dbo.[테이블명]" queryout "D:\BCPTest2.txt" -T -c -S [서버명] -U [아이디] -P [비밀번호] -t "\t" -r "\n"

예를들어 필드 구분과 행 구분을 원하는 형태대로 설정하고자 한다면 -t 옵션과 -r 옵션을 조절하여 추출한다. 

이는 바이너리로 구성된 포맷이나 프로토콜에서 전달되는 데이터의 용량을 줄여 압축적으로 데이터를 전달하기 위해 사용되는 경우가 대부분이지만 현업에서 데이터베이스를 접하다보면 비트마스크를 활용하여 하나의 필드에 여러가지 의미를 담아놓은 필드를 발견하는 순간이 있다.

예를 들어 식당을 저장한 테이블이 존재하고 각각의 식당은 업종에 대한 코드값을 가지고 있다고 생각해보자.

• 사천마라탕 (중식)
• 파스타하우스(양식)
• 닌자초밥(일식)
• 그루브하우스 (카페, 양식)

업종 코드 테이블

식당 테이블

먼저 업종코드 테이블은 2의 배수 형태로 데이터를 저장하고 있고,

식당 테이블에서 그루브하우스를 확인하면, 업종코드테이블에 존재하지 않는 10이라는 값으로 데이터가 저장되어 있다. 

이 때, 12라는 의미는 비트마스킹의 개념을 도입한 필드로 볼 수 있다. 

양식에 해당되는 값인 2와 카페에 해당되는 8값을 더하여 10이라는 값으로 표현된 것이다. 

• 그루브하우스(10) = 양식(2) + 카페(8)

예를들어 하나의 식당에서 모든 업종에 포함되는 메뉴를 제공한다고 가정하면 코드값은 17이라는 값을 가지게 된다.

• 메가식당(17) = 중식(1) + 양식(2) + 일식(4) + 카페(8)

아래와 같이 어떤 코드값의 조합을 하더라도 기존의 코드값을 침해하지 않는 것을 확인할 수 있다. 

비트 마스킹 필드를 사용하는 것의 단점은 아래와 같은 것들이 존재한다. 

• 업종코드 테이블과 식당 테이블을 물리적인 관계를 생성할 수 없다.
• 업종을 기준으로 데이터를 조회할 때 비트 연산이나 IN 쿼리를 통한 범위 질의를 수행해야만 한다.
  비트 연산을 할 경우 인덱스 사용이 불가하여 성능의 저하가 발생하게 되고,
  IN 쿼리를 통한 질의를 할 경우 쿼리의 복잡도가 매우 상승할 수 있다. 

-- 양식(2)에 해당하는 레코드를 가져오게 될 때
SELECT *
FROM [식당 테이블]
WHERE [업종 코드] & 2 = 2
--> 좌변에 비트 연산을 해야 하므로 인덱스 사용이 불가하여 성능이 저하된다.


SELECT *
FROM [식당 테이블]
WHERE [업종코드] IN (2, 3, 6, 7, 10, 11, 15)
--> 업종코드의 비트 연산을 머리속으로 계산하여 IN 연산을 수행할 경우 쿼리의 복잡도가 높아진다.
--> 실수를 발견하기가 매우 여럽다. 

다만, 현업에서 비트 마스킹 필드를 사용하는 이유는 아래와 같은 장점이 존재하기 때문이다. 

• 기존의 스키마 변경 없이 코드값의 변경으로 문제를 해결 할 수 있다.
  위와 같은 문제를 해결하려면, 테이블이나 필드를 분해하는 대규모 수정 작업을 수행해야 하므로 많은 시간이 소요되는 작업이다.

가장 올바른 해결책은

• 코드의 개수가 작다면 각각의 코드를 필드로 분해하는 것이 타당하다.
• 코드의 개수가 많다면 [식당-업종 테이블]과 같이 [식당 테이블]과 [업종 테이블]을 연결하는 테이블로 분해하는 것이 타당하다.

하지만, 개발자에게 시간은 한정적이며, 문제 해결이 시급한 순간들이 존재한다. 이 때 비트마스크 필드를 활용하여 빠르게 문제 상황을 해결하고 이 후 시간적 여유를 가지고 수정 작업을 수행하는 것도 하나의 방법이 될 수 있을 것이라고 생각한다.

SQL Server(MS-SQL) 현재 날짜와 시간 가져오기

SQL Server에서 사용 가능한 현재 시간을 가져오는 방법에 대해 설명한다. 

본 장에서 설명하고자 하는 함수는 아래와 같다. 

사용예시

SELECT GETDATE()
SELECT SYSDATETIME() 
SELECT SYSDATETIMEOFFSET() 
SELECT SYSUTCDATETIME() 
SELECT CURRENT_TIMESTAMP 
SELECT GETUTCDATE()