블로그 이미지
불쥐의 눈으로 본세상 ㏈ª ☞ β┖υΕJini.κR

카테고리

분류 전체보기 (234)
By β┖υΕJini (131)
㏈ª By β┖υΕJini (103)
Total302,468
Today16
Yesterday15
사용자 삽입 이미지
SQL 아카데미에서 교육 받은후 SQL 를 해본 사람들이 어느 정도의 지식을 가지고 있는가

라는 주제로 정원혁 강사님께서 내주신 문제 이다.

얼마전 내 서랍속에서 있던걸 회사 사람들과 함께 풀어 보았다.

저희 회사 형이 답과 설명까지 잘 달아 주실걸 퍼왔다..^^

1.다음 구문에서 오류가 발생 되는 쿼리는?
 
1)  SELECT type, SUM(price) as sum_price FROM dbo.titles GROUP BY type HAVING sum_price IS NOT NULL
2)  SELECT * FROM dbo.titles t, dbo.pub_info p WHERE t.pub_id = p.pub_id
3)  SELECT case when type = 'business' then '업무용' else '비업무용' end as type, count(*) as 수량 FROM dbo.titles GROUP BY type
4)  SELECT ISNULL(Country, '합계') AS Country, qty FROM( SELECT Country, COUNT AS (*) qty FROM dbo.Customers GROUP BY Country WITH ROLLUP) AS inTable

/**********************************************************************
SELECT type, SUM(price) as sum_price FROM dbo.titles GROUP BY type HAVING SUM(price) IS NOT NULL
**********************************************************************/


2. 단일 프로세서가 장착된 서버를 업그레이드 하여 4 CPU 서버로 확장하였다. 현재 다음과 같은 서버 구성 옵션이 설정된 상태이다.  어떤 옵션을 변경해야 필요한 경우 병렬처리 계획을 통해 쿼리를 좀 더 빠르게 수행할 수 있을까?
 
1)  Query Cost Governer Limit을 32767로 변경
2)  Affinity Mask 옵션을 1로 변경
3)  Max Degree of Parallelism 옵션을 4로 변경
4)  Cost threshold for parallelism 옵션을 10으로 변경

/**********************************************************************
Query Cost Governer Limit : 이 옵션은 Query 제한 시간을 뜻한다. 즉, 이 옵션을 32767로 변경을 하면 32767초 걸리는 Query는 실행하지 않는다 라는 의미인 것 이다.
http://msdn.microsoft.com/ko-kr/library ··· %29.aspx

Max Degree of Parallelism :  이 옵션은 병렬처리 될 프로세서의 수를 뜻한다. 이 값이 0이면 모든 프로세서를 활용하여 병렬처리를 한다는 것이고, 1이면 병렬처리를 하지 않는 다는 말이다. 왜 1이면 병렬처리를 하지 않는것일까? 프로세서가 1 라고 정의 하니 병렬처리를 하지 않는것 뿐이다. 프로세서가 2개 이상은 되어야 병렬처리를 할 수 있으니 말이다.
즉, CPU가 4개인 서버에서 이 옵션이 4인것은 지극히 정상이다.
http://msdn.microsoft.com/ko-kr/library/ms181007.aspx

Cast threshold for parallelism : 이 옵션은 병렬처리 임계값을 나타낸다. 기본 값은 5 이다. 기본값이 5인데, 이것을 10으로 바꾼다고 변하는게 없다. 다만 임계값 수치가 넘어가야 병렬처리를 한다.

Affinity Mask : 프로세서 연결 선호도라고 하는데, 기본값은 0이고 이 값을 1 로 변경할때 병렬처리에 관련하여 큰 효과를 얻을 수 있다. 자세한 내용은
http://msdn.microsoft.com/ko-kr/library/ms187104.aspx
**********************************************************************/



3.FULL OUTER JOIN의 결과집합을 설명한 것 중 옳은 것은?
 
1)  ON 절에 지정된 칼럼을 기준으로 양쪽 테이블의 모든 행의 조합을 반환
2)  JOIN 조건에 일치하지 않는 행의 칼럼 값을 NULL로 치환한 상태로, 양쪽 테이블의 모든 행을 반환
3)  양쪽 테이블의 모든 행과 모든 칼럼을 반환
4)  SQL Server 에서는 사용할 수 없는 JOIN 구문

/**********************************************************************
여기에서 어떻게 보면 3)번의 내용도 FULL OUTER JOIN의 내용과 비슷하지 않나? 머 FULL OUTER JOIN의 정확하게 표현해 놓은 설명은 2)번이기 하다.
**********************************************************************/



4. sqlplan 확장자 파일에 대한 설명으로 옳은 것은?
 
1)  SQL Server 2005 Upgrade Assistant 결과물 파일
2)  병합 복제 추적 파일
3)  쿼리 실행 계획 파일(XML 형식)
4)  용할 수 없는 확장자


5. 다음 중 SQL Server 2005에서 온라인으로 수행할 수 있는 인덱스 관리 작업은?
 
1)  ALTER INDEX … WITH REBULID
2)  ALTER INDEX … WITH REORGANIZE
3)  A와 B를 모두 온라인으로 수행가능
4)  A와 B 모두 온라인으로 수행 불가능

/**********************************************************************
SQL Serer 2005의 새로 추가된 기능이며 자세한 내요은 아래 링크
http://msdn.microsoft.com/ko-kr/library/ms188388.aspx
**********************************************************************/


6.아래 표현은 관계형 데이터 모델에 적용되는 정규화 단계를 설명한다. 어떤 정규화 단계에 해당하는지 고르시오. "모든 키가 아닌 칼럼은 기본 키 전체에 의존적이어야 한다. 기본 키의 일부분에 의존적이어서는 안 된다.
 
1)  1차 정규화
2)  2차 정규화
3)  3차 정규화
4)  BCNF

/**********************************************************************
제 1 정규형 : 반복되는 속성이나 그룹의 속성을 제거 하고, 새로운 실체를 추가한 후에 기존의 실체와 일대다의 관계를 형성한다.

제 2 정규형 : 복합키(Composit Primary Key)로 구성된 경우 해당 테이블 안의 컬럼들은 복합키 전체에 의존적이어야 한다. 만일 복합키 일부에 의 존적인 컬럼이 존재한다면 이를 제거해야 한다.

제 3 정규형 : 한 테이블 안의 모든 키가 아닌 컬럼들은 기본키(Primary Key)에 의존해야 한다. 만일 키가 아닌 컬럼에 종속되는 속성이 존재한다면 이를 제거해야 한다.
**********************************************************************/


7.다음 중 데이터 무결성의 종류에 해당하지 않는 것을 고르시오.

1)  엔터티 무결성
2)  참조 무결성
3)  도메인 무결성
4)  연결 무결성

/**********************************************************************
엔티티무결성(Entity Integrity) : 한 테이블에서 각각의 로우(ROW)는 상호구분 가능해야 한다. 참조무결성을 유지하기에 가장 기본이고 필수적인것이 기본키인 것 이다.

참조무결성(Referential Integrity) : 관계를 맺고 있는 두 테이블 사이에 서로 불일치한 데이터가 발생하지 못하도록 하기 위한 제약조건이다.

도메인무결성(Domain Integrity) : 한 컬럼에 입력될 수 있는 테이터의 유형, 형식, 경우의 수 등을 정의해서 잘못된 형식의 데이터가 입력되지 못하도록 하는 것이다.
**********************************************************************/



8. 다음 중 Nonclustered Index를 사용한 쿼리의 성능을 결정짓는 가장 중요한 항목에 해당하는 것을 고르시오. (참고. SQL Server 2000 기준 용어 사용)
 
1)  인덱스 구성 수준
2)  책갈피 조회(Bookmark Lookup)
3)  인덱스 키 칼럼 수
4)  최적화기(Optimizer)

/**********************************************************************
SQL Server 2005 에서는 Bookmark Lookup 이라는 것이 없어졌다. Clustered Index가 있으면 Clustered Index Seek 해서 Join 하고 Heap이면 RID Lookup 해서 Join 한답니다.
자세한 내용은
http://www.ddoung2.com/17
**********************************************************************/


9.다음 중 Non-SARG에 해당되지 않는 것은?
 
1)  WHERE co11 + 1 = 100
2)  WHERE LEFT(col2, 1) = '1'
3)  WHERE col3 LIKE 'A%' 
4)  WHERE col1 = col2

/**********************************************************************
SARG(Search Argument)란? Index를 사용할 수 있도록 고려된 조건이라고 한다. ?? 무슨 소리?? 즉, WHERE 절에 조건이 인덱스를 사용하도록 고려하여 만들어야 한다는 말이다.
비교값은 항상 M:1(Many to one)의 관계이다. 그럼 NON-SARG란?? SARG의 반대말이겠지..
혹시나 왜 4)은 아니야? 라는 질문을 하는 사람이 있을것 같아 추가 설명을 한다.
앞서 말했듯이 M:1의 관계가 되어야 하는대 WHERE col1 = col2 여기서 col2의 값은 ?? 알수가 없다. Table Scan 고고싱 ㅋ

**********************************************************************/


10. NESTED LOOP 물리적 연산자를 사용해서 실행되는 조인의 최적 성능을 위해서 기본적으로 필요한 두 가지 요구 사항을 잘 나열한 것은?
 
1)  적은 행을 반환하는 외부(선행) 입력, 인덱스 검색이 가능한 내부(후행) 입력
2)  인덱스 검색이 가능한 내부(후행) 입력, 인덱스 스캔을 수행하는 외부(선행) 입력
3)  인덱스 스캔을 수행하는 외부(선행) 입력, 적은 행을 반환하는 외부(선행) 입력
4)  정렬된 출력을 가지는 내부(후행) 입력, 인덱스 스캔을 수행하는 외부(선행) 입력

/**********************************************************************
NESTED LOOP : 간단한게 말해서 비교 반복문이라고 할 수 있다. 다른 말로 하면 프로그램언어중의 FOR문이라고 할수도 있다. NESTED LOOP 방식의 성능을 높이기 위해서는 2가지가 필수이다.
  1. 후행테이블의 크기가 작어야 한다.
  2. 요소들의 비교가 빠르게 이루어지도록 인덱스가 미리 정의 되여 있어야 한다.

이 외에 SORT MERGE JOINHSAH JOIN 방식이 있다.

**********************************************************************/


11.트랜잭션(Transaction)이 갖추어야 할 기본 속성에 해당하지 않는 것은?
 
1) 원자성(atomicity)
2) 일관성(consistency)
3) 고립성(isolation)
4) 결정성(determination)

/**********************************************************************
원자성(Automicity) : 하나의 트랙잭션이 완료되었을 때의 데이터는 해당 트랙잭션의 처리를 모두 완료한 후의 상태이거나 처리를 받아 들이기 이전의 상태이어야 한다. 즉, 데이터의 갱신은 All or Nothing 개념이여야 한다는 것이다. 일부분 변경은 허용되지 않는다.

일관성(Consistency) : 트랜잭션의 성럽, 미성립에 불문하고 데이터의 내용은 일관성이 유지되어야 한다. 즉, 이 트랜잭션의 실행으로 일관성이 깨지지 않는다.

격리성(Isolation) : 연산의 중간 결과에 다른 트랜잭션이나 작업이 접근할 수 없다

영속성(Durability) : 트랜잭션이 종료한 후에는 장애 등으로 데이터의 내용이 변경되지 않아야 한다.

**********************************************************************/


12. 다음 T-SQL 구문중에 SQL Server 2005에서 실행되지 않는 구문은?
 
1)  SELECT * FROM t1, t2 WHERE t1.col1 *= t2.col2
2)  SELECT col1+col2 AS newCol FROM t1 ORDER BY newCol
3)  DECLARE @i INT; SET @i = 10; SELECT TOP(@i) * FROM t1
4)  DELETE TOP(5) FROM t1

/**********************************************************************
SELECT * FROM t1, t2 WHERE t1.col1 *= t2.col2 은 T-SQL 이다. ANSI 방식이 아니다.
근데 안돌아가나 ?? 안해봐서 모르겠다.. ㅋㅋ

**********************************************************************/


13.DBCC SQLPERF(waitstats) 명령을 실했을 때 CXPACKET 항목의 비율이 가장 높게 나왔다. 점검해봐야 할 부분의 하나라고 생각되는 것은?
 
1)  Network
2)  Disk 
3)  Parallel Process
4)  Memory

/**********************************************************************
너무나 많은 카운터들이 있어 다 설명 못한다.
또는 sys.dm_os_wait_stats 찾아보아라.

**********************************************************************/


14.저장 프로시저(Stored Procedure)의 정의(해당 개체를 생성한 스크립트)를 표시하고자 할 때 사용할 수 있는 구문은?
 
1)  sp_helpfile
2)  sp_helptext 
3)  sp_helpscript 
4)  sp_procoption

/**********************************************************************
sp_helpfile :
http://msdn.microsoft.com/ko-kr/library/ms174307.aspx
sp_helptext : http://msdn.microsoft.com/ko-kr/library ··· %29.aspx
sp_procoption : http://msdn.microsoft.com/ko-kr/library/ms181720.aspx
**********************************************************************/


15.다음과 같은 분할된 테이블(Partitioned Table)이 있다.
 
CREATE PARTITION FUNCTION UserInfoPF (INT)
AS RANGE RIGHT FOR VALUES (1000, 2000, 3000)
GO

CREATE PARTITION SCHEME UserInfoPS
AS PARTITION UserInfoPF TO (Fgroup1, Fgroup2, Fgroup3, Fgroup4)
GO

CREATE TABLE UserInfo(
    UNumber INT NOT NULL PRIMARY KEY,
    UName VARCHAR(50) NOT NULL,
    Age TINYINT NOT NULL
) ON UserInfoPS (UNumber);
GO

여기서 다음의 INSERT 쿼리를 실행하여 추가되는 행은 어느 파일 그룹에 저장될까?
INSERT INTO UserInfo VALUES(2000, '홍길동', 28)
 
1)  Fgroup1
2)  Fgroup2
3)  Fgroup3
4)  Fgroup4

/**********************************************************************
RANGE RIGHTRANGE LEFT 와 비교하기

CREATE PARTITION FUNCTION UserInfoPF (INT)
AS RANGE RIGHT FOR VALUES (1000, 2000, 3000)

파티션 1 : UNumber < 1000
파티션 2 : UNumber >= 1000 AND UNumber < 2000
파티션 3 : UNumber >= 2000 AND UNumber < 3000
파티션 4 : UNumber >= 3000

CREATE PARTITION FUNCTION UserInfoPF (INT)
AS RANGE LEFT FOR VALUES (1000, 2000, 3000)

파티션 1 : UNumber <= 1000
파티션 2 : UNumber > 1000 AND UNumber <= 2000
파티션 3 : UNumber > 2000 AND UNumber <= 3000
파티션 4 : UNumber > 3000

이다. 차이점을 알겠는가??

http://msdn.microsoft.com/ko-kr/library ··· examples
**********************************************************************/


16.다음 성능카운터 중 값이 높을 수록 좋은 것은?
 
1)  MSSQLSERVER\Access Methods: Forwarded Records/sec
2)  MSSQLSERVER\Access Methods: Full Scans/sec
3)  MSSQLSERVER\Access Methods: Index Searches/sec
4)  위의 1)과 3)


17.다음 성능카운터 중 메모리와 관계 없는 것은?
 
1)  Page Faults/Sec 
2)  Buffer cache hit ratio
3)  Page life expectancy 
4)  1), 2), 3) 모두 메모리와 관련 있는 성능카운터이다

/**********************************************************************
16)번 17)번은 알아서 찾아보라. 이곳에 적기에는 너무 많은 내용들이다.
**********************************************************************/



18.다음의 물리적 장치 중 가장 I/O 속도가 낮은 장치는?
 
1)  CPU 
2)  Memory 
3)  Disk 
4)  Cache Memory

/**********************************************************************
이 문제는 내가 유일하게 자신이게 대답할 수 있는 문제이다. ㅋㅋ
**********************************************************************/



19. 다음 중 백업, 복원에 대한 이야기 중 틀린 것은?
 
1)  트랜잭션 로그 복원은 전체 백업 파일을 복원 후, 가장 마지막 트랜잭션 로그 백업부분을 복원하면 된다.
2)  단순복구 모델은 개발서버 또는 테스트 서버에서 사용이 권장된다.
3)  전체복구 모델에서만 STOPAT옵션을 이용하여 특정 시점 복원이 가능하다.
4)  SQL Server 2005에서는 미러(mirror) 백업 및 복사 백업이 추가 되었다.

/**********************************************************************
트랜잭션 로그 복원은 전체 백업 파일을 복원 후, 가장 마지막 트랜잭션 로그 백업부분을 복원하면 된다. ?? 무슨 소리니 ㅋ**********************************************************************/


20.다음 중 맞는 이야기는?
 
1)  하드웨어의 증설이 가격 대비 성능 향상이 가장 높다. 
2)  읽기 수에 비해서 응답시간이 오래 걸리는 쿼리는 recompile 문제로 일어 난다. 
3)  Tempdb file의 개수는 CPU의 개수와 연관성이 없다.
4)  SQL Server 2005에서는 Database 생성 시, 그 크기와 관계 없이 매우 빠른 속도로 생성 된다.


21. 다음은 SQL Server에서 사용하는 데이터 유형이다. 다음 중 사용 공간의 크기가 다른 하나는?
 
1)  REAL
2)  INT 
3)  SMALLDATETIME
4)  MONEY


22.현재 서비스 중인 웹서버에 이상이 발견되어 페이지가 나타나지 않고 있다. 웹 서버를 확인해 보니, CPU 사용량이 낮아서 웹서버에는 문제가 없어 보인다. SQL Server 서버의 작업 관리자를 보니 CPU가 10% 이하로 낮게 나타나고 있다 (평상시 CPU 사용량은 60~70%를 유지한다.).  쿼리 분석기에서 sp_lock 명령을 실행시켜 보니 아래와 같은 사항이 발견되고 있다. 현재 상황을 가장 적절하게 설명한 것은 어느 것인가?
 
1)  52번 세션에서 대량 작업(BULK INSERT)이 이루어지고 있다. 해당 작업 세션을 강제 종료 시켜야 한다.
2)  SQL Server에는 문제가 없다. 
3)  파일 증가가 발생하고 있다. 관리자에게 요청해서 현재 DB 사용량을 파악해서 비업무시간에 파일을 수동으로 증가시켜주어야 한다.
4)  16번 DB에 이상이 생겨서 SQL Server 엔진이 자동으로 데이터를 다른 곳으로 이동시키고 있다.


23.우리회사에서 운영하고 있는 DB 서버에 업무 시간 중 Profiler를 이용해서 수집된 쿼리를 분석하였다. 튜닝하려는 쿼리는 여러 테이블을 JOIN을 걸어서 데이터를 가져오는 구조이다. 테이블 중에 스캔이 발생하는 실행계획을 확인하여 적절한 인덱스를 생성해 주었다. 하지만, 해당 쿼리는 해당 인덱스를 이용하지 않고, 기존의 실행계획을 계속 사용하고 있다. 새로 생성된 인덱스를 이용하여 실행계획을 구성하게 하려는 작업과 관계가 먼 것은?
 
1)  해당 인덱스의 선택도가 좋은지를 다시 확인하고, 선택도가 좋은 항목이 앞에 오도록 구성한다.  
2)  SARG을 준수하고 있는지 확인하고, 인덱스를 사용할 수 있는 조건절 컬럼이 가공되지 않도록 조정한다.
3)  인덱스 힌트를 부여해서 해당 인덱스를 이용하도록 지정한다.
4)  출력되는 컬럼의 순서를 인덱스 컬럼의 순서에 맞도록 조정한다.


24. 다음 중 SQL Server 서비스가 아닌 것은?
 
1) SSIS : SQL Server Integration Service
2)  SSRS : SQL Server Reporting Service
3)  SSNS : SQL Server Notification Service
4)  SSAS : SQL Server Adviser Service


25. 다음 데이터의 구조를 보고, 설계기법 중 어떤 기법을 적용해야 좋은지 선택하라.
Emp Table 의 구조 및 자료 현황이다.


사번
직원명  연봉   시급     직책   비정규직직급  입사일       고용일
1     홍길동  3000  Null      과장   Null               20040101  null
2     이순신  Null    10000  Null    선임              Null           20070228
3     김유신  Null    15000  Null    책임              Null           20060605
4     강감찬  2500  Null      대리   Null               20051231  null
5     정약용  Null    30000  Null    수석              Null           20020301

1)  제2정규화
2)  제3정규화
3)  BCNF
4)  Super Type - Sub Type

-http://ddoung2.com/entry/SQL-Server-Academy-지식-테스트 퍼왔습니다.
Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

사용자 삽입 이미지
사용자 삽입 이미지

Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

  1. 2008.11.27 15:06 β┖υΕJini.κR  댓글주소  수정/삭제  댓글쓰기

    이번에 출시된 SQL 2008 좀 파봐야 하는데 열심히 쳐다만 보고 있다.

사용자 삽입 이미지


몇년전 골드PC방의 개념이 도입되었을 당시 각 PC 의 IP 대역을 웹에서 입력 받아

예로 211.000.000.1 ~ 50 를 사용하는 PC 방이라고 가정하고 50개의 아이피를 다 입력 할려고

가입시 많은 시간과 짜증을 낼것이 분명 하다.

그래서 사용하게 된 방법이 앞 3자리 211  , 000 , 000  를 받고 시작 IP 1 과 끝 IP 50을 입력 받아

실제 DB 상에는

211.000.000.1
211.000.000.2
211.000.000.3
        :
        :
211.000.000.50
으로 넣을수 있게 구현하게 되었다.

더 좋은 방법이 있겠지만..... 한번 만들어 보았다. ^^

USE TEMPDB

CREATE TABLE TESTIP
(
 [NUM] INT IDENTITY(1,1)
,[IP] VARCHAR(20)
)
Insert Into TESTIP(IP) VALUES('211.101.221.1-50')
Insert Into TESTIP(IP) VALUES('211.101.221.51-100')
Insert Into TESTIP(IP) VALUES('211.101.221.101-150')
Insert Into TESTIP(IP) VALUES('211.101.221.151-200')

DECLARE @NUM  INT
DECLARE @IP_LAST1  INT
DECLARE @IP_LAST2  INT
DECLARE @IP_PUBLIC VARCHAR(20)

  DECLARE process_printlist CURSOR FOR
 select  [num]
 ,SUBSTRING(REVERSE(SUBSTRING(REVERSE(IP), 1, CHARINDEX('.', REVERSE(IP))-1)),1,CHARINDEX('-',REVERSE(SUBSTRING(REVERSE([IP]), 1, CHARINDEX('.', REVERSE([IP]))-1)))-1)
 ,REVERSE(SUBSTRING(REVERSE(IP), 1, CHARINDEX('-', REVERSE(IP))-1))
 ,SUBSTRING(IP, 1, LEN(IP)-CHARINDEX('.', REVERSE(IP))+1) 
 FROM TESTIP
  OPEN process_printlist

  WHILE 1 = 1
    BEGIN
      FETCH NEXT FROM process_printlist INTO @NUM,@IP_LAST1,@IP_LAST2,@IP_PUBLIC

      IF @@FETCH_STATUS = -1 BREAK
      IF @@FETCH_STATUS = -2 CONTINUE
   SET @IP_LAST1 = @IP_LAST1
   WHILE @IP_LAST1 <= @IP_LAST2
   BEGIN
     -- 테이블 만들어서 만들어진 IP 인서트 하면 되겠죠^^
      SELECT CAST(@IP_PUBLIC AS VARCHAR(20)) + ''+ CAST(@IP_LAST1 AS VARCHAR(10))
      SET @IP_LAST1 = @IP_LAST1 + 1
      IF @IP_LAST1 > @IP_LAST2
      BREAK
      ELSE
      CONTINUE
   END
  -- while 
      IF @@ERROR <> 0 RETURN
    END

  CLOSE process_printlist
  DEALLOCATE process_printlist


--- 2번째 방법

USE TEMPDB

 

 

CREATE TABLE TESTIP

    (

      [NUM] INT IDENTITY(1, 1) ,

      [IP1] VARCHAR(20) ,

      [IP2] VARCHAR(20)

    )

INSERT  INTO TESTIP

        ( IP1 ,

          IP2

        )

VALUES  ( '211.101.221.1' ,

          '211.101.221.50'

        )

 

 

DECLARE @NUM INT

DECLARE @IP_LAST1 INT

DECLARE @IP_LAST2 INT

DECLARE @IP_PUBLIC VARCHAR(20)

 

DECLARE process_printlist CURSOR

FOR

    SELECT  [num] ,

            REVERSE(SUBSTRING(REVERSE(IP1), 1,

                              CHARINDEX('.', REVERSE(IP1)) - 1)) ,

            REVERSE(SUBSTRING(REVERSE(IP2), 1,

                              CHARINDEX('.', REVERSE(IP2)) - 1)) ,

            SUBSTRING(IP1, 1, LEN(IP1) - CHARINDEX('.', REVERSE(IP1)) + 1)

    FROM    TESTIP

OPEN process_printlist

 

WHILE 1 = 1

    BEGIN

        FETCH NEXT FROM process_printlist INTO @NUM, @IP_LAST1, @IP_LAST2,

            @IP_PUBLIC

 

        IF @@FETCH_STATUS = -1

            BREAK

        IF @@FETCH_STATUS = -2

            CONTINUE

        SET @IP_LAST1 = @IP_LAST1

        WHILE @IP_LAST1 <= @IP_LAST2

            BEGIN

     -- 테이블만들어서만들어진IP 인서트하면되겠죠^^

                SELECT  CAST(@IP_PUBLIC AS VARCHAR(20)) + ''

                        + CAST(@IP_LAST1 AS VARCHAR(10))

                SET @IP_LAST1 = @IP_LAST1 + 1

                IF @IP_LAST1 > @IP_LAST2

                    BREAK

                ELSE

                    CONTINUE

            END

  -- while 

        IF @@ERROR <> 0

            RETURN

    END

 

CLOSE process_printlist

DEALLOCATE process_printlist

 

 -- 세번째 방법



 

 

SELECT  SUBSTRING(IP1, 1, LEN(IP1) - CHARINDEX('.', REVERSE(IP1)) + 1)

        + CONVERT(VARCHAR(5), number)

FROM    ( SELECT    number

          FROM      [master].dbo.spt_values

          WHERE     [type] = 'p'

                    AND number BETWEEN 1 AND 255

        ) A

        INNER JOIN TESTIP B ON A.number >= REVERSE(SUBSTRING(REVERSE(IP1), 1,

                                                             CHARINDEX('.',

                                                              REVERSE(IP1))

                                                             - 1))

                               AND A.number <= REVERSE(SUBSTRING(REVERSE(IP2),

                                                              1,

                                                              CHARINDEX('.',

                                                              REVERSE(IP2))

                                                              - 1))

 

 


Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

예전 DB 가이드에서 김현종 씨라는 분이 작성하신 글을 옮겨 보았습니다.^^

가끔 DB를 관리하다가 보면 DB 로그가 예상보다 오버되어 HDD를 full내서 DB가 다운되는 되는 현상을 겪으셨거나 혹은 겪을 수도 있으실 것입니다.
그런 부분을 미리 방지하고자 제가 사용하는 방지 방법입니다.

일단 크게 정리를 하자면
1. 로그 사이즈를 알아본다.(DBCC SQLPERF(logspace))
2. 로그 사이즈를 필요한 부분만 DB화 한다.(master, model, pub 등 system 기본 DB 제외)
3. 기준을 세워 로그를 관리한다.
A. 제가 잡은 기준은 설정 로그 사이즈에 50%
4. 문제가 생긴 로그 받기
A. 분석 내용을 email로 받도록 한다.
B. 분석 내용을 SMS 로 받도록 한다.
5. 문제가 생긴 부분을 분석하여 로그를 백업만 하면 될 것인지, 아니면 기본 로그 사이즈를 증가 할 것인지 판단하도록 한다.

로그 사이즈 알아 보기
로그 사이즈는 DBCC SQLPERF(logspace) 명령어로 손쉽게 알아 볼 수 있습니다.
 [실행]
EXEC DBCC SQLPEF(LOGSPACE)
 [결과]
 

로그 사이즈 DB화 하기
 로그 사이즈를 DB에 입력해 넣는다.
 [실행]
create table #temp(
 Database_name varchar(50),
 Log_size FLOAT,
 log_space_used FLOAT,
 status TINYINT
)

INSERT #temp
EXEC('DBCC SQLPERF(LOGSPACE)')

INSERT LOG_SPACE_CONTROL(database_name, Log_size, log_space_used, status)
SELECT * FROM #temp
WHERE Database_name not in (
'master'
,'tempdb'
,'model'
,'msdb'
,'pubs'
,'Northwind'
)

DROP TABLE #temp
 로그를 입력할 때 시스템 관련 table은 남기지 않습니다.
 이런 스크립트를 DB 작업으로 만들어서 매일 새벽에 돌려서 table에 기록을 남깁니다.
 (오늘 날짜와 함께)

기준 세워 로그 관리
 제가 기준은 50%로 잡았습니다.
일단 로그에 문제가 나도 바로 작업이 될 수 없고 1~2일 정도는 문제를 처리하지 못하는 상황이 발생할 수도 있기에 잡은 기준입니다.
 관리 담당자가 운영에 필요한 기준을 잡으면 될 것 같습니다.
 [실행]
SELECT Database_name
 FROM log_space_control
 WHERE log_space_used > 50
and sd_date >= convert(varchar(10),dateadd(dd,-1,getdate()),20)

문제가 생긴 로그 받기
 로그를 실시간으로 모니터링이 불가능 하니 특정 작업 시간에 로그를 남긴 것을 남긴 후
email이나 SMS 와 같은 서비스를 이용하여 피드백을 받도록 합니다.
저 같은 경우는 SMS 로 받도록 하고 있습니다.
이 부분에 대해서는 편리하신 방법을 적용 하시면 될 것 같습니다

문제 로그 분석 판단
 로그에 문제가 생겨서 피드백을 받게 된다면 로그의 처리 방안을 생각 하셔야 하는데
 일단은 로그를 백업 후 삭제 할 수가 있을 것 같습니다.
 [실행]
Backup log db명 옵션

그리고 로그의 문제가 관리가 안되어서 그럴수도 있지만 원래 로그의 크기가 적어서 그럴 수도 있으니 같이 검토가 필요할 것입니다.
 보통 로그의 크기는 데이터 파일(MDF)에 20~30%의 크기를 잡는 것이 일단 적입니다.


Posted by ㏈ª ☞ β┖υΕJini.κR

댓글을 달아 주세요

보호되어 있는 글입니다.
내용을 보시려면 비밀번호를 입력하세요.

사용자 삽입 이미지


현재 G사 DBA 로써 근무 하고 있다. 예전에 보았던 DBA로써의 역활과
책임이라는 글을 다시 한번 읽어 보았다.


DBA의 역할과 책임

DBA의 역할

시스템과 조직에 따라 DBA의 임무에 차이가 있을 수 있지만 일반적으로 대부분의 DBA는 다음과 같은 작업들을 책임지고 수행해야 하는 임무를 가집니다.

  • 설치와 환경설정
    - 소프트웨어 설치
    - 환경 설정
  • 보안 관리
  • 운영
    - 백업과 복원
    - 사용자 관리
    - 기타 일상적인 운영 업무
  • 서비스 레벨 유지
    - 성능 최적화 및 성능 모니터링
    - 용량 계획 (Capacity Planning)
  • 시스템 가동 시간 관리
    - 시스템 정지 시간의 계획과 일정 관리
  • 문서화 작업
  • 작업 절차 계획 및 규격화
    - 운영 유지보수 계획 수립
    - 재난 복구 계획 수립
  • 설계 및 개발 지원
    - 데이터 모델링
    - 데이터베이스 설계
    - 저장 프로시저 개발
    - 응용 프로그램 개발
  • 개발 환경 관리
    - 개발 시스템 환경 별도 제공 및 개발 시스템 관리
  • 긴급 상황 해결/장애 복구
  • SQL Server 관리에 필요한 지식 숙지


 

DBA 작업의 기본적인 원칙

DBA가 시스템 유지를 위하여 일반적으로 수행하는 모든 작업들에 대하여 기본적으로 다음과 같은 원칙에 의거하여 작업할 것을 권고합니다.

  • 작업 표준화 체계 수립

    표준화는 관리에 있어서 매우 중요한 요소입니다. 자신의 시스템에 가장 적합한 표준화 체계를 수립하고, 전체 시스템에 대하여 표준화된 관리 체계를 적용하여 관리해야 합니다. 예를 들어, 다중의 DB 서버를 관리하는 경우에는 표준화가 특히 중요합니다.

  • 문서화

    DB 관리와 같이 중요한 작업은 사람의 기억에 의한 주먹구구식의 작업이 되어서는 안됩니다. 어떤 경우라도 항상 정확하고 일관된 작업이 가능하도록 문서화가 필요합니다. 기록 가능한 모든 작업들에 대해서 문서화하고, 변경이 발생하면 지속적으로 업데이트하는 관리가 필요합니다.

    • 작업 매뉴얼 : 작업 수행 절차에 대한 정보 (설치, 장애 복구, 백업과 복원 전략, 주기적으로 수행하는 작업 등에 대한 작업 절차 및 참고 사항이 이에 포함될 수 있으며, 일반적이고 중요한 정보는 운영 매뉴얼에 기록하여 모든 DBA가 참조할 수 있도록 합니다.)
    • 시스템 환경에 대한 정보 : 서버의 하드웨어, 소프트웨어, 네트워크 등에 대한 정보
    • 담당자 및 관계자에 대한 정보 : 시스템과 관련된 내/외부 조직에 포함되는 모든 사람과 하드웨어/소프트웨어 제품 및 서비스 공급업체 및 담당자에 대한 정보
    • 장애 기록 일지 : 발생된 문제와 문제 해결에 관한 모든 절차에 대한 기록 (장애 기록에 대한 내용은 활용 및 검색이 용이하도록 웹 기반으로 만들어, 유사한 문제의 재발 시에 신속하게 처리할 수 있도록 합니다.)
  • 스크립트화

    반복적, 주기적으로 수행하는 모든 작업들은 엔터프라이즈 관리자를 사용하는 대신, 스크립트를 작성하여 수행하는 것을 원칙으로 합니다. 스크립트를 사용하면 오류 발생 가능성을 최소화할 수 있으며 반복적인 작업을 효율적으로 수행할 수 있습니다. 스크립트는 보안을 위하여 안전한 디렉터리에 중앙 집중적으로 관리하는 것이 바람직하며, 스크립트 작성 시에는 응용 프로그램과 마찬가지로 주석을 기술하여 쉽게 이해하고 활용할 수 있도록 합니다. 만약 주석만으로 불충분한 경우에는 문서를 작성하여 관리합니다.

  • 자동화

    주기적으로 수행해야 하는 작업들은 가능한 한 자동화하여 DBA의 업무 효율성을 제고할 것을 권고합니다. 예를 들어 DB 서버 성능 데이터의 수집, 디스크 공간의 확인, 백업, 블로킹 감지, 데이터 타입 오버플로우 감지 등의 작업들은 자동화가 가능합니다. 단순히 수행을 자동화하는 차원을 넘어서, SQL Server에서 제공하는 다양한 기능들을 활용하면 자동으로 경고 메일의 발송, 문자 메시지의 발신, 문제 해결을 위한 작업의 수행 등이 가능하기 때문에, DBA가 지속적으로 시스템을 모니터링하지 않더라도 시스템에 발생한 문제를 조기에 감지하는 것이 가능합니다. DBA가 주기적으로 수행되는 작업에 할애하는 시간은 가능한 한 최소화하고, 주기적인 관리 작업을 통하여 확보한 지식을 기반으로 응용 프로그램과 서버의 성능을 향상시키기 위한 전략을 모색하는데 많은 시간을 할애하는 것이 바람직합니다.

  • 신중한 변경 관리 및 롤백 전략 수립

    운영중인 시스템에 어떤 변경작업을 수행하는 경우에는 가능한 한 충분한 사전 테스트를 거친 후에 작업해야 하며, 롤백 전략을 수립한 다음에 작업하는 것을 원칙으로 합니다. 또한 한번에 여러 가지 변경 작업을 수행하지 말고, 하나의 변경 작업을 수행하고 그 변경 작업이 미친 영향을 관찰하는 것이 바람직합니다.
    모든 변경 작업에 대해서 롤백 전략을 수립하는 것이 원칙이며, 롤백에 필요한 사항들을 문서로 기록하고 롤백에 필요한 스크립트 등을 작성하고 테스트하여 검증합니다. 특히 대용량 데이터베이스의 경우에는 문제 발생 시 복구에 소요되는 시간이 길기 때문에 충분한 사전 테스트와 롤백 전략 수립이 매우 중요합니다.


    DBA가 주기적으로 수행해야 하는 작업

    시스템에 따라 차이가 있을 수 있지만, DBA는 시스템 유지를 위하여 일반적으로 수행해야 하는 작업들에 대하여 이해하고 있어야 하며, 다음과 같은 작업들을 주기적으로 수행해야 합니다.

  • 일 단위로 수행해야 하는 작업

    • 시작되어야 할 서비스들이 제대로 시작되어 있는지 확인합니다.
    • Windows NT 또는 Windows 2000의 이벤트 뷰어를 사용하여 오류 발생 여부를 점검합니다.
    • SQL Server 오류 로그에 오류 메시지가 기록되어 있는지 점검합니다. 자세한 내용은 [SQL Server 오류 로그 보기]를 참조하십시오.
    • 데이터베이스 파일과 로그 파일의 확장에 대비하여 디스크에 충분한 여유 공간이 있는지 확인합니다.
    • 데이터베이스 파일과 로그 파일의 크기와 실제로 사용되는 공간을 모니터링하며, 공간 부족으로 자동 확장이 예상되는 경우에는 미리 파일을 확장하여 충분한 공간을 확보합니다.
    • SQL Server 작업(Job)의 성공/실패 여부를 점검합니다.
    • 매일 데이터베이스 전체 백업 또는 차등 백업을 수행하기로 되어 있는 경우라면, 데이터베이스 전체 백업을 수행합니다. 자동화되어 있는 경우에는 백업이 성공적으로 수행되었는지 점검합니다. 데이터베이스 전체/차등 백업 주기는 시스템 여건과 복원 전략에 따라 달라집니다.
    • SQL Server 트랜잭션 로그를 백업 받습니다. 자동화되어 있는 경우에는 백업이 성공적으로 수행되었는지 점검합니다. 백업 주기는 시스템 여건에 따라 백업 주기는 분 단위, 시간 단위, 일 단위로 달라질 수 있으며, 트랜잭션 백업 주기에 따라 트랜잭션 로그 파일의 크기가 달라집니다. 참고로 복원이 불필요한 테스트 DB에 대해서는 복구 모델을 단순으로 설정하면 트랜잭션 로그에 대한 주기적인 관리를 줄일 수 있습니다.
    • Master, model, msdb, 배포(distribution) 데이터베이스도 변경 사항이 있으면 주기적으로 백업해야 합니다. 시스템 카탈로그의 변경이 이루어진 후에는 master 데이터베이스의 전체 백업을 수행합니다. 경고, 작업(Job), 운영자, 로그 전달(log-shipping), 복제, DTS 패키지 등에 변경이 발생한 다음에는 msdb를 백업해야 합니다. Model 데이터베이스에 변경작업을 수행한 다음에는 model을 백업해야 합니다.
    • 시스템 모니터를 사용하여 성능 카운터를 모니터링함으로써, 적절한 성능이 유지되고 있는지 점검합니다. 최소한 시스템 모니터에서 프로세서, 메모리, 디스크(I/O), 네트워크에 대한 카운터들은 필수로 점검해야 합니다. 문제 발생 시 또는 추가적인 분석이 필요한 경우에는 관련 성능 카운터들을 추가로 분석합니다.
    • 복구 모델이 전체 복구가 아니라면, 최소 로깅 작업(Minimal-logged operation)을 수행한 다음에는 차등 백업을 수행합니다.
    • 블로킹, 교착상태(Deadlock)의 발생 여부를 점검합니다.
    • 오래 수행되는 쿼리 또는 리소스를 과다하게 사용하는 쿼리가 있는지 점검합니다.
    • 문제가 발생하면 문제 해결을 위한 활동을 수행하며, 문제 분석 및 해결 과정에 대한 내용을 가능한 한 상세하게 문서화합니다.
    • 통계 자동 갱신(Auto update statistics) 옵션이 비활성화되어 있는 데이터베이스의 테이블들에 대해서는 주기적으로 (예:매일, 매주) UPDATE STATISTICS 작업을 수행합니다.
  • 주간 단위로 수행해야 하는 작업

    • 모든 시스템 데이터베이스와 운영중인 사용자 데이터베이스에 대한 전체/차등 데이터베이스 백업을 수행합니다.
    • 통계 자동 갱신(Auto update statistics) 옵션이 비활성화되어 있는 데이터베이스의 테이블들에 대해서 UPDATE STATISTICS를 매일 또는 매주 수행합니다.
    • 인덱스의 조각화를 제거합니다. CREATE INDEX WITH DROP_EXISTING 또는 DBCC DBREINDEX를 수행하여 인덱스를 재구성함으로써 물리적, 논리적 조각화를 제거할 수 있으며, DBCC INDEXDEFRAG를 사용하면 논리적인 조각화를 제거할 수 있습니다. 자세한 내용은 온라인 설명서를 참조하십시오.
    • 대형 일괄 처리의 작업 등으로 인하여 로그 파일이 과다하게 확장된 경우에는 로그 파일의 사용되지 않는 여분의 공간을 제거합니다.
  • 월간 단위로 수행해야 하는 작업

    • 전체 운영 체제를 백업합니다.
    • 최소 월 1회 모든 시스템 데이터베이스와 운영 데이터베이스에 대하여 전체 백업을 수행해야 합니다.
    • DBCC CHECKDB를 수행하여 데이터베이스의 무결성을 점검합니다. DBCC CHECKDB를 수행하면 서비스나 다른 작업에 영향을 미칠 수 있으므로, 테스트 장비에 모든 시스템 데이터베이스와 운영 데이터베이스를 복원하고, 복원된 모든 시스템 데이터베이스와 운영 데이터베이스를 대상으로 DBCC CHECKDB를 수행하여 무결성을 점검하는 것이 바람직합니다.
    • Sqldiag.exe를 수행하고 결과를 저장합니다.
    • 성능 데이터를 수집하여 시스템이 충족시켜야 하는 기준과 비교하여, 성능 향상 및 향후의 용량 계획에 활용합니다.

    [참고] 정확한 점검을 위해서는 모든 유지 관리 활동 작업에 대하여 로그를 저장하는 것이 필요합니다. 데이터베이스 유지 관리 계획 마법사와 SQL Server 작업(Job)에서는 자동으로 작업 결과를 저장하도록 설정 가능합니다.



    (출처 : 웹마스터 - 싸이월드 페이퍼)

  • Posted by ㏈ª ☞ β┖υΕJini.κR
    TAG DBA, DBA역활

    댓글을 달아 주세요

    데이터베이스의 Collation 정보를 말합니다.


    MSSQL DBMS 는 WINDOWS OS 와 상호작용을 하여 처리하는 경우가 많이 있습니다.


    Collaction 정보역시 마찬가지 인데요 이것은, SQL Server의 기본 데이터 정렬을 설정하는 옵션 입니다. MSSQL 을 설치할 때 대부분 Korean_Wansung_CI_AS 으로 설정을 하게 됩니다. 이것의 의미는 "한국어_완성형 문자" 라는 의미 입니다.


    테이블에 데이타를 입력할경우, 특정칼럼 기준으로 정렬할경우 완성형으로 비교 판단 하게 되겠지요.


    SELECT * FROM ::fn_helpcollations()
    GO


    쿼리분석기에서 위의 명령을 내리면, MSSQL에서 지원하는 Collaction 정보들이 나오게 됩니다.


    Collaction 정보를 변경하고자 한다면,


    ALTER DATABASE CollateDb
    COLLATE Korean_Wansung_CI_AI
    GO

    이렇게 하시면 됩니다.


    확인은 아래처럼 하시구요...

    EXEC sp_helpdb 'CollateDb'
    GO



    이렇게 했을때 오류가 발생할 수도 있습니다.

    "칼럼 'xxx'는 데이터베이스 데이타 정렬에 종속되어 있습니다."


    위의 오류가 나타나면, 데이터베이스에 있는 개체들이 데이터베이스의 정렬에 종속되어 있는 경우에 발생되는 오류입니다. 따라서 해당 개체들을 다른 Collation으로 변경을 한 다음에 데이터베이스 Collation으로 변경을 하셔야 합니다.


    아래는 Collaction 의 변경에 대한 구문입니다.


    Korean_Wansung_CI_AI  으로의 변경

    ALTER TABLE 테이블명
    ALTER COLUMN 칼럼명 VARCHAR(50) COLLATE Korean_Wansung_CI_AI
    GO



    ALTER TABLE 테이블명
    ALTER COLUMN 칼럼명 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS 
    GO


    sp_help 테이블명 을 통해서 결과를 확인해 보시면 되겠습니다.




    만약  아래와 같은 메시지가 나왔다면,

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

    서버: 메시지 5074, 수준 16, 상태 8, 줄 1
    개체 'pk_테이블명은'은(는) 열 '칼럼명'에 종속되어 있습니다.
    서버: 메시지 4922, 수준 16, 상태 1, 줄 1
    하나 이상의 개체가 이 열을 액세스하기 때문에 ALTER TABLE ALTER COLUMN '칼럼명'이(가) 실패했습니다.

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



    온라인 설명서에서 "데이터 정렬 변경"이라는 제목의 문서를 보시기 바랍니다.

    애러의 원인은 다음과 같습니다.

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

    현재 다음 항목에서 참조하는 열의 데이터 정렬은 바꿀 수 없습니다.

    • 계산된 열
    • 인덱스
    • 자동으로 또는 CREATE STATISTICS 문에 의해 생성된 배포 통계
    • CHECK 제약 조건
    • FOREIGN KEY 제약 조건

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




    위의 문제역시 메뉴얼을 보시면 해결 절차가 나와있느니 매뉴얼을 참고하세요


    간단한 철차를 말씀드리면,

     기본키 제거 -> Collation 변경 -> 기본키 재생성 의 순서에 따라서 Collation의 정보를 변경 하시면 됩니다.

    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요

    조회조건이 유동적인 프로시저호출


    --정적 쿼리를 사용하는 프로시저에서
    1. IF, OR과 case를 피하라


    -부하가 많이 걸리는 예
    ( case @OrderID when '%' then @OrderID  else OrderID end )  = @OrderID
    (OrderID = @orderid OR @orderid IS NULL)

    -권장되는예(NULL이 없어야함)
    od.ProductID = coalesce(@prodid, od.ProductID)


    2. coalesce 사용


    -NULL이 존재하는 경우의 검색방법
    1)컬림이 int형일 경우
    DECLARE @minint  int, @maxint  int

    SELECT @minint = convert(int, 0x80000000),
           @maxint = convert(int, 0x7FFFFFFF)

    OrderID BETWEEN coalesce(@orderid, @minint) AND
                    coalesce(@orderid, @maxint)

    *NULL이 들어갈 수없음(int구조이기때문)


    2) 컬림이 char(nchar)일 경우
    DECLARE @mincustid nchar(10), @maxcustid nchar(10),

    IF @custid IS NULL
          SELECT @mincustid = MIN(CustomerID), @maxcustid = MAX(CustomerID)
          FROM Customers

    CustomerID BETWEEN coalesce(@custid, @mincustid) AND
                       coalesce(@custid, @maxcustid)

    *NULL이 존재하는 char(nchar)형식일 경우 최소/최대값을 이용하도록 한다


    3) 컬럼이 varchar(nvarchar)일 경우 (OR쓸 수 밖에..)
    (Region = @region OR @region IS NULL)


    4) 날짜 컬럼일 경우
    AND  OrderDate >= coalesce(@fromdate, '17530101')
    AND  OrderDate <= coalesce(@todate, '99991231')


    5) LIKE검색이 필요한 컬럼
    ProductName LIKE coalesce(@prodname + '%', '%')


    -- coalesce경우 다른(OR, CASE)처럼 만큼은 아니지만 scan을 피할 수없다.
    따라서, 위의 방법으로도 성능이 나쁘다면 다이나믹 SQL(sp_executesql)을 사용하라


    *다이나믹 SQL(sp_executesql)을 사용고려

    -장점
    OR, CASE와는 달리 scan을 최소한으로 줄이는 최적화된 index seek쿼리가 가능하므로
    수행속도가 빠르다.


    -단점(알려진)
    1) 항상 재컴파일된다?..plan cash를 할 수없다?
    정적프로시저와 다이나믹프로시저(sp_executesql)는 plan캐쉬가 다르게 작동한다.
    정적프로시저는 매개변수(input)와는 상관없이 프로시저(objectid)기준으로 plan을 만들고 캐쉬한다
    따라서, 첫번째 매개변수만 사용했을 경우, 두번째 매개변수만을 사용했을 경우 plan이 만들어져 있다면 캐쉬한다.


    다이나믹프로시저(sp_executesql)는 statment기준으로 캐쉬한다

    첫번째 매개변수만을 넣어 생성한 statment와 두번쨰 매개변수만을 넣어 생성한 statment는 다른 plan을 세우지만 해당 statment자체를 캐쉬하므로 다음부터 동일패턴이 들어오면 캐쉬한다.

    따라서, 정적프로시저보다 매개변수 패턴만큼 plan을 세우므로 정적프로시저보다 나쁘다고 말할 수는 있지만, 상황에 따라 많은 Scan(논리적읽기가 커짐)현상으로 정적프로시저의 성능이 나빠진다면, 오히려 해당 statment에 최적화된 쿼리를 생성하는 다이나믹프로시저(sp_executesql)를 사용하는 것이 정확한 인덱스를 사용하면서 캐쉬할 수있다(재사용 가능)


    이것은, ad-hoc쿼리와는 다른것이다(해당 쿼리는 정확히 일치하지 않으면 다 다른 것으로 간주되지만 다이나믹프로시저(sp_executesql)는 parameter방식이기 때문에 성능이 좋다)


    2) 보안설정을 할 수없다
    정적프로시저는 해당 프로시저에 소유자 기반의 보안(Role)을 줄 수있지만, 다이나믹프로시저는 그럴수없다.
    이것은 조회조건을 제외한 나머지 메인쿼리부분을 view나 function을 사용하여 Role을 주고
    이것을 다이니믹프로시저에서 select하여 조회조건부분을 적용하면 해결할 수있다
    (단지, view(function)을 하나 더 만들어야하는 단점이 있지만, 실제로 프로시저에 role을 주는 곳은
    많지 않고(금융권), 모든 프로시저를 다이나믹프로시저(수행이 나쁜 것만 선별적 테스트하여 적용)로 사용하지 않기때문이다


    3) 유지보수가 힘들다
    그렇다고 볼 수있다. 하지만, 모든 프로시저를 다이나믹프로시저(sp_executesql)를 사용하지 않고
    문제가 된는 프로시저와 비교테스트하여 사용한다면 좋은 결과를 얻을 수있는데도 불구하고, 유지보수가 힘들다라고 아예 배제하는 것은 더욱 좋지않다


    4) SQL Injection의 위험이 있다
    parameter방식으로 프로시저에서 사용하며, sp_executesql는 내부적으로 별도의 parameter를 더 사용하여 연결하므로 실제 테스트(SQL Injection을 유발하는 value값을 테스트)한 결과 Injection의 위험은 없었다


    *일반 동적조회조건을 위한 프로시저를 생성할 시 현재까지는 coalesce방식이 작성도 쉽고, 유지보수도 쉬우므로 이 방식을 택하는 것으로한다(더 나은 방식이있다면 제시해주세요..please!!)
    하지만, 이렇게 해도 수행속도가 나빠진다면 다이나믹프로시저(sp_executesql)를 비교테스트하여
    적용을 고려하는 것도 좋은 방법이다.


    *SQL2005에서 프로시저가 캐슁되는지 판별방법

    SELECT TOP 10 usecounts, size_in_bytes, cacheobjtype, objtype,
    REPLACE (REPLACE ([text], CHAR(13), '  '), CHAR(10), ' ') AS sql_text
    FROM sys.dm_exec_cached_plans AS p
    CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
    WHERE p.objtype = 'Proc' AND cacheobjtype = 'Compiled Plan'
    ORDER BY usecounts DESC
    (정적 프로시저 , 다이나믹프로시저 모두 볼 수있다)


    SELECT TOP 10 usecounts, size_in_bytes, cacheobjtype,
    SUM (total_worker_time / 1000) AS total_cpu_time_in_ms,
    SUM (total_physical_reads) AS total_physical_reads,
    SUM (total_logical_reads) AS total_logical_reads,
    SUM (total_logical_writes) AS total_logical_writes,
    REPLACE (REPLACE ([text], CHAR(13), '  '), CHAR(10), '  ') AS sql_text
    FROM sys.dm_exec_cached_plans AS p
    INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle
    CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
    WHERE p.objtype in ('Proc','Prepared') AND cacheobjtype = 'Compiled Plan'
    GROUP BY usecounts, size_in_bytes, cacheobjtype, [text]
    ORDER BY usecounts DESC
    (정적 프로시저 , 다이나믹프로시저(statment기준)별로 IO(읽기/쓰기) 및 cpu사용시간등을 볼 수있다)


    DBCC FREEPROCCACHE
    (캐쉬를 모두 지운다)


    SET STATISTICS IO ON
    SET STATISTICS TIME ON
    을 사용하여 IO와 TIME을 볼 수있다


    (샘플예)

    1. coalesce

    http://www.sommarskog.se/dynsearch/search_orders_5.sp

    2. 다이나믹프로시저(sp_executesql)

    http://www.sommarskog.se/dynsearch/search_orders_1.sp


    참고)

    1. Windows & .NET Magazine 2005년 11월

    2. http://www.sommarskog.se/dyn-search.html

    3. http://www.powerdb.net/?inc=read&aid=506&criteria=mssql&subcrit=&id=&limit=20&keyword=&page=1

    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요

    흔히, PLAN를 재사용하여야 하고, RECOMPILE은 나쁘다라고 한다.

    과연 정말 그럴까?


    How Values with Irregular Selectivity Impact SQL Server Database Performance

    (불규칙적인 선택도를 가지는 값이 SQL서버의 성능에 얼만큼 영향을 미칠까? 정도의 해석)

    http://www.lakesidesql.com/articles/?p=8 (SQL2000기준)

    에서 이에 대한 명확한 설명과 예제가 있다.


    이것을 SQL 2005에 맞게 다시 재정리했다.


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

    테이블 및 인덱스 생성

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

    create table Customers (
        n int identity primary key,
        SSN char(9) not null,
        Amount money not null,
        Name varchar(128)
    )
    GO
    create index IX_SSN on Customers (SSN)
    GO


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

    태스트 데이터 입력

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

    set nocount on
    declare @n int
    set @n=100000
    loop:
        insert into Customers (SSN,Name,Amount)
            select convert(varchar,@n),'This is our US customer N'+
                convert(varchar,@n),@n*100
        set @n=@n-1
        if @n>0 goto loop

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

    태스트 데이터 입력

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

    declare @n int
    set @n=10000
    loop:
        insert into Customers (SSN,Name,Amount)
            select 'N/A','This is our foreign customer N'+convert(varchar,@n),@n*30
        set @n=@n-1
        if @n>0 goto loop



    SSN컬럼은 unique social security number를 의미하는 컬럼인데 US(미국)사람은

    정상적인 unique social security number를 넣었고, 그외(미국인 아닌나라)는 N/A로

    데이터를 채운것이 문제를 발생하는 케이스이다.


    SSN컬럼은 인덱스가 걸려있고, 미국인이 아닌 사람들은 사회보장번호를 모두 N/A로

    넣게 되어 결국 unique가 되지 않게된 것이다.


    쿼리나 프로시저를 수행하기에 앞서

    Microsoft SQL Sever Management Studio의 툴바에서 실행계획포함을

    클릭한 후


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

    set statistics io on


    쿼리/프로시저 수행


    set statistics io off

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


    위와 같이 테스트 하기 바란다.


    1) Ad-hoc queries쿼리


    1-1)

    select min(Amount) from Customers where SSN='345'


    결과

    --index seek
    --논리적읽기 6


    1-2)

    select min(Amount) from Customers where SSN='N/A'


    결과

    --scan
    --논리적 읽기 881


    1-3)

    select min(Amount) from Customers with(index=IX_SSN) where SSN='N/A'


    결과

    --index seek
    --논리적 읽기 30658


    위의 세가지 예를 보자!

    1-2가 1-1보다 논리적 읽기를 많이 했고, scan을 했다.


    N/A를 찾는데 881개의 논리페이지를 읽어 계산한 것은, 우리가 넣은 N/A가 넣은 1만건과

    비교해볼 때 10%도 안되는 범위안이니 만큼 그리 문제가 되지 않는다.


    문제는 index seek를 하지 않고 scan을 했다는 점인데, SQL서버가 자동으로 인덱스를

    통해 찾는 것보다 scan을 통해 찾는 것이 더 낫다고 판단한 경우인데 이게 맞는 판단이냐는

    것이다.


    1-3이 강제로 SSN컬럼에 생성한 인덱스를 힌트를 사용하여 강제한 경우이다.

    결과는 참혹했다. 논리페이지를 30658페이지를 뒤져 결과를 내놓았다. 이것은 레코드건수의

    약 3배정도를 더 찾은 경우이다.


    즉, 시퀄은 똑똑한 넘이라는 것이다!


    2) normal procedure


    2-1)

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

    CREATE procedure [dbo].[SelectMinAmount]
        @ssn char(9)
    as
        select min(Amount) from Customers where SSN=@ssn

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


    exec SelectMinAmount '345'

    exec SelectMinAmount 'N/A'


    결과 : 프로시저는 동일 프로지저호출일 경우 맨처음 사용한 Plan을 사용함
             scan을 해야 할 exec SelectMinAmount 'N/A'가
             exec SelectMinAmount '345' 수행했던 index seek를 이용하므로 해서
             오히려 성능저하(논리적 읽기 30024)


    2-2)

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

    CREATE procedure [dbo].[SelectMinAmount_Copy]
        @ssn char(9)
    as
        select min(Amount) from Customers where SSN=@ssn

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


    exec SelectMinAmount_Copy 'N/A'
    exec SelectMinAmount_Copy '345'


    결과 : exec SelectMinAmount_Copy 'N/A'가 맨처음 사용한 Plan 을 사용함으로 해서
             exec SelectMinAmount_Copy '345' 는 Scan을 사용하게된다.

             따라서, index seek를 하지못하고 오히려 성능저하 (논리적 읽기 수 881)


    정리하자면, 프로시저는 수행될 때 최초 생성한 Plan을 재사용하게 되는데

    2-1에서는 index seek한 plan을 재사용하므로 해서 scan을 타야할 2번째 프로시저는

    인덱스 힌트를 강제로 사용한 것과 같이 더 성능이 나빠졌다.


    2-2에서는 scan을 한 plan을 재사용하므로 해서 index seek를 타야할 2번째 프로시저는

    scan을 타므로 해서 성능이 더 나빠진 경우이다.


    ★★이제부터 이를 해결하는 법을 소개한다


    3) procedure and with recompile


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

    CREATE procedure [dbo].[SelectMinAmount1]
        @ssn char(9)
    with recompile
    as
        select min(Amount) from Customers where SSN=@ssn

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

    --scan
    exec dbo.SelectMinAmount1 'N/A'
    --index seek

    exec dbo.SelectMinAmount1 '345'


    with recompile를 사용하므로 해서 프로시저도 재사용하지 않고, Plan도 만들어 진것이 없다

    매번 Plan을 만들어서 사용하므로 SQL서버는 자신이 판단하여 index seek를 사용하는 것이

    좋은지 scan을 타야 좋은지 판단한다.

    결과는 SQL서버는 제대로 판단해서 (1)과 (2)와 같은 문제를 유발 하지 않았다


    이것은 정확히 우리가 원하는 결과임.


    이전 포스트(동적조회조건에서의 정적프로시저와 동적프로시저)

    에서 소개한 아래 쿼리를 사용하여보면 알 수있다.

    (http://blog.naver.com/unbimanse?Redirect=Log&logNo=30010736126)


    ♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣

    [1]프로시저를 몇번 재사용했는지 알 수 있다

    ♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣

    SELECT TOP 100 usecounts, size_in_bytes, cacheobjtype, objtype,
    REPLACE (REPLACE ([text], CHAR(13), '  '), CHAR(10), ' ') AS sql_text
    FROM sys.dm_exec_cached_plans AS p
    CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
    WHERE p.objtype = 'Proc' AND cacheobjtype = 'Compiled Plan'
    ORDER BY usecounts DESC


    ♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣

    [2]쿼리 혹은 프로시저를 실제 몇 번 Plan cache를 알 수 있다

    ♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣

    SELECT TOP 100 usecounts, size_in_bytes, cacheobjtype, p.objtype,
    SUM (total_worker_time / 1000) AS total_cpu_time_in_ms,
    SUM (total_physical_reads) AS total_physical_reads,
    SUM (total_logical_reads) AS total_logical_reads,
    SUM (total_logical_writes) AS total_logical_writes,
    REPLACE (REPLACE ([text], CHAR(13), '  '), CHAR(10), '  ') AS sql_text
    FROM sys.dm_exec_cached_plans AS p
    INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle
    CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
    WHERE p.objtype in ('Proc','Prepared','Adhoc') AND cacheobjtype = 'Compiled Plan' AND [text] NOT LIKE 'SELECT TOP%'
    GROUP BY usecounts, size_in_bytes, cacheobjtype,p.objtype, [text]
    ORDER BY usecounts DESC


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

    * Proc는 프로시저를 말하며 [text]에 프로시저명을 볼 수 있다 (Plan cache)


    * Prepared는 주로 sp_executesql를 사용할 때 나타나는데 parameterized된 statement기준으로 Plan cache한다.

    예를 들면 (@xssn char(9))select min(Amount) from Customers where SSN=@xssn 와 같은 statement로 cache하는 것이 adhoc쿼리와 다른점이다


    * adhoc는 select min(Amount) from Customers where SSN='345'와 select min(Amount) from Customers where SSN='346' 을 다른 쿼리로 인식한다. 즉, string이 정확히 일치 해야만 cache한다

    하지만, prepared는 위의 두가지 쿼리를 같은 쿼리로 인식하여 동일 plan을 재사용(cache)한다


    자세한사항은 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

    를 참조하라.

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


    4) dynamic SQL (EXEC) and with recompile / with recompile 미사용


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

    CREATE procedure [dbo].[SelectMinAmount2]
        @ssn char(9)
    --with recompile
    as
        exec('select min(Amount) from Customers '+
            'where SSN='''+@ssn+'''')

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


    exec dbo.SelectMinAmount2 'N/A'
    exec dbo.SelectMinAmount2 '345'
    exec dbo.SelectMinAmount2 '346'


    [2]를 통해서 plan cache를 측정하면

    select min(Amount) from Customers where SSN='345      '
    select min(Amount) from Customers where SSN='346      '
    select min(Amount) from Customers where SSN='N/A      '


    결과는 with recompile을 사용하지 않으면, 프로시저는 재사용 했지만( [1]수행해보라 )

    plan cache는 하지 못했다. 즉, adhoc과 동일한 결과(정확히 일치하는 문장만 cache)이다.


    with recompile를 사용하면, 프로시저도 재사용안하고, adhoc과 동일한 cache매카니즘을

    따른다.(<- 최종 결과는 SQL서버가 판단하게 하므로 원하는 답은 얻을 수있으나 별로 권장되지

    않는 방법)


    5) sp_executesql 와 with recompile미사용


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

    CREATE procedure [dbo].[SelectMinAmount3]
        @ssn char(9),
        @debug  bit = 0
    as
     DECLARE @sql        nvarchar(max),                                
       @paramlist  nvarchar(max)

        SELECT @sql = 'select min(Amount) from Customers '+
            'where SSN=@xssn'

     IF @debug = 1                                                     
      PRINT @sql

     SELECT @paramlist = '@xssn  char(9)'

     EXEC sp_executesql @sql, @paramlist, @ssn

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


    exec dbo.SelectMinAmount3 'N/A' --논리적 읽기 수 881
    exec dbo.SelectMinAmount3 '345' --논리적 읽기 수 881
    exec dbo.SelectMinAmount3 '346' --논리적 읽기 수 881


    결과 모두 프로시저 재사용, Plan cache(Proc레벨)함

    Plan을 재사용하므로 해서 맨 첫번째인 exec dbo.SelectMinAmount3 'N/A'
    를 수행시 생성한 scan을 하는 plan이 캐쉬되어 나머지 모든 프로시저 수행도

    scan을 하게되었다


    이것은 문제가 된다!


    6) sp_executesql 와 with recompile사용


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

    CREATE procedure [dbo].[SelectMinAmount3_1]
        @ssn char(9),
        @debug  bit = 0
    as
     DECLARE @sql        nvarchar(max),                                
       @paramlist  nvarchar(max)

        SELECT @sql = 'select min(Amount) from Customers '+
            'where SSN=@xssn OPTION(RECOMPILE)'

     IF @debug = 1                                                     
      PRINT @sql

     SELECT @paramlist = '@xssn  char(9)'

     EXEC sp_executesql @sql, @paramlist, @ssn

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


    exec dbo.SelectMinAmount3_1 'N/A' --논리적 읽기 수 881
    exec dbo.SelectMinAmount3_1 '345' --논리적 읽기 수 6
    exec dbo.SelectMinAmount3_1 '346' --논리적 읽기 수 6

    결과는 프로시저를 재사용했으며, Plan cache는 Prepared수준에서 재사용했음

    이것은 정확히 우리가 원하는 결과임.


    **여기서는 with recompile를 사용하지 못한다.

    OPTION(RECOMPILE)을 사용하여야 한다.


    7) IF와 index hint사용


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

    CREATE procedure [dbo].[SelectMinAmount4]
        @ssn char(9)
    as
        if @ssn = 'N/A'
            select min(Amount) from Customers where SSN='N/A'
        else
            select min(Amount) from Customers with(index=IX_SSN) where SSN=@ssn

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


    exec dbo.SelectMinAmount4 'N/A' --논리적 읽기 수 881
    exec dbo.SelectMinAmount4 '345' --논리적 읽기 수 6
    exec dbo.SelectMinAmount4 '346'  --논리적 읽기 수 6

    결과는 프로시저 재사용 및 Plan cache도 Proc수준으로 재사용했음

    결과도 원하는 결과임

    하지만, 이것은 SQL서버가 판단하는 것이 아니라 사용자가 IF로 분기한 것이다.

    즉, 지금은 컬럼이 하나여서 그렇지 IF판단기준이 많으면 코딩은 점점 커지고 복잡하게 될 것이다

    그리 권장할 만한 바는 못된다


    자 이렇게 다양한 방법으로 테스트를 하였다.


    정리를 해보자!


    최초 질문은

    "흔히, PLAN를 재사용하여야 하고, RECOMPILE은 나쁘다라고 한다.

    과연 정말 그럴까?" 에서 출발하였다..


    이제 Plan을 항상 재사용한다고 해서, Recompile을 매번 한다고 해서 항상 나쁘다고 말할 수있을까?


    [SQL2000상황]

    문제는 여기에 있다. 프로시저가 재컴파일을 하면 다른 사용자가 동일한 프로시저를 호출시에

    재컴파일 할 동안 기다린다는데 문제가 있다고 하겠다. 항상 재컴파일 하고 다수의 사용자가 기다릴테니깐....


    SQL2005에서는 이러한 상황 어떻게 달라졌는지는 아직 모르겠다.


    따라서, 이것에 대한 답은 미진하지만 결론은 아래와 같다.


    우선, 판단의 근거는 다음과 같다.

    보통 Recompile옵션이 필요한 경우가 모든 상황에서 그렇다는 것이 아니다

    라는 것이다.


    ♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣

    개발을 끝내고 오픈 테스트 혹은 운영시에 갑자기 성능이 나빠진다면, 그리고 그런 상황이

    위에서 발생한 상황과 같은 종류의 상황이라면

    문제를 발생시키는 프로시저만 Recompile옵션을 주고 해보는 것이다.


    판단은 그때가서 비교해보면서 결정하는것이다.


    아무런 대책이 없다는 것과 위와 같이 이러한 Tip도 있으니 비교해서 상황에 맞게

    대처하라는 것이다.


    즉, Plan을 반드시 재사용해야한다. Recompile이 되서는 안된다라고

    못박지 말자라는 것이다.!!

    ♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣


    **반드시 [1],[2]을 사용해가면서 어떤 쿼리가 프로시저가 캐쉬를 하는지 안하는지 잘 살펴보기를 바란다

    **DBCC FREEPROCCACHE (캐쉬초기화)

    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요

    select * from sysobjects  

    : 데이터베이스에 만들어진 객체들의 정보를 반환합니다.


    exec sp_columns TableName

    : TableName에 지정된 Table의 정보를 반환합니다.


    SELECT * FROM INFORMATION_SCHEMA.TABLES

    : 현재 사용자가 권한을 가진 현재 데이터베이스의 각 테이블에 대한 정보를 반환합니다.

    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요

    SELECT TOP page_size * FROM table WHERE primary_key NOT IN
        (SELECT TOP page_size * (page_number - 1) primary_key FROM table
         WHERE filter_conditions
         ORDER BY sort_field)
    AND filter_criteria
    ORDER BY sort_field



    [리스트 2] 기본키에 검색조건을 먼저 설정하는 일반화 쿼리
    SELECT * FROM table WHERE primary key IN
        (SELECT TOP page_size primary_key FROM table
         WHERE primary_key NOT IN

              (SELECT TOP page_size * (page_number - 1) primary_key FROM table
               WHERE filter_conditions ORDER BY sort_field) AND filter_criteria
         ORDER BY sort_field)
    ORDER BY sort_field


    [리스트 3] SELECT_WITH_PAGING 저장 프로시저
    CREATE PROCEDURE SELECT_WITH_PAGING (
    @strFields varchar(4000),
    @strPK varchar(100),
    @strTables varchar(4000),
    @intPageNo int = 1,
    @intPageSize int = NULL,
    @blnGetRecordCount bit = 0,
    @strFilter varchar(8000) = NULL,
    @strSort varchar(8000) = NULL,
    @strGroup varchar(8000) = NULL)
    /* 매개변수에 따라 반환되는 결과값을 특정 페이지로 정의하거나 전체 행을 모두 반환할 수 있도록 설정한다. */
    AS
    DECLARE @blnBringAllRecords bit
    DECLARE @strPageNo varchar(50)
    DECLARE @strPageSize varchar(50)
    DECLARE @strSkippedRows varchar(50)
    DECLARE @strFilterCriteria varchar(8000)
    DECLARE @strSimpleFilter varchar(8000)
    DECLARE @strSortCriteria varchar(8000)
    DECLARE @strGroupCriteria varchar(8000)
    DECLARE @intRecordcount int
    DECLARE @intPagecount int
    /* 페이징 조건 정규화 의미있는 페이징 조건이 입력되지 않은 경우, 페이징하지 않고 좀 더 효율적인 방법으로 쿼리를 실행시키기 위해 blnBringAllRecords 플래그를 사용 */
    IF @intPageNo < 1
    SET @intPageNo = 1
    SET @strPageNo = CONVERT(varchar(50), @intPageNo)
    IF @intPageSize IS NULL OR @intPageSize < 1
    ?- 페이징하지 않고 전체 행을 반환
       SET @blnBringAllRecords = 1
    ELSE
       BEGIN
         SET @blnBringAllRecords = 0
         SET @strPageSize = CONVERT(varchar(50), @intPageSize)
         SET @strPageNo = CONVERT(varchar(50), @intPageNo)
         SET @strSkippedRows = CONVERT(varchar(50), @intPageSize * (@intPageNo - 1))
       END
    /* 정렬 및 필터링 조건 정규화 정렬 및 필터링 조건이 지정되지 않으면, 필터링이나 정렬작업이 수행되지 않도록 하여 쿼리의 성능을 향상시킴.*/
    IF @strFilter IS NOT NULL AND @strFilter != ''
       BEGIN
         SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
         SET @strSimpleFilter = ' AND ' + @strFilter + ' '
       END
    ELSE
       BEGIN
         SET @strSimpleFilter = ''
         SET @strFilterCriteria = ''
       END
    IF @strSort IS NOT NULL AND @strSort != ''
       SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
    ELSE
       SET @strSortCriteria = ''
    IF @strGroup IS NOT NULL AND @strGroup != ''
       SET @strGroupCriteria = 'GROUP BY' + @strGroup + ' '
    ELSE
       SET @strGroupCriteria = ''
    /* 실제 조회작업을 시작 */
    IF @blnBringAllRecords = 1 -- 페이징 하지 않고 단순한 SELECT 문장만을 실행
       BEGIN

         EXEC (
                   'SELECT ' + @strFields + 'FROM' + @strTables + @strFilterCriteria +
                   @strGroupCriteria + @strSortCriteria
                   )
       END
    -- 전체 레코드를 반환.
    ELSE -- 지정된 페이지를 반환
       BEGIN
         IF @intPageNo = 1 -- 맨 처음 페이지를 찾기 때문에 서브쿼리가 없어서 가장 효율적으로 실

                                     행된다.
            EXEC (
                      'SELECT TOP' + @strPageSize + ' ' + @strFields + 'FROM' + @strTables +
                      @strFilterCriteria + @strGroupCriteria + @strSortCriteria
                     )
         ELSE -- 특정 페이지를 선택하기 위해 서브쿼리 구조를 실행한다.
            EXEC (
                       'SELECT' + @strFields + 'FROM' + @strTables + 'WHERE' + @strPK + 'IN' + '
                            (SELECT TOP' + @strPageSize + ' ' + @strPK + 'FROM' + @strTables +
                           ' WHERE' + @strPK + 'NOT IN' + '
                                (SELECT TOP' + @strSkippedRows + ' ' + @strPK + 'FROM' + @strTables +
                                 @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
                                 @strSimpleFilter +
                                 @strGroupCriteria +
                             @strSortCriteria + ') ' +
                        @strGroupCriteria +
                        @strSortCriteria
                     )
       END -- 특정 페이지를 지정한 경우
    /* 전체 레코드 수를 반환하도록 지정된 경우 */
    IF @blnGetRecordCount = 1
    IF @strGroupCriteria != ''
        EXEC (
                   'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
                    @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)
                 )
    ELSE
        EXEC (
                   'SELECT COUNT(*) AS RECORDCOUNT FROM' + @strTables + @strFilterCriteria

                   + @strGroupCriteria)
    GO

    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요

    제 1 장 데이터의 검색
    SQL 명령어는 다음과 같이 기술한다.
    ■ SQL 명령어는 한 줄 혹은 여러 줄에 기술한다.
    ■ 일반적으로 절들은 수정하기 쉽게 다른 줄에 기술한다.
    ■ TAB 을 사용할 수 있다.
    ■ SQL 명령어 단어는 생략하거나 분리할 수 없다.
    ■ SQL 명령어는 대소문자를 구분하지 않는다.
    ■ SQL 명령어는 ; 으로 종료한다.
    ■ SQL 명령어는 SQL BUFFER 에 저장된다.
    ■ SQL BUFFER 에 저장된 SQL 명령어는 / 혹은 RUN 으로 실행할 수 있다.
    SQL*PLUS 명령어는 다음과 같이 기술한다.
    ■ SQL*PLUS 명령어는 기본적으로 한 줄에 기술한다.
    ■ SQL*PLUS 명령어는 대소문자를 구별하지 않는다.
    ■ SQL*PLUS 명령어는 SQL BUFFER 에 저장되지 않는다.
    ■ SQL*PLUS 명령어는 다음과 같다.
    • DESCRIBE table명 : TABLE 의 구조를 보여준다.
    • SAVE file명 : SQL BUFFER 를 file 로 저장한다.
    • START file명 : file 을 수행한다.
    • @ file명 : file 을 수행한다.
    • EDIT file명 : EDITOR 를 사용하여 file 을 편집한다.
    • SPOOL file명 : QUERY 결과를 file 에 저장한다.
    • SPOOL OFF : SPOOL FILE 을 닫는다.
    • HOST : SQL*PLUS 를 떠나지 않고 HOST 상태로 간다.
    • HELP 명령어 : SQL, SQL*PLUS, PL/SQL 에 대한 HELP 를 보내준다.
    • EXIT : SQL*PLUS 를 종료한다.

    전체 데이타의 검색
    가장 간단한 SELECT 문장의 형식은 다음과 같다.
    . SELECT 절에는 검색하고 싶은 COLUMN 명들을 기술한다.
    . FROM 절에는 SELECT 절에서 기술된 COLUMN 명들이 포함된 TABLE 명을 기술한다.
    TABLE 의 모든 ROW 와 모든 COLUMN 을 검색한다.
    SELECT *
    FROM table명 ;
    [ 예제 ]
    S_DEPT TABLE 로부터 모든 ROW 와 COLUMN 을 검색하시오.
    SELECT *
    FROM S_DEPT ;

    특정 column의 검색
    SELECT 절에서 검색하고자 하는 COLUMN 명을 COMMA 를 사용하여 나열한다.
    COLUMN 순서는 검색하고 싶은 순서대로 나열한다.
    COLUMN HEADING 은 COLUMN 명이 대문자로 출력된다.
    SELECT column명, column명, column명,..
    FROM table명 ;
    [ 예제 ]
    S_EMP TABLE 로부터 ID, LAST_NAME, START_DATE 를 검색하시오.

    SELECT ID, LAST_NAME, START_DATE
    FROM S_EMP ;

    산술식을 사용한 검색
    산술 연산자를 사용하여 검색되는 데이타 값을 변경할 수 있다.
    산술 연산식은 COLUMN 명, 상수 값, 산술 연산자로 구성된다.
    SELECT 산술연산식
    FROM table명 ;
    [ 예제 ]
    S_EMP TABLE 로부터 ID, LAST_NAME, 연봉을 검색하시오.
    연봉은 SALARY * 12 로 계산한다. (+,-,*,/,())
    SELECT ID, LAST_NAME, SALARY * 12
    FROM S_EMP ;

    Column alias
    기본적으로 COLUMN HEADING 은 COLUMN 명이 대문자로 출력된다.
    그러나 많은 경우 COLUMN 명이 이해하기 어렵거나 무의미하기 때문에 COLUMN ALIAS 를 사용하여
    COLUMN HEADING 을 변경할 수 있다.
    ANSI SQL 92 와 호환을 위해 ALIAS 앞에 AS 를 붙일 수 있다.
    ALIAS 에 공백이나 특수문자가 포함되거나 대소문자를 구별하고 싶으면 " " 로 막아준다.
    COLUMN ALIAS 를 사용하여 COLUMN HEADING 을 변경할 수 있다.
    SELECT column명 alias, column명 "alias", column명 as alias
    FROM table명 ;
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME, (SALARY + 100) * 12, DEPT_ID 를 검색하시오.
    단, COLUMN ALIAS 는 Employee, ANNUAL_SALARY, DEPARTMENT NO 로 정의하시오.
    SELECT LAST_NAME "Employee", (SALARY + 100) * 12 AS ANNUAL_SALARY,
    DEPT_ID "DEPARTMENT NO"
    FROM S_EMP ;

    Column의 결합
    COLUMN 과 다른 COLUMN, 산술연산식, 상수 값과 결합하여 하나의 COLUMN 으로 결합할 수 있다.
    SELECT column명|| column명
    FROM table명;
    [ 예제 ]
    S_EMP TABLE 에서 FIRST_NAME 과 LAST_NAME 을 결합하여 ALIAS EMPLOYEE 로 검색하시오.
    SELECT FIRST_NAME || LAST_NAME EMPLOYEE
    FROM S_EMP ;

    Null값 처리
    특정 COLUMN 에 값이 입력되어 있지 않을 때, 그 값을 NULL 이라 부른다.
    NULL 값은 0 이나 공백과 같지 않다.
    NULL 값이 산술 연산식에 포함되면 그 결과도 NULL 이다.
    그러므로 NVL FUNCTION 을 사용하여 NULL 값을 다른 값으로 대체하여야 한다.
    NULL 값을 다른 값으로 대체한다.
    NVL (number_column, 9)
    NVL (date_column, '01-JAN-95')
    NVL (character_column, 'ABCDE')
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME, COMMISSION 값을 검색하시오.
    COMMISSION 은 SALARY * COMMISSION_PCT /100 으로 계산하시오.
    SELECT LAST_NAME, SALARY * NVL(COMMISSION_PCT,0) /100 COMMISSION
    FROM S_EMP ;

    중복 row의 제거
    SELECT 결과 값에 중복된 값이 있을 때 중복을 피하고 UNIQUE 하게 검색한다.
    중복된 ROW 를 제거한다.
    SELECT DISTINCT column명, column명
    FROM table명;
    [ 예제 ]
    S_DEPT TABLE 에서 NAME 이 중복되지 않게 검색하시오.
    SELECT DISTINCT NAME
    FROM S_DEPT ;

    데이타의 정렬
    SELECT 되는 ROW 의 순서는 알 수 없다.
    그러므로 ROW 를 SORT 하고 싶으면 ORDER BY 절을 사용하여야 한다.
    DATA 의 DEFAULT SORT 순서는 ASCENDING 이며 다음과 같다.
    • 숫자 : 1 에서 999 순으로 SORT 한다.
    • 날짜 : 01-JAN-92 에서 01-JAN-95 순으로 SORT 한다.
    • 문자 : A 에서 Z 순서로 SORT 한다.
    • NULL : ASC 순에서는 뒤에, DESC 순에서는 앞에 나온다.
    역순으로 SORT 하고 싶으면 COLUMN 명 뒤에 DESC 를 붙인다.
    COLUMN 명 대신에 ALIAS 혹은 SELECT 한 COLUMN 의 순서로 지정할 수도 있다.
    SELECT expr
    FROM table명
    ORDER BY {column명, expr} [ASC|DESC] ;
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME, DEPT_ID, START_DATE 를 LAST_NAME 순으로 검색하시오.
    SELECT LAST_NAME, DEPT_ID, START_DATE
    FROM S_EMP
    ORDER BY LAST_NAME ;

    특정 row의 검색
    WHERE 절에서 조건식을 기술하여 조건을 만족하는 ROW 만 검색할 수 있다.
    조건식은 COLUMN 명, COMPARISON OPERATOR, VALUE 로 구성되어 있다.
    문자 값은 ' ' 으로 묶어주고 값의 대소문자를 구별하여 적어준다.
    날짜 값은 ' ' 으로 묶어주고 지정된 날짜 형태로 적어준다. '01-MAR-97'
    숫자값은 값만 적어준다.
    특정 ROW 만 검색한다.
    SELECT expr
    FROM table명
    WHERE expr operator value
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME 이 Magee 인 사원의 FIRST_NAME, LAST_NAME,
    TITLE 을 검색하시오. (=,>,<,>=,<=,<>)
    SELECT FIRST_NAME, LAST_NAME, TITLE
    FROM S_EMP
    WHERE LAST_NAME = 'Magee' ;

    Between...and
    BETWEEN OPERATOR 를 사용하여 범위를 지정할 수 있다.
    범위를 지정할 때는 작은 값을 먼저 큰 값을 나중에 지정한다.
    두 범위의 한계 값을 포함한다.
    BETWEEN...AND...
    NOT BETWEEN...AND...
    [ 예제 ]
    S_EMP TABLE 에서 START_DATE 가 09-MAY-91 에서 17-JUN-91 사이에 입사한
    사원의 FIRST_NAME, LAST_NAME, START_DATE 를 검색하시오.
    SELECT FIRST_NAME, LAST_NAME, START_DATE
    FROM S_EMP
    WHERE START_DATE BETWEEN '09-MAY-91' AND '17-JUN-91' ;

    In[list]
    IN OPERATOR 를 사용하여 나열된 값들 중에서 값을 검사한다.
    IN(LIST), NOT IN(LIST)
    [ 예제 ]
    S_EMP TABLE에서 DEPT_ID 가 10 , 31, 41 혹은 50 인 사원의 FIRST_NAME,
    LAST_NAME, DEPT_ID 를 검색하시오.
    SELECT FIRST_NAME, LAST_NAME, DEPT_ID
    FROM S_EMP
    WHERE DEPT_ID IN (10, 31, 41, 50) ;

    like
    찾고자 하는 값을 정확히 모를 때, LIKE OPERATOR 를 사용하여 문자형태가 같은 ROW 를 검색한다.
    WILDCARD 를 사용하여 문자의 형태를 지정한다.
    % : 여러 문자, _ : 한문자
    LIKE '형태', NOT LIKE '형태'
    [ 예제 ]
    S_EMP TABLE에서 LAST_NAME 이 M 으로 시작하는 사원의 LAST_NAME 을 검색하시오.
    SELECT LAST_NAME
    FROM S_EMP
    WHERE LAST_NAME LIKE 'M%' ;
    SELECT LAST_NAME
    FROM S_EMP
    WHERE LAST_NAME LIKE '__M____' ;

    is null
    IS NULL OPERATOR을 사용하여 값이 NULL 인 것을 찾을 수 있다.
    NULL 값은 값이 정의되지 않은 것을 의미하기 때문에 = OPERATOR를 사용하여
    어떤 값과 비교할 수 없기 때문에 사용한다.
    IS NULL, IS NOT NULL
    [ 예제 ]
    S_EMP TABLE에서 COMMISSION_PCT 가 NULL 인 사원의 LAST_NAME, SALARY,
    COMMISSION_PCT 를 검색하시오.
    SELECT last_name, salary,commission_pct,last_name, salary
    FROM s_emp
    WHERE commission_pct is null;

    조건식의 결합
    조건식을 기술할 때 AND 와 OR 를 사용하여 여러가지 조건을 결합할 수 있다.
    AND 와 OR 가 같이 사용됐을 때 AND 가 먼저 수행되고 OR 가 나중에 수행된다.
    그러므로 우선순위를 바꾸고자 하면 ( ) 를 사용한다.
    WHERE 조건식 AND | OR 조건식
    [ 예제 ]
    S_EMP TABLE에서 DEPT_ID 가 41 이고 TITLE 이 Stock Clerk 인 사원의
    LAST_NAME, SALARY, DEPT_ID, TITLE 을 검색하시오.
    SELECT LAST_NAME, SALARY, DEPT_ID, TITLE
    FROM S_EMP
    WHERE DEPT_ID = 41
    AND TITLE = 'Stock Clerk' ;

    제 2 장 Single Row Functions
    소문자로 변환
    모든 문자를 소문자로 변환시킨다.
    LOWER(COLUMN명)
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원의 FIRST_NAME, LAST_NAME 을 소문자로 출력시키시오.
    SELECT LOWER(FIRST_NAME), LOWER(LAST_NAME)
    FROM S_EMP
    WHERE LOWER(LAST_NAME) = 'smith' ;

    대문자로 변환
    모든 문자를 대문자로 변환시킨다.
    UPPER(COLUMN명)
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원의 TITLE 을 대문자로 출력하시오.
    SELECT UPPER(TITLE)
    FROM S_EMP
    WHERE UPPER(LAST_NAME) = 'SMITH';

    첫글자만 대문자로 변환
    단어의 첫글자는 대문자로, 나머지는 소문자로 변환시킨다.
    INITCAP(COLUMN명)
    [ 예제 ]
    S_EMP TABLE 에서 TITLE 을 단어의 첫글자만 대문자로 출력시키시오.
    SELECT INITCAP(TITLE)
    FROM S_EMP ;

    문자의 부분을 자름
    문자를 시작위치(M)에서 자리수(N) 만큼 잘라준다.
    자리수(N)이 생략되면 시작위치(M)에서 끝까지 잘라준다.
    SUBSTR(COLUMN명, M, N)
    [ 예제 ]
    S_PRODUCT TABLE 에서 NAME COLUMN 의 앞에서 부터 3글자가 Ace 인 제품의
    NAME 을 출력하시오.
    SELECT NAME
    FROM S_PRODUCT
    WHERE SUBSTR(NAME, 1, 3) = 'Ace' ;

    문자의 길이를 계산
    문자의 길이를 RETURN 한다.
    LENGTH(COLUMN명)
    [ 예제 ]
    S_PRODUCT TABLE 에서 NAME, NAME 의 길이를 출력하시오.
    SELECT NAME, LENGTH(NAME)
    FROM S_PRODUCT;

    숫자의 반올림
    지정된 자리수(M) 밑에서 반올림한다.
    COLUMN 값이 1 2 3 4. 5 6 7 일 때 자리수(M)는 다음과 같다.
         M : -3-2-1 0 1 2 3
    ROUND(COLUMN명, M)
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME, SALARY/22 의 값을 소수 2째 자리까지 나타내고
    소수 3째 자리에서 반올림하시오.
    SELECT LAST_NAME, ROUND(SALARY/22, 2)
    FROM S_EMP ;

    숫자의 절사
    지정된 자리수(M) 까지 나타내고 그 밑은 잘라버린다.
    COLUMN 값이 1 2 3 4. 5 6 7 일 때 자리수(M)은 다음과 같다.
         M : -3-2-1 0 1 2 3
    절사 값은 RETURN 한다.
    TRUNC(COLUMN명, M)
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME, SALARY / 22 의 값을 십의 자리까지 나타내고
    일의 자리는 버림
    SELECT LAST_NAME, TRUNC(SALARY/22, -1)
    FROM S_EMP ;

    나누기의 나머지
    M 을 N 으로 나누고 남은 나머지를 RETURN 한다.
    MOD(M, N)
    [ 예제 ]
    10 을 3 으로 나눈 나머지를 구하시오.
    SELECT MOD(10, 3)
    FROM SYS.DUAL ;

    날짜의 연산
    DATABASE 안의 DATE 값은 다음과 같은 숫자로 저장되어 있다.
    ■ CENTURY, YEAR, MONTH, DAY, HOURS, MINUTES, SECONDS
    그러므로 산술 연산을 할 수 있다.
    ● DATE + NUMBER : 숫자만큼 일이 더해진 날짜가 RETURN 된다.
    ● DATE - NUMBER : 숫자만큼 일이 빼진 날짜가 RETURN 된다.
    ● DATE1 - DATE2 : 두 날짜 사이의 일수가 계산된다.
    날짜 계산을 한다.
    DATE + NUMBER
    DATE - NUMBER
    DATE1 - DATE2
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME, 입사한지 90 일째 되는 날, 입사한지 며칠 됐는 지 검색하시오.
    SELECT LAST_NAME, START_DATE + 90, SYSDATE - START_DATE
    FROM S_EMP;
    ( 날짜에는 시간도 포함되어 있으므로 일수 계산의 결과가 소수로 나온다. )

    날짜 사이의 개월 수
    두 날짜 사이의 개월 수를 RETURN 한다.
    MONTHS_BETWEEN(DATE1, DATE2)
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME, 입사한지 몇 달이 됐는지 출력하시오.
    SELECT LAST_NAME, MONTHS_BETWEEN(SYSDATE, START_DATE)
    FROM S_EMP ;
    (일이 포함되어 있어서 소수로 출력된다.)

    날짜에 달을 더함
    날짜에서 숫자(N) 개월만큼 더해진 날짜를 RETURN 한다.
    ADD_MONTHS(DATE, N)
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME, START_DATE, 입사한지 3 개월되는 날짜를 출력하시오.
    SELECT LAST_NAME, START_DATE, ADD_MONTHS(START_DATE, 3)
    FROM S_EMP ;

    지정한 요일 날짜
    날짜에서 지정한 요일(CHAR)이 될 날짜를 RETURN 한다.
    NEXT_DAY(DATE, 'CHAR')
    [ 예제 ]
    오늘을 기준으로 돌아오는 금요일이 언제인지 출력하시오.
    SELECT SYSDATE, NEXT_DAY(SYSDATE, 'FRIDAY')
    FROM SYS.DUAL ;

    그 달의 마지막 날
    날짜가 포함된 달의 마지막 날을 RETURN 한다.
    LAST_DAY(DATE)
    [ 예제 ]
    이번 달의 마지막 날은 언제인지 출력하시오.
    SELECT SYSDATE, LAST_DAY(SYSDATE)
    FROM SYS.DUAL ;

    날짜의 반올림
    형태에 따른 반올림 기준은 다음과 같다.
    • YEAR : 6월 이후
    • MONTH : 15일 이후
    • DAY : 12시 이후
    날짜 데이타를 지정된 형태까지 나타내고 그 이하에서 반올림한다.
    ROUND(COLUMN명, '형태')
    [ 예제 ]
    S_EMP TABLE 에서 ID, LAST_NAME, 입사 시작 달을 검색하시오.
    단, 15일 이후는 다음달로 올리시오.
    SELECT ID, LAST_NAME, ROUND(START_DATE, 'MONTH')
    FROM S_EMP ;

    날짜의 절사
    날짜 데이타를 지정된 형태까지 나타내고 그 밑은 잘라낸다.
    TRUNC(COLUMN명, '형태')
    [ 예제 ]
    S_EMP TABLE 에서 ID, LAST_NAME, 입사 시작 달을 검색하시오.
    단, 일자는 잘라버리시오.
    SELECT ID, LAST_NAME, TRUNC(START_DATE, 'MONTH')
    FROM S_EMP ;

    문자를 날짜로 변환
    CHARACTER TYPE 을 지정된 형태의 DATE TYPE 으로 변환한다.
    TO_DATE(character_column명, '형태')
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME, START_DATE 를 검색하시오.
    단, START_DATE 의 값이 92/02/07 인 사원을 검색하시오.
    SELECT LAST_NAME, START_DATE
    FROM S_EMP
    WHERE START_DATE = TO_DATE('92/02/07', 'YY/MM/DD') ;

    날짜를 문자로 변환
    DATE 값은 기본적으로 DD-MON-YY 형태로 출력된다.
    이것을 TO_CHAR FUNCTION 을 사용하면 원하는 다른 형태로 변환할 수 있다.
    ■ 형태를 지정할 때 사용된 대소문자로 출력된다.
    ■ DAY 와 MONTH 형태는 공백을 포함한 9 자리로 출력된다.
    ■ TO_CHAR 의 결과는 80 자리로 출력된다.
    DATE TYPE 을 지정된 형태의 CHARACTER TYPE 으로 변환한다.
    TO_CHAR(date_column, '형태')
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME, START_DATE 를 검색하시오.
    단, START_DATE 의 형태는 1991/06/17 14:20:00 와 같이 출력하시오.
    SELECT LAST_NAME, TO_CHAR(START_DATE, 'YYYY/MM/DD HH24:MI:SS'), START_DATE
    FROM S_EMP ;

    숫자를 문자로 변환
    NUMBER TYPE 을 지정된 형태의 CHARACTER TYPE 으로 변환한다.
    TO_CHAR(number_column명, '형태')
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME, SALARY 를 검색하시오.
    단 SALARY 를 $1,450 와 같은 형태로 출력시키시오.
    SELECT LAST_NAME, TO_CHAR(SALARY, '$999,999')
    FROM S_EMP ;


    제 3 장. 여러Table로부터 Data검색
    Equijoin
    SIMPLE JOIN (EQUI-JOIN)
    여러개의 TABLE 들로부터 정보를 검색하려면, SELECT 문장의 FROM 절에 TABLE명들을 적고
    WHERE 절에 각 TABLE의 ROW들을 연결시킬 조건식을 기술한다.
    각 TABLE 의 COLUMN명이 중복될 때는 반드시 COLUMN명 앞에 TABLE명을 붙여야 한다.
    (중복되지 않을 때는 붙이지 않아도 되지만 명확성을 위해서나 ACCESS 를 위해서 붙이는 것이 좋다.)
    N 개의 TABLE 을 JOIN 할 때는 최소한 N-1 개의 조건식이 필요하다.
    복합 COLUMN 으로 JOIN 할 때는 더 많은 조건식이 필요하다.
    2개 이상의 TABLE 에서 = 조건식을 만족시키는 ROW 들을 연결하여 검색한다.
    SELECT table명.column명, table명.column명...
    FROM table1명, table2명
    WHERE table1명.column1명 = table2명.column명 ;
    [ 예제 ]
    S_EMP TABLE 과 S_DEPT TABLE 을 사용하여 사원들의 LAST_NAME, DEPT_ID,
    NAME 을 검색하시오.
    SELECT S_EMP.LAST_NAME, S_EMP.DEPT_ID, S_DEPT.NAME
    FROM S_EMP, S_DEPT
    WHERE S_EMP.DEPT_ID = S_DEPT.ID ;

    특정 row의 join
    JOIN 문장을 기술할 때 JOIN 조건식 이외에 다른 조건식을 AND 로 연결할 수 있다.
    SELECT table명.column명, table명.column명...
    FROM table1명, table2명
    WHERE table1명.column1명 = table2명.column2명 AND condition ;
    [ 예제 ]
    S_EMP TABLE과 S_DEPT TABLE 을 사용하여 LAST_NAME 이 Menchu 인 사원의
    LAST_NAME, DEPT_ID, NAME 을 검색하시오.
    SELECT S_EMP.LAST_NAME, S_EMP.DEPT_ID, S_DEPT.NAME
    FROM S_EMP, S_DEPT
    WHERE S_EMP.DEPT_ID = S_DEPT.ID AND S_EMP.LAST_NAME = 'Smith' ;

    Table alias
    JOIN 문장에서 TABLE명이 긴 경우 TABLE명.COLUMN명 으로 적는 것이 매우 불편하다.
    그런데 TABLE명 대신 ALIAS 를 사용하면 편하게 사용할 수 있다.
    (SELECT 문장에서 TABLE명 대신 ALIAS 를 지정했다면 그 문장에서는 계속해서
    ALIAS 로 사용하여야 한다.)
    TABLE ALIAS를 사용하여 JOIN 문장을 간단하게 기술한다.
    SELECT alias명.column명, alias명.column명
    FROM table1명 alias1명, table2명 alias2명
    WHERE alias1명.column1명 = alias2명.column2명 ;
    [ 예제 ]
    S_CUSTOMER TABLE과 S_REGION TABLE 을 사용하여 고객 명,지역번호,지역 명을 검색하시오.
    단, COLUMN ALIAS 와 TABLE ALIAS 를 사용하시오.
    SELECT C.NAME "Customer Name", C.REGION_ID "Region Id",
    R.NAME "Region Name"
    FROM S_CUSTOMER C, S_REGION R
    WHERE C.REGION_ID = R.ID ;

    Non-Equijoin
    NON-EQUIJOIN
    JOIN 문장에서 두 TABLE 을 JOIN 하는 조건식에 = OPERATOR 가 사용되지 않고
    다른 OPERATOR 가 사용되는 것을 말한다.
    SELECT table명.column명, table명.column명...
    FROM table1명, table2명
    WHERE 조인조건식 ;
    [ 예제 ]
    EMP TABLE 과 SALGRADE TABLE 을 사용하여 사원의 ENAME, JOB, SAL,GRADE를 검색하시오.
    SELECT E.ENAME, E.JOB, E.SAL, S.GRADE
    FROM EMP E, SALGRADE S
    WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL ;
    (BETWEEN OPERATOR 대신에 <= 와 >= 를 사용해도 되지만 BETWEEN 이 간편하다.)

    Outer Join
    두 TABLE 을 JOIN 할 때 JOIN 조건식을 만족시키지 못하는 ROW 는 검색에서 빠지게 된다.
    그런데 이러한 ROW 들이 검색되도록 하는 것이 OUTER JOIN 이다.
    (+) OUTER JOIN OPERATOR 를 데이타가 없는 어느 한쪽의 COLUMN 쪽에 붙인다.
    JOIN 결과, 데이타가 없는 쪽의 COLUMN 값은 NULL로 검색된다.
    조건식을 만족시키지 못하는 데이타도 검색한다.
    SELECT table명.column명, table명.column명
    FROM table1명, table2명
    WHERE table1명.column1명 = table2명.column2명(+)
    [ 예제 ]
    S_EMP TABLE 과 S_CUSTOMER TABLE 을 사용하여 영업사원의 LAST_NAME,
    SALES_REP_ID, NAME 을 검색하시오.
    단, 영업사원이 정해져 있지 않은 고객의 이름도 검색하시오.

    SELECT E.LAST_NAME, C.SALES_REP_ID,
    C.NAME
    FROM S_EMP E, S_CUSTOMER C
    WHERE E.ID(+) = C.SALES_REP_ID ;

    Self Join
    TABLE 의 ALIAS 를 사용하여, 마치 2 개의 TABLE 처럼 생각하여 자신의 TABLE 과 자신의 TABLE 을 JOIN 한다.
    SELECT alias명.column명, alias명.column명...
    FROM table명 alias1명, table명 alias2명
    WHERE alias1명.column1명 = alias2명.column2명 ;
    [ 예제 ]
    S_EMP TABLE 에서 사원들의 LAST_NAME 과 그들의 상사 LAST_NAME 을 검색하시오.
    SELECT W.LAST_NAME "Woker",
    M.LAST_NAME "Manager"
    FROM S_EMP W, S_EMP M
    WHERE W.MANAGER_ID = M.ID ;

    제 4 장. Group Functions
    Group Function
    각각의 FUNCTION 은 ARGUMENT 를 받는데 기능은 다음과 같다.
    ■ DISTINCT : 중복된 값은 제외한다.
    ■ ALL : DEFAULT 로써 모든 값을 포함한다.
    ■ COLUMN명 : NULL 값은 제외한다.
    ■ * : NULL 값도 포함한다.
    TABLE 전체를 하나의 GROUP 으로 보고 GROUP FUNCTION 값을 RETURN 한다.
    SELECT group_function(column명), group_function(column명)...
    FROM table명 ;
    [ 예제 ]
    S_EMP TABLE 에서 회사 전체의 급여합계, 최고급여, 최소급여, 인원수를 검색하시오.
    SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), COUNT(SALARY)
    FROM S_EMP ;
    ( COUNT(SALARY) 는 급여를 받는 사원의 총 인원수고 COUNT(*) 는 급여를 받지 않는 사원의 인원수도 포함된다.)

    소group으로 분리
    기본적인 SELECT 절(그룹화 되지 않은 SELECT절)에는 COLUMN 명과 GROUP FUNCTION 이 같이 기술될 수 없다.
    SELECT 절에 COLUMN 명이 기술되려면 GROUP BY 절이 반드시 기술되어야 한다.
    SELECT 절에 기술된 COLUMN 명들은 전부 GROUP BY 절에 기술되어야 하며
    GROUP BY 절에 기술된 COLUMN 명들은 SELECT 절에 기술되지 않아도 된다.
    (하지만 결과를 파악하기 위해서는 SELECT 절에 기술해주는 것이 좋다.)
    GROUP BY 절을 기술하면 GROUP BY 절에 기술된 COLUMN 값으로 1 개의 TABLE이 소 GROUP 으로 나눠진다.
    결과는 COLUMN 값으로 SORT 되어서 출력된다.
    1 개의 TABLE 을 소 GROUP 으로 나누어 GROUP FUNCTION 값을 구한다.
    SELECT column1명[, column2명], group_function(column명)
    FROM table명
    GROUP BY column1명[, column2명] ;
    [ 예제 ]
    S_EMP TABLE 에서 DEPT_ID, TITLE 별로, 최고급여, 최소급여, 인원수를 검색하시오.
    SELECT DEPT_ID, TITLE,
    MAX(SALARY), MIN(SALARY),
    COUNT(SALARY)
    FROM S_EMP
    GROUP BY DEPT_ID, TITLE;

    특정 group의 선택
    HAVING 절이 기술됐을 때 처리되는 순서는 다음과 같다.
    ① ROW 들이 GROUPing 된다.
    ② GROUP 에 대해 GROUP FUNCTION 이 적용된다.
    ③ HAVING 절을 만족하는 GROUP 을 선택한다.
    그러므로 GROUP BY 절과 HAVING 절의 순서는 바뀌어도 되지만 의미상 GROUP BY
    절 다음에 HAVING 절을 기술하는 것이 좋다.
    HAVING 절에서는 GROUP FUNCTION 을 사용하여 GROUP 에 대한 조건식을 기술한다.
    SELECT column1명[, column2명], group_function(column명)
    FROM table명
    GROUP BY column1명[, column2명]
    HAVING 그룹조건식 ;
    [ 예제 ]
    S_EMP TABLE 에서 TITLE 별로 급여합계를 검색하시오.
    단, 급여합계가 5000 이상인 GROUP 만 출력하시오.
    SELECT TITLE, SUM(SALARY) PAYROLL
    FROM S_EMP
    GROUP BY TITLE
    HAVING SUM(SALARY) >= 5000 ;

    Group의 정렬
    기본적으로 GROUP BY 절에 기술된 COLUMN 값으로 SORT 된다.
    이 순서를 바꾸고자 하면 ORDER BY 절을 기술하면 된다.
    DATA 의 SORT 순서를 정한다.
    SELECT column1명[, column2명], group_function(column명)
    FROM table명
    GROUP BY column1명[, column2명]
    ORDER BY column명| group_function(column명) ;
    [ 예제 ]
    S_EMP TABLE에서 DEPT_ID 별로 인원수를 검색하시오.
    단, 인원수가 많은 부서부터 출력하시오.
    SELECT DEPT_ID, COUNT(*)
    FROM S_EMP
    GROUP BY DEPT_ID
    ORDER BY COUNT(*) DESC ;

    제 5 장. Subquery
    Single Row Subquery
    SUBQUERY 의 결과가 1 개의 ROW 로 나오는 것을 SINGLE ROW SUBQUERY 라 하며
    다음과 같은 OPERATOR 를 사용할 수 있다.
    =, >, >=, <, <=
    VALUE 값을 구하기 위해 SELECT 문장을 사용한다.
    SELECT column명, column명...
    FROM table명
    WHERE column명 operator (SELECT column명
    FROM table명
    WHERE 조건식 );
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원과 같은 업무(TITLE)를 하고 있는
    사원의 LAST_NAME, TITLE 을 검색하시오.
    SELECT LAST_NAME, TITLE
    FROM S_EMP
    WHERE TITLE =
    ( SELECT TITLE
    FROM S_EMP
    WHERE LAST_NAME = 'Smith') ;

    From절의 Subquery
    FROM 절에 기술된 SUBQUERY 문은 VIEW 처럼 사용된다.
    SELECT alias명.column명, alias명,column명...
    FROM table1명 alias1명, (SELECT column2명
    FROM table2명
    WHERE 조건식) alias2명
    WHERE alias1명.column1명 OPERATOR alias2명.column2명 ;
    [ 예제 ]
    S_EMP TABLE 에서 SALARY 가 회사평균급여 보다 적은 사원의 LAST_NAME,
    SALARY, 회사평균급여를 검색하시오.
    SELECT E.LAST_NAME, E.SALARY, S.AVGSAL
    FROM S_EMP E,
    (SELECT AVG(SALARY) AVGSAL
    FROM S_EMP) S
    WHERE E.SALARY < S.AVGSAL ;

    Multi Row Subquery
    SUBQUERY 의 결과가 여러 ROW 일 때는 반드시 IN OPERATOR 를 사용하여야 한다.
    SELECT column명, column명...
    FROM table명
    WHERE column명 IN ( SELECT column명
    FROM table명
    WHERE 조건식);
    [ 예제 ]
    S_EMP TABLE 과 S_DEPT TABLE 에서 Operations 부서에서 근무하는 사원의
    LAST_NAME, TITLE, DEPT_ID 를 검색하시오.
    SELECT LAST_NAME, TITLE, DEPT_ID
    FROM S_EMP
    WHERE DEPT_ID IN (SELECT ID
    FROM S_DEPT
    WHERE NAME = 'Operations') ;

    Multi Column Subquery
    SELECT 문장의 WHERE 절에서 여러개의 COLUMN 값을 비교하려면 LOGICAL
    OPERATOR 를 사용하여 여러개의 조건식을 기술하여야 한다.
    그런데 MULTI COLUMN SUBQUERY 를 사용하면 이를 해결할 수 있다.
    SELECT column명, column명,,,
    FROM table명
    WHERE (column명, column명...) IN (SELECT column명, column명...
    FROM table명
    WHERE 조건식);
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME Patel 인 사원과 같은 부서, 같은 업무를
    맡고 있는 사원의 LAST_NAME, TITLE, DEPT_ID 를 검색하시오.
    SELECT LAST_NAME, TITLE, DEPT_ID
    FROM S_EMP
    WHERE (DEPT_ID, TITLE) IN
    (SELECT DEPT_ID, TITLE
    FROM S_EMP
    WHERE LAST_NAME = 'Patel') ;
    SELECT LAST_NAME, TITLE, DEPT_ID
    FROM S_EMP
    WHERE (DEPT_ID) IN
    (SELECT DEPT_ID
    FROM S_EMP
    WHERE LAST_NAME = 'Patel')
    OR (TITLE) IN
    (SELECT TITLE
    FROM S_EMP
    WHERE LAST_NAME = 'Patel') ;

    제 6 장. Table 생성
    이름 붙이는 법
    이름은 다음의 규칙을 따라서 지정한다.
    ■ TABLE 명이나 COLUMN 명은 문자로 시작하고 30 자 이내로 지정한다.
    ■ A ~ Z, a ~ z, 0 ~ 9, _ , $ , # 을 사용할 수 있다.
    ■ 한 USER 내에서는 다른 OBJECT 의 이름과 동일하게 지정할 수 없다.
    ■ ORACLE7 SERVER 예약어를 사용할 수 없다.
    ■ 대소문자를 구별하지 않는다.

    Oracle 7 datatype
    COLUMN 의 DATATYPE 은 다음과 같다.
    ■ CHAR(size) : 고정된 size 의 문자 값, 최대 255 자까지 지정할 수 있다.
    ■ VARCHAR2(size) : size내에서의 가변길이 문자 값,최대 2000자까지 지정할 수 있다.
    ■ LONG : 가변길이 문자 값, 최대 2 기가 바이트까지 사용할 수 있다. TABLE 당 한 개의 COLUMN 에만 지정 가능하다.
    ■ NUMBER(p,s) : 정수, 소수 자리수의 합이 P, 소수 자리수가 s 인 숫자값, 최대 38 자리수까지 지정할 수 있다.
    ■ DATE : 날짜와 시간 값, B.C. 4712년 1월 1일부터 A.D. 4712년 12월 31일까지 입력할 수 있다.
    ■ RAW(size) : size 내에서의 가변길이 BINARY DATA
    ■ LONGRAW : 가변길이 BINARY DATA

    다른 table로부터 table생성
    기존하는 TABLE 로 부터 데이타와 구조를 복사하여 TABLE 을 생성한다.
    CREATE TABLE table명 [(column명, column명...)]
    AS subquery ;
    [ 예제 ]
    S_EMP TABLE 에서 DEPT_ID 가 41 인 사원들의 ID, LAST_NAME, USERID,
    START_DATE 만을 복사하여 EMP_41 TABLE 을 생성하시오.
    CREATE TABLE EMP_41
    AS SELECT ID, LAST_NAME, USERID, START_DATE
    FROM S_EMP
    WHERE DEPT_ID = 41;
    (S_EMP TABLE 에서 COLUMN명, TYPE, SIZE, NOT NULL CONSTRAINT 가 복사되어 EMP_41 TABLE 이 생성되며, 데이타는 DEPT_ID = 41 인 ROW 만 복사된다.)

    Constraint
    CONSTRAINT 의 종류는 다음과 같다.
    ■ NOT NULL
    COLUMN 에 NULL 값이 입력되는 것을 허용하지 않는다.
    COLUMN-CONSTRAINT 로만 기술해야 한다.
    ■ UNIQUE
    한 개의 COLUMN 혹은 복합 COLUMN 을 UNIQUE KEY 로 지정한다.
    UNIQUE KEY 에는 중복된 값을 허용하지 않는다.
    한개의 COLUMN 으로 구성된 UNIQUE KEY 는 NULL 값을 허용한다.
    COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.
    복합 COLUMN 으로 구성된 UNIQUE KEY 는 TABLE-CONSTRAINT 로만 기술해야 한다.
    UNIQUE KEY COLUMN 의 UNIQUE INDEX FILE 이 자동 생성된다.
    ■ PRIMARY KEY
    ROW 를 UNIQUE 하게 대표할 수 있는 한개의 COLUMN 혹은 복합 COLUMN 으로 지정한다.
    PRIMARY KEY 에는 중복된 값과 NULL 값을 허용하지 않는다.
    TABLE 당 한 개의 PRIMARY KEY 만 지정할 수 있다.
    COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.
    복합 COLUMN 으로 구성된 PRIMARY KEY 는 TABLE-CONSTRAINT 로만 기술해야 한다.
    PRIMARY KEY COLUMN 의 UNIQUE INDEX FILE 이 자동 생성된다.
    ■ FOREIGN KEY
    한개의 COLUMN 혹은 복합 COLUMN 으로 지정한다.
    같은 TABLE 혹은 다른 TABLE의 PRIMARY KEY 나 UNIQUE KEY 값을 참조한다.
    FOREIGN KEY 값은 모 TABLE 에 존재하는 데이타와 같던가 NULL 값을 허용한다.
    COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.
    ※ CHECK : 각각의 ROW 가 만족해야할 조건을 지정한다.
    조건식은 QUERY 조건식과 동일하게 지정한다.
    단, 다음과 같은 것은 사용할 수 없다.
    CURRVAL, NEXTVAL, LEVEL, ROWNUM, SYSDATE, USER COLUMN 이나
    TABLE-CONSTRAINT 로 기술할 수 있다.
    CONSTRAINT 명은 다음과 같이 지정한다.
    • CONSTRAINT 는 DICTIONARY 에 저장되므로 참조하기 쉽게 의미있게 붙여준다.
    • 일반적으로 TABLE명_COLUMN명_CONSTRAINT종류와 같은 형태로 지정한다.
    • 사용자가 CONSTRAINT 명을 지정하지 않으면 ORACLE7이 SYS_Cn의 형태로 붙인다.
    • 동일한 USER 내에서 CONSTRAINT명은 UNIQUE해야 한다.
    CONSTRAINT 는 다음과 같이 기술할 수 있다.
    COLUMN-CONSTRAINT : column명 [CONSTRAINT constraint명] constraint종류
    TABLE-CONSTRAINT : [CONSTRAINT constraint명] constraint종류
    (column명, column명..)

    Table 생성
    CREATE TABLE table명
    (column명 type(size) [DEFAULT VALUE] [column constraint],
    column명 type(size) [DEFAULT VALUE] [column constraint],
    .... ,
    [table constraint] ,
    [table constraint] ,
    .... ) ;
    [ 예제 ]
    S_EMP TABLE CHART를 보고 TABLE 을 생성하시오.
    단, TABLE CONSTRAINT 로 기술할 수 있는 것은 TABLE CONSTRAINT 로 정의하시오.
    CREATE TABLE S_EMP
    (ID NUMBER(7),
    LAST_NAME VARCHAR2(25) CONSTRAINT S_EMP_LAST_NAME_NN NOT NULL,
    FIRST_NAME VARCHAR2(25),
    USERID VARCHAR2(8) CONSTRAINT S_EMP_USERID_NN NOT NULL,
    START_DATE DATE DEFAULT SYSDATE,
    COMMENTS VARCHAR2(25),
    MANAGER_ID NUMBER(7),
    TITLE VARCHAR2(25),
    DEPT_ID NUMBER(7),
    SALARY NUMBER(11,2),
    COMMISSION_PCT NUMBER(4,2),
    CONSTRAINT S_EMP_ID_PK PRIMARY KEY(ID),
    CONSTRAINT S_EMP_USERID_UK UNIQUE,
    CONSTRAINT S_EMP_DEPT_ID_FK FOREIGN KEY(DEPT_ID)
    REFERENCES S_DEPT(ID),
    CONSTRAINT S_EMP_COMMISSION_PCT CHECK
    (COMMISSION_PCT IN (10, 12.5, 15, 17.5, 20))) ;

    제 7 장. Data DICTIONARY
    DICTIONARY
    • DATABASE 가 만들어 졌을때 DICTIONARY TABLE 도 만들어 진다.
    • DATABASE 가 사용중일때 DICTIONARY TABLE 은 ORACLE7 SERVER 에 의해 UPDATE 된다.
    • 사용자들은 DICTIONARY TABLE 을 SELECT 할 수 있다.
    • DICTIONARY TABLE 은 SYS USER 의 소유다.
    • DICTIONARY TABLE 의 값은 대문자로 들어있다.
    • DICTIONARY TABLE 의 종류는 다음과 같은 방법으로 알 수 있다.
    SELECT   *
    FROM    DICTIONARY ;
    DICTIONARY TABLE 의 종류는 다음과 같다.
    • USER : USER 가 소유하고 있는 OBJECT 의 정보를 보여준다.
    • ALL : USER 가 ACCESS 할 수 있는 OBJECT 의 정보를 보여준다.
    • DBA : DBA USER 가 ACCESS 할 수 있는 OBJECT 의 정보를 보여준다.

    활용예
    DICTIONARY TABLE 의 검색예는 다음과 같다.
    ■ 자신이 갖고 있는 TABLE 의 이름을 검색한다.
    SELECT   OBJECT_NAME
    FROM    USER_OBJECTS
    WHERE   OBJECT_TYPE = 'TABLE';
    ■ 자신이 갖고 있는 OBJECT 의 종류를 검색한다.
    SELECT   DISTINCT OBJECT_TYPE
    FROM   USER_OBJECTS;
    ■ GRANT 와 관련된 DICTIONARY TABLE 의 이름을 검색한다.
    SELECT   TABLE_NAME
    FROM   DICTIONARY
    WHERE   UPPER(COMMENTS) LIKE '%GRANT%';
    ■ S_EMP TABLE 의 CONSTRAINT 종류를 검색한다.
    SELECT   CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION,
    R_CONSTRAINT_NAME
    FROM   USER_CONSTRAINTS
    WHERE   TABLE_NAME = 'S_EMP';
    ■ S_EMP TABLE 의 COLUMN CONSTRAINT 를 검색한다.
    SELECT   CONSTRAINT_NAME, COLUMN_NAME
    FROM   USER_CONS_COLUMNS
    WHERE   TABLE_NAME = 'S_EMP';

    제 8 장. Data 조작
    데이타 입력
    TABLE 전체 COLUMN 에 값을 입력한다.
    INSERT INTO table명
    VALUES (value, value...);
    [ 예제 ]
    S_EMP TABLE 에 다음과 같은 데이타를 입력하시오.
    ID : 26, LAST_NAME : Jung Mi, FIRST_NAME : Hong, USERID : Hjungmi,
    START_DATE : 05-APR-97, COMMENTS : Teacher, MANAGER_ID : 10,
    TITLE : Stock Clerk, DEPT_ID : 45, SALARY : 1200 COMMISSION_PCT : 10
    INSERT INTO S_EMP
    VALUES (26, 'Jung Mi', 'Hong', 'Hjungmi', '05-APR-97',
    'Teacher', 10, 'Stock Clerk', 45, 1200, 10) ;
    (값을 지정하는 순서는 TABLE 의 COLUMN 순서에 맞춰서 지정한다.
    이 방법보다는 COLUMN명을 기술하여 입력하는 방법이 더 좋다.)

    특정 column에 데이타입력
    데이타를 입력하고자 하는 COLUMN을 선택하여 입력한다.
    INSERT INTO table명(column명, column명....)
    VALUES (value, value....);
    [ 예제 ]
    S_EMP TABLE 에 다음과 같은 데이타를 입력하시오.
    ID : 27, LAST_NAME : Smith, FIRST_NAME : Donna, START_DATE : 05-APR-97
    INSERT INTO S_EMP(ID, LAST_NAME, FIRST_NAME, START_DATE)
    VALUES (27, 'Smith', 'Donna', '05-APR-97') ;

    Null, 특수 value 입력
    COLUMN 값에 NULL 값을 지정하는 방법은 3 가지가 있다.
    • INSERT 문장의 COLUMN LIST 에서 생략한다.
    • INSERT 문장의 VALUE 절에서 NULL 로 지정한다.
    • INSERT 문장의 VALUE 절에서 '' 로 지정한다.
    COLUMN 값에 특수한 값을 입력할 수 있다.
    SYSDATE : 현재날짜와 시간
    USER : 현재 USERID
    [ 예제 ]
    S_EMP TABLE 에 다음과 같은 데이타를 입력하시오.
    ID : 29, LAST_NAME : Donna, USERID : USER, SALARY : NULL, START_DATE : SYSDATE
    INSERT INTO S_EMP(ID, LAST_NAME, USERID, SALARY, START_DATE)
    VALUES (29, 'Donna', USER, NULL, SYSDATE);

    특수형태의 날짜/시간입력
    DATE 값을 입력할 때는 지정된 DATE 형태로 입력하여야 한다.
    일반적으로 DD-MON-YY 형태를 사용하며, 이 형태로 데이타를 입력하면 세기는 현재의 세기로, 시간은 자정으로 입력된다.
    다른 세기의 날짜나 시간을 입력하고 싶으면 TO_DATE FUNCTION 을 사용한다.
    지정된 형태가 아닌 다른 형태의 날짜 값을 입력한다.
    TO_DATE('날짜값','날짜형태')
    [ 예제 ]
    S_EMP TABLE 에 다음과 같은 데이타를 입력하시오.
    ID : 30, LAST_NAME : Donna, USERID : SQL01, START_DATE : 199704051400
    INSERT INTO S_EMP(ID, LAST_NAME, USERID, START_DATE)
    VALUES (30, 'Donna', 'SQL01', TO_DATE('199704051400','YYYYMMDDHH24MI'));

    다른table로부터 데이타입력
    INSERT 문장을 사용하여 기존하는 TABLE 의 데이타를 다른 TABLE 로 COPY 할 수 있다.
    INSERT INTO table명[(column명, column명...)]
    SUBQUERY;
    [ 예제 ]
    S_EMP TABLE 의 ROW들을 HISTORY TABLE 로 COPY 하시오.
    단, 01-JAN-94 이전에 입사한 사원의 ID,LAST_NAME,SALARY,START_DATE 를 COPY 하시오
    INSERT INTO HISTORY(ID, LAST_NAME, SALARY, START_DATE)
    SELECT ID, LAST_NAME, SALARY, START_DATE
    FROM S_EMP
    WHERE START_DATE < '01-JAN-94' ;
    (INSERT 절의 COLUMN 수와 SELECT 절의 COLUMN 수는 같아야 한다.)

    데이타 수정
    UPDATE 문장을 사용하여 이미 존재하는 COLUMN 값을 수정한다.
    UPDATE table명
    SET column명 = value, [column명 = value]
    [WHERE 조건식] ;
    [ 예제 ]
    S_EMP TABLE 에서 ID 가 1 인 사원의 데이타를 다음과 같이 수정하시오.
    DEPT_ID : 32, SALARY : 2550
    UPDATE S_EMP
    SET DEPT_ID = 32, SALARY = 2550
    WHERE ID = 2 ;

    데이타 삭제
    DELETE 문장을 사용하여 데이타를 삭제한다.
    DELETE FROM table명
    [WHERE 조건식] ;
    [ 예제 ]
    S_EMP TABLE에서 ID 가 20 보다 큰 사원을 삭제하시오.
    DELETE FROM S_EMP
    WHERE ID > 20 ;

    저장
    COMMIT 문장(COMMIT;)에 의해 변경된 모든 내용이 DATABASE 에 저장된다.
    변경된 모든 데이타는 DATABASE 에 저장된다.
    그 전의 데이타는 완전히 없어진다.
    모든 사용자가 변경한 내용을 볼 수 있다.
    변경된 ROW 에 걸려있던 LOCK 이 해제된다.
    그러므로 다른 사용자가 수정할 수 있다.
    모든 SAVEPOINT 가 없어진다.
    TRANSACTION 을 종료하고 TRANSACTION 안의 모든 변경된 작업을 저장한다.

    취소
    ROLLBACK 문장(ROLLBACK)을 사용하여 모든 변경된 내용을 취소한다.
    모든 변경이 취소되며 수정하기 전의 데이타가 복구된다.
    변경된 ROW 에 걸려있던 LOCK 이 해제된다.
    다른 사용자들이 그 ROW 에 대해서 변경을 할 수 있다.
    TRANSACTION 을 종료하고 TRANSACTION 안의 모든 변경된 작업을 취소한다.

    Savepoint지정~취소
    TRANSACTION 안에서 ROLLBACK 할 수 있는 POINT 를 지정한다.
    지정된 POINT 까지만 ROLLBACK 한다.
    SAVEPOINT savepoint명 ;
    ROLLBACK TO savepoint명 ;
    [ 예제 ]
    S_EMP TABLE 에서 TITLE 이 Stock Clerk 인 사원의 SALARY 를 10% 인상하시오.
    SAVEPOINT 를 지정하시오.
    S_REGION TABLE 에 다음과 같은 데이타를 입력하시오.
    ID : 8, NAME : Central
    SAVEPOINT 까지 ROLLBACK 하시오.
    UPDATE 결과를 저장하시오.
    UPDATE S_EMP
    SET SALARY = SALARY * 1.1
    WHERE TITLE = 'Stock Clerk' ;
    SAVEPOINT S1;
    INSERT INTO S_REGION(ID, NAME)
    VALUES (8, 'Central') ;
    ROLLBACK TO S1;
    COMMIT;


    제 9 장. Table변경/삭제
    Column 추가
    TABLE 에 새로운 COLUMN 을 추가한다.
    ALTER TABLE table명
    ADD (column명 type(size) [DEFAULT value] [column_constraint],
    ...........) ;
    [ 예제 ]
    S_REGION TABLE 에 다음과 같은 COLUMN 을 추가하시오.
    COMMENTS VARCHAR2(25)
    ALTER TABLE S_REGION
    ADD (COMMENTS VARCHAR2(25))
    (추가될 COLUMN 의 위치는 지정할 수 없다. 새로운 COLUMN 은 마지막 위치에 생성된다.)

    Column 변경
    ALTER TABLE 문장의 MODIFY 절을 사용하여 다음과 같은 변경을 할 수 있다.
    COLUMN 의 크기를 확장할 수 있다.
    데이타가 들어있지 않으면 COLUMN 의 크기를 줄일 수 있다.
    데이타가 들어있지 않다면 COLUMN 의 타입을 수정할 수 있다.
    COLUMN 에 NULL 값이 없다면 NOT NULL CONSTRAINT 를 지정할 수 있다.
    DEFAULT VALUE 를 변경할 수 있다.
    이미 생성되어 있는 COLUMN 을 변경한다.
    ALTER TABLE table명
    MODIFY (column명 type(size) [DEFAULT value] [NOT NULL],
    .............) ;

    Constraint 추가
    이미 생성되어 있는 TABLE 에 CONSTRAINT 를 추가한다.
    ALTER TABLE table명
    ADD (table_constraint) ;
    [ 예제 ]
    S_EMP TABLE 에 다음과 같은 CONSTRAINT 를 추가하시오.
    MANAGER_ID COLUMN 이 S_EMP TABLE 의 ID COLUMN 을 REFERENCE 하는
    FOREIGN KEY CONSTRAINT 를 추가하시오.
    ALTER TABLE S_EMP
    ADD (CONSTRAINT S_EMP_MANAGER_ID_FK FOREIGN KEY(MANAGER_ID)
    REFERENCES S_EMP(ID)) ;

    Constraint 삭제
    이미 생성되어 있는 TABLE 의 CONSTRAINT 를 삭제한다.
    ALTER TABLE table명
    DROP PRIMARY KEY |
    UNIQUE(column명) |
    CONSTRAINT constraint명 [CASCADE] ;
    [ 예제 ]
    S_EMP TABLE 의 다음과 같은 CONSTRAINT 를 삭제하시오.
    MANAGER_ID COLUMN 의 FOREIGN KEY CONSTRAINT
    ALTER TABLE S_EMP
    DROP CONSTRAINT S_EMP_MANAGER_ID_FK ;

    전체 데이타의 삭제
    TRUNCATE 문장은 DDL 이다.
    ROLLBACK SEGMENT 를 만들지 않고 모든 데이타를 삭제한다.
    데이타가 삭제된 FREE 영역은 환원된다.
    TABLE 로부터 모든 데이타를 삭제한다.
    TRUNCATE TABLE table명 ;
    [ 예제 ]
    S_ITEM TABLE 의 모든 데이타를 삭제하시오.
    TRUNCATE TABLE S_ITEM ;

    Constraint disable/enable
    TABLE 에 있는 모든 데이타가 CONSTRAINT 를 만족시켜야 ENABLE 할 수 있다.
    PRIMARY KEY, UNIQUE CONSTRAINT 를 ENABLE 하면 그에 따른 INDEX FILE 이 자동적으로 생성된다.
    CASCADE OPTION 은 FOREIGN KEY CONSTRAINT 를 DISABLE 할 때 사용한다.
    CONSTRAINT 를 삭제하고 새로 만들지 않고 DISABLE, ENABLE 한다.
    ALTER TABLE table명
    DISABLE | ENABLE PRIMARY KEY |
    UNIQUE(column명) |
    CONSTRAINT constraint명 [CASCADE] ;
    [ 예제 ]
    S_DEPT TABLE 의 PRIMARY KEY CONSTRAINT 를 DISABLE 시키시오.
    ALTER TABLE S_DEPT
    DISABLE CONSTRAINT S_DEPT_ID_PK CASCADE;
    S_EMP TABLE 의 S_EMP_DEPT_ID_FK CONSTRAINT 도 자동적으로 DISABLE 된다.

    Table 삭제
    TABLE 을 삭제하면 그 TABLE 에 딸린 INDEX FILE 도 삭제된다.
    VIEW, SYNONYM, STORED PROCEDURE, FUNCTION, TRIGGER 등은 삭제되지 않는다.
    CASCADE CONSTRAINTS 는 모 TABLE 을 삭제하고 자 TABLE 의 FOREIGN KEY CONSTRAINT 도 삭제한다.
    DROP TABLE table명 [CASCADE CONSTRAINTS] ;
    [ 예제 ]
    S_DEPT TABLE 을 삭제하시오.
    DROP TABLE S_DEPT CASCADE CONSTRAINTS ;

    이름의 변경
    TABLE, VIEW, SEQUENCE, SYNONYM 의 이름을 변경한다.
    RENAME old명 TO new명 ;
    [ 예제 ]
    S_ORD TABLE 의 이름을 S_ORDER 로 변경하시오.
    RENAME S_ORD TO S_ORDER ;

    제 10 장. Sequence
    Sequence 생성
    SEQUENCE 는 여러 사용자에게 UNIQUE 한 값을 생성해 주는 OBJECT 이다.
    SEQUENCE 를 사용하여 PRIMARY KEY 값을 자동적으로 생성한다.
    CREATE SEQUENCE sequence명
    INCREMENT BY n
    START WITH n
    MAXVALUE n | NOMAXVALUE
    MINVALUE n | NOMINVALUE
    CYCLE | NOCYCLE
    CACHE n | NOCACHE ;
    [ 예제 ]
    S_DEPT TABLE 의 ID COLUMN 값에 사용할 SEQUENCE 를 다음과 같이 생성하시오.
    START : 51, INCREMENT : 1, MAXVALUE : 9999999, NOCYCLE, NOCACHE
    CREATE SEQUENCE S_DEPT_ID
    INCREMENY BY 1
    START WITH 51
    MAXVALUE 9999999
    NOCACHE
    NOCYCLE ;

    Sequence 변경
    SEQUENCE 에 정의된 값을 변경한다.
    ALTER SEQUENCE sequence명
    INCREMENT BY n
    MAXVALUE n | NOMAXVALUE
    MINVALUE n | NOMINVALUE
    CYCLE | NOCYCLE
    CACHE n | NOCACHE ;
    [ 예제 ]
    S_DEPT_ID SEQUENCE 를 다음과 같이 수정하시오.
    CACHE : 10
    ALTER SEQUENCE S_DEPT_ID
    CACHE 10 ;

    Sequence 삭제
    SEQUENCE 를 삭제한다.
    DROP SEQUENCE sequence명 ;
    [ 예제 ]
    S_DEPT_ID SEQUENCE 를 삭제하시오.
    DROP SEQUENCE S_DEPT_ID ;

    제 11 장. VIEW

    Simple view
    SUBQUERY 문장이 간단한 경우 VIEW 를 통해 SELECT,INSERT,UPDATE,DELETE 를 할 수 있다.
    ■ SELECT : SUBQUERY 의 조건식을 만족하는 데이타만 검색된다.
    ■ INSERT : NOT NULL COLUMN 을 다 포함하고 있는 경우 INSERT 를 할 수 있다.
    SUBQUERY 의 조건식을 만족하지 않는 데이타도 입력이 가능하다.
    ■ UPDATE : VIEW 를 통해 SELECT 할 수 있는 데이타만 수정할 수 있다.
    SUBQUERY 의 조건식을 만족하지 않는 데이타도 수정이 가능하다.
    ■ DELETE : VIEW 를 통해 SELECT 할 수 있는 데이타만 삭제할 수 있다.
    CREATE VIEW view명 [(alias명, alias명....)]
    AS SUBQUERY ;
    [ 예제 ]
    S_EMP TABLE 에서 DEPT_ID 가 45 인 사원의 ID, LAST_NAME, DEPT_ID, TITLE 을
    선택해서 VIEW 를 생성하시오.
    CREATE VIEW EMP41
    AS SELECT ID, LAST_NAME, DEPT_ID, TITLE
    FROM S_EMP
    WHERE DEPT_ID = 45 ;

    With check option
    VIEW 를 정의할때 지정한 조건식을 만족하는 데이타만 INSERT, 또는 조건식을 만족하는 데이터로만 UPDATE 가 가능하다.
    데이타가 VIEW 의 조건식을 만족하는지 CHECK 한다.
    CREATE VIEW view명 [ (alias명, alias명...)]
    AS SUBQUERY
    WITH CHECK OPTION ;
    [ 예제 ]
    S_EMP TABLE 에서 DEPT_ID 가 45 인 사원의 ID, LAST_NAME, DEPT_ID, TITLE
    을 선택해서 VIEW 를 생성하시오.
    단, DEPT_ID 가 45 가 아닌 사원은 입력되지 못하게 만드시오.
    CREATE VIEW EMP45
    AS SELECT ID, LAST_NAME, DEPT_ID, TITLE
    FROM S_EMP
    WHERE DEPT_ID = 45
    WITH CHECK OPTION ;

    With read only
    SELECT만 가능한 VIEW 를 생성한다.
    CREATE VIEW view명 [(alias명, alias명...)]
    AS SUBQUERY
    WITH READ ONLY ;
    [ 예제 ]
    S_EMP TABLE 에서 ID, LAST_NAME, DEPT_ID, SALARY 가 SELECT 만 되도록 VIEW 를 생성
    CREATE VIEW R_EMP
    AS SELECT ID, LAST_NAME, SALARY
    FROM S_EMP
    WITH READ ONLY ;

    Force
    기준 TABLE 이 존재하지 않아도 VIEW 를 생성한다.
    CREATE FORCE VIEW view명 [(alias명, alias명...)]
    AS SUBQUERY ;
    [ 예제 ]
    S_EMP TABLE 이 없어도 S_EMP TABLE 에서 ID, LAST_NAME, SALARY 를 선택해서
    VIEW 를 생성하시오.
    CREATE FORCE VIEW T_EMP
    AS SELECT ID, LAST_NAME, SALARY
    FROM S_EMP ;

    complex view
    SUBQUERY 문장에 JOIN, FUNCTION, DISTINCT 또는 연산이 포함된 경우를 말하며 이 경우 VIEW 를 통한 DML 은 수행할 수 없다.
    COMPLEX VIEW 를 생성한다.
    CREATE VIEW view명 (alias명, alias명...)
    AS SUBQUERY ;
    [ 예제 ]
    S_EMP TABLE 과 S_DEPT TABLE 에서 ID, LAST_NAME, DEPT_ID, NAME 을
    선택해서 VIEW 를 생성하시오.
    CREATE VIEW EMPDEPT
    AS SELECT E.ID, E.LAST_NAME, E.DEPT_ID, D.NAME
    FROM S_EMP E, S_DEPT D
    WHERE E.DEPT_ID = D.ID ;

    View 삭제
    VIEW 를 삭제하면 DATABASE 로부터 VIEW 의 정의가 삭제된다.
    VIEW 가 기초로 한 TABLE 은 삭제되지 않는다.
    DROP VIEW view명 ;
    [ 예제 ]
    EMPDEPT VIEW 를 삭제하시오.
    DROP VIEW EMPDEPT ;

    제 12 장. Index

    Index 생성
    TABLE 생성시 PRIMARY KEY 나 UNIQUE CONSTRAINT 를 지정하면 UNIQUE INDEX 가 자동적으로 만들어 진다.
    이 외의 COLUMN 으로 QUERY 를 할 때 속도를 향상시키기 위해서 INDEX 를 생성한다.
    INDEX 를 생성하면 QUERY 속도는 빨라질 수 있으나 DML 속도는 늦어질 수 있다.
    일반적으로 다음과 같은 경우에 INDEX 를 생성한다.
    ■ COLUMN 이 WHERE 절이나 JOIN 조건식에 빈번하게 사용될 때
    ■ COLUMN 값이 넓게 분포되어 있을 때
    ■ COLUMN 값에 NULL 값이 많이 포함되어 있을 때
    ■ TABLE 이 크고 QUERY 하는 데이터 양이 10 % 이하일 때
    CREATE [UNIQUE] INDEX index명
    ON table명(column명[, column명...]) ;
    [ 예제 ]
    S_EMP TABLE 에서 LAST_NAME 의 QUERY 속도를 향상하기 위하여 INDEX 를 생성하시오.
    CREATE INDEX S_EMP_LAST_NAME_IDX
    ON S_EMP(LAST_NAME) ;

    Index 삭제
    INDEX 는 수정할 수 없다. 수정하고 싶은 경우 삭제하고 다시 생성한다.
    DROP INDEX index명 ;
    [ 예제 ]
    S_EMP_LAST_NAME_IDX INDEX 를 삭제하시오.
    DROP INDEX S_EMP_LAST_NAME_IDX ; 
     
    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요

    보통  SELECT 를 할때도 잠금이 일어 나게 된다. 이를 막기 위해 힌트 옵션을 이용하여
    잠금해제를 하게 된다. 하지만 옵션을 통해서 잠금을 완전 해제 할수도 있다.


    with (nolock) == SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
     
    Posted by ㏈ª ☞ β┖υΕJini.κR
    TAG ISOLATION

    댓글을 달아 주세요

    2007년 초 처음으로  MS-SQL2005 를 이용한 프로젝트를 하게 되었다.
    예전 프로시져 내에서 @@error 를 이용하여 Transaction  처리를
    하였는데 2005 부터 TRY... CATCH를 이용하요 보다 쉽게 사용할수 있게
    되었다. (TRY... CATCH 자바, 닷넷에서 많으 보던건데... )

    MS-SQL 2005부터는 TRY/CATCH블럭을 이용한 오류처리가 가능하다.
    사용법은 C#의 그것과 매우 유사하며, @@ERROR의 값으로 처리 하는
    방법보다 훨씬 좋은 가독성과 성능을 낼수 있다고 한다.

    BEGIN TRY
        실행할 쿼리문...
    END TRY

    BEGIN CATCH
        오류시 처리할 구문   
    END CATCH

    사용법은 사용할 쿼리문을 단순히 Begin Try / End Try로 묶고 오류시 처리할 내용을 Begin Catch / End Catch로 묶어 주기만 하면된다.

    "2008/05/21 - [DataBase] - Transaction에서의 @@error 사용" 포스트에서 이야기한 내용을 2005의 Try/Catch를 이용하면 훨씬 간단하게 표현이 가능하다.

    Begin Transaction
        1) Insert 실행
        IF(@@Error <> 0) GOTO _QuitTran

        2) Insert 실행
        IF(@@Error <> 0) GOTO _QuitTran

    COMMIT TRAN
    GOTO _SaveTran

    _QuitTran:
    ----------------------------------------------------
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRAN
    END
    RETURN
    ----------------------------------------------------

    _SaveTran:
    ----------------------------------------------------
    RETURN
    ----------------------------------------------------
    GO

    기존에 이런식으로 처리했던 내용을 각각의 트랜젝션으로 묶을 부분을 골라 Try/Catch로 묶어 주기만 하면 된다.

    BEGIN Transaction
        BEGIN TRY
            1) Insert 실행 --> 실패
            2) Insert 실행 --> 성공
           
    Commit Transaction
        END TRY
        BEGIN CATCH
            Rollback Transaction
        END CATCH

    IF(@@Error <> 0) GOTO _QuitTran의 중복적인 사용과 _SaveTran/_QuitTran등의 구문을 사용하지 않고도 고도 매우 쉽게 Transaction을 사용할 수 있다.

    오류의 내용을 알고 싶을 경우 MS-SQL2005에 있는 기본 내장 함수를 이용한면 된다.
    ERROR_NUMBER() < 오류번호 >
    ERROR_MESSAGE() < 오류메세지 >
    ERROR_STAT() < 오류상태코드 >
    ERROR_SERVERITY() < 오류가 발생한 행번호 >
    ERRPR_PROCEDURE() < 오류가 발생한 트리거/프로시져 >

    Posted by ㏈ª ☞ β┖υΕJini.κR
    TAG catch, TRY

    댓글을 달아 주세요

    1. 2010.07.23 16:19 조그셔틀  댓글주소  수정/삭제  댓글쓰기

      오 경진이 멋져~~!!

    2. 2010.09.01 03:34 Favicon of http://jiguin.co.kr BlogIcon 뿌직이  댓글주소  수정/삭제  댓글쓰기

      인터넷 검색하다가 보고 갑니다.
      좋은 정보 감사합니다.
      덕분에 해결할것을 해결했네요.

    SQL2000

    sp_configure 'allow', 1 reconfigure with override

    begin tran
    update sysdatabases set status=32768 where name = 'DB'
    commit tran

    dbcc rebuild_log('DB', '경로')

    begin tran
    update sysdatabases set status = 0 where name = 'DB'
    commit tran


    -- mdf 파일로 로그파일을 재구성

    sp_dboption DB, single, true

    alter database drbitpack
    dbcc checkdb ('DB', REPAIR_FAST)

    alter database DB
    set multi_user

     

    SQL2005

    EXEC sp_resetstatus 'yourDBname';
    ALTER DATABASE yourDBname SET EMERGENCY
    DBCC checkdb('yourDBname')
    ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
    ALTER DATABASE yourDBname SET MULTI_USER


    참고 : How to Restore SQL Server 2005 Suspect Database
    http://www.codeproject.com/KB/reporting-services/SQL_2005_Suspect_Database.aspx
    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요

    데이터베이스의 사용자(user)와 로그인 계정(login)이 일치하지 않아서 발생하는 문제라는 건 예전부터 알고 있었는데, 이를 해결하는 sp를 찾아 냈다. 해서 원격 DB를 마이그레이션 할 일이 생기면 그때마다 사용자를 제거했다가 다시 로그인 계정을 생성하는 방식으로 매칭을 시켰는데

    sp_change_users_login 구문
    sp_change_users_login [ @Action = ] 'action'    [ , [ @UserNamePattern = ] 'user' ]    [ , [ @LoginName = ] 'login' ]        [ , [ @Password = ] 'password' ]

    다음 명령으로 로그인 계정에 연결되지 않은 사용자를 조회할 수 있다 :
    EXEC sp_change_users_login 'test';
    또한 다음 명령으로 로그인 계정에 연결되지 않은 사용자를 사용자를 위한 로그인 계정을 생성하고 할당할 수 있다 :
    USE AdventureWorks;
    GO
    EXEC sp_change_users_login 'Auto_Fix', 'username', NULL, 'passowrd';
    GO
    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요

    말그대로 DB부하 테스트 도구 입니다.

    보통의 디비 설계를 하고 가상의 데이터를 넣은 다음 쿼리문을 실행하여 DB에 어느정도의

    부하가 가해 지는지 테스트 하는 도구 입니다.

     

    툴을 얻는 방법으로는 http://support.microsoft.com/

     

    설치를 하고 나면

    c:\program files\rml 폴더가 생성됩니다.

     

    명령프롬프트 창에서

     

    1)

    ostress -Sservername -E -dpubs -Q"select * from sales" -n5 -r5 이렇게 해봅니다.

     

    -S : Servername

    -E : windows 인증

    -d : 사용db

    -Q :쿼리문

    -n : 컨넥션 오픈수

    -r : 반복횟수

     

    2)

    윈도우인증 대신 sql 인증을 사용하려면

    ostress -Sservername -Usa -P1234 -dpubs -Q"select * from sales" -n5 -r5

     

    -U : id

    -P : 1234

     

    합니다.

     

    3)

    외부파일을 통해서 쿼리를 수행하기 위해서는

    ostress -Sservername -E -dpubs -ibatch.sql -n5 -r5

     

    -i  :외부 파일명

     

    사용합니다. batch.sql 파일에 있는 쿼리를 이용하여 부하 테스트를 수행하게 됩니다.

     

    4)

    -t : 매개변수

    쿼리타임아웃을 설정하여 시뮬레이션을 수행하게 됩니다.

    것은 사용자가 쿼리를 취소하거나 쿼리 타임아웃으로 인해 SQL서버에 고아가 트랜잭션이 남아 있는 경우의 시나리오 테스트를 하는데 유용합니다.

     

    ostress -Sservername -E -dpubs -ibatch.sql -n10 -r5 -t1

    위의 명령어는

    10개의 동시 커넥션에 커넥션마다 5번씩 반복수행하며, 1초의 쿼리 타임아웃 옵션으로 배치파일을 수행하는 예제입니다.

     

    5)

    -c : 매개변수

    -c 다음의 파일은 메타파일을 지정합니다.

    rml폴더내에 있는 sample.ini 보면 ostress 수행을 위해 필요한 옵션들이 우글 우글합니다.

    이중

    CanclePct =10.00으로 하면 수행횟수의 10% 대해서 타임아웃이 발생하도록 설정하게 됩니다.

     

    ostress -Sservername -E -dpubs -ibatch.sql -n10 -r5 -csample.ini

     

     

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

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

     

    다음은 sample.ini 있는 환경변수의 설명입니다.

    INI File Section

    Value

    Description

    [Connection Options]

    LoginTimeout

    Login timeout (seconds).  Value of 0 (default) is infinite

     

    QuotedIdentifier

    Issues a SET QUOTED_IDENTIFIER with the specified value (default is OFF)

     

    AutocommitMode

    Sets the SQL_AUTOCOMMIT mode of ODBC.  Sets the IMPLICIT_TRANSACTIONS option to the opposite setting of what is here (default is on)

     

    DisconnectPct

    Randomly disconnect from the server between iterations of the specified input file or query the specified percentage of time.  A value of 0.0 (default) is never.  If the input file contains a single command, or the input is an individual query, setting this option to 100%will simulate applications that use make-break connections.

     

    MaxThreadErrors

    Maximum number of errors per connection before exiting.  Value of 0 (default) is infinite

     

     

     

    [Query Options]

    NoSQLBindCol

    Set Off to disable calls to SQLBindCol for improved performance.  All rows are still fetched, but the values for the row arens copied into any application variables.  Use this option when you really don't care about the results returned from the query.  Defaults to On in quiet mode, Off otherwise.

     

    NoResultDisplay

    Doesn't print results from the query. Implicitly set ON if NoSQLBindCol is also ON.  Defaults to ON for quiet mode, OFF otherwise

     

    PrepareExecute

    Use ODBC prepared statements (default is Off)

     

    ExecuteAsync

    Execute the query asynchronously, allowing for cancels.  Defaults setting is Off.  In replay mode, all queries are submitted asynchronously and this setting is ignored.

     

    RollbackOnCancel

    Issues a rollback after each cancel operation. Defaults to Off

     

    QueryTimeout

    Sets a query timeout for each query, in seconds.  Default is 0

     

    QueryDelay

    Interjects a delay (milliseconds) before running each query.  Default is 0

     

    MaxRetries

    Maximum retries for deadlocks & timeouts, with 0 retrying forever.  In stress mode, default value is zero.  In replay mode, this setting is ignored an no retries are done.

     

    CancelPct

    Percentage of time to attempt query cancellation. Must have ExecuteAsync=On.  Default to zero

     

    CancelDelay

    Delay (milliseconds) after issuing query before canceling.  If negative, a random delay between CancelDelayMin and CancelDelay is calculated and used.  Defaults to 0

     

    CancelDelayMin

    Minimum delay (milliseconds) after issuing query before cancelling.  Should be 0 or a positive value

     

    CursorType

    Request the following cursor type for any non-sp_cursor* queries.  Available options are forwardonly (default), keyset, dynamic and static.

     

    CursorConcurrency

    Request the following cursor concurrency of any non-sp_cursor* queries.  Available options are readonly (default), lockcc, optcc and optccval.

     

    RowFetchDelay

    Delay this amount of time (milliseconds) between each request to fetch rows (i.e. SQLFetch).  If negative, a random delay between zero and ABS(RowFetchDelay) will be calculated and used.  This can be used to simulate client apps that dont process results, or dont process them in a timely fashion.

     

    BatchDisconnectPct

    After submitting the request to the server, close the connection for this percentage of batches.  A negative number will calculate a random value.

     

     

     

    [Replay Options]

    Sequencing Options

    Comma separated list of options to use in replay mode.  Valid options are global sequence, delta, dtc replay.

     

    DTC Timeout

    DTC transaction timeout (seconds).  Default is 60

     

    DTC Machine

    The server running the MSDTC service to be used as the commit coordinator for DTC transactions.  Default is local server

     

    Playback Coordinator

    For a multi-OSTRESS replay scenario, location where ORCA will run.  Default is local server.

     

    DefaultPassword

    The password to use for any connection that is using SQL authentication but does not have a <PWD> tag in its <CONNECT> node.  If this key is missing a password of eplay?is used as a default.

    A user is assumed to be using Windows authentication if the username contains a backslash (\)character.  See the section redentials used for Login?for more information related to this area.

     

    TimeoutFactor

    OSTRESS automatically uses a query timeout to cancel queries that don run in approximately the same amount of time that they did when the trace was captured.  This is used to re-synchronize the replay in cases where something may have happened during capture (such as a deadlock) that didn happen at replay.  If you are using a slower machine or otherwise know that the queries may be much slower at replay, this can be used to weight how OSTRESS calculates the timeout.  The value is a float and initially defaults to 1.1, meaning that OSTRESS allows the queries to run approximately 110 percent of the time they did at capture.  A value of 2.0 would allow the queries to run twice as long, etc.  OSTRESS automatically increases this value (up to an upper limit of about 3) when it detects a lot of timeouts occurring during the replay.

     

    StartSeqNum

    Specifies the sequence number of the lowest event number to submit during a replay attempt.  By specifying a StartSeqNum/StopSeqNum, a "window" of events from a larger set of trace files can be replayed.  Another alternative for limiting the scope of the replay is to use the start or end time parameters when running Read80Trace.  Default is zero.

     

    StopSeqNum

    Specifies the highest sequence number to replay.  Default is largest 64 bit integer. 

     

    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요

    때때로 공유잠금 또는 교착상태에 빠져 SQL Server가 응답을 하지 않을 경우

    다음 SP를 Master데이터 베이스에 등록한 후 확인하면 어떤부분에 Lock이 발생하였고 누가 유발했는지 확인할 수 있습니다.

    간단하면서도 유용한 프로시져이니 잘 활용하면 도움이 될 겁니다.


    SPID :쿼리를 실행한 프로세스 ID

    Status:lock여부

    LoginName : 로그인한 아이디

    HostName : 쿼리를 실행한 컴퓨터명

    DBNAME : Lock이 발생한 데이터베이스명

    CMD : 실행한 명령문


    CREATE PROC SP_BLOCK 
    AS 
     IF EXISTS (SELECT * FROM SYSPROCESSES WHERE SPID IN(SELECT BLOCKED FROM SYSPROCESSES)) 
      SELECT SPID,BLOCKED,STATUS,LOGINAME,HOSTNAME,DBNAME = SUBSTRING(DB_NAME(DBID),1,10),CMD 
      FROM SYSPROCESSES 
      WHERE BLOCKED <> 0 
         OR (SPID IN(SELECT BLOCKED FROM SYSPROCESSES)) 
     ELSE 
      PRINT 'NO ONE IS BLOCKED'

    GO


    EXEC SP_BLOCK

    GO

    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요

    select
        ((select cntr_value
          from master..sysperfinfo
        where counter_name = 'buffer cache hit ratio')
        /
        (select cntr_value*1.0
          from master..sysperfinfo
        where counter_name = 'buffer cache hit ratio base'))*100.0


    ===========================================================


    이 SQL은 DATABASE Buffer Cache에 올라와있는 sql들이 얼마나 많이 공유되는지를 확인하는 쿼리 입니다.


    OLTP 환경에서는 이 값이 최소 95%이상 유지되는 것을 권장하며, 일반적으로 99%대의 수치를 나타냅니다.


    정확한 값을 측정하기 위해서는 DATABASE가 시작된지 하루 이상 지났을때 확인하여야 하며,


    그 후에도 이 값이 95% 미만으로 나온다면, SQL서버가 사용하는 메모리를 늘려 줄 필요가 있습니다.


    ===========================================================

    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요

    본 문서는 MSSQL City에 올라온 내용을 한글로 옮겨온 것임을 밝혀둡니다.
    ------------------------------------------------------------------------------------

    Some Useful Undocumented SQL Server 7.0 and 2000 DBCC Commands
    Alexander Chigrik
    chigrik@mssqlcity.com

    소개
    이 문서에서는 문서화되지 않은 유용한 DBCC 명령에 대해서 다루고자 한다. 그리고 여기서 소개되는 DBCC 명령을 이용하여 SQL Server 7.0 과 2000에서 관리와 모니터링 작업을 할 수 있을 것이다.

    DBCC는 Database Console Command의 줄임말로, 대개 데이터베이스의 물리적 논리적 무결성 검사를 하는데 쓰인다. 하지만 여기서 보게 되겠지만, 기타 다른 용도로도 쓰인다.

    참고:
      DBCC TRACEON (3604)

    위 명령은 DBCC 명령의 결과값을 보다 효과적으로 확인을 위해 다른 DBCC 명령의 소개에 앞서 먼저 다루고자 한다. 아래에서 소개된 DBCC 명령이 실제로 DBCC TRACEON 명령을 필요로 하는 것은 아니지만 DBCC TRACEON 명령과 함께 실행하지 않으면 아무 결과도 보지 못 할 것이다. (주:기본 적으로 출력은 로그에 남기게 된다. 따라서 결과를 화면으로 출력하기 DBCC TRACEON (3604) 명령을 사용하란 얘기다. 추적 플래그에 관한 글은 여기를 참고하자)

    1. DBCC BUFFER
    버퍼 캐시로부터 버퍼의 헤더와 페이지 내용을 출력한다.

    Syntax:
    dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])

    where dbid|dbname - 데이터베이스 아이디(id) 혹은 데이터베이스 이름 objid|objname - 개체의 아이디 혹은 이름 nbufs - 버퍼 넘버(number) printopt - 출력 옵션 0 - 버퍼 헤더와 페이지 헤더만 출력한다(기본값) 1 - 행별로 구분하여 출력. 2 - 행 구분없이 전체 내용 출력 -- 실행예제 DBCC TRACEON (3604) dbcc buffer(master,'sysobjects') -- 결과(일부 내용만 포함) BUFFERS (in MRU to LRU order): PAGE: (1:1354) -------------- BUFFER: ------- BUF @0x00D76940 --------------- bpage = 0x28CEA000 bhash = 0x00000000 bpageno = (1:1354) bdbid = 1 breferences = 8 bstat = 0x9 bspin = 0 bnext = 0x00000000 PAGE HEADER: ------------ Page @0x28CEA000 ---------------- m_pageId = (1:1354) m_headerVersion = 1 m_type = 2 m_typeFlagBits = 0x0 m_level = 1 m_flagBits = 0x2 m_objId = 1 m_indexId = 2 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 9 m_slotCnt = 17 m_freeCnt = 7086 m_freeData = 1072 m_reservedCnt = 0 m_lsn = (127:482:28) m_xactReserved = 0 m_xdesId = (0:9343) m_ghostRecCnt = 0 m_tornBits = 2 . .


    2. DBCC BYTES
    이 명령은 특정 메모리 어드레스 영역의 내용을 확인할 때 쓰인다. (SQL Server 개발팀에서나 유용하게 쓰일만한 명령인듯 싶습니다.)

    Syntax:
    dbcc bytes ( startaddress, length )

    where startaddress - 시작 어드레스 length - 길이(byte) -- 실행 예제 DBCC TRACEON (3604) dbcc bytes (10000000, 100) -- 실행 결과 00989680: fab018e9 bc4d8dff aff0dbe9 e04d8dff ......M.......M. 00989690: aff120e9 c210b8ff c0e900a0 8dffaff0 . .............. 009896A0: 7ce9b04d 8dffffc4 74e9844d b8ffffc4 M..|....M..t.... 009896B0: 00a0c280 aff0a6e9 9c4d8dff ffc462e9 ..........M..b.. 009896C0: c2b0b8ff 94e900a0 8dffaff0 95e9d44d ............M... 009896D0: 8dffaff0 dae90c4d 8dffaff0 d2e9104d ....M.......M... 009896E0: b8ffaff0 ....


    3. DBCC DBINFO
    특정 데이터베이스의 DBINFO structure 정보를 표시.

    Syntax:
    DBCC DBINFO [( dbname )]

    where dbname - 데이터베이스 이름. -- 실행 예제 DBCC TRACEON (3604) DBCC DBINFO (master) -- 실행 결과(일부만 표시) DBINFO STRUCTURE: ----------------- DBINFO @0x1FBDF1B4 ------------------ dbi_dbid = 1 dbi_status = 24 dbi_nextid = 17435136 dbi_dbname = master dbi_maxDbTimestamp = 1600 dbi_version = 539 dbi_createVersion = 539 dbi_ESVersion = 0 dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 1900-01-01 00:00:00.000 dbi_filegeneration = 0


    4. DBCC DBTABLE
    이 명령은 특정 데이터베이스의 DBTABLE structure 정보를 출력한다.

    Syntax:
    DBCC DBTABLE ({dbid|dbname})

    where dbid|dbname - 데이터베이스 이름 혹은 아이디(id) -- 실행 예제 DBCC TRACEON (3604) DBCC DBTABLE (master) -- 실행 결과(일부만 표시) DBTABLES: --------- DBTABLE @0x281A01C8 ------------------- dbt_dbid = 1 dbt_dbname = master dbt_spid = 0 dbt_cmptlevel = 80 dbt_crtime = 2003-10-20 10:05:11.980 dbt_dbdes = 0x37FA9000 dbt_protstamp = 57743 dbt_nextid = 17435136 dbt_dbname = master dbt_stat = 0x18 dbt_stat2 = 0x100000 dbt_relstat = 0x41000000 dbt_maxDbTimestamp = 1600 dbt_dbTimestamp = 1600 dbt_dbVersion = 539 dbt_repltrans = 568952 dbt_replcount = 0 dbt_replrate = 0.000000 dbt_repllatency = 0.000000 dbt_logmgr = 0x37F19380 dbt_BackupManager = 0x28254788 distbackuplsn = (0:0:0) distlastlsn = (0:0:0) replbeginlsn = (0:0:0) replnextlsn = (0:0:0) dbt_category = 0 dbt_dbccLogging = 0 dbt_dbccLoggingFailure = 0 dbccLoggingActive = 0 nonLoggedAllocActive = 0 WorkfileExtents = 0 FCB @0x37F19918

    (주: 원 문서에 의하면 결과중에 dbt_open 라는 항목이 있다고 한다. 이 값은 데이터베이스에 몇 명의 유저가 접속해 있는지를 알려주는 카운터라고 설명하고 있는데, 이상하게 테스트 결과 보이지가 않는다. 본인이 사용하고 있는 버젼은 SQL Server 2000 SP3(a) 이다. 혹시 다른 버젼에서도 같은 결과인지 테스트 해 주시면 고맙겠습니다..^^)


    5. DBCC DES
    DES (descriptor) 정보를 출력한다.

    Syntax:
    dbcc des [( [dbid|dbname] [,objid|objname] )]

    where dbid|dbname - 데이터베이스 아이디(id) 혹은 이름 objid|objname - 개체 아이디(id) 혹은 이름 -- 실행 예제 DBCC TRACEON (3604) DBCC DES -- 실행 결과(일부만 표시) DES: ---- DES @0x37FA9000 --------------- dhash = 0x37fa4780 dNavCnt = 0 dindex = 0x29146964 dmaxrow = 317 dopen = 0 dobjstat.objid = 1922105888 dobjstat.objsysstat = 0xe0000001 ddbid = 1 dstatus = 0x20 ddbptr = 0x281A01C8 ddbdes = 0x37FA7840 dminlen = 8 dobjid = 1922105888 dobjtype = 8277 dobjrepl = 0 dschema = 32 dobjcols = 4


    6. DBCC HELP
    DBCC HELP 명령은 특정 DBCC 명령의 구문 정보(syntax information)를 보여준다. SQL Server 6.5와 비교하면, 단지 문서화된(BOL에서 확인할 수 있는) DBCC 명령만 볼 수 있다.

    Syntax:
    DBCC HELP ('dbcc_statement' | @dbcc_statement_var | '?')

    -- 실행 예제 DBCC TRACEON (3604) DECLARE @dbcc_stmt sysname SELECT @dbcc_stmt = 'CHECKTABLE' DBCC HELP (@dbcc_stmt) -- 실행 결과 CHECKTABLE ('table_name'[, {NOINDEX | index_id | REPAIR}]) [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]


    7. DBCC IND
    특정 테이블의 인덱스에서 사용된 모든 페이지를 보여준다.

    Syntax:
    dbcc ind( dbid|dbname, objid|objname, printopt = {-2|-1|0|1|2|3} )

    where dbid|dbname - 데이터베이스 아이디(ID) 혹은 이름 objid|objname - 개체 아이디(ID) 혹은 이름 printopt - 출력 옵션 - SQL Server 7.0에서 출력 옵션은 필수적으로 명시하도록 바꿨다. -- 실행 예제 DBCC TRACEON (3604) DBCC IND (master, sysobjects, 0) -- 실행 결과(일부 내용만 표시) PageFID PagePID IAMFID IAMPID ObjectID IndexID PageType IndexLevel NextPageFID... ------- ----------- ------ ----------- ----------- ------- -------- ---------- ----------- 1 11 NULL NULL 1 1 10 0 0 1 8 1 11 1 0 1 0 1 1 365 1 11 1 0 1 0 1 1 369 1 11 1 0 1 0 1 1 373 1 11 1 0 1 0 1 1 394 1 11 1 0 1 0 1 1 448 1 11 1 0 1 0 1 1 452 1 11 1 0 1 0 1 1 664 1 11 1 0 1 0 1


    8. DBCC log
    이 명령은 특정 데이터베이스의 트랜젝션 로그(transaction log) 볼 때 사용된다.

    Syntax:
    DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )

    PARAMETERS: dbid|dbname - 데이터베이스 아이디(ID) 혹은 이름 type - 출력옵션 0 - 최소 정보 (operation, context, transaction id) : 기본값 1 - 좀더 많은 정보 (plus flags, tags, row length, description) 2 - 매우 자세한 정보 (plus object name, index name, page id, slot id) 3 - 각 작업(operation)별 모든 정보 4 - 각 작업(operation)별 모든 정보와 함께 현재 트랜젝션 로그 행의 핵사 덤프(hexadecimal dump) 포함 -1 - 각 작업(operation)별 모든 정보와 함께 현재 트랜젝션 로그 행의 핵사 덤프(hexadecimal dump)와 함게 Checkpoint Begin, DB Version, Max XDESID


    master 데이터베이스의 트랜젝션 로그를 보기위해서는 아래와 같이 실행하면 된다.
    DBCC log (master)

    (주:트랜젝션 로그 파일이 대단히 큰 경우 이 명령의 사용을 자제하기 바란다. 테스트 겸 출력옵션 3을 사용하여 작업을 하였는데, 한참후 도중 컴터가 뻗어버리는 불상사가 발생하였다..ㅠㅠ 역시 로그 확인은 Log Explorer가 짱인듯..)


    9. DBCC PAGE
    데이터 페이지의 구조를 확인할 때 사용한다.

    Syntax:
    DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])

    PARAMETERS: dbid|dbname - 데이터베이스 아이디(ID) 혹은 이름 Pagenum - 조회 하고자 하는 페이지의 번호 Print option - 출력옵션 0, 1, 2 중 하나.(선택사항) 0 - 페이지 헤더만을 출력(기본값) 1 - 헤더 + 행 단위 값 2 - 헤더 + 페이지 내용 그대로 Cache - 1 또는 0 의 값을 가질 수 있다(선택사항) 0 - 해당 페이지가 캐시에 있어도 무조건 디스크에서 내용을 참조하게 한다. 1 - 해당 페이지가 캐시에 있으면 캐시의 내용을 조회한다. 캐시에 없을 경우에는 디스크에서 직접 참조(기본값) Logical - 가상 페이지(virtual page)와 논리 페이지(logical page) 중 참고할 페이지를 선택(선택사항) 0 - 가상 페이지(virtual page) 1 - 논리 페이지(logical page) (기본값) -- 실행예제 DBCC TRACEON (3604) DBCC PAGE (master, 1, 1) -- 실행결과(일부만 표시) PAGE: (1:1) ----------- BUFFER: ------- BUF @0x00D61C40 --------------- bpage = 0x28282000 bhash = 0x00000000 bpageno = (1:1) bdbid = 1 breferences = 1 bstat = 0xb bspin = 0 bnext = 0x00000000 PAGE HEADER: ------------ Page @0x28282000 ---------------- m_pageId = (1:1) m_headerVersion = 1 m_type = 11 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0 m_objId = 99 m_indexId = 0 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0 m_slotCnt = 1 m_freeCnt = 2 m_freeData = 8188 m_reservedCnt = 0 m_lsn = (143:184:4) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 2


    10. DBCC procbuf
    프로시저의 캐시로부터 저장 스로시저의 헤더를 보여준다.

    Syntax:
    DBCC procbuf( [dbid|dbname], [objid|objname], [nbufs], [printopt = {0|1}] )

    where dbid|dbname - 데이터베이스 아이디(ID) 혹은 이름 objid|objname - 개체 아이디(ID) 혹은 이름 nbufs - 출력하고자 하는 버퍼 번호 printopt - 출력옵션 0 - 프로시저 버퍼와 헤더만 출력 (기본값) 1 - 프로시저 버퍼, 헤더, 버퍼의 내용(contents) 출력 -- 실행예제 DBCC TRACEON (3604) DBCC procbuf(master,'sp_help',1,0)


    11. DBCC prtipage
    특정 인덱스의 각 행의 페이지 번호를 출력한다.

    Syntax:
    DBCC prtipage( dbid, objid, indexid, indexpage )

    where dbid - 데이터베이스 아이디(ID) objid - 개체 ID indexid - 인덱스 ID indexpage - 출력할 인덱스의 논리 페이지 번호 -- 실행예제 DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('master') SELECT @objectid = object_id('sysobjects') DBCC prtipage(@dbid,@objectid,1,0) -- 실행결과(일부만표시) FileId PageId Row Level ChildFileId ChildPageId id ------ ----------- ------ ------ ----------- ----------- ----------- 1 12 0 0 1 8 NULL 1 12 1 0 1 668 27147142 1 12 2 0 1 1095 103671417 1 12 3 0 1 365 181575685 1 12 4 0 1 1092 269244014 1 12 5 0 1 665 363148339 1 12 6 0 1 1088 466100701 1 12 7 0 1 448 565577053 1 12 8 0 1 1093 647673355 1 12 9 0 1 666 734625660 1 12 10 0 1 669 839674039 1 12 11 0 1 394 949578421


    12. DBCC pss
    이 명령은 현재 서버에 연결된 프로세스 정보를 보여준다.

    Syntax:
    DBCC pss( suid, spid, printopt = { 1 | 0 } )

    where suid - 사용자 ID spid - 프로세스 ID printopt - 출력옵션 0 - 표준 출력, 1 - all open DES's and current sequence tree)


    13. DBCC resource
    이 명령은 서버 레벨의 RESOURCE, PERFMON, DS_CONFIG에 대한 정보를 보여준다. RESOURCE는 서버에 의해 사용된 다양한 자료 구조의 주소를 보여준다. PERFMON은 master..spt_monitor 테이블을 이루는 정보를 포함하고 있다. DS_CONFIG는 master..syscurconfigs 테이블을 이루는 정보를 포함하고 있다.

    Syntax:
    DBCC resource

    -- 실행 예제 DBCC TRACEON (3604) DBCC resource -- 실행 결과 RESOURCE: --------- RESOURCE @0x00A12000 -------------------- rdes = 939027392 *rdeshash = 0 rdescount = 2048 *prpssarray = 673284248 rprocihash = 0x37F117F0 rprocnhash = 0x37F157F0 rprocmemused = 116 rflag1 = 16 rflag2 = 0 rprocnum = 64 rMSversion = 134218488 rpsytab = 938590520 m_pLangCache = 938591208 rservername = DEVSVR servicename = MSSQLSERVER servinstname = DEVSVR ropen_objmsgs = 0 pPerfStats = 11004808 pResLock = 938520472 LoginMode = 2 rsaspid = 0 DefaultLogin = guest AuditLevel = 0 DefaultDomain = DEVSVR MapChars[0] '_' = MapChars[1] '$' = MapChars[2] '#' = MaxConnections = 32767 MaxCPUs = 32 article_cache = 672932768 pResLockFree = 938596396 MaxSubProcesses = 255 CurSubProcesses = 0 replmem = 0x281C0030 PERFMON @0x00A12038 ------------------- pcputicks = 0 pioticks = 0 pidlticks = 0 pbs_rpck = 102 pbs_rbyt = 0 pbs_conn = 27 pbs_errors = 0 pblk_rd = 805 pblk_wr = 191 pblk_errors = 0 DS_CONFIG @0x00A14060 --------------------- cconfsz = 8 cmajor = 8 cminor = 0 crevision = 10 cbootsource = 2 crecinterval = 0 ccatalogupdates = 0 cmbSrvMemMin = 0 cmbSrvMemMax = 2147483647 cusrconnections = 0 cnetworkpcksize = 4096 ckbIndexMem = 0 cfillfactor = 0 ctapreten = 0 cwritestatus = 0 cfgpriorityboost = 0x0 cfgexposeadvparm = 0x0 cfglogintime = 20 cfgpss = 0 cfgpad = 4096 cfgxdes = 16 cfgaffinitymask = 0 cfgbuf = 4362 cfgdes = 0 cfglocks = 0 cfgquerytime = 600 cfgcursorthrsh = -1 cfgrmttimeout = 10 cfg_dtc_rpcs = 0 cclkrate = 31250 cfg_max_text_repl_size = 65536 cfgupddate = 37895 cfgupdtime = 23699388 fRemoteAccess = 1 cfgbufminfree = 331 cnestedtriggers = 0x1 cfgworkingset = 0x0 cdeflang = 29 cidCfg = 0xd011 cidCfgOld = 0xd011 cfgCutoffYear = 2049 cfgLangNeutralFT = 1042 maxworkthreads = 255 minworkthreads = 32 minnetworkthreads = 32 threadtimeout = 15 connectsperthread = 0 cusroptions = 0 exchcostthreshold = 5 maxdop = 0 cchecksum = 475 rWrkExtCache = Empty


    14. DBCC TAB
    데이터 페이지의 구조를 확인할 때 사용한다. (DBCC PAGE 명령과 비교하여 이 명령은 특정 페이지 번호를 지정하는 것이 아니라 모든 데이터 페이지를 보여준다.)

    Syntax:
    DBCC tab (dbid, objid)

    where dbid - 데이터베이스 ID objid - 테이블 ID -- 실행예제 DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('master') SELECT @objectid = object_id('sysdatabases') DBCC TAB (@dbid,@objectid) -- 실행 결과(일부만표시) PageFID PagePID IAMFID IAMPID ObjectID IndexID PageType IndexLevel NextPageFID ... ------- ----------- ------ ----------- ----------- ------- -------- ---------- ----------- 1 1334 NULL NULL 30 1 10 0 0 1 1333 1 1334 30 0 1 0 0 1 1335 1 1334 30 1 2 0 0 1 1345 NULL NULL 30 2 10 0 0 1 1344 1 1345 30 2 2 0 0
    Posted by ㏈ª ☞ β┖υΕJini.κR

    댓글을 달아 주세요