Solved

sql server: When to Create Indexes on Computed Columns

Posted on 2011-02-24
11
373 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help - 12 60
Query 14 54
email about the whoisactive result 7 29
SQL Pivot Rows To Columns 8 22
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

861 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now