Index Blocking Factors, ViewsIndex ReduxIndex CalculationsIndex Calculations (cont.)ViewsPowerPoint PresentationAdvantages of ViewsCreating ViewsExample: View of Joined TablesAltering and Dropping ViewsLocating View DependenciesModifying Data Through ViewsQuiz Question 3Slide 14Moral of the StoryIndex 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 on a page – see Q1bIndex 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, eViewsEmployeeViewEmployeeViewEmployeeViewEmployeeView Lastname Lastname Firstname FirstnameDavolio Fuller LeverlingDavolio Fuller LeverlingNancy Andrew JanetNancy Andrew JanetEmployeesEmployeesEmployeesEmployeesEmployeeIDEmployeeID LastName LastName FirstnameFirstnameTitleTitle123123DavolioFullerLeverlingDavolioFullerLeverlingNancyAndrewJanetNancyAndrewJanet~~~~~~~~~~~~~~~~~~User’s ViewUser’s ViewUSE NorthwindGOCREATE VIEW dbo.EmployeeViewAS SELECT LastName, FirstnameFROM EmployeesUSE 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 to heterogeneous 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.OrderIDGOCREATE 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 ViewOrderIDOrderIDOrderIDOrderID10663108271042710451105151066310827104271045110515CustomerIDCustomerIDCustomerIDCustomerIDBONAP BONAP PICCO QUICKQUICKBONAP BONAP PICCO QUICKQUICK~~~ ~~~ ~~~ ~~~ ~~~~~~ ~~~ ~~~ ~~~ ~~~RequiredDateRequiredDateRequiredDateRequiredDate1997-09-241998-01-261997-02-241997-03-051997-05-071997-09-241998-01-261997-02-241997-03-051997-05-07ShippedDateShippedDateShippedDateShippedDate1997-10-031998-02-061997-03-031997-03-121997-05-231997-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 < ShippedDateUSE NorthwindGOCREATE VIEW dbo.ShipStatusViewASSELECT OrderID, ShippedDate, ContactNameFROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerIDWHERE RequiredDate < ShippedDateCustomerIDCustomerIDCustomerIDCustomerIDBONAPPICCOQUICKBONAPPICCOQUICKCompanyNameCompanyNameCompanyNameCompanyNameBon app'Piccolo und mehrQUICK-StopBon app'Piccolo und mehrQUICK-StopContactNameContactNameContactNameContactNameLaurence LebihanGeorg PippsHorst KlossLaurence LebihanGeorg PippsHorst KlossOrderIDOrderIDOrderIDOrderID1026410271102801026410271102801996-08-211996-08-291996-09-111996-08-211996-08-291996-09-11ShippedDateShippedDateShippedDateShippedDate1996-08-231996-08-301996-09-121996-08-231996-08-301996-09-12ContactNameContactNameContactNameContactNameLaurence LebihanGeorg PippsHorst KlossLaurence LebihanGeorg PippsHorst KlossExample: View of Joined TablesUSE NorthwindGOALTER VIEW dbo.EmployeeViewAS SELECT LastName, FirstName, ExtensionFROM EmployeesUSE NorthwindGOALTER VIEW dbo.EmployeeViewAS SELECT LastName, FirstName, ExtensionFROM EmployeesDROP VIEW dbo.ShipStatusViewDROP VIEW dbo.ShipStatusViewAltering and Dropping ViewsAltering ViewsRetains assigned permissionsCauses new SELECT statement and options to replace 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 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)USE NorthwindGOCREATE VIEW dbo.TopSalesViewASSELECT *FROM dbo.TotalPurchaseViewWHERE Subtotal > 50000GOUSE NorthwindGOCREATE VIEW dbo.TopSalesViewASSELECT *FROM dbo.TotalPurchaseViewWHERE Subtotal > 50000GOTotalPurchaseTotalPurchaseViewViewTotalPurchaseTotalPurchaseViewView11~~~~~~~~22~~~~~~~~33~~~~~~~~44~~~~~~~~55~~~~~~~~66~~~~~~~~CustomersCustomersCustomersCustomers11~~ ~~ ~~nn 22~~ ~~ ~~nn 33~~ ~~ ~~yy 44~~ ~~ ~~yy 55~~ ~~ ~~nn 66~~ ~~ ~~yy OrdersOrdersOrdersOrders11~~ ~~ ~~nn 22~~ ~~ ~~nn 33~~ ~~ ~~yy 44~~ ~~ ~~yy 55~~ ~~ ~~nn 66~~ ~~ ~~yy Order DetailsOrder DetailsOrder DetailsOrder Details11~~ ~~ ~~~~ 22~~ ~~ ~~~~ 33~~ ~~ ~~~~ 44~~ ~~ ~~~~ 55~~ ~~ ~~~~ 66~~ ~~ ~~~~ SELECT *FROM dbo.TopSalesViewWHERE CompanyName = 'Ernst Handel'SELECT *FROM dbo.TopSalesViewWHERE CompanyName = 'Ernst Handel'TopSalesViewTopSalesViewTopSalesViewTopSalesView~~~~~~~~~~~~~~~~~~TopSalesView depends on TotalPurchaseView: Any performance problems in the underlying view can be hidden.Moral of the StoryDon’t create views on