Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Core T-SQL


Core T-SQL

Author
Message
vliet
vliet
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 722
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 ...
G Peterson
G Peterson
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 310
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.
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8252 Visits: 6124
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!!!
Your Name Here
Your Name Here
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 786
Understand execution plans.
Bob JH Cullen
Bob JH Cullen
SSC Eights!
SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)

Group: General Forum Members
Points: 934 Visits: 786
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.
eric.notheisen
eric.notheisen
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 258
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.
Miru Seshadri
Miru Seshadri
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 102
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
dgreen-1126628
dgreen-1126628
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 82
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.
pbowman-543344
pbowman-543344
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 176
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.
Steph Locke
Steph Locke
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 870
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search