Does it make sense to use SQL Server's bracket notation in hand written code?

Question

Code generators tend to be simpler when they generate output using the new Microsoft bracket notation ([]) for nearly everything.

When I first saw it, I though wow a reincarnation of the somewhat banned quoted identifier notation.

As far as I know it is a proprietary extension from Microsoft (meaning Oracle doesn't support it).

Looking at SQL Server there is no difference if you define a table like

CREATE TABLE [dbo].[Table_2] ([col1] [int], [col2] [int]);

or

CREATE TABLE dbo.Table_2 (col1 int, col2 int);

Its a matter of personal or corporate style. Be consistent.

Now if you want to migrate your database to Oracle, brackets are no option.

You can use the old quoted identifiers, but these are case sensitive which causes a lot of trouble.

Is it a good idea to remove all brackets from generated code, avoid using blanks, other special characters and reserved keywords for names and just code in a way that most DBMS understand?

Answer 1

Standard SQL uses the double quote " for quoted identifiers. SQL Server supports this using the QUOTED_IDENTIFIER option (ANSI_QUOTES in mySQL). Standard SQL improves portability in general and in this case would port to Oracle. Similarly I'd change SQL keywords to upper case (Intermediate SQL-92 requirement) and expand int to INTEGER (entry level SQL-92 requirement).

Needlessly using quoted identifiers, whatever the flavour, should be avoided, IMO.

Answer 2

This is a subjective issue, but unnecessary brackets is near the top of my list of pet peeves--slightly more annoying than misspelling "!=", but not as bad as leading commas in column lists.

Objectively speaking, consider the purpose of brackets: allowing object names that would not be legal otherwise. Given that purpose, brackets are a code smell at worst, and a sign of laziness at best.

Answer 3

• Brackets are required if your table or column names:

  • contain a space: SELECT [column name] FROM table;
  • contain a bracket: SELECT [wt[f], or SELECT [wt]]f]
  • contain a non-alphanumeric symbol like ^ or ! (yes, they can contain those symbols!)
  • are reserved keywords like KEY, STATE, RULE, ...

  Obviously, if you have control over the schema then avoid using names like these. However, in some cases the best name is a reserved one (like KEY for the key column in a generic key-value table) so it's up to you to decide how badly you want to use it (and thus have to quote it everywhere).

  I also use brackets to suppress the blue highlighting that SSMS and VS give some keywords like DESCRIPTION that aren't reserved by SQL Server but are otherwise special to those tools.

• Definitely use brackets when dynamically generating SQL. The easy way to do so is by calling QUOTENAME() on the objects you are dynamically referencing (e.g. SELECT QUOTENAME(name) FROM sys.databases;). sp_MSforeachdb, for example, doesn't do this.

Answer 4

When I write code that generates code, I put the square brackets around database object names. I don't include the brackets when writing code by hand, and I find it detracts from the readability of the code. I also forbid database object names with spaces. SQL Server will let you use spaces in object names, but that doesn't mean it is a good thing.

Answer 5

I would probably not even try to have portable DDL. I would be better off if I generated Oracle table definitions off of SQL Server's system views, if needed.

I don't think it makes sense to write portable DML either - PL/SQL is totally different from T-SQL. For portability, it is easier to expose your database via an API of stored procedures. Signatures of these procedures must be the same on both platforms, but the implementations can use proprietary features - overall this is much easier than trying to use only ANSI standard SQL.

This conclusion is based on several years of experience in developing portable systems, working on both Oracle and SQL Server.