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.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden