MSSQL Server 2005에는 여러 Hash함수가 존재합니다. 제공되는 Hash함수는 다음과 같습니다.
함수 | 설명 |
CHECKSUM | 해시인덱스 작성시 사용 |
CHECKSUM_AGG | 그룹에 해시값이 필요할 경우 |
BINARY_CHECKSUM | ROW의 변경사항 검색에 사용 |
HASHBYTES | 패스워드등의 해시값 생성 |
더 있나요? 모르겠습니다. 어찌되었건 이 정도로 제공하고 있습니다. 이 중에서 어떻게 써먹으면 좋은지에 대한 내용을 담아보도록 하겠습니다.
해시인덱스
설명대로 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라는 것입니다. 만약 데이터의 양이 많다면 인덱스의 크기가 만만치 않을 것입니다. 이때!! 해시인덱스를 이용하면 됩니다.
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') |
위의 예제에서 보면 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; |
머..이따위로 쓰면 됩니다..
