홍승아블로그

MSSQL

용어정리

  • 트랜잭션 : 여러 데이터 변경에 대해 하나의 논리적 작업 단위로 취급하여 전체가 완료되거나 롤백되어 데이터의 무결성을 유지

  • COMMIT : 모든 트랜잭션 수정이 영구적으로 데이터베이스 일부로 적용, 트랜잭션에 의해 사용된 리소스 해제

  • ROLLBACK : 시작 상태로 되돌려서 트랜잭션 중 만드러진 모든 수정 내용을 취소

  • 트리거 : 특정 테이블에 자료가 수정될 때 내부적으로 관련된 다른 테이블 또는 컬럼에 자동으로 연동하여 갱신하거나 무결성 유지

  • 커서 : 메모리상에서 SQL문이 실행되는 위치 메모리에 존재하는 SQL문 실행결과를 바로 접근하여 fetch 가능(SELECT * FROM A) 현재 처리하고 있는 row

SQL Server 개요

  • 데이터베이스 : 사용자에게 필요한 데이터와 정보를 효율적으로 저장 관리하고 통합적으로 조직하고 관리 할 수 있도록 구성된 데이터

                      집합체

  • 관계형 데이터베이스

  • 연관된 데이터가 하나의 테이블에 모두 들어 있는 것이 아니라 여러 개의 서로 다른 테이블로 관리하고 데이터 사이에 어떠한 관계를

    부여함으로 데이터를 조직화하는 데이터베이스

  • SQL Server 서비스

    • 데이터베이스 엔진으로서 Transact-SQL 문을 처리하고, 서버 상의 데이터베이스를 구성하는 모든 파일을 관리한다.

  • SQL Sever Agent

    • 자동화 관리와 관련된 서비스로 작업,경고,오퍼레이터 등을 생성하여 관리를 자동화한다.

    • 예를들면, 로그가 90% 이상이면 경고를 발생시키고 경고가 발생하면 데이터베이스 로그를 백업한다.

  • MS DTC(Microsoft Distributd Transaction Coordinator)

    • 분산된 데이터를 하나의 트랜잭션으로 처리하기 위해서 일련의 작업

  • 전체 텍스트 검색(Full-Text Search)

    • 마이크로소프트 검색 서비스로 텍스트와 문자열에 대한 조회를 다양한 조건으로 쿼리를 수행

    • 기능을 사용하기 위해서는 전체 텍스트 인덱스를 만들고 유지보수 해야한다.

  • 분석서버

    • Microsoft SQL Server 2000 Analysis Services의 서버 구성 요소로서 데이터웨어하우스 및 데이터마트에 대한 신속한 분석 액세스를

      제공

  • 데이터베이스 개체

  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 기초

  • SQL 구문의 종류

  • 데이터 관리언어(DML) : 데이터입력, 조회, 수정, 삭제(INSERT, SELECT, UPDATE, DELETE)

  • 데이터 정의언어(DDL) : 데이터 베이스의 특성과 속성을 정의, 테이블, 인덱스,뷰 등 생성과 수정,삭제(CREATE, ALTER, DROP)

  • 데이터 조작언어(DCL) : 데이터베이스 개체에 대한 사용권한을 제어(GRANT,REVOKE,DENY)

  • 컬럼 별칭 세가지 방법

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

  • 식별자

  • 데이터 베이스 개체 이름

  • 예약어나 특수문자가 포함된 경우 식별자 구분을 위해서 구분자 사용함.

  • 구분자는 [] , ""(SET QUOTED_IDENTIFIER ON 세션 옵션 설정, OFF일 경우 문자열로 사용)

SELECT *

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

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

  • 논리연산자 : 우선순위 -NOT, AND, OR

  • ORDER BY( 조회자료 정렬)

  • 제약사항 : ntext, text, image ORDER BY 절 사용못함.

SELECT type, AVG(price) AS avgPrice

FROM titles

