SQL Server Performance

SQL Server Transact-SQL WHERE tip

Discussion in 'Please Tell Us What You Think' started by MartinSmithh, May 17, 2008.

  1. MartinSmithh New Member

    Refers to http://www.sql-server-performance.com/tips/t_sql_where_p1.aspx
    The advice given here will return incorrect results


    SELECT column_name FROM table_name
    WHERE column_name = 'NAME' or column_name = 'name'

    obviously won't catch the case column_name = 'Name'

    it also would have been good to have some discussion as to whether the UPPER method is any more or less efficient than an inline COLLATE clause
  2. satya Moderator

    Check andsee whether on the instance where you have tested this script is a 'case-sensitive' installation?
  3. FrankKalis Moderator

    [quote user="MartinSmithh"]
    it also would have been good to have some discussion as to whether the UPPER method is any more or less efficient than an inline COLLATE clause
    [/quote]
    Well then... We always appreciate someone new stepping in and doing some research to extend and enrich the content here. [:)]
  4. MartinSmithh New Member

    @Satya - Have you read the tip I'm referring to?
    @Frank - Point taken. I suspect that both UPPER and COLLATE will prevent the index being used but that maybe the COLLATE clause will prevent unnecessary strings being created for comparison purposes. If I get a chance I will do some testing!
  5. FrankKalis Moderator

    [quote user="MartinSmithh"]
    @Frank - Point taken. I suspect that both UPPER and COLLATE will prevent the index being used but that maybe the COLLATE clause will prevent unnecessary strings being created for comparison purposes. If I get a chance I will do some testing!
    [/quote]
    Much appreciated. [:)]
  6. MartinSmithh New Member

    In the mean time I've found a good tip for the reverse problem (case sensitive comparisons on a case insensitive column) that ensure an index seek is used

    http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

    SELECT column_name
    FROM table_name
    WHERE column_name = 'Name' COLLATE SQL_Latin1_General_CP1_CS_AS
    AND column_name = 'Name'

Share This Page