When writing scripts should I be putting the schema and square brackets all the time?

Question

question

Ian Roke asked Feb 12, '10

When writing scripts should I be putting the schema and square brackets all the time?

When I write SQL I tend to never use the schema as it is always dbo and I only tend to use square brackets round column names if they are a keyword such as [datetime] (Don't shoot me I didn't design these databases!) or if there are spaces in such as [Order Date].

My question is should I be using these all the time or is it a better practise to just use them when required such as in the cases above? I have noticed whenever you get SSMS to create the SQL it does conform to this - even putting square brackets round the data types.

What would you guys consider best practise in this case?

t-sql sql-server best-practice

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ commented · Feb 16, 2010 at 11:57 AM

Ian, a nice article here about using aliases http://wiseman-wiseguy.blogspot.com/2010/02/sql-developers-please-use-table.html?utm_source=feedburner&utm_medium=twitter&utm_campaign=Feed%3A+WiseManOrWiseGuyYouDecide+%28Wise+man+or+Wise+guy%3F++You+decide%29

0 ·

Answer 1 · 2010-02-12T07:42:52Z

David Wimbush answered Feb 12, '10

I always specify the schema because I believe that, if you don't, the Query Optimizer checks for an object with that name in your own schema first. But I don't bother with the square brackets except when the name matches a keyword or contains spaces or other symbols. I just find it's too much visual noise and makes things harder to read.

Again, I didn't originally have control over this sort of stuff. (But I do now!)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-02-12T07:36:03Z

Fatherjack answered Feb 12, '10

It depends! I do, it saves any ambiguity in which schema/object you are working with and as you say allows for less correct object names to be used. I also wrap alias names in [ and ]. Plenty of systems run without it but you could consider them a potential libility as there could be a conflict/ambiguity in the future and code will start crashing or worse, doing the wrong thing and not erroring

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke commented · Feb 12, 2010 at 02:51 PM

+1 I must admit I also wrap alias names if only to avoid putting 'AS' and still understanding what it is doing.

0 ·

Answer 3 · 2010-02-12T09:26:29Z

Scot Hauder answered Feb 12, '10

As you have noticed, spaces or reserved words may need special attention. Definitely include brackets if you are creating these dynamically eg passing a table name to a procedure

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-02-12T09:34:07Z

TimothyAWiseman answered Feb 12, '10

In scripts I always schema qualify my objects, it removes ambiguity and can provide a performance enhancement in some cases. I think this is a fairly widespread best practice.

Brackets I think are more subjective and more dependent on local group policies. Personally, I try to avoid them when they are not needed. I think they clutter the code and make it harder to read, and I make readability a priority. We have policies that no one should ever create an object that requires them. When working with dynamic SQL though I routinely use quotename in order to avoid the possible problems that could arise if someone did violate the policy and made an object that requires them.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke commented · Feb 12, 2010 at 02:50 PM

+1 Great answer thanks Timothy.

0 ·

Answer 5 · 2010-02-12T09:41:08Z

Grant Fritchey answered Feb 12, '10

At this point the question has been well and thoroughly answered, but I'm going to pile on, just to emphasize the point.

YES, always use the schema. It's a small, quite minor, cost to writing your code, but it actually increases performance (to a small degree) and increases the clarity and accuracy of your code. It's a must.

Brackets... that's a tougher call. If you have non-standard characters such as spaces, then you have to use brackets by default, but there's nothing that says you have to include them in the code. I use RedGate SQL Prompt, a lot, and I let it put the brackets in, just as a general practice. I don't see it as a hard and fast rule, but a personal preference.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke commented · Feb 12, 2010 at 02:51 PM

+1 Thanks for adding more to the other answers. I am really struggling to find the best answer from the responses! :-)

0 ·

Answer 6 · 2010-02-12T14:43:12Z

dvroman answered Feb 12, '10

Since our databases only use one schema, it's not necessary except to force the default schema on a new procedure. Also it is an excellent idea to create your source in such a way that brackets aren't necessary as much as possible. This makes reading and understanding the code much simpler.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke commented · Feb 12, 2010 at 02:52 PM

+1 Ideal but not always possible especially when maintaining legacy systems that were designed before I got my hands on them! ;-)

2 ·

Grant Fritchey ♦♦ commented · Feb 12, 2010 at 05:07 PM

But, if you don't schema qualify the object, despite the fact that you only have a single schema, you still get an additional lookup as SQL Server checks to see if the object exists under your user schema. See here: http://blogs.msdn.com/mssqlisv/archive/2007/03/23/upgrading-to-sql-server-2005-and-default-schema-setting.aspx

1 ·

dvroman commented · Feb 17, 2010 at 10:49 PM

both comments are dead nuts on!

0 ·

question