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.
| Author |
Topic |
|
X002548
Not Just a Number
15586 Posts |
 Posted - 2010-08-12 : 10:45:52
|
| Yes, I know I can test it out..But a). Can you do that, and 2). If you can, can you get an index seek operation?For example Last_nameI want everyone that starts with the letter 'D'WHERE SUBSTRING(Last_name,1,1) = 'D' is non-saragable and would incur a scan...any thoughts?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-12 : 11:39:35
|
| Yes you can and yes it could seek (assuming a sargable clause). I think the column needs to be persisted in order to create an index on it.Use LIKE 'D%' instead of the SUBSTRING fucntion and you should/might get a seek depending on your data.There are also Filtered Indexes, which might be better than creating a derived column. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-08-12 : 14:44:43
|
Datepart or datediff of a column. You can index a computed column provided the column satisfies a few conditions. From BoL:quote:
Indexes can be created on computed columns. In addition, computed columns can have the property PERSISTED. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query. To index a computed column, the computed column must deterministic and precise. However, using the PERSISTED property expands the type of indexable computed columns to include: Computed columns based on Transact-SQL and CLR functions and CLR user-defined type methods that are marked deterministic by the user.Computed columns based on expressions that are deterministic as defined by the Database Engine but imprecise.Persisted computed columns require SET options to be set as shown in the section "Required SET Options for Indexed Views".
--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Index on a derived Column