RowID in Sybase 1

[mehrnosh]

Hi all

Is there a command by which i can get the rowid of the
rows in a sybase table just like rowid in Oracle

Thanks

 

[sybaseguru]

Hi,

No there is no equivalent of rowid in sybase. in Oracle a rowid uniquely identifies the physical location of every row within a table. These are stored in binary format and you can use procedures from DBMS_ROWID package to display the rowid. Sybase does not offer anything that directly maps to this data type. However an identity column that has a clustered index built on it will simulate the same behaviour. In this case identity is equivalent to sequence in Oracle. However, Sybase identity columns are very easy to produce. For example in the following table I have,

1> create table abc
(col_id numeric(10,0) identity, username varchar(30)  not  null)
3> go
create unique clustered index abc_ind1 on abc (col_id)
go
3> sp_help abc
4> go
 Name                           Owner                          Object_type
 ------------------------------ ------------------------------ --------------------------------
 abc                            dbo                            user table

(1 row affected)
 Data_located_on_segment        When_created
 ------------------------------ --------------------------
 default                               Jun 19 2003  7:08PM
 Column_name     Type            Length      Prec Scale Nulls Default_name    Rule_name       Access_Rule_name               Identity
 --------------- --------------- ----------- ---- ----- ----- --------------- --------------- ------------------------------ --------
 col_id          numeric                   6   10     0     0 NULL            NULL            NULL                                  1
 username        varchar                  30 NULL  NULL     0 NULL            NULL            NULL                                  0
 index_name           index_description
         index_keys



         index_max_rows_per_page index_fillfactor index_reservepagegap index_created
 -------------------- --------------------------------------------------------
         ---------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------
         ----------------------- ---------------- -------------------- -------------------
 abc_ind1             clustered, unique located on default
          col_id



                               0                0                    0 Jun 19 2003  7:09PM

(1 row affected)
No defined keys for this object.
Object is not partitioned.
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme.
The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme.

 exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
 ------------ -------------- ---------- ----------------- ------------
            0              0          0                 0            0

(1 row affected)
 concurrency_opt_threshold optimistic_index_lock
 ------------------------- ---------------------
                         0                     0
(return status = 0)
1> insert abc values ('first record')
2> insert abc values ('second record')
3> insert abc values ('third record')
4> go
(1 row affected)
(1 row affected)
(1 row affected)
1> select * from abc where col_id = 2
2> go
 col_id        username
 ------------- ------------------------------
             2 first record

(1 row affected)
[/color]

So in here I created table abc with a column "col_id" defined as an identity column. This is a monotonically increasing number and when you insert a record into table you ignore this column. Sybase automatically inserts relevant values. I also built a unique clustered index on it (col_id is this case is always unique anyway). A clustered index in Sybase is equivalent to "index organised" table in Oracle. Anyway I can use the value of col_id to get the nth record. For example select * where col_id = 2 will return the second row etc.

I hope this helps

 

[mehrnosh]

Thanks sybaseguru , this is of great help.
but unfortunately i have not created a column with datatype
as identity, without an identity column can i get the
rowid

if i add a new column with identity datatype will it give
the value to the existing rows?

Thanks again for your quick reply

 

[sybaseguru]

Dear friend,

Yes no sweat. If you add a column with identity type then it should do the trick

1> create table abc (name       varchar(30) not null)
2> go
1> insert abc values ('first row')
2> insert abc values ('second row')
3> insert abc values ('third row')
4> go
(1 row affected)
(1 row affected)
(1 row affected)
1> alter table abc add col_id numeric(10,0) identity
2> go
1> select * from abc
2> go
 name                           col_id
 ------------------------------ -------------
 first row                                  1
 second row                                 2
 third row                                  3

(3 rows affected)

And good luck

 

[mehrnosh]

Thanks
Yes am planning to do the same , but am stuck up with other
work on proc's.
But by creating a new column of identity datatype will
it assign the value to that column as per the rows were
inserted initially when the table was created, cause my
table already has about 99,000+ rows

Thanks again boss

 

[sybaseguru]

Yes no problem. To be sure create a unique clustered index on col_id. In that case data will be physically stored in the orderr of col_id. So in summary alter table add identity column and create a unique clusterd index on the identity column. You can then refer to col_id effectively like the Oracle rowid and you not need to do any conversion etc.

Good luck and do not forget to mark this post as a helpful post if you agree.

 

[sybaseguru]

Hi,

Also bear in mind the difference between Oracle sequence and Sybase identity. In Oracle if you use sequence to maintain the insertion order using 'select by ordering' on the sequence column, it will only be an approximation. This is because the row with sequence number 101 may well have committed before the row with sequence 100, i.e. it was officially 'first' in the database. In contrast, Sybase identity column value is inserted 'at the time of insertion of the record' so it will always ensure a consistent view of records.