Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

5 Answers

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.

share|improve this answer
4  
Offtopic, but why are leading commas a pet peeve of yours? Is being able to read things easily a pet peeve of yours as well? –  divided Jan 5 '11 at 16:55
6  
I don't agree that leading commas are more readable, unless you mean by more readable that they stick out like sore thumbs (which is my objection to them). –  Larry Coleman Jan 5 '11 at 16:59
3  
They're a pet peeve of mine as well. The only benefit I've found is that it makes it easy to comment out one column using -- –  Lumpy Nov 1 '11 at 20:18
1  
Top of my list is leading semicolons e.g. ;WITH myCTE AS... –  onedaywhen Nov 2 '11 at 11:50
3  
@Gonsalu - It's not that a leading semicolon is required, it's more that the previous statement must be terminated with a semicolon. Because many people in the SQL Server world still don't terminate their statements with semicolons (SQL Server doesn't require it... yet, and maybe never will), the syntax to them becomes ;WITH ... instead of previous_statement; <newline> WITH ...;. –  Nick Chammas Nov 2 '11 at 15:49
show 5 more comments

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.

share|improve this answer
3  
+1 for "Needlessly using quoted identifiers..." –  a_horse_with_no_name Nov 2 '11 at 14:47
    
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
add comment

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.

share|improve this answer
add comment
  • 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.

share|improve this answer
add comment

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.

share|improve this answer
    
+1 - theres a lot more stopping portability than brackets! –  JNK May 24 '12 at 10:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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