Code pasting still not 100%
-
When pasting code into the forums, it's still really not where it used to be. The cursor seems to go all over the place when you paste (sometimes it goes to the end of the first line you pasted, sometimes it disappears off the face of the earth), and, at least for me, formatting is always lost.
If you have a look at the below, I directly pasted it from Notepad. notice that the allignment is really wonky (I've got fed up of trying to fix it in my posts now). but a little bit of my soul dies everytime I post things like this. if you open the attached text file, you'll see everything is aligned fine. Is this something that's being fixed?
CREATE TABLE #Employee (Date_and_time DATETIME,
Table_name VARCHAR(50),
Field_name VARCHAR(50),
Personnel_number INT,
Old_value VARCHAR(20),
New_value VARCHAR(20));
GOINSERT INTO #Employee
VALUES ('20120629 16:00:40.000','Maint_Employee_Details','Cost_centre','1064','2220','2220'),
('20121105 10:36:11.000','Maint_Employee_Details','Cost_centre','1064','2220','3333'),
('20120629 16:00:40.000','Maint_Employee_Details','Department','1064','H','H'),
('20121105 10:36:11.000','Maint_Employee_Details','Department','1064','H','HR'),
('20120629 16:00:40.000','Personnel_Details','Manager','1064','284','284'),
('20120807 15:44:41.000','Personnel_Details','Manager','1064','284','1074'),
('20121105 10:36:11.000','Personnel_Details','Manager','1064','1074','666'),
('20120629 16:00:40.000','Maint_Employee_Details','Post_number','1064','TST007T','TST007T'),
('20121105 10:36:11.000','Maint_Employee_Details','Post_number','1064','TST007T','FARMTST'),
('20120626 12:36:18.000','Personnel_Details','Section','1064','TSu','TSu');
GOSELECT *
FROM #Employee;SELECT E.Personnel_number,
E.Date_and_time AS DateFrom,
E2.Date_and_time AS DateTo,
E.Field_name,
E.new_value AS [Value]
FROM #Employee E
OUTER APPLY (SELECT TOP 1 *
FROM #Employee oa
WHERE oa.Personnel_number = E.Personnel_number
AND oa.Table_name = E.Table_name
AND oa.Field_name = E.Field_name
AND oa.Date_and_time > E.Date_and_time
ORDER BY oa.Date_and_time ASC) E2;GO
DROP TABLE #Employee;Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - It looks lined-up to me (depending, of course, on the style you're using). Which browser are you using? Can you add a screen shot?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
This is what it looks like for me.
If you compare to the text file, the alignment is completely different. (see image below from SSMS)
It seems that when I paste multiple whitespaces they are converted into 1. I did initially think it was every 2, but then by WHERE statement in my OUTER APPLY would be misaligned compared to the rest, which it isn't.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Now I see what you mean.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Tabs or space in source?
- Steve Jones - SSC Editor - Monday, January 30, 2017 8:54 AM
Spaces.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
What really needs to happen is that they need to make it so that you can paste to the SQL Code IFCode directly from SSMS. I just tried posting the following by pasting from SSMS directly. The preview showed that all leading spaces are deleted, all lines end up double spaced, and all "--" comments aren't colored as comments. Worse yet, when I hit "post", the "Loading" message spun forever (more than 2 minutes) and I had to stop it. The code below was pasted from NotePad.
/****** Object: UserDefinedFunction [dbo].[fnTally] Script Date: 01/30/2017 10:48:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URLs for how it works and introduction for how it replaces certain loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
Rev 03 - 22 Apr 2015 - Jeff Moden
- Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN 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) --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;--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) -
I've had similar issues as well, plus extra line spacing when pasting. Commented code is also strange.
--What's with this?
SELECT quote FROM brain WHERE original = 1
0 rows returned -
Behaviour definitely seems to differ on browser, as I don't get the problem Jeff has (extra lines) in Firefox 51.0.1 (64Bit). If I copy the text I put above from SSMS again, this time in IE 11.0.38 (KB3203621) I get the following (I haven't put in SQL Code at first on purpose):
CREATE TABLE #Employee (Date_and_time DATETIME,
Table_name VARCHAR(50),
Field_name VARCHAR(50),
Personnel_number INT,
Old_value VARCHAR(20),
New_value VARCHAR(20));
GO
INSERT INTO #Employee
VALUES ('20120629 16:00:40.000','Maint_Employee_Details','Cost_centre','1064','2220','2220'),
('20121105 10:36:11.000','Maint_Employee_Details','Cost_centre','1064','2220','3333'),
('20120629 16:00:40.000','Maint_Employee_Details','Department','1064','H','H'),
('20121105 10:36:11.000','Maint_Employee_Details','Department','1064','H','HR'),
('20120629 16:00:40.000','Personnel_Details','Manager','1064','284','284'),
('20120807 15:44:41.000','Personnel_Details','Manager','1064','284','1074'),
('20121105 10:36:11.000','Personnel_Details','Manager','1064','1074','666'),
('20120629 16:00:40.000','Maint_Employee_Details','Post_number','1064','TST007T','TST007T'),
('20121105 10:36:11.000','Maint_Employee_Details','Post_number','1064','TST007T','FARMTST'),
('20120626 12:36:18.000','Personnel_Details','Section','1064','TSu','TSu');
GO
SELECT *
FROM #Employee;
SELECT E.Personnel_number,
E.Date_and_time AS DateFrom,
E2.Date_and_time AS DateTo,
E.Field_name,
E.new_value AS [Value]
FROM #Employee E
OUTER APPLY (SELECT TOP 1 *
FROM #Employee oa
WHERE oa.Personnel_number = E.Personnel_number
AND oa.Table_name = E.Table_name
AND oa.Field_name = E.Field_name
AND oa.Date_and_time > E.Date_and_time
ORDER BY oa.Date_and_time ASC) E2;
GO
DROP TABLE #Employee;
Now I'm going to paste it again, then highlight it and click the SQL Code button and see the chaos...
CREATE TABLETABLE #Employee #Employee ((Date_and_time Date_and_time DATETIMEDATETIME,, Table_name Table_name VARCHARVARCHAR((5050),), Field_name Field_name VARCHARVARCHAR((5050),), Personnel_number Personnel_number INTINT,, Old_value Old_value VARCHARVARCHAR((2020),), New_value New_value VARCHARVARCHAR((2020));));GO
GOINSERTINSERT INTOINTO #Employee #EmployeeVALUES VALUES (('20120629 16:00:40.000''20120629 16:00:40.000',,'Maint_Employee_Details''Maint_Employee_Details',,'Cost_centre''Cost_centre',,'1064''1064',,'2220''2220',,'2220''2220'),), (('20121105 10:36:11.000''20121105 10:36:11.000',,'Maint_Employee_Details''Maint_Employee_Details',,'Cost_centre''Cost_centre',,'1064''1064',,'2220''2220',,'3333''3333'),), (('20120629 16:00:40.000''20120629 16:00:40.000',,'Maint_Employee_Details''Maint_Employee_Details',,'Department''Department',,'1064''1064',,'H''H',,'H''H'),), (('20121105 10:36:11.000''20121105 10:36:11.000',,'Maint_Employee_Details''Maint_Employee_Details',,'Department''Department',,'1064''1064',,'H''H',,'HR''HR'),), (('20120629 16:00:40.000''20120629 16:00:40.000',,'Personnel_Details''Personnel_Details',,'Manager''Manager',,'1064''1064',,'284''284',,'284''284'),), (('20120807 15:44:41.000''20120807 15:44:41.000',,'Personnel_Details''Personnel_Details',,'Manager''Manager',,'1064''1064',,'284''284',,'1074''1074'),), (('20121105 10:36:11.000''20121105 10:36:11.000',,'Personnel_Details''Personnel_Details',,'Manager''Manager',,'1064''1064',,'1074''1074',,'666''666'),), (('20120629 16:00:40.000''20120629 16:00:40.000',,'Maint_Employee_Details''Maint_Employee_Details',,'Post_number''Post_number',,'1064''1064',,'TST007T''TST007T',,'TST007T''TST007T'),), (('20121105 10:36:11.000''20121105 10:36:11.000',,'Maint_Employee_Details''Maint_Employee_Details',,'Post_number''Post_number',,'1064''1064',,'TST007T''TST007T',,'FARMTST''FARMTST'),), (('20120626 12:36:18.000''20120626 12:36:18.000',,'Personnel_Details''Personnel_Details',,'Section''Section',,'1064''1064',,'TSu''TSu',,'TSu''TSu'););
GO
GOSELECTSELECT **FROMFROM #Employee #Employee;;SELECTSELECT E E..Personnel_numberPersonnel_number,, E E..Date_and_time Date_and_time ASAS DateFrom DateFrom,, E2 E2..Date_and_time Date_and_time ASAS DateTo DateTo,, E E..Field_nameField_name,, E E..new_value new_value ASAS [Value] [Value]FROMFROM #Employee E #Employee E OUTEROUTER APPLYAPPLY ((SELECTSELECT TOPTOP 1 1 ** FROMFROM #Employee oa #Employee oa WHEREWHERE oa oa..Personnel_number Personnel_number == E E..Personnel_numberPersonnel_number ANDAND oa oa..Table_name Table_name == E E..Table_nameTable_name ANDAND oa oa..Field_name Field_name == E E..Field_nameField_name ANDAND oa oa..Date_and_time Date_and_time >> E E..Date_and_timeDate_and_time ORDERORDER BYBY oa oa..Date_and_time Date_and_time ASCASC)) E2 E2;;
GO
GODROPDROP TABLETABLE #Employee #Employee;;
Something has gone very wrong there.
Edit: If you're interested, quote my post and have a look at the mess pressing the SQL Code button made of that code in IE (when it's taken straight from SSMS).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply