(MSSQL) 알지오 SQL 강좌 노트

도메인 - 컬럼 속성의 정의

파일 - 데이터가 들어갈때 만들어진 파일들에 번갈아가면서 저장되므로 파일이 많을수록 속도가 빨라진다.xpdl

 

shift + f1 : f1 키는 도움말 정보가 찾기 힘들고 shift+f1 을 누르면 찾고자 하는 내용을 바로 확인이 가능하다.

 

timestamp 데이터 insert 순서만 가짐

 

insert test default values : defalult 값으로 정해진 항목이 삽입됨.

 

unique 는 null 을 허용하면서 유니크하게 유지하고 싶을때 사용함.

 

constraint pk_employees primaty key(deptid, teamid, empno)

CREATE TABLE [dbo].[TEST_JUN4](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [NAME] [varchar](50) NOT NULL,
 [timestamp] [timestamp] NOT NULL,
 CONSTRAINT [CK_TEST_JUN4] check (NAME like '[0-9]')
 ) name 컬럼에 제약을 줄수 있다.

체크제약조건

 

DDL : Create, Alter, Drop
DML : Insert, Update, Delete
DCL : Grant

 

exec sp_helpdb : 현재 db 서버에 구성된 테이터베이스 정보를 보여줌

 

select top 20 percent * 퍼센트로 가져옴

select top 20 with ties * 동일 항목이 있는 경우 추가로 가져옴

 

distinct 를 하게 되면 자동으로 order by (오름차순정렬)

 

where  조건의 컬럼을 조작후 연산을 비교하게되면 느려짐 인텍스 안탐.
where a+1 < 10  (X)  where a < 10 - 1 (O)

 

아래처럼 컬럼에 연산을 하지 않고
select DATEPART(yy,getdate())
select year(getdate())
아래와 같이 컬럼은 그대로 두고 비교하면 더욱 빠르다.
where GETDATE() between '2012-01-01'  and '2014-01-01'

 

연산자 우선 순위
() > NOT > AND > OR

 

join 은 256개 테이블 이상을 할수 없다.

 

count(*) 은 null 도 카운팅한다.

다만 컬럼을 명시한 경우 null 은 제외한다.

 

SET ANSI_WARNINGS ON/OFF 표형태가 아닌 텍스트 형태의 결과를 보여줄때 에러 내용을 보여줍니다. 자동으로 null 제외등등

 

select COMPANY, sum(qty) SUM_QTY from TEST_JUN where qty > 40 group by all COMPANY

조건에 해당 하지 않으나 null 인 경우 group by all 을 사용한 경우 null 정보도 결과로 출력된다.

 

SELECT *
FROM TEST_JUN order by COMPANY
COMPUTE avg(qty) by company
COMPUTE - 부분합 order by 가 반드시 있어야 한다.

select COMPANY, SEX, AVG(Qty) Average from TEST_JUN group by COMPANY, SEX
with rollup - 중간 중간에 평균이 나온다.

결과

----------------------------------
각 항목의 평균을 구할수 있다.
인브레인 여 6
인브레인 중성 4
인브레인 NULL 5 - 평균
인브레인2 여 NULL
인브레인2 NULL NULL - 평균
인브레인3 남 78
인브레인3 여 NULL
인브레인3 NULL 78 - 평균
인브레인5 남 NULL
인브레인5 여 NULL
인브레인5 NULL NULL - 평균
NULL NULL 11 - 전체 평균

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

 

select COMPANY, SEX, AVG(Qty) Average from TEST_JUN group by COMPANY, SEX
with cube - 마지막에 몰아서 전체 평균이 나온다.

결과

----------------------------------
엠아이큐브 남 13
인브레인 남 NULL
인브레인3 남 78
인브레인5 남 NULL
NULL 남 20
엠아이큐브 여 7
인브레인 여 6
인브레인2 여 NULL
인브레인3 여 NULL
인브레인5 여 NULL
NULL 여 6
엠아이큐브 중성 NULL
인브레인 중성 4
NULL 중성 4
NULL NULL 11
엠아이큐브 NULL 10 - 평균
인브레인 NULL 5 - 평균
인브레인2 NULL NULL - 평균
인브레인3 NULL 78 - 평균
인브레인5 NULL NULL - 평균

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

 

* VIEW

view 도 update, insert 등이 가능하다
view 의 내용이 insert 항목중 필수값이 모두 있을 경우만 해당

alter view test_view2
as
select * from TEST_JUN
where COMPANY='엠아이큐브'
with check option -  옵션이 있는 경우 where 조건에 부합한 항목만 핸들링 가능하다.

 

프로시저내에서 사용된 sql 문은 실행계획을 생성하고 이를 캐싱한다.

원격지에서 수행되는 저장 프로시저는 롤백되지 않는다.

 

with encryption  - 프로시저를 암호화 한다. sp_helptext 로 보이지 않는다.
sp_helpdb

 

exec sp_procoption 'sp_procedure', 'startup','true' - 자동 실행  프로시저

 

with recompile - 참조되는 데이터의 잦은 변경은 저장 프로시저의 캐싱기능을 떨어뜨릴수 있다 이 경우 이옵션을 사용하여 저장프로시저를 재생성한다 이전까지의 캐싱은 무시하고 재생성시 새로운 캐싱을 시도

 

select @@version
select @@SERVERNAME

select * from sysdatabases

 

CONVERT - style

sp_dboption 'KJUN','select into/bulkcopy','true' - 해당 옵션이 true 이여야 select into 가능하다.

select * into testinto from TEST_JUN2

 

* 펑션
스칼라값
인라인테이블 : 리턴값으로 테이블 형식으로 데이터 리턴
다중문 테이블 : 완전한 테이블 자체를 리턴합니다.

 

* 트리거
instead of 트리거 - 이전에
after if 트리거 - 이후에
> 재귀트리거
create trigger alterDelete
on test_jun2
for delete, update
AS
raiserror('데이터삭제',16,10) - 경고메세지를 보여줍니다.

alter trigger insteadTestjun2 on TEST_JUN
instead of INSERT
AS
insert into TEST_JUN_BAK select * from INSERTED
insert into TEST_JUN select * from INSERTED

 

SET QUOTED_IDENTIFIER OFF -> select * from "test table" 을 허용하지 않음

SET NOCOUNT ON ->Transact-SQL 문 또는 저장 프로시저의 영향을 받은 행 수를 나타내는 메시지가 결과 집합의 일부로 반환되지 않도록 합니다.

 

WAITFOR DELAY '00:00:10' - 10초간 기다린다.

SET XACT_ABORT ON 으로 되어있는경우 하나의 트랜젝션에 두개의 insert 문이있는경우 두번째문이 실패하면 모두롤백

OFF 로 되어있으면 두번쨰가 실패하더라도 첫번째문은 commit 된다.

 

* 각쪽 잠금 옵션 설정.
select * from sys.configurations
sp_configure 'locks','10000'

 

* 타임아웃 설정
select @@Lock_timeout
SET lock_timeout 20000

* lock
holdlock sql 문장 단위가 아닌 트랜젝션이 긑까지 고유잠금을 보유
nolock lock 잡지 않음
pagelock 테이블 단위의 잠금을 페이지 단위로 바꾼다.
readpast 잠금이 걸려있는 행을 건너 뛴다.
rowlock 행단위 잠금
tablelock 테이블 단위 잠금
updlockselet 업데이트 잡금
xlock 모든 명령문을 잠금 가장 강력한 수단.

 

* 백업
backup database KJUN to tape ='\\.\tape0'
with format
name ='kjun_backup'

전체백업
backup database KJUN to disk ='C:\backup.bak'

영구백업 - 자동 백업을 사용할때 이용
exec sp_addumpdevice 'disk','kjun_backfile','C:\backup_Dump.bak'

rebuildm.exe 데이터 베이스를 초기화합니다.

backup database KJUN to kjun_backfile
with differential - 차등백업 차등 백업은 전체 백업을 기반으로 전체 백업 이후 변경된데이터만 캡처한다. 즉 변경된 데이터만 백업 한다.

로그 백업 backup log

 

* 복구
restore database kjun from kjun_back with recovery
norecovery 는 커밋된곳까지는 롤백처리하지 않을때 사용

* DTS
외부데이터를 끌어오거나... 등등..
데이터 베이스 의 데이터를 통재로 복사

 

clustered index - 범위찾기할때 빠르다 데이터도 같이 처리
nonclustered - 키 하나로만 찾을때 용이 데이터와 분리