WHERE royalty = 10

GROUP BY type

HAVING AVG(price) < 80

  • DISTINCT와 GROUP BY절

  • DISTINCT는 주로 UNIQUE(중복을 제거)한 컬럼이나 레코드를 조회

  • GROUP BY는 데이터를 그룹핑해서 그 결과를 가져옴.

예)

– 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;

  • SELECT INTO / INSERT INTO 차이점
  • SELECT INTO : 원본이 있고, 대상 테이블을 새롭게 생성하려는 경우 사용(원본 테이블만 있으면 됨 - 복사되므로)
  • INSERT INTO : 원본테이블과 복사하는 테이블 둘다 있을 때 사용해야함
  1. 언제 사용되나?

  • SELECT INTO : 복잡한 작업을 하는 경우에 원본 테이블을 그대로 두면서 임시 테이블을 만들어서 하위작업을 하는데 주로 사용됨.

  • INSERT INTO : 원본 테이블의 자료를 복사하기 위해서 사용됨

  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 사용법

  • INSERT INTO B SELECT * FROM A

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

  • INSERT INTO B SELECT COL1,COL2,COL3 FROM A

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

  • 조인 : 두 개 이상의 테이블로부터 쿼리하여 하나의 결과물을 만드는 연산
  1. 내부 조인(INNER JOIN) / 외부(OUTER JOIN)

● INNER JOIN

  • ANSI 표준 : SELECT a.id, a.val aval, b.val bval FROM A a INNER JOIN B b ON a.id = b.id;

  • Transact-SQL : SELECT a.id, a.val aval, b.val bval FROM A a ,B b WHERE a.id = b.id;

● LEFT OUTER JOIN

  • ANSI 표준 : SELECT a.id, a.val aval, b.val bval FROM A a LEFT OUTER JOIN B b ON a.id = b.id;

  • Transact-SQL : SELECT a.id, a.val aval, b.val bval FROM A a , B b WHERE a.id *= b.id;

● RIGHT OUTER JOIN

  • ANSI 표준 : SELECT a.id, a.val aval, b.val bval FROM A a RIGHT OUTER JOIN B b ON a.id = b.id;

  • Transact-SQL : SELECT a.id, a.val aval, b.val bval FROM A a , B b WHERE a.id =* b.id;

  • UNION

  • JOIN : 두 테이블이나 결과물을 병렬조합

    UNION : 쿼리의 결콰를 상하위로 합치는 직렬 조합

  • UNION한 결과 전체에 대해서 GROUP BY 및 HAVING 절 사용하기 위해서는 임시테이블, 파생테이블 사용

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

  • DELETE / UPDATE

  • DELETE FROM authors WHERE au_lname = ‘McBadden’

  • UPDATE authors SET state = ‘PC’ WHERE state = ‘CA’

  • INSERT

  • INSERT 문은 VALUE절과 함께 사용시 INTO 절을 생략가능

  • 삽입에서 명시되지 않아도 되는 컬럼

    1. NULL 허락한 컬럼

    2. 기본값을 가진 컬럼

    3. 자동 증가형 속성을 가진 컬럼

    4. Rowversion or Timestamp 가진 컬럼

  • TRUNCATE TABLE / DELETE

  • WHERE 절이 없는 DELETE 문과 동일함

  • DELETE : 자동증가형 속성이 유지 / TRUNCATE : 자동증가형 속성이 초기화됨

  • DELETE 빠르고 트랜잭션 로그 리소스를 덜 사용 -DELETE 한 행 제거 후 각 행에 대해서 트랜잭션 로그 항목 기록

  • TRUNCATE는 행별 삭제되는 내용이 로그되지 않아서 DELETE 트리거 작동안함

    트리거 : 특정 테이블에 자료가 수정될때 내부적으로 관련된 다른 테이블 또는 컬럼에 자동으로 연동하여서 무결성을 지키기 위한 것

DB 생성과 관리

  • 데이터 베이스 : 관리하고 싶은 업무 단위와 관련된 오브젝트를 하나의 논리적인 저장소 단위로 일관성을 유지할 수 있는 관리의

                      단위이며 복구의 단위

  • 데이터베이스 기본적인 구성 : 파일 그룹 / 데이터 파일 / 로그파일

    1. 파일 그룹 : 여러 데이터 파일의 논리적인 집합

                   디스크를 분산하여 성능을 향상을 고려할 수 있음.

    2. 데이터베이스 파일 : 데이터 파일과 로그 파일로 나눌 수 있으며 자료를 기록하는 물리적인 저장소 단위를 말함.

                              주파일 : 데이터베이스 시작정보와 데이터저장 / 보조파일 : 그 외 파일

                        트랜잭션 로그파일 : 데이터 복구에 필요한 로그 정보가 저장

    3. 익스텐트 : 테이블과 인덱스에 할당되는 기본단위(하나의 익스텐트는 연속하는 8 페이지(64KB)로 구성됨)

                 기존에 할당한 저장공간이 부족할 경우 새로운 익스텐트를 할당받고 첫페이지부터 사용하게 된다.

    4. 데이터파일 : 익스텐트를 할당할 수 있는 물리적으로 확보된 공간

    5. 페이지 : 페이지는 SQL 서버에서 데이터 저장소의 기본 단위

  • 데이터베이스 생성

    1. 데이터베이스 : 여러 개의 데이터파일과 로그 파일로 구성

    2. 여러 개의 물리적인 드라이브를 가지는 경우에 데이터파일은 여러개의 파일 그룹에 분산하여 하드디스크 I/O 분산하여 처리함.

      로그 크기는 데이터수정량과 로그의 백업일정에 따라서 다르며 기본적으로 10~25%

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]

  • 파일 그룹의 사용

  • 파일 그룹은 테이블과 인덱스, BLOB형 자료, 즉 Image, text, ntext 데이터형을 저장될 공간을 명시적으로 할당함.

  • BLOB(Binary Large Object)

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. 데이터베이스 수동 축소

  • 지정한 데이터베이스에서 모든 데이터파일과 로그 파일을 축소하기 위해서 사용함.

  • ALTER DATABASE, DBCC SHRINKFILE 명시적으로 설정한 최종크기보다 작게 만들 수는 없다.

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

  • 데이터베이스 변경 및 삭제

    1. 파일 그룹 변경

  • sp_help : 데이터베이스 개체, 사용자 정의 데이터 형식 또는 데이터 형식에 대한 정보를 보고

  • Data_located on_filegroup -FG_Index

ALTER DATABASE Testpubs MODIFY FILEGROUP [FG_Index] DEFAULT

CREATE TABLE Test( id int)

EXEC sp_help Test

  1. 데이터베이스명 변경

  • 데이터베이스 이름 변경 시 단일 사용자 모드, 변경 후 다시 단일사용자 모드 해제

  • ROLLBACK IMMEDIATE : 완료되지 않은 트랜잭션을 롤백하기 위해서 옵션을 넣어준다.

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

  • 데이터베이스 옵션

    1. 자동옵션

    ● AUTO_CLOSE : 데이터베이스를 마지막으로 사용한 사용자가 종료, 데이터베이스 종료되면 데이터베이스 리소스가 해제된다.

    ● AUTO_CREATE_STATISTICS : 조건자에서 사용되는 열에 대해 자동으로 통계가 생성된다.

    ● AUTO_UPDATE_STATISTICS : 데이터가 변경되고 기존 통계가 최신 상태가 아닐때 통계를 자동으로 업데이트 한다.

    ● AUTO_SHRINK : 데이터베이스의 데이터파일과 로그 파일의 사용되지 않는 공간을 정기적으로 검사해서 축소한다.

    1. 커서옵션

    ● CURSOR_CLOSE_ON_COMMIT : 트랜잭션이 커밋될때 열려 있던 커서가 자동으로 닫힌다.

    ● CURSOR_DEFAULT_LOCAL/GLOBAL : 설정된 커서의 범위가 로컬로 적용, 연결된 세션의 전역으로 적용

    1. 복구옵션 / 상태옵션(SINGLE, READ_ONLY) / Nonlogged Operation(로그 파일에 기록을 남기지 않는 경우) / WITH

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

EXEC sp_renamedb ‘Testpubs’, ‘TestpubsNew’

EXEC sp_dboption TestpubsNew, ‘Single user’, FALSE

  • 로그의 역할

    • 로그는 해당 데이터베이스에 수정작업 모두를 기록해서 서버의 장애로 COMMIT을 하지 못할 경우에는 ROLLBACK을 수행해서

      데이터의 일관성을 유지하게 된다.

    • 트랜잭션 로그 기록사항

      트랜잭션의 시작과 끝, 모든 데이터 수정과 변경된 내용이 포함, 모든 익스텐트 할당, 취소

      테이블 또는 인덱스 만들기 또는 삭제

데이터형식 및 형변환

  • 가변 길이 데이터형

    가변 크기 데이터형 : varchar, varbinary, nvarchar

    크기고정X, 명시 할 수 없는 데이터형 : text, ntext, image, sql_variant

    • text, image는 테이블 내의 저장소가 아닌 별도의 공간에 저장, 해당 저장소의 시작주소 16바이트를 가지고 운영함.

  • 가변길이, 고정길이 언제 사용?

    가변길이 : 큰 고정문자형을 정의하고 적은 크기 문자열을 사용할 때

    고정길이 : 입력포맷(3글자)이 고정되어 있는 곳에서는 고정길이를 쓰는 것이 더 좋은 성능을 얻을 수 있음

  • 유니코드

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

  • datetime(밀리초) / smalldatetime(분)

  • 대형 데이터 형식

    • 대형 데이터를 저장하기 위해서는 text, ntext, image 데이터형

    • 2G 저장 가능, 직접 해당 데이터페이지를 저장하지 않으며. 16바이트 포인터만 저장하고 실제로는 별도의 저장공간이 있음.

  • 기타 데이터 형식

    1. Timestamp 및 rowversion(8 Byte)

    ● 데이터베이스 안에서 각 행의 자료에 대한 버전 관리

    ● 데이터베이스에서 고유함이 보장된 자동 생성 이진수를 표시

    ● 테이블 당 하나만 설정

    ● @@DBTS 함수로 마지막으로 적용된 버전 넘버를 알 수 있다.

    ● =,<>,<,>,<=,>= 단순 비교 사용가능

    ● 삭제에는 버전이 증가하지 않고 삽입에서만 가능함.

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 지정 불가능

  • 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))

  • TABLE

    • 테이블 구조의 변수를 선언

    • table 변수는 배치 처리가 완료됨과 동시에 자동으로 제거된다.

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

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

  • 사용자 정의 데이터형식

    • sp_bindrule : 열 또는 별칭 데이터 형식에 규칙을 바인딩

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’

  • 데이터 형변환

    1. 묵시적 형변환

      SELECT 4/3, 4/3.0 -1 / 1.3333 SELECT ‘1’ + 2 , ‘1’+‘2’-3 , 12

    2. NULL 연산 : NULL 연산은 항상 NULL

      SET CONCAT_NULL_YIELDS_NULL ON일 경우 -문자열 NULL

    3. 명시적 형변환(CAST, CONVERT)

    • STR(float_expression [ , length [ ,decimal ] ] )

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

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

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

  • 개체 식별자

    • 서버, 데이터베이스, 테이블 뷰,열 등등 고유한 이름 즉 식별자를 가진다.

    • 구분 기호는 [] , "" 사용가능

      "" -QUOTED_IDENTIFIER ON일 경우 사용가능

    1. 식별자 규칙

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

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

    ● Transact-SQL @@ : @@IDENTITY

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

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

  • 임시테이블, 테이블 변수

    1. 공통점 : 임시적으로 테이블을 사용하고 폐기하기 위해서 사용

    2. 다른점

      • 임시테이블 : DROP Table 강제로 제거, 세션이 끊기면 자동 삭제

      • 테이블 변수 : 쿼리 분석기에서 GO 분리자를 넘어서면 자동으로 파기됨.

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
  • 제약 조건을 정의함( 제약조건 : PRIMARY KEY, UNIQUE, CHECK 제약 등)

