달력

32024  이전 다음

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
 

/*********************************************************************

 

SQL SERVER 2005 이상의 64BIT 머신에서 링크드 서버연결시

아래의 메시지가 출력되면서 오류가 발생함.

 

*********************************************************************/

 

연결된 서버 "APP"의 OLE DB 공급자 "SQLNCLI10"이(가) 메시지 "지정되지 않은 오류입니다."을(를) 반환했습니다.

연결된 서버 "APP"의 OLE DB 공급자 "SQLNCLI10"이(가) 메시지 "이 작업을 완료하는 데 필요한 저장 프로시저가 서버에 없습니다. 시스템 관리자에게 문의하십시오."을(를) 반환했습니다.

Msg 7311, Level 16, State 2, Line 1

연결된 서버 "APP"에 대한 OLE DB 공급자 "SQLNCLI10"의 스키마 행 집합 "DBSCHEMA_TABLES_INFO"을(를) 가져올 수 없습니다. 공급자가 인터페이스를 지원하지만 이 인터페이스를 사용하면 오류 코드가 반환됩니다.

SQL Server 구문 분석 및 컴파일 시간:

   CPU 시간 = 0ms, 경과 시간 = 0ms.

 

 

 

/*********************************************************************

 

아래의 프로시저를 오류가 발생한 링크드 서버의 MASTER 데이터베이스에

생성함으로 문제 해결됨.

 

*********************************************************************/

create procedure sp_tables_info_rowset_64

      @table_name sysname,

      @table_schema     sysname = null,  

      @table_type nvarchar(255) = null

as

 

declare @Result int set @Result = 0

 

exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

 

Posted by tornado
|


Save (Not Permitted) Dialog Box

SQL Server 2008 R2

The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created.

The following actions might require a table to be re-created:

  • Adding a new column to the middle of the table

  • Dropping a column

  • Changing column nullability

  • Changing the order of the columns

  • Changing the data type of a column

To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

Posted by tornado
|
Sql Server Management Studio 를 설치할 때 Express 버전을 깔면 Profiler 가 안깔림. 

또는 Visual Studio 설치를 먼저 하면 또 Profiler 가 안깔릴 수도 있음. 왜 Express 버전을 넣어놔서 T.T

프로파일러를 사용할 다른 방법이 없나.. 찾아보니, 역시 있음.

http://sites.google.com/site/sqlprofiler/ 

 별도 어플로 깔리는데, 사용하는 방법은 동일하다.

 
Posted by tornado
|

[출처] http://jekyung.com/tc/entry/MSSQL-%C5%D7%C0%CC%BA%ED%BA%B0-size-%B9%D7-row-%B1%B8%C7%CF%B1%E2



1. 개요
 가. MSSQL DB 의 테이블별 size 및 row 를 보여주는 쿼리문입니다.

 나. 테스트환경

2. MSSQL 테이블별 size 및 row 구하기
 가. 테이블 size 구하는 쿼리문
       SELECT
             table_name = convert(varchar(30), min(o.name)), table_size = ltrim(str(sum
             (reserved) * 8192 / 1024.,15,0) + 'KB')
       FROM sysindexes i
             INNER JOIN sysobjects o on (o.id = i.id)
       WHERE i.indid in (0, 1, 255)
             and o.xtype = 'U'
       GROUP BY i.id
       order by table_size desc

       (큰사이즈의 테이블로 인한 오버플로우 오류 발생시 수식 부분을 수정하셔야
         합니다.)

 나. 테이블별 row 구하는 쿼리문
       SELECT
             o.name
             , i.rows
       FROM sysindexes i
              INNER JOIN sysobjects o ON i.id = o.id
       WHERE i.indid < 2
              AND o.xtype = 'U'
       ORDER BY i.rows desc
Posted by tornado
|

[링크] http://www.devholic.net/1000495

MS-SQL Server 2005 에서 외부 조인 연산자("*=" 또는 "=*")를 사용할때 다음과 같은 오류가 발생한다.

 

쿼리에서 ANSI 형식이 아닌 외부 조인 연산자("*=" 또는 "=*")를 사용합니다.

이 쿼리를 수정하지 않고 실행하려면 저장 프로시저 sp_dbcmptlevel을 사용하여 현재 데이터베이스의 호환성 수준을 80 이하로 설정하십시오.

가장 좋은 방법은 ANSI 외부 조인 연산자(LEFT OUTER JOIN, RIGHT OUTER JOIN)를 사용하여 쿼리를 다시 작성하는 것입니다.

SQL Server의 다음 버전에서는 ANSI 형식이 아닌 조인 연산자는 역호환성 모드에서도 지원되지 않습니다.

 

메세지 내용처럼 호환성 수준을 80으로 설정하려면

1.-- 현재 호환성 수준 확인하기
2.sp_dbcmptlevel 'DB명'
3.   
4.-- 호환성을 80으로 설정하기
5.sp_dbcmptlevel 'DB명', 80

 

허나 기왕이면 LEFT OUTER JOIN, RIGHT OUTER JOIN을 사용하길 권장한다.

Posted by tornado
|



declare @money money

set @money = 123456 

select replace(convert(varchar(15),convert(money,@money),1),'.00','')

 

-.-

 

Sql money 타입으로 캐스팅 하고 그것을 다시 convert 1 주면 1,000,000.00 같이 나오는데

그중에 .00 공백으로 치환하니 간단히 되는군요

Posted by tornado
|
원글 : http://sqlmania.net/board/view.asp?boardid=7&idx=24&page=1&srchKey=&srchVal=


출처:MSDN
---------------------------------------------------------------------------------------------------

Text, Ntext, Image에 대한 간략한 자습서

Joseph Gama

Text, Ntext, Image 데이터는 오래도록 여러 가지로 사용되어 왔지만 서로 간의 차이를 간과하기가 쉽습니다. Joseph Gama는 이 자습서에서 이러한 특수 데이터 형식의 구현과 사용에 대한 개요를 간략하게 설명합니다. Joseph Gama는 특별히 지도해 준 Metropolitan State College of Denver의 Aaron Gordon, Earl Hasz, Jerry Shultz, Shahar Boneh 박사님들과 아낌없이 지원해 준 Adam과 Karen Schwartz에게 이 기사를 바칩니다.

데이터베이스의 크기와 복잡성이 커지고 있는 원인이 부분적으로는 오늘날의 하드웨어와 소프트웨어가 멀티미디어 및 문서 데이터를 비롯하여 엄청난 양의 데이터를 저장할 수 있게 되었기 때문이라고 볼 수 있습니다. JPG, PNG, MP3, DOC/RTF, HTML, 유니코드, XML 데이터를 SQL Server 데이터베이스에 image, text 또는 ntext로 모두 저장할 수 있습니다.

일반적으로 큰 ASCII 문자열을 저장하는 데는 text를 사용하고 유니코드 문자열에는 ntext, 이진 이미지 데이터에는 image를 사용합니다. 크기의 경우 Text는 최대 2^31 - 1(2,147,483,647)자의 가변 길이 비 유니코드 문자를 제공하고 ntext는 최대 2^30 - 1(1,073,741,823)자, image는 최대 2^31 - 1(2,147,483,647)바이트를 제공합니다. ntext의 실제 저장소 크기(바이트 단위)는 입력한 문자 수의 두 배입니다. ntext의 SQL-92 동의어는 national text입니다.

이제 이러한 형식이 어떻게 작동하는지 살펴보겠습니다. 데이터를 참조할 때는 포인터를 사용하며 특수 함수에서 포인터를 통해 데이터를 그러한 형식에서 추가, 추출 또는 제거할 수 있습니다. 표 1에서는 각각의 장점과 제한 사항을 요약합니다.

표 1. text, ntext, image 데이터 형식의 장점과 제한 사항

가능

불가능

저장 프로시저의 입력 또는 출력 매개 변수로 사용됩니다.

DECLARE, SET, FETCH와 작동합니다. 즉, 다른 데이터 형식과 똑같이 변수로 사용할 수 없습니다.

UDF에 대한 입력으로 사용됩니다.

UDF의 반환 데이터 형식이 됩니다(타임스탬프도 불가능).

최대 8,000바이트를 다른 데이터 형식으로 변환합니다.

변환을 수행하지 않을 경우 FETCH로 커서에서 읽어들여집니다.

UNION ALL 절에 사용합니다.

sql_variant와 작동합니다.

GROUP BY 절에서 비교, 저장 또는 사용됩니다. 유일한 예외는 IS NULL 또는 LIKE를 사용할 경우입니다. 쉽게 해결할 수 있는 방법은 다른 데이터 형식을 반환하는 사용자 정의 함수나 CONVERT를 사용하는 것입니다.

이 형식의 UNION은 DISTINCT 절에 해당하므로 UNION 절에서 사용되며 text, ntext, image 데이터 형식은 정렬할 수 없으므로 오류가 발생합니다.

Text 및 image 함수

여기서는 여러 가지 text 함수와 image 함수에 대해 설명하면서 해당 구문과 각각의 출력 예제를 보여 줍니다.

TEXTPTR
TEXTPTR은 text, ntext 또는 image 열을 참조하는 text-pointer 값인 16바이트 길이의 varbinary를 반환합니다.

구문: TEXTPTR ( column )

--TEXTPTR 샘플, text-pointer를 만들고 해당 값을 봅니다. create table #t (n ntext) insert #t values('abcdef') DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR(n) FROM #t print @ptrval drop table #t

결과:

0xFFFF6900000000004D00000001000000

TEXTVALID
TEXTVALID는 text-pointer가 유효할 경우 값 1이 되고 그렇지 않으면 0이 되는 int를 반환합니다.

구문: TEXTVALID ( 'table.column' , text_ptr )

--TEXTPTR 샘플, text-pointer를 만들고 해당 값을 테스트합니다. create table #t (n ntext) insert #t values('abxyef') DECLARE @ptrval binary(16), @ptrval2 binary(16) SELECT @ptrval = TEXTPTR(n) FROM #t if TEXTVALID('#t.n',@ptrval)=1 print '@ptrval has a valid text pointer.' else print '@ptrval has an invalid text pointer.' if TEXTVALID('#t.n',@ptrval2)=1 print '@ptrval2 has a valid text pointer.' else print '@ptrval2 has an invalid text pointer.' drop table #t

결과:

@ptrval has a valid text pointer. @ptrval2 has an invalid text pointer.

SET TEXTSIZE
SET TEXTSIZE는 SELECT 문을 사용할 때 반환되는 text와 ntext 데이터의 크기인 int 값을 설정합니다.

구문: SET TEXTSIZE { number }

--SET TEXTSIZE 샘플 create table #t (n ntext) insert #t values('abcdefghijk') SET TEXTSIZE 10--ntext는 유니코드 2바이트/문자임 select * from #t SET TEXTSIZE 20--ntext는 유니코드 2바이트/문자임 select * from #t drop table #t

결과:

abcde abcdefghij

@@TEXTSIZE
@@TEXTSIZE는 SELECT 문을 사용할 때 반환되는 text와 ntext 데이터의 크기인 int 값을 반환합니다. 이 값은 SET TEXTSIZE를 사용하여 설정됩니다.

구문: @@TEXTSIZE

--@@TEXTSIZE 샘플 SET TEXTSIZE 10--ntext는 유니코드 2바이트/문자임 print @@TEXTSIZE SET TEXTSIZE 20--ntext는 유니코드 2바이트/문자임 print @@TEXTSIZE

결과:

10 20

WRITETEXT
WRITETEXT는 text, ntext, image 열의 데이터를 덮어씁니다.

구문: WRITETEXT { table.column text_ptr } [ WITH LOG ] { data }

--WRITETEXT 샘플 create table #t (n ntext) insert #t values('abc') DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR(n) FROM #t WRITETEXT #t.n @ptrval 'def' select * from #t drop table #t

결과:

def

UPDATETEXT
UPDATETEXT는 기존 text, ntext, image 열의 데이터를 변경합니다.

구문: UPDATETEXT { table_name.dest_column_name dest_text_ptr } { NULL | insert_offset } { NULL | delete_length } [ WITH LOG ] [ inserted_data | { table_name.src_column_name src_text_ptr } ]

--UPDATETEXT 샘플 삽입 전용 create table #t (n ntext) insert #t values('bd') DECLARE @ptrval binary(16), @i int SELECT @ptrval = TEXTPTR(n) FROM #t UPDATETEXT #t.n @ptrval 0 0 'a'--맨 앞에 삽입 select * from #t UPDATETEXT #t.n @ptrval 2 0 'c'--중간에 삽입 select * from #t set @i=(select DATALENGTH(n) from #t)/2 --/ntext일 경우만 2, 2바이트/문자 print @i UPDATETEXT #t.n @ptrval @i 0 'e'--맨 끝에 삽입 select * from #t drop table #t

결과:

abd abcd abcde

샘플 삭제 및 삽입:

--UPDATETEXT 샘플 삭제+삽입 create table #t (n ntext) insert #t values('abxyef') DECLARE @ptrval binary(16), @i int SELECT @ptrval = TEXTPTR(n) FROM #t UPDATETEXT #t.n @ptrval 2 2 'cd'--2 삽입, 2 삭제 --위치 2에서 시작하는 문자 select * from #t drop table #t

결과:

abcdef

READTEXT
READTEXT는 text, ntext, image 열의 데이터에서 일정량을 읽습니다.

구문: READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]

--READTEXT 샘플 create table #t (n ntext) insert #t values('abcdefghijk') DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR(n) FROM #t READTEXT #t.n @ptrval 3 8 --위치 3에서 시작하여 여덟 문자를 읽습니다. drop table #t

결과:

defghijk

DATALENGTH
DATALENGTH는 text, ntext, image 열의 크기(바이트 수)를 반환합니다.

구문: DATALENGTH ( expression )

--DATALENGTH 샘플 create table #t (n ntext) insert #t values('1234567890') DECLARE @i int set @i=(select DATALENGTH(n) from #t) --길이(바이트 단위)=2*UNICODE 길이로 반환해야 합니다. PRINT @i drop table #t

결과:

20

PATINDEX
PATINDEX는 text, ntext, image 열에서 패턴이 처음 나오는 위치를 int 값으로 반환하거나 패턴이 없으면 0을 반환합니다.

구문: PATINDEX ( '%pattern%' , expression )

--PATINDEX 샘플 create table #t (n ntext) insert #t values('Hello Tim, long time no see!') SELECT PATINDEX('%tim%', n) FROM #t SELECT PATINDEX('%time%', n) FROM #t drop table #t

결과:

7 17

CONVERT
CONVERT는 다른 데이터 형식으로 변환된 식을 반환합니다.

구문: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

--CONVERT 샘플 create table #t (n ntext) insert #t values('Hello Tim, long time no see!') DECLARE @c nvarchar(5) SET @c=(select convert(nvarchar(5),n) from #t) print @c drop table #t

결과:

Hello

CAST
CAST는 다른 데이터 형식으로 캐스트된(변환된) 식을 반환합니다.

구문: CAST ( expression AS data_type )

--CAST 샘플 create table #t (n ntext) insert #t values('Hello Tim, long time no see!') DECLARE @c nvarchar(5) SET @c=(select CAST ( n AS nvarchar(5) ) from #t) print @c drop table #t

결과:

Hello

일반적인 구현

이제 배경 지식이 좀 생겼으니 일반적인 사용 방법을 살펴보겠습니다.

text, ntext, image 열을 파일에 저장
text, ntext 또는 image 형식의 열 하나를 파일에 저장할 수 있는 방법을 보여 주는 저장 프로시저를 각각 세 개 만들었습니다. 이러한 코드와 다른 모든 예제를 파일 다운로드를 통해 볼 수 있습니다.

--saveText2file 샘플 create table ##t (n text) insert ##t values('Hello Tim, long time no see!') EXEC saveText2file 'c:\test.txt', '##t','n', '' drop table ##t --saveNtext2file 샘플 create table ##t (n ntext) insert ##t values('Hello Tim, long time no see!') EXEC saveNtext2file 'c:\test.txt', '##t','n', '' drop table ##t --saveImage2file 샘플 exec saveImage2file 'c:\Category1.bak', 'Northwind..Categories', 'Picture', 'where categoryid=1'

파일의 text, ntext, image 열 업데이트
TEXTPTR, WRITETEXT, UPDATETEXT의 경우 테이블 또는 열 매개 변수를 정의할 때 변수 이름을 사용할 수 없으므로 파일 내용을 열에 읽어들려면 동적 SQL을 사용해야 합니다. 저장 프로시저 readImageFromfile에서는 데이터를 이진 데이터로 읽고 임시 테이블을 사용하지 않은 상태로 데이터를 기록하므로 image와 varchar 데이터 형식을 모두 처리할 수 있습니다. Ntext는 readNtextFromfile을 사용하여 읽을 수 있습니다.

--readImageFromfile 샘플 --파일의 text 열 읽기 create table ##t (n text) insert ##t values('Hi Tim, long time no see!') EXEC readImageFromfile 'c:\hello.txt', '##t','n', '' select * from ##t drop table ##t

결과:

Hello

동일한 코드가 image 열에 대해 작동합니다. 이 저장 프로시저에서는 변환이 이루어지지 않으므로 ntext 열에는 ASCII 텍스트 파일의 데이터를 받아서는 안 되고 text 열에는 유니코드 데이터를 받아서는 안 된다는 것만 기억하면 됩니다. 데이터는 원시 형식으로 제공됩니다.

저장 프로시저 readImageFromfile2는 원래 내용을 바꾸지 않고 데이터를 추가하며 readNtextFromfile은 Ntext(유니코드) 데이터를 읽습니다. 업데이트는 원래 데이터를 새 데이터의 첫째 블록으로 먼저 바꾼 다음 새로운 데이터 블록을 연속적으로 추가하여 수행되지만 추가 시에는 첫째 단계가 필요 없으므로 데이터를 추가하는 것이 업데이트하는 것보다 쉽습니다. ntext 읽기는 유니코드가 Little Endian이 되어야 하는 것 외에는 기본적으로 다른 데이터 형식을 읽는 것과 동일합니다. 텍스트 프로세서는 0xFFFE를 유니코드 텍스트 파일의 맨 앞에 추가하는 경우가 많습니다. 저장 프로시저 readNtextFromfile에는 유니코드의 Big Endian과 Little Endian 변형을 구분할 때 사용하는 "바이트 순서 표시"(BOM)인 0xFFFEm을 제거하는 추가 코드가 약간 있습니다.

파일에서 유니코드 Big Endian을 읽어서 Little Endian으로 변환하는 readNtextFromfileBigEndian이라는 또 다른 저장 프로시저를 보려면 파일 다운로드를 참고하십시오.

개체의 텍스트를 파일로 저장
ntext 열이 포함된 임시 테이블을 사용하면 규칙, 기본값, 암호화되지 않은 저장 프로시저, UDF, 트리거 또는 뷰의 텍스트를 파일로 읽을 수 있습니다. 임시 테이블이 만들어진 다음 INSERT EXEC 기술로 새 레코드가 삽입됩니다. 이 레코드에는 시스템 저장 프로시저 sp_helptext로 sysobjects에서 읽어들인 개체의 텍스트가 포함됩니다. 다음 코드가 포함된 저장 프로시저 saveobj에서 수행됩니다.

create table #temp(s nvarchar(4000)) insert #temp exec sp_helptext @object

저장 프로시저 saveobj는 규칙, 기본값, 암호화되지 않은 저장 프로시저, UDF, 트리거 또는 뷰를 텍스트 파일에 저장하여 백업 및 문서화에 아주 유용하도록 할 수 있습니다.

--saveobj 샘플, hello 개체 저장 exec saveobj 'c:\hello.sql', 'hello'

파일에서 개체 텍스트 읽기
8,000바이트가 넘는 파일을 읽으려면 많은 버퍼가 파일 크기에 따라 채워진 다음 나중에 실행되므로 동적 SQL을 사용해야 합니다. 저장 프로시저 readobj는 루프를 사용하여 동적 SQL을 만들어서 만들 임시 변수의 개수를 확인하는 루프에 대해 작동합니다. 각 임시 변수에는 8,000바이트의 데이터가 들어가므로 가져오는 파일의 크기가 제한되지 않습니다. 문자 한 개와 일련 번호가 포함된 임시 변수 이름을 사용하여 구분하면 원하는 대로 됩니다.

--readobj 샘플, hello 개체 읽기 exec readobj 'c:\hello.sql'

8,000바이트 이상의 INSERT 또는 UPDATE 문
저장 프로시저 readobj는 텍스트 파일에서 규칙, 기본값, 암호화되지 않은 저장 프로시저, UDF, 트리거 또는 뷰 개체를 만들 수 있습니다. INSERT, UPDATE, DELETE 또는 8,000바이트가 넘는 그 밖의 문들도 사용할 수 있습니다. 문이 텍스트 파일에는 없고 큰 SQL 문자열에 있는 경우에는 다음과 같은 간단한 저장 프로시저로 해당 문자열을 실행할 수 있습니다.

CREATE PROCEDURE exec_ntext (@SQL ntext) --큰 SQL 문을 실행합니다. AS EXEC (@SQL)

사용자 정의 함수를 사용하여 text, ntext, image 열 비교
두 열을 가장 빠르고 쉽게 비교할 수 있는 방법은 크기를 비교하는 것이지만, 두 열이 내용은 다르지만 길이가 같을 수도 있기 때문에 이 방법에는 확실히 한계가 있습니다.

CREATE FUNCTION testlength (@a ntext, @b ntext) --두 입력 내용의 길이가 같으면 true를 반환합니다. RETURNS bit AS BEGIN declare @temp_bit bit if datalength(@a)=datalength(@b) set @temp_bit= 1 else set @temp_bit=0 return @temp_bit END

앞의 예제에서는 입력 내용이 ntext였지만 text나 image가 될 수도 있습니다.

두 열을 비교하는 가장 좋은 방법은 전체 내용이나 그 안의 일부 내용을 비교하는 것입니다.

CREATE FUNCTION testequality (@a ntext, @b ntext) --비교 결과가 true이면 1을 반환합니다. RETURNS bit AS BEGIN declare @temp_bit bit if @a like @b set @temp_bit= 1 else set @temp_bit=0 return @temp_bit END

와일드카드 문자를 사용하여 정의한 패턴 일치(둘째 입력 변수)를 통해 이 기술이 더욱 강력해집니다. SQL Server의 와일드카드 문자는 다음과 같습니다.

  • % 크기에 관계없는 문자열(빈 문자열도 허용)
  • _ 길이가 한 자리인 문자열
  • [ ] 문자 집합([fhsvf]) 또는 문자 범위([k-t]) 중에 있는 한 문자
  • [^] 문자 집합([^fhsvf]) 또는 문자 범위([^k-t]) 중에 포함되지 않은 한 문자

이미지의 형식 확인
이미지가 비트맵, JPEG, PNG, TIFF 중 어떤 형식인지 알아내는 간단한 방법은 이미지의 첫째 바이트를 검사하여 해당 형식의 헤더 명세에 맞는지 여부를 확인하는 것입니다. 원시 데이터의 경우 실패할 수 있기 때문에 이 방법이 절대 안전하지는 않지만 더욱 정교한 식별 기능의 첫 단계가 됩니다.

CREATE FUNCTION getImageType (@a image) --이미지 형식의 유형을 반환합니다. RETURNS varchar(4) AS BEGIN declare @out varchar(4), @temp varbinary(8) SET @temp=convert(varbinary(8), @a) SET @out=CASE WHEN LEFT(@temp,2)=0x424D THEN 'BMP' WHEN LEFT(@temp,2)=0xFFD8 THEN 'JPG' WHEN LEFT(@temp,8)=0x89504E470D0A1A0A THEN 'PNG' WHEN (LEFT(@temp,2)=0x4949)OR(LEFT(@temp,2)=0x4D4D) THEN 'TIFF' ELSE '' END return @out END

이러한 샘플과 그 밖의 샘플들을 파일 다운로드를 통해 사용할 수 있습니다. 여러 가지 샘플을 검토해 보고 응용 프로그램에 직접 활용해 보십시오.

NTEXTCODE.exe



Posted by tornado
|
출처 : swynk.com

내용 : Information Schema Views에 대한 종류와 그 내용. 샘플을 다룹니다.

Microsoft SQL Server ships with some information schema views that are very helpful for
getting information about the meta-data. And Microsoft suggests to use these views
instead of querying the system tables like systypes, sysreferences, sysindexes etc. since
they can change from release to release.
I personally prefer to have a good knowledge of the system tables and occasionally use
them for database administration purposes but for the application it's good if your SQL
queries are based off these views so you are sure that when you migrate from one
version to the other of SQL Server, you are not going to be breaking something.
All these information_schema views exist in SQL Server 2000 and detailed information on
all of them is available in BOL so I wont' go into details of what each view does. Here is
the list of the views:

Information_Schema.Check_Constraints
Information_Schema.Column_Domani_Usage
Information_Schema.Column_Privleges
Information_Schema.Columns
Information_Schema.Constraint_Column_Usage
Information_Schema.Constraint_Table_Usage
Information_Schema.Domain_Constraints
Information_Schema.Domains
Information_Schema.Key_Column_Usage
Information_Schema.Parameters
Information_Schema.Referential_Constraints
Information_Schema.Ruotine_Columns
Information_Schema.Routines
Information_Schema.Schemata
Information_Schema.Table_Constraints
Information_Schema.Table_Constraints
Information_Schema.Table_Privileges
Information_Schema.Tables
Information_Schema.Views
Information_Schema.View_Column_Usage
Information_Schema.View_Table_Usage
Some examples:
1)For getting a list of constraints, column_names and their position in the constraint:


SELECT COLS.CONSTRAINT_NAME,COLS.COLUMN_NAME,COLS.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS ON
COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
WHERE COLS.CONSTRAINT_CATALOG = DB_NAME()
AND COLS.TABLE_NAME = 'TASK_DTL'
AND CONS.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

You can replace the name of the table to whatever you want and can also replace the
constraint_type if you want to search for constraints other than the Primary Key.


2) SELECT CONSTRAINT_NAME,
COLUMN_NAME,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_CATALOG = DB_NAME()
AND TABLE_NAME = 'X'
ORDER BY CONSTRAINT_NAME, ORDINAL_POSITION

Subsitute x with the name of the table and you will get the name of the constraints defined
on the table (Primary Key as well as the Foreign key constraints), the names of the
columns involved and the position of the columns in the constraint, for example, which
column is number 1 in the case of a covered primary key.

3) For a list of tables, their columns, data-type for the columns, NULL/Not NULL criteria:


SELECT A.TABLE_NAME, B.COLUMN_NAME, B.DATA_TYPE, B.IS_NULLABLE
FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_NAME = B.TABLE_NAME
ORDER BY A.TABLE_NAME
Posted by tornado
|
Posted by tornado
|

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

  • 김정선
  • 삼성 SDS 멀티캠퍼스
  • 전임 강사
  • Microsoft SQL Server MVP



요약

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


목차

  1. 동적 쿼리
  2. 동적 쿼리 대 정적 쿼리(비 동적 쿼리)
  3. 탈출을 위한 시작, 사례별 예제
  4. 마치면서...

동적 쿼리

일반적으로 언급하는 동적 쿼리란, 코드의 실행 시점에 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


마치면서 … 단순한 사례들만을 늘어 놓았다. 최적 사례에 대한 소개가 생략된 것이다. 다음에 또 기회가 된다면, 그 때는 몇 가지 최적 사례에 대하여 논의해 볼 것이다.

예제는 예제일 뿐이다. 이 글을 읽는 분 들이 예제를 여과 없이 수용하고 개발에 적용한다면 분명 필자가 욕을 먹을 것이다. 위 예제를 예제로서 보지 말고 성능 상의 문제로까지 연결해서 분석해야 한다. 예제 중의 일부는 여전히 성능 문제를 해결하지 못하고 있으며, 여러분의 노력에 따라 더 나은 성능을 제공하는 쿼리로 변경하거나 재 개발할 수 있을 것이다. 이는 필자가 직접적으로 그 내용을 포함하지 않은 이유이기도 하다. 무엇이 문제인지를 모르고 해답만을 논할 수는 없지 않은가? 그리고 사실 이것이 더 재미있다! 그러니 오해가 없기를 바란다. 다시 한 번 당부하는 것은, 반드시 스스로 테스트하고 또 성능 문제로까지 확장해서 분석해 보아야 한다. 아마 짧지 않은 시간이 필요할 것이다!

객관식에 몇 가지 보기를 추가했을 뿐이며, 아직 많은 보기가 남아 있다. 또한, 이미 언급한대로 좋은 보기만을 제시한 것도 아니다. 다양한 사례를 통해 경험을 축적하고 이를 지식 데이터베이스화해야 한다. 최종적으로 여러분 스스로 보기를 만들어내고 가장 정답에 가까운 해답을 찾아낼 수 있는 능력을 길러야 한다.

동적 쿼리의 사용이 무조건 나쁘다는 것이 아니다. 비즈니스 모델의 특수성, 데이터베이스 모델의 변화, 단축된 개발 주기, 개발 생산성에 대한 이슈 등의 동적 쿼리를 필요로 하는 상황이 많이 발생한다. 문제는 무분별하게 과용되고 있다는 것이다. 동적 쿼리가 요구될 때, 기본적인 검토를 할 필요가 있다. 동적 쿼리를 사용하는 것이, 성능 대비 개발 생산성, 관리 용이성 등의 상대적 이득이 충분하다고 판단되어야 할 것이다. 지금 이 글을 읽고 난 뒤에, 여러분이 현재 개발하거나 운영 중인 데이터베이스의 얼마나 많은 동적 쿼리가 사용 중인지를 살펴 보자. 그리고 얼마나 무분별하게 과용되고 있는지도 평가해 보아야 할 것이다.




Posted by tornado
|
출처 : www.sql-server-performance.com

작성자 : Randy Dyess

내용 : SQL2000 Table Hints

음음.. 여러가지.. SQL서버 쿼리의 힌트 들에 대한 정보 입니다.

참고 하시길 바랍니다.




 
SQL Server 2000 Table Hints








 
by Randy Dyess

Visit his website at: www.TransactSQL.com

 

As you advance in your skills as a Transact-SQL developer, or SQL Server database administrator, there will come a time when you need to override SQL Server's locking scheme and force a particular range of locks on a table. Transact-SQL provides you with a set of table-level locking hints that you can use with SELECT, INSERT, UPDATE, and DELETE statements to tell SQL Server how you want it to lock the table by overriding any other system-wide or transactional isolation levels.

This article will describe the use of the table-level locking hints and general table hints that are available to Transact-SQL developers and SQL Server database administrators, and attempt to provide scenarios on when you should use a particular hint. You should note that SQL Server's query optimizer will automatically determine what it thinks is the best type of lock to use on an object in a query, and you should only override this choice only when necessary.

After saying that, it is sometimes necessary to correct the optimizer and use a hint to force the optimizer to use an index other than the one it picks, or to simply control the behavior of the locks. An example of choosing an alternative index would be when the query optimizer chooses an index that is constantly out of date and you do not have the cycles to bring the index up to date before you run the query. An example of controlling the locking behavior would be to lock a table to optimize a BULK INSERT.


Table Hints May Not Always Be Used By the Query Optimizer
You should note that even though you specify a table-level hint in your code, the query optimizer may ignore the hint. Table-level hints will be ignored if the table is not chosen by the query optimizer and used in the subsequent query plan.

In addition, the query optimizer will often choose an indexed view over a table. In case your table-level hint will be ignored, you can override the query optimizer's preference for indexed views by using the OPTION (EXPAND VIEWS) query hint.

Another reason the query analyzer may ignore your hint is due to the fact that the table may contain computed columns and the computed columns are computed by expressions and functions referencing columns in other tables, and the table hints are not specified for those tables. Table hints are not propagated on tables with computed columns, so the hint will not be used on tables referenced by computed columns, table-level hints are propagated on base tables and views referenced by another view though.

SQL Server also does not allow more than one table hint from either the Granularity hint group (PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, TABLOCKX), or the Isolation Level hint group (HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE) to be used in the FROM clause for each table. This basically means that you cannot specify ROWLOCK and TABLOCK for a table in the same FROM clause. SQL Server will also not allow the NOLOCK, READUNCOMMITTED, or READPAST hints to be used against tables that are the targets of a DELETE, INSERT or UPDATE statement.


Table Hint Syntax
Now that we have mentioned most of the hints, let's look at the syntax for table-level hints used with the FROM clause.

SYNTAX[ FROM { < table_source > } [ ,...n ] ] < table_source > ::= table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ] < table_hint > ::= { INDEX ( index_val [ ,...n ] ) | FASTFIRSTROW | HOLDLOCK | NOLOCK | PAGLOCK | READCOMMITTED | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK } Microsoft encourages the use of the WITH keyword, even though it is optional, as they state that a future release of SQL Server may require this keyword. Microsoft also encourages the use of the optional commas between different hints, as spaces are only allowed for backward compatibility reasons.

You can see that there are fifteen hints defined in this syntax listing. Thirteen of these hints (HOLDLOCK, NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, TABLOCK, TABLOCKX, UPDLOCK, XLOCK) are considered table-level locking hints while (INDEX and FASTFIRSTROW) are considered table hints. This doesn't mean much to most developers, but I thought I would tell you how Microsoft groups them.


Table Hint Descriptions
Now that you know the names of the hints, how they are grouped, and the syntax of each, lets go over what each hint does.


INDEX is used to specify the name or object ID of an index or indexes that will be used by SQL Server when processing the statement. SQL Server will chose an index by default if one is not specified by the use of the INDEX keyword, but sometimes you will need to force SQL Server to use a particular index.

Only one index hint can be used per table, but you can specify more than one index in this hint. If a clustered index exists on the specified table, using INDEX(0) will force a clustered index scan and INDEX(1) will force a clustered index scan or seek. If no clustered index exists on the table, then INDEX(0) will force a table scan while INDEX(1) will be interpreted as an error.

If you chose multiple indexes to be used for the hint, any duplicates will be ignored. Be careful in the order you place indexes when you use multiple indexes with the index hint. SQL Server try to apply as many conditions as possible on each index, so if you place the broader indexes first, you may have all your conditions covered and SQL Server will not have to create AND statements for all the specified indexes.

You can use a maximum of 250 non-clustered indexes in an index hint. Be aware that if an index hint with multiple specified indexes is used on a fact table in a star join, then SQL Server will ignore will return a warning message and ignore the hint.


FASTFIRSTROW will optimize the query to retrieve the first row of the result set.


HOLDLOCK (equivalent to SERIALIZABLE) applies only to the table specified and only for the duration of the transaction, and will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required. HOLDLOCK cannot be used in a SELECT statement with the FOR BROWSE option specified.


NOLOCK (equivalent to READUNCOMMITTED) permits dirty reads. Dirty reads will not issue shared locks and will ignore exclusive locks placed by other processes. It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back.


PAGLOCK will force the use of a page lock instead of a table lock.


READCOMMITTED specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, which will result in nonrepeatable reads and may cause phantom data. READCOMMITTED is the default table hint in SQL Server.


READPAST specifies that locked rows be skipped during the read. READPAST only applies to transactions operating at the default READ COMMITTED isolation level, and will only read past row-level locks. READPAST can only be used in SELECT statements. Normal blocking can be worked around by having transactions read past rows being locked by other transactions.


READUNCOMMITTED (equivalent to NOLOCK)  permits dirty reads. Dirty reads will not issue shared locks and will ignore exclusive locks placed by other processes. It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back.


REPEATABLEREAD specifies that locks be placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction.


ROWLOCK forces the use of row-level locks instead of page or table level locks.


SERIALIZABLE (equivalent to HOLDLOCK) applies only to the table specified and only for the duration of the transaction, and it will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required.


TABLOCK specifies that a table lock to be used instead of a page or row level lock. This lock will be held until the end of the statement.


TABLOCKX specifies that an exclusive lock be held on the table until the end of the statement or transaction, and will prevent others from reading or updating the table.


UPDLOCK specifies that update locks will be used instead of shared locks, and will hold the locks until the end of the statement or transaction.


XLOCK specifies that an exclusive lock be used and held until the end of the end of the transaction on all data being processed by the statement. The granularity of XLOCK will be adjusted if it is used with the PAGLOCK or TABLOCK hints.


Table Hint Usage
Now that you know a little about each of the table-level hints, you may be wondering when you may need to use them. Where I work, we have a very large database that is used by approximately 10,000 customer service reps in a call center environment. While the reps are using the database, we have to load approximately 400,000 new rows of data into the database every three days. This load process can take up to 16 hours, so we are often forced to run the load during operational hours.

To optimize our BULK INSERT load process, we have added the TABLOCK hint to lock tables and speed inserts, and the READUNCOMMITTED hint to allow dirty reads of the data. All transactions generated by the reps are placed into smaller transactional databases so dirty reads are not a problem.

Our very large database and large number of monthly inserts caused our table statistics and indexes to be out of date. We just didn't have enough operating cycles to keep the all of the statistics and indexes completely updated after every data load.

This problem sometimes caused the query optimizer to create incorrect query plans because it did not always have the most up-to-date information, resulting in poor performance. This forced me to use index hints to force a query to use an index or indexes that I knew were keep updated to solve the slow response times.

Another use of the index hint is to force the use of an index when the query optimizer insists on using a table scan. For example, the SQL Server 7.0 query optimizer seems to have a preference for table scans even though very few rows of the table will be returned by the query.

I don't tend to use the many other hints at work, but in the past I have found use for the READPAST hint in work queues to allow a row of data to be returned to a client without waiting for locks to be releases by other processes. This is nice to use if you do not want end users to obtain the same row of data.

Others have told me that they often use the FASTFIRSTROW hint when they want to return the first row to the user quickly, to give them something to do, while the rest of the query catches up. I haven't found too many uses for TABLOCKX, UPDLOCK, XLOCK, or SERIALIZE (HOLDLOCK) in the type of databases I'm accustomed to working with, but I have been told that they are great hints for financial and reporting situations when you need the data to be consistent throughout a transaction.

Different hints are needed for different types of databases or transactions, and you will eventually determine which ones are proper for your situation.


Summary
As you can see, table-level hints are available for use by Transact-SQL developers or SQL Server database administrators, but should only be used to fine-tune your code, not as a standard technique for writing queries. You should perform a strict review of the query plans procedure by the query optimizer before you decide that a table-level hint may be want you need to solve your problem. In addition, you should perform this strict review after the hint is in place.

While table-level hints are not for all levels of users, experienced administrators and developers can use them to solve a limited set of problems, as well as fine-tune a limited set of queries in which the query optimizer has failed in its job to optimize correctly.


Knowledge Based Articles
Q235880 INF: Optimizer Hint NOLOCK or Isolation Level READ UNCOMMITTED Generates Error 605
Q297466 BUG: READPAST Locking Hint Returns an Incorrect Number of Rows
Q308760 FIX: SQL Server Optimizer Ignores Index Hint for UPDATE If One or More Non-Clustered Indexes Exist
Q308886 PRB: NOLOCK Optimizer Hint May Cause Transient Corruption Errors in the SQL Server Error Log
Q310935 FIX: Use of a Dynamic API Server Cursor with a NOLOCK Hint Causes Error 1047
Q320434 FIX: Bulk Insert with TABLOCK Hint May Result in Errors 8929 and 8965 When You Run CHECKDB
Q247365 BUG: Dynamic Cursor With NOLOCK Hint, DELETE Activity, Causes Assertions in Error Log

Posted by tornado
|

sp_changeobjectowner '예전소유자.프로시저명','dbo'


도움말에 나와있음 -.-;

Posted by tornado
|