LessThanDot

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

I have been interviewing people for a SQL Server position for the past six weeks and all I can say that I am glad it is over. What a frustrating experience, people with over 10 years’ experience could not tell me the difference between UNION and UNION ALL, most of the people never heard of TRUNCATE. We finally got our guy and he starts tomorrow.
So I would like to ask you the reader what a SQL Server developer should know when he falls into these levels.

Beginner — < 2 years
Intermediate -- between 2 and 5 years
Advanced –- over 5 years

Here is what I think it should be, leave me a comment if you have something to add or disagree. I am only focusing on T-SQL here, no SSIS, SSRS, Powershell etc etc etc

Intermediate — between 2 and 5 years
Everything for the previous level plus
Dynamic SQL and parameterized queries
Deadlock, how to detect and how to avoid them
Windowing functions and CTEs
Execution plans and what they mean, how to read them
Profiler: Creating a trace, trace events and how to save a trace
Trapping errors
Isolation levels
Transactions: rollback, commit, using XACT_ABORT, Try, Catch
What a SARGable query is and how to avoid non SARGable queries
Truncate, BCP, BULK INSERT
Difference between clustered index and non clustered index
Triggers and how to write triggers that affect multirow operations
Advanced stored procedures and user defined functions programming
Advanced data modeling, cascade delete.
Linked servers
How to avoid conversions and how to choose the correct data types

Advanced – over 5 years
Everything for the previous two levels level plus
Parameter sniffing
Advanced indexing
Partitioned functions
Settings like ANSI_NULLS, ARITHABORT and how they can affect execution plans
Using Dynamic Management Views to tune your application
Indexed Views and the use of NOEXPAND in standard edition
Query and table hints
Concurrency and locking

I am sure I forgot a ton of stuff, leave me a comment if you think I placed a skill in the wrong skill level, also leave me a comment if you want to add something I have forgotten 2> 2> 2> 2> 2> 2>

About the Author

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.

30 Comments

The general list looks good but there are a few things I’d probably shift around. TRUNCATE should probably fall within beginner’s. Then again my POV is that of a DBA so I might be a bit biased. Those transaction items might also fall under beginner. Again, my view is skewed as I’m coming from administrative background.

Great list and timely for me as I’m in looking for work. Gives me a list of things to “bone up” on.

I’ve got over 10 years, but there are things in your list that I have not used or dealt with. I’ve done reading on most, but in an interview I’d definitely miss some answers because I haven’t used it, so I wouldn’t remember it.

There are only 3 items in your list that I don’t feel like I have down cold: ARITHABORT, XACT_ABORT, and NOEXPAND in standard edition (though I think I know what it is). I also know a little about what the first two do, but obviously need to read up on them.

As for the groups, here would be my tweaks:
- Add collations and ::fn_helpcollations() to the intermediate group.
- ESCAPE clause of LIKE as intermediate.
- Reading execution plans should start in the intermediate phase, but really using them to their full extent can’t come until the advanced phase with an understanding of HASH, LOOP, MERGE joins, Lazy/Eager spools, index lookups, statistics’ effect on execution plan choices, join order, tempdb spill, etc.
- For intermediate add EXCEPT and INTERSECT.
- Experts should know about database design and normalization, even if they don’t design databases, because this helps with thinking about queries and you know that developers interact with others and have influence on design processes. They should be able to know when they’re working with junk or not so they don’t think that the one database they’re learning from is best practice when it’s not.
- Experts should be able to nimbly use CROSS/OUTER APPLY and know why they’re doing it and when not to.
- Experts should know how many bytes each storage type takes or know when to look it up (e.g., in many cases bigint can save a byte of storage over an equivalent decimal value).
- I think locking should be learned in the intermediate phase, not necessarily all the ins and outs (that can wait until expert) but the developer should be aware of how negatively improper locking can affect a busy system and some basic rules of thumb for avoiding problems (such as keeping locks for as short as possible!).

One more thing… I think some people could advance through these phases faster than 5 years, or at least make great progress in these categories. Others may work for many years or more and not make it. I think that is actually the point of your post, but I’m just pointing out that it may be more useful to describe the knowledge/competencies rather than the duration of experience. Some folks are self-teachers and driven to learn and improve. Others are not.

Nice list, but I do think some things should be shifted around a bit…namely the clustered vs nonclustered question. I’ve interviewed scores of DBA and DB-dev candidates over the years, and the best thing you can do is to give a phone interview before you waste your time on the face to face. I also tend to start the candidate down a path of questions and progressively ask them harder and harder questions along that path. I’ve learned over the years to not expect too much from those that have many years of “experience”…knowing some plan iterators usually makes me pretty happy.

So, there definitely is the “tech” aspect of the interview. But, there is also the element of feeling you get from the candidate. There has been a few times when I’ve hired a person with much less experience because I’ve got a great feeling from them that they would work hard to make up for the experience that they lack. Has worked out great in those situations.

One of the really big things I’ve seen performance-wise is when the optimizer picks a loop join when it should be using a hash join. This is either because there are no statistics, they’re out of date, or the data is anomalous even though the statistics are good (or parameter sniffing chose an execution plan with different parameters where a loop join was appropriate). Once there are tens of thousands or even millions of rows, a loop join is going to send the query into la-la land for 20 or 30 minutes, maybe longer. This kind of knowledge seems like something intermediates could use, but in my experience only experts know.

It’s very difficult to gauge someone’s skill level based on how long they’ve been using a technology – as you’ve encountered, longer period of experience != greater level of knowledge necessarily. I guess in general there’s a few factors here:
- the intensity of work using that technology (e.g. spending 80% of working hours on db design and development for 5 years would more than likely lead to greater knowledge than spending 10% of time for 10 years).

- the environment(s) in which the person used it. Working for 5 years at the same company with Standard Edition only with an imposed set of “limitations” (e.g. “we don’t like SQLCLR so don’t use it”) will likely mean gaining less experience across the board as opposed to working as a SQL consultant across x different environments/sectors a year.

- in one employer’s eyes, an employee may be seen as being at an advanced level if they know their system inside out having had 5 years experience at the company. In a potential new employer’s eyes, that person could be seen as being at a beginner’s level as it turns out they know exactly what they needed to know for their current job and nothing more. i.e. skill level is relative – it’s all in the eye of the beholder!

My past experience of reviewing CVs and interviewing is never to bank on the period of time as being a reliable indicator of what to expect knowledge-wise. I would try to give a weighting to someone’s “length of service” by prodding for an indication of intensity of use and talking about parts of the technology they haven’t used to check their general awareness outside of the comfort zone of their current job (e.g. if they’ve never needed to use Linked Servers before, seeing if they at least know a bit of the “why’s and what for’s”)

I wonder a little about your idea of a SQL Server developer. Some of those things (especially in the mid to expert range) seem like things that I have seen/heard of DBA’s doing, not necessarily developers. But then again, much of my experience is as a “developer” who happens to also work with databases – there has not been specificity to a given paradigm. So would the same hold for someone who has done database programming without necessarily working solely with db programming? (With my experience, I would rank as a beginner with some intermediate skills even though I’ve ~5 years experience with SQL Server.)

which specific things from the mid to expert range do you think is a DBA task. Setting up linked servers is but as a developer you should know that too. I would go as far as saying that as an expert level developer you need to know also about index maintenance like rebuilding or defragmenting(reorg)

In my opinion a DB developer can’t really be an expert unless he could also do a DBA’s job in a pinch, or at least have a good handle on what he would need to learn to do the job.

At my work I usually push things like linked servers and permissions problems and database backups to the DBA. But not always… sometimes I am doing something small or quick or on my Dev server that the DBA doesn’t touch.

Imagine a mechanic who didn’t know how to drive a car, or a racecar driver who knew nothing about engines. This wouldn’t stop either one from being pretty good at his job, but he could never be dynamite at what he does without at least some cross-discipline knowledge. Interestingly, it seems to me that it is troubleshooting time that this knowledge really is valuable, not when everything is humming along smoothly.

Deadlock, how to detect and how to avoid them (this is a DBA concern at my current location – if I am involved, it is in trying to troubleshoot it and figure out how best to fix but I’d never even come across deadlocks until recently)
Windowing functions and CTEs (not even sure what this is)
Isolation levels (not sure about this either)
Advanced data modeling, cascade delete. (what is your definition of “advanced”?)
Linked servers

Part of my thoughts on this probably emanate from my own ignorance but are these really “SQL developer” things or stuff that could go either way? I guess that the definition of a SQL developer is somewhat fluid depending upon the circumstances.