Solved

Allow duplicates in a table

Posted on 2013-01-03
31
266 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
  • 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
 

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:ScottPletcher
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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:
ScottPletcher 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL page split per second is high 19 63
query help 18 50
Creating Alerts in sql sever 2 13
Sql query for filter 12 21
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now