Article: Using Indexed Computed Columns to Improve Performance

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources