Is it possible to create index on computed column ??

Question

Is it possible to create index on computed column ??

Ajdba

Ten Centuries

Points: 1051
More actions
July 24, 2012 at 12:16 am

#264468

Hi,
Basically, I am getting just the year part of the datetime column and converted into varchar as CONVERT(VARCHAR(4),Year(I.DateOfIncidentDte)) as incident_year; but when I am running the query it seems like it is doing a table scan on "incident_year" column which does not have
index on it; the index is on the main table column "DateOfIncidentDte". I am not sure how to solve this problem, is there a way to create some index on just "incident_year" column or there is a way to force the query to use the "incident_year" in the scan.
I appreciate anybody's help very much!
Aj

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Yes, you can create an index on a computed column. In addition, if you are selecting a specific year you can also use the the following on the specific date column:

declare @RptDate datetime = '20120301' -- want report on all records for 2012

select *

from dbo.MyTable mt

where

mt.DateOfIncidentDte >= dateadd(yy,datediff(yy,0,@RptDate),0) and

mt.DateOfIncidentDte < dateadd(yy,datediff(yy,0,@RptDate) + 1,0);

Ajdba Ten Centuries Points: 1051 More actions · Answer 2

Thanks

Also, how can I create an index on a computed column such as " incident_year"

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Ajdba (7/24/2012)
Thanks
Also, how can I create an index on a computed column such as " incident_year"
Thanks

http://msdn.microsoft.com/en-us/library/ms189292.aspx

Don't forget what I said in your other thread about SARGability of your query. The way you built that query there are no index strategies that will help because you force a scan with your use of functions on that column in your where clause.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Ajdba (7/24/2012)
Thanks
Also, how can I create an index on a computed column such as " incident_year"
Thanks

Understand, you don't need to index the computed column if you follow the example I showed you above using the source column for your computed column.

Lokesh Vij SSChampion Points: 10836 More actions · Answer 5

My Friend, point here is that SQL Optimizer will not be able to use an index if the query is sargable. It will literally try to evaluate and match "DateOfIncidentDte" column, hence getting into scan.

Best way (as explained above also) to make query non-sargable is include your column inside a function. L

Like:

SELECT *

FROM table_name

WHERE YEAR(DateOfIncidentDte) = 2012

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

lokeshvij (7/24/2012)
My Friend, point here is that SQL Optimizer will not be able to use an index if the query is sargable. It will literally try to evaluate and match "DateOfIncidentDte" column, hence getting into scan.
Best way (as explained above also) to make query non-sargable is include your column inside a function. L
Like:
SELECT *
FROM table_name
WHERE YEAR(DateOfIncidentDte) = 2012

Lokesh thanks for the second but unfortunately your example above is another NON-sargable query. The year function means the query engine has to evaluate that for every single row.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Sean Lange (7/24/2012)
lokeshvij (7/24/2012)
My Friend, point here is that SQL Optimizer will not be able to use an index if the query is sargable. It will literally try to evaluate and match "DateOfIncidentDte" column, hence getting into scan.
Best way (as explained above also) to make query non-sargable is include your column inside a function. L
Like:
SELECT *
FROM table_name
WHERE YEAR(DateOfIncidentDte) = 2012
Lokesh thanks for the second but unfortunately your example above is another NON-sargable query. The year function means the query engine has to evaluate that for every single row.

Sean, that is exactly the point Lokesh was making in his (her?) post.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Lynn Pettis (7/24/2012)
Sean Lange (7/24/2012)
lokeshvij (7/24/2012)
My Friend, point here is that SQL Optimizer will not be able to use an index if the query is sargable. It will literally try to evaluate and match "DateOfIncidentDte" column, hence getting into scan.
Best way (as explained above also) to make query non-sargable is include your column inside a function. L
Like:
SELECT *
FROM table_name
WHERE YEAR(DateOfIncidentDte) = 2012
Lokesh thanks for the second but unfortunately your example above is another NON-sargable query. The year function means the query engine has to evaluate that for every single row.
Sean, that is exactly the point Lokesh was making in his (her?) post.

/facepalm

Yes you are correct. I totally misread that. :blush:

Sorry Lokesh, your comment was absolutely correct. Again I say thanks for the backup. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lokesh Vij SSChampion Points: 10836 More actions · Answer 9

[His Post :-)]

Another example to clarify:

SELECT *

FROM table_name

WHERE DateOfIncidentDte >= '01-01-2012'

AND DateOfIncidentDte < '01-01-2013'

Hope, you agree with me now - Sean 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

lokeshvij (7/24/2012)
[His Post :-)]
Another example to clarify:
SELECT *
FROM table_name
WHERE DateOfIncidentDte >= '01-01-2012'
AND DateOfIncidentDte < '01-01-2013'
Hope, you agree with me now - Sean 🙂

I absolutely agree with you...I just misread your previous post. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lokesh Vij SSChampion Points: 10836 More actions · Answer 11

No Worries. That happens - we are humans after all 🙂

And Thanks to you and Lynn for at-least criticizing my post. I am not very old to SSC, your support instills confidence in me. Hope I would make the best you of this forum to learn and share.

Cheers,

Lokesh 😀

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Eric M Russell SSC Guru Points: 125561 More actions · Answer 12

Ajdba (7/24/2012)
Thanks
Also, how can I create an index on a computed column such as " incident_year"
Thanks

You can index a computed column that has the PERSISTED keyword.

http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Ajdba Ten Centuries Points: 1051 More actions · Answer 13

Thanks a bunch!

Also, can you please point me to a good link about tuning a query or Stored Procs

Regards

Aj

Eric M Russell SSC Guru Points: 125561 More actions · Answer 14

Ajdba (7/26/2012)
Thanks a bunch!
Also, can you please point me to a good link about tuning a query or Stored Procs
Regards
Aj

So, if you're interested in query performance tuning in general, then first step is reading up on how SQL Server constructs Execution Plans; how to analyze and refactor SQL or table schemas to improve them. That's a broad topic, but Red Gate has an eBook that covers most of it well.

Complete Showplan Operators - Fabiano Amorim

http://www.simple-talk.com/simplepod/Complete_Showplan_Operators_Fabiano_Amorim%20%28without%20video%29.pdf

SQL Server Execution Plans - Grant Fritchey

http://assets.red-gate.com/community/books/assets/sql-server-execution-plans.pdf

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho