Tuesday, February 14, 2012

Create View with associated Index

I have inherited the following code from someone before me:

create view [ABC ] as

select T.*

,rtrim( cast([COL_1] as char( 2))

+ cast ([COL_3] as char( 3)) ) [ROLL01]

from [ABC_TABLE] T;

ROLL01 is primary key index; however as primary key it is made up of COL_1, COL_2, COL_3

I take it the index view is a separate object even though the same name is used as the primary key index? Because I would think this would greatly affect performance. Of course I do not like this anyway because of the documentation confusion.

In this view the [ROLL01] is an alias column name for the column that is being constructed with

,rtrim( cast([COL_1] as char( 2))

+ cast ([COL_3] as char( 3)) )

and has no implications on the primary key defined on the table.

I believe the person that did this was just trying to see the "composite key" as one single item.

The view is listing the contents of ABC_TABLE, with this additional column appended to the end of each row.

No comments:

Post a Comment