Indexes and Views

--indexes and joins
--clustered indexes, non clustered indexes, unique, filtered

Use Automart

--the syntax is  CREATE [type of Index] [Index Name] ON [Table](Column Name]
--nonclustered is the default. You never have to actually write it
--a nonclustered index creates a B-tree that breakes the data into
--nodes. The search can locate the relevant node in 2 or three steps
--rather than run through thousands of individual rows

Create nonclustered Index ix_LastName on Person(lastName)
--unique indexes ensure that a column is unique. It speeds up searches
--because the server doesn't have to look for duplicated

Create unique index ix_ServiceName on Customer.AutoService(ServiceName)
--a filtered index has a where clause that can "filter" which rows
--in a table are indexed

Create nonclustered index Ix_olderData on Employee.VehicleService (ServiceDate) Where serviceDate > '1/1/2015'

--it is possible to include more than one column in an index
Create nonclustered index ix_employeeLoc on Employee(PersonKey, LocationID)

--primary keys are indexed by default
--but here is the syntax fro how to create one
Create clustered index ix_PersonKey on Person(PersonKey)

--forcing an index. SQL Server will not even create the index structure
--the b-tree for tables under a certain number of rows (27000 or so)
--the following syntax forces the use of the ix_Lastname index
Select Lastname, firstName, email
From Person p with (index(ix_lastName)) --this forces the index
inner join Customer.RegisteredCustomer rc
on rc.PersonKey=p.Personkey
where LastName='Smith'

--Go is used to separate batches. It means basically
--finish everything before starting the next command
Go
--Views--the basic syntax is CREATE VIEW [Name] AS then 
--SQL Statement. Views are basically stored queries
--they are filters. They don't store the actual data.
--The idea of a view is to create a "View" of the database
--for a particular set of users. Human Resources, for instance.
--Some views can be used for updates and inserts but not
--most. To allow updating and inserting the view must
--be transparent. No aliases, not more than one join,
--no calcualted fields. It is probably better to use stored
--procedures rather than views for those tasks
Create View vw_RegisteredCustomers
AS
Select LastName [Last Name]
,FirstName [First Name]
, Email
,LicenseNumber License
,VehicleMake Make
,VehicleYear [Year]
From Person p
inner join Customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
inner Join Customer.Vehicle v
on v.PersonKey=p.Personkey

Go
--the order by clause is forbidden in creating views
--but you can order the results of a query using
--a view. Also when Selecting from a view
--you must use the aliases as the column names
Select * From vw_RegisteredCustomers
Where [Last name] = 'Smith'
order by [First Name]