November 8, 2017 at 7:24 am
Sean Lange - Wednesday, November 8, 2017 7:19 AMBrandie Tarvin - Wednesday, November 8, 2017 5:24 AMSean Lange - Tuesday, November 7, 2017 2:14 PMI prefer a comma at the beginning of my line because....well....I prefer it that way.You realize, of course, that we can no longer be friends now, right? :hehe:
The comma thing is a pet peeve of mine, but I suspect it's because I'm a writer. It's not good prose to stick a punctuation mark at the beginning of a line to my mind. On the other hand, I'm not likely to berate people or go on tirades about it. I just shudder, groan, and continue on with the work because SQL Server actually scripts out stuff like that (even though it shouldn't).
I would certainly not go on a tirade about why my opinion is so much more relevant than somebody else's because <...ring knock...>. I can certainly live with trailing commas without being a big deal. But I really prefer to use: ColumnAlias = <Expression> which I can't seem to get people to do. And one line per column in the code please. 🙂
I'm on your team.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 8, 2017 at 7:35 am
Sean Lange - Wednesday, November 8, 2017 7:13 AMI would never
, start a statement
, with a semicolon
;I hope that somebody will toss me a life preserver...it is getting lonely out here in the water away from the island all by myself.
It could be worse...SELECT 1 AS [One];
;WITH CTE (
SELECT 2 AS [Two])
SELECT * FROM CTE;
Why is there a second terminator at the start of your WITH! >_<
Phil Parkin - Wednesday, November 8, 2017 7:24 AMSean Lange - Wednesday, November 8, 2017 7:19 AMI would certainly not go on a tirade about why my opinion is so much more relevant than somebody else's because <...ring knock...>. I can certainly live with trailing commas without being a big deal. But I really prefer to use: ColumnAlias = <Expression> which I can't seem to get people to do. And one line per column in the code please. 🙂
I'm on your team.
I tend to stick to one column per line, but sometimes, if the columns are related, I don't. For example on our vendor software the key is composite, so I have all 2/3 columns on one line for that.
I'll admit, I'm not a fan of the Alias = <expression> method.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 8, 2017 at 7:51 am
Thom A - Wednesday, November 8, 2017 7:35 AMSean Lange - Wednesday, November 8, 2017 7:13 AMI would never
, start a statement
, with a semicolon
;I hope that somebody will toss me a life preserver...it is getting lonely out here in the water away from the island all by myself.
It could be worse...
SELECT 1 AS [One];;WITH CTE (
SELECT 2 AS [Two])
SELECT * FROM CTE;
Why is there a second terminator at the start of your WITH! >_<Phil Parkin - Wednesday, November 8, 2017 7:24 AMSean Lange - Wednesday, November 8, 2017 7:19 AMI would certainly not go on a tirade about why my opinion is so much more relevant than somebody else's because <...ring knock...>. I can certainly live with trailing commas without being a big deal. But I really prefer to use: ColumnAlias = <Expression> which I can't seem to get people to do. And one line per column in the code please. 🙂
I'm on your team.
I tend to stick to one column per line, but sometimes, if the columns are related, I don't. For example on our vendor software the key is composite, so I have all 2/3 columns on one line for that.
I'll admit, I'm not a fan of the Alias = <expression> method.
I like the alias = expression method, makes it easier when switching from/to variables and aliases.
November 8, 2017 at 7:52 am
Brandie Tarvin - Wednesday, November 8, 2017 5:42 AMGrant Fritchey - Wednesday, November 8, 2017 5:33 AMBrandie Tarvin - Wednesday, November 8, 2017 5:24 AMSean Lange - Tuesday, November 7, 2017 2:14 PMI prefer a comma at the beginning of my line because....well....I prefer it that way.You realize, of course, that we can no longer be friends now, right? :hehe:
The comma thing is a pet peeve of mine, but I suspect it's because I'm a writer. It's not good prose to stick a punctuation mark at the beginning of a line to my mind. On the other hand, I'm not likely to berate people or go on tirades about it. I just shudder, groan, and continue on with the work because SQL Server actually scripts out stuff like that (even though it shouldn't).
Right there with you. We'll have to remove Sean from the island.... Or, everyone gets a free copy of SQL Prompt and then we can easily fix the syntax so that it's right for us.
Steve, we have a shill on the forums pretending to offer advice while trying to sell software! Should I report it? 😀
I'm sure I saw an additional word in Grant's post, I just had to add it so everyone can see it and you won't have to report it. 😀
November 8, 2017 at 7:55 am
Thom A - Wednesday, November 8, 2017 7:35 AMSean Lange - Wednesday, November 8, 2017 7:13 AMI would never
, start a statement
, with a semicolon
;I hope that somebody will toss me a life preserver...it is getting lonely out here in the water away from the island all by myself.
It could be worse...
SELECT 1 AS [One];;WITH CTE (
SELECT 2 AS [Two])
SELECT * FROM CTE;
Why is there a second terminator at the start of your WITH! >_<Phil Parkin - Wednesday, November 8, 2017 7:24 AMSean Lange - Wednesday, November 8, 2017 7:19 AMI would certainly not go on a tirade about why my opinion is so much more relevant than somebody else's because <...ring knock...>. I can certainly live with trailing commas without being a big deal. But I really prefer to use: ColumnAlias = <Expression> which I can't seem to get people to do. And one line per column in the code please. 🙂
I'm on your team.
I tend to stick to one column per line, but sometimes, if the columns are related, I don't. For example on our vendor software the key is composite, so I have all 2/3 columns on one line for that.
I'll admit, I'm not a fan of the Alias = <expression> method.
The main reason I like the Alias = style is because when debugging somebody else's code all I have to look at in the results is the column name. If the alias is the beginning of the line it makes it super easy to identify which line in the query is which column. When the alias is at the end of the line it is very difficult to figure out which column is which in the middle of a query with a lot of columns because the names are all over the place. This one took me quite some time to understand the benefit but was required in a previous job. Haha...I thought I remembered a blog from Aaron Bertrand on this topic. I found it. http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/23/bad-habits-to-kick-using-as-instead-of-for-column-aliases.aspx Not saying it is right but it is what I prefer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2017 at 7:59 am
Sean Lange - Wednesday, November 8, 2017 7:19 AMAnd one line per column in the code please. 🙂
I'd say one column per line. Otherwise, people will just try to include huge expressions that won't fit in the screen.
November 8, 2017 at 8:03 am
Luis Cazares - Wednesday, November 8, 2017 7:59 AMSean Lange - Wednesday, November 8, 2017 7:19 AMAnd one line per column in the code please. 🙂I'd say one column per line. Otherwise, people will just try to include huge expressions that won't fit in the screen.
Or the ones who will put every column on a single line with no spaces and then ask for help to debug something. :crazy: I think the biggest thing when it comes to style is the realization that there no prize for using as few characters as possible. Thing like Mycol=convert(numeric(9,2),ISNULL(a.col,b.col)) drive me nutty. I always say when in doubt, add more white space.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2017 at 8:04 am
Lynn Pettis - Wednesday, November 8, 2017 7:51 AMI like the alias = expression method, makes it easier when switching from/to variables and aliases.
I think that's part of the (my) problem with that syntax, I see an alias = <expression> and automatically think it's a variable, not a column. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 8, 2017 at 8:09 am
Sean Lange - Wednesday, November 8, 2017 8:03 AMOr the ones who will put every column on a single line with no spaces and then ask for help to debug something. :crazy: I think the biggest thing when it comes to style is the realization that there no prize for using as few characters as possible. Thing like Mycol=convert(numeric(9,2),ISNULL(a.col,b.col)) drive me nutty. I always say when in doubt, add more white space.
So many users recently have posted their questions and included their SQL unformatted, outside of the IF Code markup, and when you copy it out, it's all one line. I mean, honestly people, stuff like this is not readable! :crying:
CREATE TABLE #Sample (ID int, [TAB NAME] char(2), Approved bit); INSERT INTO #Sample VALUES (1,'AB',1),(1,'CD',0),(1,'EF',1), (2,'AB',0),(2,'CD',1),(2,'EF',1); SELECT * FROM #Sample; SELECT ID, CASE WHEN MAX(CASE WHEN [TAB NAME] = 'AB' THEN CAST(Approved AS int) ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS [isABapproved?], CASE WHEN MAX(CASE WHEN [TAB NAME] = 'CD' THEN CAST(Approved AS int) ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS [isCDapproved?], CASE WHEN MAX(CASE WHEN [TAB NAME] = 'EF' THEN CAST(Approved AS int) ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS [isEFapproved?] FROM #Sample GROUP BY ID; DROP TABLE #Sample;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 8, 2017 at 8:11 am
Phil Parkin - Wednesday, November 8, 2017 7:24 AMSean Lange - Wednesday, November 8, 2017 7:19 AMBrandie Tarvin - Wednesday, November 8, 2017 5:24 AMSean Lange - Tuesday, November 7, 2017 2:14 PMI prefer a comma at the beginning of my line because....well....I prefer it that way.You realize, of course, that we can no longer be friends now, right? :hehe:
The comma thing is a pet peeve of mine, but I suspect it's because I'm a writer. It's not good prose to stick a punctuation mark at the beginning of a line to my mind. On the other hand, I'm not likely to berate people or go on tirades about it. I just shudder, groan, and continue on with the work because SQL Server actually scripts out stuff like that (even though it shouldn't).
I would certainly not go on a tirade about why my opinion is so much more relevant than somebody else's because <...ring knock...>. I can certainly live with trailing commas without being a big deal. But I really prefer to use: ColumnAlias = <Expression> which I can't seem to get people to do. And one line per column in the code please. 🙂
I'm on your team.
Me too.
Why would you want to hide the most significant property of a column - its name - in a ragged right?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 8, 2017 at 8:24 am
ChrisM@Work - Wednesday, November 8, 2017 8:11 AMPhil Parkin - Wednesday, November 8, 2017 7:24 AMSean Lange - Wednesday, November 8, 2017 7:19 AMBrandie Tarvin - Wednesday, November 8, 2017 5:24 AMSean Lange - Tuesday, November 7, 2017 2:14 PMI prefer a comma at the beginning of my line because....well....I prefer it that way.You realize, of course, that we can no longer be friends now, right? :hehe:
The comma thing is a pet peeve of mine, but I suspect it's because I'm a writer. It's not good prose to stick a punctuation mark at the beginning of a line to my mind. On the other hand, I'm not likely to berate people or go on tirades about it. I just shudder, groan, and continue on with the work because SQL Server actually scripts out stuff like that (even though it shouldn't).
I would certainly not go on a tirade about why my opinion is so much more relevant than somebody else's because <...ring knock...>. I can certainly live with trailing commas without being a big deal. But I really prefer to use: ColumnAlias = <Expression> which I can't seem to get people to do. And one line per column in the code please. 🙂
I'm on your team.
Me too.
Why would you want to hide the most significant property of a column - its name - in a ragged right?
How is the name the most important property? The expression is far more relevant to functionality and ctrl + f isn't hard 😛
November 8, 2017 at 8:31 am
ZZartin - Wednesday, November 8, 2017 8:24 AMChrisM@Work - Wednesday, November 8, 2017 8:11 AMPhil Parkin - Wednesday, November 8, 2017 7:24 AMSean Lange - Wednesday, November 8, 2017 7:19 AMBrandie Tarvin - Wednesday, November 8, 2017 5:24 AMSean Lange - Tuesday, November 7, 2017 2:14 PMI prefer a comma at the beginning of my line because....well....I prefer it that way.You realize, of course, that we can no longer be friends now, right? :hehe:
The comma thing is a pet peeve of mine, but I suspect it's because I'm a writer. It's not good prose to stick a punctuation mark at the beginning of a line to my mind. On the other hand, I'm not likely to berate people or go on tirades about it. I just shudder, groan, and continue on with the work because SQL Server actually scripts out stuff like that (even though it shouldn't).
I would certainly not go on a tirade about why my opinion is so much more relevant than somebody else's because <...ring knock...>. I can certainly live with trailing commas without being a big deal. But I really prefer to use: ColumnAlias = <Expression> which I can't seem to get people to do. And one line per column in the code please. 🙂
I'm on your team.
Me too.
Why would you want to hide the most significant property of a column - its name - in a ragged right?How is the name the most important property? The expression is far more relevant to functionality and ctrl + f isn't hard 😛
Simple - if you don't know the name of the column the expression belongs too, it doesn't matter how clever the expression is, it's homeless!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 8, 2017 at 8:54 am
I'm a speed reader. The way I read items, things have to be bunched together in order for me to comprehend and consume properly. If columns are each on their own line, it messes with my head and I spend twice as much time (or more) trying to understand the code.
I put CASE statements on their own line. IF clauses on their own line. Long expressions on their own line (or indented down several lines). But a majority if regular columns are one right after another on the same line until I get about an inch away from the right side of the screen. Also, the Alias = Column thing I'd never seen until last year and it threw me for a loop. I use Column AS Alias. It's a standard here that column aliases *must* use the AS keyword so we know the person writing the code didn't accidentally forget a comma between two column names. Which actually has happened more often then you might think.
November 8, 2017 at 9:15 am
Brandie Tarvin - Wednesday, November 8, 2017 8:54 AMI'm a speed reader. The way I read items, things have to be bunched together in order for me to comprehend and consume properly. If columns are each on their own line, it messes with my head and I spend twice as much time (or more) trying to understand the code.I put CASE statements on their own line. IF clauses on their own line. Long expressions on their own line (or indented down several lines). But a majority if regular columns are one right after another on the same line until I get about an inch away from the right side of the screen. Also, the Alias = Column thing I'd never seen until last year and it threw me for a loop. I use Column AS Alias. It's a standard here that column aliases *must* use the AS keyword so we know the person writing the code didn't accidentally forget a comma between two column names. Which actually has happened more often then you might think.
This is where something like SQL Prompt is nice. Configure how you want things and use Ctrl-K,Ctrl-Y to format the code the way you like. When you send it to someone else, they can do the same thing to put it in a format that is easier for them.
Me, one column per line. I have moved to the leading comma, but mostly because I have been finding myself commenting columns toward the end of the select list rather than at the beginning. I still refuse to accept the ; as a beginninator. That will always belong at the end of a statement.
November 8, 2017 at 9:30 am
ChrisM@Work - Wednesday, November 8, 2017 8:11 AMPhil Parkin - Wednesday, November 8, 2017 7:24 AMSean Lange - Wednesday, November 8, 2017 7:19 AMBrandie Tarvin - Wednesday, November 8, 2017 5:24 AMSean Lange - Tuesday, November 7, 2017 2:14 PMI prefer a comma at the beginning of my line because....well....I prefer it that way.You realize, of course, that we can no longer be friends now, right? :hehe:
The comma thing is a pet peeve of mine, but I suspect it's because I'm a writer. It's not good prose to stick a punctuation mark at the beginning of a line to my mind. On the other hand, I'm not likely to berate people or go on tirades about it. I just shudder, groan, and continue on with the work because SQL Server actually scripts out stuff like that (even though it shouldn't).
I would certainly not go on a tirade about why my opinion is so much more relevant than somebody else's because <...ring knock...>. I can certainly live with trailing commas without being a big deal. But I really prefer to use: ColumnAlias = <Expression> which I can't seem to get people to do. And one line per column in the code please. 🙂
I'm on your team.
Me too.
Why would you want to hide the most significant property of a column - its name - in a ragged right?
Me three. I like the Alias = whatever syntax. I'm not so stuck on one column per line and don't generally use leading commas, but I'm not going to make a big stink about it. The bottom line in all this is readability. If the code can be read and maintained, it doesn't really matter to me.
Using a semicolon as a begininator is another story, but I'm not going to lose any sleep over it being posted on a forum. I'll never do it because it's just plain wrong.
Viewing 15 posts - 60,316 through 60,330 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply