Good Morning Sql Gurus 

Please give me some inputs on the following issues.

create table x
(
    col1    int not null,
    col2     int null
)
alter table x add constraint xpk primary key (col1)
create index xsk on x(col2)

drop table y
create table Y
(
    col1    int not null,
    col2     int null
)
alter table Y add constraint xpk1 primary key (col1)
create clustered index xpk on Y(COL1)
create index xsk on Y(col2)
create index xsk1 on Y(col2)


select * From sysindexes i
inner join sysobjects o
on i.id = o.id
where O.name like 'x'

select object_name(i.id),* From sysindexes i
inner join sysobjects o
on i.id = o.id
where O.name like 'Y'


Questions

1. A table can have indexes with different names but with the same columns

   Question : Is it not a problem ? if not what is the reason behind it ?


2.     Case 1        a.    Create a Table
            b.    Create a Clustered Index
            c.    Create a Primary Key

    Case 2        a.     Create a Table
            b.    Create a Primary Key
            c.    Create a Clustered Index

    In the case 2 it errors out but not in the case 1 why ?

On a Fresh New table if I create a Primary Key then it is a Clustered key.
But If the table has already Clustered Index then create a Primary key it
will not be a clustered key ?

so the Primary key constraint is dynamically changing based on the table has
a clustered key or not

3.    Case 1    a.    Create a Table X
        b.    Create a Primary Key With Name XPK

    Case 2    a.     Create a Table Y
        b.    Create a Primary Key With same Name XPK

In the Case 2 it fails but it should not file
to my understading, Primary key or index name should be unique with specific
to a table but not with specific to a database.

In sysindexes  primary key is  "id and indid"   


Thanks & Regards
Rajesh Peddireddy

On Fri, 21 Jul 2006 09:02:01 -0700, Rajesh
<Raj***@discussions.microsoft.com> wrote:

For the most part they are simply the way things are.  Like every
piece of software, you learn to use it the way it works.

Show quoteHide quoteIt is only a real problem if it breaks something.  At most it is a
small problem, wasting a bit of space.  If you don't want to waste the
space, don't do it.

There are times that, for performance reasons, you want a
non-clustered index on the same column(s) as the clustered index.  Not
often but sometimes.

The default is to create the PK clustered, but it is smart enough to
recognize that if there is already a clustered index then it makes it
non-clustered.

There is nothing dynamic about it, and it does not change.  Once it is
set, it is set.  What it is set TO is dependant on the conditions at
the time when it is set.

You say it should.  Microsoft did not create it that way.  Guess who
wins?

The PK name is stored in sysobjects, not sysindexes.

Roy Harvey
Beacon Falls, CT

On Fri, 21 Jul 2006 09:02:01 -0700, Rajesh wrote:

Hi Rajesh,

(snip)
Some reasons for this would be:
a) For multi-column indexes, it's possible that an index on (Col1, Col2)
is useful for some queries and an index on (Col2, Col1) is useful for
other queries;
b) Roy already mentioned the possiblity of a combination of a clustered
index and a non-clustered index on the same columns - though situations
that benefit from this combination arre very, very, very rare.
c) The most common reason for two indexes on the same columns is
operator error - creating an index without first checking if one already
exists.

Extra indexes take extra storage space and add to the overhead for
INSERT, UPDATE, and DELETE commands. Other than that, they are not a
problem.

Show quoteHide quoteIf you don't explicitly specify CLUSTERED or NONCLUSTERED when you
create the primary key constraint, it will default to clustered if there
is no clustered index on the table yet, or to nonclustered otherwise.
That's why the first case succeeds (there's already a clustered index,
so the index created for the primary kkey will be nonclustered), but the
second fails (the primary kkey index defaults to clustered and then the
explicit CREATE CLUSTERED INDEX has to fail).

For indexes, you are right. Index names have to be unique within the
table, not within the database.

However, constraint names have to be uniqne in the database.

column dataType
Joining 2 select statement with like fields
Views and CTEs performance problem
Adding a space in a field
Can't use Shared Memory protocol to connect