13

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?

11

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.

10

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.

  • Leading commas make it abundantly clear where new columns start. Brackets are definitely not a code-smell since they clearly delineate what is a column name and what is not. I'm not saying you must use them, but to say they indicate problems is a vast over-reach. – Max Vernon Dec 6 at 12:55
9
  • 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.

6

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.

5

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.

-4

Extremely stupid idea to allow blanks in table- and fieldnames, and even allow reserved words. A completely unnecessary feature, that produces a lot of messy code.

New contributor
Thomas Riedel is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 4
    Your answer provides no additional value over the other answers in this thread. Would probably been a good idea to advice the poster, when to use it and when not to use it. – Yannick Liekens Dec 6 at 9:31
  • The reserved words list is not fixed. If reserved words were not allowed in names then this would mean people could not upgrade to later versions if they happened to have a clash without renaming these first – Martin Smith Dec 6 at 13:16
  • he's right it's a bad idea, but imo he's wrong that it's a completely unnecessary feature. Sometimes your code has got to be stupid. – James Dec 6 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.