PrimaryKey -Indexes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

loufuki@gmail.com - 18 Jul 2008 21:04 GMT

Hi everyone,
just just want someone to give me a deep understanding of indexes.

I have a table with no primaryKey (PK)
the table also has no clustered index (CI).
It only have a few non-clutered index.

My question is.
to my understanding, the non-clustered index act more like a pointer
( point to primarykey, clustered index!!??)
in my case the table does not have either one of the two!??
where does this nonclusted index points too?

Must I have a PM or clusted index for the non-clusted index to work?

Thanks

Reply to this Message

Alex Kuznetsov - 18 Jul 2008 21:07 GMT

On Jul 18, 3:04 pm, "louf...@gmail.com" <louf...@gmail.com> wrote:

> Hi everyone,
> just just want someone to give me a deep understanding of indexes.
[quoted text clipped - 12 lines]
>
> Thanks

an excellent series by SQL Server MVP Greg Linwood:

http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2007/06/12/725.aspx

Reply to this Message

SQL Menace - 18 Jul 2008 21:09 GMT

From our good old friend Books On Line:

The leaf layer of a nonclustered index does not consist of the data
pages.
Instead, the leaf nodes contain index rows. Each index row contains
the nonclustered key value and one or more row locators that point to
the data row (or rows if the index is not unique) having the key
value.

If the table is a heap (does not have a clustered index), the row
locator is a pointer to the row. The pointer is built from the file
identifier (ID), page number, and number of the row on the page. The
entire pointer is known as a Row ID.

Denis The SQL Menace
http://www.lessthandot.com/
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

On Jul 18, 4:04 pm, "louf...@gmail.com" <louf...@gmail.com> wrote:

> Hi everyone,
> just just want someone to give me a deep understanding of indexes.
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message

Tom Cooper - 18 Jul 2008 21:12 GMT

If you have a clustered index, then all non-clustered indexes contain the
clustered index key (if the clustered index is not a unique index, then the
indexes also contain an integer used to resolve duplicates). If you don't
have a clustered index, then each nonclustered index contains a row
identifier (essentially the page number and where in the page the row is).
So nonclustered indexes work whether or not you have a clustered index.

Note that for most (but not all) tables it is best pratice to have a
clustered index.

Tom

> Hi everyone,
> just just want someone to give me a deep understanding of indexes.
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message

Eric Russell - 18 Jul 2008 21:35 GMT

Feel free not to cluster your table, but every table needs a primary key.

> Hi everyone,
> just just want someone to give me a deep understanding of indexes.
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message

Eric Isaacs - 18 Jul 2008 22:47 GMT

> Feel free not to cluster your table, but every table needs a primary key.

I totally agree that tables should have primary keys as a practice,
but technically, just for clarification, a unique index will work in
place of a primary key. If each table has a unique index, you really
don't need to define a primary key.

Also, if you don't specify a clustered index but do define a primary
key then by default the primary key will be the clustered index.

-Eric Isaacs

Reply to this Message

Andrew J. Kelly - 18 Jul 2008 23:03 GMT

I disagree with that statement as well. A Unique index or Constraint and a
PK constraint are not the same thing. For one the Unique Constraint allows
for 1 NULL value where as a PK does not allow any NULL's. Second you can
have multiple Unique Constraints so which one would be considered the PK?
If you didn't know the data model you could not tell just by that. Where as
a PK is unquestionable as to what it is. The Primary Key column or columns
should be defined with a Primary Key constraint so there is no doubt and
that everyone including the optimizer knows how to deal with it.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

>> Feel free not to cluster your table, but every table needs a primary key.
>
[quoted text clipped - 7 lines]
>
> -Eric Isaacs

Reply to this Message

Eric Russell - 18 Jul 2008 23:41 GMT

What I declare as the primary key, is the key that I actually use in foreign
key relationships. Even if my table has a [natural key], I may add an
identity column for use as the primary key. For example, I don't want to use
ClientID, DOB, SSN4 in foreign key relationships, because it is too wide and
unweildy, so I place a unique index on it and instead declare an identity
column named PersonID as my primary key. If I have a table containing a list
of states and territories, then StateCode is both the natural and primary key.

> I disagree with that statement as well. A Unique index or Constraint and a
> PK constraint are not the same thing. For one the Unique Constraint allows
[quoted text clipped - 16 lines]
> >
> > -Eric Isaacs

Reply to this Message

Eric Isaacs - 19 Jul 2008 02:00 GMT

> What I declare as the primary key, is the key that I actually use in foreign
> key relationships. Even if my table has a [natural key], I may add an
[quoted text clipped - 3 lines]
> column named PersonID as my primary key. If I have a table containing a list
> of states and territories, then StateCode is both the natural and primary key.

Some database purists would disagree with this approach, but I find it
works very well also. I find that keeping the user's data stored in
just one table (as opposed to duplicating the data in several tables
to maintain the relationships) simplifies physical database
architectural changes and also makes updates of the data within the
natural keys easier to maintain and implement since they're not in
multiple locations in the database. This is an approach that works
consistently, whereas the composite key approach causes other
issues.

Both techniques have their trade-offs, but in general, I prefer to
deal with the issues caused by using the surrogate keys over the
composite keys.

-Eric Isaacs

Reply to this Message

Eric Isaacs - 19 Jul 2008 01:41 GMT

> I disagree with that statement as well. A Unique index or Constraint and a
> PK constraint are not the same thing. For one the Unique Constraint allows
[quoted text clipped - 4 lines]
> should be defined with a Primary Key constraint so there is no doubt and
> that everyone including the optimizer knows how to deal with it.

Yes, I was partially incorrect in my post above. Really a Unique
Constraint that doesn't allow NULL values is logically equivalent to a
PK. A unique index and unique constraint are implemented as unique
indexes in SQL Server, but it's really the unique constraint that's
logically equivalent, though physically they're the same. A PK is
just a unique constraint that doesn't allow nulls, that's limited to
just one per table. But you can have more than one unique constraint
that doesn't allow null values in a table. You really don't need a PK
if you have a unique constraint that doesn't allow nulls. Nothing
will be gained from SQL Server's perspective by adding a primary key
if you already have a required unique constraint defined.

-Eric Isaacs

Reply to this Message