SQL Server

02 Apr 2020 - Jake Na

Microsoft SQL 서버에 대하여 기본 학습이 필요하여 이 문서를 작성합니다. 학습내용은 sqlservertutorial.net에서 제공되는 샘플 DB와 설명을 기초하였으니 자세한 내용이 필요할 경우 해당 사이트를 참조하기 바랍니다.

SQL Express 2019 설치 및 구성

• 윈도우즈 로그인이 아닌 SQL 서버 로그인이 필요할 경우 sa 계정을 활성화 시켜 주어야 함 (SQL Server Mananagement Studio 설치후 설정 가능)
• 외부에서 SQL 서버 접근이 필요할 경우 TCP 포트를 추가로 설정해 주어야 함 (SQL Server Configuration Manager를 활용하여 TCP 사용여부 및 고정포트 설정) 기본은 동적포트를 사용하고 있기에 고정포트를 사용하도록 하고, 윈도우 방화벽 inbound 규칙을 추가하여 해당 포트에 대한 접근이 가능하도록 조치해야 함.
• SQL Server 2019 설치 이후 SQL Server Configuration Manager는 C:\windows\system32\SQLServerMananger15.msc 사용 (Windows 10 기준)
• Windows 10 방화벽 설정은 c:\windows\system32\WF.msc 사용

Sample DB 다운로드 및 SQL Server DB 업로드

• 참조: https://www.sqlservertutorial.net/load-sample-database/

BikeStores DB 생성, 스키마 생성, 테이블 정의 및 Sample Data 업로드 수행

• 데이블 생성이나 데이터 업로드이후 Management Studio에서 갱신이 안되는 경우 재연결 필요

Table, View, Indexed View

• 복잡한 로직이 들어간 쿼리의 경우, 특히 테이블간의 멀티 조인으로 결과를 얻고자 하는 경우, 뷰를 생성하여 사용할 수 있다. 뷰의 경우 가상의 테이블 역할을 하기에 단순 쿼리로 필요한 데이터를 추출할 수 있다. 예를 들면,

CREATE VIEW product_master WITH SCHEMABINDING AS
  SELECT
    product_id,
    product_name,
    model_year,
    list_price,
    brand_name,
    category_name
  FROM
    production.products p
  INNER JOIN production.brands b ON b.brand_id = p.brand_id
  INNER JOIN production.categories c ON c.category_id = p.category_id;

• products, brands, cateogries 테이블을 조인하여 얻은 결과를 product_master 뷰로 만든 예를 보여주고 있다. 여기서 WITH SCHEMABINDING은 현재의 뷰에 사용된 테이블의 스키마가 변경될 경우 우선 해당 뷰를 먼저 삭제하고 진행해야 함을 의미한다.

• 'SELECT * FROM product_master' 쿼리를 실행할 경우 products, brands, categories 테이블이 참조는 것으로 알 수 있다. 자주 변경되지 않는 데이터를 대상으로 성능을 고려하여 Indexed View를 만들어 사용할 수 있다. 위와 같아 뷰를 만들고,

CREATE UNIQUE CLUSTERED INDEX ucidx_product_id
ON production.product_master(product_id);

• 위 명령을 이용하여 클러스터드 INDEX를 만들어서 뷰의 내용을 인덱스화하여 별도 저장하므로서 읽기 성능을 높일 수 있다.

Indexes

• 인덱스는 쿼리 속도를 높이기 위하여 요구되는 테이블/뷰에 연관된 특별한 데이터 구조, 즉 B-Tree (Balanced Tree)이다.

Clustered Indexes vs. NonClustered Indexes

• Primary Key가 지정되지 않은 테이블의 열(Row)은 Heap라고 불리는 정렬되지 않은 구조에 저장이 된다. 따라서, 테이블 쿼리에 대하여 순차스캔을 진행하므로 데이터가 많을 경우 검색속도가 선형적으로 증가한다.
• 데이터베이스는 검색속도를 높이기 위해서 위하여 인덱스라고 불리는 전용 구조를 제공한다. 제공하는 인덱스 형식은 두가지인데, clustered index와 nonclustered index이다.
• clustered index는 B-Tree로 clustered key(단일컬럼 혹은 복합컬럼)를 기준으로 루트(Root) 노드와 중간(Intermediate) 노드, 말단(Leaf)노드로 구성되는데 루트와 중간 노드는 인텍스 페이지(index pages)를 담고 있고, 말단 노드는 테이블 데이터열(data rows)로 구성된 데이터 페이지(data pages)를 가진다. 각 페이지는 B-Tree상에 동일 수준에 인접한 노드의 페이지와 double linked list로 연결되어 있다. 테이블 데이터가 clustered index를 구성하는 B-Tree의 말단 노드의 데이터 페이지로 직접 접근이 가능하도록 되어 있기에, clustered index는 데이블당 하나로 제한되어 clutered key를 기준으로 정렬되는 것을 알 수 있다. 따라서, Primary key를 테이블에 정의할 경우 해당 key를 기준으로 clustered index가 만들어 진다, 즉 Primary key constraint라고 함.
• nonclustered index는 clustered index와 유사하게 B-Tree 구조로 만들어 진다. clustered index와의 차이는 해당 B-Tree의 말단 노드에 테이블 데이터열을 포함하는 데이터 페이지가 아닌 테이블의 key값 (Primary Key 및 Index 생성시 포함된 컬럼값들)을 포인팅하는 포인터(Rows Pointers)를 가진다(포인터를 통해서 Primary key를 얻고, primary key 기반의 clustered index를 사용하여 다른 컬럼 데이터에 접근할 수 있다). 테이블의 데이터열과는 별개로 저장되므로, 하나의 테이블에 여러개의 nonclustered index를 만들어서 사용할 수 있다. 대신, 검색시 데이터에 접근하기 위해서 포인터를 이용해야 하기 때문에 한번 더 I/O 접근이 필요하며, 인덱스에 포함되지 않은 다른 컬럼 데이타를 얻기 위해서는 2차 검색을 할 필요가 있다(이 부분은 indexes with included columns으로 필요한 데이터 컬럼을 미리 인덱스 생성시 포함할 경우 2차 검색이 필요없음). 특정 컬럼을 기준으로 검색 속도를 높이려고 하면 'CREATE [NONCLUSTERED] INDEX'로 nonclustered index를 만들어서 사용하는 것이 필요하다.
• 기본적으로 table full scan과 index scan의 차이는 선형(linear) vs. 로그(log)의 차이가 있기에 검색속도에 많은 영향을 주므로 인덱스를 잘 활용할 필요가 있다. Index는 부가적인 데이터 구조이므로 space complexity를 고려할 필요가 있는 경우 time-space tradeoff 추가 고려 필요하다.

Rename Indexes, Diable/Enable Indexes, Unique Indexes, Drop Indexes

Indexes with included columns

• Email을 기준으로 nonclustered index 생성시 first_name, last_name 컬럼을 포함할 경우 email의 특정값을 이용하여 customer_id, first_name, last_name을 select할 경우 customer_id를 이용한 clustered index 2차 검색을 하지 않고 바로 first_name, last_name을 얻을 수 있어서 검색속도를 향상 시킬 수 있다. 이에 대한 예는 아래와 같다.

CREATE UNIQUE INDEX ix_cust_email_inc
       ON sales.customers(email)
       INCLUDE(first_name,last_name);

Filtered Indexes

• nonclustered index는 쿼리 성능향상에 크게 기여하지만 스토리지 및 유지관리 측면에서 비용이 수반된다. 인덱스 키 컬럼 데이터의 카피본이 저장되어야 하고, 테이블 Rows에 대한 삽입, 업데이트, 삭제시 연관된 nonclustered index에 대한 업데이트가 요구된다. 단지 테이블의 일부만이 쿼리되는 경우는 매우 비효율적이기에 NULL이 많이 포함된 sparse columns에 대하여 filtered indexes가 사용된다. 다음과 같이 sales.customers 테이블의 phone 커럼에 대하여 filtered index를 만들 수 있다. phone이 NULL인 rows는 nonclustered index에 포함되지 않는다.

CREATE INDEX ix_cust_phone ON sales.customers(phone) WHERE phone IS NOT NULL;

Indexes on computed columns

• 아래와 같이 email의 로컬 파트를 계산하여 쿼리할 경우 customer_id 기반 clustered index 전체를 스캔해야 하기에 매우 비효율적인다. 이러한 경우 computed column을 추가하여 해당 컬럼에 nonclustered index를 생성하여 검색속도를 높일 수 있다.

SELECT
    first_name,
    last_name,
    email
FROM
    sales.customers
WHERE
    SUBSTRING(email, 0, CHARINDEX('@', email, 0)) = 'garry.espinoza';

• Oracle 경우 function-based indexes, PostgreSQL 경우 expression-based indexes를 각각 지원하는 반면, SQL Server 경우 다음과 같이 새로운 컬럼을 추가하고, 해당 컬럼으로 인덱스를 생성하여 지원한다.

ALTER TABLE sales.customers
ADD email_local_part AS SUBSTRING(email, 0, CHARINDEX('@', email, 0));
;;
CREATE INDEX ix_cust_email_local_part ON sales.customers(email_local_part);

Stored Procedures

• SP(Stored Procedure)는 하나 이상의 Transact-SQL 문장을 논리적인 단위로 그룹화하기 위하여 사용된다. SP는 SQL 서버에 이름이 부여된 객체로 저장되며, 최초로 호출되는 시점에 실행 계획이 생성되어 캐쉬에 저장된다. SQL 서버는 실행 계획을 캐쉬에서 재사용하기에 매우 빠르고 안정적으로 SP를 실행할 수 있다.

A basic guide to SP, Parameters, Variables, Output Parameters

Control-of-Flow Statements : BEGIN…END, IF ELSE, WHILE, BREAK/CONTINUE

Cursors, Handling Exceptions, Dynamic SQL

User-defined Functions

User-defined scalar functions

Table varialbes

Table-valued functions

Removing user-defined functions

Triggers

• DML(Data Manipulation Language) triggers은 테이블에 대한 INSERT, UPDATE, DELETE시 자동적으로 호출되는 트리거이며, DDL(Data Definition Lanaguage) triggers는 CREATE, ALTER, DROP 등 스키마 변경시 호출되거나 DDL-like operations을 수행하는 SP에 반응하여 호출된다.

Creating a trigger in SQL Server

Creating an INSTEAD OF trigger

Creating a DDL trigger

Enabling/Diabling triggers

Viewing the definition of a trigger

Listing all triggers in SQL Server

Removing triggers

Functions

Aggregate Functions

Date Functions

String Functions

System Functions

Window Functions