Mentor

Question

Post reply

Mentor

john-902052

SSCommitted

Points: 1578
More actions
June 11, 2010 at 3:12 pm

#219951

I have been working with T-SQL for a few months now fulltime and have learned some of the things that I need. I would like to take my skills to the next level and was wondering if anyone would be interested in helping me?

Viewing 15 posts - 1 through 15 (of 54 total)

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

Which part of the world do you hail from?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

john-902052 SSCommitted Points: 1578 More actions · Answer 2

john-902052

SSCommitted

Points: 1578

June 11, 2010 at 3:28 pm

#1179458

Los Angeles area of California.

LutzM SSC Guru Points: 107049 More actions · Answer 3

john-902052 (6/11/2010)
I have been working with T-SQL for a few months now fulltime and have learned some of the things that I need. I would like to take my skills to the next level and was wondering if anyone would be interested in helping me?

Why just select a single person?

If you decide to hang around here for a while there will be hundred's if not thousand's of SQL Pro's willing to help you. 😉

The SSC community has been (and still is) the best mentor I ever had!

And the best part of it: you'll get more than one opinion / option. 🙂

I would consider a mentor as an "add-on"...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

john-902052 SSCommitted Points: 1578 More actions · Answer 4

Because I want to get to know the person and go deep with the topic. Here people may or may not answer and only will work against a small defined problem that won't get me to the level of learning that I am after. I can read books to solve individual select problems, it is those problems that require experience and insight that I am after.

LutzM SSC Guru Points: 107049 More actions · Answer 5

john-902052 (6/11/2010)
Because I want to get to know the person and go deep with the topic. Here people may or may not answer and only will work against a small defined problem that won't get me to the level of learning that I am after. I can read books to solve individual select problems, it is those problems that require experience and insight that I am after.

I partly agree. I disagree that learning how to solve small defined problems aren't a way to take the skills to the next level.

Insight provided by experienced SQL pro's is something you can get from this site. But I agree, it might not be to the extent you're after, since posts are limited to just written (and sometimes short) explanation. Articles and blogs will be more detailed, but again, "just text".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 6

Look for articles/books by Itzik Ben-Gan. He is great with T-SQL (I think's that his entire job :-)).

I also liked Ken Henderson's stuff, particularly "The Guru's Guide to Transact-SQL". I don't know if/what the latest release/version of this is.

Finally, search the net for "T-SQL challenges", try coding them -- before reading their solution(s) of course :-).

If you want a quick simple SQL not T-SQL challenge, try this:

A studentgrade table has one row per student and test. Each student may have taken anywhere from 0 to 3 (never more) tests.

studentgrade --> (studid int, test# int, score int)

Write a single query -- no subqueries, joins, CTEs, etc. -- that lists:

studentid

# of tests taken

hi score (if only one test, it shows here)

mid score (if only two tests, 2nd shows here; if only one test, will be null)

low score (will only be non-null if all 3 tests are found for that student)

Scott Pletcher, SQL Server MVP 2008-2010

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 7

I don't like those challenges because they teach the wrong thing. There are many times where the resolution of a problem using a single query is just flat out the wrong thing to do for scalability and/or performance.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

john-902052 (6/11/2010)
Because I want to get to know the person and go deep with the topic. Here people may or may not answer and only will work against a small defined problem that won't get me to the level of learning that I am after. I can read books to solve individual select problems, it is those problems that require experience and insight that I am after.

There is a huge difference between an occasional mentor and a private instructor.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 9

I would think in this situation a single query would be the most efficient. I/O (physical and logical) are the real culprit in performance more than 90% of the time.

Although of course it's also a challenge, so the idea is to think a little about everything you have available in a single query.

This not a killer challenge -- it requires some thought, but it's not a back-breaker.

Scott Pletcher, SQL Server MVP 2008-2010

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

scott.pletcher (6/11/2010)
I would think in this situation a single query would be the most efficient. I/O (physical and logical) are the real culprit in performance more than 90% of the time.
Although of course it's also a challenge, so the idea is to think a little about everything you have available in a single query.
This not a killer challenge -- it requires some thought, but it's not a back-breaker.

I was talking about the challenges in general. Thinking about everything you have available in a single query is exactly what I was talking about. It's frequently the worst thing you could possibly do for a given task and "set based" code doesn't mean "all in one query". 😉

I also don't understand your comment about whether it's a killer challenge or not. It simply doesn't matter because doing this one in a single query is the wrong thing to do... it WILL cause an error in the GUI if you follow the current problem description because you cannot use SET statements to suppress warnings about NULL aggregations nor can you suppress row counts which will have the same effect.

Further, the single query requirement suppresses all creativety when it comes to preventing code time bombs. What if the requirements were to change to allow an "unknown" number of tests next year? Why not let folks get creative? When the requirements change I'd much rather have someone tell me "The code needs no changes to handle the change in requirements" than having to take time to rework and regression test the code. 🙂

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 11

scott.pletcher (6/11/2010)
A studentgrade table has one row per student and test. Each student may have taken anywhere from 0 to 3 (never more) tests.
studentgrade --> (studid int, test# int, score int)

Heh... I also love the wording of some of these questions... why would there be a row in this table if a particular student had taken 0 tests? My answer would be that would never happen but without clarification or test data, there's no telling what the person posting the question has on their mind.

And why not teach some best practices as part of the question? For example, the only special character (if any are used at all) that should be in any column name is the underscore. Spaces and symbols should not be used. And why abbreviate "studentid" down to "studid"? Even if we were using Oracle, there's more than enough room to spell out "studentid".

Hmmm... perhaps I should start a new challenge... I'll write some code and we'll have people figure out how the requirements should have been stated. 😀

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 12

Hmmm... perhaps I should start a new challenge... I'll write some code and we'll have people figure out how the requirements should have been stated

Sigh. Consistently all you want to offer is pompous air.

Perhaps you could a few more self-important quotes to your signature so that you could acutally take up a whole page with every post.

Scott Pletcher, SQL Server MVP 2008-2010

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

scott.pletcher (6/14/2010)
Sigh. Consistently all you want to offer is pompous air.
Perhaps you could a few more self-important quotes to your signature so that you could acutally take up a whole page with every post.

Wholly unnecessary and irrelevant to the thread. Jeff has made two significant points here, that the challenges are unrepresentative of real-world coding and also that they may not be the best means to advance the capabilities of a TSQL novice.

Whilst I agree in principle with these opinions, Sebastian et al AFAIK make no claims in either direction so they are, essentially, moot.

Hmmm... perhaps I should start a new challenge... I'll write some code and we'll have people figure out how the requirements should have been stated

Errrrmm...is there another way?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 14

Jeff has made two significant points here, that the challenges are unrepresentative of real-world coding and also that they may not be the best means to advance the capabilities of a TSQL novice.

He made that claim. That doesn't mean it's proven. I've seen whole books of challenges used to help people learn advanced SQL. So all those authors, editors and book publishers are wrong??

Maybe Jeff just doesn't want to consider anything I say long enough to understand that learning a technique can be useful in itself to apply to another situation, regardless of the "purity" of the original code used to learn the technique.

Scott Pletcher, SQL Server MVP 2008-2010