SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using Indexed Computed Columns to Improve Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/16/2007 :  08:51:51  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 02/02/2007 :  02:27:14  Show Profile  Visit henrik staun poulsen's Homepage  Reply with Quote
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 - 06/19/2007 :  10:23:22  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000