1 SQL Server 2005의 Hash 함수의 종류

2 해시인덱스

3 행의 변경사항 검색

4 패스워드 암호화(?)

5 참고 사이트

MSSQL Server 2005에는 여러 Hash함수가 존재한다. 제공되는 Hash함수는 다음과 같다.

함수	설명
CHECKSUM	해시인덱스 작성시 사용
CHECKSUM_AGG	그룹에 해시값이 필요할 경우
BINARY_CHECKSUM	ROW의 변경사항 검색에 사용
HASHBYTES	패스워드등의 해시값 생성

2000 vs 2005 (binary_checksum()의 결과가 버전마다 틀리니 주의해야 한다)

--2000
SELECT BINARY_CHECKSUM('aa','AA','Arjan') 
----------- 
4225134 

SELECT BINARY_CHECKSUM('BQ','AA','Arjan') 
----------- 
4225134 

--2005
SELECT BINARY_CHECKSUM('aa','AA','Arjan') 
----------- 
4225134 

SELECT BINARY_CHECKSUM('BQ','AA','Arjan') 
----------- 
4356206 

설명대로 CheckSum함수는 해시 인덱스를 작성하는데 사용됩니다. 예를 들어 다음과 같은 테이블이 있다고 가정한다.

use tempdb
go

create table item
(
	item_seq int
,	item_code nvarchar(400)
,	item_name nvarchar(2000)
);

create index nix_item_code
on item(item_code);

Item_code가 nvarchar(400)다. 즉, 가질 수 있는 최대 길이가 800 Byte라는 것이다. 만약 데이터의 양이 많다면 인덱스의 크기가 만만치 않을 것이다. 또한 문자열의 길이가 길면(900 Byte 초과) 인덱스를 생성할 수 없다. 이때!! 해시인덱스를 이용하면 된다.

use tempdb
go

create table item2
(
	item_seq int
,	item_code nvarchar(400)
,	item_name nvarchar(2000)
,	item_code_hash int
);

create index nix_item_code
on item2(item_code_hash);

insert item2 
values(1, 'A0001-1111', 'GCS-BSDEF-ASDFF, 어쩌구상품', checksum('A0001-1111'))

select * from item2
where item_code_hash = checksum('A0001-1111')
and item_code = 'A0001-1111' --checksum함수의 결과가 유일하다는 보장이 없기 때문에 꼭 이렇게 해줘야 한다.

위의 예제에서 보면 item_code_hash라는 컬럼이 생긴 것을 볼 수 있다. checksum함수를 이용하여 checksum(item_code)의 결과를 int형으로 받는다. 그리고 검색할 때도 checksum함수를 이용하면 된다. 인덱스의 유지보수 및 크기가 상당히 줄지 않을까? 성능도 물론 향상 되겠다. 하지만 해시인덱스는 Point Query에만 유리하다는 것을 잊으면 안 된다. 만약 회원가입시 싸이월드처럼 긴 문자열(e메일)로 ID를 사용한다면 해시 인덱스를 사용해 봄직도 할 것이다.

Binary_checksum함수를 이용하면 행의 변경사항을 검색할 수 있다. 테이블을 하나 생성하여 변경사항을 검색해 보도록 하겠다.

use tempdb
go

create table test
(
	id varchar(20)
,	pw varchar(20)
,	last_connect_dt datetime
,	check_sum int
);

insert test 
select
	id
,	pw
,	last_connect_dt
,	binary_checksum(*) check_sum
from (
	select 
		'yasicom' id
	,	'1234' pw
	,	getdate() last_connect_dt
) t

--갱신
update test
set last_connect_dt = getdate()
where id = 'yasicom'


select
	id
,	pw
,	last_connect_dt
,	check_sum		old_hash --비교대상
,	binary_checksum(*)	new_hash --비교대상
from test
where id = 'yasicom'

DW등에서 변경에 대한 관리를 하면 좋을 것이다.

2000버전에서는 md5와 알고리즘을 구현한 dll파일을 이용하여 확장프로시저를 생성하여 패스워드나 주민번호와 같이 노출되어서는 안 되는 데이터를 해쉬하여 디코딩이 가능하지 않은 형태로 저장했었다. 필자는 무진장 맘에 안들었다. 근데 2005버전에서 MD5, SHA와 같은 해쉬 알고리즘을 제공한다. 문법은 다음과 같다.

HashBytes ( '<algorithm>', { @input | 'input' } )
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1

제공하는 알고리즘이 5가지나 된다.

select
	hashbytes('md2', 'yasicom')
,	hashbytes('md4', 'yasicom')
,	hashbytes('md5', 'yasicom')
,	hashbytes('sha', 'yasicom')
,	hashbytes('sha1', 'yasicom')

그런데 왜 결과가 sha와 sha1이 같은 것인지 모르겠다. 패스워드나 주민번호를 해시할 때 생년월일, ID등과 합쳐서 한 번더 꼬아주는 것이 어떨런지?

CASE
	WHEN BINARY_CHECKSUM(@CustID)%5 = 0 THEN HashBytes('MD2', @CustID + @BirthDT + @PW)
	WHEN BINARY_CHECKSUM(@CustID)%5 = 1 THEN HashBytes('MD4', @BirthDT + @CustID + @PW)
	WHEN BINARY_CHECKSUM(@CustID)%5 = 2 THEN HashBytes('MD5', @PW + @CustID + @BirthDT)
	WHEN BINARY_CHECKSUM(@CustID)%5 = 3 THEN HashBytes('SHA', @CustID + @PW + @BirthDT)
	WHEN BINARY_CHECKSUM(@CustID)%5 = 4 THEN HashBytes('SHA1',@PW + @BirthDT + @CustID)
	ELSE HashBytes('MD5', @CustID + @BirthDT + @PW)
END;

• http://tools.web-max.ca/encode_decode.php