Views, Alter tables and Indexes

Here is the stuff on Views and altering tables


Create table test
(
testID int identity(1,1) primary key,
test1 nchar(20) not null unique,
testGrade decimal(10,2),
--Constraint test1_unique unique(test1),
Constraint ck_Grade check(testGrade between 0 and 4)
)

Alter table test
Add Constraint test1_unique unique(test1)

Alter table Test
Add Constraint ck_Grade check(testGrade between 0 and 4)

Insert into Test(test1, testGrade)
Values('stuff', 3.9)

Insert into Test(test1, testGrade)
Values('And more stuff', 3.9)

Alter Table Test
Drop Constraint UQ__test__0BC6C43E

Select * from Test

Delete from test

Drop table test


Create View vw_Subscriptions
As
Select CustLastName as [Last Name],
CustFirstName as [First Name],
Magname as [Magazine],
SubscriptionStart as [Start Date],
SubscriptionEnd as [End Date]
From Customer c
inner join Subscription s
On c.custid=s.custid
inner join MagazineDetail md
on s.magdetID=md.magdetid
inner join Magazine m
on m.magid=md.magid



Select * from vw_subscriptions
Where magazine='IT Toys'
order by [last name]

Select * from vw_Subscriptions
Where custlastname='able'


Create Index ix_lastname on Customer (CustLastName, CustFirstName)

Create unique index ix_unique on Customer(CustPhone)

Drop index ix_unique on Customer

Create clustered index ix_clustered on Customer(CustLastName)