JKUN

Welcome To The Jkun.net

Blog

스키마 바인딩시 에러;;;

2012.10.31 14:19 DataBase/MS-SQL


스키마 바인딩할 수 없습니다. 이름은 두 부분으로 구성되어야 하며 개체는 자체 참조할 수 없습니다.


CREATE VIEW HAN_RECOM_POINT_GIVE_LIST

WITH SCHEMABINDING

AS

 셀렉트 어쩌고 저쩌고 하는데 위와 같은 오류가 발생한다. 디지게 짜증나서 한참을 삽질하다

 데이터베이스 사랑넷에서 관련 글이 있었다.

 테이블이나 오브젝트에 열심히 dbo. 을 붙여주라고 해서, 붙여봤더니 정상적으로 생성.


그런데 짜증나는건 이유를 모르겠다. 왜 dbo. 를 넣었는데 된건지;;;

그냥 그런가보다 숙지하기에는 기분이 별로다.


혹시 아시는 분들 지나가시다 보시면 답글좀 부탁드립니다.^^;

저작자 표시
신고

'DataBase > MS-SQL' 카테고리의 다른 글

OPENROWSET  (0) 2013.05.29
CHARINDEX 를 이용한 문자열 필터링  (0) 2013.05.29
스키마 바인딩시 에러;;;  (0) 2012.10.31
VIEW 인덱스 생성시 스키마바인딩  (0) 2012.10.31
실행된 쿼리내역 조회  (0) 2012.10.24
MS-SQL 임시테이블  (0) 2012.09.19
현재 0 개의 댓글이 있습니다.
Comment

Blog

VIEW 인덱스 생성시 스키마바인딩

2012.10.31 14:13 DataBase/MS-SQL


출처 : http://www.sqler.com/bSQLQA/bColumn/105239


안녕하세요. 비전팀 1기 강동운입니다.


이번에는 뷰를 생성할 때 WITH SCHEMABINDING 옵션에 대해서 얘기를 꺼내볼까 합니다.


BOL에 찾아보면...


SCHEMABINDING

기본 테이블의 스키마에 뷰를 바인딩합니다. SCHEMABINDING을 지정하면 뷰 정의에 영향을 미치는 방법으로 기본 테이블을 수정할 수 없습니다. 뷰 정의 자체를 먼저 수정하거나 삭제하여 수정할 테이블에 대해 종속성을 제거해야 합니다. SCHEMABINDING을 사용하는 경우 select_statement에 참조되는 테이블, 뷰 또는 사용자 정의 함수의 두 부분으로 구성된 이름(schema.object)이 있어야 합니다. 참조된 개체는 모두 같은 데이터베이스에 있어야 합니다.

SCHEMABINDING 절로 만든 뷰에서 사용하는 뷰 또는 테이블은 뷰가 삭제되거나 변경되어 스키마 바인딩이 더 이상 존재하지 않는 경우에만 삭제할 수 있습니다. 그렇지 않으면 데이터베이스 엔진에서 오류가 발생합니다. 또한 ALTER TABLE 문이 뷰 정의에 영향을 미치는 경우에는 스키마 바인딩이 있는 뷰에서 사용하는 테이블에서 이러한 문을 실행할 수 없습니다.

뷰에 별칭 데이터 형식 열이 있는 경우 SCHEMABINDING을 지정할 수 없습니다.



이 글만 읽게 되면... "아... 스키마 바인딩은.. 원래 테이블을 수정할 수 없게 하기 위해서 만드는 거구나" 라는 것만 생각 하게 됩니다.


그래서.. 뷰의 스키마 바인딩에 대해서 좀 더 자세히 알아보기 위해.. 이번 아티클을 작성하게 되었습니다.



뷰는 가상의 테이블입니다... 데이터가 존재하지 않는다고 생각합니다.

하지만 스키마바인딩 옵션으로 뷰에도 데이터를 가질 수 있습니다. 

즉.. 테이블과 뷰에 동시에 같은 데이터를 가질 수 있게 설정할 수 있다는 말입니다.


예제를 통해 보도록 하겠습니다.


일단 스키마 바인딩 되지 않는 뷰!


SET NOCOUNT ON

GO

IF OBJECT_ID('T1','U') IS NOT NULL

       DROP TABLE t1

GO

IF OBJECT_ID('VI_T1','V') IS NOT NULL

       DROP VIEW VI_T1

GO

CREATE TABLE t1

(

       idx                              int    identity

,      ClusteredValue             int    not null

,      ViewClusteredValue  int not null

)

GO

 

--//데이터1000건삽입

INSERT INTO t1(ClusteredValue, ViewClusteredValue)

SELECT CONVERT(INT,RAND()*10000), CONVERT(INT,RAND()*10000)

GO 1000

 

--//ClusteredValue에Clustered Index 생성

CREATE CLUSTERED INDEX CL_T1_ClusteredValue ON T1(ClusteredValue)

GO

 

CREATE VIEW VI_T1

AS

       SELECT * FROM t1

GO

SET NOCOUNT OFF

GO

 

SELECT * FROM t1

GO

1.png 

SELECT * FROM VI_T1

GO

2.png 

SELECT * FROM t1 WHERE ClusteredValue BETWEEN 100 AND 200

GO

3.png 

 

SELECT * FROM VI_T1 WHERE ClusteredValue BETWEEN 100 AND 200

GO

4.png 


4가지 쿼리에서 알 수 있듯이.. view를 조회하든.. table을 조회하든.. 실제 테이블은 t1 테이블을 참조해서 실행 계획을 만듭니다.


즉 .. 이 말은 스키마 바인딩이 안된 view는.. view에 데이터를 저장하는 것이 아니라.. 실제 테이블과 연결되는 하나의 인터페이스가 됩니다.


DBCC IND 명령어로.. 실제 테이블과 뷰에 데이터 페이지가 할당 되었는지 보도록 하겠습니다.


DBCC IND('tempdb','t1',0)

5.png

DBCC IND('tempdb','VI_T1',0)

Msg 5239, Level 16, State 1, Line 1

개체 ID 1755153298(개체 'VI_T1')을(를) 처리할 수 없습니다. 이 DBCC 명령이 이 유형의 개체를 지원하지 않습니다.


역시.. VIEW에는 데이터 페이지가 할당되지 않았습니다.

이제 스키마 바인딩으로 뷰를 생성해보도록 하겠습니다.

CREATE VIEW VI_SCHEMABINDING_T1

WITH SCHEMABINDING

AS

       SELECT * FROM t1

GO

Msg 1054, Level 15, State 6, Procedure VI_SCHEMABINDING_T1, Line 4

스키마 바운드 개체에는 구문 '*'을(를) 사용할 수 없습니다.


스키마 바인딩 된 뷰는 반드시 컬럼명을 명시해야 생성이 가능합니다.


CREATE VIEW VI_SCHEMABINDING_T1

WITH SCHEMABINDING

AS

       SELECT idx, ClusteredValue, ViewClusteredValue FROM t1

GO

Msg 4512, Level 16, State 3, Procedure VI_SCHEMABINDING_T1, Line 4

이름 't1'이(가) 스키마 바인딩에 적합하지 않으므로 뷰 'VI_SCHEMABINDING_T1'을(를) 스키마 바인딩할 수 없습니다. 이름은 두 부분으로 구성되어야 하며 개체는 자체 참조할 수 없습니다.


스키마바인딩 된 뷰를 사용할 때는.. 반드시 테이블에 소유자를 넣도록 해야되는군요 ^^

CREATE VIEW VI_SCHEMABINDING_T1

WITH SCHEMABINDING

AS

       SELECT idx, ClusteredValue, ViewClusteredValue FROM dbo.t1

GO


이제 제대로 생성이 되었습니다. 스키마가 바인딩이 되었으므로, 한번 t1의 idx라는 컬럼을 삭제해보도록 하겠습니다.

(스키마 바인딩이 되었으므로 삭제가 안되는 것이 맞습니다!)


ALTER TABLE t1

DROP COLUMN idx

Msg 5074, Level 16, State 1, Line 1

개체 'VI_SCHEMABINDING_T1'은(는) 열 'idx'에 종속되어 있습니다.

Msg 4922, Level 16, State 9, Line 1

하나 이상의 개체가 이 열에 액세스하므로 ALTER TABLE DROP COLUMN idx이(가) 실패했습니다.


역시.. 스키마 바인딩이 되어있으모로 예상이 맞았습니다.

이제 아까 제가 말씀드린 것 처럼.. 스키마 바인딩이 되었으므로 DBCC IND를 통해 뷰에도 데이터가 있는지 조사해보겠습니다.



DBCC IND('tempdb','t1',0)

5.png

DBCC IND('tempdb','VI_SCHEMABINDING_T1',0)

Msg 5239, Level 16, State 1, Line 1

개체 ID 1915153868(개체 'VI_SCHEMABINDING_T1')을(를) 처리할 수 없습니다. 이 DBCC 명령이 이 유형의 개체를 지원하지 않습니다.


헉?? 왜 데이터가 존재하지 않을까요??.. 아까 날렸던 SELECT 문장을 다시 수행해보도록 하겠습니다.


SELECT * FROM VI_SCHEMABINDING_T1

GO

6.png 

SELECT * FROM VI_SCHEMABINDING_T1 WHERE ClusteredValue BETWEEN 100 AND 200

GO

7.png 


스키마 바인딩이 되었음에도 불구하고.. 아까랑 별반 다를게 없습니다. 왜 이런것 일까요??

그럼 단지 스키마 바인딩은 컬럼을 삭제 못하게끔 하기 위해서 사용하는 것일까요?


혹시 공부하시다가.. 인덱싱된 뷰라고 들어보셨나요?? 바로... 스키마 바인딩된 뷰에는.. 인덱스를 만들 수 있습니다.

이제 뷰에 인덱스를 만들어 보도록 하겠습니다.

참고로.. 스키마 바인딩 된 뷰에는.. 유니크 클러스터드 인덱스를 먼저 만들어야.. 넌 클러스터드 인덱스를 만들 수 있습니다.


CREATE CLUSTERED INDEX CL_VI_SCHEMABINDING_T1_ViewClusteredValue ON VI_SCHEMABINDING_T1(ViewClusteredValue)

GO

Msg 1941, Level 16, State 1, Line 1

고유 클러스터형 인덱스 하나만 허용되므로 뷰 'VI_SCHEMABINDING_T1'에 비고유 클러스터형 인덱스를 만들 수 없습니다. 대신 고유 클러스터형 인덱스를 만드십시오.


유니크 클러스터형 인덱스를 만들라고 합니다.

그럼 그냥 넌클러스터드 인덱스를 만들면??


CREATE INDEX NC_VI_SCHEMABINDING_T1_ViewClusteredValue ON VI_SCHEMABINDING_T1(ViewClusteredValue)

GO


Msg 1940, Level 16, State 1, Line 1

뷰 'VI_SCHEMABINDING_T1'에 인덱스을(를) 만들 수 없습니다. 고유 클러스터형 인덱스가 없습니다.



따라서 유니크한 idx 컬럼에 만들어보도록 하겠습니다.



CREATE UNIQUE CLUSTERED INDEX NC_VI_SCHEMABINDING_T1_idx ON VI_SCHEMABINDING_T1(idx)

GO


이제 만들어진 인덱스에 page가 할당되었는지 확인해보겠습니다.

DBCC IND('tempdb','VI_SCHEMABINDING_T1',1)

8.png


뷰에 있는 인덱스에 페이지가 할당되었습니다!!


그렇다면... 아래 쿼리는 view에 생성한 Clustered Index를 탈까요?

SELECT IDX FROM VI_SCHEMABINDING_T1 WHERE idx between 100 and 200


9.png 

헉!! idx에 인덱스를 만들었는데도 불구하고.. 뷰가 아닌 t1 테이블에 Clustered Index scan(=full scan)을 합니다.

뷰에 있는 Clustered Index를 전혀 이용하지 못했다는 말입니다.


그렇다면.. 뷰에 있는 클러스터드 인덱스를 사용하려면 어떻게 해야 할까요?

바로 NOEXPAND 옵션입니다.



SELECT IDX FROM VI_SCHEMABINDING_T1 WITH(NOEXPAND) WHERE idx between 100 and 200

10.png  



이제 최종 뷰에 있는 Clustered Index Seek 한 것을 확인할 수 있었습니다. ^^


그렇다면.. t1 테이블에 데이터를 넣는다면??? 당근.. view에도 데이터가 들어가야하기 때문에.. I/O가 증가할 것입니다.


수정이 되는 경우도 마찬가지일테고요. 테스트는??? 한번 직접 해보십시요~~ㅎㅎㅎ



정리를 드리면..

WITH SCHEMABINDING 옵션은...

1. 반드시 컬럼명을 기술 해야 만들 수 있다.

2. 생성 된 뷰에 대한 컬럼은 삭제하거나 수정할 수 없다.

3. 테이블  명은 반드시 소유자 명을 지칭해야된다.

4. 인덱스를 만들면 page를 할당해서 데이터를 저장한다.

5. 인덱스가 없다면, 원래 뷰와 동일하게 작동한다.



인덱싱 뷰는..

1. WITH SCHEMABINDING 옵션으로 생성 된 VIEW에만 인덱스를 만들 수 있다.

1. 반드시 첫번째는 유니크한 클러스터드 인덱스를 만들어야 한다.

2. 뷰의 인덱스를 이용하기 위해서는 WITH(NOEXPAND)를 사용해야 한다.


참고로 NOEXPAND는 엔터프라이즈급에서는 인식한다고 합니다만... 힌트를 박아서 사용해야 확실하다고 합니다. ^^ (강산아님 댓글)


NOEXPAND 관련 민석님의 글: http://www.sqler.com/105239


감사합니다.

저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

MS-SQL 임시테이블

2012.09.19 17:46 DataBase/MS-SQL


포스팅을 안한지 오래되었네;; ㅎㅎ

그 동안 열심히 일하다 공부하다 까먹을까 중간중간 포스팅을 했는데,

요근래는 알수없는 귀차니즘..ㅠㅠ

정신차리잣!!


일단 오늘은 MS-SQL 에서 있는 임시테이블 기능이다.

데이터 가공시 조회결과를 일시적으로 저장해두었다, 다시 재활용 해야 하는 경우에 유용한 것 같다.

오늘도 즐겁게 써먹음. ㅋㅋㅋ


일단 출처는 큼지막하게~!


출처 : http://dualist.tistory.com/133


임시 테이블

지역 및 전역 임시 테이블을 만들 수 있습니다. 지역 임시 테이블은 현재 세션에서만 볼 수 있으며 전역 임시 테이블은 모든 세션에서 볼 수 있습니다.
지역 임시 테이블 이름 앞에는 숫자 기호가 하나 추가되고(예: #table_name) 전역 임시 테이블 이름 앞에는 숫자 기호가 두 개 추가됩니다(예: ##table_name).
SQL 문은 CREATE TABLE 문의 table_name에 대해 지정된 값을 사용하여 임시 테이블을 참조합니다.
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)

여러 사용자가 동시에 실행할 수 있는 응용 프로그램 또는 저장 프로시저에서 지역 임시 테이블이 만들어지는 경우 SQL Server는 다른 사용자가 만든 테이블을 구별할 수 있어야 합니다. SQL Server는 각 지역 임시 테이블 이름에 내부적으로 숫자 접미사를 추가하여 구별합니다. tempdb의 sysobjects 테이블에 저장된 것과 같은 임시 테이블의 전체 이름은 CREATE TABLE 문에서 지정된 테이블 이름과 시스템에서 생성한 숫자 접미사로 구성됩니다. 접미사를 추가해야 하므로 지역 임시 이름으로 지정된 table_name은 116자를 초과할 수 없습니다.
임시 파일은 DROP TABLE을 사용하여 명시적으로 삭제하지 않는 한, 범위를 벗어나는 경우 자동으로 삭제됩니다. 
 · 저장 프로시저에서 만들어진 지역 임시 테이블은 저장 프로시저가 완료될 때 자동으로 삭제됩니다. 테이블은 해당 테이블을 만든 저장 프로시저에 의해 실행되는 모든 중첩된 저장 프로시저에 의해 참조될 수 있습니다. 테이블은 테이블을 만든 저장 프로시저에 의해 호출된 프로세스에 의해서는 참조될 수 없습니다.
 · 기타 모든 지역 임시 테이블은 현재 세션이 끝날 때 자동으로 삭제됩니다.
 · 전역 임시 테이블은 테이블을 만든 세션이 끝나고 이를 참조하는 다른 모든 작업이 중지되면 자동으로 삭제됩니다. 작업과 테이블 간의 연결은 단일 Transact-SQL 문의 사용 기간 동안만 유지 관리됩니다. 즉, 전역 임시 테이블은 만들기 세션이 끝났을 때 테이블을 참조하는 마지막 Transact-SQL 문이 완료되면 삭제됩니다. 
저장 프로시저 또는 트리거에서 만들어진 지역 임시 테이블은 저장 프로시저 또는 트리거가 호출되기 전에 만들어진 같은 이름의 임시 테이블과 구별됩니다. 쿼리가 임시 테이블을 참조하고 같은 이름의 두 임시 테이블이 동시에 존재하면 쿼리가 확인될 테이블이 정의되지 않습니다. 중첩된 저장 프로시저 또한 이를 호출한 저장 프로시저에 의해 만들어진 임시 테이블과 같은 이름의 임시 테이블을 만들 수 있습니다. 중첩된 저장 프로시저 내의 테이블 이름에 대한 모든 참조는 중첩된 프로시저에서 만들어진 테이블에 대해 확인됩니다. 예를 들어, 다음과 같습니다.
CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1

GO





안녕하세요.

우선 테스트는 MS-SQL에서 했습니다.
MS-SQL 같은 경우는 지역임시테이블일 경우에는 #,
전역임시테이블일 경우에는 ## 을 붙여 임시테이블로 표현합니다.
Informix에서의 임시테이블 표현은 제가 잘 몰라서리... ^^

#임시테이블 표현만 변경하시면 될 것 같습니다.
그리고 맨 밑에 보시면 DROP을 하는데,
어차피 세션 끊기면 사라집니다.

표현 바꾸고 실행해 보시면 두 임시테이블 모두 조회되는것을
보실 수 있을 것입니다.


CREATE TABLE     #임시테이블

        아이디           CHAR(20)     NOT NULL
    ,   이름             VARCHAR(12) NOT NULL
    ,   주민등록번호     CHAR(13)     NOT NULL
    ,       CHECK
            (
                    주민등록번호     >= '0000000000000'
            AND     주민등록번호     <= '9999999999999'
            )
)

CREATE TABLE     #임시테이블_2

        아이디           CHAR(20)     NOT NULL
    ,   이름             VARCHAR(12) NOT NULL
    ,   주민등록번호     CHAR(13)     NOT NULL
    ,       CHECK
            (
                    주민등록번호     >= '0000000000000'
            AND     주민등록번호     <= '9999999999999'
            )
)

INSERT #임시테이블
(
        아이디
    ,   이름
    ,   주민등록번호

VALUES
(
        'MRHONG'
    ,   '홍길동'
    ,   '0200421567801'
)

SELECT   *
FROM     #임시테이블

INSERT   #임시테이블_2
(
        아이디
    ,   이름
    ,   주민등록번호
)
(
SELECT   A.*
FROM     #임시테이블 A
    ,   MU100 B
WHERE   A.아이디 = B.아이디
)


SELECT   *
FROM     #임시테이블_2

DROP TABLE   #임시테이블
DROP TABLE   #임시테이블_2

님의 질문에 맞는 대답인지는 잘 모르겠습니다만... ^^
어쨌든 도움이 되셨기를 바랍니다.

그럼 즐프하세요.


저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

연결된 서버(Linked Server) 를 이용한 조건별 다수 업데이트

2012.07.02 09:19 DataBase/MS-SQL


졸다가 업데이트를 조건을 안걸고 F5 키를 눌러버리고 말았다..

그래서 다행히도 관계형이 아닌 테이블이어서 원본 테이블에는 지장이 없는 테이블이었기에 (운좋았다~ㅋㅋ)

조인을 걸어서 수정을 할 수 있게 했는데 까먹으니까 써놓자~

 

  UPDATE MemberInfor

  SET MemberInfor.member_id = b.member_id

  FROM

  -- 실제 조인 연동 (링크드 서버와)

   MemberInfor a INNER JOIN (

    SELECT * FROM OPENQUERY(LinkedServerName, '

       SELECT member_id, member_name FROM TestDB.dbo.MemberInfor a, TestDB.dbo.MemberInfor b

       WHERE a.member_id = b.member_id

    ')) b

    ON  a.member_id = b.member_id

    AND a.server_id = b.server_id

  WHERE a.server_id = 24

  AND a.server_id NOT IN ('51','52','54','61','62','63','67','68','69','70')

 

반드시 링크드 서버가 활성화(연결)되어있어야만 구동이 가능하다.

저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

경고: 집계 또는 다른 SET 작업에 의해 Null 값이 제거되었습니다.

2012.05.18 09:06 DataBase/MS-SQL


MS-SQL 에서 GROUP BY 등으로 집계연산시에 NULL 값이 있으면,

CASE 절로 예외처리 해줘도 


경고: 집계 또는 다른 SET 작업에 의해 Null 값이 제거되었습니다. 


와 같은 메세지가 나타난다. 이 메세지가 나타날때는 웹페이지에서 데이터로드시에는

오류가 발생하기도 한다.


이에 SELECT 절 실행전에 아래 키워드를 넣어준다.


SET ANSI_WARNINGS


MS-SQL 도움말 참조.


SET ANSI_WARNINGS는 다음 조건에 적용됩니다.

  • ON으로 설정한 경우 SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, COUNT 등의 집계 함수에 NULL 값이 있으면 경고 메시지가 생성됩니다. OFF로 설정한 경우에는 경고가 발생하지 않습니다.

  • ON으로 설정한 경우 0으로 나누기 및 산술 오버플로 오류가 발생하면 문이 롤백되고 오류 메시지가 생성됩니다. OFF로 설정한 경우 0으로 나누기 및 산술 오버플로 오류가 발생하면 NULL 값이 반환됩니다. 새 값의 길이가 열의 최대 크기를 초과하는 character, Unicode 또는 binary 열에 INSERT나 UPDATE 작업을 하려고 하면 0으로 나누기 또는 산술 오버플로 오류로 인해 NULL 값이 반환될 수 있습니다. SET ANSI_WARNINGS 옵션이 ON이면 ISO 표준에 지정된 대로 INSERT나 UPDATE가 취소됩니다. 문자 열에 대해서는 후행 공백이, 이진 열에 대해서는 후행 NULL 값이 무시됩니다. 이 옵션이 OFF이면 열의 크기에 맞게 데이터가 잘리고 문이 성공적으로 실행됩니다.

    참고:
    binary 또는 varbinary 데이터로 또는 그 역으로의 변환에서 잘림이 일어날 때는 SET 옵션에 상관없이 경고나 오류가 생성되지 않습니다.

    참고:
    저장 프로시저 또는 사용자 정의 함수에 매개 변수를 전달할 때 또는 일괄 처리 문에서 변수를 선언하고 설정할 때 ANSI_WARNINGS는 인식되지 않습니다. 예를 들어 변수가 char(3)로 정의된 경우 3자보다 큰 값으로 설정하면 정의된 크기로 데이터가 잘리고 INSERT 또는 UPDATE 문이 성공합니다.

sp_configure의 user options를 사용하여 서버의 모든 연결에 대해 ANSI_WARNINGS의 기본값을 설정할 수 있습니다. 자세한 내용은 sp_configure(Transact-SQL) 또는 서버 구성 옵션 설정을 참조하십시오.

계산 열이나 인덱싱된 뷰에서 인덱스를 만들거나 조작할 때는 SET ANSI_WARNINGS 옵션을 ON으로 설정해야 합니다. SET ANSI_WARNINGS 옵션이 OFF이면 인덱싱된 뷰나 계산 열에 인덱스가 있는 테이블에서 CREATE, UPDATE, INSERT, DELETE 문이 실패합니다. 인덱싱된 뷰 및 계산 열의 인덱스에 사용되는 필수 SET 옵션 설정 방법은 SET(Transact-SQL)을 참조하십시오.

SQL Server에는 ANSI_WARNINGS 데이터베이스 옵션이 포함되어 있습니다. 이 옵션은 SET ANSI_WARNINGS와 같습니다. SET ANSI_WARNINGS 옵션이 ON이면, 0으로 나누기나 데이터베이스 열에 대해 너무 큰 문자열에서 오류나 경고가 발생하고, 기타 유사한 오류가 발생합니다. SET ANSI_WARNINGS 옵션이 OFF이면 이러한 오류나 경고가 발생하지 않습니다. model 데이터베이스에서 SET ANSI_WARNINGS의 기본값은 OFF입니다. 이 옵션을 지정하지 않으면 ANSI_WARNINGS 설정이 적용됩니다. SET ANSI_WARNINGS를 OFF로 설정하면 SQL Server가 sys.databases 카탈로그 뷰에 있는 is_ansi_warnings_on 열의 값을 사용합니다. 자세한 내용은 데이터베이스 옵션 설정을 참조하십시오.

분산 쿼리를 실행할 때는 ANSI_WARNINGS를 ON으로 설정해야 합니다.

SQL Server Native Client ODBC 드라이버와 SQL Server용 SQL Server Native Client OLE DB 공급자는 연결될 때 자동으로 ANSI_WARNINGS를 ON으로 설정합니다. ODBC 데이터 원본과 ODBC 연결 특성 또는 SQL Server에 연결하기 전에 응용 프로그램에 설정된 OLE DB 연결 속성에서 이 옵션을 구성할 수 있습니다. DB-Library 응용 프로그램으로부터 연결할 때 SET ANSI_WARNINGS의 기본값은 OFF입니다.

SET ANSI_DEFAULTS 옵션이 ON이면 SET ANSI_WARNINGS 옵션이 설정됩니다.

SET ANSI_WARNINGS 옵션은 실행 시 또는 런타임에 설정되며 구문 분석 시에는 설정되지 않습니다.

SET ARITHABORT 옵션이나 SET ARITHIGNORE 옵션 중 하나가 OFF이고 SET ANSI_WARNINGS 옵션이 ON이면 SQL Server에서 0으로 나누기 또는 오버플로 오류가 발생할 경우 여전히 오류 메시지를 반환합니다.

저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

MS-SQL 2008 에서 IDENTITY 경고 풀기

2012.03.06 13:07 DataBase/MS-SQL



SQL Server 2008 에서 IDENTITY 를 적용된 사항을 풀거나, 테이블의 옵션을 변경하는 경우
경고가 나타나며 안된다고 한다.
그럼 옵션에 위 항목을 확인해 보자. 
저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

FreeSQLFormatter [SQL정렬]

2012.02.13 13:35 DataBase




보통 쿼리 (SQL스크립트) 를 짜거나, 이미 작성되있는 쿼리가 정렬이 안되어있으면
상당히 짜증이 치민다.
그런데 Toad For 씨리즈들은 라이센스가... 회사에서 사용하기에는....
그래서 불편함을 참다가.. 우연이 찾았다.
FreeSQLFormatter .
SQL 스크립트를 옵션에 맞게 정렬해주는 심플한 유틸리티.
고마운 소스포지~ㅋㅋㅋ
아래압축파일을 받아서 해당 OS 에 맞는 버전으로 압축을 해제하고 실행하면 된다.



해당 다운로드 링크 출처는  http://sourceforge.net/projects/fsqlf/ 

 
저작자 표시
신고

'DataBase' 카테고리의 다른 글

[ER-WIN] Names Option  (0) 2016.10.25
memsql  (0) 2013.12.30
FreeSQLFormatter [SQL정렬]  (0) 2012.02.13
NoSQL  (0) 2011.01.14
ERStudio  (0) 2010.08.24
현재 0 개의 댓글이 있습니다.
Comment

Blog

MS-SQL 페이징 기법 종류

2012.02.09 13:36 DataBase/MS-SQL


출처 :  http://skymong9.egloos.com/1841645 

ORCLE이 정말 좋은 RDB라는 것을 알게 하는 것이 바로 페이징 기법일거라 생각되네요.
MySQL도 내부적으로 페이징이 가능한 쿼리를 지원해주지만 MSSQL은 최근 2005버전까지도... 좋은 페이징 기법을 소개하지 못하고 있는 것 같네요. 성능 좋은 페이징 기법이 공개된 것이 있기도 하지만... 초심자에게 쉽지 않은 쿼리들인 것 같습니다.
이에 조금 쉽게... 어떻게 하면 성능이 향상되는지를 설명해 보려고 합니다.

아래의 내용은 온라인상에서 바로 작성하는 내용이고 맞춤법등이 틀릴 수 있기 때문에 copy해서 사용하지 마시기 바랍니다.

개념을 잡는 정도로 활용하시면 좋을 것 같습니다.

MS SQL의 페이징 기법의 키는 TOP 키워드입니다.

#1. TOP과  클러스터드 인덱스

SELECT TOP 10 ID, subject, contents FROM TBL

이 쿼리는 누구나 알고 있는 쿼리입니다. 여기서 중요한 것이 TOP 10 입니다.  상위 10개만 갖고 오겠다는 뜻입니다.MSSQL은 내용저장을 클러스터드 인덱스 순으로 저장을 하게 됩니다. 만약 ID를 PK로 지정하셨거나 따로 클러스터드 인덱스로써 ID를 지정해 놓으셨다면 ID순으로 자동으로 정렬이 되어 상위 10개만 가져오게 됩니다.

기본키는 반드시 설정할 필요는 없습니다. 다만 레코드를 구분하는 아이디값을 대게의 경우 기본키로 놓게 되는데요.

사실 기본키가 성능에 있어서의 의미는 없다고 봅니다.(확인된 바 없음)

기본키는 자동으로 인덱스 컬럼이 된다는게 의미가 있겠죠. 그것도 테이블당 딱 하나 사용할 수 있는 클러스터드 인덱스로 자동 설정됩니다. 헌데 문제는 ASC로 설정된다는 것입니다.

웹페이지에서 최근 글의 경우는 대부분 DESC로 정렬합니다. 즉 최근 글을 먼저 표시해주지요. PK로 만들더라도 꼭 ID를 DESC로 인덱스하도록 만들어야 합니다.

바로 이 유일 인덱스 컬럼이 성능을 좌우하는 핵입니다.

초심자의 경우 인덱스의 중요성을 넘기는 경우가 많은데요. PK가 중요한 것이 아니라 정렬을 원하는 컬럼을 클러스터드 인덱스로 만들어 놓는 것이 성능에 가장 중요합니다.(레코드 수가 늘 수록 엄청난 성능 향상이 있습니다.)

팁) TOP 10 PERCENT라는 키워드도 가능합니다. 말 그대로 전체의 10%만 가져온다는 것입니다. 쓸모가 많은 팁이라 생각되네요.


#2. 가장 많이 쓰이는 페이징 쿼리

SELECT TOP 10 ID, subject, contents FROM TBL where ID not in (SELECT TOP 현재페이진 이전까지의 모든 게시물 수 ID FROM TBL order by ID DESC) order by DESC

참 좋은 쿼리입니다. MS SQL에서 나올 수 있는 가장 간결하고 좋은 쿼리가 아닌가 싶습니다.

저역시 작은 규모의 게시판 종류는 무조건 이 쿼리를 이용합니다. 유지보수가 편하기 때문입니다. 누구나 쉽게 알아 볼 수 있기 때문에 제가 도저히 못 봐줄 유지보수 프로젝트라면 다른 사람이 대신할 수 있는 쿼리이기 때문에... 이 쿼리를 즐겨사용합니다.

다만 이 쿼리에는 조건이 하나 붙습니다.

사용자들이 100페이징 이하(게시물 수로 2000개 이하정도)의 글을 되도록 조회한다.

이 쿼리는 2만건 이하의 테이블에 적당하다고 생각됩니다.

1페이지라고 하면 성능이 최고가 되며
2페이지라면 not in 안의 쿼리문에 의해 한페이지가 10개의 글이라고 가정하면 10개를 일단 불러 들이게 됩니다. 제거를 위해서죠.
3페이지라면 20개를 불러들여서 제거를 해야 하겠고
4페이지라면 30개를...

그럼 100페이지라면 990개의 글을 읽어 들여야 겠군요. 990개를 먼저 불러 들이는 것이 문제입니다.

가장 심플한 쿼리이지만 DB에 어느정도의 부하를 주는 쿼리라는 것을 알 수 있습니다.

개인적으로 레코드셋종류의 객체에 내용을 불러오는 쿼리는 0.1초 이전에 끝나야 한다고 생각됩니다. 물론 검색이 들어가면 이야기가 달라지지만 기본적인 형태(아무런 검색이 없는 경우)에서는 0.1초 이하에서 OPEN을 끝내는 것이 좋다고 생각되네요.

 

#3. ORDER BY문

아시다시피 ORDER BY문은 정렬의 조건입니다. 위 쿼리의 경우 ORDER BY ID DESC이니깐 ID에 대해 내림차순으로 정렬하겠네요. 사실 ORDER BY 문은 안써주는 것이 성능에 가장 좋습니다.

만약 ID를 클러스터드 인덱스로 지정해 놨다면 안써도 될 것 같습니다. 클러스터드 인덱스를 내림차순으로 지정해 놨다면 ORDER BY ID DESC는 성능에 전혀 영향을 미치지 않습니다. 가끔 MSSQL에서 서브쿼리를 썼을 경우 원하는 값을 리턴하지 않습니다. 이유는 모르겠습니다. 서브쿼리문을 가져오는 방법에서 더 빨리 가져오려고 하는 그 부분에서 나오는 문제일거라 생각되네요.

클러스터드 인덱스로 지정했을 경우 ORDER BY ID DESC는 되도록이면 넣으시는게 좋습니다. 정확성을 위해서!;


#4. 성능저하 요소 SELECT COUNT(*) FROM TBL

NOT IN 쿼리보다 심플한 페이징 쿼리는 MSSQL에서 없는 것 같습니다. 개발자 입장에서 프로그래밍하기도 정말 쉬운 쿼리입니다. 현재 페이지 번호만 넘겨주면 모든 것이 가능합니다. 가장 큰 장점이 바로 개발하기 편하다는 것이 겠고, 사용자의 웹접근 액션과도 상당히 잘 맞아 떨어집니다. 사람들은 10페이지 이상은 잘 보지 않으려는 경향이 있기 때문입니다.

NOT IN 쿼리는 생각보다 좋은 쿼리라는 것을 일단 말씀 드립니다. 단 조건은 INDEX를 잘 설정해줬다는 조건이 붙습니다.


게시판 등을 개발할 때 전체 게시물 수를 구해오는데 가장 많이 쓰는 쿼리문이 SELECT COUNT(*) FROM TBL입니다. 페이지 바로 가기 버튼을 위해서도 필요하고 게시물 번호를 붙히는 데도 쓰이기 때문에 쓰는 경우가 많습니다.

헌데 이 쿼리문 속도가 상당히 늦습니다. 100만건을 조회할 경우 1초가 넘어가 버리는 무식한 쿼리입니다. 야야~ 그럼 IDEX행을 가져와봐... 라고 하실 것 같습니다. SELECT COUNT(ID) FROM TBL ... 허나 애석하게 속도가 똑같습니다.

그리고 COUNT함수는 가능하면 (*)를 사용하시기 바랍니다. 이것이 정확한 방법입니다. COUNT(*)에서 속도향상을 위한 방법은 솔직히 말씀드려서 없습니다.

가장 좋은 방법은 테이블 하나를 만들어서 데이타 입력/삭제시마다 업데이트 하면서 게시물 수를 저장해 놓는 방법입니다. MSSQL 내에서는 트리거라는 기능을 제공합니다. INPUT/DELETE 시 UPDATE TBL_SETTING set TBL_COUNT = xxx 뭐 이런 식으로 짜 놓으면 되겠죠.

전체 게시물 수를 가져올 때는 되도록이면 트리거를 쓰거나 프로그램을 통해 정보를 저장하는 테이블에 업데이트하고 이 자료를 페이징시 가져오는 것이 성능을 위한 좋은 방법입니다.

1만건 이하라면 COUNT(*)를 쓰던 NOT IN을 쓰던 별 지장이 없다는 게 제 생각입니다. 고로, 자꾸 써먹어도 좋은 쿼리 들임을 일단 알려드립니다.


#5 성능향상에 가장 좋은 방법

1000만건을 테스트 해보지는 않았지만, 1페이지와 100만 페이지가 같은(해보진 않았죠^^)-혹은 비슷한- 성능을 보이는 쿼리는 다름 아닌 NOT IN보다 훨씬 더 간결한 쿼리입니다.

SELECT TOP 11 ID, subject, contents FROM TBL where ID <= 현재페이지 최상위 ID (order by ID DESC)

이 쿼리에서 가장 중요한 것은 ID가 클러스터드 인덱스로 ID를 설정하고 내림차순으로 지정되어 있어야 한다는 것입니다.

아무리 많은 글들도 0.1초안에 해결될만한 가장 성능이 좋은 쿼리문입니다. 동접자수 엄청나고 글 수가 많다면 이방법 이외의 방법은 사용하지 마시기 바랍니다.

허나 장점이 있으면 단점도 있습니다. 이 쿼리를 사용할 경우 ID값에 바로 접근하긴하기 때문에

이전 1 2 3 4 5 6 7 8 9 10 다음 <- 이와 같은 구성이 불가능하다는 것입니다.

그래서 TOP 10이 아닌 11을 사용한 것입니다. 1부터 10까지 구성은 힘들지만 다음페이지 버튼은 가능하기 때문입니다. 갯수가 11개면 다음 페이지가 있다는 이야기가 되며, 다음페이지의 최상위 ID값도 가져올 수 있게 됩니다.

어쨌거나 위 쿼리는 성능에 있어서는 더이상 좋을 수 없는 쿼리입니다. 분명 글이 많고 사용자 액션이 단순한 사이트라면 반드시 고려해보셔야 할 쿼리입니다. 이전 페이지 구현은 프로그램적으로 머리를 좀 굴려야 할 부분이긴 합니다.

어짜피 다음 페이지 버튼을 눌러야만 가능하니깐 이전 페이지의 ID값은 무조건 가지고 갈 수 있습니다. POST방식으로 이전 페이지 정보를 계속 넘기는 방법도 괜찮은 방법일 것입니다.


SELECT TOP 10 ID FROM TBL where ID > 현재페이지 최상위 ID order by ID ASC 를 어쩔 수 없이 쓰는 것도 한 방법이겠구요. 그래도 다른 쿼리들보다는 빠른 방식이니깐요. <- 이건 저도 테스트 해보진 않았습니다.


#6. 사용자 편의성도 좀 생각해 보자

대부분 웹사이트에서는 NOT IN쿼리가 좋은 방법입니다. 사용자 편의성에 있어서 좋은 선택이니깐요.

이전 1 2 3 4 5 6 7 8 9 10 다음 <- 이게 얼마나 편한 방법입니까^^; 그리고 사람들은 10페이지 이상 조회를 거의 하지 않기도 합니다.

성능을 생각한다면 #5번의 방법이 정말 좋은 방법이지요. 다만 사용자 편의를 위한 인터페이스 구현은 사실상 불가능 합니다. 이전 페이지 구현도 쉽지 않죠.

목표가 생겼습니다.

이전 1 2 3 4 5 6 7 8 9 10 다음

이 기능을 한번 구현해 보죠. 약간 성능 저하가 있더라도... 전체 NOT IN보다는 훨씬 더 빠르게 한다는 목표를 가지고...

일단 쿼리를 하나 보죠.

SELECT TOP pageSize*10+1 ID from TBL where ID <= 1페이지 11페이지 21페이지 등 각 1페이지의 처음 ID

이 쿼리는 페이지 바로가기 버튼 구현을 위한 쿼리입니다.

페이지 사이즈가 10개라고 하면 101개를 가져옵니다. 101개면 너무 많지 않냐 하겠지만 적은 갯수입니다.^^

컬럼이 하나밖에 없기 때문에 속도 저하가 거의 없는 쿼리죠.

이 결과를 가지고 1 2 3 4 5 6 7 8 9 10 다음 버튼 구현이 가능합니다. 이전 버튼 구현은 역시 약간 복잡하죠?

PageInfo = rs.getrows() 등의 좋은 메소드 등을 통해 배열로 만든 후 이 기능을 구현하는 것이 가장 좋은 방법일거라 생각됩니다.


SELECT TOP 10 ID, subject, contents from TBL where ID not in(SELECT TOP pageSize*(현재페이지의한자리숫자-1 / 0일때는 10) ID from TBL  where ID <= 페이지 11페이지 21페이지 등 각 1페이지의 처음 ID order by ID DESC)  and ID <= 페이지 11페이지 21페이지 등 각 1페이지의 처음 ID order by ID DESC


이 쿼리는 실제로 글을 뿌려주는 쿼리입니다. NOT IN이 쓰였네요. 하지만 10페이지 단위로 끊어서 10페이지 글 내에서 NOT IN을 사용하기 때문에 항상 빠른 속도를 내줄 수 있는 쿼리입니다.

페이지당 글 수가 아무리 많아도 100개 이하가 대부분이기 때문에 10페이지 단위로 끊는다고 해도 1000개의 글 내에서 모든 작업이 이루어지기 때문에 성능 저하는 거의(아예) 없다고 보시면 됩니다. 1000개정도 레코드는 아무것도 아니지요.


다만 걸리는 것은 페이지 바로 가기 버튼을 구현하기 위해 비슷한 쿼리를 두번 날렸기 때문에 두배의 비용이 든다는 거겠지요.


#7. 검색에 대한 이야기

검색 속도를 위한 가장 좋은 방법은 고가의 검색 엔진을 사용하는 것입니다. 검색엔진에 DB를 설정하고 URL 저장 방법만 설정해 놓고 스케쥴링만 해 놓으면 검색엔진은 알아서 DB를 검색하고 고쳐진 값에 대해서 URL 링크를 인덱싱해 놓습니다.

속도도 빠르고 한글의 경우 형태소 분석기를 통해 별에 별 검색도 가능하며 서비스 측면에서는 하일라이트 생성기등을 통해 사용자 편의성을 제공해주고... 뭐... 문제는 쩐이군요^^;

MSSQL에서 우리는 like '%xxx%' 검색을 많이 활용하게 됩니다.

SELECT ID, subject, contents from TBL where subject like '%xxx%'

이 쿼리는 xxx를 가지는 모든 subject 컬럼에 대해 검색을 하게 됩니다. 그럼 어떻게 해야 할까요. 넵~ 인덱스를 설정해야 합니다. subject에 대해서 넌클러스터드 인덱스를 설정해 주어야 합니다. 그러면 엄청난 속도 향상을 느낄 수 있습니다.

subject 는 varchar(255)형입니다. 그렇기 때문에 인덱스 설정이 가능합니다.

그럼 contents like '%xxx%'는?

헌데 contents가 text형이거나 varchar(max) (이건 2005에서 지원)라면?...

불행히도 인덱스를 줄수가 없습니다. MSSQL은 그다지 많은 인덱스 공간을 지원해주지 않습니다.

이경우 가능하면 input쪽에 varchar(4000)정도 만큼만 글을 입력하도록 제한하거나 varchar(4000)을 몇개 더 만들어서 DB저장시 나누어서 넣어주는게 좋습니다.

편법이고 지저분한 방법입니다.^^; 그래도 뭐 속도 향상이 있다면야... 모두들 어쩔 수 없이 text형도 검색을 하지만, 좋은 방법이 아닙니다. 글이 1만개만 넘어가다 상당한 부하가 걸릴 거라 생각됩니다.

넌클러스터드 인덱스만이라도 걸려 있다면, 성능은 무척 좋아집니다.

하지만 너무 방법이 지저분 하네요.

더좋은 방법이 있겠죠? MSSQL은 이런 경우를 위해 풀텍스트검색서비스를 지원합니다. varchar(MAX)나 text형 등을 시중의 검색엔진과 비슷한 방식으로 인덱싱하고 검색할 수 있는 방법은 제공합니다. 속도가 그렇게 좋지는 못합니다만...  형태소 분석기도 들어 있고 유사어 검색도 가능하고 정확도 정렬도 가능합니다. 다만 문제는 속도가 생각처럼 나오지는 않는 다는 것입니다. 특히 정확도 정렬을 위한 정렬의 경우 100만건 이상으로 테스트시 1초가 넘어가더군요.

그리고 MSSQL 2000에서 한글은 제대로 지원되지 않았습니다. 2005에서 한글 지원이 되는데... 실제로 띄어쓰기를 하지 않아도 검색이 되는 정도를 확인하였습니다. 동의어 검색도 가능하다고 하는데... 저역시 많은 테스트가 필요할 것 같습니다.

2000에서는 전혀 안쓰는 기능이었지만, 2005에서는 충분히 활용가치가 있을 것 같네요. 다만 호스팅 업체에서 이 기능을 지원하지 않는 다는 것이 가장 큰 문제입니다.^^; 인덱싱 속도는 꽤 빠르나 하드디스크를 많이 차지하기 때문에, 그리고 인덱싱시 꽤 부하를 주기 때문에 지원하지 않는 것 같으며, 2000의 경우 한글 인덱싱 자체가 잘 안되기 때문에 지원을 하지 않는 것 같습니다.

다만 MSSQL 2005라면 프로젝트에 충분히 쓸 수 있을 것도 같습니다. 제가 이번에 활용해보도록 하겠습니다.

상용검색엔진에 비하면 못하겠지만 어느정도 흉내를 내주고 CONTAINS(TABLE)/ FREETEXT(TABLE)등의 4개의 함수를 통해 SQL쿼리문 내에서 사용하기 때문에 개발하기가 수월합니다.

풀텍스트 검색엔진에 대한 이야기는 또 다음에 계속 하도록 하겠습니다.



정리;
속도 향상을 위해 가장 중요한 것은 바로 INDEX 설정!;

저작자 표시
신고
현재 1 개의 댓글이 있습니다.

2012.02.12 00:34

비밀댓글입니다


Comment

Blog

MS-SQL 페이징 쿼리 종류별 성능 비교

2012.02.09 13:34 DataBase/MS-SQL


출처 :  http://www.mkexdev.net/Article/Content.aspx?parentCategoryID=2&categoryID=24&ID=304 

mkex.pe.kr 에 '허동석' 님이 작성해 주신 글을 옮겨 옵니다


페이징 쿼리 종류별로 성능을 비교한 좋은 자료가 있어서 상당부분 인용했습니다.

"ex)"에 들어가는 샘플 쿼리는 바투 락커룸 DB에 파일첨부(TB_AttachFile) 테이블을 대상으로 페이징 쿼리를 작성해 봤습니다.

5번에 표시된 내용이 데이터 건수에 상관없이 실질적으로 가장 빠르지만 우리 시스템에 적용할 수 있는지는 의문이 갑니다. 테이블 별로 인덱스 생성이 필요할 수 있고 중간에 데이터가 삭제되거나 어떤 반응이 생길 때 문제가 될 소지가 있는지도 조사해야 할 것 같습니다.

참고로 웹젠 빌링에서 사용하던 페이징 쿼리는 4번을 이용했었습니다.
그리고 2번에 샘플 쿼리를 보면 TB_AttachFile 테이블에 FileGuid가 None Clustered Index로 걸려 있어서 서브쿼리 내에서도 ORDER BY FileGuid DESC를 해줘야합니다.(즉 ORDER BY 를 2번 해야한다는..)
대부분의 기본키들이 None Clustered Index 라 이 점에 대한 이슈도 있습니다.

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

  레코드셋의 AbsolutePage를 이용해서 페이징을 했습니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 



2. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호필드] NOT IN (SELECT TOP [제거할 게시물수] [글번호필드] FROM [테이블명])

   예전에 태요 사이트에서 보았던 쿼리 구문입니다.. NOT IN 때문에.. 문제시 되었던 쿼리구문이죠.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid NOT IN 
(
 SELECT TOP 0 FileGuid 
 FROM TB_AttachFile
 ORDER BY FileGuid DESC

ORDER BY FileGuid DESC 


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

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

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid IN 
(
 SELECT TOP 123 FileGuid -- 총 데이터수 - ( (페이지수 - 1) * 10) // 1페이지 : 123 - 0, 2페이지 : 123 - 10 ...
 FROM 
 (
  SELECT FileGuid 
  FROM TB_AttachFile 
 )AS A 
 ORDER BY FileGuid

ORDER BY FileGuid DESC 


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

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

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid <=
(
 SELECT MIN(FileGuid)
 FROM 
 (
  SELECT TOP 11 FileGuid -- (현재페이지수-1) * 10 + 1  
  FROM TB_AttachFile
  ORDER BY FileGuid DESC
 )AS A 

ORDER BY FileGuid DESC 


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

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

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid <=
(
 SELECT MIN(FileGuid)
 FROM 
 (
  SELECT TOP 11 FileGuid -- (현재페이지수-1) * 10 + 1  
  FROM TB_AttachFile
  WHERE IDX_FileGuid > 350  -- 350은 이전 페이지의 끝 게시물 번호. (350보다 큰 10개를 얻음.)
  ORDER BY FileGuid DESC
 )AS A 

ORDER BY FileGuid DESC 


결과.

게시물은 100만개를 넣고 테스트를 했습니다
서버정보 : CPU : p4-1.8, RAM : 768Mb, 컴팩 프리자리오 2820AP, 환경 : 윈도우2003 MSSQL2000
처음페이지(1), 마지막 페이지(50000) 처리 시간이 아래와 같습니다.(단위 ms) 
 첫페이지 실행    끝페이지 실행
1 :  273                 11476.56
2 :  289                 4406.25
3 :  289                 2695.31
4 :  289                 1218.75
5 :  7.81                23.44

저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment

Blog

MySQL 에서 MS-SQL 로 데이터 이관 (MySQL ODBC Connector)

2012.01.10 17:04 DataBase/MS-SQL


이미 많이들 알고 있겠지만 난 개인적으로 처음이어서 포스팅을 한다.
MySQL 에서 MS-SQL 로 데이터 이관하는 작업이다.

테스트 환경은 다음과 같다.

* 원본 위치 환경
OS : Windows 2008 R2 Enterprise 64bit
DBMS : MS-SQL Server 2008 R2

* 이관 위치 환경
OS : Windows 2008 R2 Enterprise 64bit
DBMS : MySQL 5.1

이관될 위치 환경은 APMSETUP7 으로 설정된 환경이다.

순서는 아래와 같다.

먼저 MySQL ODBC Connector 를 다운로드 받는다. (다운로드 링크를 누른다.)


 
다운로드 받은 파일을 설치하고, 시작 → 관리도구  →  데이터 원본(ODBC) 를 실행한다.


그리고 사용자 DSN 탭에 추가 버튼을 누른다. (난 이미 추가되있어서..)


그럼 다음과 같은 새 데이터 원본 만들기 창이 나타난다.



저 상태에서 MySQL ODBC 5.1 Driver 항목을 선택하고 더블클릭 한다.

그럼 아래와 같은 ODBC Connector 창이 나타난다. 그럼 MySQL 계정정보를 입력하자.
여기서 Data Source Name 은 MySQL 에서 사용할 이름이다. 입맛에 맞게 설정하자.
 
r


여기까지 입력이 완료하고 OK 버튼을 누르고 마침 버튼을 누른다.
그럼 ODBC에 사용자 DSN 항목에 작성한 이름이 추가되어 나타나있는것을 확인할 수 있다.

[##_http://www.jkun.net/script/powerEditor/pages/1C%7Ccfile10.uf@191352334F0BE8E432AC5B.PNG%7Cwidth=%22533%22%20height=%22379%22%20alt=%22%22%20filename=%224.PNG%22%20filemime=%22image/jpeg%22%7C_##]
 그리고 이제 부터 MS-SQL 2008 에서 적용하는 과정이다.
 이미 Linked Server (연결된 서버) 를 사용해 본 사람들이 쉽게 할 수 있다. 
 아래와 같이 연결된 서버를 실행한다.


그리고 새 연결된 서버 창이 나타나면 정보를 입력한다.
공급자 : Microsoft OLE DB Provider for ODBC Driver
제품이름 : MySQL
데이터 원본 : 사용할 이름
공급자 문자열 : Driver={MySQL ODBC 5.1 Driver}; Server=서버; Database=데이터베이스;
위치 : 사용할 이름 


그리고 보안 탭에 MySQL 계정정보를 입력한다.

 
그리고 확인버튼을 누른다. 이에 만일 데이터액세스가 안되는 경우 서버 옵션 탭에
RPC 항목을 True 로 바꾸어 준다.
 


그리고 연결된 서버공급자 항목MSDASQL 항목에 속성을 활성화한다.
 


그리고 공급자 옵션 창이 나타나면 다음 항목에 체크해 준다.
중첩쿼리, 0 수준만, Inprocess 허용, 트랜잭션되지 않은 업데이트, LIKE 연산자를 지원합니다.


그리고 확인하고 마무리 지으면 다음처럼 활성화된 항목을 확인하며 MySQL 쪽에 데이터베이스 정보가
나오는 것을 확인.
 


그리고 조낸 캡쳐를 떠가며 작성한 사람을 위해서 댓글을 달아주는 센스까지 하면
완전 마무리가 된다.

MySQL 에서 MS-SQL 로 데이터 이관, 
MySQL 에서 MS-SQL 로 데이터 옮기기,

저작자 표시
신고
현재 5 개의 댓글이 있습니다.

김도현 2012.01.26 11:30 신고

링크드로 연결 시키는 방법인가요?
찾고 있는 자료였는데 감사합니다.
적용시켜 보겠습니다.


김도현 2012.01.26 12:15 신고

그런데 혹시 반대의 경우도 적용 가능 할까요?


나그네 2012.02.23 19:33 신고

자세한 설명 감사합니다..
다른 자료들은 그림도 없고 비슷한 내용을 두리뭉실하게 설명해놓았는데..
여기는 설명이 자세해서 따라하기 쉬웠습니다..

작성자 최고.. !!


csy3596 2013.02.20 11:46 신고

감사합니다.
MSSQL에 MYSQL을 연결시킬때 마다 MSSQL쪽이 죽어버려서 곤란한 상황에 있었는데 작성자님 덕분에 해결할 수 있었습니다.


  • JKUN Dev. Jkun 2013.02.20 12:25 신고

    아. ㅎㅎ 도움이 되셨다니 다행입니다.
    센스댓글 감사합니다~ ^^

Comment

Blog

SQL Server 2008 설치 오류 [역할관리도구]

2011.12.13 08:59 OS/Windows


한동안 SQL Server 를 쓰지 않다보니.. 어처구니 없는...
오류메세지가.. 나타났는데 도저히 기억이 안난다. 그냥 닷넷 프레임워크가 설치가 잘 안되서
그런줄 알았뜨만.. 짜증나게..

 
아어!
서버관리자에  기능추가를 해주자.

서버관리자에 기능추가를 선택하면 .NET Framework 3.51 기능
이란 선택란이 있다. 요걸 선택해서 활성화 해주자.



뭐.. 간단하게 설치순서를 요약하자면..
1. .NET Framework 설치
2. IIS 설치, 기능추가에 닷넷프레임워크 기능 활성화
3. 설치.

뭐 OS 2008 에는 따로 Windows Installer 를 설치하지 않았던 것 같다. 그리고 마이크로소프트에서
다운로드 받으려 하면 Windows Installer 는 XP와 2003 까지만 지원하는 것 같다.
그 이후버전에는.. 왠지 OS 설치때부터 지원하는 것 같다.
 
저작자 표시
신고
현재 0 개의 댓글이 있습니다.
Comment