Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
t1g312
Posting Yak Master
148 Posts |
 Posted - 2004-07-28 : 02:50:58
|
| Hi all,I have a table, masPrincipal, that contains a unique ID and Company's name among other things. The ID has been defined as the primary key. I have also created an index for the field Company's name. Is there any overhead in making it a unique index/constraint?Thanks!Adi-------------------------/me sux @sql server |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-28 : 03:47:07
|
The Company Name column already has an index and you want to change it to a Unique Index?No, I don't see any additional overhead in doing that.EDIT: Worth checking first that the current values are already uniqueSELECT [Company Name], COUNT(*)FROM MyTableGROUP BY [Company Name]HAVING COUNT(*) > 1 Kristen |
 |
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2004-07-28 : 04:17:02
|
| Thanks Kristen! The reason why I asked that was because the field is varchar of size 50 and everytime a new company is inserted (although that won't happen very often) Sql Server has to check to see if the name is unique.Adi-------------------------/me sux @sql server |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-28 : 06:56:44
|
| As it will be indexed that isn't going to take SQL very long at all, so I wouldn't worry about it. But its worth being aware of the fact that any column that is indexed requires SQL to insert new entry, or modify an existing one, as part of saving a record.Kristen |
|
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-07-28 : 07:43:58
|
| You need to be aware that as this is a unique index, there cannot be any duplicates - and there are duplicated company names (Apple for example). Of course this may not affect you, now or ever.Just my 2p worthGraham |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-28 : 08:05:23
|
| You need to figure out what the natural unique key is in your instance, which would serve as the "natural primary key". For example, sometimes the company name might not be unique, but the company name and address would be. If you limit yourself to having the company name as unique, then you end up "creating" a pseudo-name for a company instead which is confusing. Just a thought. You should always consider this kind of thing in data modeling though. It needs to be normalized. At the same time, it needs to reflect the true needs of the business.Just my 2p worth. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-28 : 10:01:05
|
| If the Company Name is "nearly unique" I think I would put something in the application that pops up an "Are you sure this isn't the same record as 12345" when someone tries to create a new record with the same Company name as another. (Then just put a regular index on the column for faster query speed)Kristen |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2004-07-28 : 15:07:13
|
| But how do I find out if a name is nearly unique? Using the Google API? :PAdi-------------------------/me sux @sql server |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-28 : 16:08:21
|
| If you have no duplicates in your data already, and are confident there won't be any, then go with the UNIQUE INDEX.Otherwise make a non-unique index, and in your application whenever the user adds a new record first check if that company name already exists.Kristen |
|
|
|
|
|
|
|
|
Newbie question - Unique Index