SQL Server Name Convention and T-SQL Programming Style
-
Comments posted to this topic are about the item SQL Server Name Convention and T-SQL Programming Style
-
I hate the way this site does NOT handle tables. I have a screen wide enough to display the whole table but it's still formatted super narrow so someone can read it on a bloody phone. Well, kind of read it... you can't scroll left or right within the same screen even on a monster screen. Here's what it looks like on my screen, which is obviously not a phone screen...
--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) -
On the subject of the article itself, thanks for taking the time to put something so extensive together.
As you said, though... "The recommendations in this article are not the ultimate truth. Please consider this article as a rule template that you can adapt to your needs". There's a whole lot that I'd disagree with (tbl-ing, 30 character name limits, quoted alias names instead of bracketed, etc, for example) but then most people would disagree with mine, as well.
With that being said, hopefully it will inspire shops to make one of their own and actually enforce it.
--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) -
I'm with Jeff on this - lots of what is on that are personal preferences and should be decided by each team/organization
For example I do not allow any uppercase so all functions/keyworks are lowercase - object names/attributes are all PascalCase - no camelCase allowed
and basically if any "name" can not be specified without square brackets/quotes then its considered invalid and can't be used.
A few other things - but for example isnumeric vs try_convert - neither should be used to test for numeric/date as both fail to convert particular values in that they "think" its a valid number/date when in reality they are not.
But there are some good thinks in any case.
-
Well, I got to the first line of the article "Database UPPERCASE". I just wondered why you should have a database in uppercase? What if it consisted of 2 or more words, so what do you do then, separate the words with an underscore? What if it was the EXPERTSEXCHANGE or PENISLAND database?
-
As mentioned by Jeff tbl-ing is really setting up an environment for bad things. What happens when you have a table tblEmployee and you need to make some changes but you have some other code you don't want to change. So you create a view named tblEmployee and change the name of the base table to something else. Now you have a view with the table prefix. This actually happened at a client I was working with at one point. It was so confusing because you couldn't trust the tbl-ing to actually be what the name claimed. This is one reason why putting the datatype into the name has fallen out of favor. Not just in databases but in all of programming.
Column prefixes of PK_ and FK_ brings out one of my biggest pet peeves. That is columns changing their name based on the table they are in.
This is a great starting point for any shop to put together and document naming conventions. Thanks for taking the time to put this together and for sharing it.
_______________________________________________________________
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/ - frederico_fonseca wrote:
I'm with Jeff on this - lots of what is on that are personal preferences and should be decided by each team/organization
For example I do not allow any uppercase so all functions/keyworks are lowercase - object names/attributes are all PascalCase - no camelCase allowed
and basically if any "name" can not be specified without square brackets/quotes then its considered invalid and can't be used.
A few other things - but for example isnumeric vs try_convert - neither should be used to test for numeric/date as both fail to convert particular values in that they "think" its a valid number/date when in reality they are not.
But there are some good thinks in any case.
I would have a hard time with the all lowercase function/keyword thing. Love the no camelcase thing. I'll say "IT DEPENDS" on the IsNumeric/Try Convert thing.
--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) -
Generally, I am in agreement with this article. However, Naming Conventions and coding styles are not just tools of standardization; they must allow quick and easy interpretation with no ambiguity. Through the years I have experimented with many different types of coding style and found that minimal scrolling is as important as white space. That is, the practice of putting each column name in a SELECT list on a separate line does not make it easier for me to interpret the code. When the data is being manipulated using functions, UDFs, etc. putting columns on a separate line with an alias to the right has value, but otherwise a list of columns should be placed in a block so the eye can view it without having to scroll the editor screen. As an example: When in an INSERT block, I have found it is much preferable to group about five columns on each line in the INSERT column list and the SELECT column list, making it very easy to identify what column in the SELECT is going into what column in the INSERT.
Another practice that I found problematic is putting commas to the left of the columns in a list. It has no value or me; it does make the final line easy to copy but not the first line. Anyway, I am more interested in reading the column name and the comma to the left gets in the way; I can ignore it at the end of the column name in the same way as I ignore periods and commas at the end of a line in text.
-
I've written an entire book on this topic (Joe Celko's SQL programming style, ISBN 9780120887972). No, I am not going to post 200+ pages of text; you can get a copy from Amazon to take a look at it or check it out of the library.
It was largely based on the research that was done back in the 1970s, when software engineering was a magic buzzword. I was working for AIRMICS, an Army research group based at Georgia Tech. And we were contracting with various universities to do actual research on the readability of code.
Readability of text is measurable and not a matter of style or opinion. This is why my book includes "rationale" and "exceptions" subsections within each rule. Back then, we had to look at movies of people reading the screen or print out to manually track their eye movement. However, you could claim that all research is the study of graduate students and new employees who get to be guinea pigs 🙂
Another consideration applies to RDBMS is that the data element names will be used in places other than SQL. Some ISO standards are case-sensitive, and some are not. None of them allow embedded spaces. Some use different punctuation marks in their naming conventions. The Metadata Committee, ISO 11179, and other industry-standard groups have set up some conventions that are well worth following.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
This is a helpful article with a lot of useful tips and documentation to support those tips.
One thing I noticed missing is location of creating temporary tables in stored procedures. I regularly have to correct procedures created by others and reiterate the importance of placing temporary table creation at the beginning of stored procedures to prevent recompiling.
-
The entire plural column is 'no' including temporal tables. If tables are collections of rows then they should be plural, no? If the table name is 'orders' then one row is one order. I try to make all table names plural.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- Jeff Moden wrote:
I hate the way this site does NOT handle tables. I have a screen wide enough to display the whole table but it's still formatted super narrow so someone can read it on a bloody phone. Well, kind of read it... you can't scroll left or right within the same screen even on a monster screen. Here's what it looks like on my screen, which is obviously not a phone screen...
Hi, Jeff. You can use github version with headers, back to top and other useful format options (I tried to format article but wordpress editor is sucks on this blog and cant work with tables): https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Name%20Convention%20and%20T-SQL%20Programming%20Style.md
- Jeff Moden wrote:
On the subject of the article itself, thanks for taking the time to put something so extensive together.
As you said, though... "The recommendations in this article are not the ultimate truth. Please consider this article as a rule template that you can adapt to your needs". There's a whole lot that I'd disagree with (tbl-ing, 30 character name limits, quoted alias names instead of bracketed, etc, for example) but then most people would disagree with mine, as well.
With that being said, hopefully it will inspire shops to make one of their own and actually enforce it.
- tbl-ing: it is strange that most readers decided that I propose using the word table in the table name (most likely an unsuccessful example - MyTable means for example OrganizationIncome or Person table name without tbl-ing)
- 30 character name limits: only for database and schema name, for our purposes this is more than enough and the main reason is the convenience when working with Excel (hi limit to 31 characters per sheet name) and some other programs. I do not advocate this option, you can safely use 128 symbols in your convention.
- quoted alias name work like a charm in any database (not only SQL Server), why not use ANSI standard?
- Jonathan AC Roberts wrote:
Well, I got to the first line of the article "Database UPPERCASE". I just wondered why you should have a database in uppercase? What if it consisted of 2 or more words, so what do you do then, separate the words with an underscore? What if it was the EXPERTSEXCHANGE or PENISLAND database?
d why you should have a database in uppercase?: historically, in our development team, we mainly had novice developers who were irresponsible in relation to various database settings, and in particular the DROP DATABASE statement. Therefore, uppercase for the database name promotes a more careful attitude to any code that modifies or works with database.
What if it consisted of 2 or more words: in our practice, a beautiful database name in one or several words (for example, Person or ExpertsExchange) does not carry any payload and can be easily replaced by, for example, DBPROD01.
I do not advocate this option, you can safely use any convention for database name - the main thing is that it be consistent for the whole team.
- Sean Lange wrote:
As mentioned by Jeff tbl-ing is really setting up an environment for bad things. What happens when you have a table tblEmployee and you need to make some changes but you have some other code you don't want to change. So you create a view named tblEmployee and change the name of the base table to something else. Now you have a view with the table prefix. This actually happened at a client I was working with at one point. It was so confusing because you couldn't trust the tbl-ing to actually be what the name claimed. This is one reason why putting the datatype into the name has fallen out of favor. Not just in databases but in all of programming.
Column prefixes of PK_ and FK_ brings out one of my biggest pet peeves. That is columns changing their name based on the table they are in.
This is a great starting point for any shop to put together and document naming conventions. Thanks for taking the time to put this together and for sharing it.
Hi, Sean. For tbl-ing see my answer to Jeff.
Column prefixes of PK_ and FK_ brings out one of my biggest pet peeves: it is not column name prefix - it is primary key name and foreign key name prefixes. For Table Column name see Table Column row. I do not advocate this option, you can safely use any convention for keys.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply