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에서는 각각의 장점과 제한 사항을 요약합니다.

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