Rule of Thumb in terms of good programming practice

Question

Rule of Thumb in terms of good programming practice

dba321

Hall of Fame

Points: 3917
More actions
April 16, 2007 at 1:32 pm

#116941

In terms of good practice, should you always bracket field, tables, etc.? if so, why?
Example: SELECT @CountryName = Descrip FROM [mydb].[dbo].[syCountry]

Viewing 15 posts - 1 through 15 (of 30 total)

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

David McFarland SSChampion Points: 11815 More actions · Answer 1

It's a good practice, but many people don't do it. The reason why is so that you don't confuse the parser by using reserved words, spaces in column names, etc. To see this in action, create the following table:

CREATE TABLE tmpReservedTest

(

[SELECT] tinyint

)

INSERT INTO tmpReservedTest VALUES (123)

Now try the following:

SELECT SELECT FROM tmpReservedTest

Won't work so well. But this, on the other hand, works fine:

SELECT [SELECT] FROM tmpReservedTest

In reality, many DBAs would much rather not use the brackets, and are careful with avoiding reserved words as column names. If there is even a slight chance that you'll run into a reserved word, say you've created a process that dynamically creates code based on a passed column name (or user, or database name, etc.) over which you have no control, then definitely use brackets.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

This is only required if the names contain special characters that would otherwise render them unusable. The practice guidelines for our shop state that such names should not be used and that the brackets would be used only for names from vendors supplied systems over which we had no control which required them.

And then again, I might be wrong ...
David Webb

dba321 Hall of Fame Points: 3917 More actions · Answer 3

I know about the need for it with reserver words, but, for example our dba always puts it around everything. Also, thew new SQL Prompt by RedGate does also.

David McFarland SSChampion Points: 11815 More actions · Answer 4

While some people might debate whether best practices require the brackets, it's certainly not bad programming practice to do so, so they're just covering their bases.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

And old QA does it every time.

Automatic tools cannot predict hat kind of names you will use for your objects. So, when it generates scripts it must not give any chance of generating SELECT * FROM dbo.USER

_____________
Code for TallyGenerator

dba321 Hall of Fame Points: 3917 More actions · Answer 6

Thanks all! I tend to go with what is recommended industry wide. On with the brackets!

Karl Hewlett Mr or Mrs. 500 Points: 528 More actions · Answer 7

Hi

Another point to consider is that the use of square bracket is MSSQL specific whereas the use of double quotes is quite standard. I have an idea (but may not be correct) that double quotes are ANSI standard.

And while you may not be planning to migrate your App, neither were the developers of any of those that i have migrated over the years. As a cross platform DBA I recommend that you use the highest possible selection of

ANSI standard

Industry Standard

Vendor Standard

Site Standard

Seemed like a good idea at the time

Regards

Karl

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 8

I would always put readability and maintainability first - any rules are a means to an end, not the end in themselves. As a general principle though, avoid reserved words!

TheReligion2000 Valued Member Points: 51 More actions · Answer 9

I think the question shouldn't be if brackets are good practice but rather why would you need them? I think it's best practice to use only 7-bit ASCII alpha and numeric characters, no spaces, no hyphens and so on. One should not forget that only because SQL Server can cope with bracketed names another application might not be able to understand spaces in column names f.e. Remember Win95 and long file names? Even today there are still applications that have troubles with spaces in file names.

Will1922 SSCertifiable Points: 7189 More actions · Answer 10

I think that the brackets are a pain in the butt that just bandaids poor naming conventions. Why would you name a column SELECT or any reserved words? You are just degrading the readability of your code. You should never name any columns any reserved words or special characters.

Live to Throw
Throw to Live
Will Summers

dba321 Hall of Fame Points: 3917 More actions · Answer 11

>>why would you need them

well, yea, that was included really in that I would like to know for sure!

dba321 Hall of Fame Points: 3917 More actions · Answer 12

dba321

Hall of Fame

Points: 3917

April 18, 2007 at 11:13 am

#701180

Thanks TheReligion2000

dba321 Hall of Fame Points: 3917 More actions · Answer 13

My question was

>>In terms of good practice, should you always bracket field, tables, etc.? if so, why?

dba321 Hall of Fame Points: 3917 More actions · Answer 14

TheReligion2000, by my own standards, I have never put a space in for lets say an alias, column name, etc. So I guess the question may be still the "why" then, if you never use spaces...any input on that?