FILTER

  • Lokesh Vij

    SSChampion

    Points: 10836

    Comments posted to this topic are about the item FILTER

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • demonfox

    SSCertifiable

    Points: 6289

    my query unexpectedly took 15 minutes to run and I cancelled it after that ..

    then I came to ssc and an easy one to start the tuesday ...

    felt relieved ...time to stress on another pawn ..

    thanks for the question..

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Easy one, thanks.

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

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Thanks for the question Lokesh-ji, (so simple and yet so much to consider).

    (actually i did not focus on the collation but saw the count(*) and then I chose 6 :hehe: , not a right way but got by 1 point 😉 )

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Lokesh Vij

    SSChampion

    Points: 10836

    Raghavendra Mudugal (9/17/2012)


    Thanks for the question Lokesh-ji, (so simple and yet so much to consider). )

    Glad to know that Raghu..sometimes we are so engrossed in the perplexities of complex issues that we forget the basics 😉 It was just a small effort to make you all revise:-)

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Sorry for taking this thread out of the scope... (this is nothing to do with the QToD's objective, but was playing around and kind of stuck with this...:Whistling: )

    Considering the same data given, can anyone advise what is happening on #3 ?

    It is giving me this result...

    COL2 (No column name)

    AB 2

    xy 1

    But I was expecting

    COL2 (No column name)

    AB 2

    aB 1

    xy 1

    -- #1

    SELECT COL2, COUNT(COL2)

    FROM test

    WHERE col2 IN ( 'AB', 'aB', 'xy' )

    GROUP BY COL2

    -- #2

    SELECT COL2, COUNT(COL2 COLLATE SQL_Latin1_General_CP1_CS_AS)

    FROM test

    WHERE col2 IN ( 'AB', 'aB', 'xy' )

    GROUP BY COL2

    -- #3

    SELECT COL2, COUNT(COL2)

    FROM test

    WHERE col2 COLLATE SQL_Latin1_General_CP1_CS_AS IN ( 'AB', 'aB', 'xy' )

    GROUP BY COL2

    -- #4

    SELECT COL2, COUNT(COL2 COLLATE SQL_Latin1_General_CP1_CS_AS)

    FROM test

    WHERE col2 COLLATE SQL_Latin1_General_CP1_CS_AS IN ( 'AB', 'aB', 'xy' )

    GROUP BY COL2

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • philip.cullingworth

    SSCrazy

    Points: 2150

    Raghu,

    The results you are getting are due to you only specifying the collation on the where clause.

    The query is selecting rows 1, 3 and 6 since they are the rows that match when the case sensitive collation is specified.

    However the Group By clause does not specifiy Case Sensitive and so the results are grouped case insensitive, combining the AB and aB.

    You would need the code below to get your expected result (assuming the count next to AB was meant to be 1)

    SELECT COL2 COLLATE SQL_Latin1_General_CP1_CS_AS, COUNT(COL2)

    FROM testQOTD

    WHERE col2 COLLATE SQL_Latin1_General_CP1_CS_AS IN ( 'AB', 'aB', 'xy' )

    GROUP BY COL2 COLLATE SQL_Latin1_General_CP1_CS_AS

  • demonfox

    SSCertifiable

    Points: 6289

    I think it should work ...

    SELECT COL2 COLLATE SQL_Latin1_General_CP1_CS_AS, COUNT(COL2 COLLATE SQL_Latin1_General_CP1_CS_AS)

    FROM #test

    WHERE col2 IN ( 'AB', 'aB', 'xy' )

    GROUP BY COL2 COLLATE SQL_Latin1_General_CP1_CS_AS

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • demonfox

    SSCertifiable

    Points: 6289

    philip.cullingworth (9/18/2012)


    Raghu,

    The results you are getting are due to you only specifying the collation on the where clause.

    The query is selecting rows 1, 3 and 6 since they are the rows that match when the case sensitive collation is specified.

    However the Group By clause does not specifiy Case Sensitive and so the results are grouped case insensitive, combining the AB and aB.

    You would need the code below to get your expected result (assuming the count next to AB was meant to be 1)

    SELECT COL2 COLLATE SQL_Latin1_General_CP1_CS_AS, COUNT(COL2)

    FROM testQOTD

    WHERE col2 COLLATE SQL_Latin1_General_CP1_CS_AS IN ( 'AB', 'aB', 'xy' )

    GROUP BY COL2 COLLATE SQL_Latin1_General_CP1_CS_AS

    that is what I meant , and yes there is no need to add collation in the count ....

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Skanker

    Hall of Fame

    Points: 3059

    Nice question to start the day. Thanks. 😀

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Good question; the explanation could have been better.

    "To make this query case sensitive we need to either set the collation level to Case Sensitive or use 'COLLATE SQL_Latin1_General_CP1_CS_AS' in the select query with col2"

    To me, the above sentence reads as if there are two methods. The second one is to force a different collation in the query (as demonstrated in the profivded code fragment); the first one is to, somehow, "set the collation level". And that, again: to me, sounds as if some SET statement would be used.

    This may all be due to my English reading skills (I'm not a native English speaker), but in any case, I wanted to add a comment to clarify this in case ohers have the same misunderstanding,

    First, there is no SET statement to control the default collation used in comparisons. The collation used in a comparison will always be the collation of the column used, unless an explicit COLLATE clause is used to override that.

    The only way to make this query use a case sensitive collation without specifying it in the table is to change the column's collation. This can be done by adding a COLLATE clause to the CREATE TABLE (or ALTER TABLE) statement. Or it can be done by changing the default collation of the database (using the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement) and not specifying a collation when creating the table. Finally, the last way to get case sensitive comparisons by default is to specify a case sensitive collation when installing the SQL Server instance, then using the defaults when creating database and table. Changing the default collation for an already installed instance is very hard; it requires rebuilding the master.

    The link in the explanation does not work, unfortunately. I think the author intended to post two links, but accidentally mashed them together. Here are the links I think he tried to supply:

    * On MSDN, the official documentation on COLLATE: http://msdn.microsoft.com/en-us/library/ms184391.aspx

    * A recent article on SQLServerCentral about case sensitive searches: http://www.sqlservercentral.com/blogs/nycnet/2012/01/19/case-sensitive-searches-in-t-sql/[/url]


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Awesome Philip, thank you 🙂 for the details.

    philip.cullingworth (9/18/2012)


    Raghu,

    The results you are getting are due to you only specifying the collation on the where clause.

    The query is selecting rows 1, 3 and 6 since they are the rows that match when the case sensitive collation is specified.

    However the Group By clause does not specifiy Case Sensitive and so the results are grouped case insensitive, combining the AB and aB.

    You would need the code below to get your expected result (assuming the count next to AB was meant to be 1)

    SELECT COL2 COLLATE SQL_Latin1_General_CP1_CS_AS, COUNT(COL2)

    FROM testQOTD

    WHERE col2 COLLATE SQL_Latin1_General_CP1_CS_AS IN ( 'AB', 'aB', 'xy' )

    GROUP BY COL2 COLLATE SQL_Latin1_General_CP1_CS_AS

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • This was removed by the editor as SPAM

  • Divine Flame

    SSCoach

    Points: 15941

    Nice question! Thanks for reminding the basics.


    Sujeet Singh

  • Jinxseee

    SSC-Addicted

    Points: 408

    Thanks, easy one for a slow Tuesday!:-D

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

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