September 16, 2015 at 9:40 am
Lynn Pettis (9/16/2015)
Sean Lange (9/16/2015)
Kristen-173977 (9/16/2015)
Sean Lange (9/16/2015)
ClientID is a great name. It can be named ClientID in every table and you shouldn't end up with a name collision.I'm never sure where that approach starts-and-ends. ClientID I get - so in the Order Header table I can expect to find a ClientID column, that will join to Customer/Client table.
What about other columns in Customer/Client table that have "common" names, which will also be found in other tables e.g. FirstName (lets assume that exists in Customer, Staff, maybe Supplier)
Or is it just PKey Columns?
I would say that things like FirstName are perfectly fine. This is where aliases become really useful. c.FirstName is obviously the Customer table but s.FirstName indicates the Staff table.
Sean Lange (9/16/2015)
I love a good discussion/debate.Discussion for another thread, but I'm in the process of changing coding-style from having commas at right-end of line to left-end. Definitely got a love-hate relationship with that, and still debating if it is a good choice or not ...
This one has been discussed around here before. I used to be a "right-ender" but am now a "left-sider". I find it easier most of the time to develop on the left side because I can just comment out an entire line and it removes that one from the return. Even easier if you stick some silly constant as a first column while developing so you know you always have a first column. I have never heard any compelling "arguments" that either is actually a better approach, this one really boils down to preference, and maybe shop standards.
Just as long as people use the semicolon (;) as a statement terminator not a begininator. That is my pet peeve.
I am reminded of this thread where many of us vented our sql pet peeves. http://www.sqlservercentral.com/Forums/Topic1575686-61-1.aspx
_______________________________________________________________
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/
September 16, 2015 at 10:05 am
Lynn Pettis (9/16/2015)
Just as long as people use the semicolon (;) as a statement terminator not a begininator. That is my pet peeve.
I saw one argument to using it as a begininator, as I recall it was to ensure that the previous statement block was properly terminated and that nothing was left hanging when the next block began. I think it was a defensive approach when doing a cut/paste of someone else's code, especially since the use of it as a terminator is so unevenly applied. (raises hand in acknowledgment that I'm definitely guilty of this, but trying to do better)
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
September 16, 2015 at 10:11 am
Sean Lange (9/16/2015)
c.FirstName is obviously the Customer table but s.FirstName indicates the Staff table.
For me, that's where it starts to fall down. I don't know if S is Staff or Supplier. Of course I can look at the table list and check, but stf_FirstName or sup_FirstName tells me straight away. Also my personal opinion is that in the situation where Staff and Supplier tables are both used in the same FROM/JOIN I am at risk from Staff being aliased as S and Supplier as SU and I accidentally use the S alias when I should have used SU - the code I have written is legal .. but wrong ... so will waste time (either an immediate forehead-thump and cry of "IDIOT" 🙂 or more expensively if it goes unnoticed until after rollout to Production).
We also find that if we want to change something about Staff FirstName - e.g. we changed the DDL from VARCHAR to NVarchar - the ability to do a FIND across all source code for "stf_FirstName" is helpful. When we use a variable to reference a column we always include the column name (e.g. @stf_FirstName_START & @stf_FirstName_END), so they show up in a global find too.
Sean Lange (9/16/2015)
I used to be a "right-ender" but am now a "left-sider".
About the only thing that is annoying me is that left-siding ", " adds two characters to the width of the element's name, so on lines that define an object (SProc parameter or DECLARE variables etc.) the chances are, now, high that I will need one extra TAB to vertically align whatever is in the second column, and it seems to me that I am moving a lot of stuff further apart, as a consequence, which I think increases error risk. Not sure if that is a real error-generator, or just an "annoyance" as I'm new to it 😎 , but there has been a lot of cursing in my office in recent weeks!!
September 16, 2015 at 10:22 am
Kristen-173977 (9/16/2015)
Sean Lange (9/16/2015)
c.FirstName is obviously the Customer table but s.FirstName indicates the Staff table.For me, that's where it starts to fall down. I don't know if S is Staff or Supplier. Of course I can look at the table list and check, but stf_FirstName or sup_FirstName tells me straight away. Also my personal opinion is that in the situation where Staff and Supplier tables are both used in the same FROM/JOIN I am at risk from Staff being aliased as S and Supplier as SU and I accidentally use the S alias when I should have used SU - the code I have written is legal .. but wrong ... so will waste time (either an immediate forehead-thump and cry of "IDIOT" 🙂 or more expensively if it goes unnoticed until after rollout to Production).
This is where consistently using aliases has some advantages. Maybe Staff is st and Supplier is su. No approach is flawless for sure. What I really don't like about prefixes is it adds several keystrokes that you have to stop and think about what they are. Some argue it is good to stop and think about the table but for queries that get written time and time again it is annoying to say the least. I would argue that a suffix would be better than a prefix. At least then you can use the logical name and intellisense will pick up the nonsense part. 😉 Also, I find prefixes make it very difficult to find columns in a list because they all start with the same several characters.
We also find that if we want to change something about Staff FirstName - e.g. we changed the DDL from VARCHAR to NVarchar - the ability to do a FIND across all source code for "stf_FirstName" is helpful. When we use a variable to reference a column we always include the column name (e.g. @stf_FirstName_START & @stf_FirstName_END), so they show up in a global find too.
How often do you actually change things like datatypes? And when you do how often do you have to find every single occurrence and change anything?
Sean Lange (9/16/2015)
I used to be a "right-ender" but am now a "left-sider".About the only thing that is annoying me is that left-siding ", " adds two characters to the width of the element's name, so on lines that define an object (SProc parameter or DECLARE variables etc.) the chances are, now, high that I will need one extra TAB to vertically align whatever is in the second column, and it seems to me that I am moving a lot of stuff further apart, as a consequence, which I think increases error risk. Not sure if that is a real error-generator, or just an "annoyance" as I'm new to it 😎 , but there has been a lot of cursing in my office in recent weeks!!
I don't like to line up the datatypes. That would be on my list of pet peeves. 😉 Yet another preference thing but as I get older my vision is getting worse and I sometimes get the lines crossed when there are long expanses of white space after the column name. For me it is much harder to read those.
_______________________________________________________________
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/
September 16, 2015 at 11:06 am
Wayne West (9/16/2015)
Lynn Pettis (9/16/2015)
Just as long as people use the semicolon (;) as a statement terminator not a begininator. That is my pet peeve.I saw one argument to using it as a begininator, as I recall it was to ensure that the previous statement block was properly terminated and that nothing was left hanging when the next block began. I think it was a defensive approach when doing a cut/paste of someone else's code, especially since the use of it as a terminator is so unevenly applied. (raises hand in acknowledgment that I'm definitely guilty of this, but trying to do better)
I think of it as a crutch. I work hard at using the semicolon at the end of every statement. Considering that TSQL is moving that direction since Microsoft has deprecated NOT terminating statements with a semicolon, it only makes sense to use them consistently.
September 16, 2015 at 12:07 pm
Sean Lange (9/16/2015)
This is where consistently using aliases has some advantages. Maybe Staff is st and Supplier is su. ... What I really don't like about prefixes is it adds several keystrokes that you have to stop and think about what they are.
As broad as it is long? If I alias every table, consistently (bit of a risk that on a bad day someone might not be consistent - but I suppose Code Review takes care of that) then st.FirstName not much different to stf_FirstName?
I find prefixes make it very difficult to find columns in a list because they all start with the same several characters.
I don't have an opinion on that as I've been doing it prefix-way for too too long!! ... all I can say, given the too too long :cool:, is that I must be able to tune-out the prefixes as they don't distract me from the "meat". My prefixes are variable length (only by a char or two, but enough to prevent a list of columns from multiple tables lining up "By the meat"), which might well be an annoyance for some. But I suppose Table Aliases of different lengths cause the same indentation-offsets in a column list.
How often do you actually change things like datatypes?
Very rarely (well, perhaps a bit more often early in DEV cycle, but at that point a) very little code effected and b) all fresh in everyone's mind). Its not a justification for prefix naming for me, but having an exceptionally high level of confidence that we checked every possible usage of a column is reassuring.
when you do how often do you have to find every single occurrence and change anything?
I would say [subjectively speaking] that is "quite often". I'm trying to think of why we change the DDL of a column. Most common (although that's still "not many") is (n)Varchar(NN) where we decide to change the "NN" (longer, or shorter) but we have also done INT to BIGINT and NUMERIC(precision change). We don't really use DATE, still stuck in DATETIME-land, but I can imagine that we might have a spate of DATETIME-to-DATE changes for columns where we don't need/use the time. Such columns might change with no associated code changes though ... but I expect I'd like to double-check, belt&braces, if & when it happens.
Other usage is where we discover an "Issue" with a column. Perhaps we have been assuming it does X when in fact it does Y (particularly data we get from a 3rd party APP), so we want to go and double check, and fix :cool:, any usage that is busted.
Sean Lange (9/16/2015)
as I get older my vision is getting worse and I sometimes get the lines crossed when there are long expanses of white space after the column name. For me it is much harder to read those.
Me too, that's why I don't want the extra TAB space. But I am definitely sat on the fence about vertical alignment of things. I sometimes find it easier to read along-the-line, exactly as you describe, othertimes I can read it easier if its laid out "like a grid". (I'm dyslexic, which may well mean that what works for me is not bog standard!!)
Perhaps I should feature-request for SSMS to get alternate-line-highlighting ...
September 16, 2015 at 12:18 pm
Kristen-173977 (9/16/2015)
Sean Lange (9/16/2015)
This is where consistently using aliases has some advantages. Maybe Staff is st and Supplier is su. ... What I really don't like about prefixes is it adds several keystrokes that you have to stop and think about what they are.
As broad as it is long? If I alias every table, consistently (bit of a risk that on a bad day someone might not be consistent - but I suppose Code Review takes care of that) then st.FirstName not much different to stf_FirstName?
Correct but the column name remains the same in every table.
when you do how often do you have to find every single occurrence and change anything?
I would say [subjectively speaking] that is "quite often". I'm trying to think of why we change the DDL of a column. Most common (although that's still "not many") is (n)Varchar(NN) where we decide to change the "NN" (longer, or shorter) but we have also done INT to BIGINT and NUMERIC(precision change). We don't really use DATE, still stuck in DATETIME-land, but I can imagine that we might have a spate of DATETIME-to-DATE changes for columns where we don't need/use the time. Such columns might change with no associated code changes though ... but I expect I'd like to double-check, belt&braces, if & when it happens.
But surely you aren't joining on these string values right??? If you change the length of a name column you shouldn't have to do too much in the way of change to the database. Change the table and the CRUD stored procs. Unless they are being put into temp tables/variables there really isn't any other changes needed.
_______________________________________________________________
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/
September 16, 2015 at 12:27 pm
Sean Lange (9/16/2015)
But surely you aren't joining on these string values right???
Correct. Main thing I would be expecting to find would be an @MyColumn parameter to an SProc, or a DECLARE @MyColumn or a CREATE #TABLE / @TableVar with [MyColumn].
I might also find @MyColumn or [MyColumn] used somewhere within the code in a way that the outcome is now a problem. Ridiculous example but:
INSERT INTO MyLoggingTable(LinkedTableName, LinkedID_AsINT, TheMessage)
SELECT 'MyTable', MyID_NowBigInt, 'WARNING: Col3 IS NULL'
FROM MyTable
WHERE Col3 IS NULL
September 16, 2015 at 12:28 pm
P.S. On the day that that one ACTUALLY comes up I will be ROYALLY pissed off!!
September 16, 2015 at 12:33 pm
Sean Lange (9/16/2015)
Change the table and the CRUD stored procs
Probably not of any relevance to the discussion, but our CRUD are mechanically generated, so the chance of me missing a change in a CRUD procedure / trigger is tiny.
"mechanically generated" is a bit of a misnomer as we have to compare a freshly generated version with the actual, current, code in case it has any custom changes. I doubt even 5% of our CRUD have any custom changes ... I seriously need to change that so that the generator is able to determine if that table has custom code and if not then autoMagically replace the existing.
I'm on very intimate terms with my DIFF tool 🙂
September 16, 2015 at 12:42 pm
Kristen-173977 (9/16/2015)
P.S. On the day that that one ACTUALLY comes up I will be ROYALLY pissed off!!
LOL true dat!!!
_______________________________________________________________
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/
September 16, 2015 at 1:13 pm
Kristen-173977 (9/16/2015)
Sean Lange (9/16/2015)
But surely you aren't joining on these string values right???Correct. Main thing I would be expecting to find would be an @MyColumn parameter to an SProc, or a DECLARE @MyColumn or a CREATE #TABLE / @TableVar with [MyColumn].
I might also find @MyColumn or [MyColumn] used somewhere within the code in a way that the outcome is now a problem. Ridiculous example but:
INSERT INTO MyLoggingTable(LinkedTableName, LinkedID_AsINT, TheMessage)
SELECT 'MyTable', MyID_NowBigInt, 'WARNING: Col3 IS NULL'
FROM MyTable
WHERE Col3 IS NULL
That is one thing I liked about Oracle during the one year I worked with it. I could define the type of a variable based on the type of the column whose data would be stored in it. For example, if I had CUSTOMER_NAME VARCHAR2(30), I could declare a variable (not the actual syntax here as it has been five years now) DECLARE @CustomerName Table/Column%TYPE%. If the data type changed either in length, or from non-Unicode to Unicode, the type of the variable would automatically change when the procedure/script/function/whatever ran after the change to the table.
September 16, 2015 at 1:30 pm
Lynn Pettis (9/16/2015)
Kristen-173977 (9/16/2015)
Sean Lange (9/16/2015)
But surely you aren't joining on these string values right???Correct. Main thing I would be expecting to find would be an @MyColumn parameter to an SProc, or a DECLARE @MyColumn or a CREATE #TABLE / @TableVar with [MyColumn].
I might also find @MyColumn or [MyColumn] used somewhere within the code in a way that the outcome is now a problem. Ridiculous example but:
INSERT INTO MyLoggingTable(LinkedTableName, LinkedID_AsINT, TheMessage)
SELECT 'MyTable', MyID_NowBigInt, 'WARNING: Col3 IS NULL'
FROM MyTable
WHERE Col3 IS NULL
That is one thing I liked about Oracle during the one year I worked with it. I could define the type of a variable based on the type of the column whose data would be stored in it. For example, if I had CUSTOMER_NAME VARCHAR2(30), I could declare a variable (not the actual syntax here as it has been five years now) DECLARE @CustomerName Table/Column%TYPE%. If the data type changed either in length, or from non-Unicode to Unicode, the type of the variable would automatically change when the procedure/script/function/whatever ran after the change to the table.
Now that is pretty darned cool!!!!
_______________________________________________________________
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/
September 16, 2015 at 7:50 pm
useful discussion Sean, thanks for your time 🙂
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply