Thursday, March 29, 2012

alternate to formula

I have a table where one of the field is having formula, where UDF is used.
Is there any way by which formula can be removed and any other method is
used like trigger,
Because we can not have index on a formula based fieldCOMPUTED column? Can you show us the source?
"Vikram" <aa@.aa> wrote in message
news:eq0vE6aeGHA.3572@.TK2MSFTNGP03.phx.gbl...
>I have a table where one of the field is having formula, where UDF is used.
> Is there any way by which formula can be removed and any other method is
> used like trigger,
> Because we can not have index on a formula based field
>|||I think you cannot have an index on computed field either.
Though in SQL Server 2005 you can set the computed column as persisited and
create an index on it.|||Why not?
create table test (c1 int not null,c2 as c1*10)
create index ind_comp on test(c2)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:45CD1DD1-6AF4-4872-AD5D-28450AA30E23@.microsoft.com...
>I think you cannot have an index on computed field either.
> Though in SQL Server 2005 you can set the computed column as persisited
> and
> create an index on it.|||Oops.. sorry... you are right.. tea time for me :)
I it with foreign key constraints|||In fact, computed columns may be indexed if certain conditions are met:
http://msdn2.microsoft.com/en-us/library/ms189292.aspx
In SQL 2000 a computed column is not persisted until it's used in an index,
while SQL 2005 has this new option that you've mentioned.
ML
http://milambda.blogspot.com/|||> I it with foreign key constraints
You can create an index on the column that has FK
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BB66B25E-F4A8-4163-B5C5-8C20A8734C48@.microsoft.com...
> Oops.. sorry... you are right.. tea time for me :)
> I it with foreign key constraints|||No.. i meant foreign key on a computed column.|||Yes ,it is true
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:0A422A1B-2100-4D50-A610-AAD305AFDD3B@.microsoft.com...
> No.. i meant foreign key on a computed column.
>sql

No comments:

Post a Comment