Index Blocking Factors,ViewsRose-Hulman Institute of TechnologyCurt CliftonIndex Redux Heap storage Clustered (primary) index Non-clustered (secondary) index On heap stored table On clustered tableIndex 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 ona page – see Q1bIndex Calculations (cont.) Single level index… If primary (clustered), then one entry for eachblock 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, eViewsEmployeeViewEmployeeView Lastname FirstnameDavolio Fuller LeverlingNancy Andrew JanetEmployeesEmployeesEmployeeID LastName Firstname Title123DavolioFullerLeverlingNancyAndrewJanet~~~~~~~~~UserUser’’s Views ViewUSE NorthwindGOCREATE VIEW dbo.EmployeeViewAS SELECT LastName, FirstnameFROM EmployeesAdvantages of Views Focus the Data for Users Focus on important or appropriate data only Limit access to sensitive data (hide SSN from professors) Mask Database Complexity Hide complex database design Simplify complex queries, including distributed queries toheterogeneous data by embedding them in views Simplify Management of User Permissions Different user access DB from different viewsCREATE VIEW dbo.OrderSubtotalsView (OrderID, Subtotal)ASSELECT OD.OrderID, SUM(CONVERT(money,(OD.UnitPrice*Quantity*(1-Discount)/100))*100)FROM [Order Details] ODGROUP BY OD.OrderIDGOCreating Views Creating a View Restrictions on View Definitions Cannot include ORDER BY clauseOrderIDOrderID1066310827104271045110515CustomerIDCustomerIDBONAP BONAP PICCO QUICKQUICK~~~ ~~~ ~~~ ~~~ ~~~RequiredDateRequiredDate1997-09-241998-01-261997-02-241997-03-051997-05-07ShippedDateShippedDate1997-10-031998-02-061997-03-031997-03-121997-05-23Orders CustomersShipStatusViewUSE NorthwindGOCREATE VIEW dbo.ShipStatusViewASSELECT OrderID, ShippedDate, ContactNameFROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerIDWHERE RequiredDate < ShippedDateCustomerIDCustomerIDBONAPPICCOQUICKCompanyNameCompanyNameBon app'Piccolo und mehrQUICK-StopContactNameContactNameLaurence LebihanGeorg PippsHorst KlossOrderIDOrderID1026410271102801996-08-211996-08-291996-09-11ShippedDateShippedDate1996-08-231996-08-301996-09-12ContactNameContactNameLaurence LebihanGeorg PippsHorst KlossExample: View of Joined TablesUSE NorthwindGOALTER VIEW dbo.EmployeeViewAS SELECT LastName, FirstName, ExtensionFROM EmployeesDROP VIEW dbo.ShipStatusViewAltering and Dropping Views Altering Views Retains assigned permissions Causes new SELECT statement and options toreplace existing definition Dropping ViewsLocating View Dependencies Use: sp_depends viewname Will list: Objects upon which view depends The "underlying" or "base" relations Objects that depend on the viewModifying Data Through Views Update or delete allowed on view when it canbe mapped to just one underlying table Cannot modify computed columns Queries executed by translation to underlyingtable (typically)Quiz Question 3 Pertinent SodaBases relations: Soda(name, manf) Likes(customer, soda) Customer(name, addr, phone)USE NorthwindGOCREATE VIEW dbo.TopSalesViewASSELECT *FROM dbo.TotalPurchaseViewWHERE Subtotal > 50000GOTotalPurchaseTotalPurchaseViewView1 ~ ~ ~ ~2 ~ ~ ~ ~3 ~ ~ ~ ~4 ~ ~ ~ ~5 ~ ~ ~ ~6 ~ ~ ~ ~CustomersCustomers1 ~ ~ ~ n 2 ~ ~ ~ n 3 ~ ~ ~ y 4 ~ ~ ~ y 5 ~ ~ ~ n 6 ~ ~ ~ y OrdersOrders1 ~ ~ ~ n 2 ~ ~ ~ n 3 ~ ~ ~ y 4 ~ ~ ~ y 5 ~ ~ ~ n 6 ~ ~ ~ y Order DetailsOrder Details1 ~ ~ ~ ~ 2 ~ ~ ~ ~ 3 ~ ~ ~ ~ 4 ~ ~ ~ ~ 5 ~ ~ ~ ~ 6 ~ ~ ~ ~ SELECT *FROM dbo.TopSalesViewWHERE CompanyName = 'Ernst Handel'TopSalesViewTopSalesView~ ~ ~~ ~ ~~ ~ ~TopSalesView depends onTotalPurchaseView: Anyperformance problems in theunderlying view can be hidden.Moral of the Story Don’t create views on
View Full Document
Unlocking...