Creating Clustered Indexes

Creating Clustered Indexes

Post by ben » Thu, 07 Mar 2002 22:52:46



Hi,

I need my new tables to be ordered on one field...my PK is
not doing the job...

My sp archives data by creating tables dated on the fly as
such:
-------------------------



AID)'


--------------------------

How can I set another index (CLUSTERED) on the field
[ITIME] (ITIME is set as type char(30)).
OR - can my first index be clustered?

Thanks in advance,
Ben

 
 
 

Creating Clustered Indexes

Post by Kevin O'Quin » Thu, 07 Mar 2002 23:55:12


Yes, your first index can be clustered.  Just add 'CLUSTERED' to your
existing create statement (i.e.e 'CREATE UNIQUE CLUSTERED INDEX'.)

HTH,
Kevin O'Quinn


> Hi,

> I need my new tables to be ordered on one field...my PK is
> not doing the job...

> My sp archives data by creating tables dated on the fly as
> such:
> -------------------------



> AID)'


> --------------------------

> How can I set another index (CLUSTERED) on the field
> [ITIME] (ITIME is set as type char(30)).
> OR - can my first index be clustered?

> Thanks in advance,
> Ben


 
 
 

Creating Clustered Indexes

Post by Vijaykumar Ask » Fri, 08 Mar 2002 05:26:21


Ben,
      If  the first index you are talking about is not a PK, then you have
to ensure that your pk is non -clustered before building making it clustered
index or building another clustered index. By default pk on your table will
be clustered.  You can have ONLY ONE clustered index on a table because the
data can be stored only in one sorted order. However you can have 255
non-clustered indexes.

Vijay


> Yes, your first index can be clustered.  Just add 'CLUSTERED' to your
> existing create statement (i.e.e 'CREATE UNIQUE CLUSTERED INDEX'.)

> HTH,
> Kevin O'Quinn



> > Hi,

> > I need my new tables to be ordered on one field...my PK is
> > not doing the job...

> > My sp archives data by creating tables dated on the fly as
> > such:
> > -------------------------



> > AID)'


> > --------------------------

> > How can I set another index (CLUSTERED) on the field
> > [ITIME] (ITIME is set as type char(30)).
> > OR - can my first index be clustered?

> > Thanks in advance,
> > Ben

 
 
 

Creating Clustered Indexes

Post by Rishi M. Agrawal [M » Wed, 20 Mar 2002 04:42:16


Ben,

You could create the Clustered index by using a statement like  "CREATE CLUSTERED INDEX ....."; if it is unique then "CREATE UNIQUE CLUSTERED INDEX....."

Another thing, the new table will not have the primary key if you "Select Into" it. You would have to create a Primary Key if you want that to be defined.

Hope that helps.

Regards,
Rishi M. Agrawal, MCDBA

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure?  For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
--------------------

| Subject: Creating Clustered Indexes
|
| Hi,
|
| I need my new tables to be ordered on one field...my PK is
| not doing the job...
|
| My sp archives data by creating tables dated on the fly as
| such:
| -------------------------

|


| AID)'
|

| --------------------------
|
| How can I set another index (CLUSTERED) on the field
| [ITIME] (ITIME is set as type char(30)).
| OR - can my first index be clustered?
|
| Thanks in advance,
| Ben
|

 
 
 

1. Syntax to create cluster index when create table?

In Sybase I do

create table factory
(
    factory_code char(6),
    factory_name varchar(30,1),
    constraint factory_pri primary key clustered (factory_code)
);

This by default will have unique not null cluster index for factory_code
as
primary key.

I want to do the same in Informix 7.x by

create table factory
(
    factory_code char(6) not null,
    factory_name varchar(30,1),
    primary key (factory_code) constraint factory_pri
);
create cluster index on factory (factory_code);

But this won't work. Because by default the above Informix SQL,
the primary key created a null non cluster index and assigned a
constraint name like 129_99.
I found out this by get in the dbaccess. In my case I have to do

alter index 129_99 to cluster;

But that is not what I want! I want to create a not null cluster index
for the
primary key when creating table like Sybase does.
What is the syntax? Thank you!

--Raymond
--
RC Square Team.

  rctwo.vcf
< 1K Download

2. Rman backup failure (ORA-19571)

3. DBCC DBREINDEX Vs DROP/CREATE clustered index

4. question re dbcombo control

5. Is it possible to create clustered indexes...

6. UDF vs SP

7. Creating Clustered index locks everybody else out of the server

8. passing temp table to another sp

9. Server lockup while creating Clustered index

10. Create clustered index before non-clusterd

11. create clustered / non-clustered index

12. Which index to create : clustered or non-clustered ?