Data Science Lab

트리거를 사용하여 특정 컬럼 업데이트 하기 (After 트리거) Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 트리거는 특정 이벤트가 발생(INSERT, UPDATE, DELETE)할 때 자동적으로 실행되는 특수한 종류의 저장프로시저이다. 트리거는 FOR|AFTER와 INSTEAD OF 인수가 있다. FOR|AFTER : AFTER는 DML 트리거를 지정한 모든 작업이 성공적으로 실행되었을 때만 트거가 실행도록 지정한다. 모든 참조 연계 동작 및 제약 조건 검사도 이 트리거가 실행되기 전에 성공해야 한다. INSTEAD OF : 트리거를 시작하는 SQL문 대신 DML 트리거가 실행되도록 지정한다. DDL 또는 LOGON 트리거에 대해서는 INSTE..

컬럼스토어 인덱스 성능 (Columnsotre Index Performance) Version : SQL Server 2012, 2014, 2016 SQL Server 2012부터 도입된 컬럼스토어 인덱스는 열 기반 데이터 저장소 및 열 기반 쿼리 처리를 사용하여 데이터를 저장하고 관리한다. 인덱스는 주로 대량 로드 및 전용 쿼리를 수행하는 데이터웨어하우징 작업에 효과적이다. 전체 테이블 검색을 사용하는 쿼리에서는 뛰어난 성능을 제공하지만 특정 값을 찾아 데이터를 검색하는 쿼리에는 부적합 하다. 자세한 내용은 MSDN을 참고한다. Columnstore Index 소개 https://msdn.microsoft.com/ko-kr/library/gg492088(v=sql.120).aspx 일반 B-tree 인..

In-Memory 최적화 파일 경로 변경 Version : SQL Server 2014, 2016 SQL Server 2014이상에서는 인 메모리 OLTP 기능과 메모리 최적화 테이블을 만들 수 있다. In-Memory OLTP (In-Memory Optimization) https://msdn.microsoft.com/en-us/library/Dn133186(v=SQL.120).aspx 이 기능을 사용하면 SQL Server는 실제로 성능을 최적화하는 네이티브 코드를 생성한다. 그 결과 DLL, PDB 파일과 중간 파일이 생성된다. 메모리 최적화는 각 저장 프로시저 또는 테이블이 별도로 파일이 생성될 수 있도록 설정을 해야 한다. 일반적인 상황의 경우 기본 경로를 사용하므로 사용자가 설정하지 않아도 된..

SQL Server 2016 Query Store 실행된 쿼리에 대한 실행 계획 및 통계 정보를 확인하자 Version : SQL Server 2016 SQL Server 2016에 도입된 Query Store는 요청된 쿼리가 실행 될 때 실행 된 쿼리를 저장 및 실행 계획 등을 함께 저장하여 통계 및 계획을 추적하는데 도움이 된다. 쿼리를 저장하기 위해서는 Query Store를 활성화 해야 한다. SSMS 및 T-SQL을 사용하여 쿼리 스토어 기능을 활성화할 수 있다. ALTER DATABASE [DEMO_1] SET QUERY_STORE = ON GO 쿼리 스토어를 활성화하면 쿼리 저장과 관련된 다른 옵션을 설정할 수 있다. SSMS 및 T-SQL을 사용하여 설정을 변경할 수 있다. ALTER DA..

SQL Server 2016 동적 데이터 마스킹을 우회하여 원본 데이터 출력하기 Version : SQL Server 2016 SQL Server 2016에 도입된 동적 데이터 마스킹은(Dynamic Data Masking, DDM) 권한이 없는 사용자에 대해 중요한 데이터를 마스킹해 표시함으로써 데이터 노출을 제한한다. 동적 데이터 마스킹을 수행하면 고객이 응용 프로그램 레이어에 대한 영향을 최소화하면서 표시할 중요한 데이터의 양을 지정할 수 있도록 하여 중요한 데이터에 대한 무단 액세스를 방지할 수 있다. 동적 데이터 마스킹 : http://sqlmvp.kr/220425326727 동적 데이터 마스킹은 실제 데이터가 암호화 되지 않는다. 그러므로 데이터는 언제나 노출 될 가능성이 있다. 동적 데이터 ..

SA 계정 이름 변경 및 비활성화 Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 SQL Server를 설치하면 기본적으로 SA 계정이 생성되어 사용된다. 보안 규칙이 잘 정의된 조직이라면 SA에 대한 보안에 많은 신경을 쓰지만 일부 조직에서는 SA를 기본적으로 사용하는 곳도 많다. SA 계정은 외부 위협으로부터 많이 사용되는 계정이므로 보안을 강화하거나 비활성화 하여 노출을 최소화하는 것이 좋다. SA 계정에 대한 보안을 강화하기 위한 방법은 크게 4가지가 있다. 암호를 추측할 수 없도록 복잡하게 구성한다. SA의 이름을 변경한다. SA를 사용하지 않도록 비활성화 한다. 다른 계정 이름이 SA가 없는지 확인한다. [SA 로그인 이름 바꾸기] SSM..

SQL Server 데이터베이스 속성을 확인할 때 사용되는 master.dbo.spt_values 복구 Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 SSMS에서 SQL Server의 속성을 클릭하였을 때 master.dbo.spt_values 오류가 발생하였을 때 해결하는 방법에 대해서 알아본다. SSMS에서 서버 속성을 클릭하였을 때 master.dbo.spt_values 오류가 발생하면 다음과 같은 경고문이 나타난다. master.dbo.spt_values 프로시저는 다양한 데이터 항목을 포함하는 문서화 되지 않은 항목이다. 이 프로시저는 C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\..

SQL Server 2016 라이브 쿼리 통계 Version : SQL Server 2016 SQL Server 2016 이전까지는 쿼리에 대한 실행 계획, 통계 정보 등이 쿼리가 완료된 후에 확인 할 수 있었다. SQL Server 2016부터 도입된 라이브쿼리 통계는 쿼리가 실행 되는 동안의 실행 계획 및 처리 행수, 실행 통계 등을 확인할 수 있다. 라이브 쿼리 통계는 SSMS에서 상단 메뉴 [쿼리] 또는 [빠른 실행아이콘]에서 [활성 쿼리 통계 포함]을 선택하여 활성화 할 수 있다. 라이브 쿼리 통계를 활성화 후 쿼리를 실행하면 별도의 탭에 실행계획의 흐름이 점선으로 표시 된다. 각 운영자는 실행 단계에서 얼마나 많은 작업이 수행되고 있는지 알 수 있다. 실행 계획에서 마우스 오른쪽을 클릭하여 나..

XML로 생성된 정보를 테이블로 저장하기(sp_xml_preparedocument) Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 XML로 작성된 정보를 파싱하여 테이블로 저장하는 방법에 대해서 살펴본다. SQL Server에서제공되는 sp_xml_preparedocument 프로시저와 OPENXML 함수 기능을 함께 사용하여 편리하게 XML데이터를 사용할 수 있다. sp_xml_preparedocument는 입력으로 제공되는 XML 텍스트를 읽고 MSXML 파서(Msxmlsql.dll)을 사용하여 텍스트의 구문을 분석하여 사용할 수 있는 상태로 제공한다. sp_xml_preparedocument는 XML문서의 새로 생성된 내부 표현에 액세스하는데..

SSRS 보고서에 사용된 쿼리문 확인(테이블, 컬럼, 프로시저 찾기) Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 SSRS 보고서에서 사용되고 있는 쿼리문을 확인하여 테이블, 컬럼 및 프로시저를 확인하는 방법에 대해서 살펴본다. 이 정보는 리포트 데이터베이스의 Catalog 정보를 XML로 변환 및 파싱하여 정보를 추출 할 수 있다. 리포트 데이터베이스에서 Catalog 테이블을 조회하면 다음과 같은 컬럼 정보가 나타난다. Content 컬럼을 VARBINAMRY형식으로 가져와서 XML로 변경한다. SELECT C.Name,c.Path,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML,C...