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?
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 column's 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