MSSQL

DB 2011. 1. 29. 02:20

RAID (Redundant Array of Independent Disks 또는 Redundant Array of Inexpensive Disks)

여러 개의 하드 디스크에 일부 중복된 데이터를 나눠서 저장하는 기술 (wikipedia)

설명 : http://yamoe.tistory.com/204

SQL Server 2005 다운로드 : http://www.microsoft.com/korea/msdn/vstudio/express/sql/download/

SQL Server 2005 설치 및 설정 설명 : http://blog.naver.com/noir1986?Redirect=Log&logNo=40089260377

SQL Server Best Practices

http://technet.microsoft.com/ko-kr/sqlserver/bb331794.aspx

SQL Server 2005 제품소개및기술자료

http://www.microsoft.com/korea/sqlserver/2005/prodinfo/SQL2005_Resources.mspx

시작/종료

방법. "관리도구> 서비스> SQL Server (MSSQLSERVER)" 서비스정지/실행

방법. 콘솔명령사용

cmd> net start MSSQLSERVER (시작)

cmd> net stop MSSQLSERVER (중지)

cmd> net pause MSSQLSERVER (일시정지)

cmd> net continue MSSQLSERVER (재시작)

SQL Server실행파일위치: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe

System Database

master database : table, view, stored procedure에대한정보및관리등의메타데이터보관

model database : database 생성시사용할모델데이터보관

msdb database : 백업, 복제등의예약작업의내용을보관

tempdb database : 임시작업시사용하는데이터베이스

버전확인

SELECT @@VERSION --버전확인

SELECT SERVERPROPERTY('productlevel') --서비스팩확인

SELECT 'SQL Server ' -- SERVERPROPERTY사용한버전확인

+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '

+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('

+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

DATA BASE

DB 사용

USE master

DB 생성

CREATE DATABASE TestDB --데이터베이스이름

(

NAME = 'Test_dat', -- 논리적이름

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf',

SIZE = 10MB, -- 초기size

MAXSIZE = 50MB, -- 최대size

FILEGROWTH = 5% -- 증가단위. maxsize의%를의미

)

LOG ON --로그저장을위한설정

(

NAME = 'Test_log', -- 논리적이름

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\testlog.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB

)

GO --실행

DB 파일설정수정

ALTER DATABASE TestDB MODIFY FILE

(

NAME = N'TestDatabase',

SIZE = 100MB

)

DB 파일줄이기

DBCC SHRINKDATABASE (TestDB, NOTRUNCATE) -- 사용공간과빈페이지분리(정리작업)

DBCC SHRINKDATABASE (TestDB, TRUNCATEONLY)-- 빈페이지를해제하여OS에게돌려줌

파일별로용량줄이기(db 및log 파일)

DBCC SHRINKFILE ('test.mdf', NOTRUNCATE) -- 파일이름엔전체경로필요

DBCC SHRINKFILE (로그파일명, 파일크기(단위:MB)) -- 로그파일줄이기

DB 자동축소옵션: AUTO_SHRINK

DB 파일에사용되지않는공간이% 이상일때주기적으로파일크기를축소

ALTER DATABASE TestDB SET AUTO_SHRINK ON

사용자접근제한옵션: SINGLE_USER, MULTI_USER, RESTRICTED_USER

ALTER DATABASE TestDB SET RESTRICTED_USER

DB 활성화상태변경옵션: ONLINE, OFFLINE, EMERGENCY

ALTER DATABASE TestDB SET EMERGENCY -- 응급상태로변경

DB 상태

ONLINE : 정상상태

OFFLINE : 중지상태

RESTORING : 복원중인상태

RECOVERING : 복구중인상태

RECOVERY PENDING : 복구중리소스관련오류발생한상태

SUSPECT : 주파일그룹이주의대상이거나손상된상태

EMERGENCY : 사용자가DB 변경하고응급상태로설정한경우

SELECT NAME, STATE_DESC FROM SYS.DATABASES -- 전체DB 상태확인

SELECT DATABASEPROPERTYEX( 'TestDB', 'STATUS') 'Is Online?' -- 특정DB 상태확인

DB 업데이트권한옵션: READ_ONLY, READ_WRITE

USE master

ALTER DATABASE TestDB

SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- single user 사용으로변경

ALTER DATABASE TestDB SET READ_ONLY -- 읽기전용DB로변경

ALTER DATABASE TestDB SET MULTI_USER -- multi user 사용으로변경

ALTER DATABASE TestDB SET READ_WRITE -- 읽기/쓰기DB로변경

DB 이름변경

--단일사용자로변경후작업방식권장

ALTER DATABASE TestDB SET SINGLE_USER -- single user 사용으로변경

ALTER DATABASE TestDB MODIFY NAME=Test -- DB 리네임

ALTER DATABASE Test SET MULTI_USER -- multi user 사용으로변경

DB 삭제

USE master

DROP DATABASE TestDB1, TestDB2 -- DB 삭제(여러개지정가능)

데이터타입

날짜, 시간

datetime : 8 byte, 1753.01.01~9999.12.31. ms 단위까지표현

smalldatetime : 4 byte, 1900.01.01~2079.06.06. 1분단위까지표현

SELECT GETDATE() AS TODAY -- 오늘날짜

--날짜테스트

DECLARE @DATE1 DATETIME, @DATE2 DATETIME

SET @DATE1 = '1949-09-29'

SET @DATE2 = '1950-09-29'

SELECT @DATE1 AS '49', @DATE2 AS '50'

문자열

char, varchar : 0~8000 byte. 8000자까지지원

nchar, nvarchar : 0~8000 byte. 4000자까지지원

Text : 0~2GB. 가변길이데이터

Ntext : 0~2GB. 유니코드데이터

바이너리

binary : 0~8000 byte. 이진값

varbinary : 2 byte. -32768~32767까지의수

image : 0~2GB.

화폐단위

money : 8 byte. -2^63~2^63-1. 천단위콤마를사용하여천조까지처리가능.

smallmoney : 4 byte. -2147483648~2147483647. 천단위콤마지정가능.

기타

cursor : 커서지정용

uniqueidentifier : 16 byte. 항상고유한값을위한데이터형

SELECT NEWID()

timestamp : 8 byte. DB에서유일하게사용하는고유값

하나의테이블에하나의timestamp 컬럼생성허용.

xml : xml 데이터형식저장용도

sql_variant : text, ntext, timestamp, sql_variant를제외한모든데이터형저장가능

table : 2차원데이터저장

identity 속성

자동으로숫자가증가하는컬럼생성시사용하며

테이블하나에한개의컬럼에만사용가능.

CREATE TABLE Test (

ID INT IDENTITY (1, 1), -- identity 컬럼

NAME VARCHAR(10)

)

INSERT Test(NAME) VALUES('aaa') -- id 컬럼의값은자동증가

INSERT Test(NAME) VALUES('bbb')

-- identity 컬럼에직접값입력방법

SET IDENTITY_INSERT Test ON -- identity 컬럼직접값입력준비

INSERT Test(ID, NAME) VALUES(7, 'ccc')

SET IDENTITY_INSERT Test OFF-- 다시자동증가설정

사용자정의데이터타입

CREATE TYPE 문을통해생성가능

유니코드문자정렬(Collation)

언어의문자에따른정렬방식

-- Collation 확인

SELECT * FROM ::fn_helpcollations()

-- 컬럼에collation 적용

CREATE TABLE Test

(

id INT PRIMARY KEY,

name nvarchar(10) COLLATE Korean_Wansung_BIN NULL -- collation 적용

)

-- select 쿼리시collatoin 적용

SELECT * FROM Test WHERE name = 'KING' COLLATE korean_Wansung_CS_AS --CS는case sensitive임

테이블(table)

-- 테이블생성

CREATE TABLE Test (

num INT PRIMARY KEY, -- 기본키설정

name VARCHAR(30) DEFAULT '하이', -- 기본값설정

cont TEXT NOT NULL,

date DATETIME

)

-- 컬럼추가

ALTER TABLE Test ADD col1 varchar(10) NULL

-- 컬럼삭제

ALTER TABLE Test DROP COLUMN col1

-- 컬럼타입변경

ALTER TABLE Test ALTER COLUMN col1 varchar(50) NULL

-- 테이블삭제

DROP TABLE Test

-- PRIMARY KEY(기본키설정)

ALTER TABLE Test ADD CONSTRAINT PK_id PRIMARY KEY(id)

-- FOREIGN KEY(참조키설정)

ALTER TABLE Test ADD CONSTRAINT FK_name FOREIGN KEY (name)

REFERENCES Test2(name2)

-- 키삭제

ALTER TABLE Test DROP CONSTRAINT FK_name

-- 기본값

INSERT INTO Test(name) values(DEFAULT) -- 기본값으로insert

-- 규칙(rule:룰)

CREATE RULE rulename1 as @rule1 BETWEEN 0 AND 9 -- rule 생성

sp_bindrule rulename1, 'Test.num' -- rule 적용

내장함수

CAST, CONVERT

형변환을위한함수

-- 단순예제

DECLARE @VAR VARCHAR(10

SET @VAR = '1000'

SELECT CAST(@VAR AS INT)

SELECT CONVERT(INT, @VAR)

-- 날짜형식변환(102는yyyy.mm.dd 형식을의미)

SELECT CONVERT(varchar(20, GETDATE(), 102) AS DATE

ABS : 절대값반환

ROUND : 반올림

SIGN : 0보다작으면-1, 0이면0, 0보다크면1 리턴

RAND : 0~1사이의랜덤값

+ : 문자열연결

ASCII : ASCII 코드를문자로변환

CHAR : ASCII 코드값을문자로변환

LEFT, RIGHT : 왼쪽혹은오른쪽에서부터지정한수만큼문자출력

SUBSTRING : 문자열자르기

CHARINDEX : 특정문자의위치반환

LOWER, UPPER: 대문자, 소문자변환

LTRIM, RTRIM: 좌,우공백제거

GETDATE : 현재날짜/시간리턴

DATEADD : 지정된일수후의날짜반환

DATEDIFF : 두날짜간차이리턴

YEAR, MONTH, DAY : 날짜에대한연, 월, 일반환

DATEPART : 자세한데이터형식반환

AVG : 편균값

SUM : 총합

COUNT : 개수리턴

MAX/MIN : 최대/최소값

COMPUTE : 특정결과를별도로출력

SELECT * FROM auction COMPUTE avg(price)

COMPUTE BY : 특정타입별로출력

SELECT * FROM auction COMPUTE avg(price) BY price_type

DDL (Data Definition Language)

DML (Data Manipulation Language)

DCL (Data Control Language)

SELECT ~ INTO

선택된결과로새로운테이블생성

SELECT * INTO Test2 FROM TestDB.Test WHERE 1=2 -- 동일한구조로새로운테이블생성

BULK INSERT

대용량데이터파일을테이블로insert 하는작업

1. 테이블의데이터를txt 파일로출력

c:\> bcp "테이블이름" out "bulk.txt" -c -S"." -U "Sa" -P "암호"

2. CREATE TABLE 문으로테이블생성

3. BULK INSERT

BULK INSERT Test FROM 'C:\bulk.txt'

UPDATE ... FROM

update할데이터를join문을통해가져올수있음

UPDATE ... FROM 대신서브쿼리를사용가능

UPDATE Test1 SET t1.name = t2.name

FROM Test1 t1, Test2 t2 where t1.id=t2.id

UPDATE된값확인하기

DECLARE @var int

UPDATE Test SET @var = id = 2

select @var

UPDATE로SWAP

DECLARE @var int

UPDATE Test SET @var = col1, col1 = col2, col2 = @var

TRUNCATE

DROP 보다빠른테이블삭제하며identity 컬럼을초기화시킴

로그를남기지않으므로다음의경우에해당되는테이블엔사용하면안된다.

다른테이블에서참조된경우

트리거설정된경우

복제작업에참여하고있는경우

인덱스된뷰에참여하고있는경우

그러므로운영서버에선비권장사항이다.

TRUNCATE TABLE Test

T-SQL (Transact-SQL)

[변수사용]

DECLARE @num INT;

DECLARE @name nvarchar(10), @address nvarchar(50);

DECLARE @TestTable table(

id INT NOT NULL,

name varchar(50)

);

SET @name = N'bill'; -- N은유니코드를의미

PRINT @name -- 메시지로출력

SELECT @name -- 테이블형식의출력

[주석]

/** .. **/

[시스템전역변수(구성함수)]

@@CONNECTIONS : SQL 서버시작이후연결시도개수

@@MAX_CONNECTIONS : 동시연결가능한사용자수

@@CPU_BUSY : SQL 서버시작이후동작한시간

@@DATEFIRST : 이번주의시작요일

@@DBTS : 현재db의마지막TIMESTAMP 반환값

@@CURSOR_ROWS : 커넥션에서커서가마지막으로오픈한로우의개수

@@ROWCOUNT : 구문실행이반영된로우의개수

@@FETCH_STATUS : 현재오픈된커서를FETCH 한후상태값반환

@@IDENTITY : 테이블이IDENTITY 컬럼을가질때이값이마지막INSERT 된후의IDENTITY값반환

@@NESTLEVEL : 저장프로시저실행시프로시저내에서호출이중첩된수를리턴

SELECT @@CONNECTIONS -- 출력

SELECT * FROM Test

SELECT @@ROWCOUNT -- select한row 개수

[일괄처리]

여러개의SQL문을묶어(batch) 한번에처리

여러개의SQL문을일괄처리로보내면전체SQL문에대해하나의실행계획으로변환한다.

SELECT .. GO SELECT .. GO 는2개의실행계획을갖지만

SELECT .. SELECT .. GO 는1개의실행계획을갖는다.

GO 문뒤에숫자를주어반복실행이가능하다

INSERT Test(name) VALUES ('hi')

GO 100 -- INSERT문100번실행

[BEGIN ... END]

--블록지정

DECLARE @GENDER NCHAR(1)

SET @GENDER = N'남'

IF @GENDER = '남'

BEGIN

SELECT '남자'

END

[IF ELSE]

DECLARE @GENDER NCHAR(1)

SET @GENDER = N'남'

IF @GENDER = '남'

BEGIN

SELECT '남자'

END

ELSE

BEGIN

SELECT '여자'

END

[CASE]

DECLARE @AGE INT

SET @AGE = 40

SELECT (

CASE

WHEN @AGE = 10 THEN '10대'

WHEN @AGE = 20 THEN '20대'

WHEN @AGE = 30 THEN '20대'

ELSE '기타'

END

)

[WHILE, BREAK, CONTINUE]

DECLARE @I INT

DECLARE @TOTAL INT

SET @I = 1

SET @TOTAL = 0

WHILE (@I <= 100)

BEGIN

IF (@I %2) = 0

BEGIN

SET @I = @I + 1

CONTINUE

END

IF @I > 10

BREAK

SET @TOTAL = @TOTAL + @I

SET @I = @I + 1

END

SELECT @TOTAL

[RETURN]

RETURN @I

[GOTO]

GOTO MyErr

MyErr:

SELECT 'ERROR'

[TRY CATCH]

DECLARE @I INT

BEGIN TRY

SET @I = 10/0

END TRY

BEGIN CATCH

SET @I = 0

PRINT CAST(ERROR_NUMBER() AS VARCHAR) -- 에러번호

PRINT ERROR_MESSAGE() -- 에러메시지

PRINT CAST(ERROR_SEVERITY() AS VARCHAR) -- 에러심각도

PRINT CAST(ERROR_LINE() AS VARCHAR) -- 에러행번호

PRINT ISNULL(ERROR_PROCEDURE(), '없음') -- 에러프로시저

END CATCH

[WAITFOR]

WAITFOR DELAY '00:00:01' --1초sleep

WAITFOR TIME '10:10:10' --10시10분10초가될때까지sleep

[SP_ADDMESSAGE, SP_DROPMESSAGE, FORMATMESSAGE]

-- 사용자정의오류메시지등록

EXEC SP_ADDMESSAGE 50001, 15, N'%S user error message', US_ENGLISH;

EXEC SP_ADDMESSAGE 50001, 15, N'%1! 사용자에러메시지', 한국어;

-- 등록된사용자정의오류메시지확인

SELECT * FROM SYSM.MESSAGE WHERE MESSAGE_ID = 50001

-- 에러메시지에문자열정의

SET LANGUAGE 한국어;

SELECT FORMATMESSAGE(50001, N'SQL 서버');

SET LANGUAGE US_ENGLISH;

SELECT FORMATMESSAGE(50001, N'SQL SERVER');

-- 사용자정의오류미시지삭제

EXEC SP_DROPMESSAGE 50001, 한국어;

EXEC SP_DROPMESSAGE 50001, US_ENGLISH;

[RAISERROR]

-- 메시지ID 혹은임의메시지로에러를발생시며이벤트로그에서확인가능

RAISERROR(N'긴급에러발생%s 프로시저%d 줄.', -- 메시지내용

10, -- 중요도

1, -- 상태

N'Student', -- 첫번째인자

25) -- 두번째인자.

WITH LOG;

[PIVOT]

-- GROUP BY와CASE WHEN의혼합기능

USE AdventureWorks

SELECT TerritoryID 지역번호, [279] AS 영업, [282] AS 영업, [277] AS 영업

FROM

(

SELECT TerritoryID, SalesOrderID, SalesPersonID

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IN(279, 282, 277)

) X

PIVOT

(

COUNT(SalesOrderID)

FOR SalesPersonID IN([279], [282], 277])

) AS X_PIVOT

ORDER BY TerritoryID

[UNPIVOT]

-- PIVOT이나유사형태의테이블을펼쳐주는기능

-- PIVOT 테이블생성

SELECT * INTO PVT_TABLE

FROM (

SELECT TerritoryID 지역번호, [279] AS 영업, [282] AS 영업, [277] AS 영업

FROM

(

SELECT TerritoryID, SalesOrderID, SalesPersonID

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IN(279, 282, 277)

) X

PIVOT

(

COUNT(SalesOrderID)

FOR SalesPersonID IN([279], [282], 277])

) AS X_PIVOT

) AS COPY

-- UNPIVOT

SELECT 지역번호, 영업자, 주문개수

FROM PVT_TABLE

UNPIVOT (주문개수

FOR 영업자IN (영업, 영업, 영업)

) AS UNPVT_TABLE

[ROW_NUMBER()]

-- ROW에대한시퀀스넘버

SELECT ROW_NUMBER() OVER (ORDER BY ModifiedDate) as RowNum, *

FROM Person.Contact

[RANK()]

-- 동일순위존재시다음순위를건너뛴다. (EX. 1, 1, 3, 4 ..)

CREATE TABLE 주문( --샘플TABLE

지역NVARCHAR(10),

판매량INT

)

INSERT 주문VALUES (N'서울', 300);

INSERT 주문VALUES (N'전남', 300);

INSERT 주문VALUES (N'경기', 250);

INSERT 주문VALUES (N'충북', 250);

INSERT 주문VALUES (N'인천', 100);

INSERT 주문VALUES (N'서울', 220);

INSERT 주문VALUES (N'전남', 320);

SELECT RANK() OVER (ORDER BY 판매량DESC) 순위, 지역, 판매량FROM 주문

[DENSE_RANK()]

-- 동일순위가존재해도다음순위를건너뛰지않는다. (EX. 1, 1, 2, 3, 4..)

SELECT DENSE_RANK() OVER (ORDER BY 판매량DESC) 순위, 지역, 판매량FROM 주문

[NTILE()]

-- 지정한숫자에맞게정렬된데이터를그룹핑한다.

SELECT NTILE(4) OVER (ORDER BY 판매량DESC) 순위, 지역, 판매량FROM 주문

[PARTITION BY]

-- 파티션별순위지정시사용

SELECT RANK() OVER (PARTITION BY 지역ORDER BY 판매량DESC) 순위, 지역, 판매량

FROM 주문

ORDER BY 지역, 순위, 판매량

[CTE : Common Table Expression]

-- CTE를사용한쿼리

WITH EmpCTE(EmployeeID, Title, LoginID)

(

SELECT EmployeeID, Title, LoginID

FROM HumanResources.Employee

)

SELECT * FROM EmpCTE

WHERE EmployeeID <= 10

-- CTE를사용하지않는위와동일한쿼리

SELECT *

FROM (

SELECT EmployeeID, Title, LoginID

FROM HumanResouerces.Employee

) as EmpCTE

where EmployeeID <= 10

-- 재귀적CTE (자기자신을참조)

WITH EmpCTE(MgrID, EmpID)

(

SELECT E.ManagerID, E.EmployeeID

FROM HumanResources.Employee E

WHERE ManagerID = 3

UNION ALL

SELECT E.ManagerID, E.EmployeeID

FROM HumanResources.Employee E

JOIN EmpCTE ON EmpCTE.EmpID = E.ManagerID

)

SELECT * FROM EmpCTE;

-- 도시별인주를집계해서상위다섯개도시의평균은얼마인가?

WITH CityCTE(City, Cnt)

(

SELECT City, Count(*) Cnt FROM Person.Address GROUP BY City

), Top5CityCTE(City, Cnt)

(

SELECT TOP 5 City, Cnt FROM CityCTE ORDER BY Cnt DESC

), Top5AvgCityCTE(AvgCnt)

(

SELECT AVG(Cnt) FROM Top5CityCTE

)

SELECT * FROM Top5AvgCityCTE

[MAXRECURSION]

-- CTE 사용시무한루프를회피하기위해최대반복수지정

-- 무한반복: 0, 최대값: 32767

SELECT * FROM Top5AvgCityCTE OPTION (MAXRECURSION 10) -- 최대10번까지반복

[CASE .. WHEN]

-- 간단한조건문

SELECT * FROM TestTable

WHERE (CASE col1 WHEN 'c1' THEN c1 ELSE c2 END) > 10

[동적쿼리: EXEC, SP_EXECUTESQL]

-- EXEC : 조합한문자열로쿼리실행. 실행계획재사용불가

EXEC("SELECT * FROM Employee")

-- EXECUTE AS : 특정권한으로쿼리실행

EXECUTE ('CREATE TABLE TestTable (id INT, name VARCHAR(10));')

AS USER = 'user1';

-- EXECUTE AT : 특정서버에서쿼리실행

EXEC sp_addlinkedserver 'SERVER1', 'SERVER2'

EXECUTE ('CREATE TABLE TestTable (id INT, name VARCHAR(10));')

AT SERVER1;

-- SP_EXECUTESQL : 쿼리바인딩으로실행계획재사용가능

DECLARE @SQL AS NVARCHAR(100)

SET @SQL = 'SELECT @emp_name=fname+ CHAR(13) +lname FROM Employee WHERE emp_id=@emp_id'

DECLARE @EMP_NAME AS VARCHAR(100)

EXEC SP_EXECUTESQL

@stmt = @SQL,

@params = N'@emp_id AS VARCHAR(10), @emp_name VARCHAR(100) out',

@emp_id = 'PMA42628M';

@emp_name out;

SELECT @emp_name;

뷰테이블(VIEW TABLE)

[제약사항]

뷰정의에사용된개체에대한SELECT 권한이있어야사용할수있다.

(GRANT SELECT ON emp_jobs TO guest)

뷰정의에사용되는SELECT문은ORDER BY, INTO, COMPUTE, COMPUTE BY 문사용불가.

임시테이블에대한뷰생성불가.

트리거, 색인생성불가.

select_list로최대1024개컬럼, 최대250 컬럼참조가능.

[뷰생성/수정/삭제]

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]

[ WITH <view_attribute> [ ,...n ] ]

AS select_statement

[ WITH CHECK OPTION ] [ ; ] -- 뷰에대해실행된모든데이터수정문이select_statement 내의기준집합을준수하도록설정

<view_attribute> ::=

{

[ ENCRYPTION ] -- CREATE VIEW 문의텍스트가포함된sys.syscomments의항목을암호화

[ SCHEMABINDING ] -- 뷰에서참조하는개체들의변경을금지

[ VIEW_METADATA ] }

ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ]

[ WITH <view_attribute> [ ,...n ] ]

AS select_statement

[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::=

{

[ ENCRYPTION ]

[ SCHEMABINDING ]

[ VIEW_METADATA ] }

DROP VIEW [ schema_name . ] view_name [ ...,n ] [ ; ]

[뷰정보확인]

SELECT * FROM SYS.VIEWS

SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID('TVIEW')= OBJECT_ID --TVIEW는뷰테이블이름

SP_HELPTEXT TVIEW -- VIEW 생성쿼리확인

SP_DEPENDS TVIEW -- VIEW 참조개체확인

SELECT * FROM INFORMATION_SCHEMA.TABLES

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

SELECT * FROM INFORMATION_SCHEMA.VIEWS

SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

[Indexed View]

[Partitioned View]

[DMV : Dynamic Management View]

모니터링에대한시스템뷰로서버재시작시모든정보초기화됨(SQL Server 2005)

Dynamic Management Views and Function : http://msdn.microsoft.com/en-us/library/ms188754(v=SQL.90).aspx

SELECT

name as [DMV/DMF Name],

type_desc as [Type],

[DMV Category] =

CASE

WHEN name LIKE 'dm_audit%' THEN 'Auditing Related DMV'

WHEN name LIKE 'dm_cdc%' THEN 'Change Data Capture Related DMV'

WHEN name LIKE 'dm_clr%' THEN 'CLR Related DMV'

WHEN name LIKE 'dm_db%' THEN 'Database&Objects Related DMV and DMF'

WHEN name LIKE 'dm_exec%' THEN 'Execution Related DMV and DMF'

WHEN name LIKE 'dm_xe%' THEN 'Extended Events Related DMV'

WHEN name LIKE 'dm_fts%' THEN 'Full-Text Search Related DMV and DMF'

WHEN name LIKE 'dm_filestream%' THEN 'FileStream Related DMV'

WHEN name LIKE 'dm_io%' THEN 'I/O Related DMV and DMF'

WHEN name LIKE 'dm_sql%' THEN 'Object Ref Related DMV and DMF'

WHEN name LIKE 'dm_provider%' THEN 'Provider Related DMV and DMF'

WHEN name LIKE 'dm_qn%' THEN 'Query Notifications Related DMV'

WHEN name LIKE 'dm_repl%' THEN 'Replication Related DMV'

WHEN name LIKE 'dm_resource%' THEN 'Resource Governor Related DMV'

WHEN name LIKE 'dm_broker%' THEN 'Service Broker Related DMV'

WHEN name LIKE 'dm_os%' THEN 'SQL Server OS Related DMV'

WHEN name LIKE 'dm_server%' THEN 'Server Audit Related DMV and DMF'

WHEN name LIKE 'dm_tran%' THEN 'Transactions Related DMV'

WHEN name LIKE 'dm_cryp%' THEN 'Security Related DMV and DMF'

WHEN name LIKE 'dm_cdc%' THEN 'Change Data Capture Related DMV'

WHEN name LIKE 'dm_database%' THEN 'Transparent Data Encryption Related DMV'

ELSE 'Other DMV'

END

FROM sys.system_objects

WHERE name LIKE 'dm[_]%'

ORDER BY [DMV Category]

[SQL 서버실행가능한작업수확인]

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

[CPU를많이사용하는상위50개의쿼리확인]

SELECT top 50

sum(qs.total_worker_time) as total_cpu_time,

sum(qs.execution_count) as total_execution_count,

count(*) as number of statements,

qs.plan_handle

FROM

sys.dm_exec_query_stats qs

GROUP BY qs.plan_handle

ORDER BY sum(qs.total_worker_time) DESC

[현재실행중인쿼리문확인하기]

SELECT

r.session_id, status,

substring(qt.text, r.statement_start_offset/2,

(case when r.statement_end_offset = -1

then len(convert(nvarchar(max), qt.text)) * 2

else r.statement_end_offset end - r.statement_start_offset)/2) as query_test,

qt.dbid,

qt.objectid,

r.cpu_time,

r.total_elapsed_time,

r.reads,

r.writes,

r.logical_reads,

r.scheduler_id

FROM

sys.dm_exec_requests r CROSS apply sys.dm_exec_sql_test(sql_handle) as qt

WHERE

r.session_id > 50

ORDER BY r.scheduler_id, r.status, r_session_id

[옵티마이저정보보기]

SELECT * FROM SYS.DM_EXEC_QUERY_OPTIMIZER_INFO

[메모리분배정보확인]

SELECT type, sum(multi_pages_kb)

FROM sys.dm_os_memory_clerks

WHERE multi_pages_kb != 0

GROUP BY type

[페이지래치대기정보확인]

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms

FROM sys.dm_os_wait_stats

WHERE wait_type LIKE 'PAGEIOLATCH%'

ORDER BY wait_type

Stored Procedure (저장프로시저)

sp_help JOB -- JOB 테이블의컬럼, 타입, index 등의정보출력

sp_databases -- 데이터베이스출력

sp_fkeys JOB -- JOB 테이블에대한기본키정보반환

sp_helptext sp1 -- sp1의프로시져소스출력

sp_depends JOB -- JOB 테이블과연관된개체출력

sp_lock -- lock 정보출력. select object_name(objid컬럼값) 으로확인

sp_who -- 현재접속정보

**. 프로시저실행도EXEC보단SP_EXECUTESQL로실행하는것이좋음. (실행계획컴파일문제)

[프로시저작성]

-- procedure 생성

CREATE PROC sp1

@id varchar(10) = 'aaaaaa', -- 입력값, 기본값지정

@cnt INT OUTPUT -- 출력값

SET @cnt = (SELECT COUNT(*) FROM TestTable WHERE id = @id)

IF cnt = 0

BEGIN

RETURN -1 -- 리턴값사용

END

-- procedure 호출

DECLARE @cnt INT -- 받을출력값

DECLARE @ret INT -- 받을리턴값

EXEC @ret = sp1 12, @cnt OUTPUT

SELECT @cnt, @ret

-- procedure 삭제

DROP PROC sp1

[자동실행stored procedure]

-- SQL Server 시작시시간을기록하는자동실행SP 샘플

USER master

CREATE TABLE SERVER_LOG ( START_DT DATETIME ) -- 시작시간을기록할테이블생성

CREATE PROC SP1 -- 자동실행할프로시저생성(매개변수갖지못함)

INSERT master.dbo.SERVER_LOG VALUES(GETDATE())

-- 옵션변경

EXEC SP_CONFIGURE 'show advanced options', '1'

RECONFIGURE WITH OVERRIDE

EXEC SP_CONFIGURE 'scan for startup procs', '1'

RECONFIGURE WITH OVERRIDE

-- 자동실행등록

EXEC SP_PROCOPTION 'SP1', 'startup', 'ON'

[캐시된프로시저삭제]

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

Trigger (트리거)

[DML 트리거]

-- 테이블insert, update시실행되는트리거

CREATE TRIGGER trigger1

ON TestTable FOR INSERT, UPDATE

PRINT '성공하셨습니다.'

[DDL 트리거]

-- 전체서버에대해계정생성차단트리거

CREATE TRIGGER DDL_SP1

ON ALL SERVER -- 전체서버에대해

FOR CREATE_LOGIN

PRINT '새로운계정생성제한'

ROLLBACK TRAN; -- 롤백처리

[트리거순서지정]

SP_SETTRIGGERORDER 'SP1', 'FIRST', 'INSERT'

SP_SETTRIGGERORDER 'SP2', 'LAST', 'INSERT'

CURSOR (커서)

OPEN cur -- 커서오픈

-- 커서로데이터획득

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM cur

END

CLOSE cur -- 커서닫기. 닫기만한경우다시OPEN 가능

DEALLOCATE cur -- 커서자원해제

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

[샘플]

CREATE FUNCTION Func1(@STR nvarchar(100)) -- function 생성

RETURN NVARCHAR(100)

BEGIN

RETURN (@str + N' 입력받음')

END

SELECT dbo.Func1('hi') -- function 호출

DROP FUNCTION Func1 -- function 삭제

Transaction (트랜잭션)

BEGIN TRAN -- 트랜잭션시작

COMMIT TRAN -- 커밋

ROLLBACK TRAN -- 롤백

[Savepoint]

SAVE TRAN savepoint1 -- savepoint 지정

ROLLBACK TRAN savepoint1 -- savepoint 위치로롤백

Lock (락)

잠금모드: http://msdn.microsoft.com/ko-kr/library/ms175519(v=SQL.90).aspx

1. sp_who 혹은sp_sho2 프로시저로사용자별정보확인

2. sp_lock 프로시저로락정보출력

3. DBCC INPUTBUFFER(spid) 로마지막사용한명령문확인

분산쿼리

이기종DB와연결및연관작업을가능하게해주며다양한데이터저장소를SQL문으로이용가능

분산쿼리이용방법. Linked server names(링크드서버) 사용

분산쿼리이용방법. Ad hoc connector names(임시커넥터이름) 사용

분산쿼리: http://msdn.microsoft.com/ko-kr/library/ms188721(v=SQL.90).aspx

기타

[네트워크상에존재하는SQL 서버들찾아보기]

C:\> isql -L

C:\> osql -L

C:\> sqlping localhost -- sql server에ping 날려보기

System Architecture

[파일]

Primary Data Files(MDF) : 기본디바이스파일로주데이터파일.

Secondary Data Files(NDF) : 필수는아니지만성능향상을위해쓰이는보조데이터파일.

Log Files(LDF) : 로그파일

[데이터파일구조]

Page(페이지)

: 기본적인IO 단위. 8KB(8192 byte)로96 byte의페이지헤더와8096 byte의데이터공간을사용.

Extent(익스텐트)

: 8개의페이지로이루어진64KB의기본할당단위. 테이블, 인덱스저장관리에쓰임.

Mixed Extent(혼합익스텐트)

: 각각64KB 이하의공간이필요한여러테이블또는인덱스저장용

Uniform Extent(단일익스텐트)

: 균일테이블또는인덱스저장용

IAM 페이지(Index Allocation Map Page)

: 테이블, 인덱스에사용되는Extent에대한정보가들어있는할당페이지

Index (인덱스)

[인덱스]

CREATE CLUSTERED INDEX IX_id ON TestTable(id) -- 인덱스생성

DROP INDEX TestTable.IX_id -- 인덱스삭제

SP_HELPINDEX TestTable -- 테이블의인덱스확인

[unique index (유니크인덱스)]

중복값을갖을수없는인덱스

CREATE UNIQUE CLUSTERED INDEX IX_id ON TestTable(id) -- 유니크인덱스생성

[composite index (복합인덱스)]

여러컬럼을지정한인덱스로컬럼사용에따라인덱스를타지않을수도있다.

CREATE INDEX IX_id ON TestTable(id, username) -- 복합인덱스

SP_HELPINDEX TestTable -- 복합인덱스확인

[covered index (커버드인덱스)]

쿼리에사용된컬럼이모두인덱스인경우를말하여이쿼리를커버드쿼리라한다.

[인덱스옵션]

FillFactor : 리프레벨의페이지에여유공간을비율로지정하여페이지분할방지

Pad_index : 넌리프레벨의페이지에여유공간비율을두어페이지분할방지

DROP_EXISTING : 존재하는인덱스의옵션값을바꾸어재구축할경우사용. 페이지단편화현상을줄이기위한목적

SORT_IN_TEMPDB : tempDB에중간정렬결과를저장해서인덱스를생성하는옵션

[인덱스단편화해소방법]

1. 해당테이블및DB 백업후리스토어

2. DROP INDEX, CREATE INDEX로인덱스재생성

3. CREATE INDEX시DROP_EXISTING 옵션으로재생성

4. DBCC DBREINDEX로인덱스재구축

5. DBCC INDEXDEFRAG로인덱스단편화조절

DBCC DBREINDEX

FILLFACTOR 값만바꾸어인덱스재생성지원

Ex) DBCC DBREINDEX (TestTable, IX_id, 80) -- 인덱스한개만재구축

Ex) DBCC DBREINDEX (TestTable) -- 테이블의모든인덱스재구축

ALTER INDEX .. REBUILD

INDEX 리빌드구문

Ex) LATER INDEX ALL ON TestTable REBUILD

DBCC SHOWCONTIG

인덱스단편화정보획득

Ex) DBCC SHOWCONTIG(TestTable) -- 테이블인덱스단편화정보출력

DBCC INDEXDEFRAG

인덱스단편화제거(Index Defragmentation)

Ex) DBCC INDEXDEFRAG(TestDB, TestTable.IX_id) -- 특정인덱스의단편화제거

[인덱스사용시주의사항]

1. CLUSTERED INDEX의결정

테이블에넌클러스터드인덱스는여러개설정이가능하지만클러스터드인덱스는하나만생성가능.

정렬되어있는컬럼, 사용빈도가높고중복이적은컬럼, 컬럼길이가너무길지않은컬럼에사용권장

2. FILLFACTOR, PAD_INDEX의사용

데이터변경이빈번한테이블은페이지분할방지및쿼리성능을위해해당옵션사용권장

3. 테이블당적정인덱스수는4~5개사이

읽기전용의테이블이나뷰는좀더많이생성해도나쁘지않다.

4. 복합인덱스(Composite Index) 주의사항

인덱스가너무커지지않도록주의해야한다.

사용빈도가높은컬럼이인덱스생성시앞에위치해야한다.

5. 문자열보단정수컬럼에인덱스생성이유리

컬럼의길이가긴경우정수컬럼의대리키생성도하나의방법이다.

6. 쿼리가인덱스를사용하도록작성

Query Optimizer (질의최적화기)

SQL Server는Cost based optimizer(비용기반최적화방법) 사용.

SET SHOWPLAN_ALL ON -- T-SQL을실행하지않고예상실행계획출력

SET SHOWPLAN_TEXT ON -- T-SQL을실행하지않고예상실행계획을텍스트형식으로출력

SET STATISTICS IO ON -- 디스크에대한IO 정보표시

SET STATISTICS PROFILE ON -- 프로필정보표시

SET STATISTICS TIME ON -- 각명령문에따른수행시간(MS) 출력

SET FORCEPLAN ON -- 조인쿼리사용시FROM절의테이블순서데로조인순서가이루어지도록실행계획을강제화함.

[분포통계]

-- 인위적으로새로운분포통계생성

CREATE STATISTICS STAT_id

ON TestTable(id) WITH SAMPLE 5 PERCENT -- 5%의데이터로분포통계생성

DBCC SHOW_STATISTICS(STAT_ID, id) -- 분포통계확인(테이블, 인덱스명)

UPDATE STATISTICS TestTable -- 분포통계업데이트

[힌트절]

쿼리힌트(Transact-SQL) : http://msdn.microsoft.com/ko-kr/library/ms181714(v=SQL.90).aspx

-- 힌트절지정방식1. WITH 문

SELECT * FROM TestTable WITH(INDEX = IX_id)

WHERE id > 10

-- 힌트절지정방식2. WITH 절생략

SELECT * FROM TestTable(INDEX = IX_id)

WHERE id > 10

[SQL Server Profiler]

SQL Server 설치시설치되며모든T-SQL의모니터링및분석자료생성툴.

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

긴문장으로구성된열의내용을검색할때외부인덱스를사용해빠른시간내에검색하기위한외부기능

http://msdn.microsoft.com/ko-kr/library/ms142571(v=SQL.90).aspx

Backup (백업)

풀백업> 파일그룹백업> 파일그룹차등백업> 차등백업> 로그백업

Full Backup (전체백업)

BACKUP DATABASE TestDB TO disk = "C:\testdb.bak"

Differential Backup (차등백업)

전체백업후변경된데이터만백업

BACKUP DATABASE TestDB To disk = "C:\testdb.bak" WITH DIFFERENTIAL

Transaction Log Backup (트랜잭션로그백업)

