SQL Server Name Convention and T-SQL Programming Style

Question

SQL Server Name Convention and T-SQL Programming Style

Viewing 13 posts - 16 through 27 (of 27 total)

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

Konstantin Taranov SSCertifiable Points: 6488 More actions · Answer 1

Joseph M. Steinbrunner wrote:

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.

Hi, Joseph. Could you describe this tip more detailed and give some code examples please?

Konstantin Taranov SSCertifiable Points: 6488 More actions · Answer 2

Steve Collins wrote:

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.

Hi, scdecade. It is holy war and you need choose your side in this battle (also, be prepare for tabs with spaces discussion).

The main reason for our choice of the singular for naming database objects is that we do not know English very well and we don’t have time to google the plural form of English words. We also try to protect the environment and use fewer characters in the code.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 3

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?

I prefer all uppercase with underscores.

But then I started working in DB with case sensitive turned on and table names in upper case with underscores so I just got used to never taking my finger off the shift key 😛

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

jcelko212 32090 wrote:

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.

Another book on my do not purchase list.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

I have gone to the extreme of using [ and ] around almost everything when coding, especially when writing dynamic SQL. I have found that there are individuals that have no idea what reserved words or special characters do when you have to use dynamic SQL to accomplish a variety of tasks. It has just become basic self defense on my part. And yes, when writing dynamic SQL I use QUOTENAME to put the [ and ] around the object names instead of hardcoding them in the dynamic SQL.

You do what you have to do when you inherit the system you have to support.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

Konstantin Taranov wrote:

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%5B/quote%5D

I know I can... I just shouldn't have to. 😉 It's not your fault... it's the fault of forum software.

--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)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Konstantin Taranov wrote:

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.

<li style="list-style-type: none;">
<li style="list-style-type: none;">
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)

<li style="list-style-type: none;">
<li style="list-style-type: none;">
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.

<li style="list-style-type: none;">
<li style="list-style-type: none;">
quoted alias name work like a charm in any database (not only SQL Server), why not use ANSI standard?

You've mistaken what I mean by "tbl-ing". Although the name would make it seem that it only has to do with tables, it actually has to do with "Hungarian Notation". The name "tbl-ing" has become popular as a replacement term for "Hungarian Notation" in SQL Server because a lot of people name their tables with the "tbl_" prefix. You did not.

https://www.computerhope.com/jargon/h/hungarian-notation.htm

Although the use of prefixes like PK_, FK_, AK_, IX_, and IXC_ seem to have been accepted, a lot of people (including myself) dislike prefixes for tables, views, procedures, functions, triggers, synonyms, etc, unless there's some really good reason to use them. You used "tbl_ing" for several of those objects as a "prefix" recommendation.

As you said elsewhere, though, arguing about such a thing would constitute a "holy war" or, more likely, an argument about which way toilet paper should be hung in the holder or leaving the toilet seat up 😀 . And that was my point... there are a whole lot of things in your good article that are choices rather than "Best Practices" that I'd rather not see in code. Some might even be considered to be dangerous. For example, I don't believe that specifying all caps for database names is going to work real well on case sensitive servers and so code written in such a fashion may have to go through one hell of a search'n'change effort when porting code from case-insensitive servers to case-sensitive servers especially for system databases.

And, again, I don't hold you responsible for any of that. As you said at the beginning of the article, these ARE, in fact, mostly personal choices and that's not what the article is about. The article is actually about setting up a standard and following it.

Unfortunately (for me, at least), a lot of people may follow the article to the "T" when it comes to those choices.

--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)

gserdijn Hall of Fame Points: 3280 More actions · Answer 8

Good work. I'd like to suggest a few additions with regard to your examples:

Only use IF with BEGIN and END. That should be mandatory in my opinion.
CONCAT is your friend. It will make your dynamic SQL and debugging code easier to read - and a lot more robust.

Dutch Anti-RBAR League

Steve Collins SSCrazy Points: 2068 More actions · Answer 9

gserdijn wrote:

<li style="list-style-type: none;">
Only use IF with BEGIN and END. That should be mandatory in my opinion.

So this...

if @x=1
begin
return 'xyz';
end
else
begin
return 'abc';
end

...is preferable to?

if @x=1
return 'xyz';
else
return 'abc';

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

gserdijn Hall of Fame Points: 3280 More actions · Answer 10

I'd write it like this:

IF @X = 1 BEGIN
    RETURN 'xyz';
END
ELSE BEGIN
    RETURN 'abc';
END;

Indeed preferable, since it should prevent errors like:

IF @X = 1
    SET @Rc = 'xyz';
    /* and then someone else adds some code for the same condition later ... */
    SET @ConditionMet = 1;

And those errors happen, unfortunately.

Dutch Anti-RBAR League

Konstantin Taranov SSCertifiable Points: 6488 More actions · Answer 11

gserdijn wrote:

Good work. I'd like to suggest a few additions with regard to your examples:
<li style="list-style-type: none;">
Only use IF with BEGIN and END. That should be mandatory in my opinion.
<li style="list-style-type: none;">
CONCAT is your friend. It will make your dynamic SQL and debugging code easier to read - and a lot more robust.

Hi, gserdijn. Great thanks for suggestions. Absolutely agree with if begin end (added to my to-do list).

Contactfh functiona has 2 drawbacks: it is not working for 2008 SQL server and not ANSI function.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 12

gserdijn wrote:

Dutch Anti-RBAR League

Just noticed... I DO like your signature line. 😀 😀 😀

--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)

Konstantin Taranov SSCertifiable Points: 6488 More actions · Answer 13

gserdijn wrote:

I'd write it like this:
IF @X = 1 BEGIN
    RETURN 'xyz';
END
ELSE BEGIN
    RETURN 'abc';
END;
Indeed preferable, since it should prevent errors like:
IF @X = 1
    SET @Rc = 'xyz';
    /* and then someone else adds some code for the same condition later ... */
    SET @ConditionMet = 1;
And those errors happen, unfortunately.

Great thanks for this suggestion, added via https://github.com/ktaranov/sqlserver-kit/commit/d31e2c7740bb56fc210fb044fcd5dc9bcde0027f