danpoong blog

행 집합을 다루는데 편리한 기능

성능을 크게 떨어뜨리는 요인이라 사용을 자제하는것이 좋음

여러 개의 행을 쿼리한 후에 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식

커서의 처리 순서

커서 선언 (declare)

커서 열기 (open)

-------------------------------------------

커서에서 데이터 가져오기 (fetch)

데이터 처리

------------------------------------------- while문으로 모든 행이 처리될 때까지 반복

커서 닫기 (close)

커서의 해제 (deallocate)

커서의 확장 구문

[local/global] 디폴트는 global

local

지역 커서는 지정된 범위 안에서만 유효하며 해당 범위를 벗어나면 소멸함

프로시저에서 output 매개변수로 커서를 밖으로 돌려주면 커서를 참조하는 변수가 해제되거나 소멸할 때 같이 커서도 소멸됨 (close, deallocate 자동)

global

전역 커서는 모든 프로시저나 일괄처리에서 커서의 이름을 참조 가능

[forward_only/scroll]

forward_only

시작 행부터 끝행의 방향으로만 커서가 이동함

사용 가능한 데이터 가져오기는 detch next 한정

scroll

커서 이동이 자유로움

fetch next, first, last, prior 등 여러가지 사용 가능

[static/dynamic/keyset/fast_forward] 디폴트는 dynamic

static

원본 테이블을 tempdb로 가져올 때 사용할 데이터를 보두 복사해서 데이터를 사용함

커서의 선언 이후 원본 테이블에 변경사항이 있어도 커서에서는 변경 전 데이터를 사용하여 변경 사항을 알 수 없음

dynamic

행 데이터를 가지고 올  때마다 원본 테이블에서 가져옴

원본 테이블의 update, insert 내용이 모두 보임

keyset

모든 키 값을 tempdb에 저장

원본 테이블의 update 된 내용만 보이며 insert된 내용은 보이지 않음

keyset을 사용하려면 원본 테이블에 꼭 고유 인덱스(unique key)가 있어야 함 

fast_forward

forward_olny와 read_only 옵션이 합쳐진 것

커서에서 행 데이터를 수정하지 않을 것이라면 가장 바람직한 옵션

[read_only, scroll_locks, optimistic]

read_only

읽기 전용으로 설정하는 것

scroll_locks

위치 지정 업데이트나 삭제가 가능하도록 설정하는 것

optimistic

커서로 행을 읽어들인 후 원본 테이블의 행이 업데이트 되었다면 커서에서 해당 행을 위치 지정 업데이트나 삭제하지 않도록 지정

type_warning

요청한 커서 형식이 다른 형식으로 암시적으로 변환된 경우 클라이언트에 경고 메시지를 보내는 것

예를 들어 코유 인덱스가 없을 때 keyset 커서 를 만들려고하면 암시적 변환이 작동해 static 커서로 만들어짐

이 엽션을 설정하지 않으면 아무 메시지가 나오지 않아 알수없음

이 옵션을 설정하면 문제를 미리 경고 메시지로 확인 가능함

FORselect_statement

select 문장을 사용하는 부분

select문의 결과가 한 행씩 처리됨

compute, compute by, for browse, into 키워드는 사용 불가

for update [of column_name [...n]]

그냥 for update만 지정하면 select 문의 모든 열을 업데이트 가능

of 열이름 을 지정하면 특정 열 지정 가능

커서 사용 시 기본적으로 전역 커서로 열기를 시도함

만약 지역, 전역에 같은 이름의 커서가 있다면 지역이 우선 됨

같은 이름이 있다면 꼭 global 키워드를 써줘야 전역 커서 열기 가능

프로시저와 비슷해 보이지만 일반 프로그래밍 언어에서 사용되는 함수와 갗이 복잡한 프로그래밍이 가능하며 return문에 의해 특정 값을 돌려주는 기능이 있음

select문에 포함되어 실행 가능하며 스칼라 함수는 exec로 실행 가능

시스템 함수

sql server 에서 제공하는 함수

사용자정의 스칼라 값 함수

리턴 값이 단일 값인 함수로서 text, ntext, image, cursor, timestamp를 제외한 모든 데이터 형식이 가능

사용자정의 테이블 값 함수

테이블 함수 라고도 하며 리턴 값이 테이블인 함수

인 라인 테이블 함수, 다중 문 테이블 함수가 있음

인 라인 테이블 함수

테이블을 돌려주는 함수이며 뷰와 비슷한 역할을 함

select문이 와서 결과 집합을 돌려줌

다중 문 테이블 함수

begin ~ end 로 정의되며 내부에 일련의 T-SQL을 이용해서 반환될 테이블에 행 값을 insert 하는 형식을 가짐

스키마 바운드 함수

테이블, 뷰 등이 수정되지 못하도록 설정한 함수

예를 들어 함수A가 테이블B, 뷰C 를 참조하고 있다면 이 테이블이나 뷰를 삭제하거나 열 이름을 바꾸지 못하게 함

함수 생성 시 with schemabinding 옵션을 주면 됨

사용자 정의 함수에서는 try~catch 사용이 불가하며 create, alter, drop또한 사용이 불가능함

함수 실행 중 오류가 발생하면 함수의 실행이 멈추고 값을 반환하지 않음

트랜잭션이 발생할 경우 개체에 접근할 수 없는 것을 블로킹이라고 함

만약 트랜잭션을 끝내지 않아 무한대로 기다리는 상황이 생길 수 있어 이것을 막기위해 타임아웃 옵션을 설정할 수 있음

select @@LOCK_TIMEOUT 로 조회 가능하며 -1 일 경우 무한대로 대기함

exec sp_who 프로시저로 블로킹 중인 프로세스 확인이 가능하며 kill프로세스로 해당 프로세스를 중단시킬 수 있음

set lock_timeout 1000 식으로 설정 가능하며 단위는 밀리초 이므로 1000 = 1초 임

타임아웃이 지난다고 트랜잭션이 취소되는 것은 아니며, 잠금이 걸린 데이터에 접근하는 쿼리를 취소하는 것임

두 잠금이 서로 상대가 풀리도록 대기하는 상태를 교착상태라고 함

서로 풀릴때까지 기다리느라 무한대로 멈추어야하지만 sql server에서 이런 상황을 자동으로 검색하여 한쪽을 희생시켜 해결 함 (검색 주기 디폴트 5초)

이 또한 시스템 자원을 소모하므로 성능을 위해 이런 상황을 만들지 않는게 좋음

되도록 트랜잭션 격히 수준을 read committed로 두고 하나의 트랜잭션에 너무 많은 쿼리를 넣지 않아야 함

분산 트랜잭션

서로 다른 서버에서 하나의 트랜잭션으로 데이터를 처리하는것

개념적으로는 하나의 트랜잭션이며 서버가 분리되어있다는 것만 차이 있음

begin tran 대신 begin distributed transaction을 사용하며 커밋은 동일하게 commit tran을 사용

분산 트랜잭션을 사용할떄는 xact_abort 옵션을 on으로 설정해야함

xact_abort를 on으로 설정하면 하나의 트랜잭션이 오류를 일으키면 전체 트랜잭션을 취소하므로 분산 트랜잭션에서는 꼭 ON으로 설정해야함 디폴트는 OFF임

또한 분산 트랜잭션을 사용하기 위해서는 MS DTC가 설치되고 가동되고 있어야 함

자동 커밋 트랜잭션

각 쿼리마다 자동으로 트랜잭션이 붙는 것

보통 사용하는 쿼리는 자동 커밋 트랜잭션이 작동 됨

명시적 트랜잭션

직접 begin, commit 을 사용하는 것

암시적 트랜잭션

타 데이터베이스와 호환을 위해 사용

자동으로 begin tran이 붙지만 commit tran이나 rollback tran 은 직접 써줘야 하는 것

set implicit_transaction on 을 사용하면 암시적 트랜잭션이 시작 됨

잠금

sql server에서 잠금이 중요한 이유는 일관성 떄문

잠금은 동시에 여러 사용자가 하나의 데이터에 접근할 경우 즉 트랜잭션 상황에서 데이터의 격리 수준을 말함

↑약한 격리 수준

read uncommitted (커밋되지 않은 읽기)

동시성은 높지만 일관성이 낮음

read committed (커밋된 읽기) <- sql server 2005 기본값

데이터 변경이 있는 트랜잭션 중에는 읽을 수 없음

update나 insert로 배타적 잠금 (exclusive lock)이 발생되어 dirty read 문제 해결

select에 의한 공유잠금(shared lock)일 경우 데이터 변경이 가능한 unrepeatable read 발생

repeatable read (반복된 읽기)

공유잠금 상태에서 update를 통한 데이터 변경이 불가

하지만 insert를 통한 새로운 데이터 입력은 가능 (phantom read)

snapshot

트랜잭션중인 테이블에 데이터를 입력하면 tempdb에 임시로 저장하고 커밋 후에 원래 테이블에 입력함

serializable (직렬화 가능)

phantom read를 해결

일관성은 높지만 동시성이 낮음

↓강한 격리 수준

*여러 사용자가 동시에 하나의 데이터에 접근하면 일어나는 문제

-dirty read (커밋되지 않은 데이터 읽기)

메모리 (더티 페이지)에 변경된 데이터를 읽는 것

디스크에 저장되지 않고 데이터가 롤백된다면 잘못된 데이터를 읽은것이 됨

-unrepeatable read (반복되지 않은 읽기)

데이터 변경이 있는 트랜잭션 중에는 읽을 수 없음

배타적 잠금 (exclusive lock)이 발생되어 더티리드 문제 해결

select에 의한 공유잠금(shared lock)일 경우에도 데이터 변경이 가능

-phantom read (가상 읽기)

공유잠금 상태에서 insert를 통한 새로운 데이터 입력이 가능

잠금이 걸리는 리소스

행 단위 부터 데이터베이스 단위 까지 여러 잠금 범위가 있음

단위가 작을수록 동시성은 좋지만 관리 할 부분이 많아져 오버헤드가 증가됨

단위가 크다면 반대로 관리 할 잠금 수가 줄어 오버헤드는 줄지만 동시성이 나빠짐

잠금 리소스 종류

RID : 행 식별자는 단일 행을 잠그는 데 사용

KEY : 인덱스 내의 행 잠금은 serializable 격리 수준에서 사용

PAGE : 8KB 크기의 데이터 페이지나 인덱스 페이지

EXTENT : 8개의 페이지가 연속된 것

HORT : 클러스터형 인덱스가 없는 테이블에서 데이터 페이지나 인덱스를 보호하는 잠금

TABLE : 데이터와 인덱스가 포함된 전체 테이블

FILE : 데이터베이스 파일

APPLICATION : 응용프로그램이 지정한 리소스

METADATA

ALLOCATION_UNIT : 할당 단위

DATABASE : 전체 데이터베이스

잠금 모드 종류

서로 같이 작동할 수 없는 것들도 있고, 다른 모드를 수정하는 것도 있음

공유잠금 (S:shared lock) : select같은 데이터 읽기 작업에 사용, 공유잠금 끼리는 호환됨 (동시에 같은 데이터 select 가능)

배타잠금 (X:exclusive lock) : 데이터 수정작업 (insert, update, delete)에 사용, 다른잠금과 호환 안됨, 하나의 리소스에 여러 작업을 못하게 방지

업데이트잠금 (U:update lock) : 업데이트할 수 있는 리소스에 사용, 여러 사용자가 업데이트할 때 발생하는 교착상태 방지

의도잠금 (intent lock) : 잠금 계층 구조를 만드는 데 사용, 의도공유 의도배타 의도배타공유 등이 있다

스키마잠금 ( schema lock) : 테이블 스키마에 종속되는 작업이 실행될 때 사용, 스키마수정, 스키마안정성 잠금이 있음

대량 업데이트 잠금 (bulk updqte lock) : 데이터를 테이블로 대량 복사하는 경우와 TABLOCK힌트가 지정된 경우 사용

키 범위 잠금 : serializable 트랜잭션 격리 수준을 사용할 때 쿼리가 읽는 행 범위를 보호

자주 사용하는 쿼리를 모듈화 시켜서 필요할때 호출하여 사용하도록 제작 한 것

하나의 개체로서 데이터베이스 내부에 저장됨

create procedure 프로시저 이름

as

  쿼리

형식으로 생성하며

exec 프로시저 이름

형식으로 사용 함

수정은 alter procedure, 삭젠ㄴ drop procedure

실행 시 입력 매개변수 지정이 가능

@입력매개변수이름 데이터 형식 [ = 디폴트 값]

exec 프로시저 이름 [입력매개변수]

형식으로 사용 함

출력 매개변수 지정도 가능

@출력매개변수이름 데이터형식 OUTPUT(또는 OUT)

exec 프로시저 이름 @변수명 OUTPUT

형식으로 사용 함

프로시저 내부에서 sql 프로그래밍이 가능

exec sp_helptext 프로시저 이름

을 사용하면 프로시저 코드를 볼 수 있으며 프로시저 생성 시 with encryption 옵션을 주면 암호화가 되어 코드를 볼 수 없음

저장 프로시저를 사용하면 서버 성능을 향상시킬 수 있음

처음 프로시저를 실행하면 최적화, 컴파일 등의 과정을 거치고 그 결과가 메모리에 저장됨

다시 프로시저를 실행하면 메모리에 올라간 것을 사용하므로 최적화, 컴파일 과정이 생략 됨

쿼리를 저장 프로시저로 저장해 놓으면 쿼리 관리, 실행이 수월해짐

사용자에게 테이블 접근 권한을 주지 않고 프로시저 실행 권한만 주어 보안을 강화 할 수 있음

긴 쿼리를 저장 프로시저로 만들면 네트워크 전송량도 줄일 수 있어 네트워크 부하가 줄어듬

저장 프로시저의 종류

사용자 정의 프로시저

T-SQL 저장 프로시저

사용자가 직접 create proc 으로 생성한 프로시저

CLR 저장 프로시저

T-SQL 프로시저 보다 효율적이고 강력한 프로그래밍이 가능

.net framework 어셈블리 클래스에 공용의 정적 메소드로 구현되며, 사용자로부터 매개변수를 입력받아 결과를 반환하는 .net franework clr 메소드의 참조로 사용됨

시스템 저장 프로시저

시스템 관리를 위해 sql server에서 제공하는 프로시저

주로 sp_ 라는 접두어가 붙어있음

저장 프로시저의 작동

일반 T-SQL문을 실행하면 다음 순서에 따라 작동 됨

구문분석 : select, from 등 단어에 오류가 있는지 분석, 예약어나 오타를 체크

개체 이름 확인 : 테이블, 데이터베이스, 컬럼 등 이름이 정상적으로 있는지 확인

사용권한 확인 : 현 사용자가 접근 권한이 있는지 확인

최적화 : 쿼리의 가장 좋은 성능을 낼수있는 결로를 결정, 주로 인덱스 사용 여부를 결정

컴파일 및 실행계획 등록 : 최적화된 결과를 컴파일 하고 결과(실행계획)을 메모리에 등록

실행 : 컴파일된 결과를 실행

동일한 T-SQL문을 다시 실행하면 메모리에서 실행계획을 읽어와 바로 실행

동일한 쿼리의 조건은 대소문자 띄어쓰기 등이 완전 같아야 함 select와 Select 만 달라도 구문분석부터 시작 됨

저장 프로시저의 정의 순서는 다음과 같음

저장 프로시저 정의 (create proc)

구문 분석 : 구문의 오타나 문법 오류 등을 파악

지연된 이름 확인 : 프로시저를 정의 하는 시점에서 개체 (테이블 등)가 없어도 관계가 없음, 개체의 존재 여부는 프로시저 실행 시 확인하게 됨 (이미 있는 테이블의 컬럼 명이 다르면 안됨)

생성권한 확인 : 현 사용자가 저장프로시저를 생성할 권한이 있는지 확인

시스템 테이블에 등록 : 저장프로시저의 이름 및 코드가 시스템 테이블에 등록 됨 (sys.objects, sys.sql_modules 카탈로그 뷰로 확인 가능)

저장 프로시저를 정의한 후 최초 1회의 작동 방식은 다음과 같음

개체 이름 확인 → 사용권한 확인 → 최적화 → 컴파일 및 실행계획 등록 → 실행

프로시저 정의 시 진행한 구문 분석을 포함하면 일반 쿼리 실행 순서와 유사함

두번째 실행 시에는 메모리에 저장된 것을 그대로 사용

저장 프로시저의 재컴파일

첫 실행에서 최적화된 저장프로시저가 다시 실행될 때 다른 최적화 방식을 사용해야 할 때는 재컴파일 되어야 함

4가지 방법이 있음

-프로시저 실행 시 with recompile 옵션을 추가

-'sp_recompile 테이블 이름' 시스템 프로시저를 사용하면 해당 테이블과 관련된 프로시저를 다시 사용할 때 재컴파일 됨

-dbcc freeproccache 를 사용하면 메모리 프로시저 캐시 영역을 비우고 프로시저 사용시 재컴파일 함

-저장프로시저를 실행할 때마다 자동으로 재컴파일되도록 프로시저 작성

데이터에 빠르게 접근할 수 있도록 도와줌

데이터베이스 튜닝에 중요

잘못 사용하면 성능 저하

인덱스를 저장할 추가 공간 필요

데이터 변경 작업 (insert, update, delete)가 자주 일어나면 성능 저하

-clustered index

영어사전과 같은 형식

테이블당 1개만 생성 가능

생성 시 데이터 전체 정렬

행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬

루트 페이지와 중간 페이지, 그리고 리프 페이지는 인덱스로 구성되어 있으며 리프 페이지 자체가 데이터 페이지

-nonclustered index

인덱스가 별도로 있고 인덱스를 찾은 후 해당 부분으로 가야 실제 내용 확인 가능

여러개 생성 가능

데이터 페이지를 건드리지 않고 별도의 인덱스 페이지를 생성

리프 페이지에 인덱스로 구성한 열을 정렬하고 데이터 위치 포인터를 생성

데이터 위치 포인트는 페이지번호+#오프셋 으로 기록되어 이는 데이터 영역의 데이터의 위치를 가리킴

일정 수 이상의(전체 데이터의 약 1~3%) 스캔을 할 경우 테이블 스캔을 실시함

-clustered와 nonclustered index가 테이블에 같이 있을 경우

clustered의 인덱스 페이지는 동일하지만 nonclustered는 리프 페이지에 포인터 대신 clustered의 키 값을 가지고 있음

기존처럼 포인터로 찾아가지 않고 clustered 키값을 가지고 clustered index 페이지에서 검색을 함

인덱스는 컬럼단위로 생성

하나 또는 여러개의 컬럼에 생성 가능

제약조건 없이 테이블 생성 시에 인덱스를 만들 수 없으며 인덱스가 자동 생성되기 위한 열의 제약조건은 primary key와 unique 뿐

-primary key

1개만 생성 가능

다른 clustered index로 지정된 인덱스가 없으면 기본적으로 clustered index로 생성 됨

생성 시 nonclustered 옵션을 주면 nonclustered 인덱스로 생성 됨

-unique

여러개 생성 가능

기본적으로 nonclustered index

clustered index로 설정 가능

인덱스는 균형 트리와 유사한 방식으로 동작

sql 데이터는 페이지라는 단위로 저장됨

select에 유리한 방식으로 저장됨

insert, update, delete 등 작업이 일어나면 트리로 연결되어있는 노드(페이지)의 정렬, 페이지 공간 부족시 페이지 분할 이 일어나기때문에 느려질 수 있음

인덱스 삭제시에는 drop index [테이블].[인덱스] 형식을 사용

제약조건으로 생성된 인덱스는 drop index 로 삭제 불가

