Solved

sql server: When to Create Indexes on Computed Columns

Posted on 2011-02-24
11
412 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 334 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 334 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 166 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 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