전산이야기

SSMS 이전버전에서는 로그인 암호 파일이 별도로 있어서 해당 파일만 삭제 하면 해결 되었으나,

(SQL 서버 2008 SSMS 로그인시 암호저장이 잘 안될때 참조)

SSMS v.18에서는 해당파일 자체가 존재 하지 않아서 기존방법으로는 자동로그인 된 정보를

삭제할 수가 없습니다.

 

해결방법은 의외로 간단한데, 

로그인 화면에서 '로그인(L):' 드롭박스를 클릭해서 나오는 리스트에서 원하는 로그인명을 Del  키로 삭제하면 됩니다.

삭제 후 다시 로그인과 암호를 입력하고 '암호 저장'에 체크를 해서 성공적으로 로그인이 되면 암호저장이 정상적으로 됩니다.

저는 처음에 이걸 몰라서 좀 애를 먹었다는...^^

Comment +0

아시는 바와 같이 임시 테이블은 #(로컬) 및 ##(전역)이 prefix로 붙은 테이블 입니다.
그리고 테이블변수는 TABLE 타입으로 DECLARE된 로컬변수 입니다.
 
둘 간에 여러가지 차이점이 있지만 여기서는 성능적인 이슈만 언급 하겠습니다.
테이블변수의 문제는 SQL서버가 통계정보를 생성하지 않기 때문에 적절한 실행계획 수립이 안될 수 있다는 점 입니다.
 
아래는 테이블변수를 사용한 특정 SP의 실행계획 입니다 (처리속도는 14초).

NL JOIN 으로 처리되어 과도한 Execute, Rows가 나왔습니다. SQL 서버가 NL을 선택한 것은 예상 행수가 모두 1 이기 때문 입니다.
예상 행수가 1:1 이기 때문에 NL로 처리해도 아무 문제가 없다고 판단이 되었습니다.
실제 테이블변수의 행은 1067 입니다.
 
이제 다른것은 그대로 두고 테이블변수를 임시테이블로만 변경한 후, 다시 실행계획을 확인 해봅니다 (처리속도는 6초).

이번에는 HASH JOIN 으로 처리 되었습니다. 이유는 예상행수가 1067, 617 이기 때문 입니다.
1067건과 617건을 NL JOIN 으로 처리하면 부하가 크다고 판단하고 HASH로 JOIN 전략을 변경한 겁니다.
 
결론적으로 일반적으로 행수가 적은 경우는 테이블변수를 사용해도 무관하지만, 크기가 큰 경우는 임시테이블을 사용해야 성능이슈가 발행하지 않게 됩니다.
 
 

 

Comment +0

SP는 최초(처음) 실행 하는 시점에 실행계획을 수립하고 이후에는 실행계획을 계속 재사용 하게 됩니다

(이것이 SP를 사용하는 중요한 이유 중 하나 입니다).

이 최초 실행계획 수립시, 인수로 넘겨진 파라미터(매개변수)를 기준으로 통계를 통해 예상 행수를 추측하게 됩니다.

그런데 넘겨진 파라미터가 아닌 로컬 변수를 이용해서 쿼리를 실행하면 옵티마이저가 행수 추측이 실패하게 되어

원하지 않는 실행 계획이 생성 될 수 있습니다.

이로 인하여 SP 성능이 저하 될 수 있습니다.

 

예를 들어 다음과 같이 SP 가 되어 있을 때,

create proc Proc1 (
@p_date varchar(8)
)
as
SELECT
   *
FROM 
   SDSaleMaster a
WHERE
   a.SaleDate = @p_date

SP를 호출 하면,

EXEC Proc1 @p_date = '20190601'

실행계획에서 행수를 1326으로 예측 합니다.

이는 통계와 일치하는 제대로 된 예상 행 수 이기 때문에, 실행계획이 적합하게 나올 가능성이 높습니다.

그런데 다음과 같이 매개변수가 아닌 로컬 변수를 사용 하게 되어 있을 때,

create proc proc1 (
@p_date varchar(8)
)
as
declare
   @v_date varchar(8)

set @v_date = convert(varchar(8), dateadd(d, -1, @p_date), 112)  -- 하루 전

SELECT
   *
FROM 
   SDSaleMaster a
WHERE
   a.SaleDate = @v_date

SP를 호출 하면,

 EXEC Proc1 @p_date = '20190602'

옵티마이저가 예상 행수를 제대로 추정할 수가 없어서 549로 나오는 데, 이는 해당 테이블의 평균 밀도값을 이용한 것 입니다.

평균밀도

예상 행수 = 밀도 * 테이블 전체 row 수

 

이 글이 도움이 되셨으면 좋아요 눌러 주시거나 댓글을 남겨 주세요.

Comment +0

SQL 서버에서 서로 다른 데이터 타입간에 연산을 하게 되면
연산 전에 데이터 형식을 묵시적으로 형변환하게 되며,
이는 최종 결과 값의 데이터 형식이 된다.
그런데 여기에는 우선순위가 있기 때문에 때때로 연산결과 값에서 소수점이 사라지거나 오차가 발생 할 수 있으므로 주의해야 한다.

우선순위는 다음과 같다 (참조: https://docs.microsoft.com/ko-kr/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017)

  1. 사용자 정의 데이터 형식(가장 높음)
  2. sql_variant
  3. xml
  4. datetimeoffset
  5. datetime2
  6. datetime
  7. smalldatetime
  8. date
  9. time
  10. float
  11. real
  12. decimal
  13. money
  14. smallmoney
  15. bigint
  16. int
  17. smallint
  18. tinyint
  19. bit
  20. ntext
  21. text
  22. image
  23. timestamp
  24. uniqueidentifier
  25. nvarchar(nvarchar(max) 포함)
  26. nchar
  27. varchar (varchar(max) 포함)
  28. char
  29. varbinary (varbinary(max) 포함)
  30. binary(가장 낮음)

그리고 피연산자에 숫자 상수값이 들어가는 경우 상수의 표현 방식에 따라 데이터형이 결정 되므로 역시 주의 해야 한다.

  1. 이진상수 : 0x 접미사 (16진수)
  2. integer 상수: 소수점 없는 정수
  3. 10진수(decimal, numeric) 상수: 숫자에 소수점이 포함
  4. float 및 real 상수: 과학 표기법 (예: 0.5E-1)

함수의 결과 값도 원래 컬럼의 데이터타입과 다른 경우가 있으므로 주의 해야 한다.
  1. SUM() :  decimal에 대한 계산 결과는 무조건 decimal(38, s) 가 된다.
                 s는 원래 decimal 타입 값의 소수점이하 자리 수.
                 전체 자리수가 무조건 38이 되므로 주의!
  2. CEILING(), FLOOR() : decimal에 대한 결과는 decimal(p, 0)이 된다.
                                p는 원래 decimal 타입 값의 전체 자리 수.

decimal 타입간의 연산 결과는 두 decimal 타입 중 더 넓은 범위의 값을 저장할 수 있는 타입이 우선순위를 가지는 것으로 보인다.
예를 들어 decimal(38, 5) 와 decimal(38, 2) 의 결과는 decimal(38, 2)로 된다.
소수점 이하 정밀도 보다는 정수 부분이 더 큰/작은 넓은 범위의 숫자를 저장할 수 있는 타입에 우선순위가 있는  것 같다.
특히 decimal에 대한 SUM()의 결과는 무조건 decimal(38, s)이 되고 CEILING()은 decimal(p, 0)이 되므로
만약 SUM()에 의하여 decimal(38, 5)가 되고, CEILING()에 의해 decimal(38, 0)이 된 경우 그 결과는 decimal(38, 0)이 되어
데이터에서 소수점이하 값이 사라질 수 있으므로 주의 해야 한다.

SELECT * 
INTO   #c 
FROM   (SELECT Sum(CONVERT(DECIMAL(38, 5), 123.45)) AS x 
        UNION 
        SELECT Ceiling(CONVERT(DECIMAL(38, 5), 456.78)) AS x) a 
go 

SELECT * 
FROM   #c 



Comment +0

윈도우 환경에서 Oracle Client 설치를 해도 ADO를 이용한 접속이 제대로 안되는 경우가 있다.

TNS, 포트 등 기본적인 설정상의 문제가 아니라면 다음을 확인 해보자.


1) OraOLEDB.Oracle 경우

  접속 문자열에서 Provider를 'OraOLEDB.Oracle' 로 지정한 경우 시스템에 'Oracle Provider for OLE DB' 공급자가 정상적으로

  등록되어 있어야 한다. 

  등록여부는 다음과 같이 확인 할 수 있다.

 

  1. 확장자 'UDL'로 임의의 파일을 생성 한다.

  2. 해당 파일의 속성 창에서 '공급자' 페이지를 확인 한다.  

   

  만약 위와 같이 'Oracle Provider for OLE DB' 가 나타나지 않으면 Oracle 사이트에서 ODAC를 받아서 설치를 한다.

  (http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html) 

 

  그런데 기존에 Oracle client 설치시 함께 설치를 했는데도 나오지 않는 경우에는 regsvr32 명령으로 OraOLEDB11.dll(11g 기준)을

  레지스트리에 다시 등록을 해 본다.

  만약 ODAC를 기존 Oracle client와 다른 별도의 경로에 설치 한 경우, network\admin\tnsnames.ora 파일에 TNS를 등록해야 한다.

  그리고 32비트, 64비트용이 각각 존재 하므로 만약 64비트 윈도우즈 환경에서 32비트 프로그램이 Oracle 접속이 되지 않는다면

  32비트 ODAC의 설치가 필요 하다.

 

  참고로 공급자에 제대로 등록이 되어 있지 않은 상태에서 Oracle 접속을 시도할 경우 경우 다음과 같은 오류가 발생 한다. 

 

 

2) MSDAORA.1 경우

  접속 문자열에서 Provider를 'MSDAORA.1'로 지정한 경우 시스템에 'Microsoft OLE DB Provider for Oracle' 공급자가 정상적으로

  등록되어 있어야 한다. 위와 동일한 방법으로 확인 할 수 있다. 

 

  그런데 MSDAORA는 32비트에서만 제공된다. 64비트에서는 더 이상 지원이 안되기 때문에 사용할 수가 없다.

  처음에 이것을 몰라서 엄청 애를 먹었다. 꼭 Provider를 MSDAORA를 사용 해야 한다면 어플리케이션을 32비트로 컴파일 해서 

  사용하고 그렇지 않으면 OraOLEDB.Oracle 를 대신 사용해야 한다.

 

  참고로 공급자에 제대로 등록이 되어 있지 않은 상태에서 Oracle 접속을 시도할 경우 경우 다음과 같은 오류가 발생 한다.

  어떤 경우에 아래와 같이 각각 다른 형태의 메시지가 나타나는 지는 알 수가 없었다. 


 


 

 

이 글이 도움이 되셨으면 댓글을 남겨 주세요!

Comment +0

SQL서버 2012 이후 새로운 함수가 추가 되었는데 잘 모를 수 있어서 
공유하니 개발시에 도움되기 바랍니다.

1. 월의 마지막 일자

이전에는 월의 마지막 일자를 구하기 위하여 이런식 비슷하게 사용했을 것이다.
(여기서는 전월의 마지막 일자)


SELECT CONVERT(VARCHAR(8), DATEADD(D, -1, CONVERT(VARCHAR(6), GETDATE(), 112) + '01'), 112)

 

SQL서버 2012이후 제공되는 EOMONTH, FORMAT 함수를 이용하면 간단히 다음과 같이 할 수 있다.


SELECT FORMAT(EOMONTH(getdate(), -1), 'yyyyMMdd')



2. 숫자에 천단위 표시

위에서 이미 사용해 본 FORMAT 함수를 이용하면 된다.

SELECT FORMAT(123456789, '#,#'



​3. CASE​를 간단하게

아래 쿼리를 

SELECT  CASE DATEPART(WEEKDAY, GETDATE())

                                WHEN 1 THEN '일요일'

                                WHEN 2 THEN '월요일'

                                WHEN 3 THEN '화요일'

                                WHEN 4 THEN '수요일'

                                WHEN 5 THEN '목요일'

                                WHEN 6 THEN '금요일'

                                WHEN 7 THEN '토요일'

                     END

 

CHOOSE함수를 이용해서 다음과 같이 간단하게 구현할 수 있다.


SELECT CHOOSE(DATEPART(WEEKDAY, GETDATE())                     

    , '일요일','월요일','화요일','수요일','목요일','금요일','토요일')

 


​4. 이건 2012에 추가된 것은 아니지만 기억상실증을 예방 하기 위하여...

문자열이 날짜인지, 숫자인지 확인하는 함수

SELECT ISDATE('20161032'), ISDATE('20161031')

SELECT ISNUMERIC('123'), ISNUMERIC('a123'), ISNUMERIC('123a')

 


Comment +0

① 저장 프로시저(이하 SP) 존재 여부 검사

  IF EXISTS (SELECT * FROM sysobjects WHERE id=object_id('SP명') AND objectproperty(id, 'IsProcedure')=1) ...


② SP가 배치(batch)의 첫번째 문장에서 실행된다면 EXECUTE 는 필요하지 않다.


③ SP 정의시 body는 BEGIN END 로 묶지 않아도 된다. 그냥 AS 뒤에 T-SQL이 오면 된다(사용자 함수 정의와 차이점 이다. 함수는 BEGIN END 사이에 정의 되어야 한다).


④ SP가 정보를 되돌릴 수 있는 방법은 3가지 이다.

  1. ResultSet (SELECT 문장 또는 다른 SP 호출로 가능)

  2. OUTPUT 파라미터

  3. 리턴값


  리턴값은 정수만 가능 하다. 리턴값이 설정되어 있지 않고 그냥 RETURN 만 하는 경우 기본값인 0을 되돌려 준다.

  SP호출시 리턴값은 다음과 같은 식으로 전달 받을 수 있다.

   

  DECLARE @intRet INT

  EXECUTE @intRet = Proc1 Pram1


⑤ SP에 매개변수값을 넘기는 방법에는 위치 매개변수 전달과 이름 매개변수 전달 방법이 있다.

    전자의 경우 SP에서 정의된 파라미터의 순서와 일치하게 넘기는 것이고 후자의 경우 @를 포함한 파라미터이름을 지정하여 넘기는 것이다.

    이름 매개변수 전달은 다음과 같은 식으로 한다.


   EXEC Proc1 @Param1 = 100, @Param2 = 'FOO'


⑥ SP 재컴파일

  때때로 Index 생성등을 통해 실행계획이 변경될 것을 예상 했으나 기존의 실행계획을 계속 유지하는 경우가 있다. 이럴 때는 SP를 재컴파일 해주면 새로운 실행계획이 반영될 수 있다.

특정 SP를 재컴파일 하기 위해서는 다음과 같이 한다.

  

  EXEC sp_recompile SP명


그런데 SP의 매개변수 값에 따라 데이터 분포도가 차이가 많이 나는 경우 하나의 실행계획으로만 처리되면 성능이 나오지 않는 경우가 있다. 이때는 다음과 같이 WITH RECOMPILE 옵션을 이용해서 SP를 정의하면 SP가 호출 될 때마다 매번 실행계획을 다시 수립하여 성능이 향상 될 수 있다.


CREATE PROC SP명

WITH RECOMPILE

AS


⑦ SP명 바꾸기

  ALTER PROC 으로는 SP명을 변경할 수 없다. 이를 때 sp_rename 프로시저를 호출하여 SP명을 변경하면 된다.


  EXEC sp_rename SP명1, SP명2


⑧ SP 종속(의존) 객체 확인

  sp_depends 를 이용하면 특정 SP에서 사용하고 있는 테이블, 컬럼등의 객체정보를 확인 할 수 있다.

  

  EXEC sp_depends SP명


⑨ 긴 SP 내용 검색

  SP 소스코드는 syscomments 시스템 테이블에 기록되는데, SP 소스 크기가 8000바이트 이상인 경우 2개 이상의 row로 소스가 분할 해서 저장이 된다. 그래서 여러 문자를 AND 로 LIKE 검색 하는 경우 제대로 해당하는 SP를 찾지 못할 수 있다. 이럴 때 다음과 같이 하면 원하는 SP를 찾을 수 있다. 


SELECT a.name

FROM sysObjects a

    INNER JOIN (    

        SELECT 

            id , STUFF ((

                SELECT ',' + text

                FROM sysComments y

                WHERE y.id = x.id

                FOR XML PATH ('')

                ) , 1,1 , '') AS AllText

        FROM  syscomments x

        GROUP BY id

    ) b

    ON a.id = b.id

WHERE    

    a.xtype = 'P'

    AND b.AllText Like '%문자열1%'

    AND b.AllText Like '%문자열2%'


Comment +0

SQL서버의 트리거에서는 UPDATE() 함수를 이용해서 컬럼이 수정되었는 지를 확인 할 수 있는데

실제로 값이 변경되었는지가 아니라 단순히 UPDATE 구문의 SET 에 해당 컬럼이 사용되었는 지를

나타낸다고 보는게 더 맞는것 같습니다.


아래와 같이 테스트를 해 보았습니다.

우선 테스트용 테이블을 만들고 트리거를 작성 합니다.


CREATE TABLE TriggerTest
(id int identity Primary Key,
 prodName varchar(10),
 Spec varchar(10),
 Qty int
)
go

CREATE TRIGGER TR_TriggerTest_AU 
ON TriggerTest
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
   IF UPDATE(Qty) 
   BEGIN
        SELECT 'Qty is updated', *
        FROM inserted
   END

   SELECT
        'Qty is modified', a.*
   FROM 
    inserted a
        LEFT JOIN deleted b
            ON a.id = b.id
   WHERE
    a.Qty <> b.Qty
END
go

테스트용 데이터 몇개를 INSERT 해 봅니다.

 
insert into TriggerTest
 values ('Prod1', '2*3', 100)

 insert into TriggerTest
 values ('Prod2', '10*3', 200)

 insert into TriggerTest
 values ('Prod3', '8*5', 300)
 go

 insert into TriggerTest
 values ('Prod4', '15*10', 400)
 go


이 때 트리거에 의해서 다음과 같이 출력 되는걸 확인 할 수 있는데

이를 통해서 UPDATE() 함수에서는 INSERT 했을 때도 true 값을 반환한다는 것을 알 수 있습니다.



이번에는 아래와 같이 트리거에서 체크하지 않는 컬럼에 대하여 UPDATE를 시도 해 보면

UPDATE 구문에 Spec 컬럼만 있기 때문에 트리거에서 아무것도 처리되고 있지 않습니다.


다음으로 트리거에서 체크 하고 있는 Qty 컬럼에 대하여 UPDATE를 해보면 당연히 다음과 같이 조회가 됩니다.



위에서는 Qty 컬럼의 값을 실제로 변경 시키고 있는데 이번에는 컬럼 값을 동일하게 UPDATE 해 보겠습니다.

실무적으로는 UPDATE용 SP를 작성 해 놓고 해당 SP에 들어오는 인자 값을 이용해서 UPDATE 구문을 수행하다보니

특정 테이블의 모든 컬럼을 UPDATE 구문에 포함하게 되어서 실제로 수정된 컬럼이 아닌 컬럼도 (수정 전후가 동일한 값으로) UPDATE 처리가 되기 때문에 이 경우를 주의 깊게 봐야 할 것 입니다.

결과를 보면 Qty 컬럼은 수정 전후 값이 동일하지만 UPDATE() 함수는 true 값을 반환 했다는 것을 알 수 있습니다.



결론은, 트리거 내에서 특정 컬럼의 수정여부(실제로 값의 변화가 있는 지)를 확인 하려면 UPDATE() 함수를 사용해서는 알 수가 없고 inserted 테이블 과 deleted 테이블을 PK로 JOIN 하여 해당 컬럼의 값을 서로 비교해야 한다는 것 입니다.

이 방법 말고 더 좋은 방법을 알고 계신 분은 댓글 부탁 드립니다.

Comment +0

트리거 내에서 RAISERROR 로 오류를 생성 할 경우, 심각도(severity)가 20~25인 경우에는

 

자동으로 rollback 처리 된다.

 

(예)

CREATE TABLE Test1
(number INT IDENTITY,
 bookname nvarchar(100),
 UnitPrice INT,
 Qty INT,
 Price INT
)

CREATE TRIGGER TR_Test1_INSERT
ON Test1
FOR INSERT
AS
    update Test1
    Set Price = (select UnitPrice from inserted) * (select Qty from inserted)
    where number = (select number from inserted)

    RAISERROR ('Error raised!', -- Message text.
               20, -- Severity.
               1 -- State.
               ) with log;

 

insert into Test1 (bookname, UnitPrice, Qty)
values ('book1', 600, 2)

 

 

Comment +0

어떤 테이블을 삭제하려고 할 때 "FOREIGN KEY 제약 조건에서 참조하므로 삭제할 수 없습니다" 라는 오류가 나면서


삭제가 안되는 경우가 있다. 이는 해당 테이블을 외래키로 참조하는 다른 테이블이 존재 하기 때문 이다.


그런데 삭제 하려는 테이블 자체에 대한 외래키 제약조건은 sp_helpconstraint 로 확인 할 수 있으나,


해당 테이블을 참조하는 다른 테이블은 sp_helpconstraint 로는 알 수가 없다.


이럴때는 다음과 같이 하면 참조하고 있는 다른 테이블과 연결된 컬럼명을 확인 할 수 있다.


SELECT 
   f.name,
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = '테이블명'


Comment +0