MSSQL - Cursor vs Temp Table

#테이블 변수사용의 예


use pubs
go
declare @tmptable table
(
    nid int identity(1,1) not null,
    title varchar(80) not null
) -- 테이블 변수 선언 

insert @tmptable(title)
select title from titles -- titles테이블의 title을 테이블변수에 삽입(루프생성을 위해)

declare @print varchar(5000) -- print변수 선언
declare @i int, @maxno int, @title varchar(80)     -- 루프변수 선언 
select @i=1, @maxno=max(nid) from @tmptable   -- 루프변수 초기화 
while @i<=@maxno -- 루프문 
begin
    select @title=title from @tmptable where nid=@i -- 커서의 fetch into해당
    if(@i = 1)
    begin
        set @print = @title
    end
    else
    begin
        set @print = @print + ' | ' + @title
    end
    set @i=@i+1
end  

print @print



#커서사용의 예


declare @title varchar(100)
declare @print varchar(5000)

set @print = ''
declare c_cursor CURSOR FOR
   select title from titles
for read only

open c_cursor
fetch next from c_cursor
into @title

while(@@fetch_status <> -1)
   begin
       if(@@fetch_status <> -2) 
       begin
           if(@print = '')
           begin
              set @print = @title
           end
           else
           begin
              set @print = @print + ' | ' + @title
           end
       end
  fetch next from c_cursor

  into @title
end

close c_cursor
deallocate c_cursor

print @print

출처 : https://using.tistory.com/14

MSSQL - MSSQL to Oracle (Migration)

 

0. 환경 구성
다음과 같이 MSSQL 2014의 데이터를 Linux 환경의 Oracle 11g로 옮기는 테스트 임.
중간의 개인 PC 작업 환경에 SQL Developer가 설치 되어 있어야 하며, SQL Developer에서 MS-SQL에 접속하기 위하여 jtds를 다운 받아야 함.



 
1. SQL Developer Download
 
Download Site : http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

윈도우 10이 64bit 이므로 64bit용 SQL Developer를 다운로드 받으며, JDK가 포함된 버전으로 받음.


다운 받은 zip 파일을 푼 후에 SQL Developer를 기동해서 정상 동작 하는지 확인.
프로그램이 기동되면 + 단추를 눌러서 접속 가능한 DB의 목록을 확인해 보면 Oracle 밖에 보이질 않는다.
 



 
 
2, MS-SQL에 접속하기 위한 jTDS 다운로드
 
Download Site : https://sourceforge.net/projects/jtds/files/
 
위 사이트에 접속해서
jtds-1.3.1-dist.zip 최신 버전을 다운로드 한다. 글 작성 시점에는 1.3.1 이 최신 파일임.
압축을 풀어서 SQL Developer에서 사용할 수 있게 등록하면 된다.
 
Tools > Preferences > Database > Third Party
JDBC Drivers > Add Entries... > 1-3-1.jar 추가
 



 
MS-SQL Server와 Sybase가 추가된 것을 확인 할 수 있다.
 



 
 
3. Repository 설정
Windows 10 PC에서 마이그레이션에 사용할 Repository 설정을 해 준다.
 



 
접속을 추가 한 후에 Migration에 사용할 유저를 생성한다. 유저 생성 시 권한을 주는데, 레파지토리 생성 시 에러가 종종 발생 하기도 해서 전체 권한을 줌. 
connect , resource ,create view 권한만 주는 경우도 있으나, 그냥 전체를 주었음.
 



 

  

  
CREATE USER
  MIGRATION IDENTIFIED BY "migration"
  
DEFAULT
  TABLESPACE USERS
  
TEMPORARY
  TABLESPACE TEMP;
  
 
  
GRANT ALL
  PRIVILEGES to MIGRATION ; 
  
 


 



 
 
4. Repository 생성
생성한 migration 유저로 접속하는 접속을 하나 만든 후 해당 유저에 Repository를 생성 한다.
 



 
생성 한 migration 접속에서 우 클릭을 한 후에 > Migration Repository > Associate
Migration Repository 선택
 



 



 



 
테이블을 눌러 보니 테이블들이 생긴 것을 확인 할 수 있다.
 



 
 
5. Source MS-SQL접속 정보 추가
Source 서버인 MS-SQL 서버에 접속해 본다.
 
192.168.1.250  
sa 정보를 입력 > 
"테스트" > 데이터베이스 검색 > 마이그레이션을 원하는 DB 선택
 



 
6. Target Oracle 접속 정보 추가
Target 서버인 Oracle 서버에 접속해 본다.
 
192.168.10.130 system 정보 입력 > 테스트 > 저장
 



 
7. 마이그레이션 진행
Tools > Migration > Migrate.... 
 



 



 
이름은 MS_to_Oracle로 했으며, 파일이 저장되는 디렉터리는 d:/temp로 지정 함.
 



 



 
이전 할 DB 선택
 



 



 
기본적으로 constraint , function,
procedure, trigger , view 데이터가 기본으로 마이그레이션 된다.
 



 



 



 



 
 



 



 
8. 이전 된 데이터 확인
이전 된 데이터 확인
 

[출처] https://bstar36.tistory.com/333

MSSQL - CURSOR

 

-- select * from @TempTable order by ID

DECLARE @ID varchar(120)
 
-- DECLARE InfoCursor CURSOR FAST_FORWARD
DECLARE InfoCursor CURSOR FAST_FORWARD FORWARD_ONLY READ_ONLY
FOR
SELECT ID FROM @TempTable WITH(NOLOCK) ORDER BY MEM_ID
OPEN InfoCursor
FETCH NEXT FROM InfoCursor INTO @ID
 
WHILE @@FETCH_STATUS = 0
BEGIN

-- 작업 구문 --

select * from @TempTable where ID = @ID

FETCH NEXT FROM InfoCursor INTO @ID
END

CLOSE InfoCursor
DEALLOCATE InfoCursor

MSSQL - 테이블 필드명 찾기 #3

SELECT 'DB명' as [DataBase명]
, c.[TABLE_NAME] AS [테이블명(물리명)]
        , c.[COLUMN_NAME] AS [컬럼명(물리명)]
        , (
               CASE WHEN c.[CHARACTER_MAXIMUM_LENGTH] IS NOT NULL AND c.[DATA_TYPE] IN ('varchar', 'nvarchar', 'char') 
   THEN c.[DATA_TYPE] + '(' + CONVERT(VARCHAR, c.[CHARACTER_MAXIMUM_LENGTH]) + ')'
               ELSE c.[DATA_TYPE] END
        ) AS [데이터타입]
        , c.[IS_NULLABLE] AS [NULL여부]
FROM INFORMATION_SCHEMA.TABLES t 
INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON c.[TABLE_NAME] = t.[TABLE_NAME] AND t.[TABLE_TYPE] = 'BASE TABLE'
--WHERE c.[TABLE_NAME] IN ('TB_ORDER_TAXA')
where c.[COLUMN_NAME] = '필드명'
ORDER BY c.[TABLE_NAME]

MSSQL - 프로시저, 함수 찾기 #2

SELECT *
FROM sysobjects AS a
LEFT JOIN syscomments AS b ON a.id = b.id
WHERE 1 = 1
and a.type in ('P','FN','TF')
AND b.text = 'DB명'

MSSQL - 함수 - Split_WithEmpty

CREATE FUNCTION [dbo].[fn_Split_WithEmpty]
(
@String VARCHAR (MAX),
@Delimiter VARCHAR (12)
)
RETURNS @ValueTable TABLE
(
idx int identity(1,1),
StrValue VARCHAR(MAX)
)
BEGIN
DECLARE 
@NextString VARCHAR(MAX),
@Pos INT,
@NextPos INT,
@CommaCheck VARCHAR(1)
 
SET @NextString = ''
SET @CommaCheck = RIGHT(@String, 1) 
 
SET @String = @String + @Delimiter
 
SET @Pos =  CHARINDEX(@Delimiter, @String)
SET @NextPos = 1
 
WHILE (@pos > 0)  
BEGIN
SET @NextString = SUBSTRING(@String, 1, @Pos - 1)
  
--IF (LEN(@NextString) > 0)
INSERT INTO @ValueTable (StrValue) VALUES (@NextString)
  
SET @String = SUBSTRING(@String, @pos + LEN(@Delimiter), LEN(@String))
  
SET @NextPos = @Pos
SET @pos  = CHARINDEX(@Delimiter, @String)
END
 
RETURN
END 

MSSQL - 함수 - Split

CREATE FUNCTION [fn_Split]
(
@String VARCHAR (MAX),
@Delimiter VARCHAR (12)
)
RETURNS @ValueTable TABLE
(
idx int identity(1,1),
StrValue VARCHAR(MAX)
)
BEGIN
DECLARE 
@NextString VARCHAR(MAX),
@Pos INT,
@NextPos INT,
@CommaCheck VARCHAR(1)
 
SET @NextString = ''
SET @CommaCheck = RIGHT(@String, 1) 
 
SET @String = @String + @Delimiter
 
SET @Pos =  CHARINDEX(@Delimiter, @String)
SET @NextPos = 1
 
WHILE (@pos <>  0)  
BEGIN
SET @NextString = SUBSTRING(@String, 1, @Pos - 1)
  
IF (LEN(@NextString) > 0)
INSERT INTO @ValueTable (StrValue) VALUES (@NextString)
  
SET @String = SUBSTRING(@String, @pos + LEN(@Delimiter), LEN(@String))
  
SET @NextPos = @Pos
SET @pos  = CHARINDEX(@Delimiter, @String)
END
 
RETURN
END 

MSSQL - 함수 - WorkDay(영업일) 계산

 
-- select [MFUNC_GetWorkDay](getdate(), -3)
-- select [MFUNC_GetWorkDay]('20231111', 3)
CREATE FUNCTION [MFUNC_GetWorkDay]
(
@startDate date,
@interval int
)
RETURNS INT
AS
BEGIN
--Declare @startDate date, @interval int
--SET @startDate = '20230607' --getdate()
--SET @interval = -3
DECLARE @holidayCalendar TABLE (CalendarDate date NULL)
DECLARE @AddDate date
if @interval is null set @interval = 0
-- 휴일만 등록
insert into @holidayCalendar (CalendarDate)
values ('20230101'), ('20230121'), ('20230122'), ('20230123'), ('20230124')
, ('20230301')
, ('20230505'), ('20230527'), ('20230529')
, ('20230606')
, ('20230815')
, ('20230928'), ('20230929'), ('20230930')
, ('20231003'), ('20231009')
, ('20231225')
, ('20240101')
, ('20240209'), ('20240210'), ('20240211'), ('20240212')
, ('20240301')
, ('20240410')
, ('20240505'), ('20240506'), ('20240515')
, ('20240606')
, ('20240815')
, ('20240916'), ('20240917'), ('20240918')
, ('20241003'), ('20241009')
, ('20241225')
--select * from @holidayCalendar
Declare @rCnt int, @cCnt int, @flag int
SET @rCnt = 0
SET @cCnt = 0
If @interval >= 0
set @flag = 1
else
set @flag = -1
set @AddDate = @startDate
while abs(@interval) > @rCnt
begin
set @cCnt = @cCnt + 1
set @AddDate = DateAdd(day,  @cCnt * @flag, @startDate)
if (datepart(dw, @AddDate) = 1) 
OR (datepart(dw, @AddDate) = 7)
OR exists(SELECT CalendarDate FROM @holidayCalendar WHERE CalendarDate = @AddDate)
begin
set @interval = @interval + (1 * @flag)
end
set @rCnt = @rCnt + 1
end
RETURN CONVERT(VARCHAR(10), @AddDate, 112)
END

