Code Test from IE

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715841

    Win 10, IE v 11.576

    CREATE OR ALTER PROCEDURE RunTimeTests

    AS

    BEGIN

    SELECT

    Taskid,

    MIN(StartTime),

    MAX(EndTime),

    DATEDIFF(MINUTE, MIN(StartTime), MAX(EndTime)),

    ReferenceDate = '2017-02-23 10:11:47.413'

    FROM TimeTests

    GROUP BY Taskid;

    END;

    GO

  • Jeff Moden

    SSC Guru

    Points: 994661

    Is that cut and paste from SSMS or from Notepad like I have to preserve both the leading spaces and the proper line spaceing?  Also, I'm see it as if it were double spaced and the colors are nothing close to what are in SSMS.  You also didn't include any inline "double dashed" comments, which are also nothing close to the right color while block comments (/*...*/) are.  I posted a good example on the test thread you folks had before you closed it.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994661

    Here's T-SQL test code from SQL Server 2008 SSMS posted through IE 11.  It is nice to see that they finally got it so that leading spaces have been preserved but the line spacing is horrible, as are the colors.  While I'm happy that the decision to "go with it" was based on an 85% majority, 100% used to be able to do just fine and now I'm part of the 15% that just have to "live with it" and do the NotePad workaround.  Forgive me, ol' friend but I expected that the code windows would have been made so that everyone could use them without such problems.  Heh... since I'm a known IE user, that must be why I wasn't selected for UAT. 😉


    /****** Object: UserDefinedFunction [dbo].[DelimitedSplit8K] Script Date: 03/02/2017 02:07:34 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[DelimitedSplit8K]

    /**********************************************************************************************************************

    Purpose:

    Given a string containing multiple elements separated by a single character delimiter and that single character

    delimiter, this function will split the string and return a table of the single elements (Item) and the element

    position within the string (ItemNumber).

    Notes:

    1. Performance of this function approaches that of a CLR.

    2. Note that this code implicitly converts NVARCHAR to VARCHAR and that conversion may NOT be faithful.

    Revision History:

    Note that this code is a modification of a well proven function created as a community effort and initially documented

    at the following URL (http://www.sqlservercentral.com/articles/Tally+Table/72993/). This code is still undergoing

    tests. Although every care has certainly been taken to ensure its accuracy, you are reminded to do your own tests to

    ensure that this function is suitable for whatever application you might use it for.

    --Jeff Moden, 01 Sep 2013

    **********************************************************************************************************************/

    --===== Define I/O parameters

    (@pString VARCHAR(8000) , @pDelimiter CHAR(1)) --DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    --===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...

    -- enough to cover VARCHAR(8000).

    WITH E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ), --10E+1 or 10 rows

    E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d), --10E+4 or 10,000 rows max

    cteTally(N) AS ( --=== This provides the "base" CTE and limits the number of rows right up front

    -- for both a performance gain and prevention of accidental "overruns"

    SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

    ),

    cteStart(N1) AS ( --=== This returns N+1 (starting position of each "element" just once for each delimiter)

    SELECT 1 UNION ALL

    SELECT CASE WHEN SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN THEN t.N+1 END --added short circuit for casting

    FROM cteTally t

    WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN

    ),

    cteLen(N1,L1)AS ( --=== Return start and length (for use in substring).

    -- The ISNULL/NULLIF combo handles the length for the final of only element.

    SELECT s.N1,

    ISNULL(NULLIF(CHARINDEX(@pDelimiter ,@pString COLLATE Latin1_General_BIN,s.N1) ,0)-s.N1,8000)

    FROM cteStart s

    )

    --===== Do the actual split.

    SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),

    Item = SUBSTRING(@pString, l.N1, l.L1)

    FROM cteLen l

    ;

    Here's what happens, still using IE 11, when I make the side trip through Notepad.  Except for the horrible coloring (even just plain black would look better, the formatting does seem to have been preserved.

    /****** Object:  UserDefinedFunction [dbo].[DelimitedSplit8K]    Script Date: 03/02/2017 02:07:34 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[DelimitedSplit8K]
    /**********************************************************************************************************************
     Purpose:
     Given a string containing multiple elements separated by a single character delimiter and that single character
     delimiter, this function will split the string and return a table of the single elements (Item) and the element
     position within the string (ItemNumber).

     Notes:
     1. Performance of this function approaches that of a CLR.
     2. Note that this code implicitly converts NVARCHAR to VARCHAR and that conversion may NOT be faithful.

     Revision History:
     Note that this code is a modification of a well proven function created as a community effort and initially documented
     at the following URL (http://www.sqlservercentral.com/articles/Tally+Table/72993/). This code is still undergoing
     tests. Although every care has certainly been taken to ensure its accuracy, you are reminded to do your own tests to
     ensure that this function is suitable for whatever application you might use it for.
     --Jeff Moden, 01 Sep 2013
    **********************************************************************************************************************/
    --===== Define I/O parameters
            (@pString VARCHAR(8000) , @pDelimiter CHAR(1)) --DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
    RETURNS TABLE WITH SCHEMABINDING AS
     RETURN
    --===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
         -- enough to cover VARCHAR(8000).
      WITH E1(N) AS (
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                    ),                                      --10E+1 or 10 rows
           E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d), --10E+4 or 10,000 rows max
     cteTally(N) AS ( --=== This provides the "base" CTE and limits the number of rows right up front
                         -- for both a performance gain and prevention of accidental "overruns"
                     SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                    ),
    cteStart(N1) AS ( --=== This returns N+1 (starting position of each "element" just once for each delimiter)
                     SELECT 1 UNION ALL
                     SELECT CASE WHEN SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN THEN t.N+1 END --added short circuit for casting
                       FROM cteTally t
                      WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN
                    ),
    cteLen(N1,L1)AS ( --=== Return start and length (for use in substring).
                         -- The ISNULL/NULLIF combo handles the length for the final of only element.
                     SELECT s.N1,
                            ISNULL(NULLIF(CHARINDEX(@pDelimiter ,@pString COLLATE Latin1_General_BIN,s.N1) ,0)-s.N1,8000)
                       FROM cteStart s
                    )
    --===== Do the actual split.
     SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
            Item       = SUBSTRING(@pString, l.N1, l.L1)
       FROM cteLen l
    ;

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715841

    direct from SSMS to IE 11.

    IE has seriously broken clipboard  issues.  We started working on fixes, but they're substantial, and a project in and of themselves. We abandoned it because this is such a small group of our users.

    The coloring needs work, for sure.

  • Jeff Moden

    SSC Guru

    Points: 994661

    Damn.  Just my luck that I'm in that group "small group".  Guess it's time for me to finally try FireFox.  Thanks, Steve.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715841

    sorry. I really pushed on this, but what works well in Chrome/FF/Edge is broken in IE. Looking at some other editors (like for articles), they spend a massive time dealing with IE issues.

    Part of the problem is that most forums aren't just for code. They include lots of text, images, etc. The code places have mostly gone to markdown, which I don't love. SSMS isn't a great tool, and it's specific to us, so massive changes for IE are just not easily justifiable.

  • Jeff Moden

    SSC Guru

    Points: 994661

    NP and understood.  Just seriously disappointed because it used to work really well after you folks fixed things after the last major forum code upgrade more than a decade ago.  I thought you might be able to steal some of that code especially since most posts do actually involve code windows.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

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