Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 31,456 through 31,470 (of 66,749 total)

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

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 1

GSquared (11/3/2011)
I just found a stored procedure from my predecessor at my current job, and it just kind of made my jaw drop in the elegant simplicity of it's complete stupidity.
It looked basically like this:
create proc dbo.MyProc
as
set nocount on;
declare @TableVariable (
columns, including PK definition)
insert into @TableVariable
select distinct columns
from tables
where business rules
select distinct * from @TableVariable
The actual query involves several computed columns and joins between four different tables. It works as designed.
I discovered this query because it started to throw a primary key violation on what everyone here thought was just a Select statement. Note that it doesn't actually DO anything with the table variable except select from it. No data modifications. Not even a Where clause on the select.
How does someone come up with a design like that? Why? What weird logic process leads to doing all that just to select some data.
Sorry, had to vent, and thought you guys might get some entertainment out of this.
PS: Even our business analyst thought this one was a bit odd in terms of working a Select that way. He doesn't even write code.

There is a decent reason to write a query like that. If you want to be absolutely sure your columns are typed properly on the way out. The repeated "DISTINCTS" are a little weird, but if you've created a stored procedure for an SSIS package or some program that requires specific data types (and those data types don't always align well with SQL data types) it can help to insert into a temp table and then just do a select from that. You shouldn't be doing a SELECT * at that point, and it makes much more sense if you're doing multiple modifications, but it does solve some problems. And by putting it in the table, it helps readability.

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

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 2

Koen Verbeeck (11/7/2011)
Stefan Krzywicki (11/7/2011)
But there isn't a 2008 SSIS forum!
No, but there is a general SSIS forum in the Data Warehouse section 🙂
(to be honest, that forum is not used frequently. The SSIS 2005 is the most active one, even for 2008 questions)

It's not terribly clear which forum to use for SSIS questions. The 2005 version doesn't seem to be read by a lot of 2008 people (or maybe I just asked questions no one could answer). It would be nice to have a separate Business Intelligence group for SSIS, SSAS and SSRS of any version that isn't directly related to Data Warehouse. But that might just make the problem worse.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 3

Stefan Krzywicki (11/7/2011)
... The repeated "DISTINCTS" are a little weird...

I've actually run into situations where I need duplicate DISTINCTs because of the way the data comes out of a complicated query with multiple JOINs. I can't remember the exact situation off the top of my head, but I do recall putting data into a temp table with a SELECT DISTINCT, then (on my final SELECT from the temp table) having to put SELECT DISTINCT again because if I didn't, I'd still get duplicate records.

I think it had something to do with OUTER JOINs in the original query... If I find the example anytime soon, I'll post samples.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Totally different topic: this thread asks how to format datetime values in T-SQL.

I think this is a very bad habit to kick and I put together an article (well, it was meant to be a post for my blog, but I'm sure it would have a larger audience here).

Is anyone interested in taking a look at the draft and give some feedback?

-- Gianluca Sartori

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 5

I'd stick SSIS questions in the Integration Services forum: http://www.sqlservercentral.com/Forums/Forum364-1.aspx

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Gianluca Sartori (11/7/2011)
Totally different topic: this thread asks how to format datetime values in T-SQL.
I think this is a very bad habit to kick and I put together an article (well, it was meant to be a post for my blog, but I'm sure it would have a larger audience here).
Is anyone interested in taking a look at the draft and give some feedback?

Yes - I'd love to do that.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95461 More actions · Answer 7

Gianluca Sartori (11/7/2011)
Totally different topic: this thread asks how to format datetime values in T-SQL.
I think this is a very bad habit to kick and I put together an article (well, it was meant to be a post for my blog, but I'm sure it would have a larger audience here).
Is anyone interested in taking a look at the draft and give some feedback?

I'll take a look. If you still have my email, just send it there. (PM me if you need it)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Thanks Jason and Wayne!

I still have to wite a decent intro and include performance tests results.

I must warn you: lots of code in there.

Hope it turns worth publishing with your suggestions!

-- Gianluca Sartori

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

Gianluca Sartori (11/7/2011)
Totally different topic: this thread asks how to format datetime values in T-SQL.
I think this is a very bad habit to kick and I put together an article (well, it was meant to be a post for my blog, but I'm sure it would have a larger audience here).
Is anyone interested in taking a look at the draft and give some feedback?

I agree it isn't the best way to do it, but sometimes you may be constrained by the application to do it. I commented on that thread and did give an answer, but added my opinion that this isn't the best way either.

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

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

Someone with more patience than I have want to finish this? http://www.sqlservercentral.com/Forums/Topic1197753-1550-1.aspx

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 11

GilaMonster (11/7/2011)
Someone with more patience than I have want to finish this? http://www.sqlservercentral.com/Forums/Topic1197753-1550-1.aspx

I don't have more patience, but I'm less annoyed :-D.

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

Ninja's_RGR'us (11/7/2011)
GilaMonster (11/7/2011)
Someone with more patience than I have want to finish this? http://www.sqlservercentral.com/Forums/Topic1197753-1550-1.aspx
I don't have more patience, but I'm less annoyed :-D.

That works too.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

GilaMonster (11/7/2011)
Someone with more patience than I have want to finish this? http://www.sqlservercentral.com/Forums/Topic1197753-1550-1.aspx

Chimed in.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

GilaMonster (11/7/2011)
Ninja's_RGR'us (11/7/2011)
GilaMonster (11/7/2011)
Someone with more patience than I have want to finish this? http://www.sqlservercentral.com/Forums/Topic1197753-1550-1.aspx
I don't have more patience, but I'm less annoyed :-D.
That works too.

... getting there now tho :pinch:!

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

GilaMonster (11/7/2011)
Someone with more patience than I have want to finish this? http://www.sqlservercentral.com/Forums/Topic1197753-1550-1.aspx

So you're un-annoyed now? Or did we just transfer that from the op to us? :hehe: