More than a two-part column name - Depricated Features

Question

More than a two-part column name - Depricated Features

gideon_king

SSC Veteran

Points: 242
More actions
June 2, 2016 at 11:07 am

#308155

Was looking at deprecated features in future versions of SQL in the sys.dm_os_performance_counters table. Trying to figure out what code changes we need to start implementing.
Most of what I'm looking at makes perfect sense; no more - text - ntext or image data types; sysobjects to sys.objects; etc.
One thing that I'm having a hard time wrapping my head around is the "More than two-part column name" getting deprecated in future versions of SQL?
Per Microsoft -
"A query used a 3-part or 4-part name in the column list. Change the query to use the standard-compliant 2-part names. Occurs once per compilation."
Question: Why on earth would Microsoft (or anyone) care? What possible harm could it be doing?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

gideon_king (6/2/2016)
Was looking at deprecated features in future versions of SQL in the sys.dm_os_performance_counters table. Trying to figure out what code changes we need to start implementing.
Most of what I'm looking at makes perfect sense; no more - text - ntext or image data types; sysobjects to sys.objects; etc.
One thing that I'm having a hard time wrapping my head around is the "More than two-part column name" getting deprecated in future versions of SQL?
Per Microsoft -
"A query used a 3-part or 4-part name in the column list. Change the query to use the standard-compliant 2-part names. Occurs once per compilation."
Question: Why on earth would Microsoft (or anyone) care? What possible harm could it be doing?

Quick thought, two part column name restriction based on a two to four part object reference makes sense when aliasing the object, full referential path to an object.column is probably not that sensible i.e. server.db.table.column

😎

select

server.db.table.column

from server.db.table

where server.db.table.column is xxx

Bill Talada SSChampion Points: 11954 More actions · Answer 2

I believe it would have to do with scoping, default schemas, and different objects showing up depending on who is running the query and which parts of the names are missing. Personally I'd prefer mandatory Aliases on all tables and columns in a SELECT.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

I have no inside information on this, but I have a guess.

It may have something to do with the work done by the optimizer, specifically the algebrizer process that identifies all the objects involved in the query. By putting all the work at the FROM operation, it reduces work that would need to occur in SELECT, WHERE, etc.

Just a guess.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

gideon_king (6/2/2016)
Question: Why on earth would Microsoft (or anyone) care? What possible harm could it be doing?

Grant's supposition on the subject seems the most likely reason from a Microsoft standpoint.

I'm actually pretty happy to see 2 part naming being enforced so that I won't have to worry about enforcing it anymore. People don't understand that things can necessarily change. For example, it may be that there's a reason to move an object to a different database or to rename a server. If you're using 3 part naming in the former and 4 part naming in the later in the SELECT list, WHERE clause, JOIN conditions, or whatever, that will cause a huge number of required changes in the code along with at least smoke testing and likely some regression testing. It's much easier to use SYNONYMs or "pass through" views to support the 2 part naming because then you don't have to change the code at all and you can programmatically change the SYNONYMs in just a couple of heart beats.

As a personal side bar, I find the 3 and 4 part naming adds a whole bunch of clutter to the code even when people take good pains to make it more readable.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gideon_king SSC Veteran Points: 242 More actions · Answer 5

Thank you all for answering this silly question;

Helping the optimizer, reducing clutter, and standardizing code are all things that I can live with.

thx:-)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

gideon_king (6/3/2016)
Thank you all for answering this silly question;
Helping the optimizer, reducing clutter, and standardizing code are all things that I can live with.
thx:-)

Nah... it wasn't a silly question at all. In fact, I'll say that more people should ask about it for all the reasons given but simply don't know enough reasons to ask the question. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)