CREATE an INDEX when using CREATE TABLE?

17 Sep 2005 3:43 PM

DazedAndConfused

Is there a way to create a index with create table when it is not a primary
key or unique?

Please see below, to see where I am getting lost.

I can create the index using the code below, but I can not see how to create
the index using CREATE TABLE.

drop table customer
-- Customer Table
CREATE TABLE customer
( personID int      not null,
userID   varchar(10) null
-- cust_userID_ind NONCLUSTERED (userid)
-- CONSTRAINT cust_userID_ind NONCLUSTERED (userid)
,
since    smalldatetime not null,
notes    varchar(1000) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
   CONSTRAINT PK_cust_personID PRIMARY KEY CLUSTERED(personID),
--   CONSTRAINT cust_userID_ind NONCLUSTERED (userid),
   CONSTRAINT FK_cust_personID FOREIGN KEY (personID) REFERENCES
person(personID),
   CONSTRAINT FK_cust_updateby FOREIGN KEY (updateby) REFERENCES
users(userID)
)
go

CREATE INDEX cust_userID_ind
ON customer (userID)
go

17 Sep 2005 3:53 PM

Hari Prasad

Hi,

Only the Indexes created based on the constraints (PRIMARY AND UNIQUE) will
be created using a
Create Table command.

Other Indexes needs to be created using CREATE INDEX command.

Thanks
Hari
SQL Server MVP

Show quote

"DazedAndConfused" <AceMago***@yahoo.com> wrote in message
news:uHs5M65uFHA.2312@TK2MSFTNGP14.phx.gbl...
> Is there a way to create a index with create table when it is not a
> primary key or unique?
>
> Please see below, to see where I am getting lost.
>
> I can create the index using the code below, but I can not see how to
> create the index using CREATE TABLE.
>
> drop table customer
> -- Customer Table
> CREATE TABLE customer
> ( personID int      not null,
> userID   varchar(10) null
> -- cust_userID_ind NONCLUSTERED (userid)
> -- CONSTRAINT cust_userID_ind NONCLUSTERED (userid)
> ,
> since    smalldatetime not null,
> notes    varchar(1000) null,
> lastupdate smalldatetime not null,
> updateby varchar(10) not null,
>   CONSTRAINT PK_cust_personID PRIMARY KEY CLUSTERED(personID),
> --   CONSTRAINT cust_userID_ind NONCLUSTERED (userid),
>   CONSTRAINT FK_cust_personID FOREIGN KEY (personID) REFERENCES
> person(personID),
>   CONSTRAINT FK_cust_updateby FOREIGN KEY (updateby) REFERENCES
> users(userID)
> )
> go
>
> CREATE INDEX cust_userID_ind
> ON customer (userID)
> go
>

17 Sep 2005 4:07 PM

DazedAndConfused

Thank you
Show quote

"Hari Prasad" <hari_prasa***@hotmail.com> wrote in message
news:uWYaqA6uFHA.740@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Only the Indexes created based on the constraints (PRIMARY AND UNIQUE)
> will be created using a
> Create Table command.
>
> Other Indexes needs to be created using CREATE INDEX command.
>
> Thanks
> Hari
> SQL Server MVP
>
> "DazedAndConfused" <AceMago***@yahoo.com> wrote in message
> news:uHs5M65uFHA.2312@TK2MSFTNGP14.phx.gbl...
>> Is there a way to create a index with create table when it is not a
>> primary key or unique?
>>
>> Please see below, to see where I am getting lost.
>>
>> I can create the index using the code below, but I can not see how to
>> create the index using CREATE TABLE.
>>
>> drop table customer
>> -- Customer Table
>> CREATE TABLE customer
>> ( personID int      not null,
>> userID   varchar(10) null
>> -- cust_userID_ind NONCLUSTERED (userid)
>> -- CONSTRAINT cust_userID_ind NONCLUSTERED (userid)
>> ,
>> since    smalldatetime not null,
>> notes    varchar(1000) null,
>> lastupdate smalldatetime not null,
>> updateby varchar(10) not null,
>>   CONSTRAINT PK_cust_personID PRIMARY KEY CLUSTERED(personID),
>> --   CONSTRAINT cust_userID_ind NONCLUSTERED (userid),
>>   CONSTRAINT FK_cust_personID FOREIGN KEY (personID) REFERENCES
>> person(personID),
>>   CONSTRAINT FK_cust_updateby FOREIGN KEY (updateby) REFERENCES
>> users(userID)
>> )
>> go
>>
>> CREATE INDEX cust_userID_ind
>> ON customer (userID)
>> go
>>
>
>