12

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?

  • [I] [try] [to] [discourage] [the] [use] [of] [square] [brackets] [around] [identifiers] [in] [our] [organisation]. [The] [reasons] [are] [clear] [and] [obvious], [but] [strangely], [not] [to] [Microsoft]. – Reversed Engineer Jun 15 '18 at 12:45
12

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.

  • 2
    In 1992, color editors weren't nearly as pervasive as they are now. Why bother with upper case on keywords now? It is a standard for a problem that no longer exists IMO. The only time I can see it makes sense is with a binary collation and in that circumstance you have far worse problems than capitalization. – Thomas Jun 14 '12 at 10:00
  • 1
    I agree. I actually really dislike having all the SQL keywords uppercase. It feels like such a throwback to EBCDIC... (and I'm a guy who likes SQL and favors it over virtually all ORM's) – Craig Sep 11 '17 at 23:58
  • 2
    @Thomas: I simply find it easier to read and therefore understand the code, evidenced by the fact I nearly always take the time to rewrite other peoples SQL keywords into uppercase when I need to analyze it. – onedaywhen Sep 14 '17 at 10:17
  • 2
    Mercifully, code reformatters have gotten much better these days so that those of us that don't want to relive that spaghetti, sir-mix-a-lot flashback all caps era can modernize that SQL code so its readable. ;D – Thomas Sep 15 '17 at 16:02
11

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.

7
  • 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.

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.

  • 1
    Spaces in database object names is an egregious offense. – Craig Sep 11 '17 at 23:59
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.

  • 1
    +1 - theres a lot more stopping portability than brackets! – JNK May 24 '12 at 10:04

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.