![]() *see the srction 'Query predicate is not SARGable' I've put a link to an article by Gail Shaw (aka GilaMonster on SSC), below, which covers this though there are many more examples on the web. I believe that some of the results you get are not necessarily valid as you are using functions on the column-side of the predicates and thus making them non-SARGable. I am also interested in how you get the result like the neat two rows table you have using SQL trace, how do you use it? SQL_Latin1_General_CP1_CI_AS is there for back compatibility. I searched on line a lot about the topic, it seems it is better to use windows collation Latin1_General_CI_AS. But ever since we had a group of consultant developers and dba who worked in our org many years ago, they don't use the default but setup to use Latin1_General_CI_AS ,that becomes our standard when we setup our new servers in our organization. I believe SQL_Latin1_General_CP1_CI_AS is the default of U.S. ![]() Select * from test where (col2 = 'npE') and (col2 COLLATE SQL_Latin1_General_CP1_CS_AS='npE')īy that way, it finds 51 rows from the index and then filters 7 of them to the result.Ī super clever optimization engine might be able to do this automatically. Select * from test where col2 COLLATE SQL_Latin1_General_CP1_CS_AS='npE' I believe that the case sensitive query from case insensitive index can be optimized by changing: Wednesday, J10:12:13 PM - Miguel Chillitupa Here is a good link you can use for creating trace files. will contain German and Greek words but when I use collate Greek does not support German and when I use German collate does not support Greek Can you help me Here are examples of each query.įriday, Febru1:40:29 AM - kartis panagiotis For the SQL_Latin1_General_CP1_CS_AS column we have two options, either we can use the T-SQL UPPER() function or specify the collation in the WHERE clause. In this scenario, for the query on the column defined with SQL_Latin1_General_CP1_CI_AS we can simply use the equality operator in our WHERE clause. Let's first take a look at the results when performing a case insensitive search of each of our columns. VALUES )ĮND Test Scenario 1 - Case insensitive query (5) COLLATE SQL_Latin1_General_CP1_CS_AS,ĬREATE NONCLUSTERED INDEX ON. ![]() (5) COLLATE SQL_Latin1_General_CP1_CI_AS,
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |