ChrisM@Work (11/26/2013)

---

Jeff Moden (11/25/2013)

---

rlortega (11/25/2013)

---

So what minimum T-SQL skills are necessary if you're a DBA, not a developer?

To be honest... learning to read minds and to anticipate "what's coming" by keeping in contact with the business users and the development team. There's nothing like having a 60% full disk system and not having a clue about getting a "giant client" that will double the disk requirements virtually overnight.

For T-SQL skills, you really need to be a Ninja at it if your duties include "performance tuning". You should also have a very deep understanding of system tables, DM_DBs, and dynamic T-SQL.

Interesting you should go beyond "core skills" here, Jeff. Over the last year or two I've been compiling a list of skills which I think a "senior SQL Server developer" should have mastered. That of course includes performance tuning. I don't wish to railroad Steve's editorial, perhaps any discussion of it should go elsewhere. It's not a complete list either - it's heavily biased towards stuff myself and friends have covered:

Activity Monitor

Aggregates: GROUP BY and OVER(PARTITION BY).

All of the window functions introduced with 2005. Row / group numbering; NTILE(), RANK(), DENSE_RANK, ROW_NUMBER() and aggregate; SUM(), AVG(), COUNT(), MIN(), MAX().

All join types: LEFT/RIGHT/FULL/INNER/OUTER/CROSS. Joining a parent table 1 to 1 to a child table with many matching rows, returning the correct child row based on criteria such as date.

At least a basic understanding of execution plans; the three join types, key (bookmark) lookups & covering indexes, seeks vs. scans, row counts.

Cascaded CROSS APPLY.

Choosing the best datatype.

CTE’s – not just how to use them, but how or when not to use them e.g. excessive nesting / cardinality changes through aggregation. Understanding why the same CTE referenced more than once in the same query can generate different results.

DDL – creating and modifying sql server objects.

Derived tables.

Documentation and formatting – make it work, make it fast, make it pretty.

Dynamic SQL: EXEC() and sp_executesql, scope of variables and #temp tables. SQL Injection.

Expensive queries – identification, resolution.

Indexes – choice of cluster key(s), monitoring index usage with DMV’s and recommending changes. SARGability.

iTVF’s vs. other function types – usage and performance. Schemabinding.

Issues with FLOAT datatype.

Moving data into and out from SQL Server – bcp and DTS/SSIS.

rCTE’s for hierarchies and number/text crunching e.g. running totals.

Pivot & Crosstab, Unpivot and CROSS APPLY VALUES.

Profiler.

Row/table constructors.

Running totals methods – TJ, cursor, QU, rCTE.

Staggered sequences: islands’n’gaps.

Tally (numbers) tables – hard & inline.

Use of #temp tables to break up complex queries. Indexes on #temp tables.

Working with DATETIME data having a populated time component.

Working with NULL, implication with IN ().

Enjoy.