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