November 22, 2013 at 5:37 am
I agree with the comments already provided. Now I would like to add some things that many would not consider important for a developer.
a. Maintenance Plans.
I was a developer on a project several years ago and was the only person interacting with the SQL Server on a daily basis. I set up full and incremental backups for my database early on. Three days after I finished the contract, I received a call from a panic stricken developer who shared with me the director, thinking he was a programmer, corrupted the database. I was able to point the developer to the backups and saved the day.
b. Source Control
Storing all scripts for creating and modifying the database and populating with base data is imperative to successfully being able manage a data store.
Yes there are others who should handle these areas but in many small businesses the database developer is the only one on site. I would always prefer to be a hero 51 percent of the time and a slug 49 percent of the time than the reverse. If you can keep the hero time above the 51 percent mark you will always be remembered as the hero.
November 22, 2013 at 5:51 am
A couple of skills that I have used as a database developer
- Thinking of data in sets instead or rows. (this will avoid cursor processing)
- multi table updates and deletes
- Analytical functions
- Merge statement
Like others mentioned a basic understanding of SQL server 'under the hood' as far as DML is concerned like Indexes and such. (I may have steered away from the topic!)
My two cents, thanks for listening!
MM
November 22, 2013 at 6:29 am
I'll add a few to the mix:
*Creative uses of CASE, since there's no if structure except in procedures, functions
*Know how to create a user defined function and when it is useful or needed
*SubQuerying in the Select, From/Join, and Where clauses
*Handling multiple instances of the same table in a single query
And knowing that there are other databases and SQL variations out there and how they differ to what you have available in tSQL.
November 22, 2013 at 6:37 am
Bad plans vs good plans. Knowing the difference and how to transform one into the other. (Includes re-writing monster queries into efficient multi-step processees with temp tables, table vars, etc, plus a working knowledge of join, index and query hints).
Locking & isolation levels. For high-concurrency OLTP systems this is the alpha and the omega. Avoiding classic boo-boos like selecting from two+ tables twice in a txn, the second time in a different sequence to the first (classic deadlocking by poor T-sql coding).
And no RBAR (Row-By-Agonising-Row) sql coding. i.e. why cursors are evil and how to recode in set-based operations. Including some knowledge of recursion in sql.
November 22, 2013 at 6:42 am
Key fundamentals for me include:
1) Basic understanding of relational databases - purpose, structure, basic modelling modelling principals, data types, indexes
2) Solid understanding of set/join theory - everyone needs to be able to understand joins and how to translate from a need for information into the relevant combinations of tables to retrieve it
3) Basic aggregations - you need to be able to do mundane aggregations
4) String manipulation - you should be able to perform tasks like splitting strings, left and right segments
5) Date manipulation - you need to be able to handle time comfortably
6) Comparison - you need to be able to understand NULLs, string comparison, sort orders etc
7) Logic - you need to understand boolean logic, truth tables, etc to grasp how to build sensible criteria and processing flows
8) ANSI standards - awareness of the standards and what they are so code is portable and easily assimilated
I don't care too much if someone can't build a temp table off the cuff, or can't build merge statements, I want them to understand the fundamentals of data tasks, and aside of 8 (which would be translated to the relevant set of standards for the respective tool) these should apply to everyone who processes data in some form or another.
November 22, 2013 at 6:55 am
Steph Locke (11/22/2013)
Key fundamentals for me include:1) Basic understanding of relational databases - purpose, structure, basic modelling modelling principals, data types, indexes
2) Solid understanding of set/join theory - everyone needs to be able to understand joins and how to translate from a need for information into the relevant combinations of tables to retrieve it
3) Basic aggregations - you need to be able to do mundane aggregations
4) String manipulation - you should be able to perform tasks like splitting strings, left and right segments
5) Date manipulation - you need to be able to handle time comfortably
6) Comparison - you need to be able to understand NULLs, string comparison, sort orders etc
7) Logic - you need to understand boolean logic, truth tables, etc to grasp how to build sensible criteria and processing flows
8) ANSI standards - awareness of the standards and what they are so code is portable and easily assimilated
I don't care too much if someone can't build a temp table off the cuff, or can't build merge statements, I want them to understand the fundamentals of data tasks, and aside of 8 (which would be translated to the relevant set of standards for the respective tool) these should apply to everyone who processes data in some form or another.
Yes. I have often heard on these forums that too many people are missing the fundamentals and as long as anyone has those they can easily be assisted to do the rest.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 22, 2013 at 7:13 am
The thing I love about T-SQL is that it actually doesn't change that frequently, and ANSI SQL changes even less often. Once you get past the two year mark, it's really not a struggle to keep up. What's really new (well they've been around for 7 years now), are the ranking, windowing, and CTE functions. Of course there are T-SQL statements to support new features, but I wouldn't count them as being a core requirement, because it depends on wether the organization leverages a specific feature. For example, I've never used the new spacial data types and functions and don't see a need for it over the horizon, unless I change jobs.
For the most part SQL Server development is evolutionary, not revolutionary. You don't see T-SQL developers tossing out their existing codebase and replacing it with something else from scratch, not the same way that web developers scrap and replace frameworks, unless the previous developer simply wrote bad T-SQL.
In addition to Steve's list, I'd expect a T-SQL developer to know statements related to role/permission based security, indexing, execution plans, and database management views related to performance. We shouldn't expect the DBA to sprinkle that essential stuff on top of our T-SQL and tables like salt, it needs to be designed and baked into the codebase by the developer and then unit tested in development before going to QA and production. If a SQL Server developer doesn't know security and performance optimization, then they're still intermediate level at best, even if they otherwise know how to write sophisticated T-SQL.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 22, 2013 at 8:35 am
I suck, I've been a SQL developer/DBA/consultant/.NET developer for 13 years, and have never used merge, cross join, pivot, apply, and rarely used complex CTEs.
I think I've done all that stuff by using temporary tables instead though through various designs. Probably would be nice to learn when merge should and should not be used.
November 22, 2013 at 8:52 am
For pure minimums, I like to stay a little simpler:
Set based solution design
Transactionalization (ACID compliance)
Data types
Normal Form based database design
Learn new programming syntax
Benchmarking
Testing for correctness.
T-SQL when used as a procedural language isn't much different than any other old high-level procedural language used without a stack and without objects - you've got variables, loops, subroutines/procedures/functions, language-specific quirks (i.e. scalar UDF's are slow), and so on.
It's when you add in sets (primarily) and transactions (slightly) that you need to learn to think a little differently when working out the design.
Syntax is syntax is syntax - it's something to look up for whatever language you're using at the time. Quirks of a language are something to watch out for and learn - T-SQL has a lot, but other languages do too.
November 22, 2013 at 9:03 am
One is not always in a work environment where these skills are needed, thus the ability to use them may diminish without consulting BOL or other source. Once you have worked through problems, they are easy to re-introduce into your skillset as needed.
November 22, 2013 at 9:41 am
At least have some understanding of data normalization.
Can be some huge paybacks.
Set based thinking reminds me of stepping back and sometimes having to think 'big picture', not always single task or RBAR.
Use of Distinct. I've seen this misused way too often. Do they know when they should use it? And some of the pitfalls?
How to return both matched and unmatched rows when one of your select criteria could be one of the NULLS. This can indicate understanding of some of the lower level working of joins.
November 22, 2013 at 9:48 am
The problem with such a question is that many do not know how huge/gigantic/enormous their gap in SQL knowledge is and how profoundly it can affect their productivity. Over the last year the company i work for needed to expand in programmers and at the same time make start with a switch to C#/.NET as the platform for new work.
I done many interviews as a result of this. And I can say that the skill of data aware thinking and manipulation with SQL is abysmal at best. It is not considered sexy enough to spend time on, even tho it underpins almost every application made out there. It is a really shocking thing to witness! Even those that were convinced they knew their way in SQL were still at a thinking and SQL feature use level matching SQL Server 2000 and earlier and did not show any useful intimate knowledge at that.
That said, two weeks back I found myself for the first time taking a closer look at the merge statement, a feature available to me since 2008. In retrospect, it took me 5 years to find it of interest enough to spend time on and evaluate it! And this is me, someone that thinks data/data-flow centric instead of code centric and can tangle with the best of them when it comes to handling data using SQL.
To me, this says something about how easy it is to not learn everything about something so valuable to our work. There are so many counterproductive distractions out there, and more are surfacing every day.
Server wise essential are:
* Understanding of data and sets of data and when to use what type.
* Understanding the server workflow your SQL goes trough (plan caching e.d.)
* Understanding collations
* Understanding how indexes speed up searches and know of include columns to cover whole requests
* Understanding filtered indexes
* Understanding DRI to enforce integrity
T-SQL wise, these features are VERY important and a MUST know:
* Bulk insert
* Aggregate functions
* Window functions
* Group by / Having clause
* Cross joins
* CTE's (Common table expressions)
* Cross/Outer apply
* Table variables and temporary tables
* Views
* User defined functions
* Sub-selects
* union all (just union is most often not what is required)
* Minimally know what a stored procedure is and how to use it (you can do without most of the time IMO).
There are features that are less important but nice to know and handy in specific cases:
* The output class
* (Un-)Pivot
* Full outer joins
* Merge statement
* Derived tables (replaced with the better CTE)
* CLR integration/functions (just find a good enough library if you aren't very deep into doing them yourself)
I am sure I did not list everything we use frequently, but most of it is there. If you find many of the important T-SQL features are not yet in your repertoire (most of it exists for 8 or more years now), you cannot with a straight face claim to have basic T-SQL knowledge. Beyond that list of basic features is simply more features and certainly a lot more detailed understanding to gain to become advanced or even expert level.
I clearly take the view that knowing how to write a select statement and use a where clause with it is not yet basic level, but simply introduction level stuff!
We live in a data driven world and as DBA or developer you need to be able to be productive and deliver quality. Not knowing this basic level of SQL means you cannot be competitive in both and are just part of the surprisingly large grey mass (no reference to brains here).
Tools like LINQ2SQL/Entity2SQL on the application side can mitigate the problem a bit. Which is exactly why so many developers like it and spend time learning it. Problem is, they are clearly learning the wrong things which will hinder their development for years, maybe even their whole career!
A simple and recent real world example is this:
Another company we sub-contracted needed to process a series of large CSV files, exported by another system. Something seemed to be wrong and they needed a long running ETL job to get the data before they could analyze what exactly. My boss, not the SQL guru, but knowledgeable in many of the basic T-SQL features and with a lot more on its mind then this, did the job in half an hour using a simple bulk insert plus some simple SQL to verify the things of interest.
That on a daily basis is an insane productivity difference, on par with what you would expect the difference to be between an application user abstracted away form the data by an interface and a developer that has full access and knows how to use it!
November 22, 2013 at 11:51 am
Biggest skill I need is how to find stuff on SQLServercentral.com
November 22, 2013 at 12:15 pm
pdanes (11/22/2013)
Biggest skill I need is how to find stuff on SQLServercentral.com
Yes, I'm when searching a sqlserver topic and want to whittle down the result to only the good stuff, I typically look here first. Not only can I find at least one well written and edited article, but attached to it will be a discussion with input from a lot of smart people as well.
site:SQLServercentral.com +stuff
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 22, 2013 at 2:35 pm
From the article:
This week I'm curious if we can debate about, and compile, a list of core skills with T-SQL that we think someone ought to understand to be considered competent as a database developer.
IMHO, the quote above is a bit of an oxymoron because if all someone has is "core skills" as typically defined by a good amount of the community, then they're not a "competent database developer". They're a partially educated and usually dangerous user.
What do I define as "core skills" for a Database Developer? The list is way too long for me type up but I can give you an example. I don't care how long someone has been programming in T-SQL or what kind of "miracles" they think they may have pulled off, if they have to lookup how to use a LEFT OUTER JOIN or a CROSS APPLY, then I don't consider them to be a Database Developer never mind a competent one. As another example, there's a huge difference between someone that has used BCP and someone that has skillfully used BCP. The former is a user, the latter might be a Database Developer.
I think that the bar has been set way too low for the position of "Database Developer" or "skilled C#/.Net programmer with good SQL experience" or even DBA. I think that any DBA (system, application, or otherwise) that doesn't actually know what a clustered index is should be banned from using "DBA" on their resume. I think any front-end programmer candidate that can't describe in detail the differences between a "Get" and a "Post" should go to jail for as long as they've been in the business because they've been robbing whomever they have worked for.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 105 total)
You must be logged in to reply to this topic. Login to reply