March 8, 2017 at 2:49 am
Hi all,
I noticed that if I use spaces and/or tabs in a 3-part name OUTSIDE the brackets , they are ignored.
For example:
select count(0) from [SomeDB].[dbo].[SomeTable]
does the same as:
select count(0) from [SomeDB] . [dbo] . [SomeTable] -- contains multiple tabs and spaces.
However, parsename() results in NULL when the expression contains one or more spaces.
I get the same results in both MSSQL 2014 and 2008R2, also with 2-part names.
Isn't this weird?
March 8, 2017 at 2:51 am
SQL's whitespace behaviour is weird in general.
This is valid and works:
SELECT*FROM master
. sys .
objects ;
ParseName is probably doing some string processing, and hence is more sensitive to whitespace than the T-SQL parser is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2017 at 5:51 am
Let's keep this secret to ourselves, lest folks will want to start coding that way.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 8, 2017 at 6:40 am
Eric M Russell - Wednesday, March 8, 2017 5:51 AMLet's keep this secret to ourselves, lest folks will want to start coding that way.
Right!
Actually, I'm shocked about all the possibilities we certainly do NOT want, like End-Of-Line comments or even Multi-line comments in between...
March 8, 2017 at 6:55 am
Sadly, this is T-SQL legal too.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 8, 2017 at 7:00 am
Eric M Russell - Wednesday, March 8, 2017 6:55 AM
Sadly, this is T-SQL legal too.
And how much fun would it be to see the results of a query against INFORMATION_SCHEMA after this script was run....
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
March 8, 2017 at 7:32 am
Eric M Russell - Wednesday, March 8, 2017 6:55 AM
Sadly, this is T-SQL legal too.
Let's keep that a dark and deep secret too...
March 8, 2017 at 8:15 am
You guys are doing a really good job of keeping it all a "secret". 😉
--Jeff Moden
March 8, 2017 at 8:34 am
Eric M Russell - Wednesday, March 8, 2017 6:55 AM
Sadly, this is T-SQL legal too.
Oh you can do a lot worse than that. I've got a 'sample' query somewhere with column names and table names like FROM and WHERE and , and =.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2017 at 9:39 am
I worked with a very skilled database architect who thought nothing of using reserved keywords for field names. Since I was the consultant writing the T-SQL I changed them to be more meaningful and NOT reserved keywords.
I have no doubt he's doing it to this day.
March 8, 2017 at 10:57 am
I'll use the best column name for the particular data/business requirement. If that happens to be a reserved word, so be it. I don't go out of my way to avoid reserved words.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 8, 2017 at 11:52 am
ScottPletcher - Wednesday, March 8, 2017 10:57 AMI'll use the best column name for the particular data/business requirement. If that happens to be a reserved word, so be it. I don't go out of my way to avoid reserved words.
Other than 'RowCount', I don't find myself tripping over T-SQL reserved works when choosing column names.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 8, 2017 at 12:10 pm
If that happens to be a reserved word, so be it. I don't go out of my way to avoid reserved words.
I never do this. I avoid RowCount with RecordCount. Hate it when I have to pull data from an external column and the column names are reserved words.
March 8, 2017 at 12:14 pm
RonKyle - Wednesday, March 8, 2017 12:10 PMIf that happens to be a reserved word, so be it. I don't go out of my way to avoid reserved words.
I never do this. I avoid RowCount with RecordCount. Hate it when I have to pull data from an external column and the column names are reserved words.
How would you ever know for sure they're not reserved anyway? The only specific name I remember was "authorization". That is what the business called it, not "authorized_by", but "authorization". So that's what I named the column. Yes, we could have put a view on top of another physical column name, but that company did not do that.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 8, 2017 at 1:16 pm
I would never name a column 'authorization'. It would be something like 'AuthorizationDate' or 'AuthorizationCode', etc.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply