Contents

1 SQL Server 2005 Hash 譬襯
2 伎碁煙
3 覲蟆曙 蟆
4 れ 誤(?)
5 谿瑚 危


[edit]

1 SQL Server 2005 Hash 譬襯 #

MSSQL Server 2005 Hash螳 譟伎. 螻給 Hash れ螻 螳.

る
CHECKSUM伎碁煙 煙
CHECKSUM_AGG蠏碁９ 伎螳 蟆曙
BINARY_CHECKSUMROW 覲蟆曙 蟆
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
[edit]

2 伎碁煙 #

る襦 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襯 る 伎 碁煙るゼ 覺讌 蟆企.

[edit]

3 覲蟆曙 蟆 #

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'
hash02.jpg

DW煙 覲蟆曙 蟯襴襯 覃 譬 蟆企.

[edit]

4 れ 誤(?) #

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;

[edit]

5 谿瑚 危 #