MSSQL - 함수 - MASKING

/*
select [MASKING]('1','010-1234-1234')
select [MASKING]('1','01012341234--')
select [MASKING]('1','1234--')
select [MASKING]('2','홍길동')
select [MASKING]('3','test007@test.com')
select [MASKING]('4','12345')
select [MASKING]('5','서울시 영등포구 여의대여의동 122 abcd')
select [MASKING]('6','12345678')
select [MASKING]('8','12345')
select [MASKING]('9','1234-5678-1234-0123')
select [MASKING]('10','123-23-456789')
select [MASKING]('11','123-23-1234')
select [MASKING]('12','123456789')
select [MASKING]('16','1234567890abcdefghi1234567890abcdefghijk')
*/
CREATE FUNCTION [MASKING] (
@GUBUN VARCHAR(2),
@PARM VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @RET VARCHAR(255)
-- 전화번호
IF @GUBUN = '1' 
/*
SET @RET = CASE WHEN CHARINDEX('-',@PARM) > 0 THEN
                           CASE WHEN CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) > 0 THEN
                                LEFT(@PARM, CHARINDEX('-', @PARM)) + REPLICATE( '*', CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) - 1 - CHARINDEX('-', @PARM)) +
                                + '-' + RIGHT(@PARM, LEN(@PARM) - CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1))
                           ELSE LEFT(@PARM, CHARINDEX('-', @PARM) - 1) + '-' + '****'
                           END
                      ELSE '****'
                      END
*/
SET @RET = CASE WHEN CHARINDEX('--',@PARM) > 0 THEN
CASE WHEN LEN(@PARM) > 8 THEN SUBSTRING(@PARM,1,4) + '****' + SUBSTRING(@PARM, 9, LEN(@PARM)-8)
WHEN LEN(@PARM) = 8 THEN SUBSTRING(@PARM,1,4) + '****'
WHEN LEN(@PARM) <= 4 THEN '****'
ELSE SUBSTRING(@PARM,1,LEN(@PARM)-4) + '****'
END
WHEN CHARINDEX('-',@PARM) > 0 THEN
CASE WHEN CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) > 0 THEN
LEFT(@PARM, CHARINDEX('-', @PARM)) + REPLICATE( '*', CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) - 1 - CHARINDEX('-', @PARM)) +
+ '-' + RIGHT(@PARM, LEN(@PARM) - CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1))
ELSE LEFT(@PARM, CHARINDEX('-', @PARM) - 1) + '-' + '****'
END
ELSE '****'
END
-- 이름(한글)
ELSE IF @GUBUN = '2' 
SET @RET = CASE WHEN LEN(@PARM) < 2 THEN '*'
                           WHEN LEN(@PARM) = 2 THEN LEFT(@PARM, 1) + '*'
                           WHEN LEN(@PARM) > 2 THEN LEFT(@PARM, 1) + '*' + RIGHT(@PARM, 1)
                      END
-- EMAIL
ELSE IF @GUBUN = '3' 
begin
-- SET @RET = REPLICATE('*', CHARINDEX('@', @PARM) - 1) + SUBSTRING(@PARM, CHARINDEX('@', @PARM), LEN(@PARM))
---- SET @RET = REPLICATE('*', 8) + '@' + REPLICATE('*', 8)
set @RET = [dbo].[fn_Split_IndexString](@PARM,'@',1)
if (LEN(@RET) > 4) 
set @RET = '****' + RIGHT(@RET, LEN(@RET)-4) + '@' + [dbo].[fn_Split_IndexString](@PARM,'@',2)
else
set @RET = '**' + RIGHT(@RET, 2) + '@' + [dbo].[fn_Split_IndexString](@PARM,'@',2)
end
-- 우편번호
ELSE IF @GUBUN = '4' 
--SET @RET = SUBSTRING(@PARM,1,4) + REPLICATE('*', 3)
SET @RET = case when len(@PARM) > 2 then left(@PARM, 3) + '**' else @PARM + '**' end
-- 주소
ELSE IF @GUBUN = '5' 
--SET @RET = CASE SUBSTRING(@PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ',@PARM) + 1) + 1) - 1, 1) WHEN '구' 
        --                  THEN SUBSTRING(@PARM, 1, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM) + 1) + 1)) + REPLICATE('*', 10)
        --              ELSE SUBSTRING(@PARM, 1, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM) + 1)) + REPLICATE('*', 10)
        --              END
SET @RET = LEFT(@PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM) + 1) + 1))) + '*****'
-- 요양기관번호
ELSE IF @GUBUN = '6' 
--SET @RET = '********'
set @RET = case when len(@PARM) > 3 then '****' + right(@PARM, LEN(@PARM)-4) else '****' + @PARM end
-- 주민번호
ELSE IF @GUBUN = '7' 
SET @RET = LEFT(@PARM, 6) + '-*******'
-- 면허번호
ELSE IF @GUBUN = '8' 
--SET @RET = REPLICATE('*', 5)
SET @RET = case when len(@PARM) > 2 then left(@PARM, 3) + '**' else @PARM + '**' end
-- 카드번호
ELSE IF @GUBUN = '9' 
SET @RET = SUBSTRING(@PARM, 1, CHARINDEX('-', @PARM) - 1) + '-****-****-' + 
                      RIGHT(@PARM, LEN(@PARM) - CHARINDEX('-', @PARM, CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) + 1)) 
