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 열의 데이터를 변경합니다.
--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
--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에서 수행됩니다.
저장 프로시저 saveobj는 규칙, 기본값, 암호화되지 않은 저장 프로시저, UDF, 트리거 또는 뷰를 텍스트 파일에 저장하여 백업 및 문서화에 아주 유용하도록 할 수 있습니다.
--saveobj 샘플, hello 개체 저장
exec saveobj 'c:\hello.sql', 'hello'
파일에서 개체 텍스트 읽기 8,000바이트가 넘는 파일을 읽으려면 많은 버퍼가 파일 크기에 따라 채워진 다음 나중에 실행되므로 동적 SQL을 사용해야 합니다. 저장 프로시저 readobj는 루프를 사용하여 동적 SQL을 만들어서 만들 임시 변수의 개수를 확인하는 루프에 대해 작동합니다. 각 임시 변수에는 8,000바이트의 데이터가 들어가므로 가져오는 파일의 크기가 제한되지 않습니다. 문자 한 개와 일련 번호가 포함된 임시 변수 이름을 사용하여 구분하면 원하는 대로 됩니다.
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
이러한 샘플과 그 밖의 샘플들을 파일 다운로드를 통해 사용할 수 있습니다. 여러 가지 샘플을 검토해 보고 응용 프로그램에 직접 활용해 보십시오.
내용 : 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
일반적으로언급하는동적쿼리란, 코드의실행시점에 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 개발자들이이를수용, 프로젝트에적용하기시작했다 - 사실, 저장프로시저의적극적인사용을권장한것은꽤오래된이야기이지만국내에서활발하게적용되기시작한것은얼마되지않는다.
이러한방법론을통해얻을수있는이득이많은반면, 피하기어려운문제점또한가지고있었다. 그중에하나가바로서버측에서의동적쿼리의필요성이었다. 런타임시쿼리를문자열상수와변수로구성하고, 이를통해완성된최종쿼리를호출하던이전의방식에서는런타임시조건에따라다양한쿼리를구성할수있는편리함이제공된반면저장프로시저, 트리거등에서런타임시쿼리를동적으로변경하는것은상대적으로쉬운문제가아니었다. 가장
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.
