Solved

sql server: When to Create Indexes on Computed Columns

Posted on 2011-02-24
11
361 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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

18 Experts available now in Live!

Get 1:1 Help Now