Indexes and Views

Use MagazineSubscription

--adding a column
Alter table Customer
Add Email Nvarchar(255) 

Select * from Customer

--dropping a column
Alter Table Customer
Drop column Email


--drop a foreign key constrain
Alter table MagazineDetail
Drop constraint FK1_MagazineDetails

--use a system table to see foreign keys in current database
Select * from sys.foreign_keys 

--create a table to test default
Create table test
(
 testID int identity(1,1) primary key,
 testState nvarchar(10) default 'finished'
)
--add a column
Alter table test 
add testDate date 

--insert some records
Insert into test (TestDate) Values (GetDate())

Select * from Test

/**********************************
*Indexes and views
***********************************/
Use Automart

--noclustered is optional
Create nonclustered index ix_LastName on Person(LastName)

Drop index [UQ__Register__A9D105341CAB755D] on Customer.RegisteredCustomer

--uique index
Create unique index ix_Email on Customer.RegisteredCustomer(Email)
--filtered index
Create index ix_filtered on Employee.VehicleService(ServiceDate)
Where ServiceDate > '1/1/2012'

--composite index
Create index ix_service on Employee.vehicleService(ServiceDate, ServiceTime)

Create table testtable
(
 testKey int identity,
 TestName nvarchar(255) not null,
 Constraint PK_TestTable  primary key nonclustered(testKey)
)
--clustered index
Create clustered index ix_testname on TestTable(testName)

Insert into testTable(testName)
values('FirstTest'),
('a test'),
('third test')

Insert into testTable(testName)
values('alpha')


Select * from testTable
\--forcing an index
Select LastName, firstName, email, LicenseNumber, VehicleMake, VehicleYear
From Person p with (NoLock, Index(ix_lastName))
inner join customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
inner join Customer.Vehicle v
on p.Personkey =v.PersonKey
Where LastName='Smith' 

Go --go seperates batches
--views
Create view vw_Customer
As
Select LastName [Last Name]
, firstName [first Name]
, email Email
, LicenseNumber [License Number]
, VehicleMake Make
, VehicleYear [Year]
From Person p 
inner join customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
inner join Customer.Vehicle v
on p.Personkey =v.PersonKey
go
Select * from vw_customer

--using a view
Select [Last Name], Email from vw_Customer
Where [Last Name] ='Smith'
order by Email
go

Create view vw_simple
As
Select * from Person
Order by LastName
--can't order a view

Go
--altering a view
Alter view vw_customer
As
Select LastName [Last Name]
, firstName [first Name]
, email Email
, LicenseNumber [License Number]
, VehicleMake Make
, VehicleYear [Vehicle Year]
From Person p 
inner join customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
inner join Customer.Vehicle v
on p.Personkey =v.PersonKey