Can a Sybase SQL table have a computed column?-CodePudding

I am trying to modify an SQL table so that one of the columns is "computed", in this case it always holds the LOWERcase of another column. This would enable me to use this column in an index and speed up results. However, I cannot make it work with the Sybase SQL system that I am using. I cannot find examples except for all other SQL types (MS or Oracle, for example). Any help please?

CodePudding user response：

I apologise, I might have found the answer, which is to select directly into the computed value

v_Value COMPUTED(LOWER(v_Field1)

and forget any types.

The next problem is the index:

Cannot create index on a virtual computed column (v_Value). Modify it to materialized using the ALTER TABLE MODIFY commmand first and retry.

CodePudding user response：

Sybase ASE does support computed columns, in two different formats ... virtual (value is determined each time column is selected) ... and materialized (value is (re)calculated when a base columns value is changed, with the (re)calculated value being written into the materialized/computed column).

An index can only be created on a materialized/computed column.

More details can be found in the Transact-SQL Users Guide

Not sure how far OP's gotten in the research so here are a few examples:

NOTE: assorted sp_help* output modified for brevity

Create virtual column with create table command:

create table mytab
(name    varchar(30)
,lname1  as lower(name)
)
go

sp_help mytab
go

 Column_name Type    Computed_Column_object          
 ----------- ------- --------------------------------
 name        varchar NULL                            
 lname1      varchar mytab_lname1_835531029 (virtual)

Verify index cannot be created on a virtual column:

create index idx1 on mytab(lname1)
go

Msg 1777, Level 16, State 1:
Server 'ASE400', Line 2:
Cannot create index on a virtual computed column (lname1). Modify it to materialized using the ALTER TABLE MODIFY commmand first and retry.

Modify column to be materialized:

alter table mytab
modify lname1 materialized
go

sp_help mytab
go

 Column_name Type    Computed_Column_object          
 ----------- ------- --------------------------------------
 name        varchar NULL                            
 lname1      varchar mytab_lname1_1139532112 (materialized)

Now index can be created:

create index idx1 on mytab(lname1)
go
Non-clustered index (index id = 2) is being rebuilt.

sp_helpindex mytab
go

Object has the following indexes

 index_name index_keys index_description
 ---------- ---------- -----------------
 idx1        lname1    nonclustered

Add another computed column via the alter table command, add an index:

alter table mytab
add lname2 as lower(name) materialized
go

create index idx2 on mytab(lname2)
go

sp_help mytab
go

 Column_name Type    Computed_Column_object          
 ----------- ------- --------------------------------------
 name        varchar NULL                            
 lname1      varchar mytab_lname1_1139532112 (materialized) 
 lname2      varchar mytab_lname2_1459533252 (materialized)

Object has the following indexes

 index_name index_keys index_description
 ---------- ---------- -----------------
 idx1        lname1    nonclustered 
 idx2        lname2    nonclustered

Take for a test drive:

insert mytab (name) values ('Bob Smith')
go

select * from mytab
go

 name      lname1    lname2
 --------- --------- ---------
 Bob Smith bob smith bob smith

update mytab set name = 'Suzie Q'
go

select * from mytab
go

 name    lname1  lname2
 ------- ------- -------
 Suzie Q suzie q suzie q

FWIW, Sybase ASE also supports function-based indexes; under the covers a new materialized/computed column is created to support the index, eg:

create index idx3 on mytab(lower(name))
go

sp_help mytab
go

 Column_name Type    Computed_Column_object          
 ----------- ------- --------------------------------------
 name        varchar NULL                            
 lname1      varchar mytab_lname1_1139532112 (materialized) 
 lname2      varchar mytab_lname2_1459533252 (materialized)
 sybfi4_1    varchar mytab_sybfi4_1923534905 (functional index key)

Object has the following indexes

 index_name index_keys index_description
 ---------- ---------- -----------------
 idx1        lname1    nonclustered 
 idx2        lname2    nonclustered 
 idx3        sybfi4_1  nonclustered