Question about Syntax with T-SQL

[rhaazy]

When I am writing queries, whether in application code, or in a
mangement utility, what is the difference between referencing columns
with or without brackets [].

For example what is the difference between these two statements:

select [column1], [column2] from myTable

select column1, column2 from myTable

I would think that since I am not currently aware of a difference,
that the bracket must be used in special cases, but I assume it would
be best practice to just always use the brackets.

I'm not sure and would appreciate any feedback. Thanks

[Tom Moreau]

Use the brackets when you are dealing with names that contain special
characters, e.g. blanks:

select [column 1], [column 2] from myTable

Otherwise, you can leave them out for readability.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"rhaazy" <rha...@gmail.com> wrote in message
news:9b51985e-33d6-420d...@x35g2000hsb.googlegroups.com...

[vinu]

HI

Let say your table has a column called call id
if you write select statement lik this

select call id from table

the sql server think call and id are 2 different field, to avoid such issues
you put it in [] like [call id]

also [] is used to distingusing between sql keywords for ex.

select 1 as top -- where top is an alias , will fail b'cose top is a keyword

but

select 1 as [top] -- will work file

vinu

"rhaazy" <rha...@gmail.com> wrote in message
news:9b51985e-33d6-420d...@x35g2000hsb.googlegroups.com...

[Tom Cooper]

The brackets are used when the name of a column, table, view, etc is not a
legal SQL identifier, either because it contains illegal characters (for
example blanks) or because the identifier is a reserved word. For example,
if you have a table named My Table with a column named Select in that table,
then
Select Select From My Table
will, of course, give you syntax errors. But
Select [Select] From [My Table]
will work.

IMO, best practice is you should never use an illegal name or reserved word
as an identifier. Then you never need brackets. So I don't use them
because leaving them out makes the code easier to read.

However, one place you often see brackets is in generated code. If you
write a generalized routine that you intend to use to generate code for any
database, it is a good idea to generate brackets around every identifier so
that it will work for any case.

It should also be noted that while SQL Server uses brackets for this, the
ANSI standard uses double quotes ("). You can use double quotes in SQL
Server if you have the option SET QUOTED_IDENTIFIER set to ON. In my
experience, most SQL Server developers use brackets rather than ".

Tom

"rhaazy" <rha...@gmail.com> wrote in message
news:9b51985e-33d6-420d...@x35g2000hsb.googlegroups.com...

[Raymond D'Anjou]

"Tom Moreau" <t...@dont.spam.me.cips.ca> wrote in message
news:uaTB8$wyIHA...@TK2MSFTNGP05.phx.gbl...

> Use the brackets when you are dealing with names that contain special
> characters, e.g. blanks:
>
> select [column 1], [column 2] from myTable
>
> Otherwise, you can leave them out for readability.

...or for column names that are reserved keywords.

Even better:
Don't name columns (or anything in SQL) using special characters or reserved
keywords.

[Russell Fields]

rhaazy,

The [ ] can hold together a name that will not parse correctly without it.
This could be because of the use of a keyword, e.g. a column named [Date],
or some character string that would be parsed incorrectly, e.g. a column
named [Cost-Value]. As long as the column, table, etc are all named
reasonably, the [ ] is unneeded.

In fact, I would recommend avoiding using names that ever require [ ] to
parse correctly. If you cannot, then use the [ ] only in exceptional cases
where it was really necessary. I think it is a typing headache, is more
visual clutter to mess with, and is only useful in making poor physical
naming decisions work despite themselves.

Also, FWIW, I believe that the ANSI standard is not [ ] but " " around
"invalid names", i.e. SET QUOTED_IDENTIFIER ON.

RLF

"rhaazy" <rha...@gmail.com> wrote in message
news:9b51985e-33d6-420d...@x35g2000hsb.googlegroups.com...

[rhaazy]

On Jun 10, 11:47 am, "Russell Fields" <russellfie...@nomail.com>
wrote:

> > I'm not sure and would appreciate any feedback.  Thanks- Hide quoted text -
>
> - Show quoted text -

Thanks everyone for you explanations.