November 29, 2013 at 12:04 pm
Jeff Moden (11/29/2013)
The exception to that rule is that spelling errors are simply not forgivable and incorrect substitution of correctly spelled words (for example, you meant to use the word "data" but used "date" instead) are mostly not forgivable (I'll allow only 1 or 2) because if you don't care that much about your own resume, why would you care about the job at hand?
Good call.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 29, 2013 at 4:53 pm
Gary Varga (11/27/2013)
I am in shock.With all that has been posted here I am either a SQL Server god (because the majority are rubbish - thanks for the ego boost Jeff) or a SQL Server bottom feeder (because I haven't used some of the techniques - come on Jeff, I'm not that bad).
It's not my job to judge but, you good Sir... I'd work with you anytime.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2013 at 5:31 pm
Jeff Moden (11/29/2013)
Gary Varga (11/27/2013)
I am in shock.With all that has been posted here I am either a SQL Server god (because the majority are rubbish - thanks for the ego boost Jeff) or a SQL Server bottom feeder (because I haven't used some of the techniques - come on Jeff, I'm not that bad).
It's not my job to judge but, you good Sir... I'd work with you anytime.
Thanks Jeff. Likewise.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 30, 2013 at 6:02 pm
Gary Varga (11/29/2013)
Jeff Moden (11/29/2013)
The exception to that rule is that spelling errors are simply not forgivable and incorrect substitution of correctly spelled words (for example, you meant to use the word "data" but used "date" instead) are mostly not forgivable (I'll allow only 1 or 2) because if you don't care that much about your own resume, why would you care about the job at hand?Good call.
I remember a weekly newletter at work, with a picture of a sign.
Authorized Personal Only.....talking about an aisle not to be used except by certain personnel.
I chuckled as I pointed it out to the gal putting out the newsletter.
She was amazed at how many eyes had looked and not opened their mouth.
I guess it was close enough for everyone.
Imagine what someone from the outside taking a tour might think.
The aisle got a new sign within a week.
Sloppy can be very bad at times.
There are many jobs out there which require attention to the little details.
Excellent point!
December 1, 2013 at 5:13 pm
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.
Ya know, the more I think about it, the more I'd like someone to write an article about this list, Chris. Are you up for it? Maybe even make it the "go to" for people that want some guidelines on what it takes to be a Senior level SQL Developer and maybe the T-SQL side of what it takes to be an application DBA.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2013 at 7:03 pm
Greg Edwards-268690 (11/30/2013)
Gary Varga (11/29/2013)
Jeff Moden (11/29/2013)
The exception to that rule is that spelling errors are simply not forgivable and incorrect substitution of correctly spelled words (for example, you meant to use the word "data" but used "date" instead) are mostly not forgivable (I'll allow only 1 or 2) because if you don't care that much about your own resume, why would you care about the job at hand?Good call.
I remember a weekly newletter at work, with a picture of a sign.
Authorized Personal Only.....talking about an aisle not to be used except by certain personnel.
I chuckled as I pointed it out to the gal putting out the newsletter.
She was amazed at how many eyes had looked and not opened their mouth.
I guess it was close enough for everyone.
Imagine what someone from the outside taking a tour might think.
The aisle got a new sign within a week.
Sloppy can be very bad at times.
There are many jobs out there which require attention to the little details.
Excellent point!
I saw an example once where someone had taken a whole sentence and jumbled up all but the leading and trailing "sound" letters or even left some of the intermediate letters out. People were told they would be shown the sentence for a short period of time and then be expected to repeat the sentence. Most repeated the sentence correctly without ever realizing that the words had been jumbled. They explained it as "familiarity" with words and that a lot of humans only "see" the first and last "sound" letters of a reasonably short word.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2013 at 1:53 am
Jeff Moden (12/1/2013)
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.
Ya know, the more I think about it, the more I'd like someone to write an article about this list, Chris. Are you up for it? Maybe even make it the "go to" for people that want some guidelines on what it takes to be a Senior level SQL Developer and maybe the T-SQL side of what it takes to be an application DBA.
Sure. The "year of madness"* is almost over and I'm beginning to get some free time at weekends now. I'd like to get this one underway or even completed over the festive season, and another Spackle one covering TOP without ORDER BY submitted - the lagniappe with this is showing the same CTE referenced twice in the same query yielding different results from each.
*not in a bad way. Rebuilding the house, more or less.
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
December 2, 2013 at 9:03 am
L' Eomot Inversé (11/24/2013)
I think the idea of "Core TSQL" isn't unreasonable, but no set of knowlege which is just about T-SQL and not about the bigger picture will ever make a competent database developer.
I tend to agree. I was just focusing on T-SQL here. There are other skills, but we can easily get to a large list that's hard to discuss/debate.
December 2, 2013 at 9:36 am
Jeff Moden (11/26/2013)
Actually, that's an impressive and thorough list, Chris. Other than some of the folks on this forum and some at the local PASS chapter, I've not actually met many people that claim to be in the profession (especially those interviewing for a job) that could talk to any of those, as "base" as some of them are. For a SENIOR SQL Developer, that's more of what I think "core skills" should be.
Agree. I think this is a good list. Better than mine.
December 3, 2013 at 2:20 am
Steve Jones - SSC Editor (12/2/2013)
L' Eomot Inversé (11/24/2013)
I think the idea of "Core TSQL" isn't unreasonable, but no set of knowlege which is just about T-SQL and not about the bigger picture will ever make a competent database developer.I tend to agree. I was just focusing on T-SQL here. There are other skills, but we can easily get to a large list that's hard to discuss/debate.
I'm also inclined to agree with Tom, however, converting "the bigger picture" into a set of bullet points isn't straightforward. "Know your data", for instance. How you acquire that knowledge, with or without a usable ERD, is driven by numerous factors. Just thinking about it - and I'm in the middle of the process right now, two weeks into a new gig - it seems like a different method every time.
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
December 3, 2013 at 6:08 am
ChrisM@Work (12/3/2013)
Steve Jones - SSC Editor (12/2/2013)
L' Eomot Inversé (11/24/2013)
I think the idea of "Core TSQL" isn't unreasonable, but no set of knowlege which is just about T-SQL and not about the bigger picture will ever make a competent database developer.I tend to agree. I was just focusing on T-SQL here. There are other skills, but we can easily get to a large list that's hard to discuss/debate.
I'm also inclined to agree with Tom, however, converting "the bigger picture" into a set of bullet points isn't straightforward. "Know your data", for instance. How you acquire that knowledge, with or without a usable ERD, is driven by numerous factors. Just thinking about it - and I'm in the middle of the process right now, two weeks into a new gig - it seems like a different method every time.
Relationships in the data - they only tell a small part of the story, especially in a data warehouse. Many times there will be a Business Logic layer, and this can be a key part in really 'understanding' the data.
I fall into the same thought pattern as Tom. If it is just the technical skills that they have, it's very possible that they are only a good order taker. The best work is usually the result of good back and forth conversation with the business, especially when a developer can map out some of the what if scenarios.
In light of how quickly the list can grow and vary in different environments, it is hard to focus on just T-SQL.
Desire to learn and master the unknown as it comes up (business changes over time), is something hard to quantify. But I see as important to success, and might be used to cover some of the scope creep.
March 11, 2014 at 8:19 pm
I noticed that you've not written an article on this subject yet and thought I'd give you a nudge. I think the list you came up with is outstanding and I'd love to see it take to the wing in the form of an article. I think you'd do an awesome job at it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2014 at 2:47 am
Jeff Moden (3/11/2014)
@ChrisM,I noticed that you've not written an article on this subject yet and thought I'd give you a nudge. I think the list you came up with is outstanding and I'd love to see it take to the wing in the form of an article. I think you'd do an awesome job at it.
Jeff, I haven't stopped thinking about it since you mentioned it last and I've added a couple of sections since - transactions, ACID and locking and blocking. However, I'm extremely busy both at work and at home and it could be a while before I can tackle it. Here's the thing though - an article of this style is right up your street - Dwain's too, I know you've seen his "Make it Pretty" blog post. If I can't get near it in the next fortnight, it's all yours ol' friend.
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
March 12, 2014 at 5:44 am
I see nothing wrong with a 3 author article.
Or at least send it to a couple of others for comments / feedback at this point.
March 12, 2014 at 7:30 am
Neither do I Greg and that’s an excellent point you’ve made.
An article claiming to list the required skill set for a general-purpose TSQL developer, published here on ssc, absolutely has to be first-rate.
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
Viewing 15 posts - 61 through 75 (of 105 total)
You must be logged in to reply to this topic. Login to reply