February 27, 2008 at 10:59 am
can i know what is checksum what does it do i tried in msdn but i couldnt understand the exact us of that i tried some examples but i didnt get that
select checksum(listprice)from dbo.nydirect
8447545
8447545
8447545
8447545
where as the listprice is 48.75 ...whats the check sum then ???? n even checksum_agg and otheres can u give any example or link whcih is clear ......
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 29, 2008 at 9:02 pm
From BOL:
CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator.
If you send the same value to checksum it returns the same value. So anytime you run CheckSum(48.75) you will get the same value back for each row.
BOL basically says you can use the checksum to create a column to index on instead of indexing on a large varchar or char column. This will make a smaller index (int) and allow for faster joining and searching since you can join or search on the checksum column.
Thus if I have a varchar(500) column I can create an integer column into which I put a checksum of the varchar(500) column and create an index on that. I can also use the checksum integer column in my where clauses to check for equality.
Create Table cs_test
(
long_text varchar(100),
cs_long_text Int
)
Create Clustered Index IX_cs_test on cs_test(cs_long_text)
Go
Declare @i int
Set @i = 1
While @i <=100
Begin
INsert into cs_test
Select
'This is a long text column. The checksum field works great! ' + Convert(varchar(3), @i),
CheckSum('This is a long text column. The checksum field works great! ' + Convert(varchar(3), @i))
Set @i = @i + 1
End
Go
Select * from cs_test where cs_long_text = checksum('This is a long text column. The checksum field works great! 100')
This is could be more efficient than searching on the varchar column.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 1, 2008 at 7:53 am
You can use it in this way, but you still need to do an additional check to confirm the results meet your criteria - two distinct values can give the same checksum.
March 1, 2008 at 8:14 am
When two distinct values gives the same checksum value then how can we index on that column ....it says that u can use checksum as an additonal column so that u can index on that instead of indexing on long column ,...but if it has ssame checksum for diff values then what would be the use anyway....anywy thkz for the information till now ....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 3, 2008 at 7:12 am
Include the checksum column and the character column in the where clause. The checksum index would be used to pull out all the potential matches, and these would then be filtered against the exact character data. You could have the character data as an included column on the checksum index, but I doubt there would be much benefit to doing so.
March 3, 2008 at 7:35 am
thkz matt got some idea it was helpful thkz
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply