Transcript SQL_Advanced_Programming.ppt

제 16 회 SQL 고급 과정 세미나
실무를 고려한
SQL Advanced Programming
2004.10.18
우철웅
㈜인브레인 기술이사
[email protected]
제 16 회 SQL 고급과정세미나
차례

Session 1 : 저장 프로시저에서 스크립트 사용과 SQL 주입 공격 막기
14:00~15:00
 저장 프로시저 내부에서 스크립트 사용
 침입자가 당신의 DBA가 되는 것을 막아라.

Session 2 : 자동화 추적 만들기와 성능 해결 단계 예 살펴보기
15:10~16:10
 자동화된 추적을 설정하기 위한 9단계 추적기록으로부터 무엇을 살필 것인가?
 성능 해결책을 해부해보자. ( 프로필러, 쿼리분석기 도구를 이용한 추적 )

Session 3 : 실무 고급 활용 프로그래밍
16:20-17:50
 실무에 바로 적용 가능한 고급 활용 I
 BOM 구조 처리에 대한 최적화 방법은 무엇인가?
 동시 사용성 향상을 위한 채번 최적화는 어떻게 해야 하는가?

* 실무에 바로 적용 가능한 고급 활용 II
 IDENTITY() 함수를 어떤 해결을 위해 효과적으로 사용할 수 있을까?
 부분 범위 처리에 효율적인 계층형 게시판 구현 방법은 무엇인가?
 뷰에 있어서의 백도어 활용
제 16 회 SQL 고급과정세미나
저장 프로시저 내부에서
스크립트 사용
제 16 회 SQL 고급과정세미나
저장 프로시저 내부에서 스크립트 사용

왜 이런 부분이 필요할까





DTS로 처리하기 어려운 경우에 고려
DTS에서 Scripts를 사용하는 경우와 같음
일반 Application으로 구현하기는 변경이 잦거나 낭비일 수 있는
경우
동적으로 Job을 생성하여 실행시킬 수 있음
동적 Job 생성과 실행





SQLServerAgent 서비스로 실행하는 새 작업을 추가
EXEC msdb..sp_add_job
지정된 서버에 지정된 작업을 대상으로 지정
EXEC msdb..sp_add_jobserver
작업에 단계를 추가
EXEC msdb..sp_add_jobstep
SQL Server 에이전트가 즉시 작업을 실행
EXEC msdb..sp_start_job
기타
sp_stop_job, sp_delete_job, sp_delete_jobstep
sp_update_job, sp_update_jobstep 등
제 16 회 SQL 고급과정세미나
저장 프로시저 내부에서 스크립트 사용

스크립트 작성과 Job Step에 추가하기
EXEC msdb..sp_add_jobstep @job_name=@NewJobName, @step_id=1,
@step_name='Create Excel File',
@subsystem='ActiveScripting', @command=@ScriptCode
SET @ScriptCode = 'Dim xlApp, xlBook, xlSheet
dim cn, rs, cmdText, t, FldName
SET xlApp = CreateObject("excel.application")
SET xlBook = xlApp.Workbooks.Open("' + @TargetName + '", 0, False, 2)
SET xlSheet = xlBook.Worksheets(1)
xlApp.DisplayAlerts = False
xlSheet.Name = "' + @TargetTable + '"
xlApp.ActiveCell.EntireRow.Insert
SET cn = CreateObject("ADODB.Connection")
:
cn.Properties("Initial Catalog").Value = “Pubs"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
cmdText = "SELECT table_name, column_name, ordinal_position FROM
information_schema.columns WHERE table_name = ''' + @TargetTable + ''' ORDER BY
ordinal_position"
rs.Open cmdText, cn
FOR t = 1 TO ' + cast(@FieldCount AS Varchar(4)) + '
FldName = rs.Fields("column_name")
xlSheet.Cells(1, t).Value = FldName
rs.MoveNext
NEXT
:
제 16 회 SQL 고급 과정 세미나
저장 프로시저 내부에서 스크립트 사용
Demo
제 16 회 SQL 고급과정세미나
침입자가 당신의 DBA가
되는 것을 막아라
제 16 회 SQL 고급과정세미나
침입자가 당신의 DBA가 되는 것을 막아라

주입 공격(Injection Attack) 이란?




권한이 없는 사용자가 자료 처리를 위한 필드에 SQL 문을
끼워 넣어 데이터 베이스에 접근하는 공격
방화벽이나 암호화와는 별개의 보완이 필요한 부분
대부분의 응용 프로그램의 입력란을 이용하여 공격 가능
이미 테이블명에 대해 알고 있는 시스템 테이블의 경우 문
제가 심각할 수 있음
제 16 회 SQL 고급과정세미나
침입자가 당신의 DBA가 되는 것을 막아라

ASP.NET 코드 비하인드(Code-Behind)
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
Button1.Click
Dim strQuery As String
:
End Try
strQuery = "SELECT * FROM UserInj WHERE userid='" & TextBox1.Text & "' AND password='" &
TextBox2.Text & "'"
Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(strQuery)
cmd.Connection = conn
Dim rdr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
If (rdr.Read()) Then
lblResult.Text = "**User Authenticated, Welcome: " & rdr.Item(0)
Else
lblResult.Text = "--Invalid User--"
End If
rdr.Close()
conn.Close()End Sub

조합된 SQL 구문
SELECT * FROM UserInj
WHERE userid='sadf' AND Password='password‘
SELECT * FROM UserInj
WHERE userid='sadf' OR 1 = 1 ; DROP TABLE UserInj -- ' AND Password='password'
제 16 회 SQL 고급과정세미나
침입자가 당신의 DBA가 되는 것을 막아라

방어 방법

사용자 입력 검사

MaxLength로 입력 필드의 길이를 제한

특수 문자 입력을 검사
•
•
.NET의 ValidationSummary 또는 RegularExpression 유효성검사 컨트롤
을 이용
정규 표현식의 예
^[0-9a-zA-Z_]$
Dim reg As Regex = New Regex("^[A-Za-z0-9_]{10}$")
reg.IsMatch(TextBox1.Text)

SQL Server에서 강화

Ad Hoc 쿼리  저장 프로시저로 전환
그러므로 변수들을 코드와 분리

응용 프로그램 로그인 계정에 대한 최소 권한만 설정
적어도 dbo나 systemadmin, db_ddladmin와 같은 관리자 권한이
아닌 db_datareader나 db_datawriter 보다 같거나 낮은 권한으로


관련 작업 동작에 관한 권한만 설정된 계정 사용
SQL Server의 반환 메시지를 그대로 사용하지 말자.
그러므로 다음 공격 동작에 대한 실마리를 제공할 수 있음

TextBox1.text.Replace("'“, "''“)로 작은 따옴표 하나 더 추가
제 16 회 SQL 고급과정세미나
자동화된 추적을
설정하기 위한 9 단계
제 16 회 SQL 고급과정세미나
자동화된 추적을 설정하기 위한 9 단계
1.
프로필러를 사용하여 추적 정의를 생성
1.
2.
2.
3.
추적 실행
추적 정의 및 추적 산출물을 파일로 저장
1.
2.
4.
6.
7.
8.
9.
추적 결과를 T-SQL 스크립트로 저장
추적 결과를 포함하고 있는 파일을 선택
추적 정의 스크립트를 생성하기 위한 저장 프로시저 생성
1.
5.
필요한 데이터 열 선택(DB, Application, TextData, User, Duration, CPU,
Reads, Writes 등)
필터 선택(Duration 1초 이상 등)
3단계의 추적 스크립트를 이용 저장 프로시저로 작성
추적 출력 파일로부터 테이블 생성
추적 경로 및 파일 명칭을 저장하기 위한 테이블 생성
추적을 실행하기 위한 작업 일정 설정
추적을 중단하고, 파일을 업로드하고, 해당 파일을 삭제하는 작업 일정
설정
추적 데이터에 대한 조사 및 활용
제 16 회 SQL 고급과정세미나
자동화된 추적을 설정하기 위한 9 단계

프로필러 관련 저장 프로시저 들
CREATE PROCEDURE _duration_trace
@file_name nvarchar(155), -- NOTE: no file extension
@trace_id int output
AS
DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint
SET @maxfilesize = 5
추적 정의
EXEC @rc = sp_trace_create @TraceID output, 0, @file_name, @maxfilesize, NULL
:
BEGIN
이벤트 및 이벤트
DECLARE @on bit
열 추가/제거
SET @on = 1
EXEC sp_trace_setevent @TraceID, 10, 1, @on
--1 = textData
EXEC sp_trace_setevent @TraceID, 10, 3, @on
--3 = DatabaseID
EXEC sp_trace_setevent @TraceID, 10, 13, @on --13 = Duration
:
필터 적용
SET @bigintfilter = 1000
EXEC sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
:
EXEC sp_trace_setstatus @TraceID, 1
추적을 시작/중지
SET @trace_id = @traceID
END
제 16 회 SQL 고급과정세미나
자동화된 추적을 설정하기 위한 9 단계

PSSDiag 소개





PSS 팀 내부적으로 사용하던 Tool
프로파일러 기능 외에 성능 측정치 수집, Blocking,
SQLDag 등
PSS 소개
http://support.microsoft.com/default.aspx?scid=kb;enus;830232
download
http://www.microsoft.com/downloads/details.aspx?amp;a
mp;amp;displaylang=en&familyid=5564386a-28c2-44838293-76fff67b9eb3&displaylang=en
PSS에서 사용하는 SP 설명
http://support.microsoft.com/default.aspx?scid=kb;enus;271509
제 16 회 SQL 고급과정세미나
자동화된 추적을 설정하기 위한 9 단계

Trace 내용 분석




주로 CPU, Duration, Reads, Writes 등의 분석
오류 및 경고에 Attention이나 Exception이나 Warning 등
의 이벤트 클래스도 포함할 필요 있음
잠금 수에 Lock Timeout이나 Deadlock 등도 포함 필요
튜닝 대상 선정



쿼리 유형별 횟수와 관측치의 평균, 최대 , 최소, 편차 등을
구함
대부분 상위 10%가 전체 쿼
문제의 주요 쿼리에 대한 선정
리의 90% 이상을 차지함
가장 빈번 쿼리에 대한 선정
제 16 회 SQL 고급 과정 세미나
자동화된 추적을 설정하기 위한 9 단계
Demo
제 16 회 SQL 고급과정세미나
BOM 구조 처리
제 16 회 SQL 고급과정세미나
BOM 구조 처리

BOM 구조에 대한 이해


㈜한국유통
조직, 계정과 같이 재귀 참조 관
계를 갖음
멤버가 교차 참조는 없어야 함
영업부
영업1부




조직이나 계정 멤버의 경우는 하
나의 상위 멤버만 가지지만
BOM의 경우의 자재는 여러 제품
의 하위 멤버로 존재할 수 있음
그러나 반성품 각 BOM은 한번만
존재 해야 함
일반적으로 커서를 처리하려 함
Set Base로 처리하는 방법을 찾
아야 함
BOM : Bill Of Material
영업2부
구매부
내자과
강남지점
특수유통팀
강북지점
실수요영업팀
…
생산부
외자과
제 16 회 SQL 고급과정세미나
BOM 구조 처리

처리 방법
방법
장점
단점
단순 커서로 처리
가장 단순한 형태로 논리적 이해가 편함
순차 처리로 IDENTITY 컬럼 사용 할 수 있음
레벨 확장을 Hard Coding에 의존
스크립트 량 많음
32 단계 이상 중첩 호출 할 수 없음
커서 재귀 호출
레벨 확장 자동 처리 됨
스크립트 량 단축
순차 처리로 IDENTITY 컬럼 사용 할 수 있음
호출 횟수 레벨 수 만큼으로 축소
비용 최소화
계층 정렬이 필요시 계층키 만들어서 활용
해야 함, 추가 비용 필요
Set Base로 처리
32 단계 이상 중첩 호출 할 수 없음
불필요한 중첩 호출일 수 있음
Set Base 재귀 호출
위에 열거한 것과 유사한 장단점을 갖음
함수 이용 처리
호출한 함수 결과셋을 다른 테이블과 관련된
조인 작업 등이 용이
제 16 회 SQL 고급과정세미나
BOM 구조 처리

Set Base 처리 스크립트
CREATE PROC up_GetBOM_UseSet
@Item_CD AS CHAR(2)
AS
SET NOCOUNT ON
CREATE TABLE #tree
(Item_CD CHAR(2) NOT NULL
,pItem_CD CHAR(2) NULL
,Item_Type SMALLINT
NULL
,Level INT
NOT NULL
,Hierarchy_CD VARCHAR(250)
DECLARE @Level AS int
SET @Level = 0
)
INSERT INTO #tree
SELECT Item_CD, pItem_CD, Item_Type, @Level, Item_CD FROM BOM WHERE Item_CD = @Item_CD
WHILE @@ROWCOUNT> 0
BEGIN
SET @Level = @Level + 1
INSERT INTO #tree
SELECT B.Item_CD, B.pItem_CD, B.Item_Type, @Level, T.Hierarchy_CD + '.' + B.Item_CD
FROM BOM AS B JOIN #tree AS T
ON B.pItem_CD = T.Item_CD AND T.Level = @Level - 1
END
SELECT * FROM #tree ORDER BY Hierarchy_CD
GO
제 16 회 SQL 고급과정세미나
BOM 구조 처리

Set Base Recursive 처리 스크립트
CREATE PROC up_GetBOM_UseSetRecursive
@level AS INT
AS
IF EXISTS( SELECT * FROM BOM AS B JOIN #tree AS T
ON B.pItem_CD = T.Item_CD AND T.level = @level)
BEGIN
INSERT INTO #tree
SELECT B.*, @level + 1, T.Hierarchy_CD + '.' + B.Item_CD
FROM BOM AS B JOIN #tree AS T
ON B.pItem_CD = T.Item_CD AND T.level = @level
SET @level = @level + 1
EXEC up_GetBOM_UseSetRecursive @level
END
GO
CREATE TABLE #tree
(Item_CD CHAR(2) NOT NULL
,pItem_CD CHAR(2) NULL
,Item_Type INT
NULL
,level
INT
NULL
,Hierarchy_CD VARCHAR(250)
)
INSERT INTO #tree SELECT *, 0, Item_CD FROM BOM WHERE Item_CD ='A'
EXEC up_GetBOM_UseSetRecursive 0
SELECT * FROM #tree ORDER BY Hierarchy_CD
제 16 회 SQL 고급 과정 세미나
BOM 구조 처리
Demo
제 16 회 SQL 고급과정세미나
동시 사용 향상을 위한
채번 최적화
제 16 회 SQL 고급과정세미나
동시 사용 향상을 위한 채번 최적화

일반적인 채번 과정
MAX값
77을 조회
1 증가한
78을 삽입
배타적 잠금 처리 않은
작업 A
MAX값
77을 조회
작업 A의 입력 작업이
시작되기 전에
동일한 MAX 값을 조회
작업 B
1 증가한
78을 삽입
배타적
잠금으로
대기
중복키로
삽입오류
MAX값
조회 대기
배타적
잠금으로
작업 A, B의 입력 작업이
대기
진행 중에 MAX 값을 조회
작업 C
트랜잭션을 처리하지 않
은
MAX값
78을 조회
1 증가한
79을 삽입
제 16 회 SQL 고급과정세미나
동시 사용 향상을 위한 채번 최적화
 일반적인 채번 과정의 문제점

동시 작업 불가


트랜잭션 내의 모든 작업이 완료될 때 까지 다음 채번 작
업은 대기 즉 Serializable
중복 채번으로 인한 오류

채번 작업과 실제 자료 삽입 작업 간의 인터벌이 길 수록
중복 오류 발생의 여지가 많음
제 16 회 SQL 고급과정세미나
동시 사용 향상을 위한 채번 최적화
 효율 적인 채번 처리 방법

NoLock으로 채번하고 중복 오류시 Retry


Xlock or UpdLock으로 처리한 채번과 데이터 처리
에 대한 Transaction 분리



NoLock으로 채번하므로 동시 사용성을 높이고 중복 오류
시 Retry로 오류 처리 강화
진정한 문제는 채번에 대해 데이터 처리 작업을 하나의
Transaction으로 묶으므로 발생하게 됨
Transaction을 분리로 데이터 처리에 필요한 시간 만큼
동시 사용성을 높일 수 있음
효율적인 채번 테이블 사용


채번 기준의 하나의 행으로 관리하는 방법
채번 기준과 일치하는 다중행으로 관리하는 방법
제 16 회 SQL 고급과정세미나
동시 사용 향상을 위한 채번 최적화

채번과 데이터 처리 트랜잭션을 분리한 채번 과정
Order
Details
삽입
Orders
삽입
채번
작업 A
채번
채번
대기
Order
Details
삽입
Orders
삽입
작업 B
트랜잭션을 처리하지 않
은
채번작업 A

Transaction 분리
채번
채번값을 이용한 데이터 처리
채번
채번값을 이용한 데이터 처리
제 16 회 SQL 고급과정세미나
동시 사용 향상을 위한 채번 최적화

채번
형태
번호
일자
일자
번호
일자
번호
1
20010601
20010601
1
20010601
S01060001
2
20010601
20010601
2
20010601
S01060002
3
20010602
20010602
1
20010602
S01060003
4
20010602
20010602
2
20010602
S01060004
5
20010602
20010602
3
20010602
S01060005
6
20010701
20010701
1
20010701
S02070001
단순 채번
그룹별 채번
조합 번호 채번
문자열을 잘라서 Max를 얻어야 한다면 채번 테이블이 더욱 필요

채번
테이블
구조
OrdDate
SlipNo
OrdDate
SlipNo
20010601
4
20010601
1
20010602
2
20010601
2
20010601
3
20010601
4
20010602
1
20010602
2
단일 행 채번 테이블
다중 행 채번 테이블
Transaction 내에서 채번이 필요하다면 다중 행 채번 테이블 사용 고려
제 16 회 SQL 고급과정세미나
동시 사용 향상을 위한 채번 최적화

채번 테이블 구조에 따른 동시성
OrdDate
SlipNo
20010601
3
20010602
1
Transaction
TR1 처리중, TR2 대기중
TR3 처리중, TR4 TR5 대기중
단일 행 채번 테이블
OrdDate
SlipNo
20010601
1
20010601
2
20010601
3
TR1 처리중
20010601
4
TR2 처리중
20010602
1
20010602
2
20010602
3
TR3 처리중
20010602
4
TR4 처리중
20010602
5
TR5 처리중
다중 행 채번 테이블
Transaction 내에서 채번이 필요하다면 다중 행 채번 테이블 사용 고려
Transaction
제 16 회 SQL 고급과정세미나
동시 사용 향상을 위한 채번 최적화

채번 테이블 구조에 따른 채번 최적화


Max 값을 채번 후 갱신이나 삽입하지 말자
가능한 하나의 한 구문으로 처리하도록 하자.
단일 행 채번 테이블
UPDATE SlipNo SET @SlipNo = SlipNo = SlipNo + 1 WHERE OrdDate = @OrdDate
IF @@RowCount = 0
BEGIN
SET @SlipNo = 1
INSERT INTO SlipNo(OrdDate, SlipNo) VALUES(@OrdDate, @SlipNo)
END

다중 행 채번 테이블
INSERT INTO SlipNo
SELECT @OrdDate, ISNULL( MAX(SlipNo), 0) + 1
FROM SlipNo
WHERE OrdDate = @OrdDate

제 16 회 SQL 고급 과정 세미나
동시 사용 향상을 위한 채번 최적화
Demo
제 16 회 SQL 고급과정세미나
효율적인 계층형
게시판 구현
제 16 회 SQL 고급과정세미나
효율적인 계층형 게시판 구현

계층형 게시판의 이해







상위 글 번호를 재귀 참조하는 관계임
리스트는 입력된 순서와 관계가 없음
조회 순서는 근원 게시번호를 내림차순으로 그 안에서 계층순으로
리스트들에 대해 페이지 단위로 효율적인 부분 범위 처리 필요 함
BOM과 같이 특정 품목을 기준으로 조회하지 않기 때문에 재귀 참조
로 구현하면 속도가 느림
별도의 컬럼이나 컬럼들을 가지고 효과적인 처리가 필요함
리스트 자료의 압축을 위해 데이터 량이 큰 내용에 관련된 컬럼을 분
리하거나 효율적인 인덱스 설정이 필요 함
제 16 회 SQL 고급과정세미나
효율적인 계층형 게시판 구현

계층형 게시판의 구현 방법



순번 이용 : 게시번호, 근원 게시번호, 상위 게시번호,
레벨, 레벨내 순번 등으로 관리
계층키 이용

게시 번호를 조합한 단순 계층키
000001
000001000002
000001000002000003

근원 게시번화와 답글 순서키를 조합한 계층키
000001
000001
000001a
00000101
000001aa
0000010101
기타 여러 방법이 존재
방법
장점
삽입이 복잡함
부분 범위 처리가 복잡함
복합 인덱스로 비용이 많이 듬
순번 갱신 횟수가 많을 수 있음
순번 이용
계층키 이용
단점
삽입이 간단함
부분 범위 처리가 용이함
단일 컬럼 인덱스로 비용이 적게 듬
순번을 사용치 않으므로 갱신이 적음
제 16 회 SQL 고급과정세미나
효율적인 계층형 게시판 구현

효율적인 테이블 구조를 위한 고려

효율적인 계층키 관리
 계층키 내의 두가지 속성을 구분


레벨별로 답글의 고정 위치에 입력 순번을
가지면 됨
 리스트 정렬 방법을 위한 최적화 고려
테이블 분리로 데이터 압축율 높이기
 리스트 조회에서만 사용되는 컬럼들과 내
용 조회에 사용 되는 컬럼 분리
 물리적 분리대신 게시내용에 대해 Text 데
이터형식을 사용하므로 분리하는 것도 고
려할 수 있음


근원 게시번호 : 계속적인 사용으로 많은
구성원이 들어감 자릿수가 많이 필요
하위 답글 : 하나의 게시번호에 많아야 몇
십개 정도로 적음
글번호
글번호
:
게시내용
계층키
:
제목
작성자
작성일
조회수
:
글번호
int
:
계층키
Varchar(100)
:
제목
Varchar(100)
작성자
Char(12)
작성일
datetime
조회수
smallint
내용
Text
제 16 회 SQL 고급과정세미나
효율적인 계층형 게시판 구현
계층형키 예제 I

4 페이지
3 페이지
2 페이지
1 페이지
No
PNo
Root
No
-. 근원 게시번호 6 자리 :
-. 답글 구성번호 2 자리 : 한 멤버의 리플수는 100개
최대 리플수에 따라 자릿수 조정하여 사용
HKey
Title
8
Null
8
000008
아
5
Null
5
000005
마
6
5
5
00000501
7
6
5
0000050101
10
5
5
00000502
1
Null
1
000001
2
1
1
00000101
3
2
1
0000010101
4
3
1
000001010101
9
2
1
0000010102

전체 쿼리 형태
SELECT * FROM Notify
ORDER BY RootNo DESC, HKey ASC
☞바

첫페이지 쿼리 형태
SELECT TOP 4 * FROM Notify
ORDER BY RootNo DESC, HKey ASC
☞사
☞New
가
☞나
☞다
☞라
☞신규

둘째 페이지 쿼리 형태
SELECT * FROM (
SELECT TOP 4 * FROM (
SELECT TOP 7 * FROM Notify
WHERE (RootNo = 8 AND Hkey >= ‘000008’)
OR RootNo < 8
ORDER BY RootNo DESC, Hkey ASC ) t1
ORDER BY RootNo ASC, Hkey DESC ) t2
ORDER BY RootNo DESC, Hkey ASC
이해가 갈 것 같은데 복잡 !!!
같은 레벨의 최근 것이 먼저 위치하지 않음.
제 16 회 SQL 고급과정세미나
효율적인 계층형 게시판 구현

부분 범위 처리를 위한 뷰 백도어 이용




뷰를 정의할 때 SELECT 문에 ORDER BY 절 허용치 않음(ANSI)
즉 뷰에 ORDER BY 절은 권장치 않으며, 뷰를 SELECT 하는 문장에
ORDER BY 절을 사용하는 것이 좋음
뷰에 ORDER BY를 사용할 수 있는 백도어
 TOP 절과 함께 ORDER BY 사용
 OPENQUERY() 호출 내부에 ORDER BY 사용
실행 계획에서 최적화되지는 않음
select * from
(select top 100 percent *
from Pubs.dbo.Sales
order by qty ) t
order by qty desc
제 16 회 SQL 고급과정세미나
효율적인 계층형 게시판 구현

4 페이지
3 페이지
2 페이지
1 페이지
No
계층형키 예제 II
PNo
Root
No
-. 문제는 RootNo와 Hkey를 DESC, ASC를 조합한 사용으로 복잡
-. 01부터가 아닌 99부터 감소값으로 적용해도 해결 않됨
-. 01~99까지 보다 항상 큰 값이 되게 Sub Fix 붙이면 OK
HKey
Title

전체 쿼리 형태
SELECT * FROM Notify
ORDER BY HKey DESC
8
Null
8
000008z
아
5
Null
5
000005z
마
10
5
5
00000502z
☞New
6
5
5
00000501z
☞바
7
6
5
0000050101z
☞사
1
Null
1
000001z
가
2
1
1
00000101z
☞나
9
2
1
0000010102z
☞신규
3
2
1
0000010101z
4
3
1
000001010101z
☞다

첫페이지 쿼리 형태
SELECT TOP 4 * FROM Notify
ORDER BY HKey DESC

둘째 페이지 쿼리 형태
SELECT * FROM (
SELECT TOP 4 * FROM (
SELECT TOP 7 * FROM Notify
WHERE Hkey <= ‘000008z’
ORDER BY Hkey DESC ) t1
ORDER BY Hkey ASC) t2
ORDER BY Hkey DESC
☞라
이제 RootNo가 없어도 OK!!!
제 16 회 SQL 고급과정세미나
효율적인 계층형 게시판 구현

조회 화면 유형

페이지 리스트가 없는 화면
No

PNo
Root
No
HKey
Title
8
Null
8
000008z
아
5
Null
5
000005z
마
10
5
5
00000502z
|◀
◀
☞New
▶
▶|
페이지 리스트가 존재하는 화면
No
PNo
Root
No
HKey
Title
8
Null
8
000008z
아
5
Null
5
000005z
마
10
5
5
00000502z
◀
☞New
1 2 3 4 5 6 7 8 10 11 12 13 14 15 16 17 18 19 29
▶
제 16 회 SQL 고급 과정 세미나
효율적인 계층형 게시판 구현
Demo
제 16 회 SQL 고급과정세미나
IDENTITY() 함수



INTO로 테이블 생성과 함께 만들어야만 함
IDENTITY 컬럼을 만들고 데이터 입력하는 원리와 동일
결과집합의 정렬순서를 만들기 위해 적합치 않을 수 도
있음




버전에 따라 ORDER BY 절에 의해 결과집합을 정렬하기 전
에 먼저 IDENTITY 값을 할당하기도 함
특히 병렬 처리의 경우는 정렬 기준으로 순위 부여할 수 없음
버전이나 SP에 관계 없이 항상 정렬순으로 identity 값을 부여
하기 위해서는 하위 쿼리를 TOP 100 Percent 절과 함께 만들
어 사용할 수 있음
2005에선 새로운 RANK(), DENSE_RANK() 함수를 제
공함
제 16 회 SQL 고급과정세미나
IDENTITY() 함수

사용을 고려할 수 있는 곳




순위
누적 결과 집합 생성
이동 평균 구하기
비정상 Relation 구조에서 하나의 결과 집합 만들기
제 16 회 SQL 고급 과정 세미나
Q&A
감사합니다