question

Ian Roke avatar image
Ian Roke asked

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-sqlsql-serverbest-practice
1 comment
10 |1200

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

David Wimbush avatar image
David Wimbush answered

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

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

Fatherjack avatar image
Fatherjack answered

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 comment
10 |1200

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

Ian Roke avatar image Ian Roke commented ·
+1 I must admit I also wrap alias names if only to avoid putting 'AS' and still understanding what it is doing.
0 Likes 0 ·
Scot Hauder avatar image
Scot Hauder answered

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

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

TimothyAWiseman avatar image
TimothyAWiseman answered

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 comment
10 |1200

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

Ian Roke avatar image Ian Roke commented ·
+1 Great answer thanks Timothy.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

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 comment
10 |1200

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

Ian Roke avatar image Ian Roke commented ·
+1 Thanks for adding more to the other answers. I am really struggling to find the best answer from the responses! :-)
0 Likes 0 ·
dvroman avatar image
dvroman answered

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 comments
10 |1200

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

Ian Roke avatar image Ian Roke commented ·
+1 Ideal but not always possible especially when maintaining legacy systems that were designed before I got my hands on them! ;-)
2 Likes 2 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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 Like 1 ·
dvroman avatar image dvroman commented ·
both comments are dead nuts on!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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