?
Solved

sql server: When to Create Indexes on Computed Columns

Posted on 2011-02-24
11
Medium Priority
?
437 Views
Last Modified: 2012-08-14
Hi experts: about When to Create Indexes on Computed Columns
You can create indexes on computed columns when:
     The expression is deterministic and precise

which means?
"The expression is deterministic and precise"
I understand the concept, but do not understand why I should I consider when Create Indexes on Computed Columns

0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34976489
You need to read this article from SQL Server BOL:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/en-us/library/ms189292.aspx

Strictly speaking you can always create an index on a computed column even when it is not deterministic it just has to be Persisted in that case.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34976498
>>Strictly speaking you can always create an index on a computed column even when it is not deterministic it just has to be Persisted in that case. <<
On second thoughts, this is not correct.  The computed column has to be deterministic, it just does not have to be precise.
0
 

Author Comment

by:enrique_aeo
ID: 34976517
I read the url that you sent me, but still do not understand
 A non-deterministic function to return May Sometimes Different result, Given the Same input

 I do not understand because I can not Create Indexes on Computed Columns
 when using this
 select CAST (GETDATE () AS VARCHAR (20));
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34976642
That is because GETDATE() is not deterministic.
0
 

Author Comment

by:enrique_aeo
ID: 34976720
an example would help me a lot transact sql
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1336 total points
ID: 34976808
Do this:

SELECT GETDATE()
SELECT GETDATE()

Are the values returned the same? Answer no.  Therefore it is not deterministic.  Now do the same with something like this:
SELECT ISNUMERIC('123ABC')
SELECT ISNUMERIC('123ABC')

Incidentally, the word "deterministic" is very similar to the word in Spanish (obviously a Latin root) and has the same meaning.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1336 total points
ID: 34976824
0
 

Author Comment

by:enrique_aeo
ID: 34976858
Dear acperkins:
 I understand the concept but I do not understand because I can not Create Indexes on Computed Columns
 the transact sql shows me an error?
0
 
LVL 6

Assisted Solution

by:Rajesh_mj
Rajesh_mj earned 664 total points
ID: 34977021
We can create index on computed columns. Please check below link:
http://blog.sqlauthority.com/2010/08/22/sql-server-computed-columns-index-and-performance/

However, we can not create index on non-deterministic computed columns. As per my understanding reason is as follows:
Computed columns are actually not physically created on the related database table unless they are defined as "Persisted" computed columns. You can think of computed columns as virtual columns. They are not physically stored in the related sql database table.
They are calculated every time they are referenced in a sql statement.

I got it from http://www.kodyaz.com/articles/sql-server-computed-column-calculated-column-sample.aspx


0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34979170
select CAST (GETDATE () AS VARCHAR (20));

are you actually using this as a column?

what is the point of having NOW as a column and why would you index it?

do you really want to use a deault value of getdate() for a column and index that?
0
 

Author Comment

by:enrique_aeo
ID: 34979408
hello, do not use it. Just wanted an example to see what happens if I use
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question