나른한 오후~

SELECT * INTO '생성될 테이블명' FROM '복사할 테이블 명'
ex) SELECT * INTO NEWTABLENAME FROM OLDTABLE

alter table [tableName] add  [filedName] VARCHAR (50)  

인덱스는 기본적으로 생성해두는것이 효율성에 좋습니다.
데이터가 적을 경우는 기본적인 인덱스로 결과를 출력할 수 있도록 하고, 데이터가 많아질 수록 쿼리에 맞는 인덱스를 걸어주면 효율적입니다.

인덱스는 일반적으로 한 테이블당 5개전후로 많이 잡습니다. 그 이상을 걸면 데이터의 양에 오히려 좋지 않은 영향을 줄 수 있기 때문입니다.

인덱스는 기본적으로 전체 테이블의 10%정도를 가져오는데 효과적입니다.
하지만 1000만건중에 100만건을 가져오는데는 인덱스가 효과적이라고 보기는 힘들지만 기본적은 인덱스는 필수입니다.

현재 느리다고 생각되는 쿼리가 있다면 다음의 과정으로 인덱스를 만들어보세요.

1. 쿼리 분석
현재 이 서버에 돌아가고 있는 쿼리를 알기 위해 다음을 실행합니다.
select * from master..syscacheobjects
sql이란 부분에 현재 캐시되어있는 쿼리가 보입니다.
이 쿼리들을 돌려가며 느리다고 느껴지는 쿼리를 찾습니다.

2. 기본 인덱스, 부하가 걸리는 인덱스를 체크
sp_helpindex A
이 명령으로 A테이블의 인덱스를 찾을 수 있습니다.

3. 인덱스 생성
create index A_ix01 on A(aa)
처음엔 고유증가 번호등의 잘 변하지 않는 필드를 Clustered Primary Key로 잡아야 합니다. 최종적으로 데이터를 찾아가는 길을 만들기 위함입니다. 이게 없으면 전체를 메모리에 띄워야 하기 때문에 많은 리소스를 잡아먹습니다.
Join문과 Where에서 찾는 필드명은 index로 걸어주는 것이 좋습니다. text등의 900바이트가 넘는 데이터는 index가 생성이 되지 않으니 주의 하세요.
index를 걸어줄 때 필드를 여러개를 한번에 지정하는 방법이 있고 한개씩 여러번 지정하는 방법이 있습니다.

한번에 여러개를 지정하는 방법의 예:
create index A_ix01 on A(aa, bb)

한개씩 여러번 지정하는 방법의 예:
create index A_ix01 on A(aa)
create index A_ix02 on A(bb)

위의 방법은 aa순으로 정렬된 뒤에 bb순으로 정렬하는 것입니다. 때문에 where절등에서 bb를 먼저찾거나 bb만 찾게 되는 경우 위의 인덱스는 타지 않습니다.
대신 아래처럼 한개씩 여러번 지정하는 것에 비해 aa,bb순으로 찾는 경우 가장 빠른 효율을 줍니다. 따라서 위방법과 아래방법은 전혀 다른 인덱스가 됩니다. 참고하면서 만드세요.

4. 테스트
인덱스를 만들어가면서 대량의 데이터의 쿼리를 돌려봅니다.
인덱스를 바꿔가면서 가장 맞는 인덱스를 생성합니다.
최초로 돌릴 경우는 메모리에 올리는 시간 때문에 느릴 수 있습니다. 때문에 쿼리를 여러번 돌린후 마지막 시간을 재는게 좋습니다.

5. 불필요한 인덱스 삭제
drop index AA.AA_ix01
반드시 테이블명.인덱스명 의 형식으로 지워야 지워집니다.

* syscacheobjects에 비슷한 쿼리가 많다면 그 쿼리는 Static SQL을 이용하는 것이 좋습니다. Static SQL을 이용하면 같은 쿼리의 변수만 바뀌는 식의 쿼리를 하나의 쿼리로 인식하여 처리하므로 비슷한 캐시를 줄여 캐시를 활용할 공간이 넓어집니다.

ALTER TABLE tabFlights
ALTER COLUMN LeavingFrom VARCHAR(50) COLLATE Korean_Wansung_CI_AI
GO

ALTER TABLE tabFlights
ALTER COLUMN LeavingFrom VARCHAR(50) COLLATE Korean_Wansung_CI_AS
GO

CREATE VIEW VORDERS
AS
SELECT ORDERID, CUSTID
FROM DBO.ORDERS
-- 테스트로 함 불러보고

EXEC SP_ADDLOGIN 'TEST_USER', '1234', PERFORMANCE
-- 로그인 생성(아이디,패스워드,데이터베이스)

EXEC PERFORMANCE.DBO.sp_grantdbaccess TEST_USER
-- 엑세스 가능하게 만들고

GRANT SELECT ON VORDERS TO TEST_USER
-- 딱 뷰 하나만 SELECT 하는 권한 주고
GO

SETUSER 'TEST_USER'
-- TEST_USER 로 가장해서 들어간 뒤 테스트
GO

SELECT * FROM VORDERS
-- 성공

SELECT * FROM ORDERS
-- 실패

SETUSER
-- 원래 권한 유저로 돌아오기

MSSQL] 백업 , 복구, 로그자르기 MS_SQL

2006/12/10 19:55

http://blog.naver.com/ronghuan/110011923855

--------------------------------------------------------------------------------------
--디바이스(장치)

 --1.디바이스 설치
 sp_addumpdevice 'disk', '장치명', '경로 및 파일'
 
  ----sp_addumpdevice 'disk', 'XERP', 'd:databackup\dbbackup\xerp'
 
 --2.디바이스 삭제
 sp_dropdevice '장치명'
 
  --sp_dropdevice 'XERP'
 
 --3.디바이스 정보
 sp_helpdevice '장치명'
 
  --sp_helpdevice XERP

--BACKUP

1. 전체 BACKUP
    BACKUP DATABASE 'DB명' TO '디바이스명'
    BACKUP DATABASE 'DB명' TO '디바이스명' WITH INIT--(장치내역 HISTORY 삭제)
2. 차등 BACKUP
 BACKUP DATABASE 'DB명' TO '디바이스명' WITH DIFFERENTIAL
3. 로그 BACKUP
 BACKUP LOG 'DB명' TO '디바이스명'

RESTORE HEADERONLY FROM XERP

  BACKUP DATABASE XERP TO XERP -- WITH INIT
  BACKUP DATABASE XERP TO XERP WITH DIFFERENTIAL
  BACKUP LOG XERP TO XERP

--RESTORE

1. RESTORE HEADERONLY FROM '디바이스명(장치명)' 을 하여 복원할 파일의 번호(POSITION)를 알아낸다.

2. RESTORE DATABASE 'DB명' FROM '디바이스(장치)명' WITH FILE = 복원할 파일의 번호(POSITION) , RECOVERY --NORECOVERY(복원해야 할 것이 더 있을때)
   RESTORE LOG 'DB명' FROM '디바이스(장치)명' WITH FILE = 복원할 파일의 번호(POSITION) , RECOVERY --NORECOVERY(복원해야 할 것이 더 있을때)

   WITH INIT 을 한 파일을 복원 할때 = RESTORE DATABASE 'DB명' FROM '디바이스(장치)명'

 --EM을 사용하여 복원하는걸 권장 (IDC에서도 정기적으로 백업을 함)

--로그잘라내기

1. BACKUP LOG 'DB명' TO '디바이스명(장치명)'   --로그백업  
   BACKUP LOG 'DB명' WITH TRUNCATE_ONLY        --로그 잘라내기

/*
 ex)BACKUP LOG XERP TO XERP    
    BACKUP LOG XERP WITH TRUNCATE_ONLY

 위의 두가지 방법 모두 사용할 수 있으나 로그백업을 권장함(단순복구모델일때는 로그 잘라내기를 함)

*/

2. DBCC SHRINKFILE('로그파일명', 파일크기)      --파일크기를 정해주어 파일을 축소하는 방법
   DBCC SHRINKFILE('로그파일명', TRUNCATEONLY)  --불필요한 로그 파일을 잘라 파일을 축소하는 방법  
/*
 ex)DBCC SHRINKFILE ('XERP_LOG',500)
    DBCC SHRINKFILE ('XERP_LOG',TRUNCATEONLY)

       DBCC LOGINFO

 위의 두가지 방법 모두 사용할 수 있으니 TRUNCATEONLY를 더 권장 함

*/

--파일명 알아내기

 EXEC SP_HELPFILE
    SELECT * FROM SYSFILES

그런 녀석들 입니다. 마치 소면과 채소 사이에 숨어있는 진짜 쫄깃한 골뱅이라고도

표현이 가능하겠지요. 갑자기 맥주 한잔이 땡기는군요.

　

첫번째인 변수 선언 입니다.

변수의 선언법은 아시겠죠?

DECLARE @변수명 자료형

하시면 변수가 생성되는 것입니다.

여기에 값을 대입 하려면?

SET @변수명 = 값

이런 식이 되지요.

DECLARE @변수1 int SET @변수1 = 10 SELECT @변수1

간단히 보시면 저런 식이 됩니다. 어렵지 않으시져?

이미 커서와 저장 프로시져 등에서 많이 봐 오셔서 어렵지는 않으실 거에요.

　

다음 IF - ELSE 입니다. 분기라는 것만 생각하세요.

IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15    BEGIN       PRINT 'The following titles are excellent mod_cook books:'       PRINT ' '       SELECT SUBSTRING(title, 1, 35) AS Title       FROM titles       WHERE type = 'mod_cook'    END ELSE    PRINT 'Average title price is more than $15.'

또는 이를 중첩 하신후. ^_^

IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15    BEGIN       PRINT 'The following titles are excellent mod_cook books:'       PRINT ' '       SELECT SUBSTRING(title, 1, 35) AS Title       FROM titles       WHERE type = 'mod_cook'    END ELSE    IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') > $15       BEGIN          PRINT 'The following titles are expensive mod_cook books:'          PRINT ' '          SELECT SUBSTRING(title, 1, 35) AS Title          FROM titles          WHERE type = 'mod_cook'       END

이렇게 구성이 됩니다. if의 끝에 End if가 없지요? ^_^

약간의 차이만 느끼시면 문제 없지요.

느끼시듯이 IF의 구문은

IF Boolean_expression
     { sql_statement | statement_block }
[ ELSE
     { sql_statement | statement_block } ]

이런 식입니다.

주의하실점으로 IF 후 처리한 루틴에서 여러 처리가 들어갈시 반드시

BEGIN - END로 블럭을 지정해 단위별로 수행을 하셔야만 합니다.

BEGIN - END로 묶지 않을시는? 에러가 나거나 첫 단위만 수행합니다.

　

다음은 WHILE구문 입니다.

USE pubs GO WHILE (SELECT AVG(price) FROM titles) < $30    BEGIN       UPDATE titles          SET price = price * 2       SELECT MAX(price) FROM titles       IF (SELECT MAX(price) FROM titles) > $50          BREAK       ELSE          CONTINUE    END    PRINT 'Too much for the market to bear'

일반적으로 이러한 식의 처리로 조건이 맞을때 까지 루프를 돌면서

처리하게 됩니다. 적절한 방식으로 비교 루틴을 두신후 돌리시면 되지요.

WHILE의 구문은

WHILE Boolean_expression
     { sql_statement | statement_block }
     [ BREAK ]
     { sql_statement | statement_block }
     [ CONTINUE ]

의 식입니다.

BREAK는 WHILE구문을 끝내며

CONTINUE는 WHILE 루프를 다시 시작하게 합니다.

　

다음 CASE입니다.

CASE구문 정보를 먼저 봐 보지요.

단순 CASE 함수 CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END 검색된 CASE 함수 CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

단순 처리시 입니다.

USE pubs GO SELECT Category =    CASE type       WHEN 'popular_comp' THEN 'Popular Computing'       WHEN 'mod_cook' THEN 'Modern Cooking'       WHEN 'business' THEN 'Business'       WHEN 'psychology' THEN 'Psychology'       WHEN 'trad_cook' THEN 'Traditional Cooking'    ELSE 'Not yet categorized'    END, CAST(title AS varchar(25)) AS 'Shortened Title', price AS Price FROM titles WHERE price IS NOT NULL ORDER BY type, price COMPUTE AVG(price) BY type GO

역시나 CASE샘플로 비교값을 가지는 케이스 입니다.

USE pubs GO SELECT 'Price Category' =    CASE       WHEN price IS NULL THEN 'Not yet priced'       WHEN price < 10 THEN 'Very Reasonable Title'       WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'    ELSE 'Expensive book!'    END, CAST(title AS varchar(20)) AS 'Shortened Title' FROM titles ORDER BY price GO

이러한 부분 입니다.

　

좀더 유연한 CASE 처리를 위해 문자열 처리 함수와 혼용하는 샘플 입니다.

USE pubs SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+ RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id, Type =    CASE       WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'       WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'       WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'       WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'       WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'    END FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id

천천히 샘플을 수행해 보세요. 이게 어떤 종류구나가 중요한게 아니라..

CASE구문으로 IF ELSE의 중첩을 많이 막을 수 있으며 매우 유용합니다. ^_^

　

다음은 CASE함수와 비슷한 처리가 가능하지만 한큐로 수행이 가능한 유용한 함수 입니다.

NULLIF 입니다.

이는 NULLIF(expression1, expression2)

식이며 두 값이나 표현식이 같은 경우에 NULL을 반환합니다.

즉.. 조금더 생각해 보시면

계산된 평균값에 어떤 특정 값이 포함되지 않기를 원하는 경우에

사용될 수 있지요.

이하 두개의 질의를 비교해 보세요.

SELECT royaltyper FROM titleauthor SELECT NULLIF(royaltyper, 100) FROM titleauthor

　

다음은 COALESCE 입니다.

역시나 간단합니다.

COALESCE(expression1, expression2, ….. , expressionN)

한 구문이 있을 경우...

NULL이 아닌 처음 값을 표현식부터 찾습니다. ^_^

아래의 INSERT 되는 데이터를 봐 보세요.

테이블을 아래와 같이 설계 하신후..  앞의 컬럼의 값부터 NULL이 아닌

값을 찾으려 한다면?

SET NOCOUNT ON GO CREATE TABLE wages ( emp_id tinyint identity, hourly_wage decimal NULL, salary decimal NULL, commission decimal NULL, num_sales tinyint NULL ) GO INSERT wages VALUES(10.00, NULL, NULL, NULL) INSERT wages VALUES(20.00, NULL, NULL, NULL) INSERT wages VALUES(30.00, NULL, NULL, NULL) INSERT wages VALUES(40.00, NULL, NULL, NULL) INSERT wages VALUES(NULL, 10000.00, NULL, NULL) INSERT wages VALUES(NULL, 20000.00, NULL, NULL) INSERT wages VALUES(NULL, 30000.00, NULL, NULL) INSERT wages VALUES(NULL, 40000.00, NULL, NULL) INSERT wages VALUES(NULL, NULL, 15000, 3) INSERT wages VALUES(NULL, NULL, 25000, 2) INSERT wages VALUES(NULL, NULL, 20000, 6) INSERT wages VALUES(NULL, NULL, 14000, 4) GO SET NOCOUNT OFF GO SELECT COALESCE(hourly_wage, salary, commission * num_sales) FROM wages GO

자 여기까지 입니다. ~~~  수고하셨습니다.

이곳까지가 개발자 분이라면 꼬옥 봐 두어야할 SQL서버 개발에 필요한 부분의 정리라고

보시면 됩니다. - 이하 부분은 문제상황이 발생하거나 다른 처리를 원하실때 찬찬히

보시면 되구요. ^_^

이제 프로그래밍 부분도 마무리가 다행히 되었군요. ^_^

다음장부터는 조금 난해해 질 수 있습니다. - 여기까지 보셨으니 이제 초보자 분은

아니신 겁니다. ^_^

자 수고하셨구요.   -  이제는 실제 자신의 업무에 적용하면서..

덕지덕지한 어플리케이션.. ASP나 VB의 코드를..  앞의 많은 내용을 통해

한줄로 줄여 보시길 바랍니다. ^_^

1. VIEW 테이블의 정의

테이블에 대한 제한을 가지고 테이블의 일정한 부분만 보일 수 있는 가상의 테이블로서 VIEW TABLE은 실제 자료를 갖지는 않지만, VIEW을 통해 Table을 관리 할 수 있다.

하나의 테이블에 View의 개수는 제한이 없다.

2. VIEW 테이블의 목적

권한을 제한하여 만들수 있다.

*사용자에게 테이블에 대해 사용할 수 있는 모든 권한을 부여시는 다음과 같이 쓴다,

*권한부여시) GRANT SELECT/INSERT ON 테이블명 TO 사용자

*그러나, 일반적으로 사용자에게 테이블에 관한 모든 권한은 주지 않으며..따라서,, 제한적으로 권한부여를 해 주기 위해 View명령사용한다.

*실데이타는 가지고 있지않으며, 어떤 테이블을 제한적으로 다른 사용자가 볼수 있도록 해준다.

두개의 테이블을 join 할 경우 자료로 View 테이블로 만들어 작업의 효율성을 높일수 있다.

(복잡한 Query를 간단하게 해준다.) 여러 개의 테이블이 있을 때 view를 만든후, 쉽게 자료를 여러 개의 테이블로부터 볼 수 있다.

3. 형식

Create View view테이블명 AS Select …..Where …..

Drop View view테이블명 - :삭제

Alter View view테이블명 - :수정

4. 제한 조건

View을 통한 자료의 transation (DELETE./UPDATE/…)이 가능하게 하려면

1. Group By ,Distinct, Group 함수등을 이용한 View가 아니어야 한다.

2. 하나의 테이블에서 생성된 View이어야한다.

수식이 사용된 필드는 수정, 삭제 할 수 없다.

view을 통한 삽입이 가능하려면 NOT NULL 로 설정된 필드가 VIEW에 모두 포함되어야 한다.

view 생성시 Order By는 사용할 수 없으며, Group By, Having조건을 사용할 수 있다.

SQL> Create View v_sa5 as select saname,sajob,deptno from sawon where deptno=10;

SQL> update sawon set deptno=20 where saname='이상아'; - UPDATE 의 경우

1 개의 행이 갱신되었습니다.

SQL> Grant select on V_sa5 to scott;

권한 부여 성공입니다.

Connect Scott에서 View Table 보기

SQL> select * from Angle.V_sa5;

SQL> Create table v_sa6 as select saname,sapay,sapay*2 as bouns from sawon where deptno=20;

**** 필드명을 안 써주었을 경우 Error 가 발생

SQL> Create table v-sa6 (x,y,z) as select saname,sapay,sapay*2 from sawon where deptno=20;

****원하는 필드명으로 Change할 수 있다.

원하는 필드명으로 JOIN을 할 경우

SQL> Create view v_sa7(부서명,이름,직책,급여) as select dname,saname,sajob,sapay from sawon,dept

where dept.deptno=sawon.deptno;

원하는 필드명으로 join 과 group by 을 사용했을 때

SQL> Create View v_sa8(부서명,인원수,급여합계) as select dname,count(*),sum(sapay) from sawon,deptwhere sawon.deptno=dept.deptno group by dname;

http://blog.naver.com/sevenwind/130020436098