CREATE TABLE Sales

(

…

, CONSTRAINT PK_Sales PRIMARY KEY NONCLUSTERED

(

SaleDate, SlipNo

)

)

  • IDENTITY

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

● 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로 설정

  2. IDENTITYCOL 예약어

  • IDENTITY 컬럼행 리턴함. ( WHERE 사용 )

    WHERE IDENTITYCOL = 1

  1. DBCC CEHCKIDENT

  • 사용이유 : IDENTITY 속성 최대치에 넘어설 경우 오버플로우 오류, 초기치 설정가능

    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. 제약조건
  • NOCECK 옵션 : FOREIGN KEY 가지고 있지 않은 테이블에서 참조 무결성을 어기는 기존의 문제는 점검하지 않고 제약 생성

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서버 ’) + ’>’ - <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. DATEADD

      SELECT 월시작일 = DATEADD(dd, 1, date)

    2. DATEDIFF

      DATEDIFF(dd, newdate, olddate)

    3. DATEPART

      DATEPART(mm, GETDATE())

    4. DATANAME

      DATENAME(dw, GETDATE())

  • 시스템 함수

    1. CAST and CONVERT

    CAST(11.11 AS char(10) , CONVERT(char(10) , 11.11)

    1. COALESCE - 여러 컬럼 값 중 첫번째로 NULL이 아닌 값을 반환

    2. @@IDENTITY 세션에서 마지막에 부여된 Identity 값을 반환

    3. ISDATE, ISNULL(ISNULL(qty, 0)), ISNUMERIC

    4. @@ROWCOUNT

    SELECT GETDATE() - 2001-10-22~~

    SELECT @@ROWCOUNT

    1. @@TRANCOUNT

    BEGIN TRANSACTION

     ...

    SELECT @@TRANCOUNT - 1

    BEGIN TRANSACTION

    …

    SELECT @@TRANCOUNT - 2

    1. NULLIF(두값이 같으면 NULL, 아니면 첫번째 값을 반환)

      NULLIF(10, 10), NULLIF(10, 20)

  • 연산자

    1. 비트연산자

    • int, smallint, tinyint, image 제외한 bit, binary, varbinary 수행

    • 시스템 테이블의 정보값을 관리하는데 쓰인다.( 0001 autoclose, 0100 sp_dboption) OR연산으로 시스템 정보 설정

    1. 논리연산자

    • ALL : 비교하는 모든 값이 참일때 참이 된다.

      WHERE ID(1,7,9) ANY(SELECT ID FROM TestTemp - 4,6,8) -9

    • ANY, SOME : 비교하려는 값들 중 하나만 참이라도 참이 된다. SOME( ISO 표준 동의어 )

      WHERE ID(1,7,9) ANY(SELECT ID FROM TestTemp - 4,6,8) -7,9

    • EXISTS(서브 질의에 데이터 존재하면 참), IN(나열된 값들 중 하나만 일치하면 참이된다.)

      IF EXISTS(SELECT * FROM SALES WHERE stor_id = ‘6380’)

      WHERE state IN(‘CA’, ‘IN’, ‘MD’)

고급쿼리

  • CASE 함수
  • 조건 목록을 평가하고 여러 결과 식 중 하나를 반환
  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

  • 상관 하위 쿼리를 사용한 CASE 함수

    ● 외부쿼리 : 서브(내부) 쿼리를 포함한 쿼리 / 내부쿼리(서브쿼리, 하위쿼리) : 쿼리 안의 쿼리

    • 외부 쿼리에 영향을 받아 하위 쿼리가 수행되는 것

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

  • 하위쿼리

    • SELECT, INSERT, UPDATE, DELETE 문이나 다른 하위 쿼리 내부에 중첩된 SELECT 쿼리(최대 32개 중첩이 가능)

    • SELECT : 하나의 값으로 반환해서 표현

      WHERE : 하나의 열이나 행 집합(EXISTS, IN, ANY, ALL 논리연산자 이용)

      FROM : 여러 열이나 표현식으로 여러 행이 올수 있음(파생테이블)

    1. 하위쿼리 제한조건

    하위 쿼리는 TOP 절이 있는 경우에만 ORDER BY 절을 포함할 수 있습니다

    1. 반복적으로 재계산하여 사용하는 경우는 먼저 계산값을 재사용함.

    DECLARE @SumQty int

    SELECT @SumQty = SUM(qty) FROM Sales

    SELECT stor_id, Qty, @SumQty FROM Sales

    1. IN을 사용한 하위쿼리 ( 조인으로 처리가능)

    SELECT title_id, title, pub_id, price

    FROM titles

    WHERE title_id IN( SELECT title_id FROM Sales WHERE qty>40)

    =

    SELECT titles.title_id, title, pub_id, price

    FROM titles INNER JOIN Sales

    ON titles.title_id = Sales.title_id AND qty>40

    1. 하위 쿼리로 차집합 구하기(NOT IN 구문 이용)

    SELECT title_id, title FROM titles

    WHERE title_id NOT IN( SELECT title_id FROM Sales)

    1. EXISTS 사용

    SELECT DISTINCT pub_name FROM publishers WHERE EXISTS ( SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type=‘business’)

= IN사용

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

  • 파생테이블

    • FROM 절에 사용하는 하위 쿼리를 말함.

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

  • 부분 범위 조회

  • TOP 명령어를 이용한 부분 범위를 설정가능함.

  • 저장프로시저 : 쿼리문의 집합으로 어떠한 동작을 일괄처리하기 위한 용도로 사용된다.

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)

  • 동적쿼리 : 동적으로 쿼리 문자열을 만들고 EXECUTE 문으로 실행할 수 있다.

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

       복잡한 쿼리, 자주사용하는 쿼리를 단순화해서 사용할 수 있다.
  • STR : 소수점 이하는 절삭하고 10자리 문자열로 만들어준다.

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

  • COMPUTE BY
  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

인덱스 디자인과 사용

  • 용어정리

  • 인덱스페이지 : 실제 인덱스가 저장되어 있는 페이지

  • 데이터페이지 : 실제 데이터가 저장된 페이지

  • 쿼버드 쿼리(Covered Query) : 조회 컬럼과 조건 컬럼을 복합키로 클러스터되지 않은 인덱스로 만든 것

  • 인덱스
  • 쿼리의 응답속도 향상시키기 위해서 사용됨
  • 인덱스 사용하는 이유

  • 주로 자료의 조회뿐만 아니라, 검색 조건을 가진 갱신에 대해서도 수행속도를 높임

  • 유일성을 강화하여 중복자료 발생을 막기 위해서 사용됨.

  • 인덱스의 단점

  • 인덱스는 별도의 저장 공간이 더 필요하며, 인덱스가 많은 경우 데이터를 삽입, 업데이트, 삭제 명령어를 수행할 때 시간이 더 걸린다.

  • 또한 유지, 관리하기 위한 처리시간이 더 필요하다.

  • 클러스터된 인덱스

  • 키 값을 기준으로 테이블에 데이터 행을 정렬하고 저장하는 인덱스, 한 페이지 내의 데이터는 인덱스 키를 기준으로 정렬됨

  • 많은 자료 중 하나의 자료를 얻는 것, 데이터 자체가 정렬되어 있기 때문에 범위 검색이 더 효율적으로 사용된다.

  • 데이터의 저장소와 인덱스의 저장소가 같이 존재하는 경우이며 데이터는 인덱스의 순서대로 저장하게 된다.

  • 클러스터 되지 않은 인덱스

  • 데이터의 저장소와 인덱스의 저장소가 별도의 공간으로 분리되어 있으면서 데이터 저장소에 있는 행을 찾아 갈 수 있는 포인터를 갖는

    인덱스

  • 클러스터 인덱스가 존재하지 않은 경우 RID(행식별자) 포인터로 가지며, 클러스터된 인덱스가 존재하는 경우 클러스터된 인덱스 키 값을

    포인터로 갖음.

  • 인덱스 디자인

● 인덱스가 유용한 경우

  • 특정 검색 키 값과 정확히 일치하는 행을 검색하는 경우(WHERE emp_id = 357)

  • 특정 범위에 있는 검색 키 값을 가진 행을 검색하는 경우(WHERE job_lvl BETWEEN 9 and 12)

  • 조인 조건자를 기준으로 테이블 T2행과 일치하는 테이블 T1의 행을 검색하는 경우

  • 특정 컬럼을 기준으로 정렬된 쿼리 출력을 만드는 경우(ORDER BY Qty DESC)

  • 유일성 강화하기 위해 PRIMARY KEY 및 UNIQUE 제약 조건을 보장하는 경우

  • FOREIGN KEY 제약 조건이 정의되는 두 테이블 사이의 참조 무결성을 보장하는 경우

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

  • 서로 다른 값이 적은 컬럼 : 성별과 같이 유일자가 작은 경우

  • 긴 문자열 데이터형 컬럼( 한페이지에 넣을 수 있는 인덱스의 한계가 있으므로)

  • 검색 대상이 자주 사용되지 않은 컬럼

  • bit, text, ntext, image 데이터 형에는 인덱스를 생성할 수 없음

  • 작은 테이블에서 인덱스 사용X(테이블 스캔보다 인덱스 통화 시간이 더 길어질 수 있으므로)

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

  • 테이블의 데이터가 변경되면 인덱스도 모두 조정되야 하므로 테이블에 인덱스를 많이 만들면 INSERT, UPDATE, DELETE 성능이

    나빠진다

  • SELECT 문과 같이 데이터를 수정하지 않은 쿼리에 대해서는 성능이 좋아진다.

  • 데이터 및 인덱스 저장 구조

    ● 클러스터된 인덱스를 갖고 있는 클러스터된 테이블

    • 데이터 행은 클러스터된 인덱스 키에 기반한 순서대로 저장된다.

    ● 클러스터된 인덱스가 없는 테이블(힙)

    • 특정 순서로 저장되지 않으며 데이터 페이지 시퀀스에 대한 특별한 순서는 없다.

    ● 인덱스 ID(sysindexes에서 확인가능)

    • 클러스터된 인덱스가 존재하지 않은 테이블에 대해서는 indid 값 : 0

    • 클러스터된 인덱스 indid값 : 1

    • 클러스터되지 않은 인덱스 indid값 : 2~250

    • text, ntext, image 열이 있는 테이블 : 255

    ● Root와 FirstIAM

    • sysindexes의 root 컬럼은 인덱스 B-트리의 맨위를 가리킨다.

      FirstIAM -IAM(테이블의 데이터 페이지 컬렉션) -데이터행 헤더

  • 데이터를 검색하는 여러가지 방법

    ● 테이블 스캔 : 클러스터된 인덱스가 존재하지 않은 테이블에서 IAM 페이지 체인을 이용하여 테이블의 모든 페이지를 I/O 작업

                        FirstIAM -IAM 페이지 체인의 시작포인터를 통해서 자료가 있는 모든 페이지를 가져옴

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

  • 인덱스 디자인

● 인덱스가 유용한 경우

  • 특정 검색 키 값과 정확히 일치하는 행을 검색하는 경우(WHERE emp_id = 357)

  • 특정 범위에 있는 검색 키 값을 가진 행을 검색하는 경우(WHERE job_lvl BETWEEN 9 and 12)

  • 조인 조건자를 기준으로 테이블 T2행과 일치하는 테이블 T1의 행을 검색하는 경우

  • 특정 컬럼을 기준으로 정렬된 쿼리 출력을 만드는 경우(ORDER BY Qty DESC)

  • 유일성 강화하기 위해 PRIMARY KEY 및 UNIQUE 제약 조건을 보장하는 경우

  • FOREIGN KEY 제약 조건이 정의되는 두 테이블 사이의 참조 무결성을 보장하는 경우

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

  • 서로 다른 값이 적은 컬럼 : 성별과 같이 유일자가 작은 경우

  • 긴 문자열 데이터형 컬럼( 한페이지에 넣을 수 있는 인덱스의 한계가 있으므로)

  • 검색 대상이 자주 사용되지 않은 컬럼

  • bit, text, ntext, image 데이터 형에는 인덱스를 생성할 수 없음

  • 작은 테이블에서 인덱스 사용X(테이블 스캔보다 인덱스 통화 시간이 더 길어질 수 있으므로)

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

  • 테이블의 데이터가 변경되면 인덱스도 모두 조정되야 하므로 테이블에 인덱스를 많이 만들면 INSERT, UPDATE, DELETE 성능이

    나빠진다

  • SELECT 문과 같이 데이터를 수정하지 않은 쿼리에 대해서는 성능이 좋아진다.

  • 데이터 및 인덱스 저장 구조

    ● 클러스터된 인덱스를 갖고 있는 클러스터된 테이블

    • 데이터 행은 클러스터된 인덱스 키에 기반한 순서대로 저장된다.

    ● 클러스터된 인덱스가 없는 테이블(힙)

    • 특정 순서로 저장되지 않으며 데이터 페이지 시퀀스에 대한 특별한 순서는 없다.

    ● 인덱스 ID(sysindexes에서 확인가능)

    • 클러스터된 인덱스가 존재하지 않은 테이블에 대해서는 indid 값 : 0

    • 클러스터된 인덱스 indid값 : 1

    • 클러스터되지 않은 인덱스 indid값 : 2~250

    • text, ntext, image 열이 있는 테이블 : 255

    ● Root와 FirstIAM

    • sysindexes의 root 컬럼은 인덱스 B-트리의 맨위를 가리킨다.

      FirstIAM -IAM(테이블의 데이터 페이지 컬렉션) -데이터행 헤더

  • 데이터를 검색하는 여러가지 방법

    ● 테이블 스캔 : 클러스터된 인덱스가 존재하지 않은 테이블에서 IAM 페이지 체인을 이용하여 테이블의 모든 페이지를 I/O 작업

                        FirstIAM -IAM 페이지 체인의 시작포인터를 통해서 자료가 있는 모든 페이지를 가져옴

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

  • DBCC SHOWCONTIG

    • 테이블의 조각화 여부를 파악하는데 사용한다. 테이블의 조각화는 테이블에서 INSERT, UPDATE,DELETE 문 등의 데이터 수정 문을

      처리할 때 발생한다.

  • DBCC INDEXDEFRAG

    • 인덱스의 잎 수준 페이지를 논리적순서로 다시 정렬한다.

  • DBCC DBREINDEX

    • 테이블의 특정 인덱스나 테이블에 정의도니 모든 인덱스를 다시 작성한다.
이전글
← Windows API
다음글
pdf →
Thank You for Visiting My Blog,
© 2021 Developer SeungAh, Powered By Gatsby.