October 20, 2018 at 1:02 am
Comments posted to this topic are about the item Naming Conventions for SQL Server Objects
Best wishes,
Phil Factor
October 20, 2018 at 1:08 pm
Heh... maybe "Capitialize() instead of the past tense "capitalized". I also hate the use of all upper case or all lower case for user defined object names, especially in this day and age of things like Intelli-Sense and especially SQL Prompt.
Speaking of standards, if you look at objects and code that MS has written for SQL Server, you'll generally find no standard or, worse yet, many standards that have neither been followed to a "T" even within the same code and certainly not across code. It's like a bunch of kids were set to task with crayons that all wrote different "colors" of T-SQL... well... except for the one that writes set-based code. My favorite example of that shortfall is the code for sp_SpaceUsed mostly because it's been around for a very long time and they've only recently (2012) made a bit of a change to it. They also missed the opportunity to turn it into a system function that you could actually use a WHERE clause against it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2018 at 1:32 pm
As for the CMS standards that you provided a link to... there's some good advice here and there in it but, for the most part, BLEAH! Imagine using Hungarian Notation on all the variables and having prefixes on some objects and suffixes on others not to mention being limited to column names of just 18 characters. Heh... and there's no mention of sticking with a 2 part naming convention for objects.
Even though that mess was written 10 years ago, it's insane even for way back then. Unfortunately, many such standards end up in the same condition. I normally will kow tow to OPS ("Other People's Stuff" as a polite term) but need to remind the folks writing these things that being consistent isn't always the best thing if the standards are consistently wrong.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2018 at 2:23 pm
Some basic standards I'd like to see organisations adopt is to choose conventions to apply across all their projects:
Table Names - singular or plural e.g. Customer or Customers.
PK Identity Names - All Ids called Id or tablenameId e.g. CustomerId
I wish people would always use aliases for their tables and prefix all columns with the alias when writing a query. There is not much worse than coming across a query from a system you know little about with multiple table joins and someone hasn't used table aliases in the select columns and joins, you end up taking a lot of time just working out which table each column comes from.
My personal preference is for PK Identities to be named tablenameID. Then from that I prefer to have tables named in the singular as I wouldn't want to see a column called CustomersId; CustomerId is much better and when defining the table all you need to do is paste in the tableName and append 'Id' to it. Also, some singular words when pluralised you don't just add an 's' on the end.
I also don't like to have the same column named differently just because it's on a different table. This would all make it easier to transfer between projects and save time when writing code as little thought or knowledge is needed when joining columns together. It would also be easy to write a data dictionary for a system as all columns could be identifiable just from their column name (maybe with the exception of columns like Description, LongDescription, ShortDescription)
When I join two columns together I like them to be named with exactly the same name. For example, I would much rather see WHERE c.CustomerId = o.CustomerId than WHERE c.Id = o.CustomerId
October 22, 2018 at 3:14 am
Seems that most developers are still writing code like done in the 90s and have not moved on in naming schemes to what Intellisense allows now. Even though Intellisense has its issues with large scripts.
So things like SELECT c.name, o.dated, ... FROM Customer c, Orders o
The following is much easier to digest on first encounter:SELECT customer.name, order.dated, ...
FROM dbo.Customer AS customer
INNER JOIN dbo.Orders AS order
ON order.CustomerID = customer.CustomerID
In a BI / DW environment there can be up to 100 of columns (or more) that transform data from multiple tables. Using full names (whether Hungarian, CamelCase or Pascal) is much easier for beginners to follow. And changing jobs can make everybody pretty much a beginner again.
One of the best phrases I heard and took to heart is: "Code is for humans". As in code should read in a flow like a book, not a bunch of sticky notepads.
Looking at my old code I know I fail in this myself though new stuff gets written with following styling:
* Use " AS " between table and alias
* Use schemas - it is a lot easier to change schemas moving tables and then code will not break if suddenly two tables of with same object but different schema names exist
* Comment the Why?, not the How - business rules included in code are not as easily forgotten than the ones in a Word document
* Explicit alias names for code that is under source control
* Think how much less time I spent if code is clear and explicit instead of shorthand and think of the developers that have to continue and maintain what I started
* Document business requirements for which the code is written
October 22, 2018 at 7:44 am
Assigning useful names to unique keys, foreign keys, and check constraints (rather than simply retaining the GUI assigned default) is important, because these things appear in error messages.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 22, 2018 at 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.
October 22, 2018 at 8:47 am
Knut Boehnert - Monday, October 22, 2018 3:14 AMSo things likeSELECT c.name, o.dated, ... FROM Customer c, Orders o
The following is much easier to digest on first encounter:SELECT customer.name, order.dated, ...
FROM dbo.Customer AS customer
INNER JOIN dbo.Orders AS order
ON order.CustomerID = customer.CustomerIDWhy the AS clauses? What's wrong with this?
SELECT customer.name, order.dated, ...
FROM dbo.Customer
INNER JOIN dbo.Orders
ON order.CustomerID = customer.CustomerID
October 22, 2018 at 9:16 am
David Walker-278941 - Monday, October 22, 2018 8:47 AMTable 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.
October 22, 2018 at 9:27 am
David Walker-278941 - Monday, October 22, 2018 8:47 AMKnut Boehnert - Monday, October 22, 2018 3:14 AMSo things likeSELECT c.name, o.dated, ... FROM Customer c, Orders o
The following is much easier to digest on first encounter:SELECT customer.name, order.dated, ...
FROM dbo.Customer AS customer
INNER JOIN dbo.Orders AS order
ON order.CustomerID = customer.CustomerIDWhy the AS clauses? What's wrong with this?
SELECT customer.name, order.dated, ...
FROM dbo.Customer
INNER JOIN dbo.Orders
ON order.CustomerID = customer.CustomerID
Why the AS clauses? Well, the last query will fail to compile.
Second, why use table aliases? Because if you use logical short names for much larger table names it makes things easier and still allows you to look and column names in select lists, join and where conditions and know what table each column comes from in the query.
October 22, 2018 at 9:43 am
David Walker-278941 - Monday, October 22, 2018 8:47 AMTable 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.
Table aliases are sometimes required (such as self joins). I prefer to be consistent and always use aliases rather than to only use aliases when they are necessary.
I've also seen table names that are ridiculously long. I find that it obfuscates code much more to wade through long table names to find the columns being referenced.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 22, 2018 at 9:52 am
JustMarie - Monday, October 22, 2018 9:16 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable 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.
Oracle does not like AS alias which is why the AS tends to be omitted in SQL Server as well.
October 22, 2018 at 10:06 am
David Walker-278941 - Monday, October 22, 2018 8:47 AMTable 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.
When you have table names like FactVehicleUtilisationUnitsetDayGrain, DimComponentUtilisationFreeFormatText
you might well find that fvuudg and dcufft are much easier to read and follow.
My main point is that all column names should be prefixed by the alias for the table.SELECT name, dated, ...
FROM dbo.Customer c
INNER JOIN dbo.Orders o
ON o.CustomerID = c.CustomerID
When someone doesn't prefix the column names with the table aliases how are you supposed to work out which table each column comes from?
October 22, 2018 at 11:16 am
Jonathan AC Roberts - Monday, October 22, 2018 10:06 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable 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.When you have table names like FactVehicleUtilisationUnitsetDayGrain, DimComponentUtilisationFreeFormatText
you might well find that fvuudg and dcufft are much easier to read and follow.
My main point is that all column names should be prefixed by the alias for the table.SELECT name, dated, ...
FROM dbo.Customer c
INNER JOIN dbo.Orders o
ON o.CustomerID = c.CustomerID
When someone doesn't prefix the column names with the table aliases how are you supposed to work out which table each column comes from?
He's probably talking about using the actual table names instead of aliases. That's ok if you don't violate 2 part naming (3 and 4 part naming in the SELECT list has been deprecated and it's a great practice to not use 3 or 4 part naming in any code other than in synonyms).
Personally, we use longer table names and avoid the horror that a lot of abbreviations can bring, so using long table names would make the code a difficult read at best. Besides, we also use table aliases as a form of embedded documentation especially if a table is called more than once in the same query. There's nothing worse than finding aliases based only on the table name when you find junk like Customer1 and Customer2 for table aliases. A simple example would be to alias one of the instances of the Customer table as "mgr" and the other "emp" if you have code that's working with such a concept.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2018 at 2:38 pm
Jonathan AC Roberts - Monday, October 22, 2018 10:06 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable 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.When you have table names like FactVehicleUtilisationUnitsetDayGrain, DimComponentUtilisationFreeFormatText
you might well find that fvuudg and dcufft are much easier to read and follow.
My main point is that all column names should be prefixed by the alias for the table.SELECT name, dated, ...
FROM dbo.Customer c
INNER JOIN dbo.Orders o
ON o.CustomerID = c.CustomerID
When someone doesn't prefix the column names with the table aliases how are you supposed to work out which table each column comes from?
Are you serious? Really? I would find "fvuudg" and "dcufft" WAY harder to follow than FactVehicleUtilisationUnitsetDayGrain and DimComponentUtilisationFreeFormatText. And yes, as Jeff said, I think all column names should have the table name also and not JUST the column name.
Sheesh, if I had to read code with table aliases like fvuudg I would poke my eyes out with a dull spoon.
Someone advocated using the first letter of the table name as the table alias in a Join statement. Therefore, in some expressions, "c" means Customer, and in some expressions, it means Charges. Customer and Charges are far easier than seeing a "c" which SOMETIMES means Customer and sometimes Charges.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply