Entire Row to be Unique in the whole Table...
-
Hi Friends,
I am having a need to implement a Business requirement where in each row in the whole table must be unique.Would be grateful if I can get any help from you in achieving this...Thank you in advance!
Best Regards,
Arshad
- This topic was modified 3 years, 2 months ago by Arsh.
-
Create a unique index on all the columns and make that unique?
- pietlinden wrote:
Create a unique index on all the columns and make that unique?
For 2014 and earlier, the limit for key columns in an index is 16 unless you have at least one XML column and then it's 15 (IIRC).
For 2016 and later, the limits are 32 and 31 as above.
I wonder if the op really means that every attribute (column) must be totally unique for the entire table or if he just needs a row to be unique. Huge difference there.
If the former and you have more columns than the limits that I previously identified above, I suppose you could at a unqiue NCI to every column but that, of course, would more than double the total size that the table requires.
I'm thinking that we need a more clear definition or at least a confirmation of the OP actually needs as well as the total column count and the datatypes for all columns because lobs aren't going to take to a unique index. You also have to worry about the 900/1700 byte limit depending on which version of SQL they have.
It would really be interesting to know what the business requirement is to require such a thing other than "that's what they want".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As already stated, a unique index or a unique constraint are your options
ref: T-SQL Create index
With a brain fart, you may even consider a checksum column, but keep in mind checksum may create duplicate values !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
depending on number of columns on that table you may need (and it may be advisable) to resort to have a hash column added and make this column the single column of a unique index.
column defined as binary(20) and populated with
hashbytes('SHA1', concat_ws('|',col1,col2,...,coln))
reason for a separator is to avoid collisions caused for similar strings on continuous columns
although still possible to have collisions they will be sooo rare that its not worth considering it for this particular case.
-
If it can't be done with unique constraint/index. Maybe a trigger?
- frederico_fonseca wrote:
depending on number of columns on that table you may need (and it may be advisable) to resort to have a hash column added and make this column the single column of a unique index.
column defined as binary(20) and populated with
hashbytes('SHA1', concat_ws('|',col1,col2,...,coln))
reason for a separator is to avoid collisions caused for similar strings on continuous columns
although still possible to have collisions they will be sooo rare that its not worth considering it for this particular case.
Beginning with SQL Server 2016 (13.x), all algorithms other than SHA2_256, and SHA2_512 are deprecated.
https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15
- Jonathan AC Roberts wrote:frederico_fonseca wrote:
depending on number of columns on that table you may need (and it may be advisable) to resort to have a hash column added and make this column the single column of a unique index.
column defined as binary(20) and populated with
hashbytes('SHA1', concat_ws('|',col1,col2,...,coln))
reason for a separator is to avoid collisions caused for similar strings on continuous columns
although still possible to have collisions they will be sooo rare that its not worth considering it for this particular case.
Beginning with SQL Server 2016 (13.x), all algorithms other than SHA2_256, and SHA2_512 are deprecated.
https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15
True enough. However, the MD2, MD4, and MD5 algorithms have been deprecated for more than a decade. I agree that the deprecated versions should never be used for the one-way encryption that HASHBYTES was designed for but SHA1 (20 bytes) still makes a pretty dandy comparison tool that's head and shoulders above using any form of regular CHECKSUM for comparisons.
SHA 1 (20 bytes) is a fair bit faster than the 256 or 512 byte versions and, since even MD2 is still available after all this time, I'm thinking that SHA1 is going to be around for another couple of decades just like the deprecated-in-2005 Text/NText/Image datatypes have been.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jonathan AC Roberts wrote:frederico_fonseca wrote:
depending on number of columns on that table you may need (and it may be advisable) to resort to have a hash column added and make this column the single column of a unique index.
column defined as binary(20) and populated with
hashbytes('SHA1', concat_ws('|',col1,col2,...,coln))
reason for a separator is to avoid collisions caused for similar strings on continuous columns
although still possible to have collisions they will be sooo rare that its not worth considering it for this particular case.
Beginning with SQL Server 2016 (13.x), all algorithms other than SHA2_256, and SHA2_512 are deprecated.
https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15
yes - and if this was used for crypto related processes I would not recommend it - for identifying uniqueness of data SHA1 is more than enough, faster than SHA256 and requires only 20 bytes storage vs 32 or 64 of newer algorithms.
- frederico_fonseca wrote:Jonathan AC Roberts wrote:frederico_fonseca wrote:
depending on number of columns on that table you may need (and it may be advisable) to resort to have a hash column added and make this column the single column of a unique index.
column defined as binary(20) and populated with
hashbytes('SHA1', concat_ws('|',col1,col2,...,coln))
reason for a separator is to avoid collisions caused for similar strings on continuous columns
although still possible to have collisions they will be sooo rare that its not worth considering it for this particular case.
Beginning with SQL Server 2016 (13.x), all algorithms other than SHA2_256, and SHA2_512 are deprecated.
https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15
yes - and if this was used for crypto related processes I would not recommend it - for identifying uniqueness of data SHA1 is more than enough, faster than SHA256 and requires only 20 bytes storage vs 32 or 64 of newer algorithms.
Yes, I'm not saying a collision would ever happen.
It's just I am just not keen on implementing deprecated functions in new code.
-
Understood.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Friends,
Thank you to all of you for sharing your ideas...The ones the client IT would consider is the usage of Hash Algorithm by "Frederic"..Special thanks to him too...Had already suggested the unique index option but not favorable especially because the table has lots of columns...
As for the requirement,I dont have info beyond this , as I didn't start on the assignments with them yet....This was asked as an informal question..
🙂
Best Regards....Arshad
-
Thanks Fred...This option will be required to look into.
Best Regards....Arshad
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply