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?

more ▼

asked Feb 12, 2010 at 07:09 AM in Default

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

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!)

more ▼

answered Feb 12, 2010 at 07:42 AM

David Wimbush gravatar image

David Wimbush
5k 29 31 34

I didn't realise the query optimizer checked in that way. However if I was by default a member of the 'dbo' schema would it not just be looking there anyway for objects in that schema or would I gain performance by specifying 'dbo.Table'?
Feb 12, 2010 at 02:49 PM Ian Roke
I guess it wouldn't make any difference if your default schema is dbo and the object was in that schema. I think the performance gain would be small as it should only affect compiling a plan, not every subsequesnt execution.
Feb 12, 2010 at 06:48 PM David Wimbush
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 12, 2010 at 09:34 AM

TimothyAWiseman gravatar image

15.6k 21 23 32

+1 Great answer thanks Timothy.
Feb 12, 2010 at 02:50 PM Ian Roke
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 12, 2010 at 09:41 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

+1 Thanks for adding more to the other answers. I am really struggling to find the best answer from the responses! :-)
Feb 12, 2010 at 02:51 PM Ian Roke
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Feb 12, 2010 at 07:36 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

+1 I must admit I also wrap alias names if only to avoid putting 'AS' and still understanding what it is doing.
Feb 12, 2010 at 02:51 PM Ian Roke
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Feb 12, 2010 at 09:26 AM

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 12, 2010 at 07:09 AM

Seen: 3373 times

Last Updated: Feb 12, 2010 at 07:09 AM