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 ««12345»»»

Core T-SQL Expand / Collapse
Author
Message
Posted Friday, November 22, 2013 4:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:17 PM
Points: 65, Visits: 413
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 ...
Post #1516720
Posted Friday, November 22, 2013 4:32 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 5:21 PM
Points: 33, Visits: 235
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.
Post #1516721
Posted Friday, November 22, 2013 4:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 5,191, Visits: 2,811
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!!!
Post #1516722
Posted Friday, November 22, 2013 5:16 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:33 PM
Points: 139, Visits: 614
Understand execution plans.
Post #1516724
Posted Friday, November 22, 2013 5:27 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:26 AM
Points: 614, Visits: 480
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.
Post #1516729
Posted Friday, November 22, 2013 5:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:08 AM
Points: 37, Visits: 29
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.
Post #1516734
Posted Friday, November 22, 2013 5:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:04 AM
Points: 43, Visits: 95
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

Post #1516743
Posted Friday, November 22, 2013 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 11:59 AM
Points: 10, Visits: 72
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.
Post #1516756
Posted Friday, November 22, 2013 6:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:39 AM
Points: 24, Visits: 169
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.
Post #1516759
Posted Friday, November 22, 2013 6:42 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:02 AM
Points: 162, Visits: 832
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.
Post #1516760
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse