November 25, 2013 at 11:13 am
scott.fountain (11/25/2013)
Applying indentation so scripts are readable
Yes, that's important.
[
Nothing spells a good day at work like reviewing a query containing multiple nested table expressions; all the while, no indentations - everything is blocked on the left margin.
Actually everything on the left isn't nearly as much of a day spoiler as indentation that looks as if it's intended to reflect teh structure of the code but is actually totally misleading - and that's almost as common as the everything on the left thing.
Tom
November 25, 2013 at 11:18 am
L' Eomot Inversé (11/25/2013)
scott.fountain (11/25/2013)
Applying indentation so scripts are readableYes, that's important.
[
Nothing spells a good day at work like reviewing a query containing multiple nested table expressions; all the while, no indentations - everything is blocked on the left margin.
Actually everything on the left isn't nearly as much of a day spoiler as indentation that looks as if it's intended to reflect teh structure of the code but is actually totally misleading - and that's almost as common as the everything on the left thing.
One of the first things I learned when I started coding in school was to follow an indentation pattern (and a capitalization pattern). Some of my coworkers, who aren't primarily developers, laugh at me. The first thing I do when they ask me to help with their code is reformat it. 😀
November 25, 2013 at 11:29 am
scott.fountain (11/25/2013)
Applying indentation so scripts are readableNothing spells a good day at work like reviewing a query containing multiple nested table expressions; all the while, no indentations - everything is blocked on the left margin.
This idea could be out of scope or considered an obvious skill, but boy...it grates my nerves.
:crazy:
When confronted with a pile of spaghetti SQL, I don't even try to make sense of. I use this SSMS plugin for formatting T-SQL.
If the original developer has moved on, then I may even reformat and save the stored procedure, but then it has to be unit tested just in case a bug gets introduced in the process of reformatting.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 25, 2013 at 11:54 am
Jeff Moden (11/22/2013)[hr
Shifting gears a bit, why do so many people assume that where there's a database, there's a client application? And when there is an application available, why do so many people think that most of the processing is done by or can be done by the application?
You said the magic word 'assume'.
Many people are afraid to admit when they don't know something.
I'm sure you've run into some of the ones who can't even describe (or take the time to describe) what they need, but expect you to code a solution. And they expect it yesterday.
Being able to think logically, isolate an issue, and own the solution.
You hit some real keys there.
November 25, 2013 at 12:04 pm
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2013 at 6:35 am
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
November 26, 2013 at 1:28 pm
ChrisM@Work (11/26/2013)
Interesting you should go beyond "core skills" here, Jeff.
Heh... "Core Skills" for a Database Developer or a DBA. You might as well say "breathing" with as low as some folks have set the bar. 😀
It's kind of like looking at the minimum hardware and software requirements to run SQL Server. Yeah... you could go with that basic "core" requirements, but no one would intentionally run on such a configuration just like no one would hire a DBA or SQL Developer with just "core" skills unless it was for a very junior position or there just wasn't anyone else left on the planet. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2013 at 6:57 pm
ChrisM@Work (11/26/2013)
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.
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.
As a bit of a sidebar, I especially like the one about "make it work, make it fast, make it pretty" except you left off the 4th item there... " and it ain't done 'til it's pretty!" 😀
You need to add a super important requirement to your list, though. According to more than half of the 20+ interviews I've done for people in the last 6 months that have "Sr. SQL Server DBA", "Sr. SQL Server Developer", or "Sr. Web Developer" listed as their primary job for at least 10 years, you've left a small but critical bit of knowledge off your list. Here's the question I've been asking as the first question in every Sr. level interview I've conducted and it really separates the proverbial "men from the boys". Brace yourself. Ready? (Children... please leave the room)
.
.
.
.
.
.
.
.
.
"Using T-SQL, how do you get the current date and time?"
I'm dead serious. Because of that question, one guy even accused me of asking questions that were "too SQL Server centric" (although he couldn't tell me what Current_TimeStamp was {I was looking for GETDATE(), of course} and the posted job WAS for SQL Server) and another accused me of asking "way too complex a question for the job at hand". This is after I told them that some questions would be extremely simple just to get them to relax and that I NEVER ask trick or esoteric questions.
How about this. The candidate that I interviewed about two hours ago has listed himself as a "Sr. SQL Server DBA" since 2000 and nearly every job he listed has similar to following in it...
Responsible for the optimization of stored procedures and backup and recovery activities.
This person didn't know what a Clustered Index was (seriously!), couldn't describe what a PK did other than "should be an integer and makes the queries go faster", and when I asked him what "Point in time" backups were, he asked "You mean DIF files? Yeah... I know those but I haven't used them".
These are supposed to be "warmup" questions just to get people to relax!
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2013 at 2:06 am
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).
Even I would be able to answer the current time questions (I'd expect people to offer that there was a UTC equivalent too).
BTW You get the same sort or nonsense for .NET developers too e.g. actual telephone interview incident:
Me: "How do you make a property of a class read only?"
Interviewee: "Erm. Erm. It's here somewhere. Erm. I mean it's on the tip of my tongue."
[Sound of pages being flipped.]
Me: "No problem. Let's move on and you can tell me if it comes to you."
...
[Other questions and...well, other questions.]
...
Interviewee: "I've found it!!! Erm. I mean I remember now. [Very stilted] To make a class' property read only..."
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 27, 2013 at 7:17 am
I agree on your point. I got into this field fresh out of college with very little understanding of programming, scripting in general and just about zero knowledge of SQL Server. What I learned early (and what I have heard from my peers) is that in general other are way too busy to always help answer. They showed me the tools I needed from the blogs, TN, this website and others, in order to solve the problems I was faced with.
Being able to utilize these online (and offline) resources has proven invaluable when changing positions/companies and has allowed me to hold my own. I will argue that you still need a basic understanding to be able to utilize some of these resources to their capacity.
November 28, 2013 at 2:02 am
Jeff Moden (11/26/2013)
As a bit of a sidebar, I especially like the one about "make it work, make it fast, make it pretty" except you left off the 4th item there... " and it ain't done 'til it's pretty!" 😀
Heh I like it too - but "make it work, make it fast, make it pretty - and it ain't done 'til it's pretty!" is yours and would require an acknowledgement in the text somewhere. I figured if I started out down that route I'd end up with more acks than body text 😉
You've posted up that little anecdote about GETDATE() before and it still makes me cringe. The agent on my last gig estimates that roughly 50% of candidates - permy and contractors - lie on their cv's. That's Lie with a capital "L", not exaggerate. I've only once experienced such a candidate, most gigs I apply for have some kind of testing to whittle out the liars. He got around his limitation by developing locally then posting the resulting data to the dev server, so I couldn't see the code he was using until we were well into the project. When I finally did, it was ten times worse than the scrubbiest code I've ever seen on ssc! Worse than that, he was manually updating the data for the gap analysis...
Perhaps this is why contractor rates haven't risen in the UK in twenty years. Too many of us have left the client with a huge pile of unmaintainable cr@p!
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
November 28, 2013 at 3:31 am
ChrisM@Work (11/28/2013)
...Perhaps this is why contractor rates haven't risen in the UK in twenty years. Too many of us have left the client with a huge pile of unmaintainable cr@p!
Unfortunately, too true. We are judged not only on our performance but that of our peers :'-(
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 28, 2013 at 3:49 am
Gary Varga (11/28/2013)
ChrisM@Work (11/28/2013)
...Perhaps this is why contractor rates haven't risen in the UK in twenty years. Too many of us have left the client with a huge pile of unmaintainable cr@p!Unfortunately, too true. We are judged not only on our performance but that of our peers :'-(
I cannot speak for the UK or any country as a whole, but is it not more important to deliver value and build up your own network?
Next to what is concluded here, that a lot of crap has been made and obviously continues to be so, the customers where this happened are also to blame for this. IT projects go primarily south with clients that have zero in house experience and treat IT as buying a new plasma TV and just try to get the cheapest with the most build in options.
November 28, 2013 at 4:02 am
peter-757102 (11/28/2013)
Gary Varga (11/28/2013)
ChrisM@Work (11/28/2013)
...Perhaps this is why contractor rates haven't risen in the UK in twenty years. Too many of us have left the client with a huge pile of unmaintainable cr@p!Unfortunately, too true. We are judged not only on our performance but that of our peers :'-(
I cannot speak for the UK or any country as a whole, but is it not more important to deliver value and build up your own network?
Next to what is concluded here, that a lot of crap has been made and obviously continues to be so, the customers where this happened are also to blame for this. IT projects go primarily south with clients that have zero in house experience and treat IT as buying a new plasma TV and just try to get the cheapest with the most build in options.
Totally agree, however, rarely does blame gets placed where the faults lie.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 29, 2013 at 10:37 am
ChrisM@Work (11/28/2013)
Jeff Moden (11/26/2013)
As a bit of a sidebar, I especially like the one about "make it work, make it fast, make it pretty" except you left off the 4th item there... " and it ain't done 'til it's pretty!" 😀
Heh I like it too - but "make it work, make it fast, make it pretty - and it ain't done 'til it's pretty!" is yours and would require an acknowledgement in the text somewhere. I figured if I started out down that route I'd end up with more acks than body text 😉
No, no.... that's OK, Chris. I wasn't looking for any form of acknowledgement on that. It's one of my favorite sayings and I just wanted to make sure that the 4th and most important part was included because, as we've seen on this very thread, some people that we've had to work with think some of the steps are optional.
As to the rest of it, I started to write a rather lengthy rant about resumes and 3rd parties but I'll just summarize by saying that I've found that the estimate of 50% of the people out-right lying on their resumes is a grand understatement according to the interviews I've been conducting and the "service" I've personally witnessed from others. It's really a shame because good people like yourself and many others that I've seen on these fine forums end up having to suffer through the stigma that those liars and posers have left in their wake of crap code, poor service, and dishonest billing practices.
To wit, it's one of the reasons why I almost never hire nor reject on the basis of a resume alone. If you apply for a job that I'm conducting interviews for, I will interview you. Why? Because it takes me only a couple of in-person questions to figure out if you're a liar or poser and I don't want to take the chance of missing out on someone good just because they don't know how to write a resume. 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?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 46 through 60 (of 105 total)
You must be logged in to reply to this topic. Login to reply