-- 계좌번호
ELSE IF @GUBUN = '10' 
--SET @RET = SUBSTRING(@PARM, 1, LEN(@PARM) / 2) + REPLICATE('*', 10)
SET @RET = case when len(@PARM) > 5 then left(@PARM, len(@PARM)-5) + '****' else @PARM + '****' end
-- 사업자등록번호
ELSE IF @GUBUN = '11' 
--SET @RET = 
-- CASE 
-- WHEN LEN(@PARM) > 0 THEN 
-- LEFT(@PARM, CHARINDEX('-', @PARM)) 
-- + REPLICATE( '*', CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) - 1 - CHARINDEX('-', @PARM)) +
-- + '-' + '*'+RIGHT (RIGHT(@PARM, CHARINDEX('-', REVERSE(@PARM))-1), LEN(RIGHT(@PARM, CHARINDEX('-', REVERSE(@PARM))-1))-1)
-- ELSE ''
-- END
set @RET = case when len(@PARM) > 6 then left(@PARM, 7) + '****' when len(@PARM) = 0 then '' else @PARM + '****' end
-- ID
ELSE IF @GUBUN = '12' 
--SET @RET = LEFT(@PARM, LEN(@PARM)-2) + '**'
set @RET = case when len(@PARM) > 4 then '****' + right(@PARM, LEN(@PARM)-4)
when len(@PARM) > 1 then '**' + right(@PARM, LEN(@PARM)-2) 
else '*' end
-- 생년월일 (yyyy-MM-dd)
ELSE IF @GUBUN = '13' 
set @RET = [dbo].[fn_Split_IndexString](@PARM,'-',1) + '-**-**'
-- IP (ver4)
ELSE IF @GUBUN = '14' 
set @RET = [dbo].[fn_Split_IndexString](@PARM,'.',1) + '.' + [dbo].[fn_Split_IndexString](@PARM,'.',2) + '.***.' + [dbo].[fn_Split_IndexString](@PARM,'.',4)
-- IP (ver6)
ELSE IF @GUBUN = '15' 
set @RET = case when len(@PARM) > 34 then left(@PARM, 35) + '****' else @PARM + '****' end
-- DI
ELSE IF @GUBUN = '16' 
set @RET = case when len(@PARM) > 6 then left(@PARM, 7) + '*****************' else @PARM + '*****************' end
-- 법인등록번호
ELSE IF @GUBUN = '17' 
set @RET = case when len(@PARM) > 6 then left(@PARM, 7) + '*******' else @PARM + '*******' end
-- 이름(영문)
ELSE IF @GUBUN = '18' 
set @RET = case when len(@PARM) > 2 then left(@PARM, 3) + '*****' else @PARM + '*****' end
    RETURN @RET
END

MSSQL - 함수 - Split_IndexString

 
CREATE Function [fn_Split_IndexString]
(
@str VARCHAR(MAX)
, @p CHAR(1)
, @n INT
)
  Returns varchar(max)
AS
Begin
IF (LEN(@str) > 0 AND RIGHT(@str,1) != @p) 
SET @str = @str + @p
 
DECLARE @new_str VARCHAR(50), @i TINYINT 
 
SET @new_str = ''
IF (CHARINDEX(@p, @str) <> 0)
BEGIN
SET @i = 1
WHILE (CHARINDEX(@p, @str) <> 0)
BEGIN 
SET @new_str = SUBSTRING(@str,1,CHARINDEX(@p,@str) - 1)
IF (@n = @i)
RETURN @new_str
--SET @str = SUBSTRING(@str,CHARINDEX(@p, @str) + LEN(@p), LEN(@str))   
if (@p = ' ')
SET @str = SUBSTRING(@str,CHARINDEX(@p, @str) + 1, LEN(@str))
else
SET @str = SUBSTRING(@str,CHARINDEX(@p, @str) + LEN(@p), LEN(@str))
SET @i = @i + 1
END
END
RETURN NULL
End

MSSQL - 영업일 기준 날짜 만들기 (함수)

* 휴일만 임시 테이블로 관리

* 휴일을 수동으로 관리해야 하는 사항이지만 1년에 한번만 적용하면 끝

* startDate 기준으로 interval 값에 따라 (+)앞에 있는 날짜 (-)뒤에 있는 날짜 만들어짐

* 함수로 만들어 DB에서 호출하여 사용 가능

[기준일로 부터 영업일 수 만큼의 날짜 구하기]

-- select [dbo].[MFUNC_GetWorkDay](getdate(), -3)

-- select [dbo].[MFUNC_GetWorkDay]('20231111', 3)

CREATE FUNCTION [dbo].[MFUNC_GetWorkDay]
(
@startDate date,
@interval int
)
RETURNS INT
AS
BEGIN
--Declare @startDate date, @interval int
--SET @startDate = '20230607' --getdate()
--SET @interval = -3

DECLARE @holidayCalendar TABLE (CalendarDate date NULL)
DECLARE @AddDate date

if @interval is null set @interval = 0

-- 휴일만 등록 (23년, 24년)
insert into @holidayCalendar (CalendarDate)
values ('20230101'), ('20230121'), ('20230122'), ('20230123'), ('20230124')
, ('20230301')
, ('20230505'), ('20230527')
, ('20230606')
, ('20230815')
, ('20230928'), ('20230929'), ('20230930')
, ('20231003'), ('20231009')
, ('20231225')
, ('20240101')
, ('20240209'), ('20240210'), ('20240211'), ('20240212')
, ('20240301')
, ('20240410')
, ('20240505'), ('20240506'), ('20240515')
, ('20240606')
, ('20240815')
, ('20240916'), ('20240917'), ('20240918')
, ('20241003'), ('20241009')
, ('20241225')

--select * from @holidayCalendar

Declare @rCnt int, @cCnt int, @flag int
SET @rCnt = 0
SET @cCnt = 0

If @interval >= 0
set @flag = 1
else
set @flag = -1

set @AddDate = @startDate

while abs(@interval) > @rCnt
begin
set @cCnt = @cCnt + 1
set @AddDate = DateAdd(day,  @cCnt * @flag, @startDate)
if (datepart(dw, @AddDate) = 1) 
OR (datepart(dw, @AddDate) = 7)
OR exists(SELECT CalendarDate FROM @holidayCalendar WHERE CalendarDate = @AddDate)
begin
set @interval = @interval + (1 * @flag)
end
set @rCnt = @rCnt + 1
end

RETURN CONVERT(VARCHAR(10), @AddDate, 112)

END

[기간에 해당되는 영일 수 계산]

REATE FUNCTION [dbo].[fn_GetTotalWorkingDays]

(

    @DateFrom Date,

    @DateTo Date

)

RETURNS INT

AS

BEGIN

    DECLARE @TotDays INT = DATEDIFF(DAY, @DateFrom, @DateTo) + 1;

    DECLARE @TotWeeks INT = DATEDIFF(WEEK, @DateFrom, @DateTo) * 2;

    DECLARE @IsSunday INT = CASE WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday' THEN 1 ELSE 0 END;

    DECLARE @IsSaturday INT = CASE WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday' THEN 1 ELSE 0 END;

    DECLARE @TotWorkingDays INT = @TotDays - @TotWeeks - @IsSunday + @IsSaturday;

    RETURN @TotWorkingDays;

END

GO

MSSQL - CLR

 

-- master 테이블로 변경합니다.
use [master]
go

--clr을 사용할수 있도록 변경을 해야 하는데 sysadmin 권한이 있어야 한다.
exec sp_configure 'clr enabled', 1;
reconfigure;
go

-- 그다음 함수를 하용하고자 하는 디비에 대해 trustworthy 기본 속성이 off이므로 높은 권한적용을 위해 on 시킴
alter database  [사용하고자 하는 디비명]
set trustworthy on

/* 에셈블리를 등록하다 보면 
master 데이터베이스에 기록된 데이터베이스 소유자 sid가 데이터베이스 '~~'에 기록된 
데이터베이스 소유자 sid와 다릅니다. 
alter authorization 문을 사용하여 데이터베이스 '~~~'의 소유자를 다시 설정하여 
이 문제를 해결해야 합니다.
라는 에러가 나면 
master 에 등록된 디비의 소유자를 sa 로 변경합니다.
alter authorization on database::사용하고자 하는 디비명 to sa ;
*/

이제 어셈블리를 등록합니다 . 
--assembly 생성 - 대소문자 구분함. [] 없으면 안됩니다.
create assembly [SqlFunction]
from 'd:\sqldb\SqlFunction.dll'  --파일 지정 경로
with permission_set = unsafe
go

에러가 나올수 있습니다. 
어셈블리 'sqlfunction'이(가) 트러스트되어 있지 않으므로 어셈블리 'sqlfunction'에 대한 create assembly가 실패했습니다. 어셈블리는 unsafe assembly 권한과 함께 해당하는 로그인이 있는 인증서 또는 비대칭 키로 서명되어 있는 경우나 sp_add_trusted_assembly를 사용하여 트러스트되어 있는 경우에 트러스터됩니다.
​위에서 꼭 set trustworthy on 해주어야 합니다 . 
했는데 이번엔 다른 에러가 나옵니다. 
master 데이터베이스에 기록된 데이터베이스 소유자 sid가 데이터베이스 '사용하고자 하는 디비명'에 기록된 데이터베이스 소유자 sid와 다릅니다. alter authorization 문을 사용하여 데이터베이스 '사용하고자 하는 디비명'의 소유자를 다시 설정하여 이 문제를 해결해야 합니다.
​
그러면 위에 있던 디비 소유자를 변경해 줍니다. 
대부분은 sa 일거니가... sa 로 해줍니다. 
​
d:\sqldb\SqlFunction.dll 에 복사해두었습니다 . 
SqlFunction.dll 이라는 CLR을  SqlFunction 라는 어셈블리로 등록한다는 의미..
​
이제 dll에 있는 함수를 sql 에 사용할수 있도록 sql 함수를 만듭니다. 
create function dbo.fn_UsrFun1(
@p_original_string nvarchar(4000)
)
returns nvarchar(max) with execute as caller
as
-- 어셈블리명.dll 내 class 명.함수명
external name [SqlFunction].UsrFunction.UsrFun1
go

sql 서버내 함수명 : dbo.fn_UsrFun1
external name [SqlFunction].UsrFunction.UsrFun1
이 부분이 제일 중요하겠죠 ... 
SqlFunction 어셈블리 > UsrFunction Class > UsrFun1 method 라는 의미입니다 .
​
UsrFun2 라는 함수도 만들었으니  아래처럼 될겁니다.
create function dbo.fn_UsrFun2(
@p_original_string nvarchar(4000)
)
returns nvarchar(max) 
with execute as caller
as
external name [SqlFunction].UsrFunction.UsrFun2
go
​
디비에서 확장하면  다음처럼 등록된 것을 볼수 있습니다.


참조 : https://m.blog.naver.com/jskimmail/222083779381

MSSQL - INDEX 재 정리

SQL Server Management Studio 사용하여 인덱스 유지 관리

인덱스를 다시 구성하거나 다시 작성하려면

1. 개체 탐색기에서 인덱스를 다시 구성할 테이블이 포함된 데이터베이스를 확장합니다.

2. 테이블 폴더를 확장합니다.

3. 인덱스를 다시 구성할 테이블을 확장합니다.

4. 인덱스 폴더를 확장합니다.

5. 다시 구성할 인덱스를 마우스 오른쪽 단추로 클릭하고 다시 구성을 선택합니다.

6. 인덱스 다시 구성 대화 상자에서 다시 구성할 인덱스 표에 올바른 인덱스가 있는지 확인한 다음 확인을 클릭합니다.

7. 큰 개체 열 데이터 압축 확인란을 선택하여 LOB(Large Object) 데이터가 포함된 모든 페이지도 압축되도록 지정합니다.

8. 확인.

테이블의 모든 인덱스를 다시 구성하려면

1. 개체 탐색기에서 인덱스를 다시 구성할 테이블이 포함된 데이터베이스를 확장합니다.

2. 테이블 폴더를 확장합니다.

3. 인덱스를 다시 구성할 테이블을 확장합니다.

4. 인덱스 폴더를 마우스 오른쪽 단추로 클릭하고 모두 다시 구성을 선택합니다.

5. 인덱스 다시 구성 대화 상자에서 다시 구성할 인덱스에 올바른 인덱스가 있는지 확인합니다. 다시 구성할 인덱스 표에서 인덱스를 제거하려면 인덱스를 선택한 다음 Delete 키를 누릅니다.

6. 큰 개체 열 데이터 압축 확인란을 선택하여 LOB(Large Object) 데이터가 포함된 모든 페이지도 압축되도록 지정합니다.

7. 확인.

[1] 특정 인덱스를 다시 구성하려면

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode

    ON HumanResources.Employee

    REORGANIZE;

[2] 테이블의 모든 인덱스를 다시 구성하려면 (인덱스를 다시 작성하는 것보다 리소스가 덜 사용됩니다. 기본 인덱스 유지 관리 방법.)

ALTER INDEX ALL ON HumanResources.Employee 

REORGANIZE;

[3] 인덱스를 다시 작성하려면 (인덱스가 삭제된 다음 다시 생성됩니다. 작업 시 잠금 및 DB성능 저하.)

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee 

REBUILD;

[4] 테이블에서 모든 인덱스를 다시 작성하려면 (서비스 내리고 진행해야 함. 데이터의 크기에 따라 시간이 오래 걸릴 수 있음.)

ALTER INDEX ALL ON Production.Product

REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,

              STATISTICS_NORECOMPUTE = ON);

MSSQL - 중급교육 #3

 

* MSSQL 실행 순서 : 쿼리문 -> 컴파일 -> 통계정보 -> 실행계획 -> Seek(OLTP) or Scan(OLAP) 실행

* WHERE 첫 번째 조건은 무조건 "=" 조건이 되도록 

  . SARG 조건

  . WHERE 구문에 맞게 Index 생성 (오름차순, 내림차순도 동일하게 적용)

  . 사용자 정의 함수 X

  . 로컬 변수, 로컬 테이블 X (SP 파라미터로 적용 : SP에서 SP 호출 방식 적용)

  . OLTP : Hash Match 나오는 경우 튜닝 해야 함

  . OLAP : Hash Match (대용량, Index 사용 못함)

7장. 고급 쿼리 튜닝

. JOIN

  - Nested Loop Join (OLTP성 조인)

  - Hash Match Join, Merge Join (OLAP성 조인)

  - 외부 입력이 먼저 실행되고 내부 입력이 다음에 실행 된다

  - 2개의 테이블 중 선택도가 좋은 지에 따라 "외부 입력" 지정 (인덱스 사용 가능, select 되는 행 수가 적은 테이블)

  - "내부 입력"은 반드시 인덱스 사용 가능 (Index Seek)

  - "외부 입력" --> "검색 테이블" (선택도 좋아야 함)

  - "내부 입력" --> "조인 테이블" (인덱스 조인)

  - 조인 시 "내부 입력" 테이블의 컬럼은 변형하지 말아야 한다 (반드시 Index Seek 해야 성능이 좋아진다)

  - 집합 연산자는 거의 대부분이 중복 제거 된다고 생각하면 된다

  - JOIN 시 한번에 2 테이블 씩 처리 (예 10개 테이블 10! = 3,628,800 가지) 

    . join 테이블이 많을 수록 성능 저하의 요인 (실행 계획 수립 과정 적정?한 선택)

  * (핵심) 초기 테이블의 외부, 내부 입력 선정

  * 조인 분리 : 임시 테이블 사용 고려

  * 비정규화 고려

. OUTER JOIN

  - 불필요한 OUTER JOIN 사용하지 말자

. Hash Match Join ("외부 입력", "내부 입력" 그대로 유지)

  - 인덱스 유무 확인 필요

. Merge Join

  - 데이터가 "정렬"되어 있어야 한다

  - 클러스터 인덱스가 있어야 한다 또는 커버링 인덱스가 있어야 한다

  - Sort 하고 Merge 처리 : Sort Merge (가끔씩 발생) 

. Adaptive Join (적응형 조인)

  - 실행 계획에 "논리적 연산 : 내부 조인" 표시

  - 실행 계획에 재사용 부작용 --> 쿼리 분리 처리 (옵티마이저에서 자동 처리)

  - OPTION(USE HINT(...))

. Semi Join 과 Anti Semi Join

  - Semi Join (교집합)

    . WHERE aaa in (selesct aaa from ...)

  - Anti Semi Join (차집합)

    . WHERE aaa not in (selesct aaa from ...)

    . 모델링에서 고려해야 함

    . 가장 어려운 튜닝 조건

8장. Hint

. Join Hint

  - INNER {...} JOIN : 왼쪽 테이블이 "외부 입력"

  - OPTION (... JOIN)

  - 반드시 필요한 경우 사용

  - 사용 시 왜 사용하는 지에 대한 주석으로 설명을 추가 한다

9장. Subquery

(예)

  - and exists (select * from ....)

  - and 중분류 = (select top 1 중분류 from ....)

. Join <--> Group by

  - Group by 먼저 해야 할 경우는 조인(집계)열의 밀도가 높은 경우

  - join (....) b on a.aa = b.bb : (....) 안에 있는 쿼리는  a.aa = b.bb 조건을 내부적으로 미리 적용된다

. ROW_NUMBER()

  - 페이징 쿼리에 많이 사용

  - ver 2012 이상 : ORDER BY ... DESC OFFSET ... ROWS FETCH NEXT .. ROWS ONLY (사용 권장)

  - 검색 조건별 인덱스 생성해서 사용하는 것이 좋음

  - 첫번째 페이징 정적 쿼리문 작성, 이후 페이지는 동적 쿼리문 작성 (예)

. 차집합 구하기 (참조)

  - nullif() 함수 : null 값 활용 : select 1 / 0 <-- nullif()

   (예)

    . ISNULL(E_PLAYER_NAME,'HELLOWORLD') ISNULL값,      /*E_PLAYER_NAME이 널이면 HELLOWORLD 출력*/

    . NULLIF(TEAM_ID,'K10') K10이면NULL,                /*TEAM_ID가 K10이면 NULL*/

    . COALESCE(E_PLAYER_NAME,NICKNAME)           /*널 아닌 최초값 출력*/

. OLTP 기분 병렬 쿼리

  - 병렬 처리 퀴리는 OLAP성 쿼리

  - 병렬 처리 퀴리 2개 이상의 CPU를 사용한다

  - OPTION (MAXDOP 0) : CPU 사용수 (0 --> 병렬 전체 사용, 1 --> 직렬 처리, 4 --> 병렬 최대 4개)

. STRING_AGG (...) : "1,2,4,7,4,5,7,,...." 문자열 만드는 함수

  - ver 2017 이상

. 함수

  - 실제 실행 계획에서 "QueryTimeStats" 속성 아래 "UdfCpuTime", "UdfElapsedTime" 속성 참조

  - ver 2019 이상 함수 쿼리가 자동으로 튜닝 처리 해준다. (Scalar UDF InLine 옵션 적용 시)

  - "인라인 스칼라 Tsql Udf 포험 : True"

  - STRING_SPLIT() : ver 2016 이상

10장. 저장 프로시저 튜닝

. 권장 세션 옵션

  - SET LOCK_TIMEOUT

  - SET NOCOUNT ON

  - SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

. sp_ 접두사는 금기

11장. 성능 문제 및 튜닝 방안

. OPTION (RECOMPILE)

. OPTION (OPTIMIZE FOR (@aaa {'값'})) 

  

MSSQL - 중급교육 #2

 

4장. 열 통계(STATISTICS)

- 인덱스 생성 시 통계 정보 자동 생성 (FullScan)

  . (a,b,c,..) "a" 첫번째 필드 정보에 대해서만 통계 생성

- 비 인덱스 경우 쿼리 실행 시 통계 정보 자동 생성 (Sampling 일부 데이터로)

SELECT * 

FROM sys.stats st 

WHERE st.object_id = OBJECT_ID('myOrders')

-- 통계 내용 확인

DBCC SHOW_STATISTICS(myOrders, NC_myOrders_OrderID)

- 통계 보기

  . Steps (200) : 막대기 수, 최대 200개, null 추가 시 201 (수가 적을 경우 예상 행 수 추정에 문제)

  . All density : 열 밀도 (0에 가까울 수록 유니크한 값이 많다. 1에 가까울 수록 중복 데이터가 많다)

  . EQ_ROWS : RANGE_HI_KEY 본인 값에 해당 되는 레코드 수

  . DISTINCT_RANGE_ROWS : 앞에 있는 값에서 해당 값까지 생략된 레코드 수 (중복 제거)

  . RANGE_ROWS : 앞에 있는 값에서 해당 값까지 생략된 레코드 수

  . AVG_RANGE_RIWS = RANGE_ROWS / DISTINCT_RANGE_ROWS

  . 가정법 : 열 통계 정보에 없는 데이터는 어떻게 추정하느냐? (마지막 데이터 < ?)

    - ver 2014 이상 : 예상 행 수가 많은 것으로 처리 된다. (seek --> scan)

  . 행수 추정 방해 요소 : 로컬변수 DECLARE @....  : col <= @...  ,  col = @...

  . 평균 선택도 = Rows * All density  (col = @...)

  . 오늘 기준 데이터는 통계 정보에 없기 때문에 예상 행 수 N건으로 처리

  . 통계 정보 업데이트 재 생성(계산)해야 한다

  . 통계 정보 업데이트 생성을 매일 1~2번은 재 계산 할 수 있도록 한다. 

  . update statistics 통계명

  . alter database {db명} set auto_update_statistics {on|off}

  . BTS 예매 시 대용량 데이터 발생 시 최종 티켓 판매 수 값을 예상 행 수 추정이 어렵지만 ver 2014 이상 일 경우 N건 처리

  

5장. 잠금 (Lock) 및 차단 (Blocking)

  . update

    - select -> update 진행

    - 테이블 및 인덱스도 잠금 

. READ UNCOMMITTED ( = NOLOCK) : Dirty Read (수정 중인 데이터 읽기)

. 행 단위 잠금

. COM+ : 기본적으로 Serializable 격리 수준 부하 (대량 DeadLock 발생)

. new TransactionScope() : 분산 처리, 무겁다, 기본 Serializable 격리 수준 (변경해서 사용 할 것)

. NOLOCK : Dirty Read + Index Allocation Scan 발생 시 쿼리 결과 정합성 이슈 주의

. 정합성 이슈 : NOLOCK 사용 시 ORDER BY 생략하고 대량의 데이터 변경 시 데이터를 인식 못하거나 동일한 데이터가 나타나는 현상 발생

. Lock Escalation : 행 단위 lock이 많을 경우 테이블 lock 처리

6장. 기본 쿼리 튜닝

. SARG (Search ARGument, 검색인수)

. 정적 데이터는 케쉬 사용 권장

*** 프로시저문 (인덱스 적용이 가능하도록) 

WHERE (OrderID = @OrderID OR @OrderID IS NULL)

WHERE OrderID = COALESCE(@OrderID, OrderID)

OPTION (RECOMPILE) -- PEO

select *

from qqqq ....

INNER JOIN uf_aaa('1,2,3,4,3,5,2') -- distinct 안됨

WHERE aaa in (select val from uf_aaa('1,2,3,4,3,5,2')) -- 자동 distinct 됨

GROUP BY  (1) DIFF... (2) YEAR(... and MONTH(... (3) LEFT(...  : 성능이 좋은 순서

Hash Match : Index 사용 못하는 경우, 대용량 인 경우 (OLAP성 쿼리)

Hash Match는 OLTP성 쿼리에서 나오면 안됨 (인덱스 확인해야 함, 예상 행 수 문제,...)

UNION <--> OR <--> IN

... AND (CASE aa THEN 'C' THEN b.bbb ELSE c.ccc END) = 'aaaaa'

--> ... AND aa = b.bbb ... UNION ... AND aa = c.ccc ...

MSSQL - 중급교육 #1

 

. 인프런 온라인 교육 : https://www.inflearn.com

. 행 수 추정 (Cardinality Estimation)

  - 집합의 원수 (데이터 레코드)

  - 처리 예상 행 수 추정 (잘 할 수 있도록 하는 것이 쿼리 튜닝이다)

  - 밀도 : 열의 유일 값 

    . 1 / 열의 유일 값 개수 = ? (값이 1에 가까울 수록 중복 데이터가 많은 것)

  - 선택도 : 

    . 결과 행 수 / 테이블 행 수 = ? (값이 작을 수록 선택도가 좋다)

. OLTP (트랜젹션용 작업용 쿼리) : 빠른 응답 속도 목표, 단일 CPU 사용 충분, Index Seek

. OLAP (통계 및 대용량 작업 쿼리) : 전체 처리 성능 목표, 병렬 처리 필요, 하드웨어 성능, Index Scan

. 2014 버전 이상 사용 권장 (미만 버전 성능 버그가 많음)

. 내부 함수 사용 시 행 수 추정이 좋지 않은 경우가 많음

. 쿼리 튜닝 : 인덱스 튜닝, 쿼리 자체 튜닝, 리소스 대기 튜닝 (CUP, RAM, 네트워크, ...)

. SET STATISTICS PROFILE ON

. SET STATISTICS XML ON

. SET SHOWPLAN_XML ON

. 물리적(논리적) 연산자 : 논리적 연산자 --> (내부 조인), (Aggregate), ...

. 논리적 연산자는 최종 적용된 연산자 정보 표시

. "예상" 비용 : 좋은 쿼리 나쁜 쿼리라는 판단하는 것은 문제가 있다. 성능과는 무관하다.

. 예상 행 수가 다르면 예상 비용도 다르게 된다.

*** 실행 계획 (튜닝) ***

. 실제 행 수 <--> 예상 행 수 : 차이가 적어야 한다 (OLTP or OLAP 선택 조건)

  - 복합 인덱스 튜닝 부족

  - 열 통계 관련 문제

  - SARG 위반 등의 SQL 쿼리 자체 문제

  - 기타

. (예) IO 용량 = 논리적 일기 수 6 * 8K

. 쿼리 옵티마이저가 사용하는 내부 임시 테이블 : Worktable, Workfile (사용되지 않도록 해야 함)

. RID Lookup + Join (** 중요)

. 실제 읽기 --> 논리적 읽기로 생각하면 됨

. 인덱스 쓰는 손익분기 점 기준은 1% 내외 (예) 1.325% 이하 (고정 아님) 

  (Table Scan (Index Scan) --> Index Seek (Lookup))

. 클러스터 인덱스 컬럼의 필드 업데이트 할 경우 insert, delete 함께 발생

. 클러스터 인덱스 선택 기준 - 트랜잭션 성능을 위한 후보 열 (OLTP성 테이블의 열)

  - 등록, 수정, 삭제 등의 작업을 빠르게 하는 작업

. 주문일자

. 주문일자 + 순번

. Covering Index (Key Lookup이 발생하지 않는 경우)

. 넌클러스터 인덱스 생성 할 경우 (넌클러스터 인덱스 , 클러스터 인덱스 로 생성된다)

  (UnitPrice --> UnitPrice, OrderID, ProductID)

. Key Lookup + Join (넌클러스터 인덱스의 성능을 좌우한다)

. Index 또한 테이블이다 (일반 테이블의 종속 데이블이 된다)

. Index에 필요한 필드가 포함되면 Key Lookup이 발생하지 않는다.

. key columns : 정렬 대상

. non-key columns : 정렬 대상 아님, 필더링 대상 아님 : INCLUDE (non-key column1, ...)

  - 단순 Select만 하는 필드

. WHERE : 첫번째 = 조건이 오도록

. WHERE 사업장 LIKE @a  --> 사업장 = @a   or 사업장 in (@a)

. 조건자

. Seek 조건자 : 필드명이 보여야 한다

. select + update : 인덱스 적용된다.

. select + delete : 인덱스 적용된다.