### 백업 본 설정 및 복구(*** MDF  파일 삭제 된 후 마지막 Tailog--트랜잭션 로그 백업 필히 작업 후 복구 작업 시작)

** 복구 모델 전체 / 단순(트랜잭션 로그는 백업 안됨--noarchive mode)

drop database zDB

go

create database zDB

go

use zdb

go

create table T1 (id int)

go

-- 0. 백업폴더 생성

insert into T1 values (10),(20),(30)

select * from T1

go

-- 1. 전체백업

insert into T1 values (40),(50),(60)

select * from T1

go

-- 2. 차등백업

insert into T1 values (70),(80)

select * from T1

go

-- 3. 로그백업

insert into T1 values (90),(100)

select * from T1

go

-- 4. 차등백업

insert into T1 values (200),(300)

select * from T1

go

-- 장애

 - 데이터베이스 오프라인

 alter database zDB set offline

 - zDB.mdf 삭제

 - 데이터베이스 온라인

alter database zDB set online

-- Tail-Log 백업 ********

 backup log zDB to disk='c:\drive\zDB_last3.bak'

with no_truncate, init --(비상시백업,백업매체초기화)

-- 복원

-- 1. 기존 백업본을 통한 복원 (옵션: With norecovery)

--> 복원중 표시되고, 뒤에 복원을 또 할 수 있음.

-- 2. Tail-Log 백업본을 통한 복원 (옵션: With recovery)

--> 복원 완료를 의미, 뒤에 복원을 할 수 없음.

-- 확인

use zDB

select * from T1

## Shrink 테스트

ShrinkLog.sql

 ### 잠금의 단위

 - RID : 테이블에 있는 한 행을 잠그기 위한 행 ID입니다. 

 - 키(Key) : 인덱스에 있는 행 잠금입니다. 

 - 페이지(Page) : 8킬로바이트(KB) 데이터 페이지 또는 인덱스 페이지입니다. 

 - 익스텐트(Extent) : 인접한 여덟 개의 데이터 페이지 또는 인덱스 페이지 그룹입니다. 

 - 테이블(Table) : 모든 데이터와 인덱스가 포함된 전체 테이블입니다.  

 - 데이터베이스(DB) : 데이터베이스입니다. 

 ### 잠금의 종류

 - 공유(S) : (Shared Lock) SELECT 문처럼 데이터를 변경하거나 

업데이트하지 않는 작업(읽기 전용 작업)에 사용합니다. 

공유 잠금을 형성하여 다른 트랜잭션에 의해 현재의 데이터가 

데이터가 변경되지 못하도록 합니다.  

 - 업데이트(U) : (Update Lock) 데이터를 UPDATE 하기 위해 

Exclusive Lock을 형성하기 전에 미리 걸어주는 잠금입니다. 

즉, "곧 변경할테니까 접근하지 마라!" 라는 의미로 보시면 됩니다.  

 - 단독(X) : (Exclusive Lock)  INSERT, UPDATE, DELETE와 같은 

데이터 수정 작업에 사용합니다. 

여러 개의 업데이트 작업이 같은 리소스에 대해 동시에 

이루어지지 못하게 합니다.

데이터 읽기 조차도 못하게 됩니다. 

 - 내재 : (Intent Lock)  잠금 계층 구조를 만드는 데 사용합니다. 

내재된 잠금의 종류에는 내재된 공유(IS) 잠금, 

내재된 단독(IX) 잠금, 공유 및 내재된 단독(SIX) 잠금이 있습니다. 

예를 들어 레코드에 공유잠금(S)이 형성되어 있으면 

이 레코드를 포함하는 테이블은 내재된 공유잠금(IS)를 형성하여 

"레코드에 공유 잠금이 형성되어 있으니 너희들은 나에게 

단독 잠금(Exclusive Lock) 걸면 안돼!" 라고 알려주는 것입니다. 

 - 스키마 : (Schema Lock)  테이블의 스키마에 종속되는 작업이 실행될 때 사용합니다. 

스키마 잠금의 종류에는 스키마 수정(Sch-M)과 스키마 안전성(Sch-S) 

두 가지가 있습니다.  

 - 대량 업데이트(BU) : (Bulk Update Lock) 데이터를 테이블로 

대량 복사하는 경우와 TABLOCK 참고가 지정된 경우 사용합니다. 

 ### 고립화 수준(Isolation Level) 

 - 아직 UPDATE가 완료 되지 않았기 때문에 UPDATE 되기전 

잘못된 데이터를 읽을 위험이 있으므로 이를 방지하기 위함입니다. 

하지만 원한다면 UPDATE 작업이 완료되지 않은 상태의 데이터를 

다른 세션에서 읽을 수 있도록 할 수 있습니다.

 - 트랜잭션의 고립화 수준이란 여러개의 트랜잭션이 동시에 수행 될 때 

잠금 충돌이 발생 하면 이를 어떻게 처리할지에 대한 문제라고 보시면 됩니다. 

고립화 수준을 어떻게 하느냐에 따라 다른 결과를 볼수 있게 됩니다.

 - 두 세션의 잠금 현상을 이용해보도록 하겠습니다. 

한 세션이 UPDATE 중일 다른 세션은 이 UPDATE 작업이 완료 될때까지 

그 데이터를 대상으로 검색을 할 수가 없었습니다. 

왜냐하면 현재 진행중인 UPDATE 작업이 COMMIT 될 지 

ROLLBACK 될 지 아직 모르기 때문에 완벽하지 않은 데이터를 보는것을 

방지하도록 하기 위해서입니다. 

하지만 다음과 같이 트랜잭션의 고립화 수준을 별도로 지정하게 되면 

결과는 달라집니다.

  --> SET TRANSACTION ISOLATION LEVEL 

   READ UNCOMMITTED

   GO 

 ### 고립화 수준(Isolation Level)의 종류

 - 고립화 수준은 낮을 수록 성능은 좋아집니다. 

하지만 데이터의 무결성은 깨어질 가능성이 높습니다. 

트랜잭션의 고립화 수준에는 다음과 같이 4가지 종류가 있습니다.

 1) READ UNCOMMITTED

  : Trouble

 - DirtyRead

 - NonRepeatable Read

 - Phantom Read

고립화 수준에서 가장 낮은 수준입니다. 

즉, DIrty Page를 읽어오게 되는 것입니다. 

2) READ COMMITTED (Default)

: Trouble

 - NonRepeatable Read

 - Phantom Read

  Dirty Page를 읽는, 즉 DIrty Read를 방지합니다. 

  별도의 고립화 수준을 설정하지 않았거나 

READ COMMITTED를 설정하게 되면 

  COMMIT 하지 않은 트랜잭션이 이용한 자원에 대해 

트랜잭션이 완료되어야 

(= 잠금이 해제되어야) 데이터를 읽을 수 있습니다.

3) REPEATABLE READ

: Trouble

 - Phantom Read

SQL Server는 기본적으로 같은 값을 반복적으로 가져 올 경우 이전 값과 

항상 같다는 것을 보장하지 못합니다. 

이러한 것을 Nonrepeatable Read라고 하는데 이러한 문제는 

한 트랜잭션 내에서 같은 SQL문을 여러번 사용할 경우 내부적으로 

공유 잠금(S)를 트랜잭션 종료시까지 유지하지 않기 때문입니다. 

REPEATABLE READ 고립화 수준은 이러한 문제를 해결할 수 있는 고립화 수준입니다. 

트랜잭션이 완료될 때까지 공유 잠금을 유지하여 여러번 같은 데이터를 읽어도 

항상 같은 값이 얻어지도록 합니다.

주의해야할 사항은 REPEATABLE READ가 설정되면 

원래 읽었던 데이터에 대하여서는 항상 같은 값이 유지 되지만 

새로 추가되는 데이터를 막을 수는 없습니다. 

예를 들어 특정 조건에 만족한 레코드가 5개였으면 

이 5개의 레코드는 언제든 같은 값을 보이게 되지만 

다른 트랜잭션에 의해 이 조건을 만족하는 새로운 레코드가 추가되면 

원래 없었던 새로운 레코드가 보이게 됩니다. 

이러한 것을 Pantom Read라고 합니다.

4) SERIALIZABLE

: Trouble

 - Nothing

가장 높은 고립화 수준으로 REPEATABLE READ 

고립화 수준이 막지 못하는 Phantom Read까지 방지해줍니다. 

완벽한 데이터베이스의 일관성은 유지해 주지만 

잠금을 유지하기 위하여 오버헤드는 증가하게 됩니다.

### 잠금크기와 관련된 잠금힌트

1) ROWLock : 공유 행 잠금

2) PAGLock : 공유 페이지 잠금

3) TABLock : 공유 테이블 잠금

4) TABLockX : 단독(Exclusive) 테이블 잠금

### 그밖의 잠금힌트

1) XLock : 단독(Exclusive)잠금 지정

2) UpdLock : 업데이트 잠금 지정

3) ReadPast : 잠긴 행은 건너뛰고 잠기지 않는 행들만 접근 

-> Read Commited 격리수준에서만 적용

### 커서(Cursor)에 대한 요약 정리

커서란?

- 데이터 처리는 집합단위로 이루어지기도 하지만 조건에 따라서는 행 단위로 처리하기도 한다.

- 행 단위의 데이터 처리시 커서를 사용한다.

커서의 장점

- 데이터를 행 단위로 처리할 수 있음.

커서의 단점

- 데이터 처리 속도가 느려짐.(SQL문에 비해 느림)

1.1 커서의 종류

- 서버커서(Server Cursor), 클라이언트 커서(Client Cursor)

서버커서(Server Cursor)

- T-SQL 커서와 API 커서로 나뉨

T-SQL 커서 란?

- 결과 집합을 만드는 T-SQL스크립트, 트리거, 저장 프로시저에서 사용

- 변수로 한 열을 할당받는다.(변수의 크기는 열의 데이터 타입과 데이터를 모두 받을 수 있을 만큼 선언)

API 커서란?

- OLEDB, ODBC, ADO를 이용하여 SQL 문의 결과 집합에서 커서 매핑.

클라이언트 커서란?

- ODBC에서 지원하는 커서, 결과 집합을 클라이언트 캐쉬에 저장(읽기전용)

1.2 커서의 작업

- DECLARE를 이용한 커서 선언

- OPEN을 이용한 커서 열기

- FETCH를 이용한 데이터 불러오기

- CLOSE를 이용한 커서 닫기

- DEALLOCATE를 사용ㅇ한 커서 선언 제거

1.3 커서의 ANSI 문법

- Declare 커서 이름 [ INSENSITIVE ] [ SCROLL ] CURSOR

   FOR SELECT 구문

   [ FOR { READONLY | UPDATE [ OF 컬럼명 [......N]] } ]

- INSENSITIVE : 커서에서 사용할 데이터를 임시로 복사해 주는 커서를 정의

                커서에서 정의된 데이터는 tempDB에 저장됨.

                잠금이 생김, 동시성 저하.

- SCROLL : 모든 인출 옵션 사용이 가능

           인출 옵션(FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE)

           인출 옵션이란? 데이터를 불러올 행을 이동하는 방식

- SELECT 구문 : 일반적인 select 구문 형식을 따름

- READ ONLY : 커서를 통한 데이터 변경 및 삭제를 방지, DELETE, UPDATE 기능을 무시.

1.4 커서의 T-SQL 문법

- DECLARE 커서 이름 CURSOR [ LOCAL | GLOBAL ]

 [ FORWARD_ONLY | SCROLL ]

 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

 [ TYPE_WARING ]

 FOR SELECT 구문

  [ FOR UPDATE [ OF 컬럼명 [ ,......N]]]

- LOCAL: 커서의 범위를 로컬로 지정(로컬변수)

- GLOBAL: 커서의 범위를 전역을 지정(전역변수)

- FORWARD_ONLY: "전진만 있되 후퇴는 없도다.", 유일한 명령 옵션은 FETCH_NEXT

- SCROLL: 모든 인출 옵션(FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE)를 사용하도록 지정

- STATIC: 수정이 안됨(임시커서)

- KEYSET: 커서에 포함되는 행과 멤버와 순서가 고정되도록 지정.

- DYNAMIC: 인출할 때 마다 행의 데이터 값과 순서, 멤버가 변경됨, 동적 커서

- FAST_FORWARD: 성능 최적화가 설정된 FORWARD_ONLY, READ_ONLY 커서를 지정.

- READ_ONLY: 데이터에 대한 변경 및 삭제 방지, UPDATE, DELETE 구문의 WHERE CURRENT OF 절에서 이키를 사용할 수 없음

- SCROLL_LOCKS: 위치 지정 업데이트나 삭제가 성공하도록 지정

-- Declare 구문지정

DECLARE YearPlan_Cur CURSOR FOR

  SELECT CustCode, WkCnt, DeptCode, Week, Day, SMethodYN, DaN

  FROM RM_YearPlan

  WHERE WkCnt = '1'

-- OPEN 구문

OPEN YearPlan_Cur

-- FETCH 구문

FETCH NEXT FROM YearPlan_Cur

WHILE @@FETCH_STATUS = 0

 BEGIN

  FETCH NEXT FROM YearPlan_Cur

 END

-- CLOSE 구문

CLOSE YearPlan_Cur

-- 커서 해제

DEALLOCATE YearPlan_Cur

Cursor.sql

### INDEX 생성 시

1) 조회 빈도

2) 데이터 입력이 빈번한가

3) 범위형 쿼리(CI 가 좋음)

4) 포인트 쿼리(CI/NCI 관게 없음)

5) 사이즈

### INDEX  활용

 1)업무분석

 - 조회컬럼

 - 정렬컬럼

 - 컬럼에 대한 프로세스 

 2) 인덱스가 걸리는 컬럼의 사이즈

 - 컬럼의 사이즈 -> 데이터페이지 -> CI의 인덱스 페이지 (CI의 키값)

-> NCI 의 인덱스 페이지

 3) 한 테이블의 컬럼의 숫자를 고민해보세요 

 - Lock, Dead Lock

 4) 복합인덱스 => 검색 데이터 사용량 빈도 ?

 - '풍물'동아리에 있는 학생의 수 => 동아리 코드 + 학생번호

 - '홍길동'학생이 가입한 동아리의 수 => 학생번호 + 동아리코드

 5) 커버드 쿼리의 이용을 고민해보자

 - 커버드 쿼리 : 조회의 조건과 대상 컬럼이 인덱스에 있고,

그 인덱스로 질의를 하는 것

 - Size와 페이지분할

<- 복합인덱스(4)

<- 커버드쿼리(2+2)

 6) 파일그룹을 이용하자

 - 파일그룹을 이용해서 분한테이블/분할인덱스를 만듦으로써

   한개의 테이블에 걸리는 인덱스의 높이/깊이를 

   감소시키면서 성능을 향상시킨다

 7) 인덱스 활용쿼리 디자인

 - 인덱스가 정의된 열을 가공하지 말아야 한다

 - Like 키워드의 검색문자열 앞에 와일드 카드 사용을 피한다

### Index 생성 고려사항

 1. 조회가 빈번하게 일어나지 않는 테이블에 Index 생성하지말것

 2. Where 절 뒤에 오는 컬럼을 대상으로 Index를 고려해야 함

 3. 밀도가 높은 컬럼은 Index를 걸지 말것 예)성별[남,녀]

 4. Index를 재구성, 재작성을 통해서 재정렬을 할수 있음

- 재구성 (Re-Organization) : 리모델링

- 재작성 (Re-Build)   : 재건축

 5. Index 를 설정한다고 해도 반드시 Unique 한것은 아니다

 6. PK는 Clustered Index를, UQ는 Non-Clustered Index가 기본적으로

설정된다

 7. Index가 필요하다면,

포인트 쿼리, 범위쿼리, 추가(순차적/비순차적), 크기

 8. Clustered Index 

- 범위쿼리와 순차적 Data입력/수정/삭제에 바람직

- Table당 한개만 생성 가능

 9. Non-Clustered Index

- 비순차적 Data입력/수정/삭제와 포인트쿼리에 바람직

- Table당 249개 생성 가능

10. Index Key 설정 시, 같은 조건이면 크기(Size) 작은 것으로 할것

11. Scan/Seek

- Table Scan : Heap의 상태에서 전체 페이지 조회

- Clustered index Scan : CI로 설정된 상태에서 전체

페이지 조회

- Clustered index Seek : CI로 설정된 상태에서 

Index를 통한 조회

- Index Seek : NCI로 설정된 상태에서 Index를 통한 조회

12. 외래키(Foreign Key)에 Index를 설정할 때, 성능이 좋아질 

확률이 많다

13. alter index 지원은 SQL Server 2005 부터 가능

14. 단일컬럼 Index 

- 복합 컬럼 index : 설정된 컬럼 모두 Key값

create nonclustered index idx_cust_N

on cust_N (customerid,추가컬럼)

- 포괄열 index    : 포괄열은 Key값은 아님

 : NCI 에서만 가능함

create nonclustered index idx_cust_N

on cust_N (customerid) include (추가컬럼)

15. 채우기비율 설정

   -채우기비율(FillFactor) : Index Leaf Page에만 영향

   -인덱스패딩(PAD_INDEX)  : ON 하면 Leaf외에도 채우기비율 적용

### Cover Index

-- Index Lookup --

if OBJECT_ID('Orders') is not null

drop table Orders

go

select * into Orders from Northwind.dbo.Orders

go

select * from Orders

Exec sp_helpindex Orders

create nonclustered index idx_ord_01 

on Orders(CustomerID) -- nchar(5)

go

set statistics profile on

set statistics io on

set statistics time on

select * from Orders

go

-- Index를 타고 있는가? --

select * 

from Orders

where CustomerID = 'ERNSH'

and ShipVia = 3

go

-- Index 강제 적용 --

select ShipVia 

from Orders with(index(idx_ord_01))

where CustomerID = 'ERNSH'

and ShipVia = 3

go

exec sp_helpindex orders

-- NC Index 재생성 -- (인덱스 포괄열 포함)

drop index Orders.idx_ord_01

create nonclustered index idx_ord_01 

on Orders(CustomerID) include (ShipVia)

go

-- NC Index 추가 생성 --

drop index Orders.idx_ord_02

create nonclustered index idx_ord_02 

on Orders(EmployeeID,ShippedDate)

go

Exec sp_helpindex Orders

go

-- 검색 --

Select * from Orders

where EmployeeID = 4

and ShippedDate >= CONVERT(datetime,'19960101',112)

and ShippedDate < CONVERT(datetime,'19980101',112)

go

-- Hint 사용 --

Select * from Orders with(index(idx_ord_02))

where EmployeeID = 4

and ShippedDate >= CONVERT(datetime,'19960101',112)

and ShippedDate < CONVERT(datetime,'19980101',112)

go

-- NC Index 제거 및 CI 추가 생성 --

drop index Orders.idx_ord_02

create clustered index idx_ord_03 

on Orders(EmployeeID,ShippedDate)

go

Exec sp_helpindex Orders

go

-- CI를 통한 검색 --

Select * from Orders

where EmployeeID = 4

and ShippedDate >= CONVERT(datetime,'19960101',112)

and ShippedDate < CONVERT(datetime,'19980101',112)

go

--별도] NC Index 강제 적용: Key Lookup을 하고 있는가? --

select * 

from Orders with(index(idx_ord_01))

where CustomerID = 'ERNSH'

and ShipVia = 3

go

-- 페이지 I/O 2개 <-- 논리적 읽기 수 2로 알수 있음

Select COUNT(*)

from Orders 

where CustomerID like 'Bo%'

go

-- Key Lookup의 횟수는 총 34회

-- 페이지 I/O가 2개 이므로, (81-2)

-- Key Lookup이 읽었던 페이지 수는 79페이지

Select CustomerID,ShippedDate,Freight

from Orders with(index(idx_ord_01))

where CustomerID like 'Bo%'

go

--> Heap의 상태에서 RID Lookup을 통한

--> 페이지 읽기의 수보다는 늘어난 상태

-- NCI 제거 후, 커버된 Index 생성 --

drop index Orders.idx_ord_01

go

create index idx_ord_04 on Orders (CustomerID)

include (ShipVia,Freight)

go

-- NCI 검색 : 읽기 페이지수 2개 --

Select CustomerID,ShippedDate,Freight

from Orders with(index(idx_ord_04))

where CustomerID like 'Bo%'

go

--> NCI Leaf레벨의 Index 페이지에 

--> ShippedDate,Freight 가 포함되어

--> Key Lookup 이 일어나지 않음

### INDEX Tuning

-- create database inDB

use inDB

select ProductID, Name, Color, Size, Weight

 into pTbl from Adventureworks.Production.Product

select SalesOrderDetailID as OrderID, ProductID, OrderQty,

unitPrice, LineTotal as Total

 into oTbl from Adventureworks.Sales.SalesOrderDetail

-- Create Constraint

Alter Table pTbl

 add constraint pk_pTbl Primary key (ProductID)

Alter Table oTbl

 add constraint pk_oTbl Primary key (OrderID)

Alter Table oTbl

 add constraint fk_pTbl 

foreign key (ProductID) references pTbl(ProductID)

--

select count(*) from pTbl

select count(*) from oTbl

-- 

Select Top(5)* from pTbl 

Select Top(5)* from oTbl

Select * from pTbl --> 7 페이지

Select * from oTbl --> 664 페이지

set statistics IO on

-- Tunning Advisor --

select p.ProductID, p.name, 

sum(o.orderqty) as [oTotal],sum(o.Total) as [Total]

from pTbl P inner join oTbl O on p.ProductID = o.ProductID

group by p.ProductID, p.name

having p.ProductID = '717';

select * from pTbl where color='Silver' order by name;

select ProductID, Total from oTbl order by Total Desc;

 ### 트리거 (Trigger)

 1) 목적 : 데이터 무결성과 일관성 유지

 2) 종류:

- DML 트리거 : insert, update, delete

- DDL 트리거 : create, alter, drop

 3) 옵션:

- After : 실행 후(트랜잭션 로그가 계속 남음)

- Instead of : 실행 전(트랜잭션 로그가 안남음)

 4) 참고: 가상 테이블 (inserted, deleted)

use sqldb

create Table TA

 ( id int identity Primary key,

name char(8))

create Table TB

 ( id int identity Primary key,

name char(8))

insert into TA values ('jhAn')

insert into TB values ('jhAn')

select * from TA

select * from TB

create Trigger Trg_A on TA

 instead of insert

 as

rollback

go

create Trigger Trg_B on TB

 after insert

 as

rollback

go

drop Trigger Trg_A

drop Trigger Trg_B

Instead Vs. After 차이점

## Procedure Compile

drop Table spTable

select * into spTable 

from AdventureWorks.Sales.Customer

order by rowguid

select * from spTable

create index idx_spTbl on spTable (customerid)

Exec sp_helpindex spTable

--set statistics time off

set statistics IO on

select * from spTable where customerID < 10

select * from spTable where customerID < 1000

create Proc usp_1

@id int

as

select * from spTable where customerID < @id

Exec usp_1 10 --with recompile

Exec usp_1 1000 --with recompile

DBCC Freeproccache

alter Proc usp_1

@id int

with recompile

as

select * from spTable where customerID < @id

## 데이터 형식 우선순위 ##

1.     사용자 정의 데이터 형식(가장 높음)

2.     sql_variant

3.     xml

4.     datetimeoffset

5.     datetime2

6.     datetime

7.     smalldatetime

8.     date

9.     time

10.   float

11. real

12.   decimal

13.   money

14.   smallmoney

15.   bigint

16.   int

17.   smallint

18.   tinyint

19.   bit

20.   ntext

21.   text

22.   image

23.   timestamp

24.   uniqueidentifier

25.   nvarchar(nvarchar(max) 포함)

26.   nchar

27.   varchar(varchar(max) 포함)

28.   char

29.   varbinary(varbinary(max) 포함)

30.   binary(가장 낮음)

트랜잭션이란? 

데이터베이스의 논리적인 작업의 처리 단위이다.

여기서 작업이라 함은 일반적으로 데이터베이스에서의 데이터 입력，수정，

삭제 등을 말하는 것으로 데이터베이스에 변경사항을 유빌하는 행위를 말한다.

그리고 하나의 입력, 수정, 삭제 직업이 개별적인 트랜잭션으로 처리될 수도 있지만,

하나의 트랜잭션 내에서 여러 작업이 포함될 수도 있다.

그래서 트랜잭션읕 물리적이 아닌 논리적인 작업의 처리단위라고 하는 것이다.

그리고 트랜잭션의 기본 개념은 All or Nothing 이다.

 # 트랜잭션

-- 명시적 트랜잭션   : Begin(O),Commit(O)

-- 암시적 트랜잭션   : Begin(X),commit(O) ==> 오라클

-- 자동커밋 트랜잭션 : Begin(X),commit(X) ==> MS SQL Server

-- set implicit_transactions on

-- AA 창 --

select * from TC

begin tran

 update TC set id = 11 where id = 10

 update TC set id = 22 where id = 20

 update TC set id = 33 where id = 30

commit tran

select @@TRANCOUNT

-- BB 창 --

 use master

 Alter database sqlDB set allow_snapshot_isolation on

 set Transaction isolation level snapshot

  -- 잠금에 걸린 Tabble이라도 메모리(캐시)가 아닌 

-- 데이터 파일의 내용을 무조건 읽어올수 있도록 만들어 줌

select * from TC

# 체크포인트

체크 포인트(Check Point) 프로세스는 현재까지 메모리에서 진행된 모든 내용을 데이터 파 일과 로그 파일에 기록하는 프로세스로이다. 마치 워드나 엑셀 같은 프로그램에서 저장버튼 을 누르는 것과 같은 역할을 수행한다. 미러한 체크 포인트는 기본적으로 SQL Server가 1분마다 상황을 체크해서 많은 작업(많은 작업에 대한 판단은 SQL Server가 한다)이 있었던 경우 발생하기 때문에 궁극적으로 비주 기적으로 발생된다고 할 수 있다. 다시 말해서 데이터베이스에서 많은 작업이 일어나면 체 크 포인트가 발생하는 주기는 1분이 될 것이며, 데이터베이스에서 일어나는 작업이 많지 않 으면 체크 포인트가 발생하는 주기를 예측할 수 없다는 말이 된다.

# 레이지 라이터 & 로그라이터

 - 레이지 라이터(Lazy Writer)

레이지 라이터(Lazy Writer)라는 프로세스는 메모리 상의 프로시저 캐시에서 시용 빈도가

낮은 실행 계획을 메모리에서 제거하고 버퍼 캐시에 있는 더티 페이지(Dirty Page)들을 디

스크에 기록함으로써 메모리상에 빈 공간을 확보하기 위해서 동작하며, 이 역시 백그라운드

프로세 스로 주기적으로 동작한다.

 - 로그 라이터(Log Writer)라는 프로세스는 트랜잭션이 완료(Commit, 커밋)되면 자동으로

커밋된 트랜잭션에 대한 로그를 트랜잭션 로그 파일에 기록하는 프로세스이다.

### 참조 제약 조건

create table byTbl

(no <-- 숫자 증가값

 name --> uTbl(userid)

 prodname

 groupname

 price

 amount

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

  create Table byTbl

(num int Identity Not Null Primary Key,

 userid nchar(8) Not Null 

Foreign Key References uTbl(userid),

 prodName nchar(6) Not Null,

 groupName nchar(4),

 price int Not Null,

 amount smallint Not Null

)

go

### JOIN 사용

-- userTbl,buyTbl에서 하세요

--1) 키가 180 이상인 사람의 userid, 이름, 거주지는?

select userID, name, addr

from userTbl

where height >= 180

--2) 핸드폰 번호가 011,018 인 사람의 userid, 이름, 거주지는?

select userID,name,addr,mobile1

from userTbl

where mobile1 in ('011','018')

--3) 이름이 박주영인 사람의 구매한 목록, 즉

-- 이름,userid, 상품명, 가격, 수량을 구하시요.

select u.name, u.userID, b.prodName, b.price, b.amount

from userTbl u, buyTbl b

where u.userID = b.userid

  and u.name = '박주영'

SELECT     userTbl.userID, userTbl.name, buyTbl.prodName, buyTbl.price, buyTbl.amount

FROM         buyTbl INNER JOIN

             userTbl ON buyTbl.userid = userTbl.userID

WHERE     (userTbl.name = N'박주영')

--4) 키가 182 이상인 사람들의 이름,키,userid, 

-- 상품명,가격,수량은?

select u.name, u.height, u.userID, b.prodName, b.price, b.amount

from userTbl u, buyTbl b

where u.userID = b.userid

and u.height >= 182

--5) 키가 182 이상인 사람별 이름, 총구매금액 중 1000이상인

-- 것만 나타내시오

select u.name, sum(b.price*b.amount) as 총구매금액

from userTbl u, buyTbl b

where u.userID = b.userid

and u.height >= 182

group by u.name

having sum(b.price*b.amount) >= 1000

--6) 안정환보다 키가 큰 사람의 이름,birthyear,addr,height ?

--1> 안정환의 키?

select height

from userTbl

where name = '안정환' --> 182

--2> 그 키보다 큰사람의 ~~~~

select name,birthYear,addr,height

from userTbl

where height > 182

--3> 문장 합치기

select name,birthYear,addr,height

from userTbl

where height > (select height

from userTbl

where name = '안정환')

--7) 김남일보다 키가 큰 사람의 이름과 거주지,키

--   구매상품명,가격,수량은?

--1> 김남일의 키는?

select height

from userTbl

where name = '김남일' -- 183

--2> 그 키보다 큰 사람의 ~~~?

select u.name,u.addr,u.height,b.prodName,b.price,b.amount

from userTbl u, buyTbl b

where u.userID = b.userid

and u.height > 183

--3> 문장합치기

select u.name,u.addr,u.height,b.prodName,b.price,b.amount

from userTbl u, buyTbl b

where u.userID = b.userid

and u.height > (select height

from userTbl

where name = '김남일')

### VIEW 설명

 # 뷰(View)

 1) 정의 : Select 문장의 덩어리 

(가상의 논리적 테이블)

 2) 목적 : 편의성, 보안성, 성능향상

 3) 종류 :

- 일반 View

- Index View

- Partition View

drop view v_userTbl

create view v_userTbl

as

 select userid,name,addr from userTbl

select * from v_userTbl 

where name='이천수'

select * from userTbl

update v_userTbl set addr='ZZ' where name = '이천수'

update v_userTbl set height=100 where name = '이천수'

insert into v_userTbl values ('KKK','김금갑','PP')

insert into v_userTbl (userid,name,addr) 

values ('KKK','kkKim','PP')

create View v_userTbl3

 as

 select userid,name,birthyear,addr from userTbl

insert into v_userTbl3 values ('KKK','kkKim','1977','PP')

select * from v_userTbl3

delete v_userTbl3 where userid='KKK'

select * from userTbl

create View v_userTbl4

 as

 select * from userTbl where height >= 180

select * from v_userTbl4

delete v_userTbl4 where height < 180 

select * from userTbl

update v_userTbl4 set birthyear ='1111' 

where height < 180

insert into v_userTbl4 

values ('TTT','ggHong','4444','GG',010,0101,190)

insert into v_userTbl4 

values ('ZZZ','ggHong','4444','GG',010,0101,100)

select * from userTbl

alter view v_userTbl4

as

 select * from userTbl where height >= 180

 with check option

select * from sys.views

Exec sp_helptext 'v_userTbl4'

alter View v_userTbl4

 with Encryption

as

 select * from userTbl where height >= 180

with check option

use sqldb

select * from userTbl --> userTbl2

select * into userTbl2 from userTbl

select * from userTbl2

-- view를 생성 

-- (userid, name, birthyear, addr, height)

create view v_uTbl2

as

select userid,name,birthyear,addr,height

from userTbl2

select * from v_uTbl2

-- userTbl2 에서 height 컬럼을 제거해보세요

alter table usertbl2

drop column height

go

select * from userTbl2

select * from v_uTbl2 <-- 실행되는지요?

drop view v_uTbl3

create view v_uTbl3

with schemabinding

as

select userid,name,birthyear,addr,mobile2

from dbo.userTbl2 --> table명 앞에 dbo 스키마를 선언

go

select * from v_uTbl3

alter table usertbl2

drop column mobile2

go

## SQL Injection

 ID     [  admin' or 1=1 !--  ] 

 PWD    [  1234  ]  

select * from where id ='swhong' or 1=1 

   select * from where id='admin' or 1=1 1!--' and pwd = '1234'

  Exec sp_userid('jane','1234')

 ### 저장프로시저 (Stored Procedure)

 1) 정의 : - Transact-SQL 문장의 집합 

 2) 용도 :

- 어떠한 동작을 절차적 일괄처리 작업하는데 사용

- SQL Server에서 사용하는 프로그래밍 기능

 3) 특징 :

- SQL Server의 성능향상 (첫 실행시 컴파일, 재컴파일 안함)

- 모듈식 프로그래밍 가능

- 보안강화 (저장프로시저 마다의 권한할당 가능)

- 네트워크 전송량 감소 (긴 쿼리문장의 단순화)

 4) 장점 :

- 빠르다

- 파라미터를 사용할 수 있다

- 여러 어플리케이션과 공유할수 있다

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

 # 일반적인 T SQL 구문 실행절차

1. 첫실행시

 1) 구문분석

 2) 개체검사

 3) 권한확인

 4) 최적화경로확인

 5) 컴파일(with 실행계획) --> 메모리 상주

 6) 실행

2. 두번째실행시 (구문이 같아야)

 1) 기 컴파일 사용

 2) 실행

 # 저장 프로시저

1. 첫실행시

 1) 구문분석

 2) 개체검사

 3) 권한확인

 4) 최적화경로확인

 5) 컴파일(with 실행계획) --> 메모리 상주

 6) 실행

2. 두번째실행시 (구문이 같지않아도)

 1) 기 컴파일 사용

 2) 실행

 use sqldb

create Procedure usp_userTbl

as

 select * from userTbl

Exec usp_userTbl

-- View 일 경우 --

create view v_Test 

as

   select * from userTbl

select * from v_Test where userID = 'AJH'

create Proc usp_userTbl2

 @userid nchar(8)

as

 select * from userTbl where userid = @userid

go

Exec usp_userTbl2 'AJH'

create Proc usp_userTbl3

 @a int,

 @b int

as

 select * from userTbl where birthyear > @a and birthyear <= @b

go

Exec usp_userTbl3 1978,1982

create Proc usp_userTbl4

@userid nchar(8)

as

declare @byear int

select @byear = birthyear from userTbl where userid = @userid

if (@byear >= 1978)

Begin

Print 'Low Age'

end

else

Begin

Print 'High Age'

end

go

Exec usp_userTbl4 'AJH'

-- Input Value, Output Value

create Proc usp_userTbl5

@userid nchar(8),

@outAge int OutPut

as

Declare @bYear int

select @bYear = birthyear from userTbl where userid = @userid

select @outAge = Year(getdate()) - @bYear

-- Execute SP

declare @iAge int

Exec usp_userTbl5 'AJH', @iAge Output

Print 'Your Age ==> ' + cast(@iAge as char(5))

select @@NESTLEVEL

### 사용자 정의 함수 (User Defined Function)

 1) 정의 : 리턴값을 갖는 프로시저의 종류

 2) 유형 : 

- 스칼라(Scalar) 함수 

- 테이블 함수

  - 인라인 테이블 값 (Inline Table Valued)함수

  - 다중문 테이블 값 (Multi Statement Table Valued)함수

 3) 제약사항 :

- 함수 내부에 Try Catch 문을 사용할 수 없다

- 함수 내부에 DDL문(Create/Alter/Drop)을 사용할 수 없다

 @ 스칼라 함수: Return문에 의해서 하나의 단일값을 돌려주는 함수

 @ 인라인 테이블값 함수 : Select 문장의 결과 집합을 돌려주는 함수

 @ 다중문 테이블 값 함수 : 정의된 임시 테이블 자체를 돌려주는 함수

 create #tmpTbl (id txt) : 사용할 수 없음

Inline_Fuction.sql

Muti_Fuction.sql

Scalar_Fuction.sql

exec sp_help usertbl  -- 테이블 분석

## 제약조건(Constraint)

create Table uTbl

(userID nchar(8) Not Null 

Constraint PK_userTbl_userID Primary Key,

 name nvarchar(10) Not Null

Constraint UQ_userTbl_name Unique,

 birthYear int Not Null --> 올해와 이후 100년 사이

Constraint CK_userTbl_BY 

check(birthyear>=year(getdate())

and birthyear<year(dateadd(yy,100,GETDATE()))),

 addr nchar(4) Not Null  --> 기본값 (서울)

Constraint DF_userTbl_addr default('서울'),

 mobile1 nchar(3) --> 010,011,016,017,018,019

Constraint CK_userTbl_mob1

check(mobile1 in ('010','011','016','017','018','019')),

 mobile2 nchar(8),

 height smallint --> 0 ~ 250

Constraint CK_userTbl_height

check (height > 0 and height <= 250)

)

go

select GETDATE()

select YEAR(getdate())

select year(dateadd(yy,100,GETDATE()))

페이지

---

SQL Server에서 페이지의 크기는 8KB입니다. 이 사실은 SQL Server 데이터베이스에 메가바이트당 128페이지가 있음을 의미합니다. 각 페이지는 96바이트 머리글로 시작하는데 이 머리글은 페이지에 대한 시스템 정보를 저장하는 데 사용됩니다. 페이지 번호, 페이지 유형, 해당 페이지의 사용 가능한 공간 크기 그리고 해당 페이지를 소유하고 있는 개체의 할당 단위 ID와 같은 정보를 저장합니다.

다음 표에서는 SQL Server 데이터베이스의 데이터 파일에서 사용되는 페이지 유형을 보여 줍니다.

페이지 유형	내용
데이터	text in row가 ON으로 설정된 경우에 text, ntext, image, nvarchar(max), varchar(max), varbinary(max) 및 xml 데이터를 제외한 모든 데이터가 있는 데이터 행
인덱스	인덱스 항목
텍스트/이미지	큰 개체 데이터 형식: text, ntext, image, nvarchar(max), varchar(max), varbinary(max) 및 xml 데이터 데이터 행이 8KB를 초과하는 경우 가변 길이 열: varchar, nvarchar, varbinary 및 sql_variant
전역 할당 맵, 공유 전역 할당 맵	익스텐트가 할당되었는지 여부에 대한 정보
페이지의 사용 가능한 공간	페이지 할당 및 페이지의 사용 가능한 공간에 대한 정보
인덱스 할당 맵	테이블 또는 인덱스에서 할당 단위당 사용하는 익스텐트에 대한 정보
대량 변경 맵	마지막 BACKUP LOG 문 이후에 할당 단위당 대량 작업에 의해 수정된 익스텐트에 대한 정보
차등 변경 맵	마지막 BACKUP DATABASE 문 이후에 할당 단위당 변경된 익스텐트에 대한 정보

참고
로그 파일은 페이지는 포함하지 않으며 일련의 로그 레코드를 포함합니다.

데이터 행은 머리글 바로 다음부터 시작하여 페이지에 차례로 나옵니다. 행 오프셋 테이블은 페이지 끝에서 시작하는데 각 행 오프셋 테이블에는 해당 페이지에 있는 각 행에 대한 항목이 하나씩 있습니다. 각 항목은 해당 행의 첫째 바이트가 페이지 시작 지점에서 얼마나 떨어져 있는지를 기록합니다. 행 오프셋 테이블의 항목 순서는 페이지의 행 순서의 역순입니다.

대용량 행 지원

행들이 여러 페이지에 걸쳐 있을 수 없지만 그러한 행 부분들이 해당 행의 페이지를 벗어나서 해당 행이 실제로는 아주 커질 수 있습니다. 한 페이지의 단일 행에 데이터와 오버헤드가 최대 8,060바이트(8KB)까지 저장됩니다. 그러나 텍스트/이미지 페이지 유형에 저장되는 데이터는 여기에 포함되지 않습니다. varchar, nvarchar, varbinary 또는 sql_variant 열이 있는 테이블의 경우 이러한 제한이 완화됩니다. 테이블에 있는 모든 고정 및 변수 열의 전체 행 크기가 8,060바이트 한계를 초과하면 SQL Server는 하나 이상의 가변 길이 열을 가장 너비가 넓은 열부터 시작하여 ROW_OVERFLOW_DATA 할당 단위에 있는 페이지로 동적으로 옮깁니다. 삽입 또는 업데이트 작업으로 행의 전체 크기가 8060바이트 한계를 초과하면 이러한 작업이 수행됩니다. 열이 ROW_OVERFLOW_DATA 할당 단위의 페이지로 이동하면 IN_ROW_DATA 할당 단위에 있는 원래 페이지의 24바이트 포인터가 그대로 유지됩니다. 후속 작업으로 행 크기가 줄면 SQL Server가 동적으로 열을 다시 원래 데이터 페이지로 이동합니다. 자세한 내용은 8KB를 초과하는 행 오버플로 데이터를 참조하십시오.

익스텐트

---

익스텐트는 공간 관리의 기본 단위입니다. 하나의 익스텐트는 실제로 연속하는 8페이지 또는 64KB입니다. 이 사실은 SQL Server 데이터베이스에 메가바이트당 익스텐트가 16개 있음을 의미합니다.

SQL Server은 효율적인 공간 할당을 위해 적은 양의 데이터를 포함하는 테이블에 전체 익스텐트를 할당하지 않습니다. SQL Server에는 다음 두 가지 유형의 익스텐트가 있습니다.

• 균일 익스텐트는 단일 개체가 소유합니다. 또한 익스텐트의 전체 8페이지는 소유하는 개체만 사용할 수 있습니다.

• 혼합 익스텐트는 최대 8개의 개체가 공유할 수 있습니다. 익스텐트의 8페이지를 각각 다른 개체가 소유할 수 있습니다.

일반적으로 새 테이블이나 인덱스에는 혼합 익스텐트의 페이지가 할당됩니다. 테이블이나 인덱스의 페이지가 8페이지로 증가하면 후속 할당을 위해 균일 익스텐트를 사용하도록 전환됩니다. 인덱스에 8개의 페이지를 생성하는 데 충분한 행을 가진 기존 테이블에서 인덱스를 만드는 경우 인덱스에 대한 모든 할당 항목은 균일 익스텐트에 있습니다.

### SHRINK 테스트

@@@ shrink database @@@

use master
create database shrinkDB
on Primary
(name = shrinkDB,
 Filename = N'C:\database\sDB\shrinkDb.mdf',
 size=3MB)
Log on
(name = shrinkDB_Log,
 Filename= N'C:\database\sDB\shrinkDb_log.ldf',
 size=3MB)

-- 1 (insert A) --

use shrinkDB
create Table test (txt varchar(1024))
go

Declare @i int
set @i = 0
while @i < 1024*10
Begin
 insert into test values (replicate ('A', 1024)) 
 set @i = @i+1
end

-- 2 (insert B,C) --

Declare @i int
set @i = 0
while @i < 1024*10
Begin
 insert into test values (replicate ('B', 1024)) 
 set @i = @i+1
end

set @i = 0
while @i < 1024*10
Begin
 insert into test values (replicate ('C', 1024)) 
 set @i = @i+1
end

-- 3 (shrink database [SSMS])

-- 4 (view Size)

-- 5 (delete B)
delete test where substring(txt,1,1) = 'B'

-- 6 (shrink database [SSMS])

-- 7 (view Size)

### File Group

 - Database Object에 대한 저장

 - Data File의 묶음

 - Raid 0 구성 (I/O를 향상) 

   -> 물리적 드라이브가 실제 분리되어 있어야 효과

 - 주 Data File(.mdf)은 기본적으로

   -> Primary File Group 소속되어져 있음

 - Primary File Group은 읽기 전용 그룹이 될 수 없음

 - 나머지 File Group은 읽기 전용 그룹이 될 수 있음

 - 백업/복원의 대상이 됨

 - 증분복원: 단계적 복원이 가능

# Table 이란??

 - Data가 저장되어지는 논리적 구조의 객체(그릇)
 - 행(Record)과 열(Column)이 존재
 - Table in RDB(관계형DB)에서는 자기/다른 Table
 과의 관계(Relationship)설정이 가능

 -> 일관성, 무결성을 위한 모델링을 하게되면

    분리된 Table 사이에서 연결고리를

    찾는게 어려워 지기때문에

    그것을 관계를 통해 해결

 - 모델링, 정규화 --> 데이터의 무결성

 # E-R 관계 사상 알고리즘

 - 단계 1: 정규 엔티티  <= 테이블 + 기본키
 - 단계 2: 약한 엔티티  <= 부모의 키 + 부분키
 - 단계 3: 1:1 관계타입  <= 부모의 PK -> 자식의 PK,FK
 - 단계 4: 1:N 관계타입  <= 부모의 PK -> 자식의 FK
 - 단계 5: M:N 관계타입  <= 신규테이블 + [양측의 PK -> FK]
 - 단계 6: 다치 애트리뷰트  <= 신규테이블 + [부모의 PK -> FK]

# 정규화란 중복성을 최소화하고 정보의 일관성을 보장하기 위한 관계형 데이터 베이스에서 제일 중요한 개념.

# 정규화를 하는 이유

 - 데이터의 중복성 제거
 - 데이터 모형의 단순화
 - Entity,Attribut의 누락여부
 - 데이터모형의 안전성 검증

 # Data 무결성

 1) 참조무결성 : 테이블간 불일치 데이터 방지하자
 2) 실체무결성 : 기본키로 Row를 구분하자
 3) 도메인무결성: 잘못된 형식을 방지하자

 # 정규화
 
 1) 1정규형
 - 데이터의 중복제거
 - 컬럼의 중복제거

 2) 2정규형
 - 기본키 전체에 의존 컬럼분류

 3) 3정규형
 - 일반컬럼 의존적 컬럼 분리

# 제 1정규화

 - 엔티티 내의 모든 속성은 반드시 하나의 값을 가져야 한다.

 - 여러 값을 가진 컬럼이 존재할 수 없다. 즉 반복되는 그룹이 존재해서는 안 된다. 각 행과 열에는 하나의 값만이 올수 있다.

# 제 2정규화

 - 복합키를 식별자로 가지는 경우 복합키의 일부분에만 종속적인 항목을 분류하는 작업

 - 모든키가 아닌 컬럼은 기본 키 전체에 의존적이여야 한다. 기본키의 일부분에 의존적이어서는 안 된다.

# 제3정규화
 
 - 엔티티 내의 식별자를 제외한 모든 속성은 종속될 수 없다.

 - 키가아닌 컬럼은, 다른 키가 아닌 컬럼에 의존적일어서는 안된다.

# 역정규화

 - 데이터의 중복을 통해 과도한 논리적 결합현상을 피한다.
 
 - 유도된 컬럼을 추가하여 불필요한 연산을 피한다. (예)판매금액=수량*단가

 - 하나의 테이블을 2개 이상의 테이블로 분리하여 불필요한 컬럼의 검색을 피한다.

 - 자주 사용되는 테이블의 논리적 결합을 피하기 위해 집계테이블을 생성한다.

논리적 모델링 단계에서 논해야 하는 문제는 개념적 모델링 후,

매핑룰에 의해 기초적인 논리적 모델링으로 내려 오고

논리적 모델링시에는 흔히 이야기 하는 정규화 과정을 거치게 됩니다.

즉, 차수 정의(1:1, 1:M, N:M)관계는 이미 개념적 모델링에서 처리가 된다는 것입니다.

 DA# 프로그램으로 말하면 플래너 단계에서 이루어 진다고 봅니다.

이에 대한 논리적 모델링 단계에선 관계형 데이타 베이스에 맞는 모델링을 해야 합니다.

마지막 단계에서 역정규화 작업이나.. 여타 작업을 하지만,

이것이 테이블에 대한 분리를 이야기 하지 않습니다.

정규화 단계를 많은 사람들이 알고 있듯이 테이블의 분할이라는 것은

속성에 대한 재정의 (차수의 변경등에 의한)를

통해서 속성에 대한 테이블의 분리로 이루어 질 수 있습니다.

하지만, 논리적 모델링 단계에서는 DBMS의 벤더에 종속되지 않는 모델링이 이루어 져야 하고,

 관계형 데이터베이스의 이론에 충실한 모델링이 이루어 져야 합니다.

1:1 엔티티에 대한 내용은 역정규화시에 통합이 되어야 하고, 업무분석시에 이루어지는 검색조건이나, 여타 조건에 의한 테이블의 분할이라는 것은 DBMS가 정해지고 플랫폼의 정의가 이루어진 후, 물리적 모델링 단계에서 이루어 져야 한다고 봅니다.
저는 1:1 엔티티에 대한 통합은 논리적 모델링 관계에서 무조건적으로 통합되어야 한다고 봅니다.

####

BCNF 정규화는
3정규형에서 BCNF 정규형이 되려면 비결정자에의한 함수종속을 제거해야 하는것을 알고계시죠..
비결정자에 의한 함수종속을 제거해서 분해된 BCNF정규형은
결과적으로 모든 속성들이 후보키로서 사용이 가능한 형태로 분해됩니다.
다시 말해 분해된 테이블의 모든 결정자들이 후보키이면
BCNF정규형이라 말합니다
제4정규화는
2정규화 된 테이블은 다대다 관계를 가질수 없다.
이건 따로 예를 안들어도 되겠죠?ㅋㅋ

#### 제약조건

 1. PK  : 기본키 => 유일한값 , NULL(X), Index (기본값:CI)

 2. UQ  : 유니크 => 유일한값 , NULL(1[MS]), Index (기본값:NCI)
          NULL(다[Ora])

 3. FK : 외래키 => 자기/다른 테이블의 PK, UQ를 참조

  참조하는 컬럼의 DataType과 Size가 일치

 4. CK : 범위 설정 => 0 < 값 < 100
   열거 조건 => 값 in (값1, 값2, 값3,,,,)

 5. DF : 기본값

 6. NN : Not Null => Null 값 허용 여부

# Application

  - Database Server : MS SQL Server

    - Database Engine
# - DBMS (SSMS[DB Obejct / Query]
  SSCM[Service / Network])

#     -  Instance <-- IP (외부접속 연결대상)
 
         1차 이름      2차 이름
   ------------------------------
  * 외부 이름 : HostName    HostName\Test
  * 내부 이름 : MSSQLSERVER    Test

      --> 접속 연결 이름

   로컬 : .   or localhost
   내부 : MSSQLSERVER
   외부 : hosname or IP

      --> DB Engine Service 실행 OS Account

   - Administrator : OS 전체 관리 계정(사용자환경)
   - 일반 OS User  : 일반 사용자 계정
   - Local System  : OS 전체 관리 계정(컴퓨터환경)
   - Local Service : 일반 서비스 계정

      --> Instance 구성정보

  - System Database
     1) master : Instance 전체 구성정보
     2) model  : Database의 기본 틀 제공
     3) msdb   : 운영 업무 자동화의 관련 정보
     4) tempdb : 임시 Database (휘발성)

 
#  - Database : 업무에 따른 공간

#     - Schema : 사전 정의

#   - Object : Table

# SQL Server  관리 도구

 - SSMS : 개체관리 + 쿼리분석
 - SSCM : 서비스 + 네트워크 설정
 - SQL cmd : DOS 창에서 사용하는 SQL 명령어 도구

### 작업 스크립트

01. identity.sql

02. UNIQUEIDENTIFIER 데이터 형식.sql

03. HIERARCHYID.sql

04. Date 데이터형식.sql

05. char_varchar.sql

06. Collation.sql

07. null저장공간.sql

08. smallDateTime.sql

09. Time.sql

10. XML_DataConversion.sql

# SQL CMD 명령어

 - sqlcmd /?

#  데이터 값 사용에 대한 확인

null저장공간.sql

### XML 데이터 타입으로 변경

CreateSampleTbl.sql

InsertBuyTbl.sql

InsertUserTbl.sql

select * from userTbl
where height > 180
for xml raw('열')

- 트리 구조로 변경

- xml 데이터 값으로 출력

- xml db 형태로 변환

1. 설치 패키지

2. 설치 전 최종 단계

ALTER DATABASE 재배치 프로시저를 사용하여 사용자 데이터베이스를 이동하는 방법을 정리하였습니다.

일반적으로 동일한 SQL Server 인스턴스 내에서 파일 이동이 필요할 경우에 이 방법을 사용하며, 만약 다른 인스턴스나  다른 SQL Server로 이동한다면 분리 및 연결(sp_attach_db, sp_detach_db 시스템 저장 프로시져) 방법 또는 백업 및 복원 방법을 이용하면 됩니다.


[시나리오]
1. 데이터베이스명 : LAIGO

2. 변경 전 파일 경로
   1) C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\LAIGO.mdf
   2) C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\LAIGO_log.ldf

3. 변경 후 파일 경로
   1) D:\Data\laigo.mdf  
   2) D:\Data\laigo_log.ldf


[작업절차]

[참고자료]
사용자 데이터베이스 이동
http://msdn.microsoft.com/ko-kr/library/ms345483.aspx

방법: 분리 및 연결을 사용하여 데이터베이스 이동(Transact-SQL)
http://msdn.microsoft.com/ko-kr/library/ms187858.aspx

시스템 데이터베이스 이동
http://technet.microsoft.com/ko-kr/library/ms345408.aspx

Configuring IIS to Run 32-bit ASP.NET Applications on 64-bit Windows

Updated: August 22, 2005

Applies To: Windows Server 2003, Windows Server 2003 R2, Windows Server 2003 with SP1

SQL Server 2005 에서 데이터(mdf,ndf) 파일을 축소하는 방법에 대해서 아래와 같이 정리하였습니다.

데이터 파일의 할당 정보는 DBCC SHOWFILESTATS 명령을 사용하여 확인할 수 있습니다. 트 랜잭션 로그 파일의 현재 사용 가능한 빈공간을 조회할 때 DBCC SQLPERF(LOGSPACE) 명령을 사용하면 됩니다.

아래와 같이 LAIGO 데이터베이스의 데이터 파일 LAIGO 의 전체 크기와 사용량을 확인할 수 있습니다.

Fileid FileGroup TotalExtents UsedExtents Name FileName

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

1 1 880 92 LAIGO D:\LAIGO.mdf

(1개 행 적용됨)

자, 산수시간입니다.

SQL Server 의 기본 저장 단위 Page 의 크기는 8KB, 이며 8개의 연속 페이지로 크기가 고정된 Extent 로 저장됩니다.

TotalExtents 값이 880 Extents 면, 1MB가 16 Extents 이므로 880/16 = 55 MB 가 됩니다.

UsedExtents 값이 92 면, 92/16 = 5.75 MB 가 됩니다.

즉, 현재 할당된 데이터 파일의 크기는 55MB 입니다만 실제 사용량은 5.75MB 라는 것을 알 수 있습니다.


불필요한 공간을 줄이기 위해 데이터 파일을 축소하는 방법은 아래와 같습니다. LAIGO 파일의 크기를 10MB 로 축소하라는 의미입니다. 실제 TotalExtents 가 160 즉, 10MB 로 줄었습니다.

Fileid FileGroup TotalExtents UsedExtents Name FileName

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

1 1 160 92 LAIGO D:\LAIGO.mdf

(1개 행 적용됨)

TRUNCATEONLY 를 사용해 보면 어떨까요?

아직 사용되지 않은 뒷부분 빈공간이 싹뚝 잘려 나갔습니다.

Fileid FileGroup TotalExtents UsedExtents Name FileName

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

1 1 92 92 LAIGO D:\LAIGO.mdf

(1개 행 적용됨)

Single user 모드가 아니어도 작업은 가능합니다만 가능하면 Single User 모드가 아닌 상태에서 Deadlock 이 발생하는 사례가 있으므로 급한 상황이 아니라면 Single user 모드에서 작업하는 게 좋을 것 같다는 의견입니다. 만약 작업이 실패할 경우에는 SQL Error Log 을 살펴봐야 할 것 같습니다.

 데이터파일 축소 실습

실제 사용하는 databae의 크기보다, db파일(mdf, ldf) 사이즈가 클 경우, dbcc shrinkfile

쿼리문을 통해, 사이즈를 줄이는 방법에 대해 포스팅 하도록 하겠습니다.

참고로, 공간 부족으로 인한 장애발생을 대비하기 위해, databae파일과 로그파일 위치 및 size는

check 하셔야 합니다.

1. jkmoon 데이터베이스를 생성, test테이블을 만들어 데이터베이스의 사이즈를

1GB로 늘렸습니다. 그런 다음 사이즈가 큰 test 테이블을 삭제하였습니다.

2. jkmoon 데이터베이스 파일과 로그파일을 확인 하였습니다.

3. 로그사이즈를 먼저 확인하도록 합니다. 1.3GB중에서 약 1.7% 사용 중입니다.

dbcc sqlperf (logspace);

4. jkmoon 데이터베이스 파일, 로그의 이름과 위치를 확인 합니다.

Sp_helpdb jkmoon;

5. 쿼리문을 수행하여, 10MB로 로그파일을 줄입니다.

Use jkmoon

Go

dbcc shrinkfile (jkmoon_log, 10);

Jkmoon_log.ldf 로그사이즈가 줄어든걸 확인 하였습니다.

6. 마찬가지로, database파일도 사이즈를 10MB로 줄입니다.

Use jkmoon

go

Dbcc shrinkfile (jkmoon, 10);

1.     문자 식에서 가장 왼쪽 문자의 ASCII 코드 값 반환 (숫자로 표시됨)

è  MSSQL이든 ORacle이든 ‘’ 안에 여러 개의 문자가 있어도 가장 좌측 값만 반환합니다.

즉, SELECT ASCII(‘A’) 나 SELECT ASCII(‘ABCDEFG’) 나 결과는 같습니다.

2.     문자 합치기

3.     ASCII 코드를 문자로 변환하기

è  참고로 9번은 TAB, 10은 LF(Line Feed), 13번은 CR(Carriage Return) 입니다.

4.     좌측에서 몇 번째에 해당 문자가 있는지 알려주기

è  MSSQL과 Oracle의 함수사용 순서가 다릅니다.

è  MSSQL에는 패턴찾기에 PATINDEX를 많이 사용합니다. 위와 같은 결과를 얻으려면

SELECT PATINDEX('%마이%', '고마해라. 마이 무우따 아이가?')

처럼 사용하면 됩니다.(와일드 카드 사용 가능)

5.     대문자 변환, 소문자 변환

6.     좌측공간을 특정 문자로 채워주기

è  MSSQL에선 없는 함수이기 때문에 아래와 같이 사용자함수를 만들어서 사용하기도 합니다.

7.     우측공간을 특정 문자로 채워주기

è  MSSQL에선 없는 함수이기 때문에 UFN_LPAD처럼 함수를 만들어서 씁니다.

다만, REPLICATE라는 함수가 있는데, 이것은 특정문자를 연속적으로 채워 줄 뿐, RPAD와는 조금 다릅니다.

   또한, SPACE라는 함수는 공백만 채워줍니다.

8.     좌/우 공백 없애주기

9.     문자의 음성표현을 가지는 문자열을 반환. 국내에서는 흔히 사용하지 않음(한글 동작 안함)

b, f, p, v = 1

c, g, j, k, q, s, x, z = 2

l = 4

m, n = 5

r = 6

è  어라? 예제에는 같은 것으로 되어 있는데, 실제 결과는 다르네요. -_- 이것 참…

10.   전체 문자열에서 지정한 길이만큼의 문자열 반환

è  4번째 자리에서 시작해서 다섯 글자를 가져옵니다.

11.   문자열 변환

è  MSSQL의 STUFF라는 함수가 있는데요. 이것은 엑셀의 REPLACE와 같습니다.

12.   음절의 첫 글자만 대문자로 변환(Pascal Case)

14.   나열한 인수 중 가장 큰 값 / 작은 값을 반환

è  비교하는 것은, 문자순위(A보다는 Z가 크다. 한글은 자모순서에 의함), 숫자순위(0보다는 9가 크가), 글자수단위(같은 문자로 시작하더라도 결국 글자수가 많은 것이 크다.)

15.   길이 가져오기, 또는 BYTE 단위 길이 가져오기

è  위에서 보시면 알겠지만, BYTE 단위로 길이를 확인하시려면 LEN 이 아닌 DATALENGTH 를 사용하셔야 합니다.

16.   NULL 일 경우 대체값 표시.

17.   숫자형을 문자형으로 변환

è  MSSQL의 STR는 기본 자릿수가 10자리입니다.

è  Oracle에서는 위에서 보시다시피 숫자형태를 Concat하더라도 자동적으로 문자로 나옵니다.

è  물론 MSSQL에서는 CONVERT나 CAST를 훨씬 더 많이 사용합니다.

18.   문자형을 숫자형으로 변환