넥스트스텝 ::

달력 더미 만들기

데이타베이스/MSSQL 2009/07/10 09:29

http://sqler.pe.kr에서 가지고 왔습니다.
석이님이 올리신 자료이며
원문은 http://sqler.pe.kr/web_board/view_list.asp?id=1332&part=MyBoard7&block=&gotopage=&tip=
입니다.

필요한 자료라서 스크랩핑 해둡니다.

CREATE TABLE [DBO].[T_CALENDAR](
[YYYYMMDD] [DATETIME] NOT NULL,
[WEEK] [INT] NULL,
[WEEK_DAY] [INT] NULL,
[DAY_OF_YEAR] [INT] NULL,
[QUARTER] [INT] NULL,
[HOLIDAY_YN] [CHAR](1) NULL,
[HOLIDAY_NAME] [VARCHAR](100) NULL,
PRIMARY KEY CLUSTERED
(
[YYYYMMDD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE PROC [DBO].[P_CALENDAR_INIT]
AS

SET NOCOUNT ON
BEGIN TRAN
TRUNCATE TABLE DBO.T_CALENDAR

DECLARE @START_DATE DATETIME
SET @START_DATE = '20080101'

WHILE (1=1)
BEGIN
INSERT INTO DBO.T_CALENDAR
SELECT
CONVERT(VARCHAR(8),TODAY,112) YYYYMMDD,
DATENAME(WEEK,TODAY) WEEK,
DATEPART(WEEKDAY,TODAY) WEEK_DAY,
DATEPART(DAYOFYEAR,TODAY) DAY_OF_YEAR,
DATEPART(QUARTER, TODAY) QUARTER,
CASE WHEN DATENAME(WEEKDAY,TODAY) IN ('토요일','일요일') THEN 1
ELSE 0 END [HOLIDAY_YN],
NULL HOLYDAY_NAME
FROM
(
SELECT @START_DATE TODAY
) A

SET @START_DATE = DATEADD(DAY,1,@START_DATE)
IF @START_DATE = '20190101' BREAK
END

-- HTTP://CAFE.NAVER.COM/SQLMVP/504 울 마누리 최고!!!!
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD
= '20090101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20090125'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20090126'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20090127'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE
YYYYMMDD = '20090301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE
YYYYMMDD = '20090502'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE
YYYYMMDD = '20090505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE
YYYYMMDD = '20090606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE
YYYYMMDD = '20090815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20091002'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석,개천절' WHERE
YYYYMMDD = '20091003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20091004'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE
YYYYMMDD = '20091225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD
= '20100101'

UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20100213'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20100214'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20100215'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE
YYYYMMDD = '20100301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE
YYYYMMDD = '20100505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE
YYYYMMDD = '20100521'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE
YYYYMMDD = '20100606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE
YYYYMMDD = '20100815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20100921'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20100922'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20100923'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE
YYYYMMDD = '20101003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE
YYYYMMDD = '20101225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD
= '20110101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20110202'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20110203'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20110204'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE
YYYYMMDD = '20110301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE
YYYYMMDD = '20110505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE
YYYYMMDD = '20110510'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE
YYYYMMDD = '20110606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE
YYYYMMDD = '20110815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20110911'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20110912'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20110913'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE
YYYYMMDD = '20111003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE
YYYYMMDD = '20111225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD
= '20120101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20120122'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20120123'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20120124'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE
YYYYMMDD = '20120301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE
YYYYMMDD = '20120505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE
YYYYMMDD = '20120528'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE
YYYYMMDD = '20120606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE
YYYYMMDD = '20120815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20120929'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20120930'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20121001'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE
YYYYMMDD = '20121003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE
YYYYMMDD = '20121225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD
= '20130101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20130209'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20130210'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20130211'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE
YYYYMMDD = '20130301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE
YYYYMMDD = '20130505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE
YYYYMMDD = '20130517'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE
YYYYMMDD = '20130606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE
YYYYMMDD = '20130815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20130918'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20130919'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20130920'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE
YYYYMMDD = '20131003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE
YYYYMMDD = '20131225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD
= '20140101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20140130'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20140131'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20140201'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE
YYYYMMDD = '20140301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE
YYYYMMDD = '20140505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE
YYYYMMDD = '20140506'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE
YYYYMMDD = '20140606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE
YYYYMMDD = '20140815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20140907'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20140908'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20140909'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE
YYYYMMDD = '20141003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE
YYYYMMDD = '20141225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD
= '20150101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20150218'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20150219'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20150220'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE
YYYYMMDD = '20150301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE
YYYYMMDD = '20150505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE
YYYYMMDD = '20150525'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE
YYYYMMDD = '20150606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE
YYYYMMDD = '20150815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20150926'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20150927'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20150928'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE
YYYYMMDD = '20151003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE
YYYYMMDD = '20151225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD
= '20160101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20160207'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20160208'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20160209'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE
YYYYMMDD = '20160301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE
YYYYMMDD = '20160505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE
YYYYMMDD = '20160514'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE
YYYYMMDD = '20160606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE
YYYYMMDD = '20160815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20160914'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20160915'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20160916'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE
YYYYMMDD = '20161003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE
YYYYMMDD = '20161225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD
= '20170101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20170127'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20170128'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20170129'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE
YYYYMMDD = '20170301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE
YYYYMMDD = '20170503'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE
YYYYMMDD = '20170505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE
YYYYMMDD = '20170606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE
YYYYMMDD = '20170815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절,추석' WHERE
YYYYMMDD = '20171003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20171004'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20171005'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE
YYYYMMDD = '20171225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD
= '20180101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20180215'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20180216'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD
= '20180217'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE
YYYYMMDD = '20180301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE
YYYYMMDD = '20180505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE
YYYYMMDD = '20180522'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE
YYYYMMDD = '20180606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE
YYYYMMDD = '20180815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20180923'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20180924'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD
= '20180925'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE
YYYYMMDD = '20181003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE
YYYYMMDD = '20181225'

COMMIT TRAN

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

MyKnowhow,Tips_SQL_Server_2000

데이타베이스/MSSQL 2009/05/29 09:06

http://download.microsoft.com/download/6/8/f/68f15f44-e957-4a1a-97b8-b7444e4d0379/MyKnowhow,Tips_SQL_Server_2000.pdf

MSSQL에 대한 여러가지 팁을 모아둔 전자책입니다. 한글로 되어있습니다. 내용이 좋군요. 많이 도움이 됩니다.

MyKnowhow,Tips_SQL_Server_2000.rar

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

MSSQL에서 다국어로 이메일 발송하기

데이타베이스/MSSQL 2009/05/12 10:37

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
   @From nvarchar(100) ,
   @To nvarchar(100) ,
   @Subject nvarchar(100)=" ",
   @Body nvarchar(4000) =" "
/*********************************************************************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

***********************************************************************/
   AS
   Declare @iMsg int
   Declare @hr int
   Declare @source nvarchar(255)
   Declare @description nvarchar(500)
   Declare @output nvarchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '121.189.18.195'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
   EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
   EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
   EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
   EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
   EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBodyPart.Charset',"UTF-8"
   EXEC @hr = sp_OASetProperty @iMsg, 'BodyPart.Charset',"UTF-8"
   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
   IF @hr <>0
     select @hr
     BEGIN
       EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
       IF @hr = 0
         BEGIN
           SELECT @output = ' Source: ' + @source
           PRINT @output
           SELECT @output = ' Description: ' + @description
           PRINT @output
         END
       ELSE
         BEGIN
           PRINT ' sp_OAGetErrorInfo failed.'
           RETURN
         END
     END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

위의 프로시저를 생성한후에

declare @Body varchar(4000)

select @Body = N'안녕하세요'

exec sp_send_cdosysmail '보내는이메일','받을이메일','제목',@Body

과 같이 프로시저를 이용해서 발송하면 완료~

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

TAG MSSQL, 메일발송

Trackback 0, Comment 0

ms sql 데이타베이스 복원방법

데이타베이스/MSSQL 2009/02/06 22:04

MS SQL 2000을 사용하시나요? 아님 2005?

UI로 설명드리면 혼동이 있을 것 같아,

스크립트로 설명을 드립니다.

RESTORE DATABASE [새로복원할데이터베이스명]
   FROM DISK = 'c:\백업파일의경로및파일명'
   WITH MOVE '논리적데이터파일이름' TO '변경하고자하는데이터파일의경로및파일명',
   MOVE '논리적트랜잭션로그파일이름' TO '변경하고자하는트랜잭션파일의경로및파일명''

예제) Northwind 데이터베이스 백업본을 Testdb라는 새로운 데이터베이스로 복원하는 절차

-- 백업생성(있으면 생략)

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'

--백업정보확인(논리적파일명, 물리적저장위치및파일명 확인)

RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'

-- TestDB라는 새로운 데이터베이스로 새로운 경로(C:\temp\test*.*)으로 복원

RESTORE DATABASE TestDB
   FROM DISK = 'c:\Northwind.bak'
   WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
   MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'

참고적으로 논리적파일이름과 현재 백업본에 포함된 물리적파일위치를 확인하고자 하면 아래의 명령을 사용하시면 됩니다.

RESTORE FILELISTONLY

FROM DISK = 'c:\백업파일의경로및파일명'

RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'

감사합니다

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

Microsoft SQL Server 2005 서비스 팩 3 릴리즈

데이타베이스/MSSQL 2008/12/31 12:15

http://www.microsoft.com/downloads/details.aspx?displaylang=ko&FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

TAG mssql 2005

Trackback 0, Comment 1

MSSQL 2005 T-SQL 새로운 기능들

데이타베이스/MSSQL 2008/12/17 05:47

출처 : http://blog.jeidee.net/194 - 필요한 내용이라 복사해 둡니다.

목 차

1. 랭킹 함수
2. CTE(Common table expressions)기반 재귀쿼리
3. PIVOT, APPLY 관계 연산자
4. DRI(Declarative referential integrity - 선언적인 참조 무결성) 강화
5. 강화된 성능과 에러 핸들링
6. 기타

참고문헌

- 원문 URL
:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05tsqlenhance.asp
- AdventureWorks 샘플 데이터베이스 다운로드
:http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

기타

MSSQL2005_T-SQL_New_Features-freeinmatrix.sql

본 내용을 스크립트 파일로 작성해서 첨부해 놓았습니다.
SQL Server Management Studio에서 사용할 수 있습니다.

1. 랭킹 함수
- 2005에서는 네개의 새로운 랭킹함수를 제공합니다.
ROW_NUMBER, RANK, DENSE_RANK, NTILE
일반적인 목적은 결과셋의 로우에 순차적으로 숫자를 할당하는 데 있다.
예를 들면 페이징, 스코어링, 히스토그램등...

USE tempdb or your own test database
CREATE TABLE SpeakerStats
(
speaker    VARCHAR(10) NOT NULL PRIMARY KEY,
track    VARCHAR(10) NOT NULL,
score    INT NOT NULL,
pctfilledevals INT NOT NULL,
numsessions    INT NOT NULL
)

SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan', 'Sys', 3, 22, 4)
INSERT INTO SpeakerStats VALUES('Ron', 'Dev', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Kathy', 'Sys', 8, 27, 2)
INSERT INTO SpeakerStats VALUES('Suzanne', 'DB', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Joe', 'Dev', 6, 20, 2)
INSERT INTO SpeakerStats VALUES('Robert', 'Dev', 6, 28, 2)
INSERT INTO SpeakerStats VALUES('Mike', 'DB', 8, 20, 3)
INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
INSERT INTO SpeakerStats VALUES('Brian', 'Sys', 7, 22, 3)
INSERT INTO SpeakerStats VALUES('Kevin', 'DB', 7, 25, 4)
GO

◆ ROW_NUMBER
SCORE가 많은 순서대로 순위를 매긴다.(문제점 : 동점자 처리)
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
speaker, track, score
FROM SpeakerStats
ORDER BY score DESC

rownum speaker    track    score
---------------------------------------------
1    Jessica    Dev    9
2    Ron    Dev    9
3    Suzanne    DB 9
4    Kathy    Sys    8
5    Michele    Sys    8
6    Mike DB 8
7    Kevin    DB 7
8    Brian    Sys    7
9    Joe    Dev    6
10 Robert Dev    6
11 Dan    Sys    3

SCORE가 많은 순서대로 순위를 매긴다.
동점자의 경우 PCTFILLEDEVALS,NUMSESSIONS,SPEAKER 순으로...
(문제점 : 여전히 동점자가 생긴다.)
SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC,
numsessions DESC, speaker) AS rownum,
speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats
ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

rownum speaker    track    score pctfilledevals numsessions
------------------------------------------------------------------
1    Ron    Dev    9 30 3
2    Suzanne    DB 9 30 3
3    Jessica    Dev    9 19 1
4    Michele    Sys    8 31 4
5    Kathy    Sys    8 27 2
6    Mike DB 8 20 3
7    Kevin    DB 7 25 4
8    Brian    Sys    7 22 3
9    Robert Dev    6 28 2
10 Joe    Dev    6 20 2
11 Dan    Sys    3 22 4

SQL 2000에서는 위와 같은 결과를 내기 위해 아래와 같은 쿼리를
작성해야 했다. 복잡하다.
그 만큼 랭킹함수는 구문을 간략히 하고,
내부 실행에 있어서도 훨씬 효율적이다.
매 로우당 기준테이블의 로우 스캔을 해야하는데다
인덱스 사용을 할 수 없어 테이블 스캔을 해야하므로
성능이 떨어질 수 밖에 없다.
그에 비하면 SQL 2005에서는 ORDER BY에 사용되는
필드가 인덱스일 경우 해당 인덱스테이블을 사용할 수 있다.
SELECT
(SELECT COUNT(*)
   FROM SpeakerStats AS S2
   WHERE S2.score > S1.score
   OR (S2.score = S1.score
   AND S2.pctfilledevals > S1.pctfilledevals)
   OR (S2.score = S1.score
   AND S2.pctfilledevals = S1.pctfilledevals
   AND S2.numsessions > S1.numsessions)
   OR (S2.score = S1.score
   AND S2.pctfilledevals = S1.pctfilledevals
   AND S2.numsessions = S1.numsessions
   AND S2.speaker < S1.speaker)) + 1 AS rownum,
speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats AS S1
ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

rownum    speaker    track    score pctfilledevals numsessions
---------------------------------------------------------
1 Ron    Dev    9 30 3
2 Suzanne    DB 9 30 3
3 Jessica    Dev    9 19 1
4 Michele    Sys    8 31 4
5 Kathy    Sys    8 27 2
6 Mike DB 8 20 3
7 Kevin    DB 7 25 4
8 Brian    Sys    7 22 3
9 Robert Dev    6 28 2
10    Joe    Dev    6 20 2
11    Dan    Sys    3 22 4

2005에서의 성능저하가 linear하다면 2000에서는 기하급수적이다.(로우수 비례)

일반적으로 ROW_NUMBER 함수는 페이징에서 사용할 수 있다.
페이지 사이즈가 3일때 두 번째 페이지를 출력하려면
넘버링을 한 테이블 D에서 순위 4위~6위까지를 출력하면 될 것이다.
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, speaker

rownum speaker    track    score
---------------------------------------------
4    Kathy    Sys    8
5    Michele    Sys    8
6    Mike DB 8

좀 더 쓸모있게 확장해 보면 아래처럼...
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 1
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker
GO

rownum speaker    track    score
---------------------------------------------
4    Kathy    Sys    8
5    Michele    Sys    8
6    Mike DB 8

좀 더 성능을 높이려면 계산된(넘버링된) 테이블을 임시테이블로 만들고
인덱스를 설정한 후 사용하면 된다.
오직 해당 페이지의 로우만 스캔하게 될 것이다.
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 1
SET @pagesize = 3

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, *
INTO #SpeakerStatsRN
FROM SpeakerStats
CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #SpeakerStatsRN(rownum)

SELECT rownum, speaker, track, score
FROM #SpeakerStatsRN
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker

DROP TABLE #SpeakerStatsRN
GO

rownum speaker    track    score
---------------------------------------------
4    Kathy    Sys    8
5    Michele    Sys    8
6    Mike DB 8

-- ◆ Partitioning - 파티셔닝
파티셔닝은 전체 로우에 일괄적으로 순번을 매기는 것이 아니라
특정 그룹내에서만 순번이 매겨지도록 할 때 사용된다.
아래구문은 PARTITION BY track 절 사용에 의해
동일 track 내에서만 순번이 매겨지도록 한다.
SELECT track,
ROW_NUMBER() OVER(
PARTITION BY track
ORDER BY score DESC, speaker) AS pos,
speaker, score
FROM SpeakerStats
ORDER BY track, score DESC, speaker

track    pos    speaker    score
---------------------------------------------
DB 1    Suzanne    9
DB 2    Mike 8
DB 3    Kevin    7
Dev    1    Jessica    9
Dev    2    Ron    9
Dev    3    Joe    6
Dev    4    Robert 6
Sys    1    Kathy    8
Sys    2    Michele    8
Sys    3    Brian    7
Sys    4    Dan    3

-- ◆ RANK, DENSE_RANK
ROW_NUMBER와 아주 유사한 사용패턴을 갖는다.
ORDER BY에 의해 소팅되고, PARTITION BY에 의해 그룹내에서 적용된다.
이름에서도 알 수 있듯이 분명한 사용상의 차이가 있다.
ROW_NUMBER에서는 동점 로우가 존재해도 순번이 달리 매겨졌던데 비해
RANK,DENSE_RANK는 동점 로우에 같은 순번을 매긴다.
즉 말 그대로 전형적인 랭킹 함수라 할 수 있다.
SELECT speaker, track, score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
RANK() OVER(ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER(ORDER BY score DESC) AS drnk
FROM SpeakerStats
ORDER BY score DESC

speaker    track    score rownum rnk    drnk
---------------------------------------------------------------------------------
Jessica    Dev    9 1    1    1
Ron    Dev    9 2    1    1
Suzanne    DB 9 3    1    1
Kathy    Sys    8 4    4    2
Michele    Sys    8 5    4    2
Mike DB 8 6    4    2
Kevin    DB 7 7    7    3
Brian    Sys    7 8    7    3
Joe    Dev    6 9    9    4
Robert Dev    6 10 9    4
Dan    Sys    3 11 11 5

위 쿼리의 결과에서 알 수 있듯이
RANK의 경우 1,2,3,4위가 있을 때 1,2,3위가 같아 모두 1위로 매겨지게 되면
4위가 2위가 되지 않고 그대로 4위가 되는 것이고,
DENSE_RANK의 경우에는 4위가 2위로 매겨지는 차이가 있다.

-- ◆ NTILE
결과셋을 n개의 그룹으로 나누고 나뉘어진 그룹에 순번을 매긴다.
아래의 쿼리를 실행하면 11개의 로우를 score로 정렬한 후,
4개씩 그룹을 지어 순번을 매기게 되는데 마지막 그룹의 로우수는
다른 그룹과 다를 수 있다.(여기에서는 3개)
SELECT speaker, track, score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
NTILE(3) OVER(ORDER BY score DESC) AS tile
FROM SpeakerStats
ORDER BY score DESC

speaker    track    score rownum tile
---------------------------------------------------------------
Jessica    Dev    9 1    1
Ron    Dev    9 2    1
Suzanne    DB 9 3    1
Kathy    Sys    8 4    1
Michele    Sys    8 5    2
Mike DB 8 6    2
Kevin    DB 7 7    2
Brian    Sys    7 8    2
Joe    Dev    6 9    3
Robert Dev    6 10 3
Dan    Sys    3 11 3

NTILE의 활용예를 보자. 3개의 그룹으로 나누어
각 그룹별로 scorecategory를 부여하는 예이다.
SELECT speaker, track, score,
CASE NTILE(3) OVER(ORDER BY score DESC)
WHEN 1 THEN 'High'
WHEN 2 THEN 'Medium'
WHEN 3 THEN 'Low'
END AS scorecategory
FROM SpeakerStats
ORDER BY track, speaker

speaker    track    score scorecategory
--------------------------------------
Kevin    DB 7 Medium
Mike DB 8 Medium
Suzanne    DB 9 High
Jessica    Dev    9 High
Joe    Dev    6 Low
Robert Dev    6 Low
Ron    Dev    9 High
Brian    Sys    7 Medium
Dan    Sys    3 Low
Kathy    Sys    8 High
Michele    Sys    8 Medium

2. 재귀쿼리와 CTE(Common Table Expressions)
◆ CTE
CTE는 VIEW와 Derived Table(인라인뷰라고도 한다.)의 장점을 모은것이다.
VIEW는 한 번 생성해 놓으면 삭제되기 전까지는 테이블처럼 사용할 수 있다.
Derived Table은 한 문장의 쿼리에서만 일시적으로 사용할 수 있다.
CTE는 View나 테이블처럼 영속성이 없고 하나의 배치쿼리내에서만
지속성을 갖을 수 있다. 즉, 배치쿼리내에서 선언적으로 정의한 후에는
여러 쿼리 문장에서 반복적으로 사용할 수 있다는 것이다.
View, Derived Table, CTE의 사용패턴을 보자.

▷ View
CREATE VIEW <view_name>(<column_aliases>)
AS
<view_query>
GO
SELECT *
FROM <view_name>

▷ Derived Table
SELECT *
FROM (<derived_table_query>) AS <derived_table_alias>(<column_aliases>)

▷ CTE
WITH <cte_alias>(<column_aliases>)
AS
(

<cte_query>
)
SELECT *
FROM <cte_alias>

CTE는 WITH 키워드를 사용해서 정의하는데
배치구문의 맨 앞에 CTE를 정의하지 않을 경우
WITH 키워드 앞에 세미콜론(;)을 반드시 입력해야 한다.
세미콜론은 FROM절에 사용되는 Hint 키워드등과의 혼동을 없애는 역할을 한다.

View, Derived Table, CTE의 사용예를 보도록 하자.
그 전에 AdventureWorks 데이터베이스를 만들어야 한다.
AdventureWorks 데이터베이스는 SQLEXPRESS버전일 경우
아래의 URL에서 다운로드 받아 설치한후 DB연결하면 된다.
AdventureWorks 샘플 데이터베이스 다운로드 :http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

▷ View
USE AdventureWorks
Go

CREATE VIEW VEmpOrders(EmployeeID, NumOrders, MaxDate)
AS
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
GO
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN VEmpOrders AS OE
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN VEmpOrders AS OM
ON E.ManagerID = OM.EmployeeID

▷ Derived Tables

SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E

JOIN (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OE(EmployeeID, NumOrders, MaxDate)
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN
(SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OM(EmployeeID, NumOrders, MaxDate)
ON E.ManagerID = OM.EmployeeID

▷ CTE

WITH EmpOrdersCTE(EmployeeID, NumOrders, MaxDate)
AS
(
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
)
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN EmpOrdersCTE AS OE
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN EmpOrdersCTE AS OM
ON E.ManagerID = OM.EmployeeID

EmployeeID NumOrders MaxDate ManagerID NumOrders MaxDate
--------------------------------------------------------------------------------
261 360 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
238 361 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
264 362 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
241 360 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
198 164 2004-10-23 00:00:00.000 274 160 2004-09-01 00:00:00.000
244 361 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
233 360 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
164 361 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
231 362 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
223 400 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
266 401 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
274 160 2004-09-01 00:00:00.000 71 NULL NULL

WITH 키워드 이후 CTE정의는 콤마(,)를 사용해서 복수개 정의할 수 있다.
우선 정의된 CTE는 곧 이어 다른 CTE정의 때에 곧 바로 사용할 수도 있다.
이 때에는 뒤에서 정의된 CTE는 앞에서 정의된 CTE에서 사용할 수 없다.
즉, 순차적으로 참조할 수 있고 자기 자신도 참조할 수 있다.
예를 들면, A_CTE, B_CTE, C_CTE에서 B_CTE는 A_CTE와 B_CTE를 참조할 수 있고,
C_CTE는 참조할 수 없다.
WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader

GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE

MN MX Diff
-----------------------------
160 401 241

◆ 재귀쿼리(Recursive Queries)
사용패턴
WITH RecursiveCTE(<column_list>)
AS
(
Anchor Member:(AM - 고정멤버)
SELECT query that does not refer to RecursiveCTE
SELECT ...
FROM <some_table(s)>
...
UNION ALL
Recursive Member(RM - 재귀멤버)
SELECT query that refers to RecursiveCTE
SELECT ...
FROM <some_table(s)>
JOIN RecursiveCTE

...
)
Outer Query
SELECT ...
FROM RecursiveCTE
...

재귀 CTE의 동작 방식은 아래와 같다.
1. 고정멤버가 활성화된다. 첫번째 결과셋 R0를 만들어낸다.
2. 재귀멤버가 활성화된다. 고정멤버가 빈 결과셋을 반환할 때까지 스텝(스텝번호=i)을
증가시키면서 Ri를 만들어낸다. 이 때 만들어진 결과셋들은 UNION ALL 구문으로 결합된다.
일반적인 재귀함수의 작동방식을 생각해내면 이해가 빠를 것이다.

사용예를 봅시다.
싱글패어런트 예제 : 사원 조직도
USE tempdb
Go
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
Go

Employees 테이블의 데이터 구조는 아래와 같다.
   1(Nancy)
┌───────────────┴─┬────────────────────────────┐
   2(Andrew) 3(Janet) 4(Margaret)
   ┌─┴───┐    ┌──┴─────┬────────────┐ │
5(Steven) 6(Michael) 7(Robert) 8(Laura) 9(Ann) 10(Ina)
이하 생략...알아서 상상하시길...

Robert(empid = 7)를 포함해서 Robert가 관리하고 있는 사원을 모두 출력해 보자(레벨순으로)
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(

Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 7
UNION ALL

Recursive Member (RM)
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

empid empname mgrid lvl
----------------------------------------------------
7 Robert    3 0
11    David 7 1
12    Ron 7 1
13    Dan 7 1
14    James 11    2

(1) 고정멤버 활성화됨. empid=7인 Robert를 가져온다.
(2) 재귀멤버 활성화됨. Employees테이블에서 mgrid=7인 사원들을 가져와서 UNION ALL한다.
   David, Ron, Dan을 가져오는데, 다시 재귀멤버가 활성화되어
   David, Ron, Dan이 관리하는 사원들을 가져오게 된다.
   이 과정이 반복되는데 더 이상 하위 사원들이 없을 때까지 반복한다.
   David가 관리하는 James 밖에 없으므로 스텝 두 번째에서 재귀호출 종료된다.
* lvl은 재귀호출의 스텝번호와 같음을 알 수 있고, 조직의 레벨값이된다.

WITH EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
(
SELECT empid, empname, mgrid, 0,
CAST(empid AS VARBINARY(900))
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE(' | ', lvl)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol

위 예제는 각자 분석해 보고,
더 많은 예제는 원문을 참조하기 바란다.

3. PIVOT and UNPIVOT
PIVOT은 말 그대로 행과 열을 바꾸는 것이라 생각하면 된다.
주로 크로스탭 결과셋을 만들 때 가장 많이 필요한 기능이다.

이해를 돕기 위해 크로스탭 질의의 간단한 예를 살펴보자.

OT(년도,수출입구분,수출입물량)이라는 OT테이블이 있다.
데이터는 아래의 표와 같다.

연도 구분 물량
--------
2000 수출 20
2000 수입 120
2001 수출 50
2001 수입 130
2002 수출 80
2002 수입 135

연도별 수출입 물량 추이는 아래와 같다.
구분 2000 2001 2002
-- --------
수출 20 50 80
수입 120 130 135

OT테이블의 연도 데이터(열 : 2000,2001,2002)가 행(컬럼)으로 바뀐 것이다.
위 테이블이 바로 크로스탭 결과셋(보고서)이다.
OT테이블에서 크로스탭 결과셋을 출력하는 쿼리를 생성하는 것은 복잡한 작업이다.
하지만 크로스탭 결과셋을 출력해야 하는 경우는 실무에선 정말 빈번한 업무라 할 수 있다.
SQL 2005에서 추가된 PIVOT 함수가 이런 어려움을 아름답게 해결해 주었다고 한다.
눈치를 챗다면 알겠지만, 행과 열을 바꾼다는 의미는 다시말해
테이블 스키마를 동적으로 변경해야 하는(필드가 고정되지 않고 동적으로 증감하는)
경우에 필요한 것임을 알 수 있다.
위의 예에서도 알다시피 OT테이블에서 연도컬럼의 로우셋이 크로스탭 결과셋에서는
컬럼으로 바뀌었다는 것을 알 수 있다.
즉, 정상적인 테이블로 설계한다면
OT2(수출입구분, 2000년, 2001년, 2002년) 이라는 OT2테이블을 설계해야 했던 것이다.
하지만 연도데이터는 2003년, 2004년 계속 늘어날 수 있고 또는 생기지 않을 수 있는
동적 데이터이기 때문에 OT2테이블 처럼 테이블구조(스키마)를 고정시킬 수 없는 것이고,
따라서 OT테이블에서 크로스탭 질의를 사용해 크로스탭 보고서를 생성할 수 밖에 없다.

이번에는 실제 데이터를 사용해서 자세히 살펴보도록 하자.
옥션에서 경매에 붙여지는 다양한 물품(아이템)정보를 표현해 보도록 하자.
AuctionItems테이블은 아이템 정보를 갖는 마스터 테이블이다.
동일 아이템도 등록된 시점에 따라 다른 아이템으로 취급된다.
CREATE TABLE AuctionItems
(
itemid INT    NOT NULL PRIMARY KEY NONCLUSTERED,
itemtype NVARCHAR(30) NOT NULL,
whenmade INT    NOT NULL,
initialprice MONEY    NOT NULL,

other columns

)
CREATE UNIQUE CLUSTERED INDEX idx_uc_itemtype_itemid
ON AuctionItems(itemtype, itemid)
INSERT INTO AuctionItems VALUES(1, N'Wine', 1822, 3000)
INSERT INTO AuctionItems VALUES(2, N'Wine', 1807, 500)
INSERT INTO AuctionItems VALUES(3, N'Chair', 1753, 800000)
INSERT INTO AuctionItems VALUES(4, N'Ring', -501, 1000000)
INSERT INTO AuctionItems VALUES(5, N'Painting', 1873, 8000000)
INSERT INTO AuctionItems VALUES(6, N'Painting', 1889, 8000000)

ItemAttributes는 AuctionItems에 등록된 아이템별 속성정보를 갖는 테이블이다.
속성은 아이템별로 다른 정보를 갖을 수 있으므로 고정된 컬럼을 갖는 테이블로
설계할 수 없다.
CREATE TABLE ItemAttributes
(
itemid INT NOT NULL REFERENCES AuctionItems,

attribute NVARCHAR(30) NOT NULL,
value SQL_VARIANT NOT NULL,
PRIMARY KEY (itemid, attribute)
)
INSERT INTO ItemAttributes
VALUES(1, N'manufacturer', CAST(N'ABC'    AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(1, N'type', CAST(N'Pinot Noir' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(1, N'color',    CAST(N'Red'    AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(2, N'manufacturer', CAST(N'XYZ'    AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(2, N'type', CAST(N'Porto'    AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(2, N'color',    CAST(N'Red'    AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(3, N'material', CAST(N'Wood' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(3, N'padding',    CAST(N'Silk' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(4, N'material', CAST(N'Gold' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(4, N'inscription', CAST(N'One ring ...' AS NVARCHAR(50)))
INSERT INTO ItemAttributes
VALUES(4, N'size', CAST(10    AS INT))
INSERT INTO ItemAttributes
VALUES(5, N'artist', CAST(N'Claude Monet' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(5, N'name', CAST(N'Field of Poppies' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(5, N'type', CAST(N'Oil'    AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(5, N'height', CAST(19.625    AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
VALUES(5, N'width',    CAST(25.625    AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
VALUES(6, N'artist', CAST(N'Vincent Van Gogh' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(6, N'name', CAST(N'The Starry Night' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(6, N'type', CAST(N'Oil'    AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(6, N'height', CAST(28.75 AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
VALUES(6, N'width',    CAST(36.25 AS NUMERIC(9,3)))

GO

아이템구분이 painting인 아이템(5,6)을 각 어트리뷰트별로 출력해 보자.
PIVOT 연산자를 사용하지 않는다.
SELECT
itemid,
MAX(CASE WHEN attribute = 'artist' THEN value END) AS [artist],
MAX(CASE WHEN attribute = 'name' THEN value END) AS [name],
MAX(CASE WHEN attribute = 'type' THEN value END) AS [type],
MAX(CASE WHEN attribute = 'height' THEN value END) AS [height],
MAX(CASE WHEN attribute = 'width' THEN value END) AS [width]
FROM ItemAttributes AS ATR
WHERE itemid IN(5,6)
GROUP BY itemid

itemid artist name type height width
--------------------------------------------------
5 Claude Monet Field of Poppies Oil 19.625 25.625
6 Vincent Van Gogh The Starry Night Oil 28.750 36.250

이번에는 PIVOT 연산자를 사용해서 같은 결과를 출력해 보자.
SELECT *
FROM ItemAttributes AS ATR
PIVOT

(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemid IN(5,6)

itemid artist name type height width
--------------------------------------------------
5 Claude Monet Field of Poppies Oil 19.625 25.625
6 Vincent Van Gogh The Starry Night Oil 28.750 36.250

더 다양한 사용 예는 원문을 참조하기 바란다.

◆ UNPIVOT
UNPIVOT은 PIVOT의 반대개념으로 보면된다.
테이블의 컬럼을 로우셋으로 만드는 것이다.
예를 들면 AuctionItems 테이블의 itemtype, whenmade, initialprice를 attribute 컬럼의 로우셋으로
각 컬럼의 원래 값을 value 컬럼의 로우셋으로 변경한다.
SELECT * FROM AuctionItems

itemid    itemtype whenmade    initialprice
-------------------------------------------------------------------
3 Chair    1753    800000.00
5 Painting 1873    8000000.00
6 Painting 1889    8000000.00
4 Ring -501    1000000.00
1 Wine 1822    3000.00
2 Wine 1807    500.00

SELECT itemid, attribute, value
FROM (SELECT itemid,
CAST(itemtype AS SQL_VARIANT) AS itemtype,
CAST(whenmade AS SQL_VARIANT) AS whenmade,
CAST(initialprice AS SQL_VARIANT) AS initialprice
FROM AuctionItems) AS ITM
UNPIVOT
(
value FOR attribute IN([itemtype], [whenmade], [initialprice])
) AS UPV

itemid    attribute value
-----------------------------
1 itemtype    Wine
1 whenmade    1822
1 initialprice    3000.00
2 itemtype    Wine
2 whenmade    1807
2 initialprice    500.00
3 itemtype    Chair
3 whenmade    1753
3 initialprice    800000.00
4 itemtype    Ring
4 whenmade    -501
4 initialprice    1000000.00
5 itemtype    Painting
5 whenmade    1873
5 initialprice    8000000.00
6 itemtype    Painting
6 whenmade    1889
6 initialprice    8000000.00

◆ APPLY
Outer 테이블(기준테이블)의 레코드 단위로 테이블반환 함수를 호출할 수 있다.
JOIN 연산자와 흡사하게 작동하는데,
쉽게 말하면 SELECT * FROM OT 문장에서 OT테이블의 레코드 하나 하나마다
함수가 호출되어 반환된 레코드셋과 조인된다고 볼 수 있다.
APPLY 연산자는 CROSS APPLY와 OUTER APPLY가 있다.

▷ CROSS APPLY

두 개의 정수를 입력 받아 작은 값과 큰 값을 갖는 결과셋을 반환하는
함수를 생성한다.
CREATE FUNCTION dbo.fn_scalar_min_max(@p1 AS INT, @p2 AS INT) RETURNS TABLE
AS
RETURN
SELECT
CASE
WHEN @p1 < @p2 THEN @p1
WHEN @p2 < @p1 THEN @p2
ELSE COALESCE(@p1, @p2)
END AS mn,
CASE
WHEN @p1 > @p2 THEN @p1
WHEN @p2 > @p1 THEN @p2
ELSE COALESCE(@p1, @p2)
END AS mx
GO
SELECT * FROM fn_scalar_min_max(10, 20)

mn mx
--------------------
10 20

기준테이블 T1을 만들어보자.
CREATE TABLE T1
(
col1 INT NULL,
col2 INT NULL
)

INSERT INTO T1 VALUES(10, 20)
INSERT INTO T1 VALUES(20, 10)
INSERT INTO T1 VALUES(NULL, 30)
INSERT INTO T1 VALUES(40, NULL)
INSERT INTO T1 VALUES(50, 50)

T1의 각 레코드별로 fn_scalar_min_max 함수를 호출한다.
SELECT *
FROM T1 CROSS APPLY fn_scalar_min_max(col1, col2) AS M

col1    col2    mn    mx
--------------------------------------
10    20    10    20
20    10    10    20
NULL    30    30    30
40    NULL    40    40
50    50    50    50

첫째 레코드를 보면 10, 20을 넘겨서 mn,mx(10,20)
둘째 레코드를 보면 20, 10을 넘겨서 mn,mx(10,20)
셋째 레코드를 보면 NULL, 30을 넘겨서 mn,mx(30,30)
....

눈치 챗나?
기준 테이블의 레코드개수만큼 fn_scalar_min_max가 호출되어 fn_scalar_min_max이
토해낸 결과셋 만큼 더해져 출력되는 것이다.
fn_scalar_min_max의 결과레코드개수가 n개 이상이면 기준테이블의 개수가 * n 개 된다.

좀 더 확실히 디벼보기 위해 부서테이블을 만들고 이전에 만들어 두었던
Employees 테이블과 연결해서 살펴보도록 하자.
Departments(부서)테이블을 만든다.
deptmgrid는 Empoloyees테이블의 empid와 연결된다.
CREATE TABLE Departments
(
deptid    INT NOT NULL PRIMARY KEY,
deptname VARCHAR(25) NOT NULL,
deptmgrid INT NULL REFERENCES Employees
)
SET NOCOUNT ON
INSERT INTO Departments VALUES(1, 'HR', 2)
INSERT INTO Departments VALUES(2, 'Marketing',    7)
INSERT INTO Departments VALUES(3, 'Finance',    8)
INSERT INTO Departments VALUES(4, 'R&D',    9)
INSERT INTO Departments VALUES(5, 'Training', 4)
INSERT INTO Departments VALUES(6, 'Gardening', NULL)
GO

재귀쿼리(CTE)에서 살펴봤던 조직도를 반환하는 사용자함수를 만든다.
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE
(
empid INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM employees
WHERE empid = @empid
UNION all
Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM employees AS e
JOIN employees_subtree AS es
ON e.mgrid = es.empid
)
INSERT INTO @TREE
SELECT * FROM Employees_Subtree
RETURN
END
GO

CROSS APPLY를 사용해 부서테이블 기준으로 해당 부서에 소속된 사원들을
트리형태로 구해보자.
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST

deptid    deptname deptmgrid empid empname    mgrid lvl
-------------------------------------------------------
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael    2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11    David    7 1
2 Marketing 7 12    Ron    7 1
2 Marketing 7 13    Dan    7 1
2 Marketing 7 14    James    11    2
3 Finance    8 8 Laura    3 0
4 R&D    9 9 Ann    3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10    Ina    4 1

그런데 결과셋을 보니 무언가 이상하다.
Gardening 부서가 출력되지 않았다.
CROSS APPLY는 함수에서 리턴되는 결과셋이 없을 경우
기준테이블의 레코드를 출력하지 않는 것이다.

▷ OUTER APPLY
Gardening 부서를 출력하는 방법은?
OUTER APPLY를 사용하는 것이다.
OUTER APPLY는 CROSS APPLY와 거의 흡사한 결과를 출력하지만(사용법도 같다.)
OUTER Table(기준테이블)의 모든 레코드를 기본으로 함수에서 리턴되는 결과셋이 없더라도
기준테이블의 레코드는 모두 출력한다.

SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST

-------------------------------------------------------
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael    2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11    David    7 1
2 Marketing 7 12    Ron    7 1
2 Marketing 7 13    Dan    7 1
2 Marketing 7 14    James    11    2
3 Finance    8 8 Laura    3 0
4 R&D    9 9 Ann    3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10    Ina    4 1
6 Gardening NULL    NULL    NULL NULL    NULL

◆ 연관된 서브쿼리에서 테이블반환 함수사용
SQL 2000에서는 지원하지 않았지만 2005에서는 지원한다.
SELECT *
FROM Departments AS D
WHERE (SELECT COUNT(*)
FROM fn_getsubtree(D.deptmgrid)) >= 3

deptid deptname deptmgrid
-------------------------------------------
1 HR 2
2 Marketing 7

4. DRI(선언적 참조 무결성) 강화
◆ SET DEFAULT와 SET NULL
SQL 2005에서는 외부키(Foreign key) 제약조건과 관련된 동작(트리거) 네 가지를 지원한다.
SQL 2000에서는 DELETE와 UPDATE시 NO ACTION과 CASCADE 두 가지만 지원했지만,
SQL 2005에서는 SET DEFAULT와 SET NULL을 추가로 지원한다.
SET DEFAULT는 DELETE와 UPDATE시 해당 컬럼의 Default Value로 값을 셋팅하며,
마찬가지로 SET NULL은 해당 컬럼을 NULL 값으로 셋팅한다.

예를 들어 설명해 보자.
USE tempdb
go

Customers 테이블을 생성한다.
CREATE TABLE Customers
(
customerid CHAR(5) NOT NULL,

other columns

CONSTRAINT PK_Customers PRIMARY KEY(customerid)
)

INSERT INTO Customers VALUES('DUMMY')
INSERT INTO Customers VALUES('FRIDA')
INSERT INTO Customers VALUES('GNDLF')
INSERT INTO Customers VALUES('BILLY')
Go

Orders 테이블은 Customers 테이블과 customerid(외부키)로 연결된다.
Customers 테이블의 레코드가 DELETE 될 때 SET NULL 액션 지정,
UPDATE 될 때 SET DEFAULT 액션을 지정한다.
CREATE TABLE Orders
(
orderid INT NOT NULL,
customerid CHAR(5) NULL DEFAULT('DUMMY'),
orderdate DATETIME NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid),
CONSTRAINT FK_Orders_Customers
FOREIGN KEY(customerid)
REFERENCES Customers(customerid)
ON DELETE SET NULL
ON UPDATE SET DEFAULT
)
INSERT INTO Orders VALUES(10001, 'FRIDA', '20040101')
INSERT INTO Orders VALUES(10002, 'FRIDA', '20040102')
INSERT INTO Orders VALUES(10003, 'BILLY', '20040101')
INSERT INTO Orders VALUES(10004, 'BILLY', '20040103')
INSERT INTO Orders VALUES(10005, 'GNDLF', '20040104')
INSERT INTO Orders VALUES(10006, 'GNDLF', '20040105')
GO

Customers 테이블의 레코드가 DELETE, UPDATE 될 때 어떻게 작동되는지 보도록 하자.
DELETE FROM Customers
WHERE customerid = 'FRIDA'
UPDATE Customers
SET customerid = 'DOLLY'
WHERE customerid = 'BILLY'

Orders 테이블 내용.
FRIDA가 삭제되면서 Orders의 FRIDA값이 NULL값으로 변경되었더.(SET NULL)
DOLLY가 수정되면서 Orders의 DOLLY값이 DEFAULT값인 DUMMY로 변경되었다.(SET DEFAULT)

orderid customerid orderdate
----------------------------------------
10001 NULL 2004-01-01 00:00:00.000
10002 NULL 2004-01-02 00:00:00.000
10003 DUMMY    2004-01-01 00:00:00.000
10004 DUMMY    2004-01-03 00:00:00.000
10005 GNDLF    2004-01-04 00:00:00.000
10006 GNDLF    2004-01-05 00:00:00.000

5. 강화된 성능과 에러 핸들링

◆ BULK Rowset Provider
BULK는 파일에 접근하는 OPENROWSET에서 명시하여 사용되는 SQL 2005의 새로운 Provider이다.
파일에서 대량의 데이터를 읽어 내는 데 사용되는데 자세한 사용패턴은 다음과 같다.

OPENROWSET
( { 'provider_name'
, { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
, { [ catalog. ] [ schema. ] object | 'query' }
| BULK 'data_filename',
{FORMATFILE = 'format_file_path' [, <bulk_options>] |
SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB}
}
)

<bulk_options> ::=
[ , CODEPAGE = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , ROWS_PER_BATCH = 'rows_per_batch']
[ , MAXERRORS = 'max_errors']
[ , ERRORFILE ='file_name']
}
)

읽기 예문은 다음과 같다.
SELECT col1, col2, col3
FROM OPENROWSET(BULK 'c:\temp\textfile1.txt',
FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)
읽어서 테이블에 추가하는 예문은 다음과 같다.
INSERT INTO MyTable WITH (IGNORE_CONSTRAINTS)
SELECT col1, col2, col3
FROM OPENROWSET(BULK 'c:\temp\textfile1.txt',
FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)
그 외 상세한 내용은 원문을 참조하거나 MSDN을 참조하기 바란다.

◆ 예외 핸들링
SQL 2005에서 드뎌 TRY...CATCH 예외처리 구문을 지원하게 되었다.
SQL 2000에서는 매 구문마다 에러를 체크한 후 GOTO 구문을 사용해 분기하여 예외처리를 하였다.
게다가 데이터타입 변경(Convert)에러 같은 것은 배치작업을 종료하게 했고, T-SQL에서는 이 에러를 잡아내지도 못했다.
SQL 2005에서는 배치작업을 비정상종료케 했던 에러들을 잡아내서 처리할 수 있다.

예제를 보도록하자.
CREATE TABLE Employees
(
empid INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT NULL,

other columns

CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT CHK_Employees_empid CHECK(empid > 0),
CONSTRAINT FK_Employees_Employees
FOREIGN KEY(mgrid) REFERENCES Employees(empid)
)

예외를 잡아내려는 문장들은 BEGIN TRY/END TRY로 감싼다.
예외가 발생했을 때 처리 문장들은 BEGIN CATCH/END CATCH로 감싼다.

Employees테이블에 1번 사원을 추가해 보자.
BEGIN TRY
INSERT INTO Employees(empid, empname, mgrid)
VALUES(1, 'Emp1', NULL)

PRINT 'After INSERT.'
END TRY
BEGIN CATCH
PRINT 'INSERT failed.'

perform corrective activity

END CATCH
처음 실행하면 After INSERT가 정상출력된다.
두번째 실행하면 INSERT Failed가 출력된다.
예외가 발생해 CATCH 블럭으로 분기되면 사용자 어플리케이션에는 에러를 전달하지 않는다.
에러를 전달하기 위해서 RAISERROR구문을 사용하던지 결과셋을 반환하는 쿼리를 작성해주어야 한다.
에러정보는 ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(),ERROR_STATE()함수를 사용할 수 있다.
위 함수는 CATCH 블럭으로 올 때 발생한 에러정보를 리턴하므로, 반복사용해도 같은 정보를 출력해 준다.
반면 @@ERROR는 다르다. @@ERROR는 최종 문장이 실행되었을 때 정보가 변경되므로,
@@ERROR대신 위 함수를 사용하는 것이 바람직하다.
저장프로시저,트리거등의 루틴에서 처리되지 않은 에러가 발생할 경우 상위 수준의 CATCH 블럭으로
제어가 넘어간다. 좀 더 자세한 정보는 원문이나 MSDN을 참조 또는 직접 테스트 해보도록 합시다.

다음 예문을 실행해 보면서 확실히 개념을 익혀두자.
PRINT 'Before TRY...CATCH block.'
BEGIN TRY
PRINT ' Entering TRY block.'
INSERT INTO Employees(empid, empname, mgrid) VALUES(2, 'Emp2', 1)
PRINT ' After INSERT.'
PRINT ' Exiting TRY block.'
END TRY

BEGIN CATCH
PRINT ' Entering CATCH block.'
IF ERROR_NUMBER() = 2627
BEGIN
PRINT '    Handling PK violation...'
END
ELSE IF ERROR_NUMBER() = 547
BEGIN
PRINT '    Handling CHECK/FK constraint violation...'
END
ELSE IF ERROR_NUMBER() = 515
BEGIN
PRINT '    Handling NULL violation...'
END
ELSE IF ERROR_NUMBER() = 245
BEGIN
PRINT '    Handling conversion error...'
END
ELSE
BEGIN
PRINT '    Handling unknown error...'
END
PRINT '    Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
PRINT '    Error Message: ' + ERROR_MESSAGE()
PRINT '    Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))
PRINT '    Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10))
PRINT ' Exiting CATCH block.'
END CATCH
PRINT 'After TRY...CATCH block.'

TRY블럭에서 트랜잭션을 사용할 경우, CATCH블럭에서 트랜잭션의 상태를 조사할 필요가 있다.
SQL 2005에서는 XACT_STATE() 함수를 제공한다.
XACT_STATE()는 트랜잭션의 상태를 리턴한다. 리턴 값들은 0, -1, 1 값이다.
0값을 리턴할 경우 트랜잭션이 오픈되지 않았음(오픈된 트랜잭션 없음)을 의미한다.
1값을 리턴할 경우 트랜잭션이 오픈되었으면 ROLLBACK되거나 COMMIT되야함을 의미한다.
-1값을 리턴할 경우 트랜잭션이 오픈되었지만 COMMIT할 수 없음을 의미한다.(uncommitable state)
즉, ROLLBACK만 허용되며 반드시 롤백을 하지 않을 경우 열려진 LOCK들은 유지되므로 데이터를 오직
읽기만 할 수 있다. 반드시 ROLLBACK을 해서 트랜잭션을 종료해주어야 한다.
XACT_STATE() 예문은 다음과 같다.
BEGIN TRY
BEGIN TRAN
INSERT INTO Employees(empid, empname, mgrid) VALUES(3, 'Emp3', 1)

other activity

COMMIT TRAN
PRINT 'Code completed successfully.'
END TRY
BEGIN CATCH
PRINT 'Error: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' found.'
IF (XACT_STATE()) = -1
BEGIN
PRINT 'Transaction is open but uncommittable.'

...investigate data...

ROLLBACK TRANSACTION can only ROLLBACK

...handle the error...

END
ELSE IF (XACT_STATE()) = 1
BEGIN
PRINT 'Transaction is open and committable.'

...handle error...

COMMIT TRANSACTION or ROLLBACK
END
ELSE
BEGIN
PRINT 'No open transaction.'

...handle error...

END
END CATCH

6. 기타

◆ TOP 기능 향상
TOP 구문은 오직 SELECT 결과셋에서 상위 몇 개 또는 몇 퍼센트의 데이터만을 출력해주는 기능으로 동작하며,
SQL 2000까지는 오직 상수만을 매개변수로 지정할 수 있었다.
SQL 2005에서는 다음 두 개의 기능이 향상되었다.
(1) 매개변수로 변수나 서브쿼리를 지정할 수 있다.
(2) DELETE, UPDATE, INSERT 쿼리에서도 동작한다.
사용패턴은 다음과 같다.

SELECT [TOP (<expression>) [PERCENT] [WITH TIES]]
FROM <table_name>...[ORDER BY...]
DELETE [TOP (<expression>) [PERCENT]] FROM <table_name> ...
UPDATE [TOP (<expression>) [PERCENT]] <table_name> SET ...
INSERT [TOP (<expression>) [PERCENT]] INTO <table_name> ...

보시다시피 expression은 소괄호()로 묶여야 한다. 상수일 경우에는 관계없다.
또한 PERCENT 옵션을 붙이지 않을 경우 expression 결과값은 bigint로 변환된다.
PERCENT 옵션을 붙일 경우 float이나 0~100의 범위로 변환된다.

변수를 사용하는 예문은 다음과 같다.
USE AdventureWorks
DECLARE @n AS BIGINT
SET @n = 2
SELECT TOP(@n) *
FROM Purchasing.PurchaseOrderHeader
ORDER BY OrderDate DESC

서브쿼리를 사용하는 예문은 다음과 같다.
USE AdventureWorks
SELECT TOP(SELECT
COUNT(*)/DATEDIFF(month, MIN(OrderDate), MAX(OrderDate))
FROM Purchasing.PurchaseOrderHeader) *
FROM Purchasing.PurchaseOrderHeader
ORDER BY OrderDate DESC

SET ROWCOUNT 구문은 배치쿼리 전반에 걸쳐 모든 문장에 적용된다.
주로 대용량 트랜잭션을 작은 트랜잭션으로 쪼개어 실행할 때 사용된다.
SET ROWCOUNT 1000
DELETE FROM BigTable WHERE datetimecol < '20000101'
WHILE @@rowcount > 0
DELETE FROM BigTable WHERE datetimecol < '20000101'
SET ROWCOUNT 0
하지만 SQL 2005에서는 성능상의 문제로 TOP 옵션을 사용하는 것이 OPTIMIZER가
더 효율적인 실행계획을 세우도록 돕기때문에 TOP 옵션을 사용할 것을 권장한다.

◆ OUTPUT 절, 갱신 쿼리에 의한 결과셋을 테이블 변수에 적용하기
INSERT, UPDATE, DELETE 문에 의해 적용된 로우셋을 테이블변수에 추가하는 기능이다.
사용패턴은 다음과 같다.
OUTPUT <dml_select_list> INTO @table_variable

사용예제를 보자.
DECLARE @DeletedOrders TABLE
(
orderid INT,
orderdate DATETIME,
empid INT,
custid VARCHAR(5),
qty INT
)
WHILE 1=1

BEGIN
BEGIN TRAN
DELETE TOP(5000) FROM Orders
OUTPUT deleted.* INTO @DeletedOrders
WHERE orderdate < '20030101'
INSERT INTO OrdersArchive
SELECT * FROM @DeletedOrders
COMMIT TRAN
DELETE FROM @DeletedOrders
IF @@rowcount < 5000
BREAK
END

◆ 동적 컬럼을 위한 MAX 명시자
예제를 보면 이해하기 쉽다.
CREATE TABLE CustomerData
(
custid INT NOT NULL PRIMARY KEY,
txt_data VARCHAR(MAX) NULL,
ntxt_data NVARCHAR(MAX) NULL,

binary_data VARBINARY(MAX) NULL
)
MAX는 가변길이 데이터 타입 컬럼의 경우 사용가능하다.
컬럼, 변수, 매개변수, 트리거등에도 사용할 수 있다.
WRITE메소드를 추가로 지원해 해당컬럼의 특정 오프셋을 지정해 값을 변경할 수도 있다.
UPDATE table_name
SET column_name.WRITE(@chunk, @offset, @len)
WHERE ...
@offset 위치에서 @len길이까지 지우고 @chunk를 삽입한다. @offset은 0(zero)기반이다.
즉, @offset = 0 일 경우 첫 문자를 가리킨다.
@chunk = NULL 일 경우 @len은 무시된다. 그리고 @offset 위치에서 값이 잘리게 된다.
다음 예문은 28의 위치에서 끝까지 잘라버린다.
UPDATE CustomerData
SET txt_data.WRITE(NULL, 28, 0)
WHERE custid = 102
@len = NULL일 경우 @offset에서 끝까지 잘라버린다. @chunk가 적용된다.
UPDATE CustomerData
SET txt_data.WRITE('102', 9, NULL)
WHERE custid = 102
@offset = NULL일 경우 @len은 무시되고, @chunk가 새로이 (끝까지) 적용된다.
UPDATE CustomerData
SET txt_data.WRITE(' is discontinued', NULL, 0)
WHERE custid = 102

◆ XML과 XQuery
SQL 2005에서는 새로이 xml 데이터 타입이 도입되었다.
PRIMARY KEY, UNIQUE, FOREIGN KEY제약조건을 적용할 수 없다.
예제를 보자.
USE AdventureWorks
DECLARE @x AS XML
SET @x = (SELECT C.CustomerID, O.SalesOrderID
FROM Sales.Customer C
JOIN Sales.SalesOrderHeader O
ON C.CustomerID=O.CustomerID
ORDER BY C.CustomerID
FOR XML AUTO, TYPE)
SELECT @x

<C CustomerID="1"><O SalesOrderID="43860" /><O SalesOrderID="44501" /><O SalesOrderID="45283" /><O SalesOrderID="46042" /></C><C CustomerID="2"><O SalesOrderID="46976" /><O SalesOrderID="47997" /><O SalesOrderID="49054" /><O SalesOrderID="50216" /><O Sales

xml 변수 @x에 저장된 SELECT쿼리가 반환하는 로우셋을 XML형태로 반환한다.

◆ DDL Triggers와 DDL과 시스템 이벤트 통지 관련 정보는 원문이나 MSDN을 참조하기 바란다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

(T-SQL) 서버 측 동적 쿼리에서의 탈출

데이타베이스/MSSQL 2008/12/13 04:03

(T-SQL) 서버 측 동적 쿼리에서의 탈출

김정선

삼성 SDS 멀티캠퍼스

전임 강사

Microsoft SQL Server MVP

요약.

수년 전부터 MS 플랫폼에서 SQL Server를 연동한 프로젝트 수행 시 개발 방법론의 중요한 한 축으로 저장 프로시저의 적극적인 활용이 일반화되고 있다. 그로 인한 부작용 중에 하나는 이전에 응용 프로그램 아키텍처 상의 프레젠테이션 계층 혹은 비즈니스 서비스 계층에서 문자열 데이터로 동적으로 구성하던 쿼리가 저장 프로시저를 통해서 서버 측에서 구성이 된다는 것이다. “서버 측 동적 쿼리”(이하 동적 쿼리로 표기)라고 표현하는 이러한 동적 T-SQL 또는 일괄처리는 성능상의 문제를 내포함과 동시에, 개발 생산성과 관리 및 유지 보수의 용이성 측면에서 많은 고려 사항을 포함하고 있다. 그럼에도 아직 많은 개발자들이 막연히 문제 해결 중심적인 사고로 동적 쿼리를 선호하는 경향이 있다. 이 글에서는 동적 쿼리를 비 동적 쿼리(이하. 정적 쿼리로 표기)로 변환하거나 대체할 수 있는 기초적인 사례들을 살펴볼 것이다. 불필요한 동적 쿼리 사용으로 인한 성능상의 문제 해결과 관리 및 유지 보수 용이성을 얻는데 미약하나마 이 글이 도움이 되기를 바란다.

- 동적 쿼리

- 동적 쿼리 대 정적 쿼리(비 동적 쿼리)

- 탈출을 위한 시작, 사례별 예제

- 마치면서...

동적 쿼리

일반적으로 언급하는 동적 쿼리란, 코드의 실행 시점에 SQL 문이 동적으로 구성되고 실행되는 쿼리를 말한다. ODBC를 사용하는 C 언어 계열에서, SQL 문을 문자 배열에서 동적으로 구성한 뒤 이를 ODBC API, SQLPrepare 혹은 SQLExecDirect 함수로 전달하거나 ADO를 사용하는 Visual Basic, ASP 등에서 SQL 문을 문자열로 동적으로 구성한 뒤 이를 Connection 오브젝트나 Recordset 오브젝트를 통해서 호출하거나, 또는 다양한 .NET Data Provider를 제공하는 .NET 호환 언어에서 역시 문자열로 동적으로 구성한 SQL 문을 DataAdapter, DataReader 등의 오브젝트를 통해서 호출하는 경우가 이에 해당한다.

참고. SQL Server .NET Data Provider 의 경우엔 동적 쿼리를 호출하더라도 순수한 쿼리 형태로 호출되지 않고, SQL Server의 확장 저장 프로시저, sp_executesql 을 통해서 자동 변환이 된다. 이는 Ad hoc 쿼리와 Precompiled 쿼리(저장 프로시저, 트리거 등)의 중간 형태인, Parameterized 쿼리의 생성 방법 중의 하나로써 .NET 기반 데이터베이스 응용프로그램 개발자들이 기본적으로 숙지해야 할 시스템 프로시저 중의 하나입니다.

동적 쿼리를 나타내는 용어도 다양하다, 원시적으로는 Embedded SQL for C(ESQL/C) 에서 사용된 용어를 시작으로 플랫폼이나 개발 언어에 따라 Dynamic Query, Hard Coded Query, Ad hoc Query 등으로 불려지고 있다.

서버 측 동적 T-SQL 혹은 동적 일괄 처리(Batch)

이전의 C, C++, Visual Basic, ASP 등에서 동적 쿼리의 사용은 일반적이었다. 물론 여전히 많은 응용 프로그램과 프로젝트에서 동적 쿼리 형식이 사용되고 있다. 그러나 현재 기본적으로 권장하는 SQL Server 데이터베이스 응용 프로그램의 개발 방법론에서는 서버 측의 T-SQL 쿼리 오브젝트(저장 프로시저, 사용자-정의 함수, 트리거, 등)를 사용하는 것이다. 이는 수년 전 IIS 기반의 ASP를 사용한 웹 응용 프로그램의 개발이 일반화되면서 ASP 개발자들이 이를 수용, 프로젝트에 적용하기 시작했다 - 사실, 저장 프로시저의 적극적인 사용을 권장한 것은 꽤 오래된 이야기이지만 국내에서 활발하게 적용되기 시작한 것은 얼마 되지 않는다.

이러한 방법론을 통해 얻을 수 있는 이득이 많은 반면, 피하기 어려운 문제점 또한 가지고 있었다. 그 중에 하나가 바로 서버 측에서의 동적 쿼리의 필요성이었다. 런타임 시 쿼리를 문자열 상수와 변수로 구성하고, 이를 통해 완성된 최종 쿼리를 호출하던 이전의 방식에서는 런타임 시 조건에 따라 다양한 쿼리를 구성할 수 있는 편리함이 제공된 반면 저장 프로시저, 트리거 등에서 런타임 시 쿼리를 동적으로 변경하는 것은 상대적으로 쉬운 문제가 아니었다. 가장 흔히 접하게 되는 몇 가지 예제를 보자:

- SELECT @컬럼명 FROM table

- SELECT * FROM table WHERE @컬럼명 LIKE @표현식

- SELECT * FROM table WHERE column IN (@표현식)

어떻게 해결 할까? SQL Server 2000 버전에서 저장 프로시저, 트리거, 혹은 스크립트 내에서 런타임 시에 쿼리를 동적으로 구성하기 위한 방법으로 다음 두 가지가 주로 사용된다.

- sp_executesql 확장 프로시저를 사용해서 유니코드 문자열을 실행. 동적 쿼리 구문 내에 매개변수를 선언하고 연동하는 것이 가능하며, 이를 통해 얻을 수 있는 추가 이득이 있다.

- EXECUTE 문을 사용해서 문자열을 실행.

참고. 하나 이상의 T-SQL 쿼리를 하나의 일괄처리(Batch) 범위 내에서 동적으로 구성할 수 있기 때문에 동적 T-SQL 혹은 동적 일괄처리라고 부르는 것이 보다 가까운 표현이 될 것이며, 온라인 설명서에도 이와 같이 언급하고 있다.

저장 프로시저 등에서 동적 쿼리 구성은 SQL Server 관련된 온라인 커뮤니티와 뉴스그룹 등에서 가장 활발한 질문/답변 유형 중의 하나이다, 두드러진 변화는 대략 1년 전부터인 것으로 기억이 난다. 그 만큼 많은 코드에서 적용되고 있다는 것을 암시하지만, 문제는 이러한 접근 방법이 또 다른 문제를 야기한다는 것이며, 그 사실을 잘 알지 못한 채 불필요한 상황에서까지 과도하게 사용하고 있다는 것이 더욱 큰 문제에 해당한다. 저장 프로시저 등을 사용해서 얻을 수 있는 성능 상의 이득을 반감시키고, 보안 위험, 관리 상의 어려움 등 여러 가지 문제가 유발된다.

동적 쿼리 대 정적 쿼리(비 동적 쿼리)

응용 프로그램 아키텍처 상에서 적용되는 계층에 관계없이, 동적 쿼리의 과용은 여러 가지 문제점들을 가지고 있다. 기본적으로 실행 시마다 컴파일을 반복하게 됨으로써 PreCompiled/Procedure Cache 재사용을 저해함으로써 발생하는 성능 상의 문제(여기서, SQL Server의 Auto Parameterization/Prepared Cache 재사용성에 대한 특징은 따로 언급을 하지 않을 것이다.)를 포함해서, Ownership Chain 권한 처리 문제와 SQL Injection (문자열 쿼리 내에 또 다른 쿼리를 삽입해서 의도하지 않는 동작을 유도하는 방식)등에 관련된 보안 상의 문제, 공통 모듈에 대한 관리 및 배포에 대한 어려움 등을 예로 들 수 있다. 이는 이미 오래 전부터 많은 전문가들에 의해 잘 알려진 사실 들이다.

참고. 이에 관련된 좋은 문서가 하나 있다, Microsoft SQL Server MVP 이기도 한 Erland sommarskog 가 쓴 글, “The curse and blessings of dynamic SQL” 을 읽어보기 바란다. 그리고 이 글은 한글 버전도 제공된다. 또한 그의 다른 좋은 글 들도 도움이 될 것이다.

동적 쿼리가 가지고 있는 여러 가지 문제점은, 저장 프로시저를 사용하도록 권장하는 이유들과 자연스럽게 연결된다. 이제는 일상적인 논쟁거리로, SQL Server 개발자들의 술자리 메뉴 중의 하나가 되었지만, “동적 쿼리에서의 탈출”이라는 사뭇 거창한 주제를 강조하기 위해서라도 다시 한 번 저장 프로시저를 사용하는 이유를 간단하게 정리해 보자:

- 실행 계획 Caching 을 통한, 성능 이득

- Network Traffic 최소화

- 출력 Parameter, Return 값 사용

- Ownership Chain 을 통한 권한 처리, SQL Injection 차단 등의 보안 기능

- 업무 논리의 캡슐화, 모듈화

- SQLXML 3.0 이후 릴리스에서 XML WebService 노출 기능

- 이 외에도 적용 방법과 구성에 따라 추가 이득을 얻을 수 있다.

저장 프로시저가 정답은 아니다. 또한 동적 쿼리를 무조건 사용하지 말라는 것도 아니다. 이들은 모두 우리가 얻을 수 있는 해답의 한 가지일 뿐이며, 무엇이 가장 정답에 가까운 해답인지에 따라서 그 이득의 유무와 정도가 달라는 것이다. 어느 것이 정답에 가까운지를 판단하기에 앞서 어떤 해답이 있는지를 알고 있는 것이 기본일 것이다 ? 여러분은 주관식과 객관식 중 어느 것을 좋아하는가?

동적 쿼리가 가지고 있는 문제점을 저장 프로시저 안에 재현하는 것은, 결국 위에서 언급한 저장 프로시저 사용에 대한 이득을 저해하는 요소가 되는 것이다. 현재 저장 프로시저의 적지 않은 코드가 동적 쿼리 형태로 작성되고 있으며, 그들 중의 상당 부분은 정적 쿼리로 재 작성할 수가 있다. 즉, 동적 쿼리를 불필요하게 남발하고 있는 코드가 많다는 것이다. 이는 객관식 보기가 충분하지 않다는 것이기도 하다. 다음에서 이러한 사례들을 살펴볼 것이며, 여러분이 가지고 있는 객관식 보기 유형에 추가하길 바란다. 이를 통해 SQL Server 저장 프로시저가 제공하는 본래의 이득에 충실한 코드를 개발하는데 도움이 될 것이다.

탈출을 위한 시작, 사례별 예제

서버 측 동적 T-SQL을 정적 쿼리로 변환하는 방법에 대한 다양한 사례가 SQL Server 전문 웹사이트, 뉴스그룹 등을 통해서 알려져 왔으면 지금도 다양한 사례가 논의되고 개발되고 있다. 여기서 기초적인 몇 가지 사례를 살펴보자. 여기에 기록한 사례들은 주로, Robert Marda 가 쓴 글, “How Dynamic SQL Can Be Static SQL”, 그리고 Erland sommarskog 의 다른 글 및 뉴스그룹 등의 자료 들을 정리한 것이다.

예제에 대한 자세한 설명은 제외했다. 이면의 부족함도 있지만, 대신 동적 쿼리 형식과 이를 대체한 정적 쿼리를 비교해서 기록했으므로, 코드를 보는 것으로도 충분히 설명을 대체할 수 있을 것이라 본다. 그리고, 예제는 모두 SQL Server의 Northwind 데이터베이스를 사용한다.

- 예제 1. 동적 WHERE 조건자 컬럼

동적 쿼리 형식

SELECT * FROM table WHERE @컬럼명 = @표현식

정적 쿼리

DECLARE @column varchar(10), @value varchar(20)

SET @Column = 'Last'

SET @value = 'Full%'

SELECT *

FROM dbo.employees

WHERE (CASE @column WHEN 'Last' THEN LastName

WHEN 'First' THEN FirstName

WHEN 'Title' THEN Title

ELSE @value

END) LIKE @value

- 예제 2. 동적 SELECT 절

동적 쿼리 형식

SELECT

{ @컬럼1, @컬럼2, @컬럼3

| @컬럼4, @컬럼1, @컬럼5

| , ... }

FROM table

정적 쿼리

DECLARE @column varchar(10)

SET @Column = 'title'

SELECT EmployeeID,

CASE @column

WHEN 'Name' THEN LastName

WHEN 'Title' THEN Title

ELSE LastName

END AS Column1,

CASE @column

WHEN 'Name' THEN FirstName

WHEN 'Title' THEN LastName

ELSE CAST(BirthDate as varchar(20))

END AS Column2,

CASE @column

WHEN 'Title' THEN CAST(HireDate as varchar(20))

ELSE ''

END AS Column3

FROM dbo.employees

WHERE EmployeeID < 4

- 예제 3. 동적 Order By 절 (컬럼명 만 유동적인 경우)

동적 쿼리 형식

SELECT * FROM table

ORDER BY @컬럼명

정적 쿼리

DECLARE @OrderBy varchar(10)

SET @OrderBy = 'LastName'

SELECT *

FROM dbo.employees

WHERE EmployeeID < 4

ORDER BY

(CASE @OrderBy

WHEN 'LastName' THEN LastName

WHEN 'Title' THEN Title

END) ASC

- 예제 4. 동적 정렬 기준 ( 정렬 기준 & 컬럼명 모두 유동적인 경우 )

동적 쿼리 형식

SELECT * FROM table

ORDER BY @컬럼명 @정렬기준

정적 쿼리

DECLARE @OrderBy varchar(10), @Sequence varchar(4)

SET @OrderBy = 'LastName'

SET @Sequence = 'DESC'

SELECT *

FROM dbo.employees

WHERE EmployeeID < 4

ORDER BY

(CASE @Sequence

WHEN 'ASC' THEN CASE @OrderBy

WHEN 'LastName' THEN LastName

WHEN 'Title' THEN Title END

END) ASC,

(CASE @Sequence

WHEN 'DESC' THEN CASE @OrderBy

WHEN 'LastName' THEN LastName

WHEN 'Title' THEN Title END

END) DESC

- 예제 5. 동적 GROUP BY, HAVING

동적 쿼리 형식

SELECT @컬럼명

FROM table

GROUP BY @컬럼명

HAVING @HAVING-조건식

정적 쿼리

DECLARE @column varchar(10), @ActivateCount bit, @Count int

SET @Column = 'name' -- Group By 기준 컬럼

SET @ActivateCount = 1-- Having 절에 적용될 컬럼

SET @Count = 4 -- Having 절에 비교될 값

SELECT

CASE @column

WHEN 'Name' THEN 'Number of Employees'

WHEN 'Title' THEN 'Number of Titles'

END AS Type,

CASE @column

WHEN 'Name' THEN COUNT(employeeID)

WHEN 'Title' THEN COUNT(Title)

END AS QTY,

CASE @column

WHEN 'Name' THEN Country

WHEN 'Title' THEN Title

END AS GroupBy

FROM dbo.employees

GROUP BY (CASE @column

WHEN 'Name' THEN Country

WHEN 'Title' THEN Title

END)

HAVING (CASE @column + LTRIM(STR(@ActivateCount))

WHEN 'Name1' THEN COUNT(employeeID)

WHEN 'Title1' THEN COUNT(Title)

ELSE @Count + 1

END) > @Count

- 예제 6. 동적 조인 컬럼 ( 조인 컬럼을 동적으로 가져가는 경우)

동적 쿼리 형식

SELECT *

FROM table1

INNER JOIN table2 ON @테이블.컬럼명 = table2.Column

INNER JOIN table3 ON ...

정적 쿼리

-- 테스트를 위한 임시 테이블

CREATE TABLE #RegionSupervisors (City varchar(20), employeeID int)

INSERT INTO #RegionSupervisors (City, EmployeeID)

SELECT 'Bern', 1 UNION SELECT 'Geneve', 1

UNION SELECT 'Koln', 1 UNION SELECT ' Albuquerque ', 5

UNION SELECT 'Seattle', 5 UNION SELECT ' Redmond ', 5

UNION SELECT 'Kirkland', 5 UNION SELECT ' London ', 4

UNION SELECT 'Cowes', 4 UNION SELECT 'Colchester', 4

-- @supervisors에 따라서, 조인 컬럼을 변경하는 경우

DECLARE @supervisors varchar(10)

SET @Supervisors = 'employee'

SELECT CustomerID, OrderDate, ShipCity, e.LastName AS [Assigned To]

, e.City AS [Employee Home Office]

, CASE @Supervisors

WHEN 'employee' THEN 'employee supervisor: ' + se.LastName

WHEN 'order' THEN 'order supervisor: ' + se.LastName END AS [Region Supervisor]

FROM dbo.Orders o

INNER JOIN dbo.Employees e ON o.EmployeeID = e.EmployeeID

INNER JOIN dbo.#RegionSupervisors s ON (CASE @Supervisors

WHEN 'employee' THEN e.City

WHEN 'order' THEN o.ShipCity END) = s.City

INNER JOIN dbo.Employees se ON se.EmployeeID = s.EmployeeID

- 예제 7. 동적 비교 연산자 ( 대/소 비교를 동적으로 가져가는 경우 )

동적 쿼리 형식

SELECT * FROM table

WHERE column @비교연산자 @표현식

정적 쿼리

DECLARE @Sign char(1), @date datetime

SET @Sign = '>'

SET @date = '19630703'

-- 1-1.

SELECT *

FROM dbo.employees

WHERE

(CASE @Sign

WHEN '>' THEN BirthDate

WHEN '<' THEN @date END) > (CASE @Sign WHEN '>' THEN @date

WHEN '<' THEN BirthDate END)

- 예제 8. 동적 조건자 ( 조건자 자체를 동적으로 구성하는 경우 )

동적 쿼리 형식

SELECT * FROM table

WHERE @조건자1 AND @조건자2 , ...

정적 쿼리

DECLARE @LastName varchar(30), @FirstName varchar(30)

SET @LastName = 'd'

SET @FirstName = 'a'

SELECT *

FROM dbo.employees

WHERE EmployeeID < 4

WHERE

(CASE WHEN @LastName <> '' THEN LastName

WHEN @LastName = '' THEN 'Eliminate' END) Like

(CASE WHEN @LastName <> '' THEN @LastName + '%'

WHEN @LastName = '' THEN 'Eliminate' END)

AND

(CASE WHEN @FirstName <> '' THEN FirstName

WHEN @FirstName = '' THEN 'Eliminate' END) Like

(CASE WHEN @FirstName <> '' THEN @FirstName + '%'

WHEN @FirstName = '' THEN 'Eliminate' END)

- 예제 9. 동적 계단식 조건자 ( 다양한 조건식을 계단식으로 구성하는 경우 )

동적 쿼리 형식

SELECT * FROM table

WHERE @조건자1 [ AND @조건자2 [ AND @조건자3 [, ... ] ] ]

정적 쿼리

DECLARE @LastName varchar(30), @FirstName varchar(30), @Country varchar(5)

, @City varchar(20), @Title varchar(30)

SET @Country = 'usa '

SET @ City = 'k'

SET @LastName = ''

SET @FirstName = ''

SET @Title = 's'

-- 최종 결과 확인용 쿼리.

SELECT *

FROM dbo.employees

WHERE Title LIKE @Title + '%'

AND Country LIKE @Country

AND City LIKE '%' + @City + '%'

-- 1. Tilte 존재 시 -> Country 존재 시 -> City 존재 시

SELECT *

FROM dbo.employees

WHERE

CASE WHEN @Title <> '' THEN Title

WHEN @Country <> '' THEN Country

WHEN @City <> '' THEN City

WHEN @LastName <> '' THEN LastName

WHEN @FirstName <> '' THEN FirstName END LIKE

CASE WHEN @Title <> '' THEN @Title + '%'

WHEN @Country <> '' THEN @Country

WHEN @City <> '' THEN '%' + @City + '%'

WHEN @LastName <> '' THEN @LastName + '%'

WHEN @FirstName <> '' THEN @FirstName + '%' END

AND

CASE WHEN @Title <> '' THEN

CASE WHEN @Country = '' THEN 'Eliminate' ELSE Country END

WHEN @Country <> '' THEN

CASE WHEN @ City = '' THEN 'Eliminate' ELSE City END

WHEN @City <> '' THEN

CASE WHEN @LastName = '' THEN 'Eliminate' ELSE LastName END

WHEN @LastName <> '' THEN

CASE WHEN @FirstName = '' THEN 'Eliminate' ELSE FirstName END

WHEN @LastName = '' THEN 'Eliminate'

WHEN @FirstName = '' THEN 'Eliminate' END LIKE

CASE WHEN @Title <> '' THEN

CASE WHEN @Country = '' THEN 'Eliminate' ELSE @Country END

WHEN @Country <> '' THEN

CASE WHEN @ City = '' THEN 'Eliminate' ELSE @City + '%' END

WHEN @City <> '' THEN

CASE WHEN @LastName = '' THEN 'Eliminate'

ELSE '%' + @LastName + '%' END

WHEN @LastName <> '' THEN

CASE WHEN @FirstName = '' THEN 'Eliminate'

ELSE '%' + @FirstName + '%' END

WHEN @LastName = '' THEN 'Eliminate'

WHEN @FirstName = '' THEN 'Eliminate' END

AND

CASE WHEN @Title <> '' THEN City

WHEN @Country <> '' THEN LastName

WHEN @City <> '' THEN FirstName

WHEN @LastName = '' THEN 'Eliminate'

WHEN @LastName <> '' THEN 'Eliminate'

WHEN @FirstName <> '' THEN 'Eliminate' END LIKE

CASE WHEN @Title <> '' THEN '%' + @City + '%'

WHEN @Country <> '' THEN @LastName + '%'

WHEN @City <> '' THEN '%' + @FirstName + '%'

WHEN @LastName = '' THEN 'Eliminate'

WHEN @LastName <> '' THEN 'Eliminate'

WHEN @FirstName <> '' THEN 'Eliminate' END

- 예제 10. 동적 UPDATE

동적 쿼리 형식

UPDATE table

SET @컬럼명 = @표현식

정적 쿼리

DECLARE @colname varchar(128)

SET @colname = 'orderdate'

UPDATE dbo.orders

SET orderdate = CASE @colname WHEN 'orderdate' THEN getdate()

ELSE orderdate END,

requireddate = CASE @colname WHEN 'requireddate' THEN getdate()

ELSE requireddate END

WHERE orderid = 10248

go

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

SQL쿼리 - 동적 저장프로시저를 정적으로 (SQL 인잭션 방지)

데이타베이스/MSSQL 2008/12/13 04:01

CREATE proc dbo.SearchBasic
(
@searchfield varchar(25),
@searchquery varchar(25)
)
as
set @searchquery = '%' + @searchquery + '%'
select * from basic where
(
  case @searchfield
   when 'name' then name
   when 'title' then title
  else
   @searchquery
  end
)
like
@searchquery

GO

[출처] SQL쿼리 - 동적 저장프로시저를 정적으로 (SQL 인잭션 방지)|작성자 수수봉봉

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

동적 쿼리에서의 text 사용법.

데이타베이스/MSSQL 2008/12/06 01:07

=======================================================
CREATE PROCEDURE DBO.SP_BOARD_INSERT
@table varchar(50),
@tid int,
@subject varchar(200),
@content text,

@uid varchar(20),
@name varchar(20),
@email varchar(100)

AS
DECLARE @param nvarchar(500), @SQL nvarchar(2000)

SET @param = '
@tid int,
@subject varchar(200),
@content text,
@uid varchar(20),
@name varchar(20),
@email varchar(100)
'

SET @SQL = '
INSERT '+@table+' ( TID, SUBJECT, CONTENT, USER_NAME, USER_ID, USER_EMAIL)
VALUES(@tid, @subject, @content, @name, @uid, @email) '

EXEC SP_EXECUTESQL @SQL, @param,
@tid = @tid,
@subject = @subject,
@content = @content,
@uid = @uid,
@name = @name,
@email = @email
GO
=======================================================

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

Collation 변경하는법

데이타베이스/MSSQL 2008/11/24 20:14

데이터베이스의 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의 정보를 변경 하시면 됩니다

지식인에서..cuteshim 님의 답변입니다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

TAG collation, MSSQL

Trackback 0, Comment 0

log 데이타 용량 초기화

데이타베이스/MSSQL 2008/09/01 02:23

---------------------------
-- 로그 잘라내기

backup log test with no_log

sp_helpdb test

/*
name ...
----------------...
test ...

name    ...
---------------...
test    ...
test_log ... -- 축소하고자 하는 로그 파일명
*/

---------------------------
-- 로그 파일 축소
use test
go
dbcc shrinkfile (test_log,0)

/*
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7    2    63    63    56    56

(1개 행 적용됨)

DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.
*/

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

TAG MSSQL

Trackback 0, Comment 0

MSSQL 원격 접속 설정

데이타베이스/MSSQL 2008/08/16 00:47

[ 원격 열결이 가능하도록 세팅 ]

1. 시작 - 프로그램 - Microsoft SQL Server 2005 - 구성도구 - SQL 서버 노출 영역 구성
2. 서비스 및 연결에 대한 노출 영역 구성 - "설치된DB" - 데이터베이스 엔진 - 원격연결 - 로컬 및 원격 연결 - TCP/IP및 명명된 파이프 모두 사용 - 적용

3. SQL Browser - 서비스 - 시작유형 - 자동

[ 원격연결 접속 정보를 위한 포트번호 알아내기 ]
1. 시작 - 프로그램 - Microsoft SQL Server 2005 - 구성도구 - SQL Server Configuration Manager
2. SQL Server 2005 네트워크 구성 - "설치된DB" 에 대한 프로토콜
3. TCP/IP - 사용함으로 설정
4. TCP/IP - 속성
5. IP주소 탭 - IPALL - Tcp 포트 - 1433(mssql 기본포트)

(ip 주소란에 실제 주소가 맞는지 확인 틀리다면 설치된 ip로 변경)

[방화벽 설정]

1. 시작 - 제어판 - 보안 센터 - windows 방화벽(xp 기준)

2. 예외 tab - 포트 추가 - 1433 - tcp

3. 같은 방법으로 135 - tcp, 1434 - udp 추가

4. 프로그램 추가 - program filesMicrosoft SQL ServerMSSQL.xsqlserver.exe

- Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe

[네트워크 설정]

1.바탕화면 네트워크 연결 - 속성 - 연결되있는 네트워크 - 속성 - 설치 - 프로토콜 - NWLink IPX/SPX/NetBIOS

[연결 방법]

ip주소,port설치된DB

(주의 보통 port연결시 : 을 많이 사용하는데 : 이 아니라 ,이다)

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

TAG MSSQL

Trackback 0, Comment 0

Invalid object name 'syspublications'.

데이타베이스/MSSQL 2008/07/31 04:00

해당 오류는 시스템 저장 프로시저를 임의로 수정하거나 했을 경우에 발생할 가능성이 많습니다.

만약에 시스템 저장 프로시저를 수정한다면 master.dbo.sysobjects 테이블의 status와 base_schema_ver 컬럼이 시스템에 의해서 업데이트 될것입니다.

하지만 위와같은 오류가 발생한다면 이전으로 값을 돌려야 합니다.

예를 들어서 sp_addmergearticle.를 수정한다면

정상적으로 동작하는 SQL서버에서

SELECT * FROM [master].[dbo].[sysobjects] WHERE name = 'sp_addmergearticle'

를 수행하여 status와 base_schema_ver 값을 확인한후에

UPDATE [master].[dbo].[sysobjects]
SET [status]=-1073741823,
[base_schema_ver]=16
WHERE name = 'sp_addmergearticle'
와 같이 해주면 정상적으로 동작하게 됩니다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

TAG MSSQL, syspublications

Trackback 0, Comment 0

xp_cmdshell 제거

데이타베이스/MSSQL 2008/06/13 06:39

제거방법 --

USE MASTER

EXEC SP_HELPTEXT XP_CMDSHELL

- 여기서 나타난 DLL 이름을 기억해 둡니다. 이것은 나중에 다시 등록할 때 필요함.

- 제거

USE MASTER

EXEC SP_DROPEXTENDEDPROC 'XP_CMDSHELL'

EXEC SP_DROPEXTENDEDPROC 'XP_DIRTREE'

EXEC SP_DROPEXTENDEDPROC 'xp_regdeletekey'
EXEC SP_DROPEXTENDEDPROC 'xp_regenumvalues'

EXEC SP_DROPEXTENDEDPROC 'xp_regread'

EXEC SP_DROPEXTENDEDPROC 'xp_regdeletekey'

- 다시 등록시

USE MASTER

EXEC SP_ADDEXTENDEDPROC 'XP_CMDSHELL', 'XPLOG70.DLL' -- 앞에서 알아냈던 DLL ;이름 입력
EXEC SP_ADDEXTENDEDPROC 'XP_DIRTREE', 'XPSTAR.DLL'

EXEC SP_ADDEXTENDEDPROC 'xp_regdeletekey', 'XPSTAR.DLL'
EXEC SP_ADDEXTENDEDPROC 'xp_regenumvalues', 'XPSTAR.DLL'

EXEC SP_ADDEXTENDEDPROC 'xp_regread', 'XPSTAR.DLL'

EXEC SP_ADDEXTENDEDPROC 'xp_regdeletekey', 'XPSTAR.DLL'

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

MSSQL 기본튜닝

데이타베이스/MSSQL 2008/04/10 11:27

분류명 튜닝 구분 튜닝 – 1
작성자 김현종 날짜 2005-06-20
제목 튜닝 방법 1
간략 내용 제가 사용하는 튜닝 방법에 대해서 정리를 해봤습니다.
프로그램 튜닝 입니다.

DB에서 튜닝을 해야 할 항목은 여러 가지 입니다.
그 중에서 가장 많이 하고 자주하게 되는 DB 프로그램에 대한 튜닝을 하는 부분입니다.
튜닝을 하는 방법 자체 보다는 튜닝을 하기 위해서 문제가 되는 스크립트를 찾는 방법에 대해서
정리를 해봤습니다.
 스크립트로 이벤트 로그 파일 생성
 파일로 생성된 이벤트 로그 파일을 DB Insert
 Insert 된 내용 분석

1. 같이 첨부한 파일에 보시면 UP_StartTrace.sql 와 UP_StopTrace.sql 파일이 같이 들어 있을 것입니다.
이 두 개의 파일을 DB server에서 실행을 하도록 합니다.
그러면 두 개의 SP가 설치 될 것입니다.

두 개의 파일의 내용은 SQL 프로필러에서 보고 저장 할 수 있는 내용을 스크립트 형태로 만들어 실행 할 수 있게 해주는 스크립트입니다.

제가 남기는 스크립트 이외에 스크립트는 이벤트를 찾아보시면 원하시는 내용을 스크립트 파일로 남기 실 수 있을 것입니다.
그리고 실제로 프로필러를 띄우는 것보다는 시스템의 부하가 적게 든다고 합니다.

2. SP를 생성했다면 이제 이벤트 로그가 기본적으로 생성될 위치를 지정합니다.
[실행]
ALTER PROCEDURE up_StartTrace (@TraceFileName NVARCHAR(245) = N'C:\WTT\Trace',
@MaxFileSize BIGINT = 200,
@Duration BIGINT = NULL,
@Reads BIGINT = NULL,
@CPU BIGINT = NULL,
@TextData NVARCHAR(245) = NULL,
@ApplicationName NVARCHAR(245) = NULL
)
붉은 색으로 된 부분이 기본적으로 이벤트 로그가 저장될 위치 입니다.
이 위치를 각자 편리한 위치에 저장 하시면 될 것 같습니다.
전 기본적으로 c:\wtt\trace에 저장 하도록 하겠습니다.

3. 기본 위치 지정까지 끝났다면 이제 UP_StartTrace를 실행합니다.
SP를 실행하면 TraceID가 나오게 되는데 이것을 기억해 두시기 바랍니다.
UP_StopTrace 시 TraceID를 입력해야 하니까여
UP_StartTrace를 계속 실행 시키면 여러 개의 SP가 실행되게 되고 Trace 파일 또한 여러 개 생성하게 됩니다.
아무튼 여러 개를 실행할 수 있기 때문에 어느 SP를 정지할지를 알아야 하기 때문에 TraceID를 넣도록 되어 있으니 기억을 하시기 바랍니다.

4. 이제 UP_StartTrace를 실행 하시고 UP_StopTrace를 사용하여 종료를 하시면
2번에서 지정한 디렉토리 밑에 file이 생긴걸 보실 겁니다.
Trace.trc 파일을 이제 DB에 넣도록 하겠습니다.
[실행]
select *
into pubs.dbo.trace_table_1
from ::fn_trace_gettable(‘c:\wtt\trace\trace.trc’,default)
Trace.trc 파일을 pubs.dbo.trace_table_1의 Table로 저장을 하도록 하겠습니다.
만약 용량이 여러 커서 파일이 여러개 생성이 되셨다면 각각을 이런 방법으로 DB table에 저장을 하셔야합니다. (문의를 해본 결과 방법이 없다고 합니다. 혹시 방법을 아시는 분은 연락을 좀 주시길..)
그리고 실 SERVER 보다는 테스트 SERVER에 같은 장비에 INSERT 하시기 바랍니다.

5. table로 저장을 한 다음 저장된 내용을 분석을 해야 하는데
제가 하는 분석방법은 duration, read, cpu 등의 값을 이용해서 분석을 합니다.
일단 분석이 되려면 입력된 데이터를 결과를 확인 하도록 해야 하니
textdata column의 TYPE을 TEXT에서 VARCHAR(2000) 자리 정도로 변화를 시키고
GROUP BY를 이용하여 조건을 정렬 하도록 합니다.
[실행]
select left(textdata,50), count(*) as ‘실행수’,
avg(duration) '평균 실행', max(duration) '최대 실행' , min(duration) '최소 실행'
from trace_table_1
group by left(textdata,50)
having max(duration) > 2000
order by count(*) desc
이런 방식으로 데이터를 정렬 합니다.
TEXTDATA 를 50자리로 잘라내서 평균 duration과 max duration, min duration에 대해서 조사를 합니다.
이런 방식으로 read와 cpu 등도 하도록 합니다.

6. 이렇게 정렬된 데이터를 가지고 하는데 일단 기준이 있어야 할 것입니다.
A. 평균 duration이 높으면서 실행수가 많은 것
i. 이런 부분만 개선을 하더라도 DB 시스템이 훨씬 좋아지신걸 느끼실수 있을 것입니다.
B. 최소 duration과 최대 duration이 차이가 많이 나는 것
i. 갑자기 문제가 생긴 부분입니다. 이런 부분은 튜닝으로 해결도 좋지만 문제의
원인 자체를 찾아 내도록 합니다.
C. 이런 식으로 해서 having 절아 duration을 줄여 가도록 합니다.
2초, 1초 이런식으로여

지금까지 제가 프로그램을 튜닝 하는 방법에 대해서 간략하게 정리를 해봤습니다.
이렇게 하시는 분도 있고 다른 방법을 이용하시는 분도 있겠지만
이런 방법을 사용하시지 않는 분이라면
한번 이용해 보시기 바랍니다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

복제필드의 사이즈 제한

데이타베이스/MSSQL 2008/03/28 15:02

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

Some of my text/image values were not replicated. What is the problem?

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

Answer:

There is max text repl size server option. This option is used to specify the maximum size (in bytes) of text and
image data that can be replicated. The default value for this parameter is 65536 bytes.
So, if the size of the text/image data is more than 65536 bytes, this data will not be replicated (if 'max text repl
size' option = 65536 bytes). You can change this option by using the sp_configure system stored procedure.

This is the example to set the max text repl size to 128Kb:

sp_configure 'max text repl size', 131072
GO
RECONFIGURE WITH OVERRIDE
GO

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

TAG MSSQL, 복제

Trackback 0, Comment 0

MSSQL 로그파일 비우는 방법

데이타베이스/MSSQL 2007/11/15 14:46

MSSQL을 쓰다보면 비정상적으로 LOG 파일의 크기가 커져있는것을 볼 수가 있다.

이는 로그관리를 제대로 하지 않았기 때문이지만, 뒤늦게 이를 눈치채고 로그관리를 하려고 해도 이미 커져있는 로그파일은 쉽게 줄어들지 않는다.

이럴때 강제로 로그파일을 줄이는 방법.

BACKUP LOG EssxdDB TO DISK='F:\TEMP\EssxdDB_LOG.bak'
BACKUP LOG EssxdDB WITH NO_LOG
DBCC SHRINKFILE(EssxdDB_LOG, 10)

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

TAG MSSQL

Trackback 0, Comment 0

월별 연령대별 통계구하기

데이타베이스/MSSQL 2007/11/01 10:53

SELECT CONVERT(varchar(20), jdate, 112) AS jdate,
COUNT((CASE WHEN age / 10 = 2 THEN 1 ELSE NULL END)) '20대',
COUNT((CASE WHEN age / 10 = 3 THEN 1 ELSE NULL END)) '30대',

   COUNT((CASE WHEN age / 10 = 4 THEN 1 ELSE NULL END)) '40대',
   COUNT((CASE WHEN age / 10 = 5 THEN 1 ELSE NULL END)) '50대',
   COUNT((CASE WHEN age / 10 = 6 THEN 1 ELSE NULL END)) '60대',
   COUNT((CASE WHEN (age / 10 > 6) OR (age / 10 < 2) THEN 1 ELSE NULL END)) '기타'
FROM (SELECT id, YEAR(GETDATE()) - ((CASE WHEN CONVERT(int, substring(ssn, 7, 1)) >= 3 THEN 2000
ELSE 1900 END) + CAST(SUBSTRING(ssn, 1, 2) AS int)) AS age,
jdate
   FROM jumin) AS A
GROUP BY CONVERT(varchar(20), jdate, 112)

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

테이블 -> insert 쿼리문 변환 스크립트

데이타베이스/MSSQL 2007/01/12 17:36

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[sp_generate_insert_script]')
and objectproperty(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_generate_insert_script]
go

create procedure sp_generate_insert_script
@tablename_mask sysname = NULL
as
begin

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

-- Stored Procedure: sp_generate_insert_script
-- Language: Microsoft Transact SQL (7.0)
-- Author: Inez Boone (inez.boone@xs4al.nl)
-- working on the Sybase version of & thanks to:
-- Reinoud van Leeuwen (reinoud@xs4all.nl)
-- Version: 1.4
-- Date: December 6th, 2000
-- Description: This stored procedure generates an SQL script to fill the
-- tables in the database with their current content.
-- Parameters: IN: @tablename_mask : mask for tablenames
-- History: 1.0 October 3rd 1998 Reinoud van Leeuwen
-- first version for Sybase
-- 1.1 October 7th 1998 Reinoud van Leeuwen
-- added limited support for text fields; the first 252
-- characters are selected.
-- 1.2 October 13th 1998 Reinoud van Leeuwen
-- added support for user-defined datatypes
-- 1.3 August 4 2000 Inez Boone
-- version for Microsoft SQL Server 7.0
-- use dynamic SQL, no intermediate script
-- 1.4 December 12 2000 Inez Boone
-- handles quotes in strings, handles identity columns
-- 1.5 December 21 2000 Inez Boone
-- Output sorted alphabetically to assist db compares,

-- skips timestamps
-- 1.6 June 10 2005 Beatchoi@yahoo.co.kr
-- added support for reserver keyword
--------------------------------------------------------------------------------

-- NOTE: If, when executing in the Query Analyzer, the result is truncated, you can remedy
-- this by choosing Query / Current Connection Options, choosing the Advanced tab and
-- adjusting the value of 'Maximum characters per column'.
-- Unchecking 'Print headers' will get rid of the line of dashes.

declare @tablename varchar (128)
declare @tablename_max varchar (128)
declare @tableid int
declare @columncount numeric (7,0)
declare @columncount_max numeric (7,0)
declare @columnname varchar (30)
declare @columntype int
declare @string varchar (30)
declare @leftpart varchar (8000) /* 8000 is the longest string SQLSrv7 can EXECUTE */
declare @rightpart varchar (8000) /* without having to resort to concatenation */
declare @hasident int

set nocount on

-- take ALL tables when no mask is given (!)

if (@tablename_mask is NULL)
begin
select @tablename_mask = '%'
end

-- create table columninfo now, because it will be used several times

create table #columninfo (num numeric (7,0) identity,name varchar(30),usertype smallint)

select name,id into #tablenames
from sysobjects
where type in ('U' ,'S')
and name like @tablename_mask

-- loop through the table #tablenames

select @tablename_max = MAX (name),@tablename = MIN (name)
from #tablenames

while @tablename <= @tablename_max
begin
select @tableid = id
from #tablenames
where name = @tablename

if (@@rowcount <> 0)
begin
-- Find out whether the table contains an identity column
select @hasident = max( status & 0x80 )
from syscolumns
where id = @tableid

truncate table #columninfo

insert into #columninfo (name,usertype)
select name, type
from syscolumns C
where id = @tableid
and type <> 37 -- do not include timestamps

-- Fill @leftpart with the first part of the desired insert-statement, with the fieldnames

select @leftpart = 'select ''insert into '+@tablename
select @leftpart = @leftpart + '('
select @columncount = MIN (num),@columncount_max = MAX (num)
from #columninfo

while @columncount <= @columncount_max
begin
select @columnname = quotename([name]),@columntype = usertype
from #columninfo
where num = @columncount

if (@@rowcount <> 0)

begin
if (@columncount < @columncount_max)
begin
select @leftpart = @leftpart + @columnname + ','
end
else
begin
select @leftpart = @leftpart + @columnname + ')'
end
end
select @columncount = @columncount + 1
end

select @leftpart = @leftpart + ' values('''

-- Now fill @rightpart with the statement to retrieve the values of the fields, correctly formatted

select @columncount = MIN (num),
@columncount_max = MAX (num)
from #columninfo

select @rightpart = ''

while @columncount <= @columncount_max
begin
select @columnname = quotename(name),@columntype = usertype
from #columninfo
where num = @columncount

if (@@rowcount <> 0)
begin

if @columntype in (39,47) /* char fields need quotes (except when entering NULL);
* use char(39) == ', easier readable than escaping
*/
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(' + @columnname + ','
+ replicate( char(39), 4 ) + ',' + replicate( char(39), 6) + ')+' + replicate ( char(39), 4 ) + ',''NULL'')'
end

else if @columntype = 35 /* TEXT fields cannot be RTRIM-ed and need quotes */
/* convert to VC 1000 to leave space for other fields */
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace (convert(varchar(1000),'
+ @columnname + ')' + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6 ) + ')+'
+ replicate( char(39), 4 ) + ',''NULL'')'
end

else if @columntype in (58,61,111) /* datetime fields */
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+convert (varchar(20),'
+ @columnname + ')+'+ replicate( char(39), 4 ) + ',''NULL'')'
end

else /* numeric types */
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(convert(varchar(99),' + @columnname + '),''NULL'')'
end

if ( @columncount < @columncount_max)
begin
select @rightpart = @rightpart + '+'','''
end

end
select @columncount = @columncount + 1
end
end

select @rightpart = @rightpart + '+'')''' + ' from ' + @tablename

-- Order the select-statements by the first column so you have the same order for
-- different database (easy for comparisons between databases with different creation orders)

select @rightpart = @rightpart + ' order by 1'

-- For tables which contain an identity column we turn identity_insert on
-- so we get exactly the same content

if @hasident > 0
select 'SET IDENTITY_INSERT ' + @tablename + ' ON'

exec ( @leftpart + @rightpart )

if @hasident > 0
select 'SET IDENTITY_INSERT ' + @tablename + ' OFF'

select @tablename = MIN (name)
from #tablenames
where name > @tablename

end

end
return

실행방법

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

Exec SP_insert titles

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

출력화면

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

insert into titles( title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate )

values( VarChar(6), VarChar(80), Char(12), Char(4), Money(8), Money(8), Int(4), Int(4), VarChar(200), DateTime
(8) )

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

※ MYSQL 백업처럼 INSERT를 이용한 테이블 백업을 할 수 있도록 해주는 프로시저

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

COM+ 트랜잭션 처리시 발생하는 오류에 대한 해결책

데이타베이스/MSSQL 2006/12/28 13:45

COM+ 트랜잭션 처리시 발생하는

오류에 대한 해결책

COM+를 이용해서 개발하다보면 다양한 서버구성에서 트랙잭션처리를 성공시켜야할때가 있습니다. DB 서버와 COM+ 서버가 서로 다른 서버로 구성되어있거나 방화벽을 거쳐야할경우 트랜잭션이 성공적으로 수행되지않고 오류가 발생합니다. 이 문서는 각 상황별 오류와 그 해결방안에 대해 정리한 문서입니다. 보시고 잘못된 사항이나 추가적인 사항이 있을경우에 리플달아주세요. ^^

1. COM+ 와 DB SERVER 간의 트랜잭션 처리 및 오류에 대한 이해.

COM+를 이용해 트랜잭션을 처리할 경우 여러 가지 상황에서 오류가 발생할 수 있습니다. (비트랜잭션의 경우에는 아무런 이상 없이 동작합니다.)

COM+와 DB Server 가 서로 다른 Machine에 존재할 경우, 두 Machine 간에 다음과 같은 조건이 성립되어야 합니다.

1. RPC PING 이 두 Machine 간에 성공해야 한다.

2. DTS PING 이 두 Machine 간에 성공해야 한다.

3. DB Machine에 DTC 서비스가 올라와 있어야 한다.

1,2번 를 만족하지 못하면, COM+ Machine 의 트랜잭션 정보를 DB Machine 에서 인식하지 못합니다. 3번을 만족하지 못한다면, 트랜잭션 정보를 네트웤을 통해 전달할 수 없습니다.

COM+ 를 이용한 트랜잭션 처리시 반드시 위의 3가지 사항이 만족해야만 합니다.

일반적으로 COM+ 를 이용해서 트랜잭션을 구현하는 경우에는 MSDTC를 통해서 그 트랜잭션이 관리됩니다.

2. 오류 발생 시나리오

일반적으로 오류메시지는 다음과 같이 발생합니다.

‘트랜잭션이 이미 암시적 또는 명시적으로 커밋되거나 중단되었습니다.’

‘오류 0x8004D025 (XACT_E_PARTNER_NETWORK_TX_DISABLED)‘

‘Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.’

이런 문제는 다음의 시나리오 중 한가지에 해당합니다.

시나리오 1. 하나의 Windows2003 Machine 내에서 DB SERVER 와 클라이언트(ASP.NET)가 모두 구현된 경우.

시나리오 2. 두개의 Windows2003 Machine 내에서 DB SERVER 와 클라이언트(ASP.NET)가 각각의 Machine에 구현된 경우.

3. 공통 해결방안

오류 시나리오의 공통으로 적용되는 사항입니다. Windows Server 2003 제품에서는 네트워크 DTC 액세스가 사용할 수 없게 해제되어 있습니다. 서버에서 네트워크 DTC 액세스를 해제된 상태로 그대로 두면 응용 프로그램이 해당 로컬 컴퓨터에 머물러 있는 트랜잭션만 사용할 수 있습니다. 예를 들어, 네트워크 DTC 액세스가 해제된 상태에서는 트랜잭션이 로컬 컴퓨터에서 별도의 컴퓨터에서 실행되는 데이터베이스로 이동할 수 없습니다.

네트워크 DTC 액세스를 사용할 수 있게 설정하는 단계

1. 시작을 누르고 제어판을 가리킨 다음 프로그램 추가/제거를 누릅니다.

2. Windows 구성 요소 추가/제거를 누릅니다.

3. 응용 프로그램 서버를 선택한 다음 자세히를 누릅니다.

4. 네트워크 DTC 액세스 사용을 선택한 다음 확인을 누릅니다.

5. 다음을 누릅니다.

6. 마침을 누릅니다.

7. Distributed Transaction Coordinator 서비스를 중지한 다음 다시 시작합니다.

8. 이 분산 트랜잭션에 참여하는 리소스 관리자 서비스(예: Microsoft SQL Server 또는 Microsoft Message Queue Server)를 모두 중지한 다음 다시 시작합니다. ( 7,8 번 대신reboot 해도 됩니다.)

4. 시나리오 1 해결책

'네트워크 DTC 액세스 사용'을 설정한 후 ‘관리자 도구’의 ‘구성 요소 서비스’->’컴퓨터’->’내 컴퓨터’ 의 ‘등록 정보’ 를 보시면 MSDTC라는 탭이 생깁니다. 여기서 보안 구성을 누르시면 설정 창이 하나 더 나타납니다.

'네트워크 DTC 액세스 사용' 추가 전 MS DTC 모습

'네트워크 DTC 액세스 사용' 추가 후 MS DTC 모습

보안문제 때문에 기본으로 언체크 되어 있는 항목을 그림처럼 “네트워크 DTC 엑세스” , “네트워크 관리” , “네트워크 트랜잭션” , “XA트랜잭션” 을 체크합니다.

그리고 DTC 로그온 계정이 'NT AUTHORITY\NetworkService' 로 되어 있는데 이 컴퓨터 내의 DTC 사용 자원과 DTC 통신하는 Machine 의 설정을 확실히 알지 않으시면 설정을 변경하지 않기를 권장합니다. 설정이 끝나셨으면 확인해서 나가시고 반드시 재부팅을 해야 합니다.

여기 까지 작업이 끝나면 기본적으로 시나리오 1번은 해결이 됩니다. 그래도 계속해서 오류가 발생한다면 소스 코드를 확인해볼 필요가 있습니다.

5. 시나리오 2번 해결책

시나리오 2번의 경우는 좀더 복잡한 것이 두개의 Window2003 MSDTC 간에 RPC로 통신을 한다는 것 입니다. 시나리오 2번에서도 몇가지 상황별로 해결 방안이 달라집니다.

2-1. 도메인이 다른 두개의 Windows 2003 Machine 간의 트랜잭션 통신일 경우.

2-2. 방화벽이 있는 두개의 windows 2003 Machine 간의 트랜잭션 통신일 경우.

2-1 시나리오의 경우에는 MS DTC 버그로 레지스트리 수정작업을 해야 합니다.

2-1 시나리오 해결방법

1.Regedt32.exe 실행

2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC

3. 레지스트리에 다음같을 추가 또는 다음과 같이 수정한다.

Value name

Data type

Value

TurnOffRpcSecurity

REG_DWORD

1

4. 리부팅을 한다.

2-2 시나리오 해결방법

방화벽이 있을 경우 MTS, COM+ 의 트랜잭션 설정이 Not Supported 이거나 Supported 일때는 아무 이상없이 작업이 수행된다. 하지만, Requires , Requires New 일 경우에는 트랜잭션이 성공적으로 수행되지 않고 다음과 같은 오류가 발생한다.

New transaction cannot enlist in specified transaction coordinator

또는

Error 8004d00a. Distributed Transaction error

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

Posted by 멍멍대왕

Trackback 0, Comment 0

'데이타베이스/MSSQL'에 해당되는 글 27건

1. COM+ 와 DB SERVER 간의 트랜잭션 처리 및 오류에 대한 이해.

2. 오류 발생 시나리오

3. 공통 해결방안

4. 시나리오 1 해결책

5. 시나리오 2번 해결책

카테고리

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크