November 21, 2013 at 8:37 pm
Comments posted to this topic are about the item Core T-SQL
November 22, 2013 at 2:02 am
I've been working with SQL Server for 7 years and have still not once had to use APPLY for anything... Do I not meet the minimum requirements to work with SQL Server? 😀
I've always found 'minimum' skills to be a difficult thing to really define. If someone can write a complex yet efficient set of ctes but has so rarely used 'left outer join' that he has to read the bol every time he uses it, he shouldn't be marked down for not knowing outer joins inside and out, left and right... - he clearly has the capacity to understand data so if he is thrown into an environment where he has to do a lot more outer joins you can bet he'll become an expert pretty darn quick.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
November 22, 2013 at 2:35 am
I have found these things very interesting and wonder if I have 'enough' understanding of a subject to consider myself as having the basic skills. As mentioned, I do not use certain areas of T-SQL on a daily basis and have to look them up. But I do know when to use them and that they are available, I know what they are used for etc. My definition of core skills is largely depending on the 'grasp' of the subject.
Just my thoughts on this...
(Sorry, this does not add to your list, but might create some questions and answers)
November 22, 2013 at 2:57 am
Know how google/bing/duckduckgo to find examples on how to code those problems and then apply those to their problem.
A little explaining:
15-20 years ago I was asked by someone on how to code to convert a decimal to hex (not in TSQL) and I was surprised he didn't know it. So I investigated a little and the best answer I got was:"I have to think about it". I considered it shocking that no one said me : give me 5 minutes (which I considered already a long time) and I give you the solution.
In those years, I think everything changed. I think it is more important to be able to find solutions on the internet (few had internet 15 years ago) that you can apply to your specific problem than to know directly how to code it.
I do agree that to be considered a TSQL expert (I do NOT consider myself a TSQL expert even if I am able to write complex TSQL selects that perform quite well), you should be able to write with ease a lot of those core TSQL.
November 22, 2013 at 3:12 am
From my 5 years experience, what I have realised is that it's not necessarily about whether they know the language in use, in this case T-SQL, but whether they know the theory behind relational databases. Just like someone could know Java very well doesn't necessarily mean that they know and apply the correct concept of OO programming. Similarly I think a big part of SQL is to have an understanding of the relational data model and then any SQL language/tool, which could be T-SQL, PostgreSQL, MySQL etc , can be used to apply that theory. But you are right, there needs to be some way of grading from which you can tell what level someone is at, novice, intermediate or expert. In my view a novice should be able to
*Perform basic querying such as select, insert, update and delete on single tables.
*Create tables (with PKs and FKs), views and procedures.
Taqveem
November 22, 2013 at 3:27 am
I'd include the ability to handle the possible effects of NULL values, and knowledge of how and why to use Stored Procedures.
I'm another person who hasn't ever needed to use APPLY for any actual work.
November 22, 2013 at 3:29 am
taqveem45 (11/22/2013)
From my 5 years experience, what I have realised is that it's not necessarily about whether they know the language in use, in this case T-SQL, but whether they know the theory behind relational databases. Just like someone could know Java very well doesn't necessarily mean that they know and apply the correct concept of OO programming. Similarly I think a big part of SQL is to have an understanding of the relational data model and then any SQL language/tool, which could be T-SQL, PostgreSQL, MySQL etc , can be used to apply that theory. But you are right, there needs to be some way of grading from which you can tell what level someone is at, novice, intermediate or expert. In my view a novice should be able to*Perform basic querying such as select, insert, update and delete on single tables.
*Create tables (with PKs and FKs), views and procedures.
Taqveem
Totally agree.
Many people do not understand the order that the rowsets are processed in (I can't remember the right terminology) for a SELECT statement yet the ordering is essential as you can cut down the amount of work that is required.
Also understanding that SQL is a declarative language means that you are just telling your RDBMS what you want the result to comply to not how it will produce it.
These sort of things are fundamentals. Many of the concepts are simple and easy to grasp yet offer a lot of power to those that learn them. It is not dry academic information but powerful, employable knowledge.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 22, 2013 at 3:36 am
I would add:
° How to apply updates to single rows, all rows or multiple chosen rows (UPDATE statement).
° How to delete single rows, all rows or multiple chosen rows (DELETE statement).
° How to return data in a specific ordering; both rows and columns (ORDER BY clause and SELECT statement).
I totally agree that once you know how and when to apply a technique it can be appropriate to need to check the "how" dependent on ones role.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 22, 2013 at 4:19 am
Basic concepts of database performance tuning should be a must for a competent database developer.
A specific task to accomplish in this respect would be to understand the query's estimated execution plan.
November 22, 2013 at 4:22 am
For me core tSQL are joins & nesting, understanding where clauses, the DML statements, the use of views and stored procedures, grouping as well as knowing how nulls work and are handled. Some constructs like CTEs, pivot and windowing functions are nice to have and make life easier. Later on comes database design, the importance of datatypes, constraints and referential integrity.
What is more important for me is being able to handle complexity and being able to think in sets. If given a complex query that is running slowly, they can rewrite it so that it runs more quickly or with much less overhead. When given a complex task, they can break it down into its logical constituent parts.
November 22, 2013 at 4:26 am
One concept not yet mentioned are transactions. When you change anything in a database, always start with a BEGIN TRANSACTION and check the number of rows, or even better, check the results of your UPDATEs, INSERTs and DELETEs in every table affected before you COMMIT. Ever forget to select an essential part of a WHERE clause of an UPDATE statement? ROLLBACK and your out of trouble. But maybe that's just me ...
November 22, 2013 at 4:32 am
With risk to duplicate what Gaz mentinoned, we need to understand the basic processing/binding order (FROM, ON, JOIN and so on) and realize that the Query processor is the final station when it comes to nuts&bolts on how the Query actually gets executed.
To that I would add something that gets lost all the time: Clear structure and Annotation. Don't forget to write down what all your modules are supposed to accomplish. So many hours trying to understand someone elses complex code and finally realize it's just plain incompetence that produced that seemingly complex code.
November 22, 2013 at 4:47 am
vliet (11/22/2013)
One concept not yet mentioned are transactions. When you change anything in a database, always start with a BEGIN TRANSACTION and check the number of rows, or even better, check the results of your UPDATEs, INSERTs and DELETEs in every table affected before you COMMIT. Ever forget to select an essential part of a WHERE clause of an UPDATE statement? ROLLBACK and your out of trouble. But maybe that's just me ...
Another...
ACID properties
No excuses on that one.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 22, 2013 at 5:16 am
Understand execution plans.
November 22, 2013 at 5:27 am
I have actually used APPLY (er, perhaps twice!) but not PIVOT. Just doesn't feature in the type of work I do.
A good grasp of the fundamentals is always important, but coding in a clear, maintainable style is just as crucial.
Communication skills work both ways - comprehending the input spec, and creating stuff that somebody else can pick up and run with are equally important.
If I was recruiting somebody for our business, I'd also be very interested in people who understand locking and blocking (ours is a real-time, response-time-sensitive application) and Service Broker (we use it extensively) but I appreciate that isn't everyone's cup of tea.
Viewing 15 posts - 1 through 15 (of 105 total)
You must be logged in to reply to this topic. Login to reply