Index Blocking Factors Views Rose Hulman Institute of Technology Curt Clifton Index Redux Heap storage Clustered primary index Non clustered secondary index On heap stored table On clustered table Index Calculations To understand index helpful to calculate sizes Terms Blocking factor How many records fit on a page a k a a block see Q1a Index block factor How many index entries fit on a page see Q1b Index Calculations cont Single level index If primary clustered then one entry for each block in file If secondary then one entry for each entry in file See Q1c Multi level index One entry for each block at the next lower level See Q1d e Views Employees Employees EmployeeID EmployeeID LastName LastName 11 Davolio Davolio 22 Fuller Fuller 33 Leverling Leverling Firstname Firstname Nancy Nancy Andrew Andrew Janet Janet Title Title USE USE Northwind Northwind GO GO CREATE CREATE VIEW VIEW dbo EmployeeView dbo EmployeeView AS AS SELECT SELECT LastName LastName Firstname Firstname FROM Employees FROM Employees EmployeeView EmployeeView Lastname Lastname Davolio Davolio Fuller Fuller Leverling Leverling Firstname Firstname Nancy Nancy Andrew Andrew Janet Janet User s View Advantages of Views Focus the Data for Users Mask Database Complexity Focus on important or appropriate data only Limit access to sensitive data hide SSN from professors Hide complex database design Simplify complex queries including distributed queries to heterogeneous data by embedding them in views Simplify Management of User Permissions Different user access DB from different views Creating Views Creating a View CREATE CREATE VIEW VIEW dbo OrderSubtotalsView dbo OrderSubtotalsView OrderID OrderID Subtotal Subtotal AS AS SELECT SELECT OD OrderID OD OrderID SUM CONVERT money OD UnitPrice Quantity 1 Discount 100 100 SUM CONVERT money OD UnitPrice Quantity 1 Discount 100 100 FROM FROM Order Order Details Details OD OD GROUP BY OD OrderID GROUP BY OD OrderID GO GO Example View of Joined Tables Orders OrderID OrderID CustomerID CustomerIDRequiredDate RequiredDateShippedDate ShippedDate 10663 BONAP 1997 09 24 1997 10 03 10663 BONAP 1997 09 24 1997 10 03 10827 BONAP 1998 01 26 1998 02 06 10827 BONAP 1998 01 26 1998 02 06 10427 PICCO 1997 02 24 1997 03 03 10427 PICCO 1997 02 24 1997 03 03 10451 QUICK 1997 03 05 1997 03 12 10451 QUICK 1997 03 05 1997 03 12 10515 QUICK 1997 05 07 1997 05 23 10515 QUICK 1997 05 07 1997 05 23 Customers CustomerID CustomerID CompanyName CompanyName ContactName ContactName BONAP BONAP PICCO PICCO QUICK QUICK USE USE Northwind Northwind GO GO CREATE CREATE VIEW VIEW dbo ShipStatusView dbo ShipStatusView AS AS SELECT SELECT OrderID OrderID ShippedDate ShippedDate ContactName ContactName FROM Customers C INNER JOIN Orders FROM Customers C INNER JOIN Orders OO ON ON C CustomerID C CustomerID O CustomerID O CustomerID WHERE RequiredDate ShippedDate WHERE RequiredDate ShippedDate Bon Bonapp app Piccolo Piccolound undmehr mehr QUICK Stop QUICK Stop Laurence LaurenceLebihan Lebihan Georg Pipps Georg Pipps Horst HorstKloss Kloss ShipStatusView OrderID OrderID ShippedDate ShippedDate ContactName ContactName 10264 10264 10271 10271 10280 10280 1996 08 23 1996 08 21 1996 08 23 1996 08 21Laurence LaurenceLebihan Lebihan 1996 08 30 1996 08 29 Georg Pipps 1996 08 30 1996 08 29 Georg Pipps 1996 09 12 1996 09 11 1996 09 12 1996 09 11Horst HorstKloss Kloss Altering and Dropping Views Altering Views USE USE Northwind Northwind GO GO ALTER ALTER VIEW VIEW dbo EmployeeView dbo EmployeeView AS AS SELECT SELECT LastName LastName FirstName FirstName Extension Extension FROM Employees FROM Employees Retains assigned permissions Causes new SELECT statement and options to replace existing definition Dropping Views DROP DROP VIEW VIEW dbo ShipStatusView dbo ShipStatusView Locating View Dependencies Use sp depends viewname Will list Objects upon which view depends The underlying or base relations Objects that depend on the view Modifying Data Through Views Update or delete allowed on view when it can be mapped to just one underlying table Cannot modify computed columns Queries executed by translation to underlying table typically Quiz Question 3 Pertinent SodaBases relations Soda name manf Likes customer soda Customer name addr phone TopSalesView depends on TotalPurchaseView Any performance problems in the underlying view can be hidden Customers Customers 11Orders nn Orders Order Details Details n 2211 Order nn n 1 3322 1 2 4433 2 3 5544 3 4 6655 4 66 55 66 USE USE Northwind Northwind GO GO CREATE CREATE VIEW VIEW dbo TopSalesView dbo TopSalesView AS AS SELECT SELECT FROM FROM dbo TotalPurchaseView dbo TotalPurchaseView WHERE WHERE Subtotal Subtotal 50000 50000 GO GO yy nn yy yy nn yy yy nn yy TotalPurchase TotalPurchaseView View 11 22 33 44 55 66 TopSalesView TopSalesView SELECT SELECT FROM FROM dbo TopSalesView dbo TopSalesView WHERE WHERE CompanyName CompanyName Ernst Ernst Handel Handel Moral of the Story Don t create views on views