SQL Server Standards
Version 1.5
Shane Lively & Michael Sarsany Page 14 of 24
to-read chunks, more complicated statements are very hard to
decipher. By doing this and aliasing table names when possible, you
will make column additions and maintenance of queries much easier.
Refer to the examples below.
Confusing SQL:
SELECT dbo.DealUnitInvoice.DealUnitInvoiceID,
dbo.DealUnitInvoice.UnitInventoryID, dbo.UnitInventory.UnitID,
dbo.UnitInventory.StockNumber AS [Stock Number], dbo.UnitType.UnitType
AS [Unit Type], ISNULL(dbo.Make.Description, '') AS Make,
ISNULL(dbo.Model.Description, '') AS Model, DATEPART(YEAR,
dbo.Unit.ProductionYear) AS [Year], dbo.UnitType.UnitTypeID,
dbo.MeterType.Description AS MeterType, dbo.UnitInventory.MeterReading,
dbo.UnitInventory.ECMReading, '$' + LTRIM(CONVERT(nvarchar(18),
CAST(dbo.DealUnitInvoice.Price AS decimal(18, 2)))) AS Price, '$' +
LTRIM(CONVERT(nvarchar(18), CAST(dbo.DealUnitInvoice.Cost AS
decimal(18, 2))))
AS Cost, dbo.DealUnitInvoice.IsTradeIn, ISNULL(dbo.Unit.Vin, '') AS
Vin, ISNULL(dbo.Unit.SerialNumber, '') AS SerialNumber,
dbo.UnitInventory.AvailabilityStatusID,
dbo.UnitInventory.SellingStatusID, dbo.UnitInventory.IsNew,
dbo.UnitInventory.UnitPurchaseOrderID,
dbo.UnitInventory.BaseCost, dbo.DealUnitInvoice.DealPacketInvoiceID
FROM dbo.DealUnitInvoice INNER JOIN
dbo.UnitInventory ON dbo.DealUnitInvoice.UnitInventoryID =
dbo.UnitInventory.UnitInventoryID INNER JOIN
dbo.Unit ON dbo.UnitInventory.UnitID = dbo.Unit.UnitID LEFT OUTER JOIN
dbo.MeterType ON dbo.Unit.MeterTypeID = dbo.MeterType.MeterTypeID LEFT
OUTER JOIN
dbo.UnitType ON dbo.UnitInventory.UnitTypeID = dbo.UnitType.UnitTypeID
AND dbo.UnitType.InActive = 0 LEFT OUTER JOIN
dbo.Make ON dbo.Unit.MakeID = dbo.Make.MakeID AND dbo.Make.Inactive = 0
LEFT OUTER JOIN dbo.Model ON dbo.Unit.ModelID = dbo.Model.ModelID AND
dbo.Model.InActive = 0
Now look at the same SQL Statement but organized in an easy to read
and more maintainable format:
SELECT
dui.DealUnitInvoiceID,
dui.UnitInventoryID,
ui.UnitID,
ui.StockNumber [Stock Number],
ut.UnitType AS [Unit Type],
COALESCE(mk.Description, '') Make,
COALESCE(ml.Description, '') Model,
DATEPART(YEAR,u.ProductionYear) [Year],
ut.UnitTypeID,
mt.Description AS MeterType,
ui.MeterReading,
ui.ECMReading,