Code Test from IE

Question

Code Test from IE

Steve Jones - SSC Editor

SSC Guru

Points: 734564
More actions
March 1, 2017 at 1:50 pm

#322173

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

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

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

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.

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)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

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

), --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.

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)

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 3

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: 1003894 More actions · Answer 4

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.

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)

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 5

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: 1003894 More actions · Answer 6

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.

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)