Let's calculate a hash for the Person table with the following SQL commands:. You'll definitely want to use a separator, otherwise you might get "collisions": different rows who result in the same hash. For example, if you don't use a separator, the following the rows will return the same hash:.
Such collisions can easily be avoided by adding a separator. If you have a lot of nullable columns, a collision can often happen, especially if you use a lot of flag columns columns with the bit data type as in this example:. Since the concatenation results in '10' each time, all the rows have the same hash. A dummy value is not always a good idea. For example, suppose you have an integer column that can contain any values an integer can hold.
Meaning, 0 and -1 are legit values for this column. What is your dummy value going to be? If you choose 0, an update that changes a NULL value to 0 will go unnoticed. Therefore, using an explicit separator is the best option. Related Articles.
Delete duplicate rows with no primary key on a SQL Server table. Rolling up multiple rows into a single row and column for SQL Server data. Popular Articles. How to tell what SQL Server versions you are running. Resolving could not open a connection to SQL Server errors. Ways to compare and find differences for SQL Server tables and data. Jeremy Thille Jeremy Thille Eric Eric 9 9 silver badges 25 25 bronze badges.
Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown.
The Overflow Blog. Stack Gives Back Safety in numbers: crowdsourcing data on nefarious IP addresses. Featured on Meta.
New post summary designs on greatest hits now, everywhere else eventually. Linked 1. Related Hot Network Questions. Question feed. Asked 3 years, 4 months ago. Active 3 years, 4 months ago. Viewed 9k times. Improve this question. Add a comment. Active Oldest Votes. Improve this answer. EzLo EzLo 3, 2 2 gold badges 9 9 silver badges 23 23 bronze badges.
Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password.
0コメント