MsSQL Server_강제 매개변수화 테스트

스터디에서 나온 질문 중에 다음과 같은 질문이 있어서 해당 포스트를 작성하였습니다.

 

질문 : Forced param할 때 캐릭터 타입 선언된 길이까지 같아야 재컴파일을 하는가? 

또한 강제매개 변수화가 이해가 안가시는 분들은 아래의 링크를 참조하시기 바랍니다.

강제 매개변수화 : http://technet.microsoft.com/ko-kr/library/ms175037(v=sql.105).aspx

단순 매개변수화 : http://technet.microsoft.com/ko-kr/library/ms186219(v=sql.105).aspx

 

우선 현재 강제매개변수화를 선언해 줍니다.

ALTER DATABASE WorkDB SET PARAMETERIZATION FORCED GO

 

선언이 잘 되었는지 확인 합니다.

SELECT name, is_parameterization_forced FROM sys.databases

 

0 = 강제매개변수화

1 = 단순매개변수화

 

 테이블을 만듭니다.

CREATE TABLE Forced_test (  ID           int ,Name  varchar(20) )

단순한 값만 입력 받을 수 있도록 하는 테이블 입니다.

 

그 후에 정확하고 깔끔하게 저장된 실행계획을 보기 위해서 실행계획을 비워주도록 하겠습니다.

DBCC FREEPROCCACHE

 

그리고 다음 adhoc 쿼리를 순차적으로 실행했습니다.

insert into Forced_test values (1,'주형권') insert into Forced_test values (1,'주형권주형권SQLTAG')

 

그 후에 실행계획은 어떻게 저장 하였을까요?

select cp.objtype,st.text,cp.usecounts from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_sql_text(cp.plan_handle)st order by text

확인 결과 다음과 같습니다.

 

 

 

같은 쿼리로 인식했습니다.

그렇다면 어떻게 하면 다른 쿼리로 인식할까요?

 

insert into Forced_test values (1,'주형권') insert into Forced_test values (1,N' 주형권SQLTAG')

 

문자를 Nvarchar로 날렸더니 다르게 인식했습니다. (필자가 한번 더 해봐서 count가 3개가됨)

 

 

이로써 우리는 길이는 상관이 없고 형식은 상관이 있는것을 볼 수 있습니다. 

근대 여기서 한가지 궁금한게 있습니다. varchar(8000)인데... 그럼 varchar(max)로 하고 8000자 이내와 8000자를 넘기면 어떻게 될까요?

 

 

아까 분명히 길이는 상관없는지 알았습니다. 하지만 다르게 인식합니다.

한개는 8000바이트를 넘고 한개는 넘지 않습니다. 8000바이트를 넘으니 max로 변환하더군요.

 

 

답변 : varchar8000 이내는 상관이 없었습니다. 하지만 비유니코드 nvarchar와 varchar는 다르게 인식하고 varchar(max)여도 8000자를 넘을때와 넘지 

않을 경우 다르게 나타났습니다.

즉, 길이와 형식 모두 관련이 있다는 것을 알 수 있습니다