March 19, 2012 at 2:42 pm
Artoo22 (3/19/2012)
CELKO (3/19/2012)
Part of the University of Maryland and DoD research was the effect of formatting on maintaining code. Desktop programmers do not know or think about maintaining code but that is 89-90% of the lifetime cost of a system. As the largest user of software on Earth, DoD has been concerned about this stuff.We had "cowboy coders" that loved vendor features (did you know you can add letters in Burroughs COBOL? That IBM 1620 has a neat three-way IF? etc?). The problem was costing us hundreds of billions of dollars until we got the FIPS-127 standards and the FIPS flagger to warn about the cowboys.
Then we looked at how this standardized code was being written.
Bad formatting adds 8 to 12% more to the lifetime cost of a system. People cannot find bugs in visually difficult code. The original experiments were simple; add (n) bugs to a program and see how long it takes to find them. And yes, they often find only (n-k) of the bugs.
The typography studies by newspapers and the US Printing office hold true for code (all uppercase stinks, you read in boumas, etc). Later we got good tools for eye movements (camelCase stinks, PascalCase is only a bit better because your eye jumps to uppercase letters as a cue; commas at the front of a line or keywords at the end of a line also cause eye twitches; etc)
Again, all that research went into my book; Ii am not going to post an entire book.
Thanks Joe. Which of your books are you referring to?
SQL Programming Style
im currently reading it and its preaty good. gives a good base to work off of. my shop uses a sligltly different naming conventions and i have to work within them but the readability of my code has improved since i got his book. little things i was not doing that have improved. alot of the big stuff he mentions i was all ready doing (rivers in the code and the capitalization rules.)
EDIT here is the amazon link http://www.amazon.com/Celkos-Programming-Kaufmann-Management-Systems/dp/0120887975/ref=sr_1_1?ie=UTF8&qid=1332189864&sr=8-1
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 19, 2012 at 2:45 pm
Artoo22 (3/19/2012)
Jared, do me a favour please. Find sp_spaceused and modify it. What do you think? Good code laid out badly.
I just looked at this one specifically... This layout is better than most developers I have encountered. Maybe a bad example?
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create procedure sys.sp_spaceused --- 2003/05/19 14:00
@objname nvarchar(776) = null,-- The object we want size on.
@updateusage varchar(5) = false-- Param. for specifying that
-- usage info. should be updated.
as
declare @idint-- The object id that takes up space
,@typecharacter(2) -- The object type.
,@pagesbigint-- Working variable for size calc.
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint
/*
** Check to see if user wants usages updated.
*/
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin
select @dbname = parsename(@objname, 3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
if @dbname is null
select @dbname = db_name()
/*
** Try to find the object.
*/
SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)
-- Translate @id to internal-table for queue
IF @type = 'SQ'
SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue
/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
-- Is it a table, view or queue?
IF @type NOT IN ('U ','S ','V ','SQ','IT')
begin
raiserror(15234,-1,-1)
return (1)
end
end
/*
** Update usages if user specified to do so.
*/
if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end
set nocount on
/*
** If @id is null, then we want summary data.
*/
if @id is null
begin
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles
select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
/* unallocated space could not be negative */
select
database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2) + ' MB'),
'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2) + ' MB')
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(data_pages) + sum(text_used)
** index: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
select
reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),
index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
end
/*
** We want a particular object.
*/
else
begin
/*
** Now calculate the summary data.
* Note that LOB Data and Row-overflow Data are counted as Data Pages.
*/
SELECT
@reservedpages = SUM (reserved_page_count),
@usedpages = SUM (used_page_count),
@pages = SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
),
@rowCount = SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = @id;
/*
** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
*/
IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204)) > 0
BEGIN
/*
** Now calculate the summary data. Row counts in these internal tables don't
** contribute towards row count of original table.
*/
SELECT
@reservedpages = @reservedpages + sum(reserved_page_count),
@usedpages = @usedpages + sum(used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = @id AND it.internal_type IN (202,204) AND p.object_id = it.object_id;
END
SELECT
name = OBJECT_NAME (@id),
rows = convert (char(11), @rowCount),
reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
end
return (0) -- sp_spaceused
Jared
CE - Microsoft
March 19, 2012 at 5:43 pm
Artoo22 (3/19/2012)
Hi,I don't need assistance today, but I have a comment. Why doesn't Microsoft use T-SQL coding standards?
Whenever I script a system stored proc the code looks like a dogs breakfast. Surely MS should be leading by example.
...from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
...
Cheers
My guess is... they're just like most every other company. Consider the fact that they stored dates and times as integers in MSDB. Just like everyone else, they get people with different and frequently incorrect ideas and don't really care so long as they can meet schedule.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 19, 2012 at 9:01 pm
It seems to me coding is for telling the software to do something. It only cares about syntax. Formatting is for people. Making it readable for others is like having good communication skills. It's also style. I don't think it matter much that my style is a little different as long as my point is getting across. When I think of the amount of opinions on how something should be named, capitalized, commented and tabbed I can't even imagine what standardization would look like. Of course many people agree on similar things but certainly not enough to create a standard. (think: healthcare) I will not be volunteering for THAT committee :hehe:
Luckily there are some really nice 'translators' out there like SQL prompt. I consult and contract so I work with a lot of formatting styles and I don't always have access to the nice tools. SQL is a simple language so I have some scripts that do it well >enough< to read it. I read the studies on capitalization. Plus i love typography from my other lives. Having the key words upper case used to be important and I still type that way. But to be honest, when I read it, I hardly notice the case.
For what it's worth...
March 19, 2012 at 10:46 pm
Artoo22 (3/19/2012)
Jared, do me a favour please. Find sp_spaceused and modify it. What do you think? Good code laid out badly.
My opinion of sp_spaceused is "bad code laid out poorly". Does it work? Sure it does. But the difference between "code that works" and "quality code" was certainly lost in that sp IMHO.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2012 at 3:00 pm
i use something called sql beautifier. i think it was like 20 dollars. It's very useful and formats sql into a standard readable manner. U can also manip[ulate the way it formats it. I cant live w/o it actually. When i go to a new place and have to deal w/ extreamely massive procs it is super useful. I stick w the default formatting cause i'm lazy and really just need everything the same way.
March 20, 2012 at 4:25 pm
SQLKnowItAll (3/19/2012)
Do you think a MS developer sat down and created these in an SSMS window and then executed them?
Yes, though it's more likely something like Query Analyser given the age of those procedures and possibly even a Sybase developer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2012 at 5:00 pm
CELKO (3/20/2012)
SQL Programming Style
im currently reading it and its preaty good. gives a good base to work off of. my shop uses a sligltly different naming conventions and i have to work within them but the readability of my code has improved since i got his book. little things i was not doing that have improved. alot of the big stuff he mentions i was all ready doing (rivers in the code and the capitalization rules.)
EDIT here is the amazon link http://www.amazon.com/Celkos-Programming-Kaufmann-Management-Systems/dp/0120887975/ref=sr_1_1?ie=UTF8&qid=1332189864&sr=8-1
Thank you for saving me a shameless plug 🙂
Rivers are interesting. I used to set type (my grandfather was a printer) and we wanted to avoid them in text for the same reason; the eye drops vertically instead of scanning horizontally. But code is not text and not read the same way.
it was a good read. any thing i disagreed with at first i could follow the logic to your conclusion and see exactly where you were coming from and even change my mind on some of the column naming conventions. For a person just starting into development (or all ready in development and looking for something to gain a few tidbits) it is on the must read list.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply