March 21, 2009 at 1:10 pm
Overall, I'm pretty happy with the new code windows... there are still a couple of bug-a-boos that could probably stand a little rework.
Some of my suggestions appear in the comment header in the test code below...
I'd also recommend making the edit window use the same wonderful monospaced font that the code window uses for display.
That way, people can do aligned editing and formatting right in the edit window.
--===== Test 0: Forced wrapping on contiguous data (If there are no spaces in the code, it doesn't force a wrap...
--- (I'd recommend forcing a wrap at character 121 no matter what).
SELECT REPLICATE('1234567890',25)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
--===== Test 1: Spaced Character Alignment Test (OK for what was tested)
SELECT REPLICATE('1234567890',12)
UNION ALL
SELECT REPLICATE(' i X',12)
------------------------------------------------------------------------------------------------------------------------
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
i X i X i X i X i X i X i X i X i X i X i X i X
There're some pretty bad problems when copying code from one of the code windows to QA or SMS...
First, any leading spaces simply disappear which, of course, destroys folks good efforts to format their code.
Second, as with many products of this nature, it adds extra blank lines between each and every printed line.
So, in SMS and QA, making a copy of the Test 1 code and pasting it gives you something like this (sans coloring, of course)
[font="Courier New"]---===== Test 1: Spaced Character Alignment Test
SELECT REPLICATE('1234567890',12)
UNION ALL
SELECT REPLICATE(' i X',12)[/font]
--===== Test 2: Tabbed Character Alignment Test (Tabs appear as 8 spaces when listed in edit window and appear as only 4
-- in the final display... that's actually not a bad thing especially for me since that's what my default tabs are set to. ;)
SELECT REPLICATE('1234567890',12)
UNION ALL
SELECT CAST(REPLICATE(CHAR(9),t.N)+CAST(t.N AS VARCHAR(10)) AS VARCHAR(120))
FROM dbo.Tally t
WHERE t.N < = 23
------------------------------------------------------------------------------------------------------------------------
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--===== Test 3: General formatting and smiley face test
-- Oh... not good. Things that look like the smiley face codes get expanded to the full IFCodes even if they're in comments.
-- It is good to see, though, that single parenthesis with leading spaces are no longer converted to smiley faces.
-- There's also still the issue of blank lines being deleted.
/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once. ;-) :-)
Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.
Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up do snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0)
--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
--SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1, --Table with clustered index
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
--===== Test 4: Highlight, color, and font test (Again, blank lines disappeared and none of the enhancement formatting
-- took hold. That's probably a good thing for HTML. I'll test that further below.
-- I am concerned about "ADD" being doubled up. Next test will see if that's because of any formatting I did on this test.
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally --This should be underlined
FROM Master.dbo.SysColumns sc1, --This should be italicised
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance --This should be bold
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH [highlight=""]FILLFACTOR[/highlight] = 100 --FILLFACTOR should be highligted
[font="Arial Black"]--===== Allow the general public to use it[/font] --This should be Arial Black
GRANT SELECT ON dbo.Tally TO PUBLIC --This should be RED
--===== Test 5: Same as test 4 with no enhancements (Blank lines disappeared and ADD doubled up)
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
--=====Test 6: Simple HTML
-- Heh... none of the HTML shows up.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 21, 2009 at 7:00 pm
Yikes, looks like a lot of stuff to fix. We'll go through some of this next week.
March 21, 2009 at 7:13 pm
Steve Jones - Editor (3/21/2009)
Yikes, looks like a lot of stuff to fix. We'll go through some of this next week.
I actually did this because Phil sent me an email asking what I thought would be some good improvements... I'll forward my response to you.
heh... looks like you have a good testbed for it, too! 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2009 at 6:30 pm
Jeff Moden (3/21/2009)
------------------------------------------------------------------------------------------------------------------------
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
You do realize this is classic RBAR, right?
😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 23, 2009 at 5:46 am
RBAR? How so?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2009 at 7:11 am
Looks "Row-by-Row" to me. 😛
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 23, 2009 at 7:45 am
RBarryYoung (3/23/2009)
Looks "Row-by-Row" to me. 😛
On the completely non-sequitur side of things, thanks to your post, I now have the song, "Row, row, row your code, slowly onto the screen", going through my head repeatedly. I should know better than to read this kind of stuff when I'm not actually awake yet.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2009 at 9:03 am
GSquared (3/23/2009)
RBarryYoung (3/23/2009)
Looks "Row-by-Row" to me. 😛On the completely non-sequitur side of things, thanks to your post, I now have the song, "Row, row, row your code, slowly onto the screen", going through my head repeatedly. I should know better than to read this kind of stuff when I'm not actually awake yet.
ROTL! :w00t:
Heh, I had to finish it:
Row, row, row your code,
slowly onto the screen.
Agony, agony, agony, agony!
Speed is but a dream!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 23, 2009 at 9:04 am
Man! Now I'm never going to get that one out of my head. It's doing "rounds" now! 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2009 at 3:37 pm
RBarryYoung (3/23/2009)
GSquared (3/23/2009)
RBarryYoung (3/23/2009)
Looks "Row-by-Row" to me. 😛On the completely non-sequitur side of things, thanks to your post, I now have the song, "Row, row, row your code, slowly onto the screen", going through my head repeatedly. I should know better than to read this kind of stuff when I'm not actually awake yet.
ROTL! :w00t:
Heh, I had to finish it:
Row, row, row your code,
slowly onto the screen.
Agony, agony, agony, agony!
Speed is but a dream!
AWESOME!
* Noel
March 28, 2009 at 3:23 pm
Just a couple of tests to see if they actually did anything for some of the fixes...
/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.
Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.
Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up do snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0)
--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
--SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1, --Table with clustered index
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
drop table tally
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2009 at 3:25 pm
Pasted from the code window above minus the double add...
drop table tally
---===== Create and populate the Tally table on the fly
SELECT TOP 11000 ---equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
---===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
---===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2009 at 3:27 pm
Heh... the outcome of the tests say... nothing's been done so far. BWAA-HAAA!!!! Perhaps this would be a good place to introduce folks to "agile" programming methods and releases. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2009 at 3:08 pm
Just another test... (nothing has changed... all faults still present especially when copy from the forum to QA or SSMS).
drop table tally
GO
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2009 at 3:52 pm
It's not doubling the "ADD"'s anymore is it?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply