Solved

Allow duplicates in a table

Posted on 2013-01-03
31
275 Views
Last Modified: 2013-01-07
I have the following structure of a table.

PartID
DivID
Name
Address
City
State
Postal
Position
TNotes

currently the key is DivId, I also have PartID to set to autocrement by 1.

Id like to allow duplicate DIVID in this table. What is best way to handle it while maintaining the DivID as key.

should I remove the key in DivID and no key to this table?
0
Comment
Question by:zachvaldez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
  • 6
  • +1
31 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38740420
>currently the key is DivId
>Id like to allow duplicate DIVID
The above two statements are mutually exclusive.  If DIVID can contain duplicate values, then it can not be a key.   You can index it with duplicates allowed to improve performance, but no key.

>should I remove the key in DivID and no key to this table?
Yes, or at least just remove the key in DivID.
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 38740423
If PartID is a unique auto-increment then it should be the primary key.
You can place an index on DivID and anything else to speed up lookups.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38740424
>I also have PartID to set to autocrement by 1.
If you mean this is an AutoNumber field, then this would be a candidate for a key.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:zachvaldez
ID: 38740444
How about making those 2 fields as keys or another field paired with DivID. Is that possible?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38740473
Yes, but...
(1)  If PartID is guaranteed to be unique, then it could be a key by itself.
(2)  DivID + any other field(s) would have to guarantee uniqueness in order to be a composite key.
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 38740482
A table can have only one primary key, which is unique.
A table can have many foreign keys, which do not have to be unique since they reference a unique record in some other table.  Foreign keys have an index linked to them to speed up references.
Do you have another table that has DivID as a primary key?  If not just make DivID an index.
0
 

Author Comment

by:zachvaldez
ID: 38740537
I have several tables with DIVID as primary key. In fact as previously suggested, I removed DIVID as primary key in the tables taht would allow duplicate DIVIDs. I hope that would be ther best approach and made the autoincrement field as the key instead.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38740580
When you say "key", I guess you mean "primary key" and "clustering key"?!

Keep in mind that your primary key and your clustering key do NOT have to be the same.

If the existing table performs well with the DivID as the clustering key, that alone can still be your clustered key if you prefer (SQL allows you to specify a duplicate clustering key, and it "uniquifies" each row's key internally itself).

But primary keys must be unique.  So change the pk, likely to (DivID, PartID).

The clustering key could be the same, or just DivID alone, depending on what's best for that table and its values.
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 38740601
If you will be looking up records based on DivID then you should create an index containing just DivID.

So if you are doing this:
SELECT * from MyTable WHERE DivID=12345

Then you should have:
CREATE INDEX IX_MyTable_DivID ON MyTable(DivID)

In the Management Studio when you are designing your table, the toolbar button you want has the gold key and the window, usually two buttons to the right of the gold key button.  This form allows you to manipulate all the keys and indexes on your table.
0
 

Author Comment

by:zachvaldez
ID: 38740602
I made the Pk of the table as PartID.

>>>But primary keys must be unique.  So change the pk, likely to (DivID, PartID).
Does this suggest I make 2 pks for the table?
0
 

Author Comment

by:zachvaldez
ID: 38740741
The column DivID has a property indicating its indexable  set to 'yes' . Is that suffice as a property setting or shouls I still do the suggestions...

>>>>CREATE INDEX IX_MyTable_DivID ON MyTable(DivID)

In the Management Studio when you are designing your table, the toolbar button you want has the gold key and the window, usually two buttons to the right of the gold key button.  This form allows you to manipulate all the keys and indexes on your table. >>>>>
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 38740804
You will notice that that property cannot be changed.  It is for your information, it tells you if the column can be part of an index, based on its type.
It does not tell you if the column is in one or more indexes.
You do need to hit the button Manage Indexes and Keys Picture of Manage Indexes and Keys button or from the Table Designer menu choose Indexes/Keys... and then Add a new index.
In the (General) section select the Columns and hit the ... button to pick the columns you want on it, just DivID.
Down in the Identity section change the name to IX_MyTable_DivID
Dont need to change anything else just hit close then save the table.
0
 

Author Comment

by:zachvaldez
ID: 38741758
Ok, for indexes I'll set the DivID but for primary key , PartID will still be the primary key.
0
 

Author Comment

by:zachvaldez
ID: 38741801
When I click the manage Indexes and keys button, I see there the PK_partId.
should I add another entry there, which would make 2 keys?
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 38741821
Add another one which will make one key and one index.
0
 

Author Comment

by:zachvaldez
ID: 38741874
Got it!
One more thing,
Can you summarize in short what these 2 entries would do or make efficient the table>
just for future references of questioners.

thanks
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 38741927
PK_partId is the primary key, it is a unique (probably clustered) index that is used when you want to specifically identify exactly one record in the table.  Clustered means that the order of records stored in the database are in the order of this index.  The main power of the clustered index is that its faster when you use > < and BETWEENs operations since it just has to step to the next or previous record.

IX_MyTable_DivID is a normal index.  When you want to find records using a DivID it searches the index rather than going through every row in the table.  The index is organized in a b-tree fashion so it doesn't have to take too many steps to find the row(s) you are looking for.  Think about the index in a book, it allows you to quickly find an important concept in a book, same for an index on a table.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38742100
Again, the DivID column should remain your clustered index!

The primary key should be either PartID alone or ( DivID, PartID ), depending on your data requirements.

A unique value like PartID works great as a nonclusted index.  Clustering by DivID will give better performance (assuming the table was reasonably keyed originally).
0
 

Author Comment

by:zachvaldez
ID: 38742185
Where can I see the setting for clustered or nonclustered? Is this something set manually. What is the default setting? Is this a must thing to do need to watch for,care for,aware of?
0
 
LVL 9

Assisted Solution

by:Beartlaoi
Beartlaoi earned 150 total points
ID: 38742233
Its not a must thing.  Clustering is only needed when you partition a table.  it also helps performance for certain types of queries where you are searching for a range of things.
If you just added the primary key using the gold key button then your primary key is clustered.

Go into your table design, into that indexes box, select the PK and then under Table Designer look at Create as Clustered.  If you want to move this to another index then set it to No.  Then select the IX and change its Create as Clustered to Yes.  Hit Close and then save your table.
0
 

Author Comment

by:zachvaldez
ID: 38742290
By setting the PK PartId its Clustered index to "No" and making Divid set clustered to Yes would achieve what favorable advantage and efficiency?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38742291
>> Is this a must thing to do need to watch for,care for,aware of? <<
IF you want good performance from tables, yes.  


>> Clustering is only needed when you partition a table. <<
That's 100% false.


Btw, the GUI is just too flaky to do critical tasks like creating indexes.  Especially on (very) large tables, where it may fail every time.

While line commands can be a pain if you're not comfortable with them, you really need to use SQL statements to add keys and indexes, like so:
ALTER TABLE ... ADD CONSTRAINT creates a primary key.
CREATE INDEX creates an index.


So, to get some practice, if your existing table isn't too large, drop the existing keys and use the command below.

Based on the initial q, I'm assuming the PartID wasn't indexed before -- if it wasn't indexed before, it doesn't need to be separately indexed now.

So, for now, let's say all you need is a clustering, primary key that matches your data requirements:


ALTER TABLE dbo.tablename
    ADD CONSTRAINT PK_DivID_PartID --<<-- this is the constraint name
    PRIMARY KEY CLUSTERED ( DivID, PartID )
    WITH ( FILLFACTOR = 100, SORT_IN_TEMPDB = ON )


You can adjust (or remove) FILLFACTOR if you need to.  I suggest always using the other parameter because it can help prevent your database files from over-growing just because of index builds/rebuilds.

You can get rid of the constraint, for example to add it back with different key columns, like so:

ALTER TABLE dbo.tablename
    DROP CONSTRAINT PK_DivID_PartID
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 38742303
If thats the only column that you are specifying in your WHERE and JOINS then it can help.  Go ahead and do that.
0
 

Author Comment

by:zachvaldez
ID: 38742326
I have about 25 tables and 6 of these tables require that DivId should be allowed to accept duplicates.Divid in the rest of the tables in the db is the Pk. So is it critical to ser the indices using code?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38742335
The clustering on DivID helps if you (almost) always specify it in a WHERE clause; it doesn't have to be the only column.  Quite the opposite, in fact: the more you specify other columns in WHERE clauses, the more useful the clustered index is at restricting the rows that have to be searched.  Indeed, that's why it was made the clustered index.
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 38742342
The indexes and keys will be automatically used by the query engine optimizer, you dont have to do anything special to get their benefits.  They just need to exist when you do your queries.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38742384
If the indexes are already created and they will continue to work you are fine.

But if DivID is defined as a true PK, SQL will not allow duplicate values to be added.  You will need to change the PK to add a column to insure uniqueness to leave it as a PK.  In that case, I would use the code, because typically the statement for one table can be copied, the table name changed, added column name changed if needed, and the next table is done.

The other big advantage of the command is that it forces you to understand what you are doing!  That seems like a disadvantage, but it really isn't.  If you're going to be responsible for creating SQL constraints/indexes, you really need to understand what they are and what they do, each type of them.
0
 

Author Comment

by:zachvaldez
ID: 38742526
I agree . Knowing the ins and outs separates the men from the boys. Thanks for all your inputs.
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 38743826
Make sure to learn from the proper source, read this article about clustered indexes.
MSDN - Clustered Index Design Guidelines
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 150 total points
ID: 38744023
I am an experienced expert already.

Yes, learn the highly generalized and simplified rules first.  But don't let it override expert advice about your specific situation.

That article omits a couple of other situations where a clus index is particularly valuable.  That's to be expected, as that article is not intended to be a replacement for a genuine DBA, as I'm sure MS themselves would tell you.

Unfortunately I can't give you a really full answer as I don't know the full details about your situation.  For that we'd have to look at current index usage and at least a query plan or two.

But, based on your original q, I can tell you what would make sure you do not see a big drop in performance of existing queries.  When making index changes, that's what you most want to prevent.

Finally, there's one fundamental rule you can take from this:

There is no reason to assume the pk and the clus index should be the exact same columns, because they serve different purposes.
0
 

Author Closing Comment

by:zachvaldez
ID: 38751046
I beleive I received excellent responses
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question