In sybase ase you can create a functional base index that is computed via an expression. This index creates a hidden table column that is getting extracted and generated incorrectly. The index itself is then extracted and generated incorrectly as well. Need to fix current extract table & index code to handled and support this.
Have fixed the extraction and scripting to handle this in version 15. Need to test and decide if we need to back port the code to version 14.
make sure to turn on select into [master..sp_dboption aquafold, 'select into', true]
example:
make sure to turn on select into [master..sp_dboption aquafold, 'select into', true]
example:
Verified in 14.0.9-3. Implemented and tested for Sybase ASE v15 and above. Versions below 15 won't support computed index and gives incorrect syntax if we try to create one.
Verified in 14.0.9-3. Implemented and tested for Sybase ASE v15 and above. Versions below 15 won't support computed index and gives incorrect syntax if we try to create one.
The computed index does not get reflected in the ER Modeler, which we wont implement for the time being
The computed index does not get reflected in the ER Modeler, which we wont implement for the time being
In 14.0.9-3, when we script back the index for a computed column it would be represented as a single hidden column as sybi2_1 but now it is scripted back in original with v14.0.10-1.
CREATE NONCLUSTERED INDEX myindex01 ON dbo.at5(b, begin_date, sybfi2_1) in 14.0.9-3 which was incorrect and
CREATE NONCLUSTERED INDEX myindex01 ON dbo.at5(b, begin_date, dateadd(dd, 1, begin_date)) in v14.0.10-1
To test use:
CREATE TABLE dbo.at5 (
a int NOT NULL, b int NOT NULL,
c int NOT NULL, d varchar(25) NOT NULL,
begin_date date NOT NULL, end_date date NOT NULL)
GO
CREATE NONCLUSTERED INDEX myindex01 ON dbo.at5(b, begin_date, dateadd(dd, 1, begin_date))
GO
In 14.0.9-3, when we script back the index for a computed column it would be represented as a single hidden column as sybi2_1 but now it is scripted back in original with v14.0.10-1.
CREATE NONCLUSTERED INDEX myindex01 ON dbo.at5(b, begin_date, sybfi2_1) in 14.0.9-3 which was incorrect and
CREATE NONCLUSTERED INDEX myindex01 ON dbo.at5(b, begin_date, dateadd(dd, 1, begin_date)) in v14.0.10-1
To test use:
CREATE TABLE dbo.at5 (
a int NOT NULL, b int NOT NULL,
c int NOT NULL, d varchar(25) NOT NULL,
begin_date date NOT NULL, end_date date NOT NULL)
GO
CREATE NONCLUSTERED INDEX myindex01 ON dbo.at5(b, begin_date, dateadd(dd, 1, begin_date))
GO
The incorrect scripting is now fixed in 14.0.10-1
The incorrect scripting is now fixed in 14.0.10-1
CREATE TABLE dbo.sybase_ase_fc_test (
a int NOT NULL,
b char(25) NULL,
c int NOT NULL,
d varchar(25) NOT NULL,
begin_date date NOT NULL,
end_date date NOT NULL
)
LOCK ALLPAGES
WITH max_rows_per_page = 0,
reservepagegap = 0,
identity_gap = 0
GO
CREATE NONCLUSTERED INDEX i4
ON dbo.sybase_ase_fc_test(ascii(b), char_length(b), ltrim(d))
GO
CREATE NONCLUSTERED INDEX i3
ON dbo.sybase_ase_fc_test(sqrt(a), char_length(b), sqrt(c))
GO
CREATE NONCLUSTERED INDEX i2
ON dbo.sybase_ase_fc_test(ascii(b), char_length(b))
GO
CREATE NONCLUSTERED INDEX i1
ON dbo.sybase_ase_fc_test(char_length(b))
GO
CREATE TABLE dbo.sybase_ase_fc_test (
a int NOT NULL,
b char(25) NULL,
c int NOT NULL,
d varchar(25) NOT NULL,
begin_date date NOT NULL,
end_date date NOT NULL
)
LOCK ALLPAGES
WITH max_rows_per_page = 0,
reservepagegap = 0,
identity_gap = 0
GO
CREATE NONCLUSTERED INDEX i4
ON dbo.sybase_ase_fc_test(ascii(b), char_length(b), ltrim(d))
GO
CREATE NONCLUSTERED INDEX i3
ON dbo.sybase_ase_fc_test(sqrt(a), char_length(b), sqrt(c))
GO
CREATE NONCLUSTERED INDEX i2
ON dbo.sybase_ase_fc_test(ascii(b), char_length(b))
GO
CREATE NONCLUSTERED INDEX i1
ON dbo.sybase_ase_fc_test(char_length(b))
GO
Issue #11836 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
No fixed build |
No time estimate |
Have fixed the extraction and scripting to handle this in version 15. Need to test and decide if we need to back port the code to version 14.