[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

sql server: When to Create Indexes on Computed Columns

Posted on 2011-02-24
11
Medium Priority
?
447 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
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 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