Index built on computed columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Index built on computed columns

Author

Topic

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-05-09 : 22:12:12

From the Books online, we learned that when we create an index on a computed column, the computed column must be deterministic, i.e., it has the same output for the same input.

Therefore, we cannot create index on a computed column using getdate()

Can anyone explain why I failed to create index on these computed columns?

(convert(char(12),[date_column],3)) and
(str([numeric_column],10,2))

Many thanks,
delpiero

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-09 : 22:21:03

Some error message detail would be helpfull. But just to guess, I'd say you needed to keep reading below the deterministic requirement part where it talked about:
"The connection on which the index is created, and all connections attempting INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF"

Be One with the Optimizer
TG

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-09 : 22:33:32

The STR() function is not listed in BOL as being deterministic, so that alone could be the problem. Additionally, if your date_column defaults to getdate(), then it will most likely fail the deterministic test. The same applies to the numeric_column, if it defaults to an expression instead of a specific value.

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-05-10 : 12:21:30

The error message is:

Server: Msg 1933, Level 16, State 1, Line 1
Cannot create index because the key column 'usdcost2_CHAR' is
non-deterministic or imprecise.

where usdcost2_CHAR is a computed column like STR(numeric_column)

Similar error message for the other column, and the date column does not default to getdate() ...

delpiero

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-10 : 13:58:05

Hmmm ... Let me put on my detective hat and see if we can solve this riddle ....

Here's the error:

>>Server: Msg 1933, Level 16, State 1, Line 1
>>Cannot create index because the key column 'usdcost2_CHAR' is
non-deterministic or imprecise.

Here's what your code is doing:

>>where usdcost2_CHAR is a computed column like STR(numeric_column)

Here's what Rob Volk told you:

>>The STR() function is not listed in BOL as being deterministic

Are there any conclusions we can draw from this information as to why that error is occurring?

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-10 : 14:18:03

And, by the way -- why are you doing this? Do not try to store or calculate presentation information in your database layer! return the VALUE from the column and let the UI format the value any way it needs. Why would you add the overhead of computed columns for this, and then even worse add INDEXES on them no less !?

SQL's job is to return DATA in a raw format that is accurate and easily formatted and/or manipulated any way the presentation layer wants. Do not force formatting at the database level, which results in nothing but VARCHAR's ever being returned that must be immediately cast BACK to a date or numeric type to be sorted or used in any meaningful way by the application code.

There's a reason why T-SQL, ADO, ODBC and other database technologies have limited or non-existant support for formatting -- there are not supposed to do this.

It's amazing what people will do to make things harder on themselves ....

- Jeff

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-05-10 : 22:01:08

Thanks Jeff,

If STR is not deterministic, then do u have any idea about the CONVERT function?

delpiero

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-05-10 : 22:06:50

Jeff,

My reason for creating index on a computed column:

Suppose there is a string type column with a length of 9 characters. The leftmost 3 characters for this string represent some kind of serial number, e.g. an order number. In my application we often need to filter the results using these 3 characters, so very often we have a WHERE clause such as WHERE left(string_column,3) = 'XYZ'

I tested that if I just created an index on "string_column", the index is not being used during this WHERE clause. So what I did was to create a computed column "order_number" AS left(string_column,3), and then create an index on it. We did need to change the WHERE cluase to WHERE order_number='XYZ'. In this case the index can be used and the query performance improved.

It may not be a good method, and I avoided creating too many computed columns .....

Anyway, thanks very much for your advice.

delpiero

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-11 : 06:58:31

Instead of using WHERE Left(string_column,3)='XYZ', try WHERE string_column LIKE 'XYZ%'. It is logically equivalent, and the query optimizer can use the index because the column is being referenced, not a function. Whenever you have functions on the left-hand side of the equals sign it prevents the optimizer from using indexes on columns contained within the function.

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-05-12 : 04:23:44

Thanks for the reply.

BTW, here is what I found in Books Online: (Topic SET Options That Affect Results)
Any functions whose results would be affected by changes to these options (meaning SET options) are classified as nondeterministic and cannot be used in views or computed columns that are indexed.

Hence the CONVERT function in my case would be nondeterministic because the date format would be affected by option SET DATETIME.

delpiero

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-05-12 : 04:43:52

However, I did some tests on the LIKE clause and found that it seems the query analyzer does not use the index in the LIKE case.

Support I have an index built on the string_column.
Only an exact match in the WHERE clause uses the index: WHERE string_column='ABCDEFGHI'

when I issue WHERE string_column like 'XYZ%', a full scan is performed instead.

delpiero

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-05-12 : 04:56:13

You must have a dud copy of sql server.. What version are using? SQL Server 4.2?


use northwind
go
Select * from customers
where customerid like 'ar%'

DavidM

A front-end is something that tries to violate a back-end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources