MSSQL 특징

MSSQL 특징

February 16, 2020 ( last updated : February 15, 2020 )
mssql

https://github.com/gmm117/gmm117.github.io

Abstract

    mssql 특징

용어정리

SQL Server 개요

  1. 인덱스 : 데이터 검색에 빠른 처리를 할 수 있게 하거나 데이터 무결성을 강화하기 위해서 사용되는 개체

    데이터무결성 : 데이터의 정확성과 일관성을 유지하고 보증하는 것

  2. 뷰 : 자주 사용되는 쿼리나 복잡한 쿼리를 재사용하기 위해 정의해 놓은 가상 테이블

  3. 트리거 : 데이트가 변경될 때 자동적으로 실행되는 특별한 형태의 저장 프로시저

  4. 룰 : CHECK 제약 조건과 같은 역할을 수행하며, 테이블의 컬럼이나 사용자 정의 데이터형과 바인딩하여 사용된다.

    EXEC sp_bindrule ‘ZipCode_rule’, ‘ZipCode’

  1. Master : 서버와 관련된 모든 자료를 저장한다.

  2. TempDB : 임시테이블을 만들거나 ORDER BY, DISTINCT 등의 구문을 동반시 임시 작업 공간으로 사용

  3. Model : 새로운 데이터베이스 생성시 모델이 되는 데이터베이스

  4. MSDB : 자동화 관리와 관련된 내용을 기록한다.

  5. Distribution : 복제를 설정하였을 때 로그 분배를 위해 사용한다.

Transact-SQL 기초

SELECT GETDATE() AS 일시, 2 수량, 단가=450

SELECT *

FROM [MY TABLE] // 공백 문자 포함의 경우 구분

WHERE [ORDER] = 10 // 일부예약어의 경우 구분

SELECT type, AVG(price) AS avgPrice

FROM titles

WHERE royalty = 10

GROUP BY type

HAVING AVG(price) < 80

예)

– DISTINCT를 사용한 중복 데이터 제거 SELECT DISTINCT deptno FROM emp;

– GROUP BY를 사용한 중복 데이터 제거 SELECT deptno FROM emp GROUP BY deptno;

DEPTNO

30
20
10

  1. 언제 사용되나?

    GROUP BY : 집계함수를 사용하여 특정 그룹으로 구분 할 때는 절을 사용

    DISTINCT : 특정 그룹 구분없이 중복된 데이터를 제거할 경우에 사용

예) SELECT COUNT(DISTINCT d.deptno) “중복제거 수”, COUNT(d.deptno) “전체 수” FROM emp e, dept d WHERE e.deptno = d.deptno;

– 집계 함수가 필요한 경우는 GROUP BY를 사용해야 한다. SELECT deptno, MIN(sal) FROM emp GROUP BY deptno;

  1. 언제 사용되나?

  1. SELECT INTO 사용법

    -> 원본 있고, 대상 테이블을 새롭게 생성하려는 경우 사용

    -> SELECT * INTO AA FROM A

       -> A테이블과 같은 컬럼과 데이터를 가지는 AA라는 테이블 생성

    1-1 A테이블의 특정 컬럼만 가져오고 싶다면,

           SELECT * INTO AA

          FROM ( SELECT COL1, COL2, COL3.....

                            FROM AA

                      ) AS TEMP

  -> A테이블의 특정 컬럼만 가져와서 AA라는 테이블을 생성하여 데이터를 INSERT한다.

  2. INSERT INTO 사용법

-> 위에서 테이블A와 테이블B는 스키마가 동일해야 한다. ( 데이터 구조, 같은 포맷을 가진 테이블)

-> 만일 A보다 컬럼수가 적으면 SELECT뒤에 컬럼을 명시해준다

  1. 내부 조인(INNER JOIN) / 외부(OUTER JOIN)

● INNER JOIN

● LEFT OUTER JOIN

● RIGHT OUTER JOIN

SELECT stor_ID AS StoreID, Qty*2 AS Quantity INTO #SalesTemp FROM sales

SELECT StoreID, SUM(Quantity) FROM ( SELECT Stor_ID AS StoreID, Qty AS Quantity FROM sales UNION ALL SELECT * FROM #SalesTemp ) t GROUP BY StoreID

결과)

6380 24 7066 375

DB 생성과 관리

CREATE DATABASE [Testpubs] ON PRIMARY (NAME = N’Testpubs_Data’ ,FILENAME = N’C:\TEMP\Testpubs_Data.MDF’ ,SIZE = 5, MAXSIZE=100 ,FILEGROWTH=10%) LOG ON (NAME = N’Testpubs_Log’ ,FILENAME = N’C:\TEMP\Testpubs_Log.LDF’ ,SIZE = 2 ,FILEGROWTH=1MB)

● ON : 데이터부분이 저장되는 데이터파일을 명시적으로 정의

● PRIMARY : 주파일 정의

● NAME : 논리적 이름

● LOG ON : 데이터베이스 로그가 저장되는 디스크 파일을 명시적으로 정의함.

● FOR LOAD : 데이터베이스의 백업자료로 복구하며, 초기화하기 위한 옵션

● FOR ATTACH : 기존 운영체제 파일 집합에서 데이터베이스 연결 하기 위한 옵션

ALTER DATABASE [Testpubs] MODIFY FILE (NAME = ‘Testpubs_Data’ ,SIZE = 10)

ALTER DATABASE [Testpubs] ADD FILEGROUP [Second]

ALTER DATABASE [Testpubs] ADD FILE (NAME = N’Testpubs_Data2’ ,FILENAME = N’C:\Temp\Testpubs_Data2.NDF’ ,SIZE = 1, FILEGROWTH = 10% ) TO FILEGROUP [Second]

ALTER DATABASE [Testpubs] ADD FILEGROUP [FG_Index] ALTER DATABASE [Testpubs] ADD FILEGROUP [FG_BLOB]

ALTER DATABASE [Testpubs] ADD FILE (NAME = N’Textpubs_Data3’ ,FILENAME = N’C:TEMP\Testpubs_Data3.NDF’ ,SIZE = 1 ,FILEGROWTH = 10%) TO FILEGROUP [FG_Index]

– BLOB Binary Large Object 약자로 image,text,ntext 데이터형
ALTER DATABASE [Testpubs] ADD FILE (NAME = N’Textpubs_BLOB’ ,FILENAME = N’C:TEMP\Testpubs_BLOB.NDF’ ,SIZE = 1 ,FILEGROWTH = 10%) TO FILEGROUP [FG_BLOB]

CREATE TABLE [pub_info] ([pub_id][char] (4) PRIMARY KEY ON [FG_Index] NOT NULL ,[pub_name]char NOT NULL ,[logo][image] NULL ,[pr_info][text] NULL ) ON [PRIMARY] TEXTIMAGE_ON [FG_BLOB]

  1. 자동파일 축소 : 주기적으로 사용공간의 25% 빈 여우공간을 두고 축소하여 운영체에 반납한다.

DBCC SHRINKFILE(Testpubs_Data, 25) - 30분간격으로 주기적으로 작업

  1. 파일 수동 축소

● EMPTYFILE : 모든 데이터를 동일한 파일 그룹의 다른 파일로 이동시키고 빈파일로 남김

                     해당 파일을 물리적으로 다른 공간을 이동시키고 제거하는 목적

DBCC SHRINKFILE(Testpubs_Data2, EMPTYFILE)

ALTER DATABASE Testpubs REMOVE FILE Testpubs_Data2

● NOTRUNCATE : 해제된 파일 공간을 파일에 보유하고 해제된 공간을 운영체제에 반환하지 않는다.

● TRUNCATEONLY : 파일에 사용되지 않은 공간을 운영체제에 반환, 데이터를 이동하지 않고 파일 크기를 줄인다.

                            TRUNCATEONLY 사용시 target_size는 무시된다.
DBCC SHRINKFILE(Testpubs_Data, 5, [EMPTYFILE NOTRUNCATE TRUNCATEONLY )
  1. 데이터베이스 수동 축소

DBCC SHRINKDATABASE(Testpubs, 25) 25% 빈공간을 두고 축소(NOTRUNCATE , TRUNCATEONLY )

ALTER DATABASE Testpubs MODIFY FILEGROUP [FG_Index] DEFAULT

CREATE TABLE Test( id int)

EXEC sp_help Test

  1. 데이터베이스명 변경

ALTER DATABASE Testpus SET SINGLE_USER WITH ROLLBACK IMMEDIATE

EXEC sp_renamedb ‘Testpubs’, ‘TestpubsNew’

EXEC sp_dboption TestpubsNew, ‘Single user’, FALSE

  1. 데이터베이스 삭제

DROP DATABASE TestpubsNew

ALTER DATABASE Testpus SET SINGLE_USER WITH ROLLBACK AFTER 300(초)

EXEC sp_renamedb ‘Testpubs’, ‘TestpubsNew’

EXEC sp_dboption TestpubsNew, ‘Single user’, FALSE

데이터형식 및 형변환

SELECT ASCII(‘A’) AS ASCIIA ,UNICODE(N’가’) AS UNICODE가

SELECT CHAR(65) AS CHAR65 ,NCHAR(44032) AS CHAR44032

=> 65, 44032 / A, 가

  1. 정확한 숫자 : decimal, numeric

  2. 근사 숫자 : float, real(float(24))

CREATE Table TestFloat (xNumeric Numeric(7,3) //DEFAULT 시 전체자리수 18, 소수점 이하 자릿수 0 ,xDecimal Decimal(7,3) ,xFloat Float //근사값 저장되며 내부적으로 계산이 진행됨. ,xReal Real)

INSERT TestFloat VALUES(3,3,3,3) INSERT TestFloat VALUES(12.3,12.3,12.3,12.3) SELECT * FROM TestFloat

3.000 3.000 3 3 12.300 12.300 12.3000000001 12.3

CREATE TABLE TestTimeStamp (ID int PRIMARY KEY ,Name char(10) ,xTimeStamp Timestamp)

CREATE TABLE TestRowversion (ID int PRIMARY KEY ,Name char(10) ,xRowVersion rowversion)

INSERT TestTimeStamp(ID, NAME) Values(1, ‘홍길동’) INSERT TestRowversion(ID, NAME) Values(1, ‘홍길동’)

SELECT @@DBTS

  1. Sql_variant
● 하나의 컬럼에 여러 데이터형 저장을 지원하기 위한 데이터형

● ntext, text, image, timestamp,sql_variant 지정 불가능

1) 연산에 사용되기전에 기본 데이터 형식 값으로 변환되어야 한다.

    SELECT CONVERT(int, @MyVar1) + CONVERT(int, @MyVal2)

2) 최대 8016 바이트 이상 길이 가질 수 없다.

3) IDENTITY 속성 설정 불가능

4) 대표키나 참조키 사용가능

5) ATLTER TABLE 구문을 이용하여 text, ntext, image, timestamp, sql_variant 형 바꿀 수 없다.

DECLARE @MyVal1 AS sql_variant DECLARE @MyVal2 AS sql_variant SET @MyVal1 = 1 SET @MyVal2 = ‘2’ SELECT CONVERT(int, @MyVal1) + CONVERT(int, @MyVal2)

  1. Uniqueidentifier
- GUID형을 나타내기 위한 데이터 형식

CREATE TABLE MyUniqueTable (UniqueID UNIQUEIDENTIFIER DEFAULT NEWID() ,Characters char(10))

DECLARE @TableVar TABLE (ID int PRIMARY KEY, Name nchar(10))

INSERT INTO @TableVar VALUES (1, N’홍’) INSERT INTO @TableVar VALUES (2, N’승아’)

CREATE RULE ZipCode_rule AS @value LIKE ‘[0-9][0-9][0-9]-[0-9][0-9][0-9]’

EXEC sp_bindrule ‘ZipCode_rule’, ‘ZipCode’

float_expression : 소수점이 있는 근사치(float) 데이터 형식의 식입니다. 10자리가 넘을 경우 * 표현됨 length 총길이, 기본값은 10입니다. decimal 16자리보다 클 경우 잘리게 됨

예) SELECT ‘<’ + STR(11.1234) + ‘>’ < 11>

테이블과 제약 조건 생성과 사용

  1. 식별자 규칙
● 식별자 1-128개의 문자로 이루어짐

● @(at기호) : 지역변수, 매개변수 / # : 임시테이블, 프로시저 / ## : 전역 임시 테이블

● Transact-SQL @@ : @@IDENTITY

● 예약어, 중간공백, 특수문자 사용불가능

 CREATE TABLE [$Emplayee Data] -> 구분기호 사용시 식별자로 사용가능

CREATE TABLE #cnst_example

(

…

) INTO #cnst_exam

DECLARE @cnst_example TABLE

(

…

)

  1. [ON { <파일그룹> | DEFAULT }]

CREATE TABLE [pub_info] ([pub_id] [char] (4) PRIMARY KEY ON [FG_Index] NOT NULL // pub_id FG_Index 파일그룹 ,[pub_name] [char] (10) NOT NULL ,[logo] [image] NULL ,[pr_info] [text] NULL) ON [PRIMARY] // Default만 Primary TEXTIMAGE_ON [FG_BLOB] // logo, pr_info 파일 그룹

  1. NOT FOR REPLICATION

CREATE TABLE Sales1 (SaleID INT IDENTITY(1, 1) NOT FOR REPLICATION, CHECK NOT FOR REPLICATION (SaleID <= 99999) ,SalesRegion CHAR(2) ,CONSTRAINT ID_PK PRIMARY KEY (SaleID))

SET IDENTITY_INSERT Sales ON

INSERT INTO sales1(SaleID, SalesRegion) SELECT s2.SaleID, s2.SalesRegion FROM sales2 S2

SET IDENTITY_INSERT Sales OFF

INSERT INTO Sales1 VALUES(‘H0’);

1 ‘ho’

2 ‘hk’

1000 ‘hu’

1001 ‘ha’

3 ‘hi’

  1. ROWGUIDCOL

    • uniqueidentifier 컬럼에만 ROWGUIDCOL 지정가능, 컴럼 고유 식별자 컬럼이란 것을 나타냄

    • 속성 설정시 컬럼의 값이 생성되지 않고 INSERT로 삽입 시 생성됨.

CREATE TABLE TestGUIDCOL (Seq int IDENTITY PRIMARY KEY ,GUIDCOL uniqueidentifier DEFAULT(NEWID()) ROWGUIDCOL ,Message varchar(5000))

INSERT TestGUIDCOL (Message) VALUES(‘ROWGUIDCOL TEST’) INSERT TestGUIDCOL (Message) VALUES(‘IDENTITYCOL TEST’)

SELECT ROWGUIDCOL, Message FROM TestGUIDCOL WHERE IDENTITYCOL = 1 // IDENTITY의 행을 비교하는 구문

  1. CONSTRAINT

CREATE TABLE Sales

(

…

, CONSTRAINT PK_Sales PRIMARY KEY NONCLUSTERED

(

SaleDate, SlipNo

)

)

● 컴럼에 정수값을 자동증가 및 감소되는 속성을 정의

● CREATE ,ALTER TABLE 문으로 컬럼에 정의

● 사용이유 : 유일한 식별자를 만들고 기본키로 사용하기 위해서사용

  1. IDENTITY 속성

CREATE TABLE TestIDENTITY (ID int PRIMARY KEY IDENTITY(1000, 2), NAME char(10)) - 초기값, 증가치

  1. @@IDENTITY : 마지막 ID 리턴함.

    SET IDENTITY_INSERT를 ON할 경우 IDENTITY값을 수정가능

=> 유일값으로 설정하고 싶은 경우 PRIMARY KEY, UNIQUE KEY로 설정
  1. IDENTITYCOL 예약어
WHERE IDENTITYCOL = 1
  1. DBCC CEHCKIDENT
DBCC CHECKIDENT(TestIDENTITY, RESEED, 500)
  1. @@IDENTITY 함수

CREATE TABLE Sales

( CustomerId int not null

Amt AS ROUND(Qty*Price, -2)

)

INSERT INTO Sales VALUES(1115)

CREATE TABEL TestAlterTable( column_a INT)

ALTER TABLE TestAlterTable ADD column_b varchar(20) NULL

ALTER COLUMN column_b int

DROP COLUMN column_b

DROP TABLE TestAlterTable

● ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED

● 데이터 무결성 : 일관된 자료를 유지하기 위한 방법

                       제약사항, 기본값, 자료삽입, 수정 삭제를 통해서 지켜야할 기준을 유지함.

● 참조무결성 : 기본키와 외래키의 관계를 유지(FOREIGN KET, CHECK)

                   CASECADE DELETE, CASECADE UPDATE

● 엔티티무결성 : 특정 테이블의 고유 엔티티 정의(PRIMARY, UNIQUE)

● 도메인무결성 : 주워진 열에 데이터가 유효하도록 함(CHECK, DEFAULT, NOT NULL)

  1. 제약조건

ALTER TABLE [Order Details] WITH NOCHECK ADD CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY

(

[OrderID]

) REFERENCES [Orders]

(

[OrderID]

)

- DEFAULT 제약

  ● INSERT 구문 사용시 입력되어야 할 값을 명시하지 않을 경우 DEFAULT 값이 대신들어감

- CHECK 제약

ALTER TABLE ZipCode ADD CONSTRAINT chk_ZipCode //check 제약조건 chk_ZipCode 만들고 INSERT 확인함. CHECK( phone LIKE ‘[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]’)

- PRIMARY KEY / UNIQUE

  ● PRIMARY : 유일한 식별자, 참조의 무결성을 위한 후보키 사용, NULL 포함X

  ● UNIQUE : 유일한 식별자, 참조의 무결성을 위한 후보키 사용, NULL 포함O

- FOREIGN KEY 제약조건

  ● 같은 서버의 같은 데이터베이스 내에 있는 테이블만 참조가 가능

  ● 외래키를 만들어서 참조 무결성을 구현

      ALTER TABLE [Order Details] WITH NOCHECK ADD CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY

(

[OrderID]

) REFERENCES [Orders]

(

[OrderID]

) ON DELETE CASCADE

// ON UPDATE CASCADE

 ON DELETE NO ACTION
 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 삭제/갱신하려고 하면 오류가 발생하고 UPDATE, DELETE

 문이 롤백되도록 지정

   

● DELETE/UPDATE CASCADE 제약 조건 정의 안할경우(트랜잭션 이용)

   BEGIN TRAN

DELETE [Order Details] WHERE OrderID= 10248

DELETE Orders WHERE OrderID = 10248

   END TRAN

시스템 지원 함수 및 연산자

  1. CEILING, FLOOR

    • SELECT CEILING(11.55), CEILING(11.11), FLOOR(11.11), FLOOR(11.55) -> 12, 12, 11, 11

  2. ROUND

    • SELECT ROUND(11.22, 1) , ROUND(1111.2, -2) -> 11.20, 1100.0

  3. RAND

    • INSERT INTO TestSales VALUE(RAND()*26) -> 0~25 사이에 임의의 값 생성

  4. POWER(X, Y) Y승, SQRT 제곱근

  1. LTRIM, RTRIM(공백제거)

SELECT ‘<’ + LTRIM(‘ SQL서버 ‘) + ‘>’ - 

 ,'<' + RTRIM('    SQL서버     ') + '>'     - <      SQL 서버>

 , '<' + LTRIM(RTRIM('    SQL서버     ')) + '>'   - <SQL 서버>
  1. REPLICATE

SELECT REPLICATE(‘OK’,4), ‘A’ + SPACE(5)+’B’ - OKOKOKOK A B

  1. REPLACE, STUFF

    SELECT REPLACE(‘대한 민국 만세’,’민국’,’독립 만’), - 대한 독립 만 만세

    STUFF(‘대한 민국 만세’,’민국’,3,4,’독립 만’) - 대한독립 만만세

  2. CHARINDEX, PATINDEX

    CHARINDEX(‘com’, title) -> patten 시작 위치점 반환

    PATINDEX(‘%c[a-k]n%] -> 여러가지 옵션 사용가능.

  3. DEFFERENCE( 일치도 높으면 4, 낮으면 0, 한글 지원x)

    DEFFERENCE(‘can’, ‘con’) -> 3

고급쿼리

  1. 단순 CASE 함수( 단순 식과 비교)

SELECT au_fname, au_lname

CASE state

WHEN ‘CA’ THEN ‘Califonia

END AS StateName

FROM authors

  1. 검색된 CASE 함수

SELECT title_id, CAST(title AS varchar(20)) AS title, price, PriceCategory = CASE WHEN price IS NULL THEN N’미정’ WHEN price < 10 THEN N’저가’ WHEN price >= 10 AND price < 20 THEN N’중가’ ELSE N’고가’ END
FROM titles ORDER BY price

SELECT stor_id, stor_name,

CASE WHEN (SELECT COUNT(*) From Sales WHERE stor_id = s.stor_id) > 3 THEN N’활성’

ELSE ‘비활성’

END AS 상태

FROM Stores s

= IN사용

SELECT DISTINCT pub_name FROM publishers WHERE pub_id IN ( SELECT pub_id FROM titles WHERE type=’business’ )

SELECT s.stor_id, stor_name, SumQty FROM stores t, (SELECT stor_id, SUM(qty) AS SumQty FROM sales GROUP BY stor_id ) AS s WHERE t.stor_id = s.stor_id

SELECT s.stor_id, stor_name, SUM(qty) AS SumQty FROM stores t, sales s WHERE t.stor_id = s.stor_id GROUP BY s.stor_id, stor_name

CREATE PROCEDURE up_GetOrders @Move nchar(1) = N’F’, @OrderID int = NULL AS IF @Move = N’F’ SELECT TOP 10 * FROM Orders ORDER BY OrderID DESC

IF @Move = N’L’ SELECT * FROM ( SELECT TOP 10 * FROM Orders ORDER BY OrderID ASC) t ORDER BY OrderID DESC

=> EXEC up_GetOrders ‘L’

SELECT so.* FROM Sales so

WHERE so.SaleQty = ( SELECT MAX(si.SaleQty) FROM Sales si

WHERE so.ProductID = si.ProductID)

장점 : 쿼리의 재사용의 효율을 높여서 DB 성능을 높인다.

       복잡한 쿼리, 자주사용하는 쿼리를 단순화해서 사용할 수 있다.

CREATE PROC Sales_TopN @CNT AS int AS DECLARE @Str VARCHAR(100) SELECT @Str = ‘SELECT TOP ‘ + STR(@CNT) + ‘Stor_id, Qty FROM Sales ORDER BY Qty DESC’ EXEC(@Str)

EXEC Sales_TopN 10

  1. COMPUTE와 GROUP BY의 차이를 요약

GROUP BY는 단일 결과 집합을 반환합니다. 각 그룹별로 그룹화 열과 해당 그룹의 하위 집계를 보여 주는 집계 함수가 포함된 하나의 행이 있습니다. SELECT 목록에는 그룹화 열과 집계 함수만 포함될 수 있습니다. 원시데이터를 보여주지 않음. COMPUTE (BY)는 원시데이터를 보여주고, 원시데이터와 함꼐 소계와 총계를 조회할 때 많이 쓰인다. COMPUTE BY 절의 컬럼들은 ORDER BY 절에 명시되어 있어야함. SELECT stor_id, ord_num, title_id, payterms, qty FROM sales WHERE stor_id LIKE ‘706%’ ORDER BY stor_id, qty DESC COMPUTE AVG(qty), SUM(qty) BY stor_id

COMPUTE AVG(qty), SUM(qty)

SELECT qty, AVG(qty), SUM(qty) FROM sales WHERE stor_id LIKE ‘706%’ GROUP BY qty ORDER BY qty

인덱스 디자인과 사용

● 인덱스가 유용한 경우

● 인덱스가 유용하지 않은 경우

● 인덱스 디자인을 위한 추가지침

SELECT * FROM OrderX SELECT * FROM Orders

● IAM을 사용하는 클러스터된 인덱스 스캔

 - 클러스터된 인덱스 전체를 스캔할 때 클러스터된 인덱스의 중간수준 인덱스를 사용하지 않고 IAM을 통해 클러스터된 인덱스 잎

   노드, 즉 모든 데이터 페이지를 I/O하여 작업한다.

● 이전 이후 페이지 포인터로 클러스터된 인덱스

 - 클러스터된 인덱스에서는 데이터 페이지 내에 이전 이후 페이지 포인터를 통해 첫페이지나 마지막 페이지에서부터 순차적으로

   전체페이지를 IO한다.( 이전과 다음 페이지 포인터를 통해서 조회)

 - 클러스터 인덱스 : 클러스터 인덱스 키 값을 사용(데이터페이지 공존), 클러스터 되지 않은 인덱스 : 인덱스 페이지를 이용해서

                              데이터페이지를 찾음

SELECT * FROM Orders ORDER BY OrderID SELECT OrderID FROM Orders ORDER BY OrderID DESC

● 클러스터되지 않은 인덱스 스캔

 - 인덱스 검색을 사용하지 않고 인덱스 페이지만을 스캔하여 처리할 수 있는 경우

SELECT COUNT(*) FROM Orders

● 클러스터 된 인덱스 검색

 -  인덱스 루트 노드로부터 B-Tree 검색을 수행하여 결과를 얻음

SELECT * FROM OrderX SELECT * FROM Orders

● 클러스터되지 않은 인덱스 검색

 - B-Tree 검색을 수행하면서 인덱스의 키나 행 식별자를 가지고 책갈피 연산을 수행함.

SE Northwind

SELECT * INTO OrderX FROM Orders

CREATE INDEX OrderX_OrderDate ON OrderX(OrderDate)

CREATE INDEX Orders_OD_C ON Orders(OrderDate, CustomerID) GO

EXEC sp_helpindex OrderX EXEC sp_helpindex Orders GO

● 인덱스가 유용한 경우

● 인덱스가 유용하지 않은 경우

● 인덱스 디자인을 위한 추가지침

SELECT * FROM OrderX SELECT * FROM Orders

● IAM을 사용하는 클러스터된 인덱스 스캔

 - 클러스터된 인덱스 전체를 스캔할 때 클러스터된 인덱스의 중간수준 인덱스를 사용하지 않고 IAM을 통해 클러스터된 인덱스 잎

   노드, 즉 모든 데이터 페이지를 I/O하여 작업한다.

● 이전 이후 페이지 포인터로 클러스터된 인덱스

 - 클러스터된 인덱스에서는 데이터 페이지 내에 이전 이후 페이지 포인터를 통해 첫페이지나 마지막 페이지에서부터 순차적으로

   전체페이지를 IO한다.( 이전과 다음 페이지 포인터를 통해서 조회)

 - 클러스터 인덱스 : 클러스터 인덱스 키 값을 사용(데이터페이지 공존), 클러스터 되지 않은 인덱스 : 인덱스 페이지를 이용해서

                              데이터페이지를 찾음

SELECT * FROM Orders ORDER BY OrderID SELECT OrderID FROM Orders ORDER BY OrderID DESC

● 클러스터되지 않은 인덱스 스캔

 - 인덱스 검색을 사용하지 않고 인덱스 페이지만을 스캔하여 처리할 수 있는 경우

SELECT COUNT(*) FROM Orders

● 클러스터 된 인덱스 검색

 -  인덱스 루트 노드로부터 B-Tree 검색을 수행하여 결과를 얻음

SELECT * FROM OrderX SELECT * FROM Orders

● 클러스터되지 않은 인덱스 검색

 - B-Tree 검색을 수행하면서 인덱스의 키나 행 식별자를 가지고 책갈피 연산을 수행함.

SE Northwind

SELECT * INTO OrderX FROM Orders

CREATE INDEX OrderX_OrderDate ON OrderX(OrderDate)

CREATE INDEX Orders_OD_C ON Orders(OrderDate, CustomerID) GO

EXEC sp_helpindex OrderX EXEC sp_helpindex Orders GO

  1. 부정 검색 조건에서의 인덱스 사용

SELECT * FROM orders WHERE OrderDate <> ‘19960704’

  1. 인덱스 컬럼 변형에 대한 검색

SELECT * FROM orders WHERE CONVERT(char(5), OrderID) LIKE ‘1024%’

  1. 데이터형이 맞지 않는 검색조건

CREATE TABLE TestOrders (OrderID char(5) NOT NULL, …

SELECT * FROM TestOrders WHERE OrderID = 10500

  1. 패턴 조건 검색에서 인덱스 사용

SELECT * FROM TestOrder WHERE OrderID LIKE ‘%500’

CREATE NONCLUSTERED INDEX zip_ind

ON authors(zip)

WITH FILLFACTOR = 70 -> 70 FILLFACTOR 채워서 30% 빈공간을 만듦

● FILLFACTOR - 인덱스 페이지를 어느정도 압축하거나 비워 놓을 것인가를 설정할 수 있는 옵션,

                       잎 노드의 인덱스 페이지를 명시한 % 만큼 사용하고 나머지는 비우게 된다.

                  -> 인덱스 페이지 공간이 부족하다면 새로운 페이지를 할당받아야 하므로 이런 페이지 분할 작업을 줄이기 위해서 사용됨

● PAD_INDEX : 인덱스 잎 페이지 외에 나머지 인덱스 페이지들의 수준을 FILLFACTOR 명시한 수준으로 바꿈

● IGNORE_DUP_KEY :

-> INSERT 작업으로 중복 자료가 입력되는 것에 대해 오류 대신 경고로 처리, UPDATE 작업으로 중복이 발생되는 것은 오류로 처리한다. USE tempdb GO

CREATE TABLE emp_pay (employeeID int NOT NULL ,base_pay money NOT NULL ,commision decimal(2,2) NOT NULL) GO

CREATE UNIQUE CLUSTERED INDEX employeeID_ind ON emp_pay(employeeID) WITH IGNORE_DUP_KEY GO

INSERT emp_pay VALUES(1, 500, .10) -> 가능(중복된 키값이라도 가능) INSERT emp_pay VALUES(1, 1000, .05) GO

SET NOCOUNT ON

INSERT emp_pay VALUES(5,0,.03) SELECT @@ERROR GO

UPDATE emp_pay SET employeeID = 1 WHERE base_pay = 500 SELECT @@ERROR GO

SELECT * FROM emp_pay

=> 결과

메시지 2601, 수준 14, 상태 1, 줄 2 고유 인덱스가 ‘employeeID_ind’인 개체 ‘dbo.emp_pay’에 중복 키 행을 삽입할 수 없습니다. 중복 키 값은 (1)입니다.

● DROP_EXISTING / STATISTICS_NORECOMPUTE / SORT_IN_TEMPDB

Originally published February 16, 2020
Latest update February 15, 2020

Related posts :