November 30, 2015 at 10:19 pm
Comments posted to this topic are about the item Fun with qualifiers
November 30, 2015 at 10:30 pm
This was removed by the editor as SPAM
November 30, 2015 at 11:04 pm
Only when the 'QUOTED_IDENTIFIER' is set as 'ON', can this query run successfully, otherwise, it will fail
MCSE Data Platform; BI
MCITP Database Developer; BI
December 1, 2015 at 1:18 am
I missed the most important correct answer: you'll get fired. 😀
Luckily Shane does indicate in the explanation that using this type of table names is not a good idea.
A bit of additional background for those interested.
In the ANSI standard for SQL, the 'single quote' character is defined as the string delimiter, and the "double quote" is the delimiter for object names. Using [square brackets] is not supported at all.
In Access, the 'single quote' is used for VB-style comments (if I remember correctly), and the "double quote" delimits strings, so Microsoft had to come up with something else for object names - and the [square bracket] was born.
In SQL Server, Microsoft tried a halfhearted attempt at being compatible with both the ANSI standards (and hence the major competitors), and with Access (to facilitate upgrade from Access to SQL Server):
- [square brackets] are always seen as delimiters for object names (Access style - ANSI SQL would throw an error)
- 'single quotes' are always seen as string delimiters (ANSI style - Access would consider the rest of the line as a comment)
- And "double quotes" can be interpreted either as delimiters for strings (Access style), or as delimiters for object names (ANSI SQL style), depending on the setting of the SET QUOTED_IDENTIFIER option and/or the QUOTED_IDENTIFIER database setting. The ON setting, which is default, and also a requirement for a lot of features, implies that "double quotes" are interpreted ANSI style, as object name delimiter.
The effect of this is that, in theory, code ported from Access should work with SET QUOTED_IDENTIFIER OFF, and code ported from ANSI-compliant competitors should work with SET QUOTED_IDENTIFIER ON.
Another effect of this setting is that 'single quote' for strings and [brackets] for object names always work, and are "safe". But "double quotes" are less safe, because code that works can stop working when someone tampers with the setting. This is probably the reason why all Microsoft's documentation, and all Microsoft's code generating tools will always use [square brackets] - and that in turn has resulted in [square brackets] being the de facto standard for all code I ever see on SQL Server.
Which is a bummer if your boss ever decides to port the system to Oracle so that it can be sold to other companies. Now, apart from having to deal with all the "real" inconsistencies between the products, you will also have to replace all [object names] with "object names". And a simple search and replace won't work because [brackets] also have a special meaning in LIKE patterns (and that meaning really requires brackets, in all ANSI-compliant products).
</rant>
December 1, 2015 at 5:17 am
Even though it looked horrible, I'm not surprised by the answer. I must admit I liked Hugo's answer: Someone would get fired. Well, perhaps at least a strong talking-to. 😀 Nice question.
December 1, 2015 at 5:35 am
Ed Wagner (12/1/2015)
Even though it looked horrible, I'm not surprised by the answer. I must admit I liked Hugo's answer: Someone would get fired. Well, perhaps at least a strong talking-to. 😀 Nice question.
He will investigate other possible careers 😉
December 1, 2015 at 6:38 am
Hugo Kornelis (12/1/2015)
I missed the most important correct answer: you'll get fired. 😀
So true
December 1, 2015 at 6:49 am
Thank you for the very interesting background behind the square bracket delimiter in SQL Server, Hugo. I had always assumed it was a more arbitrary decision on Microsoft's part. It's interesting to know the history behind it.
December 1, 2015 at 7:10 am
Hugo Kornelis (12/1/2015)
I missed the most important correct answer: you'll get fired. 😀Luckily Shane does indicate in the explanation that using this type of table names is not a good idea.
A bit of additional background for those interested.
In the ANSI standard for SQL, the 'single quote' character is defined as the string delimiter, and the "double quote" is the delimiter for object names. Using [square brackets] is not supported at all.
In Access, the 'single quote' is used for VB-style comments (if I remember correctly), and the "double quote" delimits strings, so Microsoft had to come up with something else for object names - and the [square bracket] was born.
In SQL Server, Microsoft tried a halfhearted attempt at being compatible with both the ANSI standards (and hence the major competitors), and with Access (to facilitate upgrade from Access to SQL Server):
- [square brackets] are always seen as delimiters for object names (Access style - ANSI SQL would throw an error)
- 'single quotes' are always seen as string delimiters (ANSI style - Access would consider the rest of the line as a comment)
- And "double quotes" can be interpreted either as delimiters for strings (Access style), or as delimiters for object names (ANSI SQL style), depending on the setting of the SET QUOTED_IDENTIFIER option and/or the QUOTED_IDENTIFIER database setting. The ON setting, which is default, and also a requirement for a lot of features, implies that "double quotes" are interpreted ANSI style, as object name delimiter.
The effect of this is that, in theory, code ported from Access should work with SET QUOTED_IDENTIFIER OFF, and code ported from ANSI-compliant competitors should work with SET QUOTED_IDENTIFIER ON.
Another effect of this setting is that 'single quote' for strings and [brackets] for object names always work, and are "safe". But "double quotes" are less safe, because code that works can stop working when someone tampers with the setting. This is probably the reason why all Microsoft's documentation, and all Microsoft's code generating tools will always use [square brackets] - and that in turn has resulted in [square brackets] being the de facto standard for all code I ever see on SQL Server.
Which is a bummer if your boss ever decides to port the system to Oracle so that it can be sold to other companies. Now, apart from having to deal with all the "real" inconsistencies between the products, you will also have to replace all [object names] with "object names". And a simple search and replace won't work because [brackets] also have a special meaning in LIKE patterns (and that meaning really requires brackets, in all ANSI-compliant products).
</rant>
Interesting history with Access, Hugo. Interconnected platforms, apparently, like it or not. Thanks for the perspective!
As today's question was specifically about nitpicky delimiters for strings and objects, should we deduct points from your reply for not including an opening "<rant>" tag?? :hehe:
Rich
December 1, 2015 at 7:56 am
Shane, that question made my eyes hurt!
Hugo, that answer is why I love reading the QOTD, the follow on discussion is always worth reading. But I only see a closing rant tag - rookie mistake?:-)
December 1, 2015 at 8:47 am
THANKS for an EXCELLENT QOD. This is one of the best I have seen on this site for 3 reasons.
1. The question was asked in such a way that most people will give the correct answers which is encouraging.
2. I quickly learned something I did not know. All too often the QOD becomes a research project. Most of us working people do not have that kind of time for the QOD.
3. Giving us code to run can be fun too. It's always cool to see it in action even though it isn't always possible depending on subject matter. Also pointing back to reason 2 - the people that have more time to spend can choose not to run the code and answer/guess from knowledge ...others can run the provided code, see results quickly, answer the question, and still learn stuff. win! win! win!
December 1, 2015 at 8:59 am
Dain Bramage (12/1/2015)
THANKS for an EXCELLENT QOD. This is one of the best I have seen on this site for 3 reasons.1. The question was asked in such a way that most people will give the correct answers which is encouraging.
2. I quickly learned something I did not know. All too often the QOD becomes a research project. Most of us working people do not have that kind of time for the QOD.
3. Giving us code to run can be fun too. It's always cool to see it in action even though it isn't always possible depending on subject matter. Also pointing back to reason 2 - the people that have more time to spend can choose not to run the code and answer/guess from knowledge ...others can run the provided code, see results quickly, answer the question, and still learn stuff. win! win! win!
Thanks Drain Bramage, I'm glad you liked it. I've learned a great deal from attempting to answer the QotD each day, so I'm glad I was able to contribute in a positive way. Oddly, I didn't think it was a very good question because it is convoluted to read and requires three answers to get it right. :laugh:
December 1, 2015 at 9:02 am
Andy Warren (12/1/2015)
Shane, that question made my eyes hurt!Hugo, that answer is why I love reading the QOTD, the follow on discussion is always worth reading. But I only see a closing rant tag - rookie mistake?:-)
Hey Andy! Feast your eyes on THIS! Muh-hahahahahaaaaaa!! :laugh:
CREATE SCHEMA [FROM];
CREATE TABLE [FROM].[FROM]
(
[SELECT] VARCHAR(10),
[WHERE] VarChar(10),
[AND] VarChar(10),
);
INSERT INTO [FROM].[FROM] ([SELECT],[WHERE], [AND]) VALUES ('SELECT','WHERE', 'AND');
SELECT [SELECT] [SELECT] FROM [FROM].[FROM] [WHERE] WHERE [WHERE].[WHERE] = 'WHERE' AND [AND] = 'AND'
December 1, 2015 at 9:08 am
That's close to evil!
December 1, 2015 at 9:40 am
Very Nice Question!
Thanks.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply