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.

Your profile

Tools

Authors

Main Categories

Search

XML Feeds

RSS 2.0: Posts, Comments
Atom: Posts, Comments

What is RSS?

Google Ads

« MADPASS Meeting Wrap-Up

Latest article by SQL Server MVP Erland Sommarskog is a gem »

comments

What does a SQL Server developer need to know?

by SQLDenis on Feb 21, 2011 in categories Data Modelling & Design, Database Programming, Microsoft SQL Server Admin, Microsoft SQL Server

Instapaper

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

Beginner -- < 2 years
Aggregates: COUNT, SUM, MAX/MIN, DISTINCT, GROUP BY, HAVING
JOINs, ANSI-89 and ANSI-92 syntax, Full, Outer, Inner
UNION vs UNION ALL
NULL handling: COALESCE/ISNULL and IS NULL
Subqueries: IN, EXISTS, and inline views, Correlated Subqueries
Constraints, Primary keys, foreign keys, defaults
Normalization
Basic stored procedures and user defined functions programming

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

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.

Social Sitings

Article Views: 5272 views

29 comments

Comment from: Jorge Segarra [Visitor] · http://sqlchicken.com

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.

02/21/11 @ 18:43

Comment from: Jack Corbett [Visitor] · http://www.wiseman-wiseguy.blogspot.com

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.

02/21/11 @ 18:45

Comment from: Naomi Nosonovsky [Member]

Check very interesting answer by Jeff Moden in
this thread

02/21/11 @ 18:47

Comment from: SQLDenis [Member]

Naomi,

to the question "How to perform row by row operations without cursors in sql server 2005?"

I would probably answer that I would use a WHILE loop

02/21/11 @ 18:48

Comment from: Naomi Nosonovsky [Member]

If so, I don't think you'll get any performance gain as WHILE LOOP and CURSOR is essentially the same and CURSORS can be quicker than WHILE LOOP

Check 3 blogs series about CURSORS by Brad Schulz - this is very enjoyable reading and was somewhat an eye opener for me.

02/21/11 @ 18:56

Comment from: SQLDenis [Member]

That is right, but it is still a correct answer to 'How to perform row by row operations without cursors in sql server 2005"

However I don't use cursors period..unless it is for maintenance stuff

02/21/11 @ 18:59

Comment from: Naomi Nosonovsky [Member]

You may ask advanced person some tricky but common problems such as Running Total or FIFO inventory calculation.

Also, you may ask about performance difference in using UNION/UNION ALL or temp table vs table variable.

Also you may ask which techniques the person is using to improve performance of a complex SP.

In addition, you can ask some basic XML related queries (or at least a general understanding of what can be used to work with XML)

02/21/11 @ 19:00

Comment from: Erik [Member]

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.

02/21/11 @ 19:01

Comment from: Naomi Nosonovsky [Member]

BTW, I hope that you allow that person to use online resources during the interview.

02/21/11 @ 19:02

Comment from: SQLDenis [Member]

Emtucifor,

HASH, LOOP, MERGE joins, Lazy/Eager spools

is a great suggestion

02/21/11 @ 19:03

Comment from: SQLDenis [Member]

BTW, I hope that you allow that person to use online resources during the interview.

During phone interview I don't know what they do....on site there is no PC involved..mostly white board

02/21/11 @ 19:04

Comment from: Naomi Nosonovsky [Member]

BTW, UNION/UNION ALL I used on phone interviews as well. About half of the people could not answer.

02/21/11 @ 19:06

Comment from: SQLDenis [Member]

I got some 'creative' answers for UNION vs UNION ALL

one person said that with UNION you can only JOIN 2 tables

Another said that with UNION the columns have to be the same but with UNION ALL they don't

02/21/11 @ 19:09

Comment from: Tim Chapman [Visitor]

· http://www.sqlservernation.com

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.

Just my .02,

Tim

02/21/11 @ 22:08

Comment from: Erik [Member]

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.

02/21/11 @ 22:52

Comment from: SQLDenis [Member]

Tim,

we always do a phone interview first...we did about 30 - 40 phone interviews and we brought 4 people on site for an in person interview

02/22/11 @ 05:52

Comment from: SQLDenis [Member]

Erik,

Loop joins can be good as a hint, see my post Use an OUTER LOOP JOIN for faster performance when one of the tables is much smaller

02/22/11 @ 05:58

Comment from: Adrian Hills [Visitor] · http://www.adathedev.co.uk

Thanks for sharing the list :)

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

02/22/11 @ 06:53

Comment from: rwaters [Visitor]

Denis,

You didn't mention anything about date handling, and Developers deal with dates all the time. And you're the dates guy (I learned best practice for dates from you) !

02/22/11 @ 07:13

Comment from: SQLDenis [Member]

Thanks Rose, I will add it to the final article..this one is mostly to get feedback and also on things that I forgot.

Yes date arithmetic and making the query SARGable is a big one

02/22/11 @ 07:24

Comment from: SQLDenis [Member]

Adrian,

Towards the end I did not even bother looking at the resume since the people with the crappiest resumes had the most knowledge and the people with fantastic resumes were only fantastic on paper

02/22/11 @ 07:26

Comment from: John Haugeland [Visitor]

I'd love more detail on what you would consider to be advanced indexing.

Your email validator incorrectly rejects plusses.

02/22/11 @ 16:09

Comment from: SQLDenis [Member]

John,

with advanced indexing I was thinking about filtered indexes, indexes that use INCLUDED columns, fillfactor, DROP_EXISTING and things like that

02/22/11 @ 16:36

Comment from: chopstik [Member]

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

02/22/11 @ 19:48

Comment from: SQLDenis [Member]

chopstik,

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)

02/23/11 @ 07:36

Comment from: Erik [Member]

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.

02/23/11 @ 13:56

Comment from: chopstik [Member]

From your list:

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

Advanced indexing (again, "advanced"?)
Partitioned functions (what is this?)
Using Dynamic Management Views to tune your application (?)
Indexed Views and the use of NOEXPAND in standard edition (?)
Concurrency and locking (?)

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.

02/23/11 @ 19:15

Comment from: SQLDenis [Member]

Windowing functions and CTEs

windowing/ranking functions are RANK(), DENSE_RANK(), ROW_NUMBER() and NTILE()

Here is an example from Books On line

USE AdventureWorks2008R2;

GO

SELECT p.FirstName, p.LastName

    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number'

    ,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'

    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank'

    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS 'Quartile'

    ,s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson s 

    INNER JOIN Person.Person p 

        ON s.BusinessEntityID = p.BusinessEntityID

    INNER JOIN Person.Address a 

        ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL 

    AND SalesYTD <> 0;

a CTE (Common Table Expression) looks like this

;WITH CTE AS (SELECT * FROM sysobjects)

SELECT * FROM CTE

Here is an example from Books On Line

USE AdventureWorks2008R2;

GO

-- Define the CTE expression name and column list.

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)

AS

-- Define the CTE query.

(

    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear

    FROM Sales.SalesOrderHeader

    WHERE SalesPersonID IS NOT NULL

)

-- Define the outer query referencing the CTE name.

SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear

FROM Sales_CTE

GROUP BY SalesYear, SalesPersonID

ORDER BY SalesPersonID, SalesYear;

GO

02/24/11 @ 07:22

Comment from: Naomi Nosonovsky [Member]

This discussion at MSDN forum can help in regards to differences between clustered and non-clustered indexes and also Kalen Delaney article

02/24/11 @ 09:46

Comment feed for this post

LessThanDot

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

What does a SQL Server developer need to know?

About the Author

29 comments

Leave a comment

LessThanDot

LTD Social Sitings

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

What does a SQL Server developer need to know?

About the Author

29 comments

Leave a comment