Clustered non-unique index appears as non-clustered.

Hi,
I'm having a problem creating a clustered non-unique index on two my 
biggest tables each one located on its own segment.
I created the table on the segment, migrated the data using DBArtisan 
and trying to create clustered index. Everything looks OK but it always 
create clustered index idxShowInputCluster as non-clustered. I have this 
problem only with those 2 tables that locate on its own segments. I
tryed this from DBArtisan and from Sybase Central - doesn't work.

Script sample for one of them:
EXEC sp_addsegment 'segShowInput','MovieFone','ShowInput1'
EXEC sp_extendsegment 'segShowInput','MovieFone','ShowInput2'

CREATE TABLE dbo.ShowInput 
(    UserID        numeric(10,0) NOT NULL,
    ShowID        numeric(10,0) NOT NULL,
    ShowTime      smalldatetime NULL,
    InputDate     datetime      NULL,
    UserType      char(1)       NULL,
    ShowDate      smalldatetime NULL,
    IsDeleted     bit           NOT NULL,
    InputUserType char(1)       NULL,
    TimeStamp     timestamp     NOT NULL,
    CONSTRAINT ShowInput_1814297523 FOREIGN KEY (ShowID) REFERENCES
dbo.MovieShow (ShowID))
ON segShowInput

--migrate the data----

CREATE CLUSTERED INDEX idxShowInputCluster
    ON dbo.ShowInput(ShowID,UserType,ShowDate)
    ON segShowInput

CREATE NONCLUSTERED INDEX idxShowInputInputDate
    ON dbo.ShowInput(InputDate)

CREATE NONCLUSTERED INDEX ShowDateIdx
    ON dbo.ShowInput(ShowDate)

Any ideas on it are greatly appreciated.
Thank you.
Ninel
0
Ken
2/8/1999 2:51:51 PM
sybase.sqlserver.admin 2338 articles. 0 followers. Follow

5 Replies
716 Views

Similar Articles

[PageSpeed] 51

Ken Wodiska wrote:
> 
> Hi,
> I'm having a problem creating a clustered non-unique index on two my
> biggest tables each one located on its own segment.
> I created the table on the segment, migrated the data using DBArtisan
> and trying to create clustered index. Everything looks OK but it always
> create clustered index idxShowInputCluster as non-clustered. I have this
> problem only with those 2 tables that locate on its own segments. I
> tryed this from DBArtisan and from Sybase Central - doesn't work.
> 
> Script sample for one of them:
> EXEC sp_addsegment 'segShowInput','MovieFone','ShowInput1'
> EXEC sp_extendsegment 'segShowInput','MovieFone','ShowInput2'
> 
> CREATE TABLE dbo.ShowInput
> (    UserID        numeric(10,0) NOT NULL,
>     ShowID        numeric(10,0) NOT NULL,
>     ShowTime      smalldatetime NULL,
>     InputDate     datetime      NULL,
>     UserType      char(1)       NULL,
>     ShowDate      smalldatetime NULL,
>     IsDeleted     bit           NOT NULL,
>     InputUserType char(1)       NULL,
>     TimeStamp     timestamp     NOT NULL,
>     CONSTRAINT ShowInput_1814297523 FOREIGN KEY (ShowID) REFERENCES
> dbo.MovieShow (ShowID))
> ON segShowInput
> 
> --migrate the data----
> 
> CREATE CLUSTERED INDEX idxShowInputCluster
>     ON dbo.ShowInput(ShowID,UserType,ShowDate)
>     ON segShowInput
> 
> CREATE NONCLUSTERED INDEX idxShowInputInputDate
>     ON dbo.ShowInput(InputDate)
> 
> CREATE NONCLUSTERED INDEX ShowDateIdx
>     ON dbo.ShowInput(ShowDate)
> 
> Any ideas on it are greatly appreciated.
> Thank you.
> Ninel

Hi,

Does it work if you enter the "create clustered index"
command from an isql session?

If not, could you post part of a session where you issue 
---------------
sp_help ShowInput
go
CREATE CLUSTERED INDEX idxShowInputCluster
     ON dbo.ShowInput(ShowID,UserType,ShowDate)
     ON segShowInput
go
sp_help ShowInput
go
----------------

-bret

--
Bret Halford 
Sybase Technical Support			
3665 Discovery Drive		
Boulder, CO 80303
0
Bret
2/8/1999 3:27:35 PM

Bret Halford wrote:

> Hi,
> Does it work if you enter the "create clustered index"
> command from an isql session?
>
> If not, could you post part of a session where you issue
> ---------------
> sp_help ShowInput
> go
> CREATE CLUSTERED INDEX idxShowInputCluster
>      ON dbo.ShowInput(ShowID,UserType,ShowDate)
>      ON segShowInput
> go
> sp_help ShowInput
> go
> ----------------
> -bret
> --
> Bret Halford
> Sybase Technical Support
> 3665 Discovery Drive
> Boulder, CO 80303

>>>>>>>>>>>>>>>>>>>.
Hi,
When I create this clustered index from isql session and run sp_help ShowInput

it shows that index created as clustered.
But if then I go to Index->Properties it says that index is non-clustered.

My dbArtisan shows this index as non-clustered in explorer but when I run
sp_help ShowInput it says index is clustered and also gives me
message 'Object is not partitioned.'

I'm new to Sybase and kind of confused with this differences in information
that I'm getting on one index. We use Sybase ver. 11.9.2.

Thank you very much for helping.
Ninel
nro@moviefone.com
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Name Owner Type
ShowInput dbo user table

Data_located_on_segment When_created
segShowInput Jan 26 1999 11:16:34:293AM

Column_name Type               Length Prec Scale Nulls Default_name Rule_name
Identity
UserID          numeric             6 10 0  0  NULL NULL 0
ShowID          numeric           6 10 0  0  NULL NULL 0
ShowTime        smalldatetime   4 NULL NULL 1 NULL NULL 0
InputDate       datetime           8 NULL NULL 1 NULL NULL 0
UserType        char                1 NULL NULL 1 NULL NULL 0
ShowDate        smalldatetime   4 NULL NULL 1 NULL NULL 0
IsDeleted       bit                    1 NULL NULL 0  NULL NULL 0
InputUserType   char             1 NULL NULL 1 NULL NULL 0
TimeStamp       timestamp       8 NULL NULL 0  NULL NULL 0

index_name index_description index_keys index_max_rows_per_page
index_fillfactor index_reservepagegap
idxShowInputCluster  clustered, allow duplicate rows located on segShowInput
ShowID, UserType, ShowDate 0  0  0
No defined keys for this object.
Lock scheme Datarows

exp_row_size reservepagegap fillfactor max_rows_per_page
0  0  0  0
---------------
Error messsage in DBArtisan output messages window:
DBMS  Dataentry2 --Object is not partitioned.



0
NRO
2/8/1999 5:47:52 PM
--------------2C34811FE2DAB295AA7DDFD3
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

> I would trust sp_help rather than the haunting GUI's.  Incidentally, I also use
> MSSQL Server, and they have invested a lot in gui based tools.  However, all of
> the seasoned professionals still use isql, so this is what I do!

Steve


> Bret Halford wrote:
>
> > Hi,
> > Does it work if you enter the "create clustered index"
> > command from an isql session?
> >
> > If not, could you post part of a session where you issue
> > ---------------
> > sp_help ShowInput
> > go
> > CREATE CLUSTERED INDEX idxShowInputCluster
> >      ON dbo.ShowInput(ShowID,UserType,ShowDate)
> >      ON segShowInput
> > go
> > sp_help ShowInput
> > go
> > ----------------
> > -bret
> > --
> > Bret Halford
> > Sybase Technical Support
> > 3665 Discovery Drive
> > Boulder, CO 80303
>
> >>>>>>>>>>>>>>>>>>>.
> Hi,
> When I create this clustered index from isql session and run sp_help ShowInput
>
> it shows that index created as clustered.
> But if then I go to Index->Properties it says that index is non-clustered.
>
> My dbArtisan shows this index as non-clustered in explorer but when I run
> sp_help ShowInput it says index is clustered and also gives me
> message 'Object is not partitioned.'
>
> I'm new to Sybase and kind of confused with this differences in information
> that I'm getting on one index. We use Sybase ver. 11.9.2.
>
> Thank you very much for helping.
> Ninel
> nro@moviefone.com
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> Name Owner Type
> ShowInput dbo user table
>
> Data_located_on_segment When_created
> segShowInput Jan 26 1999 11:16:34:293AM
>
> Column_name Type               Length Prec Scale Nulls Default_name Rule_name
> Identity
> UserID          numeric             6 10 0  0  NULL NULL 0
> ShowID          numeric           6 10 0  0  NULL NULL 0
> ShowTime        smalldatetime   4 NULL NULL 1 NULL NULL 0
> InputDate       datetime           8 NULL NULL 1 NULL NULL 0
> UserType        char                1 NULL NULL 1 NULL NULL 0
> ShowDate        smalldatetime   4 NULL NULL 1 NULL NULL 0
> IsDeleted       bit                    1 NULL NULL 0  NULL NULL 0
> InputUserType   char             1 NULL NULL 1 NULL NULL 0
> TimeStamp       timestamp       8 NULL NULL 0  NULL NULL 0
>
> index_name index_description index_keys index_max_rows_per_page
> index_fillfactor index_reservepagegap
> idxShowInputCluster  clustered, allow duplicate rows located on segShowInput
> ShowID, UserType, ShowDate 0  0  0
> No defined keys for this object.
> Lock scheme Datarows
>
> exp_row_size reservepagegap fillfactor max_rows_per_page
> 0  0  0  0
> ---------------
> Error messsage in DBArtisan output messages window:
> DBMS  Dataentry2 --Object is not partitioned.

--
============================================================================
Stephen Simpson
Simpson Professional Services, Limited
email:ss@londonmail.com
Corporate Informatio: http://www.sps.mcmail.com



--------------2C34811FE2DAB295AA7DDFD3
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>

<BLOCKQUOTE TYPE=CITE>I would trust sp_help rather than the haunting GUI's.&nbsp;
Incidentally, I also use MSSQL&nbsp;Server, and they have invested a lot
in gui based tools.&nbsp; However, all of the seasoned professionals still
use isql, so this is what I do!</BLOCKQUOTE>

<P><BR>Steve
<BR>&nbsp;
<BLOCKQUOTE TYPE=CITE>Bret Halford wrote:
<P>> Hi,
<BR>> Does it work if you enter the "create clustered index"
<BR>> command from an isql session?
<BR>>
<BR>> If not, could you post part of a session where you issue
<BR>> ---------------
<BR>> sp_help ShowInput
<BR>> go
<BR>> CREATE CLUSTERED INDEX idxShowInputCluster
<BR>>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON dbo.ShowInput(ShowID,UserType,ShowDate)
<BR>>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON segShowInput
<BR>> go
<BR>> sp_help ShowInput
<BR>> go
<BR>> ----------------
<BR>> -bret
<BR>> --
<BR>> Bret Halford
<BR>> Sybase Technical Support
<BR>> 3665 Discovery Drive
<BR>> Boulder, CO 80303
<P>>>>>>>>>>>>>>>>>>>>.
<BR>Hi,
<BR>When I create this clustered index from isql session and run sp_help
ShowInput
<P>it shows that index created as clustered.
<BR>But if then I go to Index->Properties it says that index is non-clustered.
<P>My dbArtisan shows this index as non-clustered in explorer but when
I run
<BR>sp_help ShowInput it says index is clustered and also gives me
<BR>message 'Object is not partitioned.'
<P>I'm new to Sybase and kind of confused with this differences in information
<BR>that I'm getting on one index. We use Sybase ver. 11.9.2.
<P>Thank you very much for helping.
<BR>Ninel
<BR>nro@moviefone.com
<BR>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<BR>Name Owner Type
<BR>ShowInput dbo user table
<P>Data_located_on_segment When_created
<BR>segShowInput Jan 26 1999 11:16:34:293AM
<P>Column_name Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Length Prec Scale Nulls Default_name Rule_name
<BR>Identity
<BR>UserID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; numeric&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
6 10 0&nbsp; 0&nbsp; NULL NULL 0
<BR>ShowID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; numeric&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
6 10 0&nbsp; 0&nbsp; NULL NULL 0
<BR>ShowTime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; smalldatetime&nbsp;&nbsp;
4 NULL NULL 1 NULL NULL 0
<BR>InputDate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; datetime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
8 NULL NULL 1 NULL NULL 0
<BR>UserType&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; char&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1 NULL NULL 1 NULL NULL 0
<BR>ShowDate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; smalldatetime&nbsp;&nbsp;
4 NULL NULL 1 NULL NULL 0
<BR>IsDeleted&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1 NULL NULL 0&nbsp; NULL NULL 0
<BR>InputUserType&nbsp;&nbsp; char&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1 NULL NULL 1 NULL NULL 0
<BR>TimeStamp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; timestamp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
8 NULL NULL 0&nbsp; NULL NULL 0
<P>index_name index_description index_keys index_max_rows_per_page
<BR>index_fillfactor index_reservepagegap
<BR>idxShowInputCluster&nbsp; clustered, allow duplicate rows located on
segShowInput
<BR>ShowID, UserType, ShowDate 0&nbsp; 0&nbsp; 0
<BR>No defined keys for this object.
<BR>Lock scheme Datarows
<P>exp_row_size reservepagegap fillfactor max_rows_per_page
<BR>0&nbsp; 0&nbsp; 0&nbsp; 0
<BR>---------------
<BR>Error messsage in DBArtisan output messages window:
<BR>DBMS&nbsp; Dataentry2 --Object is not partitioned.</BLOCKQUOTE>

<PRE>--&nbsp;
============================================================================
Stephen Simpson
Simpson Professional Services, Limited
email:ss@londonmail.com
Corporate Informatio: <A HREF="http://www.sps.mcmail.com">http://www.sps.mcmail.com</A></PRE>
&nbsp;</HTML>

--------------2C34811FE2DAB295AA7DDFD3--

0
Stephen
2/8/1999 6:17:05 PM
Ninel,
As with my other reply, this might actually not be indid = 1.
Could you do the following:

select * from sysindexes where id = object_id("ShowInput")

Look at name and indid = if indid = 1 then it's a allpageslocked
table with a true clustered index.  If indid>1 then it's another
locking format table with non-clustered style of clustered index,
which is expected.  DBArtisan 5.0 will support 11.9.2 better, which
is due out soon.

- John McVicker
Sybase Professional Services
Philadelphia, PA

NRO wrote:
> 
> Bret Halford wrote:
> 
> > Hi,
> > Does it work if you enter the "create clustered index"
> > command from an isql session?
> >
> > If not, could you post part of a session where you issue
> > ---------------
> > sp_help ShowInput
> > go
> > CREATE CLUSTERED INDEX idxShowInputCluster
> >      ON dbo.ShowInput(ShowID,UserType,ShowDate)
> >      ON segShowInput
> > go
> > sp_help ShowInput
> > go
> > ----------------
> > -bret
> > --
> > Bret Halford
> > Sybase Technical Support
> > 3665 Discovery Drive
> > Boulder, CO 80303
> 
> >>>>>>>>>>>>>>>>>>>.
> Hi,
> When I create this clustered index from isql session and run sp_help ShowInput
> 
> it shows that index created as clustered.
> But if then I go to Index->Properties it says that index is non-clustered.
> 
> My dbArtisan shows this index as non-clustered in explorer but when I run
> sp_help ShowInput it says index is clustered and also gives me
> message 'Object is not partitioned.'
> 
> I'm new to Sybase and kind of confused with this differences in information
> that I'm getting on one index. We use Sybase ver. 11.9.2.
> 
> Thank you very much for helping.
> Ninel
> nro@moviefone.com
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> Name Owner Type
> ShowInput dbo user table
> 
> Data_located_on_segment When_created
> segShowInput Jan 26 1999 11:16:34:293AM
> 
> Column_name Type               Length Prec Scale Nulls Default_name Rule_name
> Identity
> UserID          numeric             6 10 0  0  NULL NULL 0
> ShowID          numeric           6 10 0  0  NULL NULL 0
> ShowTime        smalldatetime   4 NULL NULL 1 NULL NULL 0
> InputDate       datetime           8 NULL NULL 1 NULL NULL 0
> UserType        char                1 NULL NULL 1 NULL NULL 0
> ShowDate        smalldatetime   4 NULL NULL 1 NULL NULL 0
> IsDeleted       bit                    1 NULL NULL 0  NULL NULL 0
> InputUserType   char             1 NULL NULL 1 NULL NULL 0
> TimeStamp       timestamp       8 NULL NULL 0  NULL NULL 0
> 
> index_name index_description index_keys index_max_rows_per_page
> index_fillfactor index_reservepagegap
> idxShowInputCluster  clustered, allow duplicate rows located on segShowInput
> ShowID, UserType, ShowDate 0  0  0
> No defined keys for this object.
> Lock scheme Datarows
> 
> exp_row_size reservepagegap fillfactor max_rows_per_page
> 0  0  0  0
> ---------------
> Error messsage in DBArtisan output messages window:
> DBMS  Dataentry2 --Object is not partitioned.
0
John
2/12/1999 5:47:12 AM
Thank you all for your responces.
I'm new to Sybase and migrating production from 11.03 to ASE 11.9.2
so this difference in approaches to clustered indexes was very 
confusing to me.
Thanks.
Ninel

John McVicker wrote:
> 
> Ninel,
> As with my other reply, this might actually not be indid = 1.
> Could you do the following:
> 
> select * from sysindexes where id = object_id("ShowInput")
> 
> Look at name and indid = if indid = 1 then it's a allpageslocked
> table with a true clustered index.  If indid>1 then it's another
> locking format table with non-clustered style of clustered index,
> which is expected.  DBArtisan 5.0 will support 11.9.2 better, which
> is due out soon.
> 
> - John McVicker
> Sybase Professional Services
> Philadelphia, PA
> 
> NRO wrote:
> >
> > Bret Halford wrote:
> >
> > > Hi,
> > > Does it work if you enter the "create clustered index"
> > > command from an isql session?
> > >
> > > If not, could you post part of a session where you issue
> > > ---------------
> > > sp_help ShowInput
> > > go
> > > CREATE CLUSTERED INDEX idxShowInputCluster
> > >      ON dbo.ShowInput(ShowID,UserType,ShowDate)
> > >      ON segShowInput
> > > go
> > > sp_help ShowInput
> > > go
> > > ----------------
> > > -bret
> > > --
> > > Bret Halford
> > > Sybase Technical Support
> > > 3665 Discovery Drive
> > > Boulder, CO 80303
> >
> > >>>>>>>>>>>>>>>>>>>.
> > Hi,
> > When I create this clustered index from isql session and run sp_help ShowInput
> >
> > it shows that index created as clustered.
> > But if then I go to Index->Properties it says that index is non-clustered.
> >
> > My dbArtisan shows this index as non-clustered in explorer but when I run
> > sp_help ShowInput it says index is clustered and also gives me
> > message 'Object is not partitioned.'
> >
> > I'm new to Sybase and kind of confused with this differences in information
> > that I'm getting on one index. We use Sybase ver. 11.9.2.
> >
> > Thank you very much for helping.
> > Ninel
> > nro@moviefone.com
> > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> > Name Owner Type
> > ShowInput dbo user table
> >
> > Data_located_on_segment When_created
> > segShowInput Jan 26 1999 11:16:34:293AM
> >
> > Column_name Type               Length Prec Scale Nulls Default_name Rule_name
> > Identity
> > UserID          numeric             6 10 0  0  NULL NULL 0
> > ShowID          numeric           6 10 0  0  NULL NULL 0
> > ShowTime        smalldatetime   4 NULL NULL 1 NULL NULL 0
> > InputDate       datetime           8 NULL NULL 1 NULL NULL 0
> > UserType        char                1 NULL NULL 1 NULL NULL 0
> > ShowDate        smalldatetime   4 NULL NULL 1 NULL NULL 0
> > IsDeleted       bit                    1 NULL NULL 0  NULL NULL 0
> > InputUserType   char             1 NULL NULL 1 NULL NULL 0
> > TimeStamp       timestamp       8 NULL NULL 0  NULL NULL 0
> >
> > index_name index_description index_keys index_max_rows_per_page
> > index_fillfactor index_reservepagegap
> > idxShowInputCluster  clustered, allow duplicate rows located on segShowInput
> > ShowID, UserType, ShowDate 0  0  0
> > No defined keys for this object.
> > Lock scheme Datarows
> >
> > exp_row_size reservepagegap fillfactor max_rows_per_page
> > 0  0  0  0
> > ---------------
> > Error messsage in DBArtisan output messages window:
> > DBMS  Dataentry2 --Object is not partitioned.
0
Ninel
2/13/1999 12:13:35 AM
Reply:

Web resources about - Clustered non-unique index appears as non-clustered. - sybase.sqlserver.admin

Resources last updated: 1/4/2016 3:01:12 PM