Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




SQL Problem Solving: Identify ... and then Simplify!

Commonly here at the SQLTeam forums, users seeking assistance explain in long detail their situation and mention how they have millions of rows of data to deal with or dozens of tables and how complicated it all is.  Invariably, those of us offering help will be confused by the requirements (they often aren't stated very clearly or completely) and we'll ask for a small sample of data to work with.

It's funny, but the more I think about it, it's understanding how to generate that small sample set of data, and how to exclude trivial tables and/or columns, that is probably the difference between those who answer questions and those who ask the questions -- not knowledge of SQL or any specific programming technology.  The skill of breaking down a problem into small parts, excluding all the “extra” stuff that is not important, and then clearly stating the requirements and testing on a small sample is probably the most valuable attribute you can have.  Even the exercise of constructing a good set of sample data, ensuring that all possible cases are covered, is a process that requires you to think logically about the problem you are faced with. 

It seems like most users really are not having trouble with T-SQL or JOIN syntax typically, but rather understanding their own problem.  Nine times out of ten, if they can restate it more precisely and create a small sample set of data to work with, the answer is obvious and problem just about solves itself.   It's amazing how many people try to find the answer when they can't even state the question.

What always gets me, though, is that there are a surprisingly large number of people are almost too proud (if that's the right word) to do this.  They are professionals, used to working with huge amounts of data and complex SQL and all that. It is almost beneath them to put aside their 30 GB database and 10,000 lines of code to create a two column table with 2 rows, and another with 4 rows, to spend time testing out joins. If you aren't sure what happens when you do a FULL OUTER JOIN, don't test it on your General Ledger, create some sample data! 

So, take the time to step back on occasion, think logically about what your specific problem is, and instead of trial and error and hoping that things “look” correct, focus on small samples and easily verifiable results.  If you still can't solve it, come to SqlTeam with that sample data and the clearly stated description and I promise you that you'll get the assistance you need!

see also:

Print | posted on Friday, April 29, 2005 2:11 PM | Filed Under [ Techniques ]



# re: Identify ... and then Simplify !

[band starts playing hail to the cheif]

Ya's like you're preaching to the choir...

You running for office or something?

[/and starts playing hail to the cheif]

Got any lawn tickets for Buffet at The Tweeter this June?

I still say Graz should post a "How to Post" on the AskSQLTeam page, or the home page.

4/29/2005 2:39 PM | Brett (Not just a Number...huh?)

# re: Identify ... and then Simplify !

Well Brett, it's sort-of there now, it's getting people to READ IT that's hard. How many Oracle and mySQL questions have there been in the last couple of weeks?
4/30/2005 11:45 AM | robvolk

# re: Identify ... and then Simplify !

I have no problem taking a shot at Oracle, DB2, ect. I know this is a SQL Server site (you do have an Others cat) but it makes life soooooo much easier if the users can post their DDL, DML and expected results. That's not listed their, and it's also only listed on the AskSQLTeam page, not the home page.


Ask SQLTeam
Before you post your question:
We answer questions on Microsoft SQL Server here. No Oracle, no Access, no ASP, no mySQL. Just Microsoft SQL Server.
Please, before you send a question, read the manual. It's called Books Online. Better yet, download your own copy.
Please read our Frequently Asked Questions page. Amazingly enough this answers many questions. People continue to ask questions that we've already answered though. Please don't be one of them. Thanks.
Please search our site before asking a question. You should see a text box on your left for searching. Many of the most common questions we've already answered. If you don't bother to search the site or read the FAQ and ask something we've already covered I'll probably make fun of you.
If you post your question here, please don't also post it in the forums. Choose one or the other. If we don't answer it on the main page, we'll put it in the forums for you.
Include OS and SQL Server version and any service packs.
And please don't email me your questions. We only respond to questions posted on the site.
We like hard questions, articles we missed, other web sites, links to MS SQL Server resources and any news article on Microsoft SQL Server. And did we mention the part about wanting your hard questions?

# re: Identify ... and then Simplify !

About 2 days ago I went to a perl chat room and asked this question:
"Potential employee asked me to send some sample perl stuff. Everything i have is old. Any ideas on a quick and dirty project I could do to impress this maniac?"
Waited a couple of minutes and some regular said:
"if you are asking us then you dont need to get the job."
I reply:
"No need to be sarcastic chum. We are all fine people here. I am just reaching out to the community."
He gave an idea after I said that. Moral of the story, its not a big deal to help somebody out. If a poor shmo asks a question and you feel like that person is not thinking enough, simply say "Ok buddy lets go through this. The problem might not be as bad as you think..." and then continue on until the person gets it. Its the nice thing to do. I do it.

Anyway, I'm gone.

7/2/2005 3:32 AM | Mooch

# re: Identify ... and then Simplify !

How frequently do you see designers using the ORM tools in Visio for this purpose?
5/20/2006 7:44 PM | doofledorfer

# New Question


actually i am having data in one table is as shiow below

sno marks1 mark2 marks3

1 98 99 90
1 98 99 90
1 98 99 90
2 98 97 90
2 98 97 90


sno marks1 mark2 marks3

1 98 99 90
1 0 0 0
1 0 0 0
2 98 97 90
2 0 0 0

i want query for this oput put


6/27/2009 10:26 AM | subhash

# re: SQL Problem Solving: Identify ... and then Simplify!

Hi there,

I am having this problem when executing a cube

Analysis Server Error: More than the maximum of 64,000 dimension member children for a single parent (dimension 'DupTicket^Ticket No'S', level 'Card Num', member '54686').; Time:13/01/2010 14:39:03

Hope someone can help me. URGENT
1/13/2010 8:06 PM | Muhammad jeffry

# re: SQL Problem Solving: Identify ... and then Simplify!

Query for subhash

case when rno = 1 then mark1 else 0 end as mark1,
case when rno = 1 then mark2 else 0 end as mark2,
case when rno = 1 then mark3 else 0 end as mark3
FROM ( SELECT *, row_number() over( partition by sno order by sno ) rno from temp ) a

2/17/2010 5:02 AM | vaibhav
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET