Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using Indexed Computed Columns to Improve Performance

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-16 : 08:51:51
I recently read a blog post on doing
case-insensitive text searches
on SQL Server 2005. The post said that
an index on a computed column might be used even if the computed column itself wasn't
used in the WHERE clause. I was curious to test that and see how far I might take it. Years ago I worked on a case-sensitive application and I vividly
remember all the headaches that caused me. I was
also curious to see if I could use that for datetime columns to strip off the time
portion and easily do a "date-only" search.

Article Link.

henrik staun poulsen
Starting Member

4 Posts

Posted - 2007-02-02 : 02:27:14
Thank you very much for an interesting article.

Have you tried DATEADD(dd, DATEDIFF(dd, 0, ModifiedDateOnly ), 0) for calculating ModifiedDateOnly ?
This way you're only doing integer arithmatic, and that may be somewhat faster?

We're have a lot of searches on the date part of datetime fields so your observations will come in handly.

Best regards,
Henrik Staun Poulsen
Go to Top of Page

Scott Pletcher
Starting Member

2 Posts

Posted - 2007-06-19 : 10:23:22
Very interesting article. I hadn't thought about using computed index as related to case sensitive/upper/lower.

As far as a datetime search, though, I don't think it warrants the overhead of a separate index. Just index the original column, then use a range search, something like this:

-- to find all dates matching 'Jun 19, 2007'

-- (full) DATETIME only
WHERE dateColumn BETWEEN '20070619' AND '20070619 23:59:59.997'

-- either (full) or SMALLDATETIME
WHERE dateColumn >= '20070619' AND dateColumn < '20070620'

-- SMALLDATETIME only
WHERE dateColumn BETWEEN '20070619' AND '20070619 23:59'
-- this one seems risky to me, since if the column is changed to
-- (full) DATETIME, that query could miss some values
Go to Top of Page
   

- Advertisement -