이야기 마을

공부하시는분들 도움돼셧으면 합니다.

-- [0] 기본 게시판(BASIC)용 테이블 설계
-- [!] DROP Table dbo.Basic
CREATE TABLE dbo.Basic
(
    Num INT IDENTITY(1,1) NOT NULL PRIMARY KEY,        -- 번호
    [Name] VARCHAR(25) NOT NULL,            -- 이름
    Email VARCHAR(100) NULL,                -- 이메일
    Title VARCHAR(150) NOT NULL,            -- 제목
    PostDate DATETIME DEFAULT GETDATE() NOT NULL,    -- 작성일
    PostIP VARCHAR(15) NOT NULL,            -- 작성 IP
    Content TEXT NOT NULL,                -- 내용
    [Password] VARCHAR(20) NOT NULL,            -- 비밀번호
    ReadCount INT DEFAULT 0,                -- 조회수
    Encoding VARCHAR(10) NOT NULL,            -- 인코딩(HTML/TEXT)
    Homepage VARCHAR(100) NULL,            -- 홈페이지
    ModifyDate SMALLDATETIME NULL,            -- 수정일
    ModifyIP VARCHAR(15) NULL                -- 수정 IP
)
Go

-- [1] 입력 : Write.aspx
INSERT Basic
VALUES
(
    '씨앗', 'cid@seed.com', '새해 복', GETDATE(), '127.0.0.1', '새해 福 많이 받으세요', '2006', '0', 'TEXT', 'http://www.cid.com', GETDATE(), NULL
)
INSERT Basic
VALUES
(
    '거니', 'guni@seed.com', '새해 복받으3!', GETDATE(), '127.0.0.8', '새해 福 많이33=3 =3', '2006', '0', 'TEXT', '', GETDATE(), ''
)
INSERT Basic
VALUES
(
    '시드', 'seed@seed.com', '새해 복이요~', GETDATE(), '127.0.0.2', '아케마시떼 오메데토~', '2006', '0', 'TEXT', NULL, NULL, NULL
)
INSERT Basic
VALUES
(
    '쓰베', 'soo@seed.com', '복복복!!!', GETDATE(), '127.0.0.3', '2006년에도 달리삼...', '2006', '0', 'TEXT', NULL, NULL, NULL
)
INSERT Basic
VALUES
(
    '시드군', 'seed@seed.com', '새해 복이요~', GETDATE(), '127.0.0.2', '아케마시떼 오메데토~', '2006', '0', 'TEXT', NULL, NULL, NULL
)
INSERT Basic
VALUES
(
    '피스', 'peace@seed.com', '근하신년', GETDATE(), '127.0.0.9', '2006에도 와우!!!', '2006', '0', 'TEXT', '', '', ''
)

-- [2] 출력 : List.aspx
Select
    Num, Name, Email, Title, PostDate, ReadCount
From Basic --Join On
-- Where
-- Group By
-- Having
Order By Num Desc

-- [3] 상세 : View.aspx
Select *
From Basic
Where Num = 5

-- [4] 수정 : Modify.aspx
Begin Tran
    Update Basic
    Set
        Name = '씨앗',
        Email = 'cidsang@seed.com',
        Homepage = 'http://www.cidsang.com/',
        Title = 'Happy New Year',
        Content    = '<u>!!! Happy New Year !!!</u>',
        Encoding = 'HTML',
        ModifyDate = GetDate(),
        ModifyIP = '127.0.0.1'
    Where Num = 5
-- RollBack Tran
Commit Tran

-- [5] 삭제 : Delete.aspx
Begin Transaction
    Delete Basic
    Where Num = 5
-- RollBack Transaction
Commit Transaction

-- [6] 검색 : Seach.aspx
Select *
From Basic
Where
    Name Like '%씨%'
    Or
    Title Like '복%'
    Or
    Content Like '%福'

-- [7] 기본 게시판(Basic)에 글을 작성하는 저장 프로시저 : procWriteBasic
Create Proc dbo.procWriteBasic
    @Name VarChar(25),
    @Email VarChar(100),
    @Title VarChar(150),
    @PostIP VarChar(15),
    @Content Text,
    @Password VarChar(20),
    @Encoding VarChar(10),
    @Homepage VarChar(100)   
-- With Encryption
As
    Insert Basic
    (
        Name, Email, Title, PostIP, Content,
        Password, Encoding, Homepage
    )
    Values
    (
        @Name, @Email, @Title, @PostIP, @Content,
        @Password, @Encoding, @Homepage
    )
Go

-- [8] 기본 게시판(Basic)에서 데이터를 읽어오는 저장 프로시저 : procListBasic
Create Procedure dbo.procListBasic
As
    Select *
    From Basic
    Order By Num Desc
Go

-- [9] 조회수 증가시켜주는 저장 프로시저 : procUpdateReadCount
Create Proc dbo.procUpdateReadCountBasic
    @Num Int
As
    Update Basic
    Set ReadCount = ReadCount + 1
    Where Num = @Num
Go

-- [10] 해당 글을 세부적으로 읽어오는 저장 프로시저 : procViewBasic
Create Procedure dbo.procViewBasic
    @Num Int
As
    Select *
    From Basic
    Where Num = @Num
Go

-- [11] 해당 글에 대한 비밀번호 읽어오는 저장 프로시저 : procReadPassword
Create Proc dbo.procReadPasswordBasic
    @Num Int
As
    Select Password
    From Basic
    Where Num = @Num
Go

-- [12] 해당 글 지우는 저장 프로시저 : procDeleteBasic
Create Proc dbo.procDeleteBasic
    @Num Int
As
    Delete Basic Where Num = @Num
Go

-- [13] 해당 글을 수정하는 저장 프로시저 : procUpdateBasic
Create Proc dbo.procUpdateBasic
    @Name VarChar(25),
    @Email VarChar(100),
    @Title VarChar(150),
    @ModifyIP VarChar(15),
    @ModifyDate DateTime,
    @Content Text,
    @Encoding VarChar(10),
    @Homepage VarChar(100),
    @Num Int
As
    Update Basic
    Set
        Name = @Name,
        Email = @Email,
        Title = @Title,
        ModifyIP = @ModifyIP,
        ModifyDate = @ModifyDate,
        Content = @Content,
        Encoding = @Encoding,
        Homepage = @Homepage
    Where Num = @Num
Go

원래 이 용도야 아니겠지만 참 편하다.
프로젝트 뛰다보면 테이블 정의서를 만들때라든가..뭐 기타 등등
MSSQL 테이블에서 컬럼 명이 주우욱 필요할때 복사가 안되는 관계로 고민할때가 많았다
이 프로시져 로 쿼리분석기에서 테이블명을 치면 복사할수있도록 좌악 나온다..

이전부터 쓰던거지만..거참 프로시져 이름이 헬프다 보니 종종헷갈려서
자주 쓰지 않기에 까먹어버린다..-0-;;; 테이블 보여주는거니까 뷰였던가..쇼였던가....참..
쓸때마다 마스터테이블의 프로시져란을 검색하면서 찾아 쓰기가 귀찮아 올리는 중..-0-;;


sp_help "테이블명"

[페이징] 리스트 페이징(6가지) 100만개 테스트 | MS-SQL

다음은 데브피아 이원문 님의 글입니다.

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

제가 어느 페이징이 제일 빠를까.. 고민 하다가.. 테스트로 만들어 보았습니다.
총.. 6가지..입니다..

간단하게 쿼리를 설명해드리겠습니다.

1. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]

  1번 리스트는 이 쿼리 구문을 써서 했고요. 현제 개시물까지 이동을 Rs.Move(이동할수) 로 처리 했습니다.

2. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]    - 1번과 동일

  1번과 쿼리구문은 동이하고요. 레코드셋의 AbsolutePage를 이용해서 페이징을 했습니다.

3. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]

   WHERE [글번호필드] NOT IN (SELECT TOP [제거할 게시물수] [글번호필드] FROM [테이블명])
   예전에 태요 사이트에서 보았던 쿼리 구문입니다.. NOT IN 때문에.. 문제시 되었던 쿼리구문이죠.

4. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호] IN (SELECT TOP [페이지출력 갯수] [글번호] FROM
   (SELECT TOP [불러올 총 게시물수] [글번호] FROM [테이블 명]) AS A ORDER BY [글번호])
   ORDER BY [글번호] DESC

   이 쿼리 구문은 3번의 쿼리 구문의 문제점을 보완한 구문입니다. NOT IN 대신에 IN을 사용 했습니다.

5. SELECT TOP [페이지 출력갯수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호] <= (SELECT MIN([글번호])
   FROM (SELECT TOP [제거할 게시물수] + 1 [글번호] FROM [테이블명]) AS A)

   5번째 쿼리 구문은 IN, NOT IN이 아닌 출력할 마지막 글번호 바로 앞이 글번호를 찾아서 처리를 해주는
   쿼리 구문입니다.

6. SELECT TOP [페이지 출력갯수] [출력 필드명] FROM [테이블명]
   WHERE [글번호] <= (SELECT MIN([글번호])
   FROM (SELECT TOP [제거할 게시물수] + 1 [글번호]
   FROM [테이블명] WHERE [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호]) AS A
   WHERE [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호])
   AND [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호]

   6번재 쿼리는.. 글에 인덱스(가칭)라는 필드를 하나 더 추가 해서.. 글 기본 2000개마다 (가변적입니다)
   인덱스를 증가 시켰습니다. 즉 2000개를 하나의 묶음으로 만든것입니다.
   그 인덱스를 기준으로 처리를 해주었습니다.(기본 개념은 영어 사전 입니다 ㅡㅡ;)


위와 같이 간단한 기본 쿼리 구문을 설명? 했습니다.
게시물은 100만개를 넣고 테스트를 했습니다.
테스트 게시판을 보시려면.. 맨위에 각 게시판을 링크를 걸었습니다.
서버가 구려서.. 제대로 될지 모르지만..
제 노트북
(CPU : p4-1.8, RAM : 768Mb, 컴팩 프리자리오 2820AP, 환경 : 윈도우2003 MSSQL2000)
에서 테스트 해본 봐로는 처음페이지(1), 마지막 페이지(50000) 처리 시간이 아래와 같습니다.(단위 ms)

1 : 320    12289.06
2 : 273    11476.56
3 : 289    4406.25
4 : 289    2695.31
5 : 289    1218.75
6 : 7.81    23.44

엄청난 차이가 나죠??
기본적으로 글번호에 Clustered Index, 인덱스번호, 글깊이에 Non Clustered Index를 걸어 두었습니다.
엄청난 차이로 인해 테스트로 만든 저 또한 입이 쩍 벌어 집니다 ㅡㅡ;;