Post reply

Reaping the benefits of the Window functions in T-SQL

  • November 2, 2018 at 10:32 am

    #2012228

    Eirikur Eiriksson - Friday, November 2, 2018 8:08 AM

    Lynn Pettis - Friday, November 2, 2018 7:52 AM

    Maybe I missed it in the article, I have read through it twice, on the CSV code why are you using the Latin1_General_BIN collation?

    The reason is that it using a binary collations is more efficient than using a language specific collations.
    😎

    Out of curiosity have you tested both BIN and BIN2?

  • November 5, 2018 at 3:36 am

    #2012367

    Lynn Pettis - Friday, November 2, 2018 10:32 AM

    Eirikur Eiriksson - Friday, November 2, 2018 8:08 AM

    Lynn Pettis - Friday, November 2, 2018 7:52 AM

    Maybe I missed it in the article, I have read through it twice, on the CSV code why are you using the Latin1_General_BIN collation?

    The reason is that it using a binary collations is more efficient than using a language specific collations.
    😎

    Out of curiosity have you tested both BIN and BIN2?

    Quite certain I did, cannot remember the details, will look into my notes and check.
    😎

  • November 5, 2018 at 5:34 am

    #2012372

    Lynn Pettis - Friday, November 2, 2018 10:32 AM

    Eirikur Eiriksson - Friday, November 2, 2018 8:08 AM

    Lynn Pettis - Friday, November 2, 2018 7:52 AM

    Maybe I missed it in the article, I have read through it twice, on the CSV code why are you using the Latin1_General_BIN collation?

    The reason is that it using a binary collations is more efficient than using a language specific collations.
    😎

    Out of curiosity have you tested both BIN and BIN2?

    Haven't looked.  What's the difference between the two?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 5, 2018 at 7:53 am

    #2012395

    Jeff Moden - Monday, November 5, 2018 5:34 AM

    Lynn Pettis - Friday, November 2, 2018 10:32 AM

    Eirikur Eiriksson - Friday, November 2, 2018 8:08 AM

    Lynn Pettis - Friday, November 2, 2018 7:52 AM

    Maybe I missed it in the article, I have read through it twice, on the CSV code why are you using the Latin1_General_BIN collation?

    The reason is that it using a binary collations is more efficient than using a language specific collations.
    😎

    Out of curiosity have you tested both BIN and BIN2?

    Haven't looked.  What's the difference between the two?

    Don't have time to search the Microsoft documentation at the moment as I am getting ready to take my dad to an outpatient procedure but one starts with code-point then binary (BIN I believe) the other is pure binary (BIN2).  You will need to check that out if I don't get the chance when I finally get to work.

  • November 5, 2018 at 8:54 am

    #2012405

    I thought you knew since you brought it up.

    Did some research and found out the BIN2 stuff uses "Code Point Comparison Semantics" and the plain BIN stuff is backwards compatible brute force binary.  The former appears to be necessary for some characters in Unicode.  It also took me a bit to figure out that a "Code Point" is the byte(s) behind the scenes to represent a character.

    Hopefully anyone that needs the BIN2 stuff will do their own performance testing because it's not likely that I'll ever need that for splitting delimited strings.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 5, 2018 at 9:34 am

    #2012416

    The only noticeable difference between BIN and BIN2 is when you sort them.

  • November 5, 2018 at 10:29 am

    #2012425

    Jeff Moden - Monday, November 5, 2018 8:54 AM

    I thought you knew since you brought it up.

    Did some research and found out the BIN2 stuff uses "Code Point Comparison Semantics" and the plain BIN stuff is backwards compatible brute force binary.  The former appears to be necessary for some characters in Unicode.  It also took me a bit to figure out that a "Code Point" is the byte(s) behind the scenes to represent a character.

    Hopefully anyone that needs the BIN2 stuff will do their own performance testing because it's not likely that I'll ever need that for splitting delimited strings.

    I used to know but when you aren't using the information you tend to forget.  That is why I find the Microsoft documentation to be my friend when I have to refresh my memory.  I just didn't have time this morning to dig in to answer your question.

  • November 5, 2018 at 10:48 am

    #2012428

    Lynn Pettis - Monday, November 5, 2018 10:29 AM

    Jeff Moden - Monday, November 5, 2018 8:54 AM

    I thought you knew since you brought it up.

    Did some research and found out the BIN2 stuff uses "Code Point Comparison Semantics" and the plain BIN stuff is backwards compatible brute force binary.  The former appears to be necessary for some characters in Unicode.  It also took me a bit to figure out that a "Code Point" is the byte(s) behind the scenes to represent a character.

    Hopefully anyone that needs the BIN2 stuff will do their own performance testing because it's not likely that I'll ever need that for splitting delimited strings.

    I used to know but when you aren't using the information you tend to forget.  That is why I find the Microsoft documentation to be my friend when I have to refresh my memory.  I just didn't have time this morning to dig in to answer your question.

    Agreed on documentation being a friend.  MS didn't have much to say about what a "Code Point" was, though.  I got "lucky" with a WIKI-Pedia article on the subject.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 26, 2018 at 6:13 pm

    #2017381

    There is a little problem with "quoted split" function (the one I spotted so far) - it does not handle "spaces only" strings well. 
    It returns NULLs instead. Even even empty strings are in quotes

    _____________
    Code for TallyGenerator

  • December 27, 2018 at 3:01 am

    #2017391

    Sergiy - Wednesday, December 26, 2018 6:13 PM

    There is a little problem with "quoted split" function (the one I spotted so far) - it does not handle "spaces only" strings well. 
    It returns NULLs instead. Even even empty strings are in quotes

    Thanks for the heads-up, hadn't noticed this before.
    😎

  • December 28, 2018 at 4:23 pm

    #2017570

    Another case where the function does not do what I'd expect it to do:

    DECLARE @Text NVARCHAR(max), @Delimiter NVARCHAR (10), @Quote NCHAR (1)
    DECLARE @rc INT

    SET @Delimiter = ','
    SET @Quote = '"'
    set @Text = '"Row
    Num","Splitter
    Name","Number
    Of
    Rows","Number
    Of
    Elements","Min
    Element
    Length","Max
    Element
    Length","Duration","Min
    Length","Avg
    Length","Max
    Length"'
    SELECT *
    FROM dbo.SplitQuoted_EEriksson (@Text, @Delimiter, @Quote) C2

    Well, it was may be not intended for that, but still - line feeds within quoted fields are quite possible in Excel exports.

    _____________
    Code for TallyGenerator

  • December 29, 2018 at 8:33 am

    #2017593

    Sergiy - Friday, December 28, 2018 4:23 PM

    Another case where the function does not do what I'd expect it to do:

    DECLARE @Text NVARCHAR(max), @Delimiter NVARCHAR (10), @Quote NCHAR (1)
    DECLARE @rc INT

    SET @Delimiter = ','
    SET @Quote = '"'
    set @Text = '"Row
    Num","Splitter
    Name","Number
    Of
    Rows","Number
    Of
    Elements","Min
    Element
    Length","Max
    Element
    Length","Duration","Min
    Length","Avg
    Length","Max
    Length"'
    SELECT *
    FROM dbo.SplitQuoted_EEriksson (@Text, @Delimiter, @Quote) C2

    Well, it was may be not intended for that, but still - line feeds within quoted fields are quite possible in Excel exports.

    Thanks Sergiy, will look into this when I have the time.
    😎
    Can you please post the code for the function dbo.SplitQuoted_EEriksson you are using?

  • May 30, 2019 at 8:04 pm

    #3648726

    The function does work with embedded line feeds but the problem is with the first character being a quote (") and also tossed in the empty quoted string for good measure.

    If you just change the @pString to start with a quoted field, the first field contains the quote

    SELECT @pString = '"100123","Blues","West Point, Mississippi","Burnett,'

    + '""Howlin'' Wolf"" Arthur Chester",1910-06-10,empty quoted spaces next," ",previous is null,but with a value," , ","works

    even with crLf"';

    Results

    1 "100123

    2 Blues

    3 West Point, Mississippi

    4 Burnett,"Howlin' Wolf" Arthur Chester

    5 1910-06-10

    6 empty quoted spaces next

    7 NULL

    8 previous is null

    9 but with a value

    10 ,

    11 works

    even with crLf

  • March 30, 2020 at 1:22 pm

    #3738937

    Just noticed this when I was putting this on a new instance, but in the .sql file the WITH starts with a leading ;, which actually breaks the DDL statement. Suppose that's another reason to educate people that ; isn't a "beginningator". 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • March 30, 2020 at 6:21 pm

    #3739067

    Thom A wrote:

    Just noticed this when I was putting this on a new instance, but in the .sql file the WITH starts with a leading ;, which actually breaks the DDL statement. Suppose that's another reason to educate people that ; isn't a "beginningator". 🙂

    Here we begin(ninator) again 😉

    😎

Viewing 15 posts - 31 through 45 (of 57 total)

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