Functional Indexes for Microsoft SQL Server 2000
This appendix discusses functional indexes for:
Microsoft SQL Server 2000.
DB2/UDB for Linux, UNIX, and Windows.
Informix.
Functional Indexes for Microsoft SQL Server 2000
The following example shows how the key fields are concatenated:
USE FS840U70 go SET IMPLICIT_TRANSACTIONS ON go IF EXISTS (SELECT 'X' FROM SYSOBJECTS WHERE TYPE = 'U' AND NAME = 'PS_QE_ALTER_NEW') DROP TABLE PS_QE_ALTER_NEW go CREATE TABLE PS_QE_ALTER_NEW (QE_ALTER_FLD1 NCHAR(1) NOT NULL, QE_ALTER_FLD2 NCHAR(1) NOT NULL, QE_ALTER_FLD3 NCHAR(1) NOT NULL, QE_ALTER_FLD4 NCHAR(10) NOT NULL, QE_ALTER_FLD5 NCHAR(20) NOT NULL, QE_ALTER_FLD6 NCHAR(11) NOT NULL, QE_ALTER_FLD7 NCHAR(1) NOT NULL, QE_ALTER_FLD8 NCHAR(1) NOT NULL, QEPC_AGE SMALLINT NOT NULL, QEPC_FILE_NUM DECIMAL(6, 2) NOT NULL, QE_BEGIN_DT PSDATE NULL, QE_BIRTHDATE PSDATE NULL, QE_31DIGFLD9 DECIMAL(31, 8) NOT NULL, QE_ANNUAL_PCT DECIMAL(3, 1) NOT NULL, QE_MS_DTTM1 PSDATETIME NULL, QE_MS_DTTM2 PSDATETIME NULL, QE_ALTER_TIMEA PSTIME NULL, QE_ALTER_TIMEB PSTIME NULL, MSSCONCATCOL AS QE_ALTER_FLD1 + QE_ALTER_FLD2 + QE_ALTER_FLD3 + QE_ALTER_FLD4 + QE_ALTER_FLD5 + QE_ALTER_FLD6 + QE_ALTER_FLD7 + QE_ALTER_FLD8 + CONVERT(NCHAR,QEPC_AGE) + CONVERT(NCHAR ,QEPC_FILE_NUM) + CONVERT(NCHAR(16),QE_BEGIN_DT, 109) + CONVERT(NCHAR(16),QE_BIRTHDATE, 109) + CONVERT(NCHAR,QE_31DIGFLD9) + CONVERT(NCHAR,QE_ANNUAL_PCT) + CONVERT(NCHAR(16),QE_MS_DTTM1, 109) + CONVERT(NCHAR(16),QE_MS_DTTM2, 109) + CONVERT(NCHAR(16) ,QE_ALTER_TIMEA, 109)) go COMMIT go SET ARITHABORT ON go CREATE UNIQUE CLUSTERED INDEX PS_QE_ALTER_NEW ON PS_QE_ALTER_NEW (MSSCONCATCOL) go CREATE NONCLUSTERED INDEX PSWQE_ALTER_NEW ON PS_QE_ALTER_NEW (QE_ALTER_FLD1, QE_ALTER_FLD2, QE_ALTER_FLD3, QE_ALTER_FLD4, QE_ALTER_FLD5, QE_ALTER_FLD6, QE_ALTER_FLD7, QE_ALTER_FLD8, QEPC_AGE, QEPC_FILE_NUM, QE_BEGIN_DT, QE_BIRTHDATE, QE_31DIGFLD9, QE_ANNUAL_PCT, QE_MS_DTTM1, QE_MS_DTTM2) go COMMIT Go
Functional Indexes for DB2/UDB for Linux, UNIX, and Windows
The following example shows how the key fields are concatenated:
CREATE TABLE PS_QE_ALTER_NEW (QE_ALTER_FLD1 VARGRAPHIC(1) NOT NULL, QE_ALTER_FLD2 VARGRAPHIC(1) NOT NULL, QE_ALTER_FLD3 VARGRAPHIC(1) NOT NULL, QE_ALTER_FLD4 VARGRAPHIC(10) NOT NULL, QE_ALTER_FLD5 VARGRAPHIC(20) NOT NULL, QE_ALTER_FLD6 VARGRAPHIC(11) NOT NULL, QE_ALTER_FLD7 VARGRAPHIC(1) NOT NULL, QE_ALTER_FLD8 VARGRAPHIC(1) NOT NULL, QEPC_AGE VARGRAPHIC(8) NOT NULL, QEPC_FILE_NUM DECIMAL(6, 2) NOT NULL, QE_BEGIN_DT DATE, QE_BIRTHDATE DATE, QE_31DIGFLD9 DECIMAL(31, 8) NOT NULL, QE_ANNUAL_PCT DECIMAL(3, 1) NOT NULL, QE_MS_DTTM1 TIMESTAMP, QE_MS_DTTM2 TIMESTAMP, QE_ALTER_TIMEA TIME, QE_ALTER_TIMEB TIME, DBXCONCATCOL GENERATED ALWAYS AS (QE_ALTER_FLD1 || QE_ALTER_FLD2 || QE_ALTER_FLD3 || QE_ALTER_FLD4 || QE_ALTER_FLD5 || QE_ALTER_FLD6 || QE_ALTER_FLD7 || QE_ALTER_FLD8 || QEPC_AGE || VARGRAPHIC(CHAR(QEPC_FILE_NUM)) || VALUE(VARGRAPHIC(CHAR(QE_BEGIN_DT)),'') || VALUE(VARGRAPHIC(CHAR(QE_BIRTHDATE)),'') || VARGRAPHIC(CHAR(QE_31DIGFLD9)) || VARGRAPHIC(CHAR(QE_ANNUAL_PCT)) || VALUE(VARGRAPHIC(CHAR(QE_MS_DTTM1)),'') || VALUE(VARGRAPHIC(CHAR(QE_MS_DTTM2)),'') || VALUE(VARGRAPHIC(CHAR(QE_ALTER_TIMEA)),''))) IN PTAPP INDEX IN PTAPPIDX NOT LOGGED INITIALLY; COMMIT; CREATE UNIQUE INDEX PS_QE_ALTER_NEW ON PS_QE_ALTER_NEW (DBXCONCATCOL); CREATE INDEX PSWQE_ALTER_NEW ON PS_QE_ALTER_NEW (QE_ALTER_FLD1, QE_ALTER_FLD2, QE_ALTER_FLD3, QE_ALTER_FLD4, QE_ALTER_FLD5, QE_ALTER_FLD6, QE_ALTER_FLD7, QE_ALTER_FLD8, QEPC_AGE, QEPC_FILE_NUM, QE_BEGIN_DT, QE_BIRTHDATE, QE_31DIGFLD9, QE_ANNUAL_PCT, QE_MS_DTTM1, QE_MS_DTTM2); COMMIT;
Functional Indexes for Informix
The following example shows how the key fields are concatenated:
BEGIN WORK; CREATE TABLE PS_QE_ALTER_NEW (QE_ALTER_FLD1 CHAR(1) NOT NULL, QE_ALTER_FLD2 CHAR(1) NOT NULL, QE_ALTER_FLD3 CHAR(1) NOT NULL, QE_ALTER_FLD4 CHAR(10) NOT NULL, QE_ALTER_FLD5 CHAR(20) NOT NULL, QE_ALTER_FLD6 CHAR(11) NOT NULL, QE_ALTER_FLD7 CHAR(1) NOT NULL, QE_ALTER_FLD8 CHAR(1) NOT NULL, QEPC_AGE SMALLINT NOT NULL, QEPC_FILE_NUM DECIMAL(6, 2) NOT NULL, QE_BEGIN_DT DATE, QE_BIRTHDATE DATE, QE_31DIGFLD9 DECIMAL(31, 8) NOT NULL, QE_ANNUAL_PCT DECIMAL(3, 1) NOT NULL, QE_MS_DTTM1 DATETIME YEAR TO FRACTION(3), QE_MS_DTTM2 DATETIME YEAR TO FRACTION(3), QE_ALTER_TIMEA DATETIME HOUR TO FRACTION(3), QE_ALTER_TIMEB DATETIME HOUR TO FRACTION(3)) IN PTAPP LOCK MODE ROW; REVOKE ALL ON PS_QE_ALTER_NEW FROM PUBLIC; COMMIT; BEGIN WORK; CREATE FUNCTION PS_QE_ALTER_NEW_SP1( QE_ALTER_FLD1 CHAR(1), QE_ALTER_FLD2 CHAR(1), QE_ALTER_FLD3 CHAR(1), QE_ALTER_FLD4 CHAR(10), QE_ALTER_FLD5 CHAR(20), QE_ALTER_FLD6 CHAR(11), QE_ALTER_FLD7 CHAR(1), QE_ALTER_FLD8 CHAR(1), QEPC_AGE SMALLINT, QEPC_FILE_NUM DECIMAL, QE_BEGIN_DT DATE, QE_BIRTHDATE DATE, QE_31DIGFLD9 DECIMAL, QE_ANNUAL_PCT DECIMAL, QE_MS_DTTM1 DATETIME YEAR TO FRACTION(3), QE_MS_DTTM2 DATETIME YEAR TO FRACTION(3)) RETURNING VARCHAR(166) WITH (NOT VARIANT); RETURN QE_ALTER_FLD1|| QE_ALTER_FLD2|| QE_ALTER_FLD3|| QE_ALTER_FLD4|| QE_ALTER_FLD5|| QE_ALTER_FLD6|| QE_ALTER_FLD7|| QE_ALTER_FLD8|| QEPC_AGE|| QEPC_FILE_NUM|| QE_BEGIN_DT|| QE_BIRTHDATE|| QE_31DIGFLD9|| QE_ANNUAL_PCT|| QE_MS_DTTM1|| QE_MS_DTTM2; END FUNCTION; CREATE FUNCTION PS_QE_ALTER_NEW_SP2( QE_ALTER_TIMEA DATETIME HOUR TO FRACTION(3)) RETURNING VARCHAR(15) WITH (NOT VARIANT); RETURN QE_ALTER_TIMEA; END FUNCTION; CREATE UNIQUE INDEX PS_QE_ALTER_NEW ON PS_QE_ALTER_NEW (PS_QE_ALTER_NEW_SP1( QE_ALTER_FLD1, QE_ALTER_FLD2, QE_ALTER_FLD3, QE_ALTER_FLD4, QE_ALTER_FLD5, QE_ALTER_FLD6, QE_ALTER_FLD7, QE_ALTER_FLD8, QEPC_AGE, QEPC_FILE_NUM, QE_BEGIN_DT, QE_BIRTHDATE, QE_31DIGFLD9, QE_ANNUAL_PCT, QE_MS_DTTM1, QE_MS_DTTM2), PS_QE_ALTER_NEW_SP2( QE_ALTER_TIMEA)) IN FS840IDX; CREATE INDEX PSWQE_ALTER_NEW ON PS_QE_ALTER_NEW (QE_ALTER_FLD1, QE_ALTER_FLD2, QE_ALTER_FLD3, QE_ALTER_FLD4, QE_ALTER_FLD5, QE_ALTER_FLD6, QE_ALTER_FLD7, QE_ALTER_FLD8, QEPC_AGE, QEPC_FILE_NUM, QE_BEGIN_DT, QE_BIRTHDATE, QE_31DIGFLD9, QE_ANNUAL_PCT, QE_MS_DTTM1, QE_MS_DTTM2) IN FS840IDX; COMMIT;