This editorial was originally published on Nov 22, 2013. It is being re-published as Steve is out of town.
Often I find myself wondering about the minimum bar we expect people to clear to be competent in some field. Almost all fields are evolving these days as technology and new ideas are put into practice in medicine, art, construction, law, etc. It seems as though a Renaissance is taking place with the speed and variety at which new information is spreading, usually due to advances in technology.
In our field, working with SQL Server, we certainly see new ideas and enhancements taking place all the time. However there are also a number of core skills that evolve, but at a slower pace. For example, T-SQL grows with each new version of SQL Server, but the core language remains, and many people continue to use the knowledge they have had for years when writing code.
This week I'm curious if we can debate about, and compile, a list of core skills with T-SQL that we think someone ought to understand to be considered competent as a database developer. I'm asking:
What core skills should someone have with T-SQL?
I'll start the list, but feel free to add to it or give me your thoughts. I think someone ought to be able to understand these items and write code to solve problems that involve:
- finding duplicate rows (grouping, joins)
- returning aggregates of single or multiple columns (MAX, MIN, SUM, COUNT)
- return aggregates in groups, or islands. (grouping and aggregates, windowing)
- join multiple tables together on matching, multiple columns (joins)
- find data in one table that doesn't have matches in another (outer joins)
- filtering data (WHERE)
- subqueries and complex CTE joins of data
- create row numbers and join back to a table without numbers (APPLY)
- pivoting data from rows to columns
That's a starting list from a number of questions I've seen, but feel free to add your own skills you think people need.