Filtered Indexes 2

Question

Filtered Indexes 2

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
June 4, 2015 at 10:52 pm

#318750

Comments posted to this topic are about the item Filtered Indexes 2
June 5, 2015 at 4:57 am

This was removed by the editor as SPAM

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Good question. I had to think for a moment about the implicit conversion that could possibly happen depending on how the columns were defined (gender and username). But that didn't really come in to play since the conversions would actually be on the right.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Easy one, thanks.

Have a nice weekend all!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Bhushan Kulkarni SSCrazy Points: 2829 More actions · Answer 3

Bhushan Kulkarni

SSCrazy

Points: 2829

June 5, 2015 at 12:45 am

#1803229

Easy One. Solved similar one recently 🙂

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

I had to think about this one as well. Good way to get the brain moving in the morning. Thanks and have a great weekend.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

timwell SSCertifiable Points: 5315 More actions · Answer 5

Of course we have to assume that neither username nor gender are "a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column" as stated in the reference.

webrunner SSC-Dedicated Points: 31863 More actions · Answer 6

Thanks for the question! I don't recall where I learned this (prob somewhere on SSC), but wherever I did, it helped me get the right answer. 🙂

-webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 7

Filtered indexes are defined on one table and only support simple comparison operators. If you need a filter expression that references multiple tables or has complex logic, you should create a view. This is one the limitations in sql 2014 out of many. May be sql 2016 can overcome these restrictions.

Thanks.

TomThomson SSC Guru Points: 104773 More actions · Answer 8

SQL-DBA-01 (6/5/2015)
Filtered indexes are defined on one table and only support simple comparison operators. If you need a filter expression that references multiple tables or has complex logic, you should create a view. This is one the limitations in sql 2014 out of many. May be sql 2016 can overcome these restrictions.

The multiple tables part of that is true of all indexes, not just filtered ones. I hope that part of it won't be fixed in SQL 2016, since it would be positively bizarre for an index on a table to refer to columns in that table. As for complex logic, it would be reasonable to allow the same logic in a filter as is allowed in a check constraint (ie any logical expression that doesn't reference another table or another row in the same table or anything whose type is an alias type), so it would not be bizarre to fix just that part of the problem. Allowing anything more complex than allowed for a check constraint would be pretty bizarre, I think.

Tom

Arno Kwetters Hall of Fame Points: 3727 More actions · Answer 9

I had to look very carefully.

At first sight all options were right.

But 3 had to be right ...

Thanks

Hany Helmy SSChampion Points: 13921 More actions · Answer 10

Another good practice on Filtered Index, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 11

timwell (6/5/2015)
Of course we have to assume that neither username nor gender are "a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column" as stated in the reference.

+1

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 12

Hany Helmy (6/8/2015)
Another good practice on Filtered Index, thanx.

Agreed

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 13

Nice question, thanks.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537