Allow duplicates in a table

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?
zachvaldezAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
BeartlaoiCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
zachvaldezAuthor Commented:
How about making those 2 fields as keys or another field paired with DivID. Is that possible?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
BeartlaoiCommented:
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
 
zachvaldezAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
BeartlaoiCommented:
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
 
zachvaldezAuthor Commented:
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
 
zachvaldezAuthor Commented:
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
 
BeartlaoiCommented:
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
 
zachvaldezAuthor Commented:
Ok, for indexes I'll set the DivID but for primary key , PartID will still be the primary key.
0
 
zachvaldezAuthor Commented:
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
 
BeartlaoiCommented:
Add another one which will make one key and one index.
0
 
zachvaldezAuthor Commented:
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
 
BeartlaoiCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
zachvaldezAuthor Commented:
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
 
BeartlaoiConnect With a Mentor Commented:
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
 
zachvaldezAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
BeartlaoiCommented:
If thats the only column that you are specifying in your WHERE and JOINS then it can help.  Go ahead and do that.
0
 
zachvaldezAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
BeartlaoiCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
zachvaldezAuthor Commented:
I agree . Knowing the ins and outs separates the men from the boys. Thanks for all your inputs.
0
 
BeartlaoiCommented:
Make sure to learn from the proper source, read this article about clustered indexes.
MSDN - Clustered Index Design Guidelines
0
 
zachvaldezAuthor Commented:
I beleive I received excellent responses
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.