SQL Scripts from Synergy - examples for execution plans

--BASIC SELECT *
SELECT *
FROM dbo.SV00585

--SPECIFY COLUMNS
SELECT Contract_Number,WSCONTSQ,Equipment_ID
FROM dbo.SV00585
WHERE WSCONTSQ=1

--USING AN INDEX TO GET RID OF THE TABLE SCAN
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID
FROM dbo.SV00585 T
 INNER JOIN dbo.SV00501 H
  ON T.Contract_Number=H.Contract_Number
   AND T.WSCONTSQ=H.WSCONTSQ
   AND T.ADRSCODE=H.ADRSCODE
   AND T.CUSTNMBR=H.CUSTNMBR
WHERE T.WSCONTSQ=1

--EXAMPLE OF ELIMINATING TABLE SCAN ON DATE FUNCTION
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID
FROM dbo.SV00585 T
 INNER JOIN dbo.SV00501 H
  ON T.Contract_Number=H.Contract_Number
   AND T.WSCONTSQ=H.WSCONTSQ
   AND T.ADRSCODE=H.ADRSCODE
   AND T.CUSTNMBR=H.CUSTNMBR
WHERE DATEADD(day, 15, T.Schedule_Date) = '01/16/2017'
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID
FROM dbo.SV00585 T
 INNER JOIN dbo.SV00501 H
  ON T.Contract_Number=H.Contract_Number
   AND T.WSCONTSQ=H.WSCONTSQ
   AND T.ADRSCODE=H.ADRSCODE
   AND T.CUSTNMBR=H.CUSTNMBR
WHERE T.Schedule_Date = DATEADD(day, -15, '01/16/2017')


--EXAMPLE OF LIKE OPERATOR
SELECT A.Equipment_ID
FROM dbo.SV00400 A
WHERE A.Equipment_ID LIKE '%A%'

--IMPLICIT CONVERSIONS
DECLARE @NCHAR NCHAR(15),
  @CHAR CHAR(15)
SELECT @NCHAR='MAIN OFFICE',
  @CHAR='MAIN OFFICE'
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID,T.CUSTNMBR,T.ADRSCODE,H.*
FROM dbo.SV00585 T
 INNER JOIN dbo.SV00501 H
  ON T.Contract_Number=H.Contract_Number
   AND T.WSCONTSQ=H.WSCONTSQ
   AND T.ADRSCODE=H.ADRSCODE
   AND T.CUSTNMBR=H.CUSTNMBR
WHERE H.ADRSCODE=@NCHAR
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID,T.CUSTNMBR,T.ADRSCODE,H.*
FROM dbo.SV00585 T
 INNER JOIN dbo.SV00501 H
  ON T.Contract_Number=H.Contract_Number
   AND T.WSCONTSQ=H.WSCONTSQ
   AND T.ADRSCODE=H.ADRSCODE
   AND T.CUSTNMBR=H.CUSTNMBR
WHERE H.ADRSCODE=@CHAR

Did this help you?  If so, please leave a comment!