Data Science Lab

테이블 외래키 트리 확인 및 데이터 삭제하기 Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 SQL Server에서 데이터 무결성을 유지하기 위해 생성한 외래키 제약조건이 있는 경우 데이터를 삭제 할 때 제약 조건에 위배되지 않도록 순차적으로 작업을 해야 한다. 테이블에 생성되어있는 외래키 연결에 대해 트리 구조로 살펴보고 순차적으로 데이터를 삭제할 수 있는 방법에 대해서 알아본다. 실습을 위해 테이블을 생성한다. 스크립트를 실행 하면 그림과 같은 외래키로 구성된 테이블이 생성된다. -- sample tables with FKs create table dbo.M (id int primary key, M_c1 int not null, M_c2 date..

기본 추적을 사용한 SQL Server 스키마 변경사항 캡처 Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 SQL Server에서 스키마 변경이 발생하였을 때 이를 캡처하고 확인할 수 있는 방법에 대해서 알아본다. 기본적으로 SSMS에서는 스키마 변경 기록 보고서를 제공한다. 이 보고서에서는 DDL 작업에 대한 내용과 시간, 로그인 사용자, 사용자 이름 등 유용한 정보가 표시되지만 SQL Server가 시작된 이후의 내용만 확인할 수 있다. SQL Server가 다시 시작되더라도 변경 내용을 히스토리로 관리할 수 있도록 사용자 테이블에 변경 내역을 저장하는 방법에 대이다. 우선 변경 내용을 저장할 테이블을 생성한다. -- create table t..

SQL Server 2016 JSON 출력에 NULL 값 포함하기 Version : SQL Server 2016 SQL Server 2016의 새로운 기능인 JSON 형식에서 기본적으로 NULL 값은 표시 하지 않는다. JSON 형식의 출력에서 강제로 NULL 값을 포함하는 방법에 대해서 알아본다. 아래 스크립트는 전형적인 SQL Server 출력을 나타내는 결과이다. Size열을 보면 NULL값이 표시됨을 확인할 수 있다. Use AdventureWorks2014 go SELECT M.ProductModelID,M.Name as [ProductModel.Name], ProductID, Size FROM Production.Product P INNER JOIN Production.ProductModel ..

SQL Server 특정 테이블의 모든 컬럼에서 문자열 찾기 Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 특정 문자열을 사용하는 값을 찾을 때 일반적으로 like를 사용한다. 예를들면 아래 스크립트과 같이 특정 컬럼을 지정하여 그 안에서 like 검색을 한다. select * from Person.Address where city like '%test%' 한 컬럼에서 찾을 때에는 매번 손으로 타이핑하여 찾을 수도 있지만 테이블의 여러 컬럼을 확인확인 할 때 컬럼명을 계속 입력하는 것도 매우 불편한 일이다. 아래 스크립트를 사용하여 SP를 생성하여 사용하면 테이블의 모든 컬럼에서 문자열 검색을 사용할 수 있어 편리하다. USE master GO C..

SQL Server 임시 테이블 특성 Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 임시 테이블은 tempdb 데이터베이스에 저장되었다가 더 이상 사용되지 않을 때 자동으로 삭제 된다. 그 외 기능은 일반 테이블과 비슷하다. 임시 테이블에는 전역과 로컬 두 유형이 있으며 로컬은 # 기호로 시작하며 전역은 ## 기호로 시작한다. 로컬 임시테이블은 현재 사용자의 연결에만 표시되고 사용자가 SQL Server 인스턴스와의 연결을 종료하면 삭제 된다. 전역은 테이블 생성 후 모든 사용자에게 표시되고 테이블을 참조하는 모든 사용자가 SQL Server인스턴스와 연결을 종료하면 삭제 된다. 즉 테이블을 생성한 사용자가 SQL Server와 연결을 끊어도 다른..

트리거를 사용하여 특정 컬럼 업데이트 하기 (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 동적 데이터 마스킹은 실제 데이터가 암호화 되지 않는다. 그러므로 데이터는 언제나 노출 될 가능성이 있다. 동적 데이터 ..