전체백업후로그데이터백업

BACKUP LOG TestDB To disk = "C:\tran.log"

Partial Backup (부분백업)

읽기전용파일그룹을제외한전체백업

프라이머리파일그룹과읽기/쓰기파일그룹만백업

BACKUP DATABASE TestDB READ_WRITE_FILEGROUPS TO disk = "C:\testdb.bak"

Mirror Backup (미러백업)

2개의백업장치에백업. 최초미러백업시WITH FORMAT 옵션지정필수

BACKUP DATABASE TestDB TO disk = 'C:\testdb.bak' MIRROR TO = 'D:\testdb.bak' WITH FORMAT

복사전용백업

전체백업을받아새로운복사DB 생성시유용.

기존및향후백업에어떠한영향도주지않음.

BACKUP DATABASE TestDB TO disk = 'C:\copyTestDB.bak' WITH COPY_ONLY

옵션

Checksum (백업확인기능)

백업데이터가올바른지WITH CHECKSUM으로확인하여복원시에도사용가능.

BACKUP DATABASE TestDB TO disk = 'C:\testdb.bak' WITH CHECKSUM

백업매체초기화

기본백업내용을삭제하여새로백업.

초기화하지않는옵션은NOINIT.

BACKUP DATABASE TestDB TO disk = 'C:\testdb.bak' WITH INIT

