Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»»

Core T-SQL Expand / Collapse
Author
Message
Posted Friday, November 29, 2013 12:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:33 AM
Points: 5,754, Visits: 3,673
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!!!
Post #1518590
Posted Friday, November 29, 2013 4:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518606
Posted Friday, November 29, 2013 5:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:33 AM
Points: 5,754, Visits: 3,673
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!!!
Post #1518613
Posted Saturday, November 30, 2013 6:02 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 1:54 PM
Points: 675, Visits: 6,815
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!
Post #1518670
Posted Sunday, December 1, 2013 5:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518719
Posted Sunday, December 1, 2013 7:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518738
Posted Monday, December 2, 2013 1:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:03 AM
Points: 6,869, Visits: 14,179
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1518794
Posted Monday, December 2, 2013 9:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,284, Visits: 15,748
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1518929
Posted Monday, December 2, 2013 9:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,284, Visits: 15,748
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1518937
Posted Tuesday, December 3, 2013 2:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:03 AM
Points: 6,869, Visits: 14,179
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1519112
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse