Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««4,1304,1314,1324,1334,134»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 7:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:18 AM
Points: 843, Visits: 7,381
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
Post #1496381
Posted Thursday, September 19, 2013 11:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 7,804, Visits: 9,556
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
Post #1496506
Posted Thursday, September 19, 2013 11:29 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 672, Visits: 6,767
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.
Post #1496511
Posted Thursday, September 19, 2013 11:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:18 AM
Points: 843, Visits: 7,381
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
Post #1496514
Posted Thursday, September 19, 2013 2:46 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
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.


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1496594
Posted Thursday, September 19, 2013 2:52 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
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.


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1496595
Posted Friday, September 20, 2013 5:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 306, Visits: 3,435
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.
Post #1496807
Posted Friday, September 20, 2013 7:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:25 PM
Points: 35,372, Visits: 31,925
Are you good folks saying that any form of SELECT/INTO is bad? If so, why?

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1496865
Posted Friday, September 20, 2013 7:12 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:18 AM
Points: 843, Visits: 7,381
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
Post #1496872
Posted Friday, September 20, 2013 7:13 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 672, Visits: 6,767
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!
Post #1496873
« Prev Topic | Next Topic »

Add to briefcase «««4,1304,1314,1324,1334,134»»»

Permissions Expand / Collapse