help needed in code tuning.

Question

help needed in code tuning.

Viewing 13 posts - 16 through 27 (of 27 total)

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

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 1

Ninja's_RGR'us (8/22/2010)
Here's a weird idea, start worrying about it when you plan to move to sql 201? and having the upgrade advisor throwing errors and warnings.
In the mean time I never used tlename.colname so I won't lose too much sleep over this :w00t: and I'm pretty sure you shouldn't have to either!

Nice advice but misplaced a bit, Remi. The only reason I worry about this is because a lot of other people make code the wrong way, IMHO. If or when this happens, there's going to be a bazillion pieces of code that need to be fixed by those people. I'd rather help people figure that out now rather than when it's too late... especially when some people keep telling other people how to write code the wrong way... IMHO. 😀

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

I see your point. My own pers. favorite has always been to use table aliases and using it on the columns, even with only 1 table in the query. And since that can't possibly be depreciated in the forseeable future, I can go back to my nap ;-).

Happy googling.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Jeff Moden (8/21/2010)
GilaMonster (8/20/2010)
Jeff Moden (8/20/2010)
Comment withdrawn. I couldn't find the MS reference that I remember seeing to support what I said so I've withdrawn my comment.
Now I'm curious... PM or email?
Nah... it's ok. I'm embarassed but it's nothing I can't post.
I was referring to using actual table names as part of 2 part notation in the SELECT list. I remember very clearly seeing something from MS about code like the following not being supported at some future date...
SELECT tablename.columnname1,
tablename.columnname2
FROM dbo.tablename
The thing I read (and can no longer find) said that you should either use just the column name (obviously not recommended when joins are present) or to use an alias for the table name to be used in the SELECT list columns as many of us currently do.
The closest thing I can find in the deprecation lists is the fact that you won't be able to use 3 and 4 part naming in the SELECT list in the future, but I can't find the part about the 2 part naming. The MS link I'm talking about (and can no longer find) had the 2 part nuance of all that clearly explained.
Of course, that "find" happened when I was on some serious meds for Bronchitis so it may have been a manifestation on my part. :hehe:

I recall the same thing - and I don't have the reference either. But, it is related to how you write your queries. In SQL Server 2005 and above, MS now recommends that you always schema qualify object access.

If you schema qualify the table, then try to use the tablename.column syntax - that doesn't work. Without an alias, you would have to use 3-part naming (or 4-part for linked servers) - which, of course, is being deprecated.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Jeffrey Williams-493691 (8/22/2010)
I recall the same thing - and I don't have the reference either. But, it is related to how you write your queries. In SQL Server 2005 and above, MS now recommends that you always schema qualify object access.
If you schema qualify the table, then try to use the tablename.column syntax - that doesn't work. Without an alias, you would have to use 3-part naming (or 4-part for linked servers) - which, of course, is being deprecated.

That matches my recollection, so it's a combination of 3-part naming of column been deprecated and schema-qualifying being recommended, not that 2-part naming of columns is deprecated.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Ninja's_RGR'us (8/22/2010)
Here's a weird idea, start worrying about it when you plan to move to sql 201? and having the upgrade advisor throwing errors and warnings.

In addition to Jeff's point, it's also the nagging irritation of having read something but not been able to recall it completely. :hehe:

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

GilaMonster (8/22/2010)
Ninja's_RGR'us (8/22/2010)
Here's a weird idea, start worrying about it when you plan to move to sql 201? and having the upgrade advisor throwing errors and warnings.
In addition to Jeff's point, it's also the nagging irritation of having read something but not been able to recall it completely. :hehe:

Been there done that. I've since gone on a low information diet.... learn / research something only if it's going to immediately help you get something done on your important todo list.

It's been a real eye opener as well as productivity booster.

Anyhow, I know you'll still google your socks off tonight so happy googling :-P.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Ninja's_RGR'us (8/22/2010)
Anyhow, I know you'll still google your socks off tonight so happy googling :-P.

Nah, mainly because I can't think of a search term that'll get me a result without a million useless hits to wade through.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 8

Ninja's_RGR'us (8/22/2010)
I see your point. My own pers. favorite has always been to use table aliases and using it on the columns, even with only 1 table in the query. And since that can't possibly be depreciated in the forseeable future, I can go back to my nap ;-).

I absolutely agree with that... even the 1 table alias.

--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)

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 9

GilaMonster (8/22/2010)
Jeffrey Williams-493691 (8/22/2010)
I recall the same thing - and I don't have the reference either. But, it is related to how you write your queries. In SQL Server 2005 and above, MS now recommends that you always schema qualify object access.
If you schema qualify the table, then try to use the tablename.column syntax - that doesn't work. Without an alias, you would have to use 3-part naming (or 4-part for linked servers) - which, of course, is being deprecated.
That matches my recollection, so it's a combination of 3-part naming of column been deprecated and schema-qualifying being recommended, not that 2-part naming of columns is deprecated.

That's a good idea and logically true... but the article I read (and can't find) very specifically said that two part naming in the SELECT list where the first part was a table name was being deprecated and wouldn't actually work at some point in the future. Lordy, I wish I could find that article.

As Remi said, I don't use table names in the SELECT list to begin with so it's not a personal problem for me. I just wish I could find the article so I could justify telling folks not to do it.

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Just use the "do what I say" line... seems to work for many people.

Leave the rest to fie and ask for our help when they screw up... :w00t:

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 11

Heh... I'm one of those people that hate the "just do what I say" line. That's a 6 pork chop offense in my book.

--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)

WayneS SSC Guru Points: 95460 More actions · Answer 12

And we know that Jeff doesn't want any pork chops aimed at him! :w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 13

WayneS (8/22/2010)
And we know that Jeff doesn't want any pork chops aimed at him! :w00t:

Z'actly. I'm busy enough launching them. I don't have time to watch for incoming. 😛

--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)