Data Science Lab

XML nodes() 함수를 이용한 OPENXML 교체 Version : SQL Server 2005, 2008, 2008R2, 2012 OPENXML은 XML문서에 대한 행 집합 뷰를 제공한다. OPENXML이 행 집합 공급자이므로 테이블, 뷰 또는 OPENROWSET 함수 등의 행 집합 공급자가 있을 수 있는 T-SQL 문에 OPENXML을 사용 할 수 있다. XML 문서의 내부 표시는 sp_xml_preparedocumnet 를 호출하여 만들 수 있다. 실습을 위해 간단히 XML 문서를 정의 한다. 1002012-01-01 2002012-02-06 [SELECT 문에 OPENXML 사용] Sp_xml_preparedocument를 사용하여 XML 이미지의 내부 표현을 만든다. OPENXML 행 집합..

XQuery를 사용한 XML 데이터 업데이트 Version : SQL Server 2005, 2008, 2008R2, 2012 XML의 값을 수정하는 방법에는 무엇이 있을까? XML 편집기 등을 이용하여 사용자가 직접 수정 할 수도 있지만 XML노드와 값을 정확하게 수정하려면 XQuery를 사용하여 수정 할 수 있다. XQuery는 구조화 되었거나 반구조화된 XML 데이터를 쿼리할 수 있는 언어이다. XQuery는 기존의 XPath 쿼리 언어를 기반으로 더 나은 반복 성능 및 정렬 결과를 위한 지원이 추가 되었다. 자세한 내용은 MSDN을 참고한다. [노드 삽입] 삽입 키워드는 다음과 같은 구조를 사용한다. Insert Expression1 ({as first | as last} into | after ..

BCP XML 파일 형식 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server 환경에서 데이터를 가져오거나 내보내기 할 필요가 있다. SQL Server 2000 까지는 DTS를 사용하였고 SQL Server 2005 부터는 SSIS라는 SQL Server 통합서비스를 사용하였다. BCP라는 대량 복사 프로그램도 있다. BCP 정리 : http://sqlmvp.kr/140176987081 SSIS의 경우에는 GUI 기능 제공으로 많은 유연성을 제공한다. BCP의 경우에도 많은 기능이 향상 되었다. SQL Server 2005 부터는 데이터를 정의하는 형식의 파일을 생성하는데 XML 서식을 사용 할 수 있게 되었다. XML 형식 파일의 이점은 쉬운 생성, 읽..

SQL Server로 데이터 가져오기 Version : SQL Server 2005, 2008, 2008R2, 2012 DBA로 일하면서 많은 하는 작업 중 하나가 데이터를 이동하는 일이다. 데이터를 이동하는 방법에는 여려가지 방법이 있다. 가장 직관적인 방법은 방법은 마법사를 이용한 [가져오기/내보내기]이다. 하지만 이외에 여러가지 옵션이 있다. 예를 들어 엑셀이나 텍스트 문서의 파일 데이터를 가져오는 경우도 있다.(아마도 파일을 가져오는 작업이 더 많을 듯 하다.) 이런 작업이 계속 반복 될 때 매번 마법사를 통하기에는 많은 불편함이 있다. 또한 로컬이 아닌 위치에 있을 때는 어떻게 할까? 다양한 옵션을 통해서 데이터를 가져오는 방법을 알아 보자. 실습에 사용할 텍스트 파일을 생성. 실습용 테이블 생..

SQL Server Stored Procedure 암호화 Version : SQL Server 2000, 2005, 2008, 2008R2, 2012 웹 서버나 기타 응용 프로그램에서 SQL Server에 데이터를 입력하거나 조회, 삭제 등의 작업을 할 때 실제 코드를 알 수 없도록 Stored Procedure(SP)를 만들어서 사용한다. SP의 장점은 캡슐화가 되어 있어 외부 사용자가 코드를 확인 할 수 없으며 성능상에서도 많은 이점이 있기에 거의 대부분이 사용한다. 하지만 관리자나 개발자는 SP의 코드를 볼 수가 있다. 중요한 로직의 경우 SP 자체를 암호화 하는 방법을 없을까? 방법은 간단하다. 프로시저 생성시 암호화 옵션을 추가 하면 된다. 암호화 옵션은 WITH ENCRYPTION 을 사용한다..

SQL Server 대칭키 vs 비대칭키 암호화 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server 암호화는 데이터를 보호하기 위해 관리자가 사용할 수 있는 여러 가지 방어 중 하나이다. 암호화 알고리즘은 사용자가 무단으로 쉽게 바꿀 수 없는 데이터 변환을 정의 한다. SQL Server에서는 DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128bit RC4, DESX, 128bit AES, 192bit AES, 256bit AES 등을 지원한다.(SQL Server 버전에 따라 지원되는 알고리즘이 다르다.) 알고리즘의 일반 적인 특징으로는 강력한 암호화는 일반적으로 약한 암호화 보다 더 많은 CPU 리소스를 사용한다. 짧..

SQL Server 마스터 키 관리 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server는 계층적 암호화 및 키 관리 인프라로 데이터를 암호화 한다. 각 계층은 인증서, 비대칭 키 및 대칭 키 조합을 사용하여 해당 계층의 하위 계층을 암호화 한다. [SQL Server 2005] 암호화 계층의 각 계층이 해당 계층 아래의 계층을 암호화하는 것을 보여 준다. 최상위 계층인 서비스 마스터키는 Windows DP API로 암호화 된다. 암호화 메커니즘 인증서 비대칭 키 대칭 키 [SQL Server 2008 이상] 비대칭 키 및 대칭 키를 SQL Server의 외부모듈에 저장할 수 있다. 계층의 시작 부분에 대한 액세스도 암호화로 보호 된다. 암호화 메커니즘 T..

대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 Version : SQL Server 2008, 2008R2, 2012 개인 정보에 대한 중요성이 강화 되면서 개인을 식별 할 수 있는 데이터에 대해서는 모두 암호화를 하도록 법적으로 강제 되고 있다. 테이블에 저장된 데이터를 암호화하는 여러 방법이 있지만 SQL Server의 대칭키를 이용하여 열 수준(컬럼단위) 암호화 하는 방법을 알아 보자. 테스트를 위하여 샘플 데이터를 생성 한다. 여기에는 중요한 개인정보인 전화번호를 예를 들었다. CREATE TABLE MEMBER( NAME NVARCHAR(50), PHONE NVARCHAR(50) ) GO INSERT INTO MEMBER VALUES ('강성욱' , '010-3786-0318') ..

DMV를 사용하여 누락된 인덱스 확인 Version: SQL Server 2005, 2008, 2008R2, 2012 SQL Server에서 인덱스가 생성되어 있지만 사용되지 않는 인덱스에 대해 식별하는 방법에 대해서 알아 보자. SQL Server 2005 부터 검색 기록을 바탕으로 인덱스 후보를 식별하기 위한 DMV가 도입 되었다. 누락된 인덱스를 확인 하기 위한 DMV는 다음과 같다. Sys.dm_db_missing_index_details : 누락된 인덱스에 대한 상세 정보 반환 Sys.dm_db_missing_index_group_stats : 누락 된 인덱스 그룹에 대한 요약 정보 반환 Sys.dm_db_missing_index_groups : 누락 된 인덱스의 특정 그룹에 대한 반품 정보 반..

DMV를 이용한 SQL Server 대기 상태 확인 Version : SQL Server 2005, 2008, 2008R2, 2012 사용자가 나에게 와서 "데이터베이스가 느려요" 라고 말해주었다. 나는 어디에서부터 시작 할까? SQL Server의 성능을 확인하기 위해서는 성능 카운터, 프로파일러 등다양한 방법이 있다. 하지만 이것으로는 왠지 부족 한것 같다. DMV를 통하여 현재 SQL Server의 실행중인 대기 스레드 상태를 확인해 보자. Sys.dm_os_wait_stats는 실행 중인 모든 스레드로 인해 발생한 대기 정보를 반환한다. 이 DMV를 사용하면 SQL Server와 관련된 문제와 특정 쿼리 및 일괄 처리와 관련된 성능 문제도 확인 할 수 있다. Sys.dm_os_wait_stats를..