|
When I write SQL I tend to never use the schema as it is always 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?
(comments are locked)
|
|
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!) 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 '10 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 '10 at 06:48 PM
David Wimbush
(comments are locked)
|
|
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 Great answer thanks Timothy.
Feb 12 '10 at 02:50 PM
Ian Roke
(comments are locked)
|
|
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 Thanks for adding more to the other answers. I am really struggling to find the best answer from the responses! :-)
Feb 12 '10 at 02:51 PM
Ian Roke
(comments are locked)
|
|
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 I must admit I also wrap alias names if only to avoid putting 'AS' and still understanding what it is doing.
Feb 12 '10 at 02:51 PM
Ian Roke
(comments are locked)
|
|
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
(comments are locked)
|
1 2 next page »
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