snippetsqlMinor
Programmatically finding minimum set of fields necessary to create a unique composite key
Viewed 0 times
uniquenecessaryprogrammaticallycreatefieldsminimumcompositefindingsetkey
Problem
I am importing flat files from different sources into tables in SQL Server. I am creating a composite primary key using a combination of fields from the extracts that will give me a unique key for every row.
The way I do it now is I just start with 1 field and then I keep concatenating fields until I find a key that is unique for all records. This can be a little time consuming or I might end up concatenating more columns than I really needed to in order to obtain the unique key.
Is there some sort of SQL script I can run on a table that will give me the minimum number of fields (names) I would need to concatenate in order to obtain a unique key? So if there is 1 field in the table that is unique for all records then that 1 field name would be returned. If I needed to concatenate [memberid], [claimid], and [date of service] in order to obtain a unique key then those 3 field names would be the result of the script.
The way I do it now is I just start with 1 field and then I keep concatenating fields until I find a key that is unique for all records. This can be a little time consuming or I might end up concatenating more columns than I really needed to in order to obtain the unique key.
Is there some sort of SQL script I can run on a table that will give me the minimum number of fields (names) I would need to concatenate in order to obtain a unique key? So if there is 1 field in the table that is unique for all records then that 1 field name would be returned. If I needed to concatenate [memberid], [claimid], and [date of service] in order to obtain a unique key then those 3 field names would be the result of the script.
Solution
While comments and srutzky offer some great advice, there is a tool made exactly for your situation. The SSIS
Simply create a new SSIS package, add the task then use the wizard like interface to profile your data. Create a new output file somewhere you can access it, select
Once you're done run the package and return to the component to select
Just to be clear I definitely agree that the business rules should determine data uniqueness... just because you find a combination of columns that fits over your data for uniqueness doesn't necessarily mean it makes any sense. =)
Data Profiling Task is intended for the purpose of identifying potential Primary Keys (for multiple columns) along with providing a lot of other useful insights into your data.Simply create a new SSIS package, add the task then use the wizard like interface to profile your data. Create a new output file somewhere you can access it, select
Quick Profile... then profile the appropriate information from the database and table desired.Once you're done run the package and return to the component to select
Open Profile Viwer... and look at all the exciting data! The tool gave me a 96% match for one of my fact tables for a three column PK when up to 7 columns were requested for the candidate key consideration (not shown).Just to be clear I definitely agree that the business rules should determine data uniqueness... just because you find a combination of columns that fits over your data for uniqueness doesn't necessarily mean it makes any sense. =)
Context
StackExchange Database Administrators Q#121238, answer score: 5
Revisions (0)
No revisions yet.