Tuesday, July 9, 2013

Indexes

Here is the link to the Power Point

SQL server Indexes

And here is the brief code we did in class with the force index. You have to run the CREATE INDEX on the bottom before you can force the index. remember to click the icon for "Show Actual Execution path" on the tool bar to see the statistics.

Select LicenseNumber as License, 
VehicleMake as Make, 
VehicleYear as [Year], 
LocationName as [Location], 
ServiceDate as [Date], 
ServiceTime as [Time],
ServiceName as [Service],
'$' + Cast(ServicePrice as nvarchar) as [Price],
DiscountPercent, 
TaxPercent,
'$' + Cast(Cast(ServicePrice -(ServicePrice* DiscountPercent) + 
((ServicePrice * DiscountPercent) * TaxPercent) as Decimal(6,2))as 
nvarchar) as ServiceTotal
From Customer.Vehicle  v
inner join Employee.vehicleService vs with (nolock, index (Ix_VehicleServiceVehicleID))
on v.VehicleId=vs.VehicleID
inner join Customer.Location loc
on loc.LocationID=vs.LocationID
inner Join Employee.VehicleServiceDetail vsd
on vs.VehicleServiceID=vsd.VehicleServiceID
inner join Customer.AutoService a
on a.AutoServiceID=vsd.AutoServiceID

go
Create index ix_VehicleServiceVehicleID on Employee.VehicleService(VehicleID)

No comments:

Post a Comment