다중백업장치초기화

WITH FORMAT 옵션으로여러백업장치초기화후백업수행.

기본값은WITH NOFORMAT 으로초기화하지않는다.

BACKUP DATABASE TestDB TO disk = 'C:\testdb.bak', 'D:\testdb.bak' WITH FORMAT

비밀번호지정

BACKUP DATABASE TestDB TO disk = 'C:\testdb.bak' WITH PASSWORD = '비밀번호'

오류발생무시

CONTINUE_AFTER_ERROR 옵션으로잘못된체크섬발생시무시하고백업진행

기본값은STOP_ON_ERROR 로에러발생시백업중단

진행률표시

WITH STAT

DB 상태이상시로그백업진행

WITH NO_TRUNCATE

RESTORE (복구)

전체/차등복원

RESTORE DATABASE TestDB FROM disk = 'C:\testdb.bak'

트랜잭션로그복원

RESTORE LOG TestDB FROM disk = 'C:\tran.log'

옵션

WITH NORECOVERY

복원작업이이후에도남아있어서복원완료상태가되지않게하기위한옵션.

WITH RESTRICTED_USER

복원후DB_OWNER, DBCREATOR, SYSADMIN에속하는사용자만접근가능하도록함

WITH MOVE

복원시데이터파일이동

RESTORE DATABASE TestDB FROM disk = 'C:\testdb.bak'

WITH MOVE 'DB_데이터' TO 'D:\data.mdf'

WITH MOVE 'DB_로그' TO 'D:\log.ldf'

WITH CONTINUE_AFTER_ERROR

복원시오류무시하고진행

WITH REPLACE

동일한파일이있더라도덮어씀

WITH STOPAT = '날짜및시간'

특정시점까지데이터복원

FROM DATABASE_SNAPSHOT = '스냅샷명'

스냅샷에서복원

RESTORE DATABASE TestDB FROM DATABASE_SNAPSHOT = '스냅샷명'

DBCC log

특정DB의트랜잭션로그보기

Snapshot

CREATE DATABASE snapshot1

ON (NAME = TestDB, FILE = 'C:\testdbSnapshot.mdf')

AS SNAPSHOT OF TestDB

'DB' 카테고리의 다른 글

문자열 입력받아 split 하기.. (0)	2012.09.06
암호화 예제 (0)	2012.04.15
백업/복구 (0)	2012.04.12
MSSQL SET 명령(옵션) 모음 (0)	2011.02.02
RAID (0)	2011.01.29
mysql (0)	2010.12.13
Oracle architecture (0)	2010.07.31
PL/SQL 기본 (0)	2010.07.30

MSSQL

'DB' 카테고리의 다른 글

BLOG VISITORS

티스토리툴바