Re: Indexes and primary keys, from Delaney
From: DW (None)
Date: 03/04/04
- Next message: RJE: "Working with large string - over 8000 bytes"
- Previous message: Vlad: "DSN or DSNless connection?"
- In reply to: Kalen Delaney: "Re: Indexes and primary keys, from Delaney"
- Next in thread: DW: "Re: Indexes and primary keys, from Delaney"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 04 Mar 2004 13:39:10 -0800
You both answered my questions, I appreciate the help. I thought a
"wide" index was worse than Ray H indicated, from things I had read
online. I'll try some tests.
Thanks.
David Walker
"Kalen Delaney" <replies@public_newsgroups.com> wrote in
news:#k25rmdAEHA.3220@TK2MSFTNGP10.phx.gbl:
> Hi David
>
> One of the issues here is that the Primary Key is a logical concept,
> and an index is a physical concept (an actual structure). During
> logical design, it is a great idea to define a Primary Key for each
> table, so you know you have a way to uniquely identify each row.
>
> If when you physically build your tables, you use SQL Server's
> constraint mechanism to define a set of columns as the PK, you are
> asking SQL Server to enforce the uniqueness of the columns. SQL Server
> does that by automatically building a unique index. But they are still
> two different things. You could just decide you want a column to be
> your PK without asking for an index to enforce it. Then YOU would have
> to find another way to make sure there were no duplicates.
>
> So you can't just drop the index that was built to support the PK,
> because you never created the index separately. You said you want SQL
> Server's help to maintain uniqueness, and to comply, it built the
> index. If you no longer want the index, you must tell SQL Server you
> no longer want it to help maintain uniqueness, and you do that by
> dropping the PK constraint.
>
> As you have figured out, you cannot have the PK or a Unique constraint
> without an index; it is the index that allows SQL Server to enforce
> the uniqueness. You can only have a logical PK that is not declared in
> the database.
>
> It is meaningless to say that you want a Unique Constraint (or PK
> constraint) but that the index is unnecessary. It is the index that
> allows SQL Server to guarantee the uniqueness.
>
> You had a lot of questions as you were trying to make sense out of
> these concepts. If I haven't answered all of them, just let me know!
>
> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "DW" <None> wrote in message
> news:Xns94A1E1BFB6A19DWalker@207.46.248.16...
>> I want to ask a semi-general question, which I think I can do without
>> giving my whole database definition.
>>
>> Books and articles about table design say things like "Columns that
>> are the primary keys or that are unique are most likely to be joined
>> and frequently queried... When no naturally efficient compact key
>> exists, it's often useful to manufacture a surrogate key using the
>> identity property on an int column". (Inside MS SQL Server 2000 by
>> Kalen Delaney and Ron Soukup, as many of you might recognize, page
>> 272.) Then it says to use this surrogate key for joining and data
>> retrieval. I see similar advice frequently.
>>
>> I understand this........ but I have a database with a big
>> transaction table. The people who supply the daily updates claim
>> that the "key" consists of the SSN, account number, transaction date,
>> and a sequence number. They insist that all four make up the key,
>> although they also claim that the sequence number is unique and never
>> reused. Okay.
>>
>> I don't want a wide primary key, because I'll never query on this --
>> the sequence number has no meaning and doesn't appear in any other
>> table.
>>
>> I generally query and/or group on SSN, often on the combination of
>> SSN and account number (which identifies a unique account), and often
>> query on transaction date ranges.
>>
>> I originally had all four fields as a composite primary key. I think
>> this is a bad idea; the index is too big. The sequence number could
>> be the primary key, but there's really no need for it be indexed. If
>> I created a surrogate key just to have a primary key, I would never
>> query or join using it.
>>
>> I fond myself querying on the SSN/account number combination, or
>> joining on SSN and Account Number in the account table, for example,
>> being equal to the SSN and Account Number in the transactions table.
>> (SSN/Account Number is the primary key in the account table; that
>> makes sense and seems normal to me.)
>>
>> I'm having trouble figuring out how this good advice applies to my
>> Transactions table. Reams of good advice can't be all wrong.
>>
>> Even without you guys knowing all the things I do with this data, is
>> it reasonable cases like this to have a primary key (the transaction
>> sequence number) that's not indexed? I don't really care if the
>> sequence number is unique or not, but I could make a Unique
>> constraint. (I always make a primary key if only so I can open my SQL
>> table from MS Access, as a project, if I want to.)
>>
>> Where the book says that you can't drop the index created as a result
>> of a primary key, but must instead drop the constraint, is kind of
>> confusing -- dropping the constraint drops the index? I might want
>> to drop the index and leave the Unique constraint.
>>
>> Is a unique constraint as efficient (or more) than having an
>> unneccesary index?
>>
>> Thanks.
>>
>> David Walker
>
>
>
- Next message: RJE: "Working with large string - over 8000 bytes"
- Previous message: Vlad: "DSN or DSNless connection?"
- In reply to: Kalen Delaney: "Re: Indexes and primary keys, from Delaney"
- Next in thread: DW: "Re: Indexes and primary keys, from Delaney"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
