Post reply

Naming Conventions for SQL Server Objects

  • October 24, 2018 at 6:26 pm

    #2011052

    Jonathan AC Roberts - Wednesday, October 24, 2018 4:23 PM

    Jeff Moden - Wednesday, October 24, 2018 4:01 PM

    Jeffrey Williams 3188 - Wednesday, October 24, 2018 1:08 PM

    xsevensinzx - Wednesday, October 24, 2018 4:27 AM

    I used to think that it didn't matter as much either. Then I started using PyCharm for my Python IDE of choice. It measures the time saved from typing speeds and using shortcuts provided by the IDE. It calculates this as time saved currently and projects time saved if you continue the course. The amount of time saved is crazy good when you actually see the differences between someone who can code/think fast versus someone who cannot.

    When it comes to thinking about code, I consider that coding as well. Mostly because if I'm on the clock, that time is getting charged to the coding task. Not another task assigned to non-coding/thinking.

    I utilize SQL Assistant - which is an intellisense tool that works across multiple IDE's and systems.  Once configured - it actually reduces time spent on looking up column names, primary keys, join criteria and other shortcuts to coding.  It also has the ability to format the code according to the style(s) I prefer - which has the benefit of allowing me to review someone else's code much faster.

    Whether or not I utilize AS for the table alias all depends on formatting.

    FROM dbo.MyTable AS mt
    INNER JOIN dbo.OtherTable AS ot ON ot.key = mt.key

    FROM dbo.MyTable                   mt
    INNER JOIN dbo.OtherTable          ot ON ot.key = mt.key

    In the second example - for me at least - it is very clear what alias is assigned to the table regardless of whether or not AS is included and is actually easier to identify the alias and join criteria than in the former example.

    That's how I format aliases in the FROM clause, as well, except that I also vertically align the table names.  The vertical alignment (with or without the "AS") makes life quite a bit easier, especially in the face of many objects in the FROM clause.

    Ah, indentation! That's a subject in its own right.

    You said a mouthful there.  For the most part, I use a "river format", which has all the keywords right aligned on column 7 (there are some that don't fit, like the word TRUNCATE, which deserves special attention anyway) and every thing else is left aligned on column 9, leaving a "river" of blanks on column 8. 

    I also use the "alias = expression" method in SELECT lists (very much like in an UPDATE) because it's easier (for me, anyway) to quickly find column names and, usually, align all the "=" signs vertically because I also frequently need to copy the column aliases vertically for other things.  Yes, it takes a little extra time to do it that way but the benefits (for me, anyway) are great, especially for readability and copying sections of code for other things.

    I also didn't used to like leading commas.  That all changed for me when they made it so you could truly edit vertically.  Ol' "XEdit" was decades ahead of it's time.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 25, 2018 at 1:53 am

    #2011076

    Jeff Moden - Wednesday, October 24, 2018 4:01 PM

    Jeffrey Williams 3188 - Wednesday, October 24, 2018 1:08 PM

    xsevensinzx - Wednesday, October 24, 2018 4:27 AM

    I used to think that it didn't matter as much either. Then I started using PyCharm for my Python IDE of choice. It measures the time saved from typing speeds and using shortcuts provided by the IDE. It calculates this as time saved currently and projects time saved if you continue the course. The amount of time saved is crazy good when you actually see the differences between someone who can code/think fast versus someone who cannot.

    When it comes to thinking about code, I consider that coding as well. Mostly because if I'm on the clock, that time is getting charged to the coding task. Not another task assigned to non-coding/thinking.

    I utilize SQL Assistant - which is an intellisense tool that works across multiple IDE's and systems.  Once configured - it actually reduces time spent on looking up column names, primary keys, join criteria and other shortcuts to coding.  It also has the ability to format the code according to the style(s) I prefer - which has the benefit of allowing me to review someone else's code much faster.

    Whether or not I utilize AS for the table alias all depends on formatting.

    FROM dbo.MyTable AS mt
    INNER JOIN dbo.OtherTable AS ot ON ot.key = mt.key

    FROM dbo.MyTable                   mt
    INNER JOIN dbo.OtherTable          ot ON ot.key = mt.key

    In the second example - for me at least - it is very clear what alias is assigned to the table regardless of whether or not AS is included and is actually easier to identify the alias and join criteria than in the former example.

    That's how I format aliases in the FROM clause, as well, except that I also vertically align the table names.  The vertical alignment (with or without the "AS") makes life quite a bit easier, especially in the face of many objects in the FROM clause.

    I indent and align table names.
    I always use aliases when there is more than one table and always in subqueries and EXISTS/NOT EXISTS.
    I never use tablename.column
    I use [] for column aliases.
    Still use trailing comma, still can't quite get used to leading but then I am grumpy and stuck in my ways :Whistling:

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 25, 2018 at 8:01 am

    #2011141

    David Burrows - Thursday, October 25, 2018 1:53 AM

    Jeff Moden - Wednesday, October 24, 2018 4:01 PM

    Jeffrey Williams 3188 - Wednesday, October 24, 2018 1:08 PM

    xsevensinzx - Wednesday, October 24, 2018 4:27 AM

    I used to think that it didn't matter as much either. Then I started using PyCharm for my Python IDE of choice. It measures the time saved from typing speeds and using shortcuts provided by the IDE. It calculates this as time saved currently and projects time saved if you continue the course. The amount of time saved is crazy good when you actually see the differences between someone who can code/think fast versus someone who cannot.

    When it comes to thinking about code, I consider that coding as well. Mostly because if I'm on the clock, that time is getting charged to the coding task. Not another task assigned to non-coding/thinking.

    I utilize SQL Assistant - which is an intellisense tool that works across multiple IDE's and systems.  Once configured - it actually reduces time spent on looking up column names, primary keys, join criteria and other shortcuts to coding.  It also has the ability to format the code according to the style(s) I prefer - which has the benefit of allowing me to review someone else's code much faster.

    Whether or not I utilize AS for the table alias all depends on formatting.

    FROM dbo.MyTable AS mt
    INNER JOIN dbo.OtherTable AS ot ON ot.key = mt.key

    FROM dbo.MyTable                   mt
    INNER JOIN dbo.OtherTable          ot ON ot.key = mt.key

    In the second example - for me at least - it is very clear what alias is assigned to the table regardless of whether or not AS is included and is actually easier to identify the alias and join criteria than in the former example.

    That's how I format aliases in the FROM clause, as well, except that I also vertically align the table names.  The vertical alignment (with or without the "AS") makes life quite a bit easier, especially in the face of many objects in the FROM clause.

    I indent and align table names.
    I always use aliases when there is more than one table and always in subqueries and EXISTS/NOT EXISTS.
    I never use tablename.column
    I use [] for column aliases.
    Still use trailing comma, still can't quite get used to leading but then I am grumpy and stuck in my ways :Whistling:

    I pretty much do the same thing and don't really care how someone uses commas so long as they're consistent.  I do try to avoid the use of brackets and, I totally agree, I NEVER use "tablename.column".  I'm like you in that I also add table aliases to every column reference in the presence of any join or type of correlated subquery including the likes of APPLY.

    I also try to avoid the use of underscores unless there's a "system prefix" involved in object names, which some folks find easier to use than separate schemas but we have those, as well.  I want to "kill" when I see tables with a prefix of "tbl" or "tbl_" and especially when I see stored procedures named "sp" or "sp_".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 25, 2018 at 8:31 am

    #2011153

    Jeff Moden - Thursday, October 25, 2018 8:01 AM

    I also try to avoid the use of underscores unless there's a "system prefix" involved in object names, which some folks find easier to use than separate schemas but we have those, as well.  I want to "kill" when I see tables with a prefix of "tbl" or "tbl_" and especially when I see stored procedures named "sp" or "sp_".

    What about "syn_" for synonyms?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • October 25, 2018 at 8:42 am

    #2011157

    drew.allen - Thursday, October 25, 2018 8:31 AM

    Jeff Moden - Thursday, October 25, 2018 8:01 AM

    I also try to avoid the use of underscores unless there's a "system prefix" involved in object names, which some folks find easier to use than separate schemas but we have those, as well.  I want to "kill" when I see tables with a prefix of "tbl" or "tbl_" and especially when I see stored procedures named "sp" or "sp_".

    What about "syn_" for synonyms?

    Drew

    Yeah... that would also be a "sin". 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 26, 2018 at 9:30 am

    #2011369

    Jonathan AC Roberts - Wednesday, October 24, 2018 5:28 AM

    andrew gothard - Wednesday, October 24, 2018 5:10 AM

    JustMarie - Monday, October 22, 2018 9:16 AM

    David Walker-278941 - Monday, October 22, 2018 8:47 AM

    Table aliases (in Join statements) -- Just Say No!
    Your coding speed is probably not limited by your typing speed (thinking time is greater).  Table aliases in Join statements make code more obfuscated.  In my opinion.  A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.

    I strongly disagree with your aversion to table aliases. I DO agree that using the very lazy 'a', 'b', 'c' kin of alias is spectacularly unhelpful but using 'cust' for InternalSource74CustomerData is very helpful indeed. Not using AS when performing an alias is also quite lazy and makes the alias less obvious.

    I think that the key thing is actually clarity.  p.Name is drivel, Patient.Name is not - the maintenance programmer - even if it's future you - will have an easier job if a meaningful object name is used.  a. c. i. are not in any sense meaningful or useful and use of this approach basically strikes me a nothing more than willful obfuscation.
    On the other hand, ttdsls040500 is less meaningful to just about anyone (even if you recognise where that comes from) than <Company>SalesOrder.  In that case, certainly use an alias for the sake of clarity.  s. or t1 does not, in any way, provide clarity there.

    I don't think that matters too much; all the reader need do is glance down at the table name in the from and see that Patient is aliased to p then they are away. If you have no alias and use the entire table name as a prefix to every reference to every column the reading of the SQL becomes more difficult as it's overloaded with table names everywhere. If you have different schemas in your query do you also include the schema name to each reference to a column?

    "Glance down, … Then they are away"... except when the From is quite complex, and there are half a dozen tables that are aliased.  Why add the additional level of obfuscation?  The query is not "overloaded" with table names; the columns are clearly listed WITH their table names.  I don't think this is the right use of the word "overloaded".  The aliases add an additional layer of translation, which is fine for computers, harder for humans. Anyway, this has been hashed out enough, so I'll leave it now.

  • October 26, 2018 at 9:45 am

    #2011371

    David Walker-278941 - Friday, October 26, 2018 9:30 AM

    Jonathan AC Roberts - Wednesday, October 24, 2018 5:28 AM

    andrew gothard - Wednesday, October 24, 2018 5:10 AM

    JustMarie - Monday, October 22, 2018 9:16 AM

    David Walker-278941 - Monday, October 22, 2018 8:47 AM

    Table aliases (in Join statements) -- Just Say No!
    Your coding speed is probably not limited by your typing speed (thinking time is greater).  Table aliases in Join statements make code more obfuscated.  In my opinion.  A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.

    I strongly disagree with your aversion to table aliases. I DO agree that using the very lazy 'a', 'b', 'c' kin of alias is spectacularly unhelpful but using 'cust' for InternalSource74CustomerData is very helpful indeed. Not using AS when performing an alias is also quite lazy and makes the alias less obvious.

    I think that the key thing is actually clarity.  p.Name is drivel, Patient.Name is not - the maintenance programmer - even if it's future you - will have an easier job if a meaningful object name is used.  a. c. i. are not in any sense meaningful or useful and use of this approach basically strikes me a nothing more than willful obfuscation.
    On the other hand, ttdsls040500 is less meaningful to just about anyone (even if you recognise where that comes from) than <Company>SalesOrder.  In that case, certainly use an alias for the sake of clarity.  s. or t1 does not, in any way, provide clarity there.

    I don't think that matters too much; all the reader need do is glance down at the table name in the from and see that Patient is aliased to p then they are away. If you have no alias and use the entire table name as a prefix to every reference to every column the reading of the SQL becomes more difficult as it's overloaded with table names everywhere. If you have different schemas in your query do you also include the schema name to each reference to a column?

    "Glance down, … Then they are away"... except when the From is quite complex, and there are half a dozen tables that are aliased.  Why add the additional level of obfuscation?  The query is not "overloaded" with table names; the columns are clearly listed WITH their table names.  I don't think this is the right use of the word "overloaded".  The aliases add an additional layer of translation, which is fine for computers, harder for humans. Anyway, this has been hashed out enough, so I'll leave it now.

    Heh.,., nah... based on your response, not hashed out nearly enough.

    Consider the length and largely varying width of table names and some of the formulas that people write and understand how proper table aliases can greatly simplify the read of such complexities.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 26, 2018 at 10:13 am

    #2011375

    Jeff Moden - Friday, October 26, 2018 9:45 AM

    David Walker-278941 - Friday, October 26, 2018 9:30 AM

    Jonathan AC Roberts - Wednesday, October 24, 2018 5:28 AM

    andrew gothard - Wednesday, October 24, 2018 5:10 AM

    JustMarie - Monday, October 22, 2018 9:16 AM

    David Walker-278941 - Monday, October 22, 2018 8:47 AM

    Table aliases (in Join statements) -- Just Say No!
    Your coding speed is probably not limited by your typing speed (thinking time is greater).  Table aliases in Join statements make code more obfuscated.  In my opinion.  A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.

    I strongly disagree with your aversion to table aliases. I DO agree that using the very lazy 'a', 'b', 'c' kin of alias is spectacularly unhelpful but using 'cust' for InternalSource74CustomerData is very helpful indeed. Not using AS when performing an alias is also quite lazy and makes the alias less obvious.

    I think that the key thing is actually clarity.  p.Name is drivel, Patient.Name is not - the maintenance programmer - even if it's future you - will have an easier job if a meaningful object name is used.  a. c. i. are not in any sense meaningful or useful and use of this approach basically strikes me a nothing more than willful obfuscation.
    On the other hand, ttdsls040500 is less meaningful to just about anyone (even if you recognise where that comes from) than <Company>SalesOrder.  In that case, certainly use an alias for the sake of clarity.  s. or t1 does not, in any way, provide clarity there.

    I don't think that matters too much; all the reader need do is glance down at the table name in the from and see that Patient is aliased to p then they are away. If you have no alias and use the entire table name as a prefix to every reference to every column the reading of the SQL becomes more difficult as it's overloaded with table names everywhere. If you have different schemas in your query do you also include the schema name to each reference to a column?

    "Glance down, … Then they are away"... except when the From is quite complex, and there are half a dozen tables that are aliased.  Why add the additional level of obfuscation?  The query is not "overloaded" with table names; the columns are clearly listed WITH their table names.  I don't think this is the right use of the word "overloaded".  The aliases add an additional layer of translation, which is fine for computers, harder for humans. Anyway, this has been hashed out enough, so I'll leave it now.

    Heh.,., nah... based on your response, not hashed out nearly enough.

    Consider the length and largely varying width of table names and some of the formulas that people write and understand how proper table aliases can greatly simplify the read of such complexities.

    And especially if you are joining tables from multiple schemas and maybe even different databases either on the same server or different servers that may even have the same name.  Also remember that 3 and 4 part naming convention is deprecated in the select column list.  Using aliases helps with understanding where columns are coming from in a query.

  • October 26, 2018 at 10:27 am

    #2011379

    David Walker-278941 - Friday, October 26, 2018 9:30 AM

    Jonathan AC Roberts - Wednesday, October 24, 2018 5:28 AM

    andrew gothard - Wednesday, October 24, 2018 5:10 AM

    JustMarie - Monday, October 22, 2018 9:16 AM

    David Walker-278941 - Monday, October 22, 2018 8:47 AM

    Table aliases (in Join statements) -- Just Say No!
    Your coding speed is probably not limited by your typing speed (thinking time is greater).  Table aliases in Join statements make code more obfuscated.  In my opinion.  A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.

    I strongly disagree with your aversion to table aliases. I DO agree that using the very lazy 'a', 'b', 'c' kin of alias is spectacularly unhelpful but using 'cust' for InternalSource74CustomerData is very helpful indeed. Not using AS when performing an alias is also quite lazy and makes the alias less obvious.

    I think that the key thing is actually clarity.  p.Name is drivel, Patient.Name is not - the maintenance programmer - even if it's future you - will have an easier job if a meaningful object name is used.  a. c. i. are not in any sense meaningful or useful and use of this approach basically strikes me a nothing more than willful obfuscation.
    On the other hand, ttdsls040500 is less meaningful to just about anyone (even if you recognise where that comes from) than <Company>SalesOrder.  In that case, certainly use an alias for the sake of clarity.  s. or t1 does not, in any way, provide clarity there.

    I don't think that matters too much; all the reader need do is glance down at the table name in the from and see that Patient is aliased to p then they are away. If you have no alias and use the entire table name as a prefix to every reference to every column the reading of the SQL becomes more difficult as it's overloaded with table names everywhere. If you have different schemas in your query do you also include the schema name to each reference to a column?

    "Glance down, … Then they are away"... except when the From is quite complex, and there are half a dozen tables that are aliased.  Why add the additional level of obfuscation?  The query is not "overloaded" with table names; the columns are clearly listed WITH their table names.  I don't think this is the right use of the word "overloaded".  The aliases add an additional layer of translation, which is fine for computers, harder for humans. Anyway, this has been hashed out enough, so I'll leave it now.

    It is the right use of the word overloaded: "load with too great a burden or cargo."

Viewing 9 posts - 31 through 38 (of 38 total)

You must be logged in to reply to this topic. Login to reply