February 2, 2012 at 9:56 am
Steve Jones - SSC Editor (2/2/2012)
davidandrews13 (2/2/2012)
isn't that just down to formatting though? the fact that the article has coloured the ColumnNames in green, which wouldn't be the case normally, just highlights it even more.Two issues there. First, width. As you get more complex, it's not necessarily fitting easily on a screen. second, scanning for a column, based on a result, I think it's easier to see.
selectTotalSales = sum( itemquantity * unitprice) - salestax
, Profit = sum( itemquantity * unit price) - salestax - sum( itemcost * itemquantity)
- shippingcost - othercost
, itemquantity
, customername = (firstname + ' ' + substring( middlename, 1, 1) + ' ' + lastname + ' ' + suffix
from ...
its definitely a personal preference thing. i guess the way the article shows it, means anyone will write the code in the same easily readable manner without worrying about trying to format it with TABs etc.
the way i write it, if the column is a long column, i'll put it on to 2,3 or more lines just to make it more readable anyway.
select
DATEADD(day,15,getdate()) AS myTime
, DATEADD(day,13,getdate()) AS myExtraTime
, 'this is a' + ' long long long long' +
' string' AS myString
February 2, 2012 at 10:06 am
I agree with davidandrews13 that it is a preference thing, however, there is plenty of empirical evidence that states that the smaller element of code should appear before the larger for easier understanding e.g. you should reverse the if statement around if the else block is smaller than the if block. Could this be an example of it?
Oh, you're about to ask for a reference to the evidence? I think you will find some reported in Steve McConnell's Code Complete. I hope my memory has held up...what was that about old dogs???
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 2, 2012 at 10:16 am
Gary Varga (2/2/2012)
Rod at work (2/2/2012)
Semicolons will at some point be required in T-SQL? Really? I've seen it used in some cases, but am completely uncertain as to when they are to be used. I write C# code, and before that C and some C++. It's easy to know when to use semicolons in those languanges. So, when do you use semicolons in T-SQL?My understanding is that they are currently an optional delimiter but can be used at the end of each statement just like the C family of languages where they are mandatory.
OK, just to clarify, let me provide a code snippet from a SP I wrote several years ago:
IF (@VoucherServiceCode > 500) AND (@VoucherServiceCode < 700)
BEGIN
/* Here's where we check the daily services. */
IF ((SELECT Count(*) FROM VoucherServices WHERE
VoucherNumber = @VoucherNumber AND
VoucherServiceCode = @VoucherServiceCode AND
DateOfService = @DateOfService) > 0)
BEGIN
RAISERROR('A Daily Service already Exists for this Treatment Type for this date.',11,1)
RETURN
END
END
ELSE
BEGIN
IF @VoucherServiceCode = 700
BEGIN
SELECT @CountServ = (SELECT Count(*) FROM VoucherServices WHERE
VoucherNumber = @VoucherNumber AND
VoucherServiceCode = @VoucherServiceCode AND
DateOfService = @DateOfService)
IF (@CountServ > 1)
BEGIN
RAISERROR('Methadone Services have been invoiced for this Month. No changes allowed...',11,1)
RETURN
END
END
END
If I have to use semicolons at the end of statements, then I can see making a SELECT statement from above looking like this:
SELECT Count(*) FROM VoucherServices WHERE
VoucherNumber = @VoucherNumber AND
VoucherServiceCode = @VoucherServiceCode AND
DateOfService = @DateOfService;
that's easy. And I'm sure that if it's in a conditional, like my lines above, then I wouldn't put in a semicolon. But what about a closing END keyword? Do you put semicolons there? e.g.:
IF (@CountServ > 1)
BEGIN
RAISERROR('Methadone Services have been invoiced for this Month. No changes allowed...',11,1)
RETURN
END;
END;
I've seen semicolons applied to the end of END closing statements in some languages, will they be required in some future version of T-SQL?
Kindest Regards, Rod Connect with me on LinkedIn.
February 2, 2012 at 10:59 am
cengland0 (2/2/2012)
IceDread (2/2/2012)
About aging, it's been proven recently that people over 45 solves equations a bit more slowly than younger ones.Really? I'd like to see that documentation. I'm over 45 and believe I solve problems and equations much faster than my younger peers. That's because I have more experience doing it. Maybe once they are my age, they can do it faster too.
I tried to find the reference, I believe I read it in http://www.svd.se or http://www.dn.se not that long ago but I could not.
I will get back to you if I do find it.
Because I could not let it go, I did some digging. This is what I found http://www.fas.se/upload/dokument/ALI%20pdf-skrifter/isbn9170453950.pdf
While one professor writes "software gets better with age but the hardware degrades" there are several articles reviewed in that book and there is no conclusive evidence for that claim, or the claim that I made.
February 2, 2012 at 11:18 am
IceDread (2/2/2012)
Because I could not let it go, I did some digging. This is what I found http://www.fas.se/upload/dokument/ALI%20pdf-skrifter/isbn9170453950.pdfWhile one professor writes "software gets better with age but the hardware degrades" there are several articles reviewed in that book and there is no conclusive evidence for that claim, or the claim that I made.
Tried to read it but it's so complicated to understand. It's almost like it's in some foreign language or something. 🙂
February 2, 2012 at 11:35 am
I've seen semicolons applied to the end of END closing statements in some languages, will they be required in some future version of T-SQL?
I've been trying to get into the habit of using semicolons for a while now, since they're already required in some T-SQL statements such as WITH. I'm sure that list will grow in 2012 and versions beyond.
It helps to think of them as statement delimiters. Your last snippet of code works, but also could be:
IF (@CountServ > 1)
BEGIN;
RAISERROR('Methadone Services have been invoiced for this Month. No changes allowed...',11,1);
RETURN;
END;
END;
To be honest, for me it has been trial-and-error so far. I've seen a lot of Incorrect syntax near ';' messages!
February 2, 2012 at 12:17 pm
Here's a sample with correct semicolons:
IF 1 = 1
BEGIN ;
PRINT 'Yep' ;
END ;
ELSE
BEGIN;
PRINT 'Nope' ;
END ;
Note that the IF statement doesn't end with one, nor does ELSE. That's because it's actuall "IF 1=1 BEGIN;" as a single statement, and "ELSE BEGIN;" as a single statement. Moving "BEGIN" to the next line is prettier and more readable, but it's still part of one T-SQL statement.
I've been using semicolons at the end of my SQL statements for years. It's for my own logical breakdown of statements, not for any necessity (except in the few cases where T-SQL requires it, like CTEs in most cases).
On the other hand, I don't use BEGIN/END to define stored procedures (CREATE PROC dbo.MyProc AS BEGIN code here END). So I'm insonsistent on that point.
To me, it's like ending sentences with punctuation Just because the reader (compiler) can figure out what I mean doesn't mean I should avoid explicit clarity It just reads better to me that way If you don't like punctuation for clarity that is by all means your choice on the matter There are of course other ways to delimit statements
(Sorry, couldn't resist that last paragraph.)
On the point of learning new things, like the editorial points out: Yeah, I'm constantly evolving my standards on various things. I'm a firm believer in the idea that "just because that's how we've been doing it, doesn't mean that's the way we should continue doing it". After all, the ability to rapidly learn new things, and learn from other people's experiences, is a large part of what makes us this planet's apex predator, instead of being saber-tooth tiger food.
- 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
February 2, 2012 at 1:09 pm
Thank you, Randy Rabin and GSquared, for giving me code examples using semicolons, especially with respect to keywords like BEGIN and END. I'll start using semicolons in more of my T-SQL code, so I can get used to it.
Kindest Regards, Rod Connect with me on LinkedIn.
February 2, 2012 at 3:34 pm
I find gsquared example confusing. Usually ; is end of sqlstatement. With all those ; where is the beginning and end?
February 2, 2012 at 3:41 pm
On the particular point of columnAlias = Somestuff, I dislike it particularly for larger queries.
They look like where clauses, or joins, to me when I'm scanning large scripts. An AS statement is easier for me to pick up during a high speed scroll looking for the 8th query.
The AS helps me mentally delineate them as columns. Now, if it was some other symbol then ones we used for equality, I'd be all for it. Pipe comes to mind.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2012 at 5:24 am
I find funny when someone complain about by *= versus explict outer join and stills write code in upper case and complains about lower case.
*=
OUTER JOIN
outer join
😛
But I digress.
I'm a old dog. Starting with computers as a hobby at 10, as a begginer at 20, mature professional at 30 and today over 40 I'm still learning.
(And for sure is easyer to learn a new tool a new technology than learn to do thing in a different way).
I belive it's the best in this field. All is changing, every day, and you must change as well.
It's challenging and a live without challenges is like a meal without spice.
:hehe:
February 3, 2012 at 5:54 am
jcb (2/3/2012)
I find funny when someone complain about by *= versus explict outer join and stills write code in upper case and complains about lower case.<Snip />
Casing of SQL is something I may have missed. I was taught that SQL keywords should always be in uppercase. Have I missed a change?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 3, 2012 at 6:04 am
MS SQL Server is mostly case insensitive.
reserved words are insensitive.
objects names and strings are insensite (if the collation used is insensitive)
and only a few things are sensitive like the today's QotD.
February 3, 2012 at 6:10 am
Until recently, I was using AS. I switched to using =, exactly because of the readability. Of course, I am also rather..... particular when it comes to formatting anyway.
For me, when writing INSERT statements, I alias every column in the SELECT statement with the column name it corresponds to.
INSERT INTO [schema].
(
col1
, col2
)
SELECT
col1 = t.colA
, col2 = t.colB
FROM
[schema].[table2] AS t
;
I am also the sort of person who has to align all the variable names and data types, and put them all together under one DECLARE statement at the beginning of the procedure, and has to align the CASE & END, WHEN & ELSE, and each of the THEN statements.
I know, too picky probably, but it makes things much easier for me to scan through.
February 3, 2012 at 6:12 am
Uppercase was just convention as opposed to a compiler requirement.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 15 posts - 31 through 45 (of 92 total)
You must be logged in to reply to this topic. Login to reply