Transcript SQL 강의노트
MS-SQL7.0 Implementation 강의 노트 Written by 남현주 History (SQL Server의 역사) 1970년 IBM : 데이터베이스 질의를 위한 언어 만듬 – SEQUEL (구조적 질의 언어 : Structured English Query Language) Sybase 와 Microsoft 가 협력 v1.0을 OS/2용으로 , v4.2 부터 NT 서버로 이식 V6.0 부터는 Microsoft 독자적으로 개발 6.5, 7.0으로 진보된 기능들을 선보임 (위저드 제공, UniCode지원) 제1장 SQL Server 개요 SQL Server란 클라이언트/서버형의 관계형 데이타베이스 관리 시스 템(RDBMS)이다. 클라이언트/서버(Client/Server Architecture) 클라이언트 : 다른 시스템 구성요소 들에게 서비스나 자원을 요청하는 시스템 구성요소 서 버 : 서비스나 자원을 다른 시스템 구성 요소 들에 제공하는 시스템 구성요소 클라이언트/서버 환경 클라이언트는 서버에게 자신이 원하는 바를 요청 (Request)한다. 그러면 서버는 그에 대해 처리하고 클라이언트에게 응답(Response)한다 데이터베이스의 필요 배경 SAM(Sequential Access Method) I-SAM(Indexed SAM) 파일 출현 PC :dBase, 호스트 :네트워크 DB, 계층적 DB 등장 DBMS(DataBase Management System) 등장 SQL Server는 관계형 데이터베이스 관리 시스템 (RDBMS:Relational Database Management System) - 데이터에 기반한 시스템의 3가지 범주 . 파일 기반 시스템(File-based systems) . 호스트 기반 시스템(Host_based systems) . 클라이언트/서버 시스템(C/S systems) 관계형 데이터베이스 관리 시스템(RDBMS) 구조 : 2차원 테이블 Data base table Data modeling entity Application record set column row attribute instance field record Transact_SQL SQL Server는 구조화된 쿼리 언어의 한 버전인 Transact-SQL을 데이터베이스 쿼리 및 프로그래 밍언어로 이용한다. Transact-SQL로는 데이터를 액세스하고 관리할 수 있다. 조 작 : DB에 저장된 데이터의 입력, 수정, 삭제, 조 회 등의 작업을 SQL에 의해서 최종사용자에 게 보여지게 함 무결성 : 데이터의 일관성, 믿을 만한 데이터로 만듬 NT 서버와의 관계 SQL Server는 NT Server가 주된 플랫폼. NT의 기능들 중에서 SQL Server가 그대로 사용하는 것 은 다음과 같다. 1. 2. 3. 4. 5. 6. 7. 보안 체계 멀트 프로세서의 지원 NT 이벤트 지원 서비스로서의 동작 성능 모니터 Index서버 백 오피스와 관련성 SQL Server 구성요소 - SQL Server 서비스 . MSSQLServer 서비스 . SQL Server Agent 서비스 . MS DTC (Microsoft Distributed Transaction Coordinator) . MS Search 서비스 - SQL Server 클라이언트 소프트웨어 . SQLServer Enterprise Manager . SQL Server Query Analyzer . SQL Server 관리도구와 마법사 . SQL Server 명령 프롬프트 관리 도구들 SQL Server 구조개요 - 통신 구조 . . . . 어플리케이션 데이터베이스 인터페이스 네트워크 라이브러리 개방형 데이터 서비스 - 데이터 액세스 구조 . 응용 프로그램 프로그래밍 인터페이스 OLE DB ODBC . 데이터 객체 인터페이스 ADO RDO 제2장 Transact-SQL 개요 SQL Server 프로그래밍 도구 SQL Server Enterprise Management 사용방법과 SQL Server Query Analyzer 사용방법 - 세션 쿼리저장 GO 배치 분리자 판독의 용이를 위한 여러 라인 코딩 실행(전체, 일부분) - Parse : 분석은 하지만 실행하지는 않음 - 단축키 - osql 유틸리티 : 명령 프롬프트에서 쿼리나 스크립트 실행 Transact-SQL 구성요소 데이터 제어 언어(Data Control Language, DCL) 데이터 정의 언어(Data Definition Language, DDL) 데이터 조작 언어(Data Manipulation Language, DML) 변수, 연산자, 함수, 처리 제어 언어, 주석 (데이터 제어 언어(Data Control Language, DCL)) DCL문은 데이터베이스 사용자 혹은 역할과 관련된 권한 을 변경할 때 사용된다. 문(Statement) 설명 GRANT 보안이 요구되는 시스템에서 데이터를 조작하고 T-SQL 문을 실행하는 사용 자를 허가하는 엔트리를 생성한다. DENY 보안이 요구되는 시스템에서 계정의 권한을 거부하고 사용자, 그룹, 역할로 부터 그들의 그룹과 역할 멤버쉽을 통 해 권한을 상속 받는 것 을 막 는다 . REVOKE 이전에 허가 또는 거부되었 던 권한을 제거한다. (데이터 정의 언어(Data Definition Language, DDL)) DDL문은 데이터베이스와 테이블 및 저장 프로시저 같은 데이터베이스 객체의 생성을 통해 데이터베이스 구조를 정의한다. CREATE , ALTER, DROP 문 * 참고 : 객체의 이름 Fully Qualified Names : 서버이름, 데이터베이스 이름, 소유자 이름, 객체 이름 대부분의 객체 참조는 로컬 서버를 디폴트로 하고 세 부분의 이름을 사용한다 (데이터 조작 언어(Data Manipulation Language, DML)) DML문은 데이터베이스에서 데이터를 조작한다. 이 문 을 이용하여 데이터 변경 또는 정보를 추출할 수 있다. SELECT, INSERT, UPDATE, DELETE 기본적인 SELECT 문 SELECT 이후에는 가져올 컬럼이나 표현식을 나열하면 된다. 모든 컬럼 지정시 * FROM 이후에는 어떤 테이블 로부터 데이터를 가져오는지 지시한다. 여러 테이블이 올 수 도 있고, 뷰가 올 수도 있다. WHERE 절 이후에는 어떤 행을 가져올 것인지를 지시한다. 생략하면 전체행을 가져옴 예) SELECT * FROM titles – titles테이블의 전체 자료 가져오기 SELECT title_id, pub_id, price, title FROM titles – 원하는 컬럼 순으로 가져오기 SELECT ‘책 번호:’, title_id, pub_id, price, title FROM titles – 임의의 컬럼 만들기 SELECT Title_no = title_id, ‘출판사 번호’ = pub_id, price, title FROM titles – 컬럼의 제목 바꾸기(1) SELECT title_id as Title_no , pub_id as [출판사 번호], price, title FROM titles – 컬럼의 제목 바꾸기(2) SELECT title_id Title_no , pub_id [출판사 번호], price, title FROM titles – 컬럼의 제목 바꾸기(3) SELECT title_id as Title_no, pubid as ‘출판사 번호’, price, Convert(char(30), title) as ‘줄인 제목’ FROM titles – 자료를 변형시킴(1) SELECT title_id as Title_no, pubid as ‘출판사 번호’, price * 1.1 as ‘가격(부가세 포함)’ FROM titles – 자료를 변형시킴(2) * 주의 : SELECT 10/3 은? (로컬 변수) 변수는 할당된 값을 갖는 언어 구성요소이다. 사용자 정의 로컬 변수는 @로 시작한다. 예) DECALRE @lvname char(20) SET @ lvname = ‘Dodsworth’ SELECT * FROM employees WHERE lastname = @lvname (연산자) 치환 연산자(=), 산술 연산자, 단항 연산자, 비교 연산자, 문자열 연결 연산자(+), 논리 연산자 (함 수) Rowset함수 : T-SQL 문 테이블에서 사용될 수 있는 객체를 반환한다. 집계(Aggregate)함수 : 일련의 값으로부터 하나의 요약된 값을 반환한다. 스칼라(Scalar)함수 : 하나의 값을 반환한다. (p96 ~102) 형변환 함수 ) CONVERT() 연산 함수) ABS() – 절대값 CEILING() – 소수점 이하 버림 SELECT CEILING(29.9) FLOOR() – 소수점 이상 절상 SELECT FLOOR(29.9) ROUND() – 반올림 SELECT ROUND(29.1, 0), ROUND(29.953, 1) 문자열 함수) LTRIM() : 앞쪽의 빈칸을 모두 삭제한다. RTRIM() : 뒤에 오는 빈 칸을 삭제한다. SUBSTRING() : 문자나 이진 문자열의 일부분을 돌려준 다. 한글도 한 글자가 한 문자로 인식된다. SELECT SUBSTRING(‘아버지 나는 누구예요?’, 3,6) SELECT SUBSTRING(‘ABFZ!!!’, 1,3) REVERSE() – 문자 표현식을 거꾸로 돌려준다. 날짜 함수) GETDATE() – 현재의 날짜와 시간 SELECT CONVERT(varchar(30), GETDATE(), 102) DATEADD() – datepart부분에 지정한 숫자 값을 더한다. SELECT DATEADD(DD, 100, GETDATE()) – 오늘로 부터 100일 후는? datepart 약어 범위 year quarter month Day of year Day Week Weekday Hour Minute Second millisencond Yy Qq Mm Dy Dd Wk Dw Hh Mi Ss Ms 1753-9999 1-4 1-12 1-366 1-31 0-51 1-7(1이 일요일) 0-23 0-59 0-59 0-999 시스템 함수) OBJECT_ID() : 데이터베이스의 오브젝트 아이디 번호를 돌려준다. OBJECT_NAME() : 데이터베이스 오브젝트명 보안 함수) SUSER_NAME() : 시스템 유저명을 돌려준다. * 조건에 맞는 행 가져오기 SELECT title_id, qty FROM sales WHERE title_id = ‘BU1032’ - *비 교 = > < >= <= - * NULL SELECT title_id, price FROM titles WHERE price IS NULL 주의) <> SELECT title_id, price FROM titles WHERE price = NULL != !> !< - * 정 렬 SELECT title_id, qty FROM sales ORDER BY title_id, qty 주의) SELECT title_id, qty FROM sales ORDER BY title_id, qty DESC - *범 위 SELECT title_id, qty FROM sales WHERE qty BETWEEN 10 AND 20 (WHERE qty >= 10 AND qty <= 20) - *목 록 SELECT title_id, qty FROM sales WHERE title_id IN (‘BU1032’,’BU1111’,’MC3021’) (WHERE title_id =‘BU1032’ OR title_id =’BU1111’ OR title_id =’MC3021’) 문자열에서 LIKE와 패턴 매칭 패턴 설명 _ 정확히 한 문자가 와야 한다. % 아무것도 없는 경우를 포함하여 어떤 것이라도 상관 없다. [ ]안에 있는 글자들 [] [^] ^ 다음에 이는 글자는 제외한 나머지 - 표현식 해당되는 것 해당되지 않는 것 book_ books, booka, booky book, booked book% book, books, booked abook, atbooks, adbooked %book% abook, abooks, book brook, brooks [st]ing sing, ting ving, king, k, v, vin [b-f]ing bing, cing, ding, eing ,fing aing, b, f, ging M[^c]% Mike, Many, Mickey McDonald, McAthur, M, Mc * 중복된 행 제거 – DISTINCT SELECT DISTINCT title_id FROM sales ( ! DISTINCT가 사용되면 자동으로 정렬해줌 ) /* 튜닝을 위한 제안 */ . NOT 연산자를 사용하지 말것 – 색인의 도움을 받을 수 없다. . 연산자 앞에는 컬럼만 오도록 할 것 . 적절한 ( )와 띄어쓰기는 가독성을 좋게한다. (흐름 제어 언어 구성요소) BEGIN… END 블록 IF… ELSE 블록 WHILE 구조 : 지정된 조건이 참인 동안 블록을 반복적 으로 수행 RETURN : 쿼리 혹은 프로시저로부터 무조건 빠져 나 옴. RETURN 뒤의 문은 실행되지 않는다. 0 값이 정 상, 0이 아닌 값이 에러지시를 위해 사용된다. 인 라인 주석( -- ) : 주석문자의 오른쪽 텍스트는 무 시된다. 블록 주석(/* */) (배치 이용하기) 여러 개의 SQL문을 모아 놓고 GO라는 키워드로 구분한 것을 배치라고 한다. 배치 구분자 GO를 이용하여 배치 정의하기 사용자 정의된 변수 범위는 배치에서 제한되므로 배치 구분자 GO 뒤에서는 참조할 수 없다. SQL Server는 GO가 나타날 때까지 문법 검사를 하지도, 실행하지도 않는다. 규칙 : CREATE DEFAULT, CREATE TRIGGER, CREATE PROC(EDURE), CREATE RULE, CREATE VIEW는 배 치의 처음에 나타나야 한다. (스크립트 사용하기) 파일로 저장되는 하나 이상의 T-SQL문이다. 즉, 배치를 여러 개 모은 것을 스크립트라고 한다. 일반적으로 파일로 저장해두고 반복적인 작업을 할 때 사용한다. 표준 확장자는 .SQL 이다. (쿼리가 처리되는 과정 P112~) (캐시 쿼리) (다이나믹하게 문 구성하기) (트랜잭션 사용하기) 제3장 데이터베이스 생성하기 R-DBMS(관계형 데이터베이스) 설계 소개 데이터베이스란 어느 한 조직의 다수 응용 시스템들이 사용하기 위해 통합, 저장되어진 서로 관련된 데이터 의 집합이다. “서로 관련된 데이터의 집합” 구조는 행(Row)과 열(Column)로 이루어진 2차원 테이블로 되어있다. 용어정리> 데이터베이스 데이터 모델링 어플리케이션 테이블(table) 실체(entity) 레코드셋(record set) 열(column) 속성(attribute) 필드(field) 행(row) 인스턴스(instance) 레코드(record) 관계형 데이터베이스는 설계시에 꼭 중요한 모델링이라 는 작업이 들어간다. 설계에 들어가는 시간이 개발에 들어가는 시간보다 월등히 많이 필요하다.(70~80%) 모델링을 하는 가장 큰 이유는 데이터의 중복성을 제거 하는 것이다. 모델링의 3단계: 실체, 관계, 속성 실체(entity) : 하나로 묶을 수 있는 것의 실질적인 집합 예) 사원 테이블, 고객 테이블 등… 관계(Relationship) : 실체에 대한 설명이나 추가적인 구분자, 기본키와 참조키로 정의된다. 예) 사원과 부서의 관계, 고객과 주문의 관계 등… 속성(Attribute) : 실체나 관계에 대한 서술적 속성. 컬럼 예) 사원명, 사원번호, 주소, 전화번호 등… 논리적인 데이터베이스 설계 .테이블 및 해당 이름 .각 테이블의 컬럼명 .유일 값 요구, 널 허용, 컬럼에 저장될 자료형 같은 컬럼의 특성 .각 테이블의 기본키 : 테이블의 컬럼을 유일하게 식별 할 수 있는 값들을 저장하는 컬럼이다. .테이블간의 관계 : 어떤 테이블의 행들은 다른 테이블 의 하나 이상의 행에 종속적이다.이러한 테이블 사 이의 종족성을 관계라 한다. 관계를 정의하기 위해 서 다른 테이블의 기본 키를 참조하는 이러한 컬럼 을 참조 키라 한다. .관계에는 일대일, 일대다, 다대일, 다대다와 같은 경 우가 발생할 수 있다. 참고로 다대다는 R-DB에서는 인정되지 않는 관계로 정규화를 거쳐야 한다. (정 규 화) 1.제 1정규화(1NF) 규칙 : 여러 값을 가진 컬럼이 존재할 수 없다. 반 복되는 그룹이 존재해서는 안된다. 각 행과 열에는 오직 한 값만이 존재해야 한다. 사번 사원명 취미 1 홍길동 여행 골프 2 사미자 등산 1번 사원은 두 가지 취미를 갖고 있다. 이 그룹은 제 1 정규화의 대상이 된다. 다음과 같이 정규화 한다. 사번 사원명 사번 취미 1 홍길동 1 여행 2 사미자 1 골프 2 등산 2.제 2정규화(2NF) 규칙 : 모든 키가 아닌 컬럼은 기본 키 전체에 의존 적이어야 한다. 기본키의 일부분에 의존적이어서는 안된다. 사번 프로젝트번호 부서 프로젝트 역할 고과율 1 A 전산 팀장 A 1 B 전산 조원 C 1 C 전산 부팀장 B 2 C 경리 팀장 A 3 C 기획 팀장 A 이 테이블에서 기본키는 (사번+프로젝트 번호)이다. 그런데 부서 컬럼은 사번에 의존적이다. 즉, 부서컬럼 은 (사번+프로젝트 번호)에 의존적이지 않고 사번에 의 존적이므로 제 2정규화를 거쳐야 한다. 사번 프로젝트 번호 프로젝트 역할 고과율 1 A 팀장 A 1 B 조원 C 1 C 부팀장 B 2 C 팀장 A 3 C 팀장 A 사번 부서 1 전산 2 경리 3 기획 3.제 3정규화(3NF) 규칙: 키가 아닌 컬럼은, 다른 키가 아닌 컬럼에 의 존적이어서는 안된다. 사번 프로젝트번호 프로젝트 역할 고과율 1 A 팀장 A 1 B 조원 C 1 C 부팀장 B 2 C 팀장 A 3 C 팀장 A 고과율 컬럼은 프로젝트 역할에 따라 변하고 있음을 볼 수 있다. 즉, 고과율은 키가 아닌, 프로젝트 역할 컬럼 에 의존적이다. 사번 프로젝트번호 프로젝트 역할 프로젝트 역할 고과율 1 A 팀장 팀장 A 1 B 조원 조원 C 1 C 부팀장 부팀장 B 2 C 팀장 3 C 팀장 4.제 4정규화(4NF) 규칙 : 3NF 테이블은 의존적인 다대다 관계를 가질 수없다. 학번 이름 과목번호 과목명 1 최철호 K 국어 2 이상진 K 국어 3 김철민 K 국어 3 김철민 M 수학 3 김철민 E 영어 2 이상진 H 역사 2 이상진 P 정치 1 최철호 P 정치 위의 테이블은 학생과 과복 간의 관계가 다 : 다 관계 이다. 이를 해결하면 다음과 같다. <학생> <수강신청> <과목> 학번 이름 학번 과목번호 과목번호 과목명 1 최철호 1 K K 국어 2 이상진 2 K M 수학 3 김철민 3 K E 영어 3 M H 역사 3 E P 정치 2 H 2 P 1 P 5. 비 정규화 정규화를 하는 것은 되도록 중복된 데이터를 제거해서 성능 향상에 도움을 주는 것에 초점을 두고 있으나, 자 주 JOIN을 사용하면 차라리 테이블을 다시 합치는 것이 나을 수도 있기 때문에 이를 고려한 방법이 비 정규화 이다. 뿐만 아니라 파생된 컬럼을 테이블에 하나의 컬 럼으로 저장하는 경우나, 한 테이블에서 자주 쓰는 행 과 그렇지 않은 행을 분리하여 테이블을 별도로 관리하 는 것 또한 비 정규화에 속한다. SQL Server 데이터베이스 소개 (데이터가 어떻게 저장 되는가) (SQL Server 데이터베이스 파일) 일차 데이터 파일 (.mdf) 이차 데이터 파일 (.ndf) 로그 파일 (.ldf) 데이트베이스의 아키텍처: 데이터를 저장하는 데이터 파일은 그 내부에 여러 개의 익스텐트 단위 구조를 가지고 있고, 다시 익스텐트는 8 개의 연속된 페이지를 기본 단위로 한다. (SQL Server에서 Input/Output단위는 page이다) 페이지가 가들 차게 되면 다음 페이지에 저장된다. 그 러나 이 때에 하나의 행은 여러 페이지에 동시에 저장 되어 질 수 없기 때문에 익스텐트가 가득 차게 되면 새 로운 익스텐트를 할당받게 된다. 즉 64KB를 더 사용 할 수 있게 된다. (트랜잭션 로그의 작동 방법) 트랜잭션이란 어떤 문제가 발생하면 여러 개의 작업이 하 나의 작업처럼 전부 처리되거나 아니면 아예 전부 처리가 안 되도록(All or Nothing) 하는, 일의 최소 단위이다. 디스크의 성능을 높이기 위해서 캐쉬라는 것을 사용한다. 캐쉬는 디스크의 내용 중 일부를 메모리에 저장해 두는 원 리나 메모리를 뜻한다. 따라서 빠른 수행 속도를 가져온 다는 장점이 있지만, 메모리에서 처리하기 때문에 정전이 되었을 경우 데이터가 하드디스크에 저장되지 못한다는 단점이 있다. SQL Server는 트랜잭션 처리를 위해서 잠금 과 로그를 사용한다. SQL Server에서는 모든 데이터를 변 경하는 작업에 대해서 로그에 먼저 기록한다. 트랜잭션이 완료되고 나면 로그는 메모리에서 디스크로 기록된다. * 데이터베이스 생성 및 삭제하기 - 생성하기 CREATE DATABASE mydb1 만 입력해도 데이터베이스 는 생성된다. 모두 기본 설정값을 그대로 반영한 것이다. 예) CREATE DATABASE mydb2 -- 데이터베이스 명은 mydb2이다 ON -- 데이터를 저장하는 공간이다. ( NAME = mydb2data -- 논리적 파일명을 지정한다. , FILENAME = ‘c:\mssql7\data\mydb2.mdf’ -- 물리적 파일명을 지정한다. , SIZE = 10MB --크기는 MB단위 , MAXSIZE = 50MB -- 최대 몇 MB까지 자동적으로 늘릴 것인지 지정한다. , FILEGROWTH = 5% -- 늘어날 때 5% 단위로 늘어나도록 지정한다. ) LOG ON ( NAME = mydb2log , FILENAME = ‘c:\mssql7\data\mydb2.ldf’ , SIZE = 5MB , MAXSIZE = 25MB , FILEGROWTH = 5MB -- 늘어날 때 MB 단위로 늘어나도록 지정한다. ) - 삭제하기 DROP DATABASE database_name [,…n] 제4장 DB 객체생성하기 데이터 형식 생성하기 (시스템 제공 데이터 형식) Type of data System-supplied data types Number of bytes Binary 1-8000 Unicode character binary[(n)] varbinary[(n)] char[(n)] varchar[(n)] nchar[(n)] nvarchar[(n)] Date and time Datetime, smalldatetime 8 (2 4-byte integers) 4 (2 2-byte integers) Exact numeric Approximate numeric Global Identifier Integer decimal[(p[, s])] numeric[(p[, s])] float[(n)] real uniqueidentifier 5-17 int smallint, tinyint integer money, smallmoney 4 2, 1 8, 4 1, 0-8 Text and image bit, cursor, sysname, timestamp text, image Unicode text ntext 0-2 GB Character Monetary Special 1-8000 (8000 characters) 2-8000 (1 - 4000 characters) 4-8 4 16 0-2 GB 정밀형 숫자 데이터 유형 : decimal, numeric 근사형 숫자 데이터 형식 : real, float (사용자 정의 데이터 형식 생성 및 삭제하기) 생성 예) EXEC sp_addtype isbn, ‘smallint’, NOT NULL 삭제 예) EXEC sp_droptype isbn 테이블 생성하기 (테이블 생성 및 삭제하기) 생성 예) CREATE TABLE mydb.dbo.emp ( Id INT , name CHAR(10) ) 삭제 예) DROP TABLE emp 테이블이 삭제되면 해당 테이블에 대한 권한 특성 뿐만 아니라 테이 블 정의 및 모든 데이터가 제거된다. 그러나 다른 테이블과의 종속성은 먼저 삭제해야 하므로, sp_depends 시스템 저장 프로시저를 실행해서 종속 성을 살핀다. 또한 삭제된 테이블에 VIEW가 있을 경우 별도로 삭제해야 한다. (IDENTITY 속성 이용하기) 고려사항 : 1. 테이블에 하나의 IDENTITY컬럼만이 허용된다. 2. IDENTITY 컬럼은 갱신될 수 없다. 3. IDENTITY 컬럼은 NULL 값을 허용하지 않는다. 4. IDENTITY 컬럼은 정수, 숫자 혹은 소수형 데이터 형식으로 사용되어야 한다. 5. IDENT_SEED : 초기값 반환, IDENT_INCR : 증가분 6. 세션 동안 마지막으로 삽입된 IDENTITY 값을 추축 하기 위해 @@ IDENTITY 함수를 사용 예) CREAT TABLE class ( student_id int IDENTITY(100,5) NOT NULL, name VARCHAR(16) ) (NEWID 함수 및 uniqueidentifier 데이터 형식 사용하기) 전역적 유일한 식별자를 저장하기 위해 사용된다. (컬럼 추가 및 삭제하기) 추가하기 예) ALTER TABLE sales ADD commission money NULL 삭제하기 예) ALTER TABLE sales DROP COLUMN customer_name 컬럼 변경하기 예) ALTER TABLE stores ALTER COLUMN store_id smallint 제5장 데이터 무결성 구현 유효한 데이터만이 데이터베이스에 저장될 수 있도 록 지켜주는 것이 데이터의 무결성이다. - 무엇을 지켜주는가(종류) 설명 방법들 영역(domain) 특정한 범위의 값만이 와야 한다. CHECK, DEFAULT 실 체 , 개 체 (entity) 행의 존재는 고유해야 한다. PRIMARY KEY, UNIQUE 참 조 (referential) 다른 속성과의 관계를 위반 하지 않아야 한다. FOREIGN KEY - 어떻게 지켜주는가(구현) 설명 방법들 절차적 방법 먼저 정의하고, 다시 바인딩하는 방법으로 절차를 거쳐 정의하고 사용한다. 재활용이 가 능하다. CREATE INDEX, CREATE RULE, CREATE DEFAULT 서술적 방법 테이블을 서술할 때 함 께 서술한다. 재활용이 불가능하지만, 문서화 하기에 매우 쉽고, 읽 기 좋다. CHECK, DEFAULT, PRIMARY KEY, FOERIGN KEY * 제약 사용하기 컬럼 레벨 제약 – 컬럼 하나에 대한 제약 테이블 레벨 제약 – 다중 컬럼에 대해 제약 설정시 컬럼 레벨 제약 예) CREATE TABLE authors (au_id int NOT NULL CONSTRAINT au_PK PRIMARY KEY, firstname char(30) NOT NULL, lastname char(30) NOT NULL, status char(10) NOT NULL CONSTRAINT cat_CHK CHECK(staus IN (‘CONTRACT’, ‘EMPLOYEE’)) ) 테이블 레벨 제약 예) CREATE TABLE employee (emp_num int NOT NULL, lastname char(30) NOT NULL, firstname char(30) NOT NULL, employed datetime NOT NULL, terminated datetime NULL, CONSTRAINT emp_PK PRIMARY KEY(emp_num), CONSTRAINT date_CHK CHECK(employed < terminated) ) 제약을 생성할 때 SQL Server는 시스템이 정하는 복 잡한 이름을 붙이기 때문에, 제약에 대한 이름은 지 정하는 것이 좋다. (나중에 수정등이 용이하려면) (DEFAULT 제약) DEFAULT 제약은 INSERT문에 값이 지정되지 않았을 때 컬럼에 값을 삽입한다. 고려사항 : DEFAULT 제약은 오직 INSERT문에만 적용된다. 하나의 컬럼에는 오직 하나의 DEFAULT 제약만이 정의 된다. IDENTITY 속 성 을 가 진 컬 럼 이 나 데 이 터 유 형 이 timestamp인 컬럼에는 사용될 수 없다. 함수를 사용하여 디폴트 값을 제공할 수 있다. (CHECK 제약) CHECK 제약은 하나 이상의 컬럼에 저장되는 데이터 값 을 제한하는 일을 한다. 고려사항 : INSERT나 UPDATE 문을 실행할 때마다 CHECK 제약은 항 상 데이터를 확인한다. CHECK 제약은 같은 테이블에 있는 다른 컬럼들을 참조 할 수 있다. CHECK 제약은IDENTITY속성을 가진 컬럼 또는 데이터 유형이 timestamp인 컬럼에는 사용될 수 없다. CHECK 제약은 서브 쿼리를 포함할 수 없다. (PRIMARY KEY제약) PRIMARY KEY제약은 테이블에 있는 기본 키를 정의한다. 기본 키 값은 테이블에 있는 각각의 행을 유일하게 식별한다. 고려사항 : . 테이블마다 오직 하나의 PRIMARY KEY제약이 정의될 수 있다. . 기본 키 값은 고유해야 한다. . NULL값은 허용되지 않는다. . 클러스터 또는 비클러스터 인덱스가 생성될 지를 지 정할 수 있다. (UNIQUE 제약) UNIQUE 제약은 한 컬럼 상의 두 개의 행이 동일한 값을 갖지 못하도록 지정한다. 고려사항 : .NULL값을 허용한다. .하나의 테이블에 여러 개의 UNIQUE 제약을 적용할 수 없다. .테이블 상에서 기본 키를 제외한, 고유한 값들로 이루 어진 하나 이상의 컬럼에 UNIQUE 제약을 적용할 수 있다. .UNIQUE 제약은 지정된 하나 또는 여러 개의 컬럼 상에 고유한 인덱스를 생성함으로써 강화된다. .디폴트로 UNIQUE 제약은 클러스터 인덱스 사용이 지정 되어 있지 않는 한 유일한 비클러스터 인덱스를 사용 한다. (FOREIGN KEY 제약) FOREIGN KEY 제약은 동일한 혹은 다른 테이블 상의 PRIMARY KEY제약이나 UNIQUE 제약이 적용된 컬럼들 에 대한 참조를 정의한다. 고려사항 : .FOREIGN KEY 제약은 단일 또는 다중의 컬럼에 대해 참조 무결성을 제공한다. .FOREIGN KEY 제약문에 설정된 컬럼의 개수나 데이터 유형은 REFERENCES절에 있는 데이터 유형과 반드시 일치해야 한다. . FOREIGN KEY 제 약 은 PRIMARY KEY, UNIQUE 제 약 과 는 달리 자동으로 인덱스를 생성하지 않는다. 그렇지만 인텍스 작업을 하기에 유리하다. FOREIGN KEY 제약이 저의되어 있는 테이블에서 터를 수정하려고 할 때, FOREIGN KEY 제약에 참조되는 테이블에 대한 SELECT나 REFERENCES 을 가지고 있어야 한다. 동 일 한 테 이 블 상 에 서 는 FOREIGN KEY 절 REFERENCES절만을 사용한다. 데이 의해 권한 없이 (제약 중지 또는 기존 데이터 검사하지 않기) 기존의 데이터 검사 무시 : WITH NOCHECK 새로운 데이터 검사 무시 : NOCHECK 그러나 어떤 경우에도 중지나 연기 할 수 없는 것들 이 있다. 기본 키, 디폴트, UNIQUE * 디폴트 및 규칙 사용하기 디폴트 및 규칙은 하나 이상의 컬럼 또는 사용자 정 의 데이터 유형에 한정되엇 이들은 한번 정의하고 반복적으로 쓸 수 있는 객체이다. 절차적 방법으로, 객체를 먼저 생성하고 바인트 시킬 수 있다. 디폴트 생성 예) CREATE DEFAULT phon_no_default AS ‘(000)000-0000’ GO EXEC sp_binddefault phone_no_defalut, customer.hone_no_col 규칙 생성 예) CREATE RULE statecode_rule AS @statecode in (‘IA’,’IL’,’KS’,’MO’) GO EXEC sp_bindrule statecode_rule, state_type 디폴트 삭제 예) DROP DEFAULT phon_no_default 규칙 삭제 예) DROP RULE statecode_rule (무결성 강화 객체들을 사용할 때 고려할 사항) 부 하 trigger Stored procedure Rule, Default 제약 자료형, null 사용자정의자료형, 기 능 제8장 다중 테이블 쿼리하기 다중 테이블로부터 데이터 결합하기 조인이란 두 개 이상의 테이블에 쿼리하여 각 테이 블의 행과 컬럼을 통합하여 하나의 결과 세트를 만 들어 내는 작업이다. 즉, 정규화로 나누어진 테이 블 혹은 컬럼들을 다시 모아오는 것이다. ANSI 문법을 사용하도록 하자 (INNER JOIN) 가장 일반적인 JOIN이다. 디폴트임 예) SELECT title, price, pub_name FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id (EquiJOIN) 전체 컬럼을 가져와서 키가 되는 컬럼이 중복되게 하는 것 (CROSS JOIN) – Cartesian Product 양쪽 테이블의 모든 행에 대해 서로 연결되게 한다. 학술적인 의미외엔 의미없다. (OUTER JOIN) INNER JOIN은 두 테이블에 있는 키 값이 일치하는 데 이터만 가져오는 것에 비해 OUTER JOIN은 어느 한 쪽의 데이터를 모두 가져온다. 실무에서 빈번히 사 용된다. 기준인 테이블을 지정해서 LEFT, RIGHT를 적어준다. 예) SELECT t.title_id, qty, title FROM titles t LEFT OUTER JOIN slaes s ON t.title_id = s.title_id (T-SQL 문법 SELECT t.title_id, qty, title FROM titles t, slaes s WHERE t.title_id *= s.title_id ) ANSI 문법과 T-SQL 문법의 차이 팔린 적이 없는 책만 보고싶다면…? SELECT t.title_id, qty, title FROM titles t LEFT OUTER JOIN sales s ON t.title_id = s.title_id WHERE qty IS NULL SELECT t.title_id, qty, title FROM titles t ,sales s WHERE t.tile_id *= s.title_id AND qty IS NULL 결과는 전혀 다르게 나온다. 이유는 WHERE절의 조 건이 JOIN 의 조건인지 WHERE의 조건인지 명확하지 않기 때문이다. 따라서 ANSI 문법을 사용해야 한다. (SELF JOIN) 자기 자신을 다시 조인하는 경우이다. SELECT * FROM titleauthor ORDER BY title_id, au_ord 작가가 두 명 이상인 책 목록을 보고 싶다고 할 경우, 다음과 같이 자신을 조인할 수 있다. SELECT t1.title_id, t1.au_id, t2.au_id FROM titleauthor t1, titleauthor t2 셀프 조인은 반드시 알리아스를 써야한다. WHERE t1.title_id = t2.title_id AND t1.au_id < t2.au_id ORDER BY t1.title_id 제9장 고급 쿼리 기법 서브쿼리(SUBQUERY) SELECT 혹은 INSERT, UPDATE, DELETE 문 안에 들 어가 있는 SELECT를 부질의라고 한다. 다른 말로는 Nested SELECT, 중첩된 SELECT라고도 한다. 특징) 괄호로 묶는다. 부 질의만 수행해도 수행된다 바깥 질의는 안쪽 질의의 결과에 의해 수행된다. 즉, 안 쪽 질의가 먼저 수행된 후, 바깥 질의가 수행된다. 예) SELECT title FROM titles WHERE title_id IN ( SELECT title_id FROM sales) 위의 문장은 아래와 같다. SELECT DISTINCT title FROM titles t JOIN sales s ON t.title_id = s.title_id (Correlated subquery) 부 질의와 상관관계 부 질의와의 차이점 부질의는 안쪽 질의만 수행하면 수행되는 반면, 상관 부 질의는 안쪽 질의만 수행하면 문법 오류가 발생한다. 상관 부 질의는 바깥 질의에서 선택된 행이 안 쪽 질의의 WHERE 절에서 다시 참조된다. 그리고 그 결과가 다시 바깥 질의에 영향을 미친다. 예) SELECT stor_name, ‘Title’ = convert(char(30), t.title), qty FROM sales s JOIN title t ON s.title_id = t.title_id JOIN store st ON s.stor_id = st.stor_id WHERE qty = ( SELECT MAX(qty) FROM sales s2 WHERE s2.stor_id = s.stor_id) ORDER BY stor_name, Title sales 테이블에서 각 서점에서 팔린 베스트셀러 의 책번호(제목)과 수량을 가져오는 것 고려사항) 가급적 부 질의와 살관 부 질의는 사용하지 않도록 한다. 이것이 성능을 향상시키는 방법이다. 대용량 의 데이터베이스의 경우 JOIN의 성능이 월등히 빠르 다. (EXIST와 NOT EXIST 키워드) 서브쿼리가 반환하는 행의 존재 유무에 따라 TRUE나 FALSE값을 반환한다. 예) USE Northwind SELECT lastName, employeeId FROM employees e WHERE EXISTS (SELECT * FROM orders WHERE e.employeeId = orders.employeeId AND orderdate = ‘9/5/97’) (UNION) JOIN이 정규화된 테이블을 연결시키기 위해 사용되는 것임에 비해서 UNION은 비정규화된 테이블을 연결시 키기 위해 사용된다. 각각의 테이블에서 똑 같은 컬 럼, 또는 표현식의 값을 가져오는 SELECT문을 각각 작성한 후 전체에 대해 UNION을 하면 된다. 예) SELECT region, count(*) FROM hot_members GROUP BY region UNION SELECT region, count(*) FROM members GROUP BY region (SELECT INTO) SELECT INTO는 현재 있는 테이블의 내용 전체나 일 부를 선택하여 새로운 테이블로 만들 때 사용한다. 새로운 테이블이기 때문에 기존에 있는 테이블에는 데이터를 입력할 수 없다. 또한 SELECT INTO는 임시, 영구 두 종류의 테이블을 만들 수 있다. 만약 영구 테이블을 만들고자 한다면 SELECT INTO/ BULK COPY 옵션이 설정되어야 한다. 옵션 설정 : sp_dboption pubs, ‘select into/bulkcopy’, true 일반테이블 세션임시테 이블 전역임시테 이블 tempdb의 일반테이블 만드는 방법 CREATE TABLE #tablename ##tablenam e CREATE TABLE 사용할 수 있 는 범위 다른 세션에 서도 해당 세션에 서만 다른 세션에 서도 다른 세션에 서도 삭제되는 시 기 DROP TABLE 세션이 끊어질 때 세션이 끊어질 때 SQL 서버가 시작될 때 * 커서(Cursors) 사용하기 커서는 결과 세트를 통해 한 번에 한 옮겨 다니며 데이터를 처리할 수 있게 는 결과 세트에서 현재의 위치나 특정 인터라고 생각하면 된다. 커서는 서버 커서와 클라이언트 커서 뉜다. 행씩 앞 뒤로 해준다. 커서 행에 대한 포 두 가지로 나 커서를 만드는 5단계) 1단계 : DECLARE 커서를 선언한다. INSENSITIVE를 지정하면 커서에서 정의된 데이터는 tempdb로 복사되고 원본이 바뀌어도 바뀐 내용은 반 영되지 않는다. SCROLL을 지정하면 커서는 행을 앞뒤로 마음대로 이 동할 수 있지만, 지정하지 않으면 다음 행만 가져올 수 있다. FOR READ ONLY를 지정하면 읽기 전용으로만 사용할 수 있다. UPDATE을 지정하면 모든 컬럼을 수정 할 수 있고, UPDATE OF 컬럼명을 주면 해당하는 컬럼만 수정할 수 있다. 2 단계 :OPEN DECLARE에서 정의한 커서를 연다. 열고난 후에는, @@CURSOR_ROWS 변수를 사용해서 상태를 검사하는 것 이 좋다. @@CURSOR_ROWS 변수 0 열기에 실패. 커서를 열어 가져온 행이 하나도 없음 양수 숫자만큼의 행을 열었다. -1 다이나믹 커서로 열렸다. 다이나믹 커서는 원본을 가져오는 것이므로 OPEN한 행의 개수는 의미가 없다. 음수 숫자만큼의 행을 비동기적으로 열었다. 실제 커서로 인해 열린 행의 개수는 너무 커서 알수 없고, 단지 현재 열린 행의 개수가 이 값이다. 3 단계 :FETCH OPEN으로 연 커서에서 하나의 행을 가져올 때 사용 한다. PRIOR등을 지정하지 않으면 NEXT로 동작하며, INTO를 사용하면 가져온 데이터를 변수로 저장할 수 있다. FETCH한 후에 @@FETCH_STATUS 값을 살펴보아 오류가 있는지 점검해야한다. 0 성공적으로 가져왔다 -1 실패 -2 그런 행이 이제는 더 이상 커서 데이터 셋에 존재하지 않는다 4 단계 :CLOSE 열었던 커서를 닫는다. 5 단계 :DEALLOCATE 커서가 사용하던 자원을 모두 풀어준다. CLOSE하지 않고 DEALLOCATE하면 자동적으로 CLOSE된다. 예) USE Northwind DECLARE tbl_space CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name OPEN tbl_space DECLARE @tbl_name sysname FETCH NEXT FROM tbl_space INTO @tbl_name WHILE (@@FETCH_STATUS = 0) BEGIN EXEC sp_spaceused @tbl_name FETCH NEXT FROM tbl_space INTO @tbl_name END CLOSE tbl_space DEALLOCATE tbl_space sysobjects 시스템 테이블로부터 northwind 데이터 베이스에 있는 테이블들의 이름을 반환하는 쿼리에 기초한 커서를 생성한다. (커서의 종류) - Dynamic cursor 커서에서 현재 키 값만 tempdb에 저장한다. 원 테이 블에 대한 모든 수정의 결과가 커서에 반영된다. 그 렇지만 성능면에서는 가장 나쁘다. - Keyset cursor 커서 데이터 셋으로부터 모든 키 값을 tempdb에 저 장하고, 한 행이 fetch되면 그 행만 가져온다. 그래 서 커스는 원 테이블의 모든 변경을 반영하지만, 키 값 자체가 삭제된 경우 –2의 @@fetch_status를 리턴 하게 된다. - Static cursor 처음 한 번만 데이터를 가져오고, 그 뒤에는 원 테 이블이 어떻게 바뀌든 개의치 않는다. 따라서 성능 면에서는 가장 빠르다. - Forword only cursor SCROLL 옵션이 없으면 FETCH NEXT밖에는 지원되지 않는다. 서버 커서는 서버의 tempdb에 데이터 셋을 저장하게 되므로 가급적 사용을 피하는 것이 좋다. 데이터 형식 생성하기 (INSERT…SELECT문 사용하기) 이미 존재하는 테이블에 여러 행의 새로운 데이터를 입력하고자 할 때는 INSERT…SELECT 를 사용한다. (참고로, SELECT…INTO는 한 테이블에 있는 데이터 를 가져와서 새로운 테이블에 입력하고자 할 때 사 용한다.) 예) INSERT t1(city, name) VALUES (‘강릉’,’김영진’) 은 하나의 데이터(행)만을 입력하는 것이나 아래와 같이 SELECT문을 쓰게되면 여러 행을 입력할 수 있 다. VALUES 대신에 SELECT 문을 넣는 것이라 생각하 면 된다. 괄호를 넣으면 에러가 발생하므로 주의. INSERT t2 SELECT * FROM t1 GO (DELETE) 테이블에 있는 모든 것을 지우려면 “DELETE 테이블 명 (또는 뷰이름)” 으로 간단히 지울 수 있다. 다 른 테이블을 기반으로 지울 수도 있다. 예) BEGIN TRAN DELETE t2 WHERE id IN ( SELECT id FROM t1 ) SELECT * FROM t1 SELECT * FROM t2 ROLLBACK TRAN -- COMMIT TRAN (TRUNCATE TABLE) 테이블에 있는 모든 데이터를 전부 지울 경우 TRUNCATE TABLE은 신속하게 데이터를 지울 수가 있 다. 이 문장은 실제 데이터를 지우는 것이 아니라, Index Allocation Map(IAM) 이 라 는 곳 에 서 allocation정보만 지우는 것이다. 마치 디스켓을 포맷할 때의 quick format옵션을 사용하는 것과 비 슷하다고 생각하면 된다. (UPDATE) 기본문법 UPDATE SET WHERE 예) titles price = price * 2 title_id = ‘BU1032’ 다른 테이블을 기반으로 수정하기 예) USE Northwind UPDATE Products SET ToDateSales = ToDateSales + od.Quantity FROM Products INNER JOIN [Order Details] od ON Products.ProductID = od.ProductID 상품 테이블에서 각 상품의 누적 판매량을 갱신 하기 위해 조인을 사용한다. USE Northwind UPDATE Products SET ToDateSales = (SELECT SUM(Quantity) FROM [Order Details] od WHERE Products.ProductID = od.ProductID) 상품 테이블에서 각 상품의 누적 판매량을 갱신 하기 위해 계산 함수를 포함하는 상관 섬브쿼리를 이용 !주의 한 UPDATE문은 한 행에 한 번만 UPDATE할 수 있다. 이것은 모든 데이터의 변경 사항이 로그에 기록되 는 SQL 서버에서 로그의 양을 줄여 성능을 높이려 는 의도이다. 제10장 데이터 요약하기 계산 함수 이용하기 평균이나 합계와 같은 요약 값을 계산하는 함수를 계 산 함수라고 한다. (계산 함수 소개) 계산 함수 설명 AVG 모든 값의 평균 COUNT NULL이 아닌 값의 개수 COUNT(*) 테이블이나 그룹 내 행의 개수 MAX 모든 값 중 최대값 MIN 모든 값 중 최소값 SUM 모든 값의 합계 함수 지원되는 자료형 COUNT Unique identifier, text, image, ntext를 제외한 모든 자료형 MAX, MIN Char, varchar, datetime, bit 자료형을 제외한 수치 자료형 SUM, AVG, STDEV, STDEVP, VAR and VARP Bit 자료형을 제외한 모든 수치 자료형 COUNT(*) 함수를 제외한 모든 SQL Server 계산함수는 컬럼에 있는 null 값을 무시한다. 따라서 평균값 계산 등에 주의하여야 한다. * ISNULL(오라클의 NVL) ISNULL(표현식, 널 일때의 값) 예) SELECT AVG(ISNULL(price,0)) FROM pubs..titles -- 1) SELECT AVG(price) FROM pubs..titles -- 2) 둘의 결과는 다르다. 이유는 SELECT SUM(price) / 18 FROM pubs..titles -- 1) SELECT SUM(price) / 16 FROM pubs..titles -- 2) 인 셈이다. * NULLIF NULLIF( 표현식1, 표현식2) 두 표현식이 같으면 널을 리턴하고, 같지 않으면 첫번째 값을 리턴한다. 예) SELECT NULLIF(2,3) SELECT NULLIF(3,3) * COALESCE 여러 개의 표현식 중에서 첫번째로 나타나는 널이 아닌 값을 리턴한다. 예) SELECT COALESCE(NULL, NULL, 1) -- 1을 리턴 SELECT COALESCE(NULL, NULL, NULL) -- 구문 에러 * CASE (오라클의 DECODE) 예) SELECT title_id as ‘책번호’, qty as ‘수량’ ,‘등급’ = CASE WHEN qty >= 50 THEN ‘A’ WHEN qty >= 30 THEN ‘B’ ELSE ‘C’ END GROUP BY 기본 모든 행이 아닌 행 그롭에 대해 요약 값을 만들고 싶을 경우 GROUP BY절을 SELECT문에 포함시킨다. sales 테이블에 기록된 데이터 중 어떤 책의 경우 는 여러 권이 판매된 것도 있다. 그래서 책 제목에 따라 전체 팔린 개수의 합을 알고 싶다면 어떻게 하면 될까? SELECT title_id, ‘sum of qty’ = sum(qty) FROM sales GROUP BY title_id SELECT문에 WHERE절을 사용하듯, GROUP BY에서 어 떤 조건에 맞는 값을 가져오고 싶으면, HAVING을 사용하면 된다. SELECT title_id, ‘sum of qty’ = sum(qty) FROM sales GROUP BY title_id HAVING sum(qty) > = 30 WHERE와 HAVING의 차이 WHERE는 예선 탈락, HAVING은 본선탈락 (GROUP BY ALL) GROUP BY ALL을 사용하면 WHERE절에서 제외된 것이 라고 할지라도 결과에 포함시킬 수 있다. (COMPUTE와 COMPUTE BY) GROUP BY는 그룹에 대한 부분 합계를 볼 수 있어 좋지만, 상세 내역을 볼 수 는 없다. 상세내역과 그룹의 합계를 모두 한꺼번에 보는 방법이 바로 COMPUTE와 COMPUTE BY이다. 예) SELECT type, title_id, price FROM titles ORDER BY type COMPUTE avg(price) BY type ( COMPUTE avg(price) -- 전체 평균을 얻기 위 해서는 COMPUTE만 사용하면 된다.) 규칙: COMPUTE BY를 사용하려면 ORDER BY가 COMPUTE BY 앞에 반드시 와야 하고, 또 ORDER BY에서 지정된 순서대로, 또는 그 일부분의 순서대로 COMPUTE BY 를 사용해야 한다. COMPUTE인 경우는 ORDER BY가 없어도 된다. (ROLLUP과 CUBE) 데이터웨어하우징, 데이터마트, 데이터 마이닝들을 위해 만들어진 키워드 이다. 예) SELECT type, pub_id, AVG(price) FROM titles GROUP BY type, pub_id WITH ROLLUP SELECT pub_id, type, AVG(price) FROM titles GROUP BY pub_id, type WITH CUBE ROLLUP은 오른쪽에서 왼쪽으로 결과를 도출해 낸다. CUBE는 여기까지 진행한 다음 가능한 그 다음 그룹 을 만들어 낸다. 제11장 뷰 구현하기 뷰 소개 뷰의 정의: 뷰란 이미 존재하는 하나 혹은 그 이상의 테이블에서 원하는 데이터만 정확히 가져올 수 있도록 미리 원하 는 컬럼만 모아 가상적으로 만든 테이블을 말한다. 즉, VIEW는 진짜로 존재하는 테이블이 아니라 가상적으 로 존재하는 테이블이다. 뷰를 사용하는 목적 : 사용의 편의성과 보안의 이유 때문에 사용한다. 뷰 생성, 변경, 삭제하기 생성 예) CREATE VIEW viewTitles AS SELECT title_id, price FROM pubs..titles 뷰를 만들 때 지켜야 하는 규칙 : 1. dbo로 로그인한다. 2. 뷰에서 사용할 SQL 문장을 먼저 작성한다. 3. 2의 문장이 제대로 실행되어 결과가 나타나는지 확인한다. 4. 2의 문장 앞에 “CREATE VIEW 이름 AS”를 붙인다. 5. 이 뷰를 사용할 사용자에게 권한을 허락한다. 6. 사용자로 로그인하여 뷰에서 데이터를 가져온다. (뷰의 수정) ALTER VIEW를 사용한다. CREATE 대신에 ALTER를 사 용한다는 것만 빼고 나머지는 똑같다. (뷰의 삭제) DROP VIEW 뷰이름 (뷰에 대한 정보 얻기) sp_helptext 뷰이름 : 볼 수 있다. 뷰를 정의한 소스 코드를 (뷰를 암호화하기) 뷰를 만들거나 고치면서 WITH ENCRYPTION옵션을 주 면 된다. 그렇게 하면 sp_helptext로 뷰의 소스 코 드를 볼 수 없게 된다. sp_depends뷰이름 : 뷰가 어떤 테이블을 참조하는 지 알 수 있다. (뷰에 대한 뷰) 뷰를 만들고, 이 뷰를 근거로 또 다른 뷰를 만들어 도 된다. (32 단계까지 가능하다) 그러나 가능하다 면 테이블을 근거로 뷰를 만들자. (뷰를 통한 데이터 수정) 고려 사항 : VIEW를 통해 입력되지 않는 컬럼에 대한 설정 널 허용, 디폴트 값 지정 등과 같은 컬럼은 뷰 수정시 데이터가 입력되지 않아도 처리된다. VIEW를 통해 계산된 컬럼과 파생된 컬럼의 설정 파생된 컬럼은 수정할 수 없다. VIEW를 통해 여러 테이블을 수정할 때 뷰를 통해서는 한번에 한 테이블만 수정할 수 있다. 꼭 여러 테이블을 수정하여야 한다면, 트랜 잭션을 사용하여야 한다. WITH CHECK OPTION 뷰를 만들거나 수정하면서 이 옵션을 지정하면 뷰에 정의된 바깥 범위로 데이터를 변경시키는 것 은 금지된다. (뷰에서 사용할 수 없는 것) 뷰 를 만 들 때 SELECT INTO, ORDER BY, COMPUTE, COMPUTE BY를 사용할 수 없다. 임시 테이블을 근거로 뷰를 만들 수 없다. 같은 배치 안에서 뷰를 만들고 참조할 수 없다.(GO 로 분리시켜야 한다.) (시스템 뷰) 테이블 제약, 색인 등에 해단 정보를 가져오기 위 해 information_schema뷰를 제공한다. 시 스 템 테 이 블 을 이 용 하 는 방 법 보 다 information_schema뷰의 사용을 권장하는 이유는 ANSI 호환성 때문도 있고, 시스템 테이블의 변경에 영향을 덜 받기 위함도 있다. 예) SELECT * FROM information_schema.check_constraints 제12장 트랜잭션 및 잠금 관리 트랜잭션 관리 (트랜잭션의 개념) 만약 문제가 생기면 전부 되거나 아예 안되도록 (All or Nothing)하는 일의 최소 단위 (트랜잭션의 종류) 암시적 트랜잭션과 명시적 트랜잭션 . 암시적(implicit) 트랜잭션(시스템 트랜잭션) 트랜잭션의 시작과 끝을 시스템이 지정하고 처리한 다 . 문 장 의 앞 과 뒤 에 자 동 으 로 BEGIN TRAN 과 COMMIT TRAN이 붙는다. . 명시적(explicit) 트랜잭션(사용자 트랜잭션) 트랜잭션의 시작과 끝을 사용자가 명시적으로 지정 해야 한다. 예) BEGIN TRAN DELETE sales UPDATE sales SET qty = 0 COMMIT TRAN (모든 수정에 트랜잭션을 걸 때) SET IMPLICIT_TRANSACTIONS ON 옵션을 설명해주면 모든 데이터의 수정 문장 끝에는 사용자가 꼭 COMMIT 또는 ROLLBACK해야만 완전하게 트랜잭션이 끝이 난다. 옵션이 설정되었는지 확인은 DBCC USEROPTIONS로 한 다. (@@TRANCOUNT와 중첩 트랜잭션) T-SQL 확장에서는 트랜잭션을 중첩시킬 수 있다. 그러나, ANSI 92에서는 중첩 트랜잭션이 없다. 값 이 0이면 트랜잭션 밖에 있고, 1이상이면 트랜잭션 진행 중이다. COMMIT은 짝을 찾아 COMMIT 시키지만, ROLLBACK은 전부다 ROLLBACK 시킴 (SAVE TRANSACTION) SAVE TRAN은 지금까지의 트랜잭션을 임시 저장하는 것이다. SAVE TRAN은 @@TRANCOUNT를 증가시키지는 않는다. 트랜잭션과 잠금 잠금(LOCK)이 왜 필요한가? 트랜잭션의 직렬화를 가능하게 하여 오직 한 번에 한 명만이 데이터를 변경할 수 있게한다. 잠금을 걸지 않으면 나타나는 문제 ANSI레벨 문제점 설명 트랜잭션 고립수준 LEVEL 0 Dirty read 더티 페이지, 즉 commit되지 않은 값을 읽어 오도록 한다. 즉, 다른 트랜잭션이 아직 완료하지 않은 엉터리 값을 읽어올 수 있다. READ UNCOMMITTED LEVEL 1 NonRepeatable Read READ COOMMITED LEVEL 2 Phantom Read 같은 값을 다시 가져왔을 때 이전 에 가져온 값과 다르다. 즉, 반복 적으로 읽었을 때 서로 다른 값이 나타난다. SQL Server의 디폴트 상태이다. 어떤 범위에 해당하는 값을 가져 왔을 때 없던 값이 갑자기 나타나 거나, 있던 값이 사라진다. 어떤 문제도 발생하지 않는다 SERIALIZABLE LEVEL 3 REPEATABLE READ 잠금의 특성 종류 언제 걸리나? 언제 풀리나? 특성 공유 잠금 읽기 잠금 SELECT SELECT가 끝나면 바로 풀린다. 다른 잠금과 공유된다. 배타적 잠금은 함께 걸 릴 수 없다. 배타적 잠 금 쓰기 잠금 INSERT, UPDATE, DELETE 트랜잭션이 끝날 때 풀린다. 배타적이라서 다른 잠 금과 함께 걸릴 수 없다. 잠금의 범위 종류 설명 RID 행 번호 (row ID) 클러스터 색인이 없을 경우 한 행에 대한 잠금이 걸릴 때 사용된다. Key 색인의 키 색인에 대해 잠금이 걸릴 때 사용되며 seralizable을 처리하기 위해 사용 된다. page 티이블이 아니라 한 페이지(8KB)를 통째로 잠근다. Extent 8개의 페이지가 모인 익스텐트에 대해 잠금다. 새로운 페이지가 필요할 때 주로 걸린다. Table 전체 테이블에 대해 잠근다. Database 전체 데이터베이스를 잠금다. 리스토어를 할 때 발샐한다. Server 실제로 서버 전체를 잠그는 방법은 없다. 개념적인 것이다. Single user mode로 서버를 시작하면 된다. 공유잠금(S) 배타적 잠금(X) 공유 잠금(S) O X 배타적 잠금(X) X X (트랜잭션과 잠금에 대한 정보 알아내기) sp_lock : 서버에 걸린 모든 잠금에 대한 정보를 보 여준다. Sp_lock@@spid : 실행 시킨 세션에서 걸고 있는 잠금에 대한 정보를 보여준다. EM에서 [Management] – [Current Activity]를 통 해 : 그래픽하게 볼 수 있다. (트랜잭션 고립화 수준 : Transaction Isolation Level) 트랜잭션 고립 수준이란 트랜잭션을 처리할 때 잠금 의 수준을 어떻게 결정할 것 인지를 지정하는 것이 다. 트랜잭션 고립 수준은 네 가지 단계가 있다. 1. READ COMMITTED (level 1) default COMMIT되지 않은 모든 트랜잭션에 대해서는 잠금이 걸리고, 읽어갈 수 없게 된다. 즉, dirty read를 금지하게 된다. 2. READ UNCOMMITTED (level 0) Dirty read를 하겠다고 선언하는 것이다.(no lock) 3. REPEATABLE READ(level 2) 트랜잭션이 끝날 때까지 잠금을 걸도록 해 주어서, 반복적으로 그 값을 다시 읽었을 때도 값은 값이 되 돌려 질 것을 보장한다. 4. SERIALIZABLE SERIALIZABLE에서는 한번 SELECT하여 가져간 모든 데이터에 공유 잠금을 걸 뿐만 아니라, 그 사이에 있는 모든 키 값에 대해서도 잠금을 걸게 된다. 5. SET LOCK_TIMEOUT 트랜잭션 고립화 수준은 아니지만, LOCK_TIMEOUT은 잠금이 걸린 데이터에 대해 처리를 할 때 지정된 시 간만큼 기다린 후 timeout 처리를 하는 명령이다. 세션 수준으로 처리되며, 단위는 ms이다. 필요에 따라 적절히 사용하면 무한정 기다리는 것 (블로킹 blocking)을 피할 수 있어 성능향상에 도움 이 된다. (잠금 힌트 Lock Option) 잠금을 수동으로 지정하는 방법이다. 잠금 힌트는 트랜잭션 고립화 수준보다 우선된다. 꼭 필요한 경우를 제외하고는 SQL 서버가 자동처리 하도록 두는 것이 좋다. 예) Repeatable Read를 처리를 하고자 할 때(SELECT를 수행한 후에도 잠금을 풀지 않을 때) BEGIN TRAN SELECT … FROM pubs (HOLDLOCK) COMMIT TRAN 옵션 설명 NOLOCK SELECT문에만 사용되며 , 잠금 을 걸지 않는다 . READ UNCOMMITTED와 같다. READUNCOMMITTED READCOMMITED REPEATABLE READ SERIALIZABLE HOLDLOCK SERIALIZABLE과 동일하다. ROWLOCK 행 단위 잠금을 건다. PAGLOCK 페이지 단위 잠금을 건다. TABLOCK 테이블 단위 잠금을 건다. TABLOCKX 테이블 단위 배타적 잠금을 건다. UPDLOCK SELELCT문에서 공유 잠금 대신 업데이트 잠금을 건다. 뿐 만 아니라 HOLDLOCK도 함께 걸려 트랜잭션의 끝까지 잠금 을 풀지 않는다. 행 단위 잠금이 걸린 것을 건너 뛴다. READPAST 예) SELECT * FROM a (READPAST) WHERE id BETWEEN 1 AND 10 (블로킹(Blocking)과 데드락(Deadlock)) 블로킹: tx1이 잠금을 걸고 있고, tx2가 같은 데이터에 대해 잠금을 걸려고 대기 중인 상태를 블로킹이라고 한다. 흔히 오해하고 있는 부분으로, 블로킹은 데드 락이 아니고 라이브 락이다. 블로킹 상태는 사용자가 일부러 일으킨 상태이기 때 문에 (예를 들어 commit되지 않은 상태) SQL서버는 자동으로 kill하거나 커밋하지 않는다. 따라서 수시 로 모니터링이 필요하다. SET LOCK_TEIMOUT은 블로킹을 직접 해결하기 위한 방 법은 아니지만, 블로킹 해제를 무한정 기다려야 하는 것은 피할 수 있다. 데드락: 라이브 락의 반대되는 개념으로 둘 이상의 트랜잭션 이 서로 순환적으로 잠금을 일으켜 무한 루프에 빠진 것이다. 2개의 트랜잭션이 자신들이 사용하는 각 객 체에 잠금을 유지하고 있으며넛 서로 상대방의 객체 에도 잠금을 요구할 때 발생한다. SQL 서번의 교착상태의 해결 1. 2. 3. 4. 교착상태 희생자가 된 트랜잭션을 롤백한다. 교착상태 희생자의 어플리케이션에 메시지 번호 1205를 띄워 알려준다. 교착 상태 희생자의 현재 요구를 취소한다. 다른 트랜잭션은 계속 수행한다. 데드락을 최소화하기 위한 방법 1. 2. 3. 4. 5. 같은 방향으로 트랜잭션을 진행시킨다. 트랜잭션을 짧게가져간다. 주기적인 모니터링 SET LOCK_TIMEOUT, SET옵션 사용 READ UNCOMMITTED 고립 수준을 적절하게 사용 (분산 트랜잭션과 DTC) 분산 데이터를 하나의 트랜잭션으로 처리하려면 BEGIN DISTRIBUTED TRAN SERVER1에서의 작업 SERVER2에서의 작업 SERVER1에서의 작업 COMMIT TRAN 이 트랜잭션 동안 양쪽 서버의 해당 페이지가 모든 잠금이 되므로, 어느 한 쪽 서버의 해당 페이지가 꺼 져있다면 다른 쪽 서버도 롤 백 될때까지 부하가 심 하게 걸린다. 또한 다른 서버에서 변경하려는 페이지 가 잠겨 있을 때도 마찬가지이다. 따라서 양쪽 모두 수정하는 상황이 아니라, 어느 한쪽 서버에서만 수정 되고 다른 쪽 서버는 데이터를 받기만 하는 상황이라 면 복제를 사용하거나, 트랜잭션이 꼭 필요한 상황이 아니라면 그냥 원격 스토어드 프로시저 정도로 구현 하는 것이 보다 나은 방법이다. 제6장 인덱스 계획 및 생성 인덱스의 기본 개념 인덱스은 데이터를 찾을 때 빨리 찾기 위해서 사용 한다. 인덱스가 없다면 특정한 겂을 찾기 위해 모 든 데이터 페이지를 다 뒤져야 한다. 이것을 table scan이라고 한다. 인덱스가 찾고자 하는 컬럼이나 표현식에 대해 존재하고, 인덱스를 사용하는 것이 더 효과적이라면, SQL서버는 모든 페이지를 뒤지지 않고 인덱스 페이지를 찾아서 쉽게 데이터를 가져 온다. 이것을 Index Serch라고 한다. (인덱스의 유용성) 데이터 정렬, 그룹단위로 계산함수 사용, 유일성 검사 • 인덱스 설정시 고려사항 .만드는데 시간이 걸린다. .만드는데 많은 공간이 필요하고, 만들고 난 후에도 추 가적인 공간이 필요하다. .데이터를 수정하는 시간은 오히려 더 많이 걸린다. * . . . . 인덱스를 하지 말아야 할 컬럼 좀처럼 검색되지 않는 컬럼 전체 중 상당 부분을 가져오는 질의에 사용되는 컬럼 유일성, 또는 같은 값이 많은 컬럼들 SELECT 속도보다 데이터의 변경 속도가 훨씬 중요할 때는 되도록 인덱스의 수를 최소화한다. (기본문법) 예) CREATE INDEX mytable_PK ON mytable (id) (인덱스의 종류) 클러스터 인덱스와 넌클러스터 인덱스 인덱스는 B-Tree(Balanced Tree)구조로 되어 있다. 제 일 하위 레벨을 리프 레벨(leaflevel)이라고 부른다 클러스터 인덱스는 1. 물리적인 행의 순서가 인덱스의 순서와 동일하다 2. 인덱스의 맨 마지막 단계인 리프 레벨이 곧 데이터 페이지이다. 3. 오직 하나의 클러스터 인덱스를 가진다. 4. 클러스터 인덱스는 테이블 내에서 행들의 물리적 정렬 순서를 변화시키기 때문에 넌클러스터 인덱스를 생성하기 전에 반드시 클러스터 인덱스를 먼 저 생 성 해야 한다. 5. 클러스터 인덱스 내의 키 값은 고유해야 한다. 인덱스를 생성할 때 UNIQUE 키워드를 사용하지 않 으면 SQL 서버는 중복되는 값을 갖는 행들에 고 유한 내부 식별자를 붙여서 유일성을 암시적으 로 유지한다. 6. 클러스터 인덱스의 평균 크기는 테이블 크기의 약 5% 7. 인덱스를 생성하는 과정에서 SQL 서버는 테이블을 복사하고 정렬하기 위해 데이터베이스 내의 공간 을 임시적으로 사용하게 된다. 인덱스가 만들어 질 때 테이블 크기의 12.배 정도가 작업 공간으로 사용된다. 넌클러스터 인덱스는 1. 물리적인 행의 순서가 인덱스 순서와 동일하지 않다. 2. 리프 레벨은 데이터 페이지가 아니다. 3. SQL 서버의 디폴트이다. 4. 테이블 당 넌클러스터 인덱스는 249개까지 만들 수 있다. 5. 클러스터 인덱스가 테이블에 정의되어 있지 않는 한 SQL 서버는 힙에 데이터 페이지를 유지한다. -> 힙에서의 넌클러스터 인덱스 구조 힙(클러스터 인덱스가 없는 테이블)의 최상위에서 넌 클러스터 인덱스가 생성될 때 SQL 서버는 해당 데이 터 페이지 내의 행을 가리키는 인덱스 페이지 내의 행 식별자를 사용한다. 행 식별자는 데이터 위치 정 보를 저장하고 있다. 힙은 IAM(Index Allocation Maps) 페이지를 사용하여 유지가 된다. IAM페이지는 힙에 할당된 익스텐트의 주소를 담고 있다. IAM페이지 안에 있는 데이터 페이 지와 행들은 특정한 순서가 없으며 서로 링크로 연결 되어 있지도 않다. 데이터 페이지들 간의 유일한 논 리적 연결은 IAM페이지 내에 기록되어 있는 것 뿐이 다. 페이지들이 링크로 연결되어 있지 않기 때문에 힙에 서 페이지 분할은 절대 일어나지 않는다. -> 클러스터 인덱스를 가진 테이블 상에서의 넌클러 스터 인덱스의 구조 데이터 페이지의 행들을 가리키기 위해 인덱스 페이 지 내의 클러스터 인덱스 키를 사용한다. DROP INDEX 문을 사용해서 기존의 클러스터 인덱스를 삭제한 경 우, 클러스터 인덱스를 생성한 경우, DROP_EXISTING 옵션을 사용하여 클러스터 인덱스 컬럼의 정의를 변 경했을 경우, 넌클러스터 인덱스는 자동으로 재구성 된다. (참고 : 책에서의 차례가 클러스터 인덱스, 찾아보기 가 넌클러스터 인덱스) 클러스터 인덱스 넌클러스터 인덱스 인덱스 만든 후 크기 증가 테이블의 1~5% 10~20% 하나의 값을 주고 찾을 때 상당히 빠르다. 클러스터 보다 아주 약간 느리 다.(거의 차이가 없다) 여러 값(범위)를 주고 찾 을 때 여전히 빠르다. 일반적으로 테이블 스캔보다 느 리다. 데이터의 수정이 일어날 때 제법 부하가 걸린다. 클러스터 보다 부하가 적다 Covered Query의 경우 별로 도움되지 않는 다. 매우 도움된다. (인덱스 생성 및 삭제하기) PRIMARY KEY제약 또는 UNIQUE 제약이 테이블에 만들 어지면 SQL 서버는 자동으로 인덱스를 생성한다. (인덱스 생성 및 삭제하기) PRIMARY KEY제약 또는 UNIQUE 제약이 테이블에 만들어 지면 SQL 서버는 자동으로 인덱스를 생성한다. DROP INDEX문 사용하기 .PRIMARY KEY 또는 UNIQUE 제약에 의해 생성된 인덱스 에 대해서는 DROP INDEX문을 사용할 수 없다. 먼 저 제약을 삭제한 후에야 이런 인덱스는 삭제가 가능하다. .테이블을 삭제할 때 해당 테이블에 대한 모든 인덱스 또한 삭제된다. .클러스터 인덱스를 삭제할 때 해당 테이블의 모든 넌클러스터 인덱스는 자동으로 재구성된다. .DROP INDEX문은 시스템 테이블에서 사용할 수 없다. (유일 인덱스와 중복 허용 인덱스) 유일 인덱스는 중복된 키 값을 허용하지 않는다. CREATE INDEX UNIQUE, UNIQUE CONSTRAINT로 만들 수 있다. CREATE INDEX문 PRIMARY KEY CONSTRAINT 클러스터와 넌클러스터 지 정이 없으면 디폴트로 넌클러스터 인덱스 클러스터 인덱스 UNIQUE 지정이 없으면 디 폴트로 중복허용 중복허용 않음. 아예 중복 허용으로 설정할 수 없음 NULL 허용 여부 허용 그 러 나 , UNIQUE 로 지정했을 때는 단 한 행만이 NULL을 사용할 수 있다. 허용하지 않음 기본적으로 기본 키는 클러스터 인덱스에 유일 인덱 스가 디폴트이다. 중복을 허용하는 넌클러스터 인덱스 생성 예) CREATE INDEX mytable_emp_id ON mydb.dbo.mytable(name) 중복을 허용하지 않는 클러스터 인덱스 생성 예) CREATE UNIQUE CLUSTERED INDEX mytable_emp_id ON mydb.dbo.mytable(name) (단일 커럶 인덱스와 복합 컬럼 인덱스) 한 컬럼에만 인덱스를 만든 것을 단일 컬럼 인덱스, 둘 이상의 컬럼에 인덱스를 만드는 것을 복합 컬럼 인 덱스 또는 복합 인덱스이라고 한다. (인덱스 옵션 생성하기) - FILLFACTOR 옵션 사용하기 FILLFACTOR 옵션을 사용해서 클러스터나 넌클러스터 인덱스를 가지고 있는 테이블에서 INSERT 와 UPDATE 문의 성능을 최적화할 수 있다. FILLFACTOR 옵션은 얼마나 많은 리프 레벨 페이지를 채울 것인지를 퍼센트 단위로 설정한다. 인덱슥가 생 성되거나 재구성될 때 FILLFACTOR 옵션이 적용된다. FILLFACTOR값이 낮으면, 페이지 분할이 줄어들기 때문 에 UPDATE 와 INSERT 문 의 성 능 이 향 상 된 다 . 낮 은 FILLFACTOR값은 온라인 트랜잭션 처리(OLTP) 환경에 적합하다. 높은 FILLFACTOR값은 더 적은 수의 페이지에서 행을 읽어 들이기 때문에 쿼리 또는 읽기 성능을 향상시킨 다. 높은 FILLFACTOR 값은 DSS 환경에 적합하다. - PAD_INDEX 옵션 사용하기 PAD_INDEX 옵션은 비리프 레벨의 인덱스 페이지에 빈 공간을 남기도록 설정한다. FILLFACTOR 옵션의 퍼센티 지 값에 의해 PAD_INDEX의 퍼센티지 값이 결정되기 때 문에 PAD_INDEX옵션은 FILLFACTOR가 지정된 때에만 사 용해야 한다. FILLFACTOR의 퍼센티지 리프 레벨 의 페이지 PAD_INDEX 설정 여부 비리프 페이지 0 ( 설 정 안 됨 , 디폴트) 완전히 채 움 아니오 인덱스 엔트리 하나가 들어 갈 공간을 비움 예 인덱스 엔트리 하나가 들어 갈 공간을 비움 1~99 설정된 페이지까 지 채움 아니오 인덱스 엔트리 하나가 들어 갈 공간을 비움 예 설정된 퍼센티지까지 채움 완전히 채 움 아니오 완전히 채움 예 완전히 채움 100 제7장 인덱스 관리하기 인덱스 유지하기 인덱스를 생성한 후에는 최적의 성능을 보장하기 위해서 그것을 관리해야 한다. 데이터가 수정될 때 페이지 분할에 의해 데이터가 점점 조각화된다. Fragmented 상태는 쿼리 과정의 속도를 저하시킨다. SQL 서버는 조각 상태를 감시하기 위한 DBCC SHOWCONTIG문을 제공한다. 조각 상태가 성능에 영 향을 미칠 때는 인덱스를 재구성해야 한다. 인덱스 재구성하는 방법) 1. CREATE INDEX문을 DROP_EXISTRING옵션과 함께 사용 2. DBCC DBREINDEX문을 사용하여 3. 먼저 인덱스를 삭제하고 나서 다시 생성 DBCC SHOWCONTIG문은 지정된 테이블의 데이터와 인덱 스에 대한 조각 상태 정보를 보여준다. 과도하게 수정 되는 테이블, 임포트된 데이터를 담고 있는 테이블, 또는 낮은 쿼리 성능을 유발하는 것으로 보이는 테이 블에서 사용한다. 통계 설명 스캔된 페이지(Pages Scanned) 테이블 또는 인덱스의 페이지 개수 스캔된 익스텐트(Extents Scanned) 테이블 또는 인덱스의 익스텐트 개수 익스텐트 스위치(Extent Switches) 테이블 또는 인덱스의 페이지들을 가로질러 검색하는 동안 DBCC문이 하나의 익스텐트에 서 다른 익스텐트로 이동해야 하는 횟수 익스텐트 당 평균 페이지 수 (Avg.Pages per Extent) 페이지 체인 내의 익스텐트 당 페이지 개수 스캔 밀도[최상치:실제치] (Scan Density[Best Count:Actual Count]) 실제치는 DBCC문이 테이블 또는 인덱스를 가 로질러 검색할 때 발생한 익스텐트 스위치의 실제 개수이다. 최상치는 모든 페이지가 순차적으로 연결되고 최소 개수의 익스텐트 할당되었을 때 익스텐트 스위치의 이상적인 개수이다. 스캔 밀도는 실제치의 퍼센트 단위의 최상치이다. 만약 조각 상태가 하나도 없다면 최고치 = 실제치가 되고 스캔 밀도는 100%가 된다. 논리적 스캔 조각 상태 (Logical Scan Fragmentation) 인덱스의 리프 페이지를 스캔할 때 순 서에서 벗어난 페이지들의 퍼센티지. 적을수록 좋다. 익스텐트 스캔 조각 상태 (Extent Scan Fragmentation) 인덱스의 리프 페이지를 검색할 때 익 스텐트의 퍼센티지는 물리적으로 연속 적이지 않다. 숫자가 낮을수록 좋다. 페이지 당 남은 바이트 수 (Avg. Bytes Free per Page[jjb1]) 스캔되는 페이지에 남은 바이트의 평균. 숫자가 높을수록 페이지에 남은 공간이 많다. 낮을수록 좋다. 평균 페이지 밀도 (Avg. Pages Density (fully)) 평균 페이지 밀도 값은 행의 크기와 관 련이 있기 때문에 얼마나 페이지가 채 워졌는지 알 수 있는 정확한 지표가 된 다. 퍼센트가 높을수록 좋다. 예) member 테이블의 테이블 ID에 액세스하는 문을 실 행한다. USE library DECLARE @table_id int SET @table_id = OBJECT_ID(‘member’) DBCC SHOWCONTIG (@table_id) 통계 정보 인덱스가 있는 컬럼에 대해(복합 인덱스의 경우 첫 번 째 컬럼만) 통계 정보는 자동으로 생성되고 관리된다. CREATE STATICS문을 써서 SQL 서버로 하여금 다른 컬 럼에 대해서도 통계를 생성하게 할 수 있다. 생성 예) USE library CREATE STATISTICS stats_outdate ON loanhist (out_date) 삭제 예) DROP STATISTICS loanhist.stats_outdate 통계 정보 * 통계 갱신하기 빈 테이블에 인덱스를 생성하고 테이블을 나중에 채 우는 경우, TRUNCATE TABLE문을 실행하고 나중에 그 테이블을 다시 채우는 경우, 좀더 자세한 인덱스 통 계가 필요한 경우 등에는 UPDATE STATISTICS문을 사 용하여 통계 정보를 수동으로 갱신할 수 있다. 또한, 통계의 자동 갱신 사용금지로 만들기 위해서 는 (인덱스 통계를 정적으로 만들기) CREATE INDEX 문에 STATISTICS_NORECOMPUTE키워드를 설정한다. (인덱스 분석) 인 덱 스 와 쿼 리 성 능 을 분 석 하 기 위 하 여 SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL, SET STATISTICS IO문을 실행할 수 있다. (성능에 관하여) 1. SHOWPLAN SET STATISTICS IO ON : 페이지I/O 가 얼마나 발생했는지 보여준다. SET STATISTICS TIME ON : 실행하는데 걸린 시간을 보여준다. SET NOEXEC ON : 실제 쿼리를 실행하지 않고 문법 검사와 OPTIMIZER 작업만 한다. 2. OPTIMIZER HINT : 사용자가 임의의 방식을 옵티 마이저에게 지시할 수 있게 한다. 예) SELECT title_id, price FROM titles (index = 0) 테이블 스캔의 의미 WHERE title_id = ‘BU1032’ 3. POINT SEARCH : 한 행을 찾아 오는 것 4. RANGE SEARCH : 범위 안에 드는 모든 값을 가져 오는 것 5. 모든 기본 키는 클러스터 색인이다? 6. COVERED QUERY : 질의에 나타난 모든 컬럼이, 넌 클러스터 색인에 복합 키로 포함되어 있는 경우 를 covered query, covered index라고 말한다. 7. FILLFACTOR : 데이터 삽입과 숮ㅇ에 대한 속도를 향상시키기 위해 약간의 빈 공간을 두기 위한 옵션이다. 그러나 FILLFACTOR를 사용하면 데이 터의 변경 속도는 빨라지지만, select속도는 오 히려 늦어진다. 8. WITH DROP EXISTING : SQL 서버에서는 클러스터 색인을 다시 만들면 조각 모임이 된다. 예) CREATE INDEX indexName ON tableName (ColumnName) WITH DROP EXISTING DBCC DBREINDEX(authors, ‘’, 70) 9. UPDATE STATISTICS : 통계치를 수동으로 갱신할 때 10. DBCC(DataBase Consistency Checker) DBCC CHECKTABLE DBCC CHECKDB DBCC SHOWCONTIG DBCC SHOW_STATISTICS 11. 익스텐트와 페이지 테이터가 저장 될 때는 데이터 영역에 저장된다. (데이터베이스를 만들 때는 데이터 파일과 로그 파일을 지정한다.) 테이터 파일은 그 내부에 익 스텐트와 페이지로 구성되어 있다. (페이지는 8KB이다.) 이런 페이지가 연속으로 여덟 개가 모인 것을 익스텐트라고 한다. 익스텐트는 테이블과 색인 의 할당 단위가 된다. 익스텐트는 사용도중 빈 공간이 생길 수 있다. 같은 테이블의 테이터만 오기 때문에 한 페이지만 사용하고 나머지 일곱 페이지는 비어 있을 수 있다. 이런 것을 보여주 는 것이 Scan Density이다. 12. 스토어드 프로시저의 RECOMPILE 스토어드 프로시저는 처음 실행 이후로는, 컴파 일 된 것을 캐쉬에서 가져오기 때문에 빠르다. 그러나, 자료를 가져오는 경우에 따라서는 이미 컴파일 된 작업경로가 아닌 새로운 작업경로가 필요할 때가 있다. 이를 위해 사용하는 것이 RECOMPILE이다. 프로시저가 실행될 때 마다 재 컴파일 하는 예) CREATE PROC procByID … WITH RECOMPILE 실행할 때만 재 컴파일 하는 예) EXEC procBYID 10000 WITH RECOMPILE Table1과 관련된 모든 프로시저가 한번만 다시 컴파일 되는 예) sp_recompile table1