alter table [테이블] drop constraint [인덱스] 로 삭제 해야함

데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효용이 없다

외래 키가 사용되는 열에는 인덱스를 생성해주는 것이 좋다

join에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다

사용하지 않는 인덱스는 제거

계산 열 (persisted)도 인덱스를 만들 수 있다

포괄 열이 있는 nonclustered 인덱스를 활용하면 쿼리 성능을 높일 수 있다

포괄 열을 nonclustered로 인덱스를 만들면 clustered 처럼 리프 노드에 데이터가 존재하게 됨

기존 데이터 형식에 별칭을 붙여 사용이 가능

ex) nchar(10) => mychartype

ssms의 개체 탐색기에서

데이터베이스 -> 해당 데이터베이스 -> 프로그래밍 기능 -> 유형 -> 사용자정의 데이터 형식

메뉴를 따라가 정의 가능함

또는

EXEC sp_addtype '사용자정의형식이름', '시스템데이터형식', 'NULL여부', '소유자';

와 같은 형식의 프로시저를 사용해 정의 가능

WITH 절은 CTE를 표현하기 위한 구문

CTE : Common Table Expression (공통 테이블 식)

CTE는 기존 뷰, 파생 테이블, 임시 테이블 등을 대신할 수 있으며 더 간결한 표현이 가능

CTE는 비재귀적, 재귀적 2가지가 있음

비재귀적 CTE

단순한 형태이며 쿼리분을 단순화 시키는 데에 적합

WITH CTE 테이블 이름 (열 이름)

AS

(

쿼리문

)

SELECT 열 이름 FROM CTE 테이블 이름;

WITH 절로 조회한 결과로 가상의 테이블을 만들어 select를 실행한다.

WITH 

AAA (열 이름)

AS (쿼리문),

BBB (열 이름)

AS (쿼리문), 

CCC (열 이름)

AS (쿼리문)

SELECT 열 이름 FROM CTE 테이블 이름;

위 형식처럼 중복 CTE도 가능하지만 BBB, CCC는 AAA의 참조가 가능하고 AAA는 아직 정의되지 않은 BBB, CCC를 참조할 수 없음

재귀적 CTE

재귀적 : 자신을 반복적으로 호출

WITH CTE 테이블 이름 (열 이름)

AS

(

<쿼리문1 : SELECT * FROM 테이블1>

UNION ALL

<쿼리문2 : SELECT * FROM 테이블1 JOIN CTE 테이블 이름>

)

SELECT 열 이름 FROM CTE 테이블 이름;

먼저 쿼리문1이 작동하고 다음에 쿼리문2가 작동하며 쿼리문2의 결과가 없을때까지 계속 재귀적으로 호출

SQL 2008 R2 설치후 위와 같이 구성 관리자에서 오류가 나는 경우가 있다.

간단하게 서비스팩을 설치하면 해결된다.

mssql 설치 후 로컬 테스트시 다음 과 같은 이유로 서버에 연결이 불가능하다고 나올때가 있다.

제목: 서버에 연결

------------------------------

ㅁㅁㅁㅁ-PC에 연결할 수 없습니다.

------------------------------

추가 정보:

SQL Server에 연결을 설정하는 중에 네트워크 관련 또는 인스턴스 관련 오류가 발생했습니다. 서버를 찾을 수 없거나 액세스할 수 없습니다. 인스턴스 이름이 올바르고 SQL Server가 원격 연결을 허용하도록 구성되어 있는지 확인하십시오. (provider: Named Pipes Provider, error: 40 - SQL Server에 대한 연결을 열 수 없습니다.) (Microsoft SQL Server, 오류: 2)

도움말을 보려면 다음을 클릭하십시오: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

------------------------------

지정된 파일을 찾을 수 없습니다

------------------------------

단추:

확인

------------------------------

'Ctrl + Alt + Del' 을 눌러 작업 관리자를 켠 뒤 서비스 탭으로 가서 MSSQLSERVER 서비스의 상태를 확인한다.

위와 같이 중지된 상태일 경우 오른쪽 클릭을 하여 서비스를 실행해준다.