Basics of SQL

• Ramesh Lende

  Ten Centuries

  Points: 1034

  More actions

  March 11, 2008 at 10:17 pm

  #379171

  Hello,

  Does any one have list of DO's and dont's for writing effective SQL? I am looking for some basics like how to index, how to write order of query, how to write Selects etc.

  Any documentations would be helpful.

  Thanks.

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  March 11, 2008 at 10:57 pm

  #788390

  http://w3schools.com/sql/default.asp

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• TraderSam

  SSCrazy

  Points: 2931

  More actions

  March 12, 2008 at 5:37 am

  #788473

  ...and read this forum.

  There are a lot of things in SQL that APPEAR to do the same thing (same end result in regards to the data), but each has a best place for use for various reasons (performance, robustness, scalability, etc.). Knowing that comes with experience and education. You can get a lot of this type of education by reading this forum and understanding the various solutions offered by the participants.

  If it was easy, everybody would be doing it!;)

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  March 12, 2008 at 6:00 am

  #788481

  Another question far too dense to answer in a forum like this.

  However, a few pointers (and, as with everything in SQL Server, there are exceptions to every one of these "rules"):

  Return only the rows and columns you need when you need them. In other words, don't use SELECT * and do include WHERE clauses.

  Qualify the owning schema of the objects you're referring to in the query; dbo.MyTable

  Use ANSI standard joins: FROM dbo.MyTable JOIN dbo.YourTable ON... instead of FROM dbo.MyTable,dbo.YourTable WHERE...

  TSQL is a set based language. This means avoid using cursors, WHILE loops, etc., and try to find the right set based approach to solve your problem.

  Every table (with exceptions, see note above) in the database should have a clustered index.

  That's barely scratching the surface. There a tons more. Start your own documentation of favorate or best practices and remember to test your assumptions regularly.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning