Ray Barnett Consulting

Agenda --- Less than an hour

Introduction --- qurlyq.com/6ur

Goals and Objective

Discussion

SQL Statement

Optimization

Questions

About Me

Ray Barnett

raybarnett@silverlightrising.com

Retired AF Programmer ('backroom support') -- worked AF "IT stuff', Healtcare, Logistics, Education, Mom and Pop, other Mil services

Code agnostic

Audience/Subject

SQL or .NET users

Anybody --- however this is a Level 100 SQL presentation with Level 400 SDLC

Presentaion is better for SQL Team member or if you work in an Enterprise ... although one man shops can apply this

Select Statement

Query Optimization

This session will demonstrate how to effectively use the standard SQL clauses in a Select statement, how to retrieve what you want, and how to optimize the statement for efficiency. The following clauses will be covered (SELECT, FROM, WHERE, JOIN, HAVING, GROUP BY, ORDER BY)

NOT Database Design

Goals and Objectives ---Triple E

Educate
Entertain (at least keep you awake)
Energize

Measurement - If you do some of these --- you are better .NET Developer a year from now --- call me

Philosohy/Thoughts

On the Shoulders of Giants ...

Work/Life balance, processes, standards, simplicity
Standards === CamelCase, Propercase, UPPERCASE --- Column Names ... PLAN, DESC = BAD !

Everyone does the best they can with what they have and what they've been taught ... throw no stones

If Code works then it is not considered Bad Code --- maybe it just needs improvement

Good Code is quickly understood by new hires and can be updated by new hires

Handcraft versus Graphical tool --- .NET / MS getting better all the time

Motivation --- code should not be this hard --- No Vacation - Consulant - inherit

Minimize - fatal errors

Is this why we outsource ?

Laws and Principles

Order of Magnitude --- Sum of Integers = N * (N+1)/2

Delphi Method

Moore's Law - no limit - software correlation

SQL is algebra

LINQ = Lambda Equation = SQL = Algebra

Keep It Simple --- TAKEAWAY 1 --- Three ways to open Default Instance

ServerName

(local)

. --- DOT

Style Counts --- TAKEAWAY 2 --- See SQL Formatter LINK

SELECT Person.Contact.Title, Person.Contact.FirstName, Person.Contact.LastName Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderDetail.OrderQty, Production.Product.Name, Sales.SalesOrderHeader.RevisionNumber FROM Sales.SalesOrderHeader INNER JOIN Sales.SalesOrderDetail ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID INNER JOIN Person.Contact ON Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID WHERE Person.Contact.FirstName LIKE 'A%' ORDER BY Person.Contact.LastName

- Spot the Error - ???

SELECT   Person.Contact.Title,
         Person.Contact.FirstName,
         Person.Contact.LastName
         Sales.SalesOrderHeader.OrderDate,
         Sales.SalesOrderHeader.DueDate,
         Sales.SalesOrderHeader.ShipDate,
         Sales.SalesOrderDetail.OrderQty,
         Production.Product.Name,
         Sales.SalesOrderHeader.RevisionNumber
FROM     Sales.SalesOrderHeader
         INNER JOIN Sales.SalesOrderDetail
           ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
         INNER JOIN Person.Contact
           ON Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID
         INNER JOIN Production.Product
           ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
WHERE    Person.Contact.FirstName LIKE 'A%'
ORDER BY Person.Contact.LastName

DreamSpark --- TAKEAWAY 4 -- Students Only

DreamSpark

Optimization --- TAKEAWAY 4

Goto SSMS

-
-
-
-
-
-

Anatomy of an SQL Statement