September 19, 2013 at 7:18 am
Greg Edwards-268690 (9/19/2013)
venoym (9/19/2013)
andrew gothard (9/19/2013)
Stefan Krzywicki (9/18/2013)
GilaMonster (9/18/2013)
L' Eomot Inversé (9/18/2013)
Don't need to is surely an understatement: you are not allowed to, I think?If you mean the SELECT * INTO, unfortunately that's allowed. I've cleaned so many of those out of client code.
I can't convince them that SELECT * INTO is bad.
Actually, in my experience the SELECT * INTO #A ... type pattern you describe is *extremely* useful. Whenever I've seen it used, it's highly correlated with a codebase so eyebleedingly bad that the perpatrator(s) should be escorted from the premesis as soon as possible.
Is this code, by any chance, also stuffed with over/mis use of temporary tables because the person doesn't seem entirely clear on what they're doing? Just a guess.
I've seen it in places with cursor (sorry for the foul language) misuse mostly...
:-):-):-)
So what happens when you add a column (or several) to #A?
Or change the order of some of the columns?
Does the processing break?
What risk / disruption to the business would this cause?
When you take the time to define, it is much easier to troubleshoot when something breaks.
And much clearer what is being done to everyone that follows.
Dynamic is one thing, but it takes a bit more thought to do it correctly.
This always reminds me of the Excel recorded macro.
When rows and column counts change, most of them break.
The response is always "We won't do that" or "We'll be careful"
With defined temp tables with indexes and proper INSERT INTO statements I've managed to shave a little over a minute off the runtime, but that's not enough to get them to switch to my code.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 19, 2013 at 11:20 am
GilaMonster (9/18/2013)
L' Eomot Inversé (9/18/2013)
Don't need to is surely an understatement: you are not allowed to, I think?If you mean the SELECT * INTO, unfortunately that's allowed. I've cleaned so many of those out of client code.
No, what I meant is that if you write SELECT <whatever> INTO #T FROM <rest of select statement> you can't also explicitly define #T, you only have the implicit definition generated by that SELECT INTO statement - you are not allowed to have an explicit table definition when the table is created by SELECT INTO. I think that's a reason to avoid SELECT INTO most of the time.
SELECT * INTO is worse than SELECT <explicit select list> INTO because the implied table definition will change any time the meaning of * changes, which would be any time a column is added to or removed from one of the base tables used in the FROM clause.
Besides, SELECT * is something which should be used only to generate the rowset being returned from a particular type of diagnostic stored proc where the output intended for human eyes or for something which automatically formats arbitrary rowsets for human eyes (eg EXCEL or SSMS or some bits of diagnostic software). So it should be avoided almost all the time.
So what with the idea the idea that SELECT * INTO is worse than SELECT <list> INTO which itself shoud be avoided most of the time, and SELECT * is in itself something whose use should be severely restricted, I can't imagine why anyone would ever want to have SelECT * INTO anywhere in production code; of couse when writing something quickly to run only once I might use it, but only if I knew for certain that I was going to throw that code away, never save it wanywhere at all. But I suspect that I may have written and sometimes kept idiotic SQL like that back in the days when I was young and innocentless old and more careless.
Tom
September 19, 2013 at 11:29 am
Stefan Krzywicki (9/19/2013)
Greg Edwards-268690 (9/19/2013)
venoym (9/19/2013)
andrew gothard (9/19/2013)
Stefan Krzywicki (9/18/2013)
GilaMonster (9/18/2013)
L' Eomot Inversé (9/18/2013)
Don't need to is surely an understatement: you are not allowed to, I think?If you mean the SELECT * INTO, unfortunately that's allowed. I've cleaned so many of those out of client code.
I can't convince them that SELECT * INTO is bad.
Actually, in my experience the SELECT * INTO #A ... type pattern you describe is *extremely* useful. Whenever I've seen it used, it's highly correlated with a codebase so eyebleedingly bad that the perpatrator(s) should be escorted from the premesis as soon as possible.
Is this code, by any chance, also stuffed with over/mis use of temporary tables because the person doesn't seem entirely clear on what they're doing? Just a guess.
I've seen it in places with cursor (sorry for the foul language) misuse mostly...
:-):-):-)
So what happens when you add a column (or several) to #A?
Or change the order of some of the columns?
Does the processing break?
What risk / disruption to the business would this cause?
When you take the time to define, it is much easier to troubleshoot when something breaks.
And much clearer what is being done to everyone that follows.
Dynamic is one thing, but it takes a bit more thought to do it correctly.
This always reminds me of the Excel recorded macro.
When rows and column counts change, most of them break.
The response is always "We won't do that" or "We'll be careful"
With defined temp tables with indexes and proper INSERT INTO statements I've managed to shave a little over a minute off the runtime, but that's not enough to get them to switch to my code.
Speed is secondary to risk.
I don't suppose they used the word 'never'? 😀
Especially when the new part of 'we' comes on board?
Certainly not best practice - rely on self imposed remembering not to do something, when it it easy to do it right.
Hopefully they get the support call at 2:30 am, not you.
September 19, 2013 at 11:34 am
Greg Edwards-268690 (9/19/2013)
Stefan Krzywicki (9/19/2013)
Greg Edwards-268690 (9/19/2013)
venoym (9/19/2013)
andrew gothard (9/19/2013)
Stefan Krzywicki (9/18/2013)
GilaMonster (9/18/2013)
L' Eomot Inversé (9/18/2013)
Don't need to is surely an understatement: you are not allowed to, I think?If you mean the SELECT * INTO, unfortunately that's allowed. I've cleaned so many of those out of client code.
I can't convince them that SELECT * INTO is bad.
Actually, in my experience the SELECT * INTO #A ... type pattern you describe is *extremely* useful. Whenever I've seen it used, it's highly correlated with a codebase so eyebleedingly bad that the perpatrator(s) should be escorted from the premesis as soon as possible.
Is this code, by any chance, also stuffed with over/mis use of temporary tables because the person doesn't seem entirely clear on what they're doing? Just a guess.
I've seen it in places with cursor (sorry for the foul language) misuse mostly...
:-):-):-)
So what happens when you add a column (or several) to #A?
Or change the order of some of the columns?
Does the processing break?
What risk / disruption to the business would this cause?
When you take the time to define, it is much easier to troubleshoot when something breaks.
And much clearer what is being done to everyone that follows.
Dynamic is one thing, but it takes a bit more thought to do it correctly.
This always reminds me of the Excel recorded macro.
When rows and column counts change, most of them break.
The response is always "We won't do that" or "We'll be careful"
With defined temp tables with indexes and proper INSERT INTO statements I've managed to shave a little over a minute off the runtime, but that's not enough to get them to switch to my code.
Speed is secondary to risk.
I don't suppose they used the word 'never'? 😀
Especially when the new part of 'we' comes on board?
Certainly not best practice - rely on self imposed remembering not to do something, when it it easy to do it right.
Hopefully they get the support call at 2:30 am, not you.
Tried those arguments, no dice. They cared about speed, which is why I focused on that.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 19, 2013 at 2:46 pm
I certainly don't advocate using SELECT * INTO #mytable, because there usually is a way around that (assuming that you control the output of the query or SP you are inserting from). We got very badly bitten by code that was written like that for 6.5 when we upgraded to 2000. What was happening was that in 2000 it assumed that the first row returned from the query should determine the "size" of the column. So if the first row returned 1 for the first row, then surely a tinyint should do? Lots of rework during that migration that could have been avoided. Anyhow, it taught me a lesson. Oh btw, I didn't write that code, and if I see it in "production-ready" code, I'll sincerely question it. Usually it goes out of the door.
September 19, 2013 at 2:52 pm
Greg Edwards-268690 (9/19/2013)
Speed is secondary to risk...
Speed? Speed of what? Runtime? Coding? Surely it can't make a difference creating a temp table and inserting into it rather than let SQL create it? Yes, code might break when you have an SP as input, but it might also break if you have a SELECT * INTO. My mantra, stay on the safe side.
September 20, 2013 at 5:15 am
venoym (9/19/2013)
andrew gothard (9/19/2013)
Stefan Krzywicki (9/18/2013)
GilaMonster (9/18/2013)
L' Eomot Inversé (9/18/2013)
Don't need to is surely an understatement: you are not allowed to, I think?If you mean the SELECT * INTO, unfortunately that's allowed. I've cleaned so many of those out of client code.
I can't convince them that SELECT * INTO is bad.
Actually, in my experience the SELECT * INTO #A ... type pattern you describe is *extremely* useful. Whenever I've seen it used, it's highly correlated with a codebase so eyebleedingly bad that the perpatrator(s) should be escorted from the premesis as soon as possible.
Is this code, by any chance, also stuffed with over/mis use of temporary tables because the person doesn't seem entirely clear on what they're doing? Just a guess.
I've seen it in places with cursor (sorry for the foul language) misuse mostly...
Aye, good point, the two do tend to go hand in hand. With the "There's nothing wrong with *my* code *you* just don't understand it!" attitude I so love as an added bonus.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 20, 2013 at 7:02 am
Are you good folks saying that any form of SELECT/INTO is bad? If so, why?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2013 at 7:12 am
Jeff Moden (9/20/2013)
Are you good folks saying that any form of SELECT/INTO is bad? If so, why?
I don't know how the resulting columns are typed. I did a cursory look yesterday and didn't find anything. When I have time I plan on looking more.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 20, 2013 at 7:13 am
Jeff Moden (9/20/2013)
Are you good folks saying that any form of SELECT/INTO is bad? If so, why?
I sense a frozen pork chop being readied for launch! :w00t:
September 20, 2013 at 7:16 am
It strikes me as somewhat amusing to see someone post a question, which is obviously not a "help me fix this" but is instead a "help me understand why this," and people immediately start trying to fix the problem.
Didn't happen here, but over on the TechNet SQL forums. Person asked if someone could explain why Agent jobs still work when SA owns them but is disabled, but fail when a different account (belonging to the SysAdmin role) owns them and is disabled.
Pointed him to a posting on SQLBlog which relatively clearly explained the reasons...
September 20, 2013 at 7:17 am
On an unrelated note, so much fun working for government...
When said govm't seems heck-bent on going into shutdown, potentially leaving one payless for who knows how long...
September 20, 2013 at 7:40 am
jasona.work (9/20/2013)
On an unrelated note, so much fun working for government...When said govm't seems heck-bent on going into shutdown, potentially leaving one payless for who knows how long...
Yeah, but government "shutting down" isn't really shutting down in the strict sense of the phrase. The work still needs to get done and when this has happened in the past, retroactive pay has been given every time. I have a friend who's worked for the VA for a long time and he's seen this several times. He's not worried. The government will still pay employees, social security, medicare and defense spending. They'll still collect taxes from everyone, pay the military and pay themselves. Their goal is only to inconvenience lots of people and then get on TV and spew the rhetoric to make the other party look bad. It's a sad situation, which is why I try to avoid political situations as much as possible.
I know it can be stressful to government employees, but I wouldn't worry too much about it. Like I said, my friend who works at the VA has been through it before and he's not worried.
September 20, 2013 at 8:43 am
Ed Wagner (9/20/2013)
jasona.work (9/20/2013)
On an unrelated note, so much fun working for government...When said govm't seems heck-bent on going into shutdown, potentially leaving one payless for who knows how long...
Yeah, but government "shutting down" isn't really shutting down in the strict sense of the phrase. The work still needs to get done and when this has happened in the past, retroactive pay has been given every time. I have a friend who's worked for the VA for a long time and he's seen this several times. He's not worried. The government will still pay employees, social security, medicare and defense spending. They'll still collect taxes from everyone, pay the military and pay themselves. Their goal is only to inconvenience lots of people and then get on TV and spew the rhetoric to make the other party look bad. It's a sad situation, which is why I try to avoid political situations as much as possible.
I know it can be stressful to government employees, but I wouldn't worry too much about it. Like I said, my friend who works at the VA has been through it before and he's not worried.
Still a headache.
And if you notice when this happens, how lite the traffic becomes, you see how many are pawns in this grandstanding.
Just wish they could bury the egos and get the job done.
Special Session equates to Extra Pay to many of them.
If this was changed, I think it would make a difference.
Definitely agree - the work piles up.
September 20, 2013 at 9:16 am
Greg Edwards-268690 (9/20/2013)
Ed Wagner (9/20/2013)
jasona.work (9/20/2013)
On an unrelated note, so much fun working for government...When said govm't seems heck-bent on going into shutdown, potentially leaving one payless for who knows how long...
Yeah, but government "shutting down" isn't really shutting down in the strict sense of the phrase. The work still needs to get done and when this has happened in the past, retroactive pay has been given every time. I have a friend who's worked for the VA for a long time and he's seen this several times. He's not worried. The government will still pay employees, social security, medicare and defense spending. They'll still collect taxes from everyone, pay the military and pay themselves. Their goal is only to inconvenience lots of people and then get on TV and spew the rhetoric to make the other party look bad. It's a sad situation, which is why I try to avoid political situations as much as possible.
I know it can be stressful to government employees, but I wouldn't worry too much about it. Like I said, my friend who works at the VA has been through it before and he's not worried.
Still a headache.
Just wish they could bury the egos and get the job done.
Special Session equates to Extra Pay to many of them.
Greg - I think you hit the nail on the head there. No matter what party they're in, most of them are out for themselves. They may go into politics with the most altruistic and noble intentions, but all that power corrupts people and it's very sad. My heart definitely goes out to the people who actually do the work and have uncertainty in their lives because of the politicians' games and political posturing.
Viewing 15 posts - 41,311 through 41,325 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply