May 29, 2005 at 2:36 pm
Let me see if I got this right. Crystal Reports can't handle changes to your schema so it's time to make developers lives more dificult. Let me suggest you dump CR and get DataDynamics ActiveReports or SQL Reporting Services or Excel or even just using the output directly from Query Analyzer. Maybe that's a little over the edge -- but just a tiny little bit <g>. In my opinion, Crystal is little better than a stone table and chissel.
What I use is Query Analyzer for coding so I get syntax highlighting and quite honestly a blue keword "As" is not a usefull as something like this...
select o.CustomerID = c.ID
, Customer = c.Name
, OrderID = o.ID
from Customer c
join Orders o on c.ID = o.CustomerID
What I like is consistent, neat and blocked-out code. As a consultant, I've had the opportunity to show my guidelines to both developers and managers and have gotten a very positive response from both groups. Mostly because it stresses good conventions that seldon need to be aliased (unlike the sample above.)
Paul
--Paul Hunter
May 31, 2005 at 10:01 am
First off I don't make any decission to make anyone's lives difficult. The use of CR is a management decission which I have to deal with just as anyone in my organization. And just because you think CR is not worth anything doesn't mean that everyone esle is suppose to ditch it in favor of your preferred rpeorting tool. Don't assume that the person posting ssomething always has absolute control over everything in their company. Are you able to make any kind of change you wish at your company?
Second off I am not criticizing your style I am merely inqurying about it. The SQL language often allows one to do the same thing via many different paths. The style used for writing that code is also something that can vary. While your style is what you like it does not mean it is the best or the only one applicable. I too have had positive feedback from my style. The difference between us is that I see others differences as just that, difference and not simply inferior and taking the attitude that mine is the only way.
Lastly, field alilases. I guess that Microsoft is just wrong here and your right. The fact that MS has taken the stance that field aliasing is a good thing by changing SQL Server 2005 to rely on it more so then SQL 2000 is just wrong since it disagrees with your stance or am I reaidng your last response incorrectly? If your reply is nothing more then a criticism instead of a crituque then don't bother replying. I'm always interested in meaningful dialog and not in taking shots back and forth.
Kindest Regards,
Just say No to Facebook!December 19, 2005 at 8:49 am
The standards outlined by Steve Jones are exactly what I have adopted - they help so much when you're dealing with large scripts and more so - someone elses scripts!
I have two additional standards:
1. All INSERT, UPDATE and DELETE scripts must be enclosed by /* */ so that they are not accidentally executed.
2. All INSERT, UPDATE and DELETE scripts must start with BEGIN TRAN.
Tom Berry
December 19, 2005 at 2:04 pm
Our coding standards are similar, but mine are a bit more stringent:
/*
1. Having all sections of the same query tabbed after the select clarifies what is and is not part of the query
2. All column names in a multi-table query should be specified as to the owner - you never know when a column of the same name will be added to a joined table, and it slightly increases performance
3. All column and table names should be in camelcase, everything else in lowercase for easier reading and typing
4. Aliased table names should be the first letter of each word of the base table name - in lowercase
5. Aggregate columns should be named for ease of identification
6. The word "as" should be used whenever aliasing.
7. Each sub-part of a section of a query should be tabbed once more than the section header
8. 99% of reads can be done safely with the (readuncommitted) locking hint. This can greatly reduce contention.
*/
select
c.CustomerID
, c.CustomerName
, count(*) as CustomerCount
from Customers as c with (readuncommitted)
inner join Orders as o with (readuncommitted)
on c.CustomerID = o.CustomerID
left outer join OrderLines as ol with (readuncommitted)
on o.OrderID = ol.OrderID
and ol.Status = 1
where
c.CustomerID = 5
and c.CustomerAddress is not null
and (c.Active = 1 or c.Status > 5)
group by
c.CustomerID
, c.CustomerName
having
count(*) > 1
order by
c.CustomerID
, c.CustomerName
December 19, 2005 at 2:51 pm
I agree with everything but using aliases. Though it makes it more difficult to read sometimes because of lengthy table names, debugging and modifications are much easier.
For instance, If you have to globally search and replace the name of a table throughout your code, you don't want to have to think of every alias variation that may have been used. The standard should be set so that the full table name is used all the time. That way there is no confusion within the code as to what table it is and no possibility that one of the aliases is missed in a modification to the code.
I also agree with making all the reserved words CAPITALized. Makes the code much easier to read.
Just my 2 pence
December 19, 2005 at 5:12 pm
Must have missed this article when it came out last year but I have been using this technique (with capitalised KEYWORDS) for many years now.
I try hard to instill it into the new developers and encourage them to ensure that all code, not jsut in the database, contain some menaingful and consistent structure.
Considering the complexity of some queries I think it is very important to use the techniques used in newspapers all over the world. Lay things out in columns. That's one of the reasons newspapers can be read so quickly by many.
It certainly works for me when coming back to look at a complex query in terms of understanding what the query is trying to achieve.
Glad to see I was on the right(?) track
December 19, 2005 at 7:11 pm
HI, thanks for your article,
I've got a question?
We proposed in my work, that the name of columns in table is about semanthic of de the column ie, "what the column is", example Order, ShipName, etc, we recomend NOT USE THE TYPE OF COLUMN, ie int, varchar, string, etc.....
The programmer say: "it's not possible because we need to know the type of de column example strShipName for an rapid development"
I think it is not correct, because what happen if the column change example, from boolean to int, in resume i don't think is better use teh hungarian notation.
Any comments of this?
thanks
zeta
December 20, 2005 at 8:46 am
Tabs into spaces is nice. You can set that in your editor. I agree with commas preceding each column. If you're not doing that currently I promise you'll like it.
July 2, 2008 at 5:23 pm
I have been using a very similar standard for years, taught to me by a developer I used to work with. Since then I have been very passionate about keeping to a good standard such as outlined in this article (although I have a few things I do slightly differently). I will also only do these things if it will increase the readability of the block which is the whole point of doing it.
I now work for a different company and comparing the 2 standards used is like chalk and cheese (the chalk tastes horrible compared to the old cheese way). The standard at my old work allowed me to open up a 1000 line stored proc that I had never seen before and be able to read very easily what was happening. I could often debug something almost instantly looking at each block of code.
Nowadays, I am working for a different company and trying to learn a system where each block of code is written almost like a solid block of text. I especially cannot understand how people can use the column names on 1 line approach and the SQL server standard of sticking the joins on the end of lines.
Firstly on the column names, if there is a bug in something, or you need to add a column, especially into an insert statement, or say you forgot a comma, nothing can be more frustrating than trying to count across the screen to find one of 50 columns quite often named inconsistantly from the data that is being put into it (think trying to read a large csv file in notepad and trying to line up which data is in which column - aliases can help though). Then imagine this jammed tight up against a whole bunch of other blocks of code:
eg. (this is only a small example but you get the point - it gets worse as it gets bigger)
[font="Courier New"]INSERT INTO SomeTable (ColumnA,ColumnB,ColumnC,ColumnD)
VALUES (0,'SomeKindOFPieceOFData',0,'SomeOtherReallyLongPieceOfData')[/font]
Now try reconciling the columns compared to this:
[font="Courier New"]INSERT INTO SomeTable
(
ColumnA
,ColumnB
,ColumnC
,ColumnD
)
VALUES
(
0 AS ColumnA
,'SomeKindOFPieceOFData' AS ColumnB
,0 AS ColumnC
,'SomeOtherReallyLongPieceOfData' AS ColumnD
)
[/font]
Aliasing also really helps in this format, so you don't need everything on the same screen at once.
I also love the commas at the beginning of the line as (besides looking neater) you can be guaranteed that if you miss one (it happens to the best of us and SQL server is not always forthright in telling you the exact problem when one is missing) it will stick out like a sore thumb and you will instantly see you need to add one in, but if it is on the end of variable length lines you will quite often have to read a whole block to find it.
Which brings me to my next point of joins on the ends of lines. A good rule of thumb is to read in your head a statement as though you were reading it out loud, adding pauses for clarity. If someone read you a paragraph of text with no punctuation at all, I bet you would drift off and not comprehend half of what they said. The same is true in SQL.
So when someone says:
[font="Courier New"]FROM Table1 LEFT OUTER JOIN
Table2 ON Table1.Column1 = Table2.Column2 INNER JOIN
Table3 ON Table1.Column1 = Table3.Column3 [/font]
...it is interesting to hear them read it aloud and pause at the important spots. Then I have to ask why you would then put the join for Table3 on the line for Table2 when the
join parameters have nothing to do with Table2 and they notice that they paused before the join. So get stuff from Table1 -pause- then make a join to table2 in this way -pause- and then make a join to table3 in this way. It just makes sense. The join is related to the table it preceeds. Like so:
[font="Courier New"]FROM Table1 T1
LEFT OUTER JOIN Table2 T2 ON T1.Column1 = T2.Column2
INNER JOIN Table3 T3 ON T1.Column1 = T3.Column3 [/font]
Say it out loud, you know it makes sense. Also, you can then comment out the join, like you can comment out one field in a select list when they are each on their own line, because the join and the table that succeeds it are meant to be together. Yet the GUI tools add the join to the end of the preceeding line and people wonder why they always screw up, i.e. never understand, their joins.
I like the use of capitalised keywords too as they make the difference between commands and fields, and also the whole structure, a lot clearer and proper indentation is a must. Whitespace is your friend. The human eye is built to read lists and indentation creates a list like nested structure. Like so (to use an example from the article):
[font="Courier New"]--===================================================
--Comment about what the block does breaks it up from
--the rest of the code so you can see where each starts
--and ends
SELECT
c.CustomerID AS CustomerID
,o.CustomerName AS CustomerName
,COUNT(*) AS RecCount
FROM
Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
LEFT OUTER JOIN OrderLines oi ON o.OrderID = oi.OrderID AND oi.Status = 1
WHERE
c.CustomerID = 5
AND c.CustomerAddress IS NOT NULL
AND (c.Active = 1 OR c.Status > 5)
GROUP BY
c.CustomerID
,c.CustomerName
HAVING
COUNT(*) > 1
ORDER BY
c.CustomerID
,c.CustomerName[/font]
So easy to read. And adding the table alias to ALL columns not only helps the proc not to break should a column of the same name be added to one of the other tables, it also allows you to see which table it comes from. When you have large numbers of columns and tables involved and you are trying to find where one of them comes from because it is failing due to a NULL value or something, this can be invaluable (especially when each table may have many columns themselves and you have to go back to each and search through looking for one in particular - very tiresome and a waste of time).
I used to use solely GUI tools when I first started and I understand the comfort factor of using them (in fact I find them much quicker to use if I am just running some simple queries to check some data), but unfortunately they are no good when you are dealing with very large stored procs with many, many sql statements in them. You absolutely must be able to read the code, hence the importance of proper formatting. But some people are just lazy and others pay the price.
January 7, 2009 at 3:34 pm
I prefer this:
SELECT customerid,
customername,
count(*) AS mycount
FROM customers c
INNER join orders o
ON c.customerid = o.customerid
LEFT OUTER JOIN orderlines oi
ON o.orderid = oi.orderid
AND oi.status = 1
WHERE customerid = 5
AND c.customeraddress is not null
AND (c.active = 1
OR
c.status > 5)
GROUP BY customerid,
customername
HAVING count(*) > 1
ORDER BY customerid,
customername
Cap and right alight all the keywords
Viewing 10 posts - 46 through 54 (of 54 total)
You must be logged in to reply to this topic. Login to reply