August 10, 2009 at 12:46 pm
timothyawiseman 18. Try to use table variables instead of Temporary Tables -
In general, I agree. Table variables are normally to be preferred, but there are some cases where the Temp Table is a better choice. Complex constraints and indexes can be applied to a Temp Table but not a table variable. (Of course, if you want complex constraints and indexes it may be better to just create actual tables, but I have seen some unusual cases where my entire development team agreed that a Temp Table with an Index was the overall best choice.) Also, temp tables are easier to deal with in conjunction with dynamic sql than table variables (again, dynamic sql should be used cautiously, but there are some cases where it is truly the best choice. The Curse and Blessing of Dynamic SQL is worth reading on this point.)
Have to sort of disagree with you on this one. Temp tables cause statement recompiles in 2005/8, and proc recompiles in 2000, but even those are often/usually better than the execution plans that you end up with if you use table variables.
Table variables have no benefits over temp tables except that they are outside of transactions, and that can be an advantage in a few cases (storing data for Try Catch handling to be used after a rollback is one example). Other than being non-transactional, they have no advantages that I know of. Temp tables, on the other hand, have plenty of advantages. They can indexed beyond the PK-clustered index, they have stats, they can easily pass data back and forth between procs, they can be accessed by dynamic SQL, and they can have their first page of data cached for re-use (2005/8) if that's needed, they can have DDL run on them after creation, they can be created by Select Into (very useful when importing data from files where you don't know before-hand what the column sizes need to be), and more.
I recommend using table variables only if you have to (UDFs, which should be avoided as much as possible anyway for their own reasons), or if you need to hang onto some data despite needing to issue a rollback command.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 10, 2009 at 12:51 pm
GilaMonster (8/10/2009)
yadavmanoj (8/10/2009)
What about NO LOCK in the Select?Only if you like your data dirty.
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Some days it's a choice between that and query timeout.
We've got a ton of nasty old legacy apps around here... the kinds written in VB6 that open a record set and leave it open until the screen is closed. There's no way to actually change data on that particular screen, but the recordset was opened read/write and locks up a good chunck of the table.
As much as I don't like it... we use READ UNCOMMITTED to get around those nasty apps. We've got all of 7 developers here and they've got deadlines to hit to get new code out of the door, most of them have little to no time to go back and re-code huge portions of applications that are at least 10 years old, and documented poorley if at all.
So we live with Dirty reads... Hell, I'm still getting over the shock of all the tables here that have identity columns, but no actually declared primary key at all or worse, something is declared a primary key but they chose to not make a clustered index on the table. I keep cleaning them up... but I'm getting tired of finding them. Only 60+ databases to comb through, in addition to trying to build a data dictionary for the past 14 years of database mangling.
August 10, 2009 at 12:58 pm
LP (8/10/2009)
jonwolds (8/10/2009)
Some points in this article no longer apply, some are mis-leading and some are just incorrect. Please read with caution.Absolutely agreed. Very dangerous article espesially for newbees. Better not to be publish something like this with tonns of mistakes and misleading info.
.
Well. Depends on how well you take. I always make sure to read all comments from users in discussion forum for each article.
I think an article which is bad written but very good comments in discussion forum is way much better than a well written article with no comments.
At least with these kind of half baked articles we get to know more diff. opinions of people around us on a single topic.
I said half backed above because even if the Author had come with 100 tips, people would have come up with 101 and every developer had his own standard tips with which he works on and all tips are bound to get lot of falks.
SQL DBA.
August 10, 2009 at 1:07 pm
LP (8/10/2009)
jonwolds (8/10/2009)
Some points in this article no longer apply, some are mis-leading and some are just incorrect. Please read with caution.Absolutely agreed. Very dangerous article espesially for newbees. Better not to be publish something like this with tonns of mistakes and misleading info.
Not sure where you get "tons of mistakes and misleading info", but yes there are some. They have also been fully discussed in this discussion thread. This is one of the best aspects of the SSC Community. Here is where you really learn things, not necessarily the articles themselves, even though there have been many that are quite well written and informative as well.
August 10, 2009 at 1:23 pm
Wesley Brown (8/10/2009)
in 7.0 and 2000 if you named a proc sp_ you got a trip to master to try and resolve the name. In 2005 that has changed a bit. Now its about quickly resolving a name to the procedure cache as much as anything from what I can gather.
Agree with the many the people who have rated the article 1*. I think it is poor form that the author has not once responded, in 10 pages of comments.
Well, I didn't post just to complain, and fair dues, at least he is *trying* to submit help, whereas I haven't yet gotten to posting articles, but merely posting here, SQLTeam, and other sites.
anyways: 1 useful piece of information I got from a Kalen Delaney course. sp_ is NOT Microsoft's "stored procedures". It actually means special, and works for tables as well. try creating a table SP_table in master, then selecting from the table from a user database.
This may or may not be useful to you...
ciao 4 now
edited for typo's
August 10, 2009 at 1:32 pm
Lynn Pettis (8/10/2009)
LP (8/10/2009)
jonwolds (8/10/2009)
Some points in this article no longer apply, some are mis-leading and some are just incorrect. Please read with caution.Absolutely agreed. Very dangerous article espesially for newbees. Better not to be publish something like this with tonns of mistakes and misleading info.
Not sure where you get "tons of mistakes and misleading info", but yes there are some. They have also been fully discussed in this discussion thread. This is one of the best aspects of the SSC Community. Here is where you really learn things, not necessarily the articles themselves, even though there have been many that are quite well written and informative as well.
Many of the articles are well written which is one of the main reasons I read this site. As of now there have been 16,475 views of the article, only a fraction of the people who have viewed the main article will have even looked at this related discussion so will go away with duff information. If this were wikipedia we could correct the article but as it stands it will stay there indefinitely as a published article giving incorrect information.
August 10, 2009 at 1:45 pm
jacroberts (8/10/2009)
Lynn Pettis (8/10/2009)
LP (8/10/2009)
jonwolds (8/10/2009)
Some points in this article no longer apply, some are mis-leading and some are just incorrect. Please read with caution.Absolutely agreed. Very dangerous article espesially for newbees. Better not to be publish something like this with tonns of mistakes and misleading info.
Not sure where you get "tons of mistakes and misleading info", but yes there are some. They have also been fully discussed in this discussion thread. This is one of the best aspects of the SSC Community. Here is where you really learn things, not necessarily the articles themselves, even though there have been many that are quite well written and informative as well.
Many of the articles are well written which is one of the main reasons I read this site. As of now there have been 16,475 views of the article, only a fraction of the people who have viewed the main article will have even looked at this related discussion so will go away with duff information. If this were wikipedia we could correct the article but as it stands it will stay there indefinitely as a published article giving incorrect information.
Sorry if you are looking for perfection in every article written here on SSC. I guess you come away disappointed at times after reading some of the articles in SQL Server Magazine as well. I've read a few articles in this fine magazine that I would not implement in any of my production databases because it is just as bad as some of the information you may find elsewhere, wether here on SSC, or SQLTeam, or elsewhere on the Net.
August 10, 2009 at 1:48 pm
A lot of good points are made on this board. Certainly the best point made on this board is that the best points made are on this board. Not in the articles.
August 10, 2009 at 1:48 pm
jacroberts (8/10/2009)
Many of the articles are well written which is one of the main reasons I read this site. As of now there have been 16,475 views of the article, only a fraction of the people who have viewed the main article will have even looked at this related discussion so will go away with duff information. If this were wikipedia we could correct the article but as it stands it will stay there indefinitely as a published article giving incorrect information.
Actually there are cases, one just last week, where Steve will insert a comment/link into an article with some questionable content directing readers to be sure to read the discussion.
There have been times where a tech review requirement has been discussed, but who would do the reviewing?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 10, 2009 at 2:02 pm
Andy DBA (8/10/2009)
I agree with Tim that many readers will assume endorsement by SSC and might not read any of the comments or even be aware of their presence. I also think SSC is judged based on what articles are selected for publication. I also feel the prestige of being published here is proportional to the quality of other published articles.I doubt SSC has the necessary resources to verify every statement made in every article that's published, but presumably articles are at least checked for the obvious such as appropriate content so there's a wide grey area here. It sounds like Steve is leaning more towards less review.
At the risk of getting off topic here, I agree with Andy and Tim Walker. I am a huge fan of SQL Server Central and have had a couple of very minor pieces published here. I think it is exceptional exactly as it is, but I think it could be even better with higher editorial standards and perhaps enforced peer review.
Off the cuff, one thought might be to circulate an article through a few volunteers prior to posting. They could send their comments to the author and send SSC an "approved" or "requires modification" rating. It would certainly take a substantial amount of initial set up, but once set up it could be mostly automated and require only limited intervention from our dear editors here.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
August 10, 2009 at 2:04 pm
yadavmanoj (8/10/2009)
What about NO LOCK in the Select?
It can be highly useful and provide enormouse performance benefits, but it comes with risks of dirty data. If that can be tolerated (perhaps you only need rough aggregates that will get rounded anyway for instance) then it can be highly useful, but it must be used with great care.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
August 10, 2009 at 2:09 pm
Thanks, Gail. You saved me a lot of time 😉
August 10, 2009 at 2:17 pm
Wanderer (8/10/2009)
anyways: 1 useful piece of information I got from a Kalen Delaney course. sp_ is NOT Microsoft's "stored procedures". It actually means special, and works for tables as well. try creating a table SP_table in master, then selecting from the table from a user database.
Now that's interesting. Didn't realise it worked for anything other than procedures. That certainly has possibilities.....
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
August 10, 2009 at 2:19 pm
timothyawiseman (8/10/2009)
Andy DBA (8/10/2009)
I agree with Tim that many readers will assume endorsement by SSC and might not read any of the comments or even be aware of their presence. I also think SSC is judged based on what articles are selected for publication. I also feel the prestige of being published here is proportional to the quality of other published articles.I doubt SSC has the necessary resources to verify every statement made in every article that's published, but presumably articles are at least checked for the obvious such as appropriate content so there's a wide grey area here. It sounds like Steve is leaning more towards less review.
At the risk of getting off topic here, I agree with Andy and Tim Walker. I am a huge fan of SQL Server Central and have had a couple of very minor pieces published here. I think it is exceptional exactly as it is, but I think it could be even better with higher editorial standards and perhaps enforced peer review.
Off the cuff, one thought might be to circulate an article through a few volunteers prior to posting. They could send their comments to the author and send SSC an "approved" or "requires modification" rating. It would certainly take a substantial amount of initial set up, but once set up it could be mostly automated and require only limited intervention from our dear editors here.
So, are you volunteering to be part of a forced peer review group for articles? Then, the more important question, how long will stay with it if you did volunteer? I know I don't have the time to volunteer for such a group. Between work and life, I barely have time for things I want to do.
August 10, 2009 at 2:19 pm
GSquared (8/10/2009)
Have to sort of disagree with you on this one. Temp tables cause statement recompiles in 2005/8, and proc recompiles in 2000, but even those are often/usually better than the execution plans that you end up with if you use table variables.
Table variables have no benefits over temp tables except that they are outside of transactions, and that can be an advantage in a few cases (storing data for Try Catch handling to be used after a rollback is one example). Other than being non-transactional, they have no advantages that I know of. Temp tables, on the other hand, have plenty of advantages. They can indexed beyond the PK-clustered index, they have stats, they can easily pass data back and forth between procs, they can be accessed by dynamic SQL, and they can have their first page of data cached for re-use (2005/8) if that's needed, they can have DDL run on them after creation, they can be created by Select Into (very useful when importing data from files where you don't know before-hand what the column sizes need to be), and more.
I recommend using table variables only if you have to (UDFs, which should be avoided as much as possible anyway for their own reasons), or if you need to hang onto some data despite needing to issue a rollback command.
You have a few excellent points about the advantages of temp tables. Their ease of use with dynamic sql is a big reason I use them and the ability to index them has come into play for me in a few occassions for instance, and your other points about the advantages are very good.
I am not clear on if temp tables generate better execution plans than table variables. I have not formally tested it, but intuitively I would expect the opposite. Eventually I will find time to test it, but if you have testing (or references) I would be very interested in seeing the results.
There are however advantages to using table variables though. The big one being that they exist only during the running of the script where as temp tables exist for the duration of the session (or until explicitly destroyed). This means that there is no risk of them hanging around and infecting another run of the script (or another script that uses the same name). They also will not clutter the tempdb long after they were intended to as some temp table can do in some cases.
Also, while I have not personally verified it, I understand that table variables generally create less overhead in terms of transaction logs and locks. Here is one reference, though again I have not personally verified: http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Viewing 15 posts - 91 through 105 (of 244 total)
You must be logged in to reply to this topic. Login to reply