Instructor Inputs - Session 10

Instructor Inputs Session Overview This session includes sections two and three of Chapter 6 and section one of Chapter 7 of the Student Guide. Slide 1 Querying and Managing Data Using SQL Server 2005 Objectives In this session, you will learn to: Create and manage views Implement a full-text search Implement batches Ver. 1.0 Session 10 Slide 1 of 31 Begin the session by sharing the objectives with the students. Slide 2 Querying and Managing Data Using SQL Server 2005 Creating Views A View is: Used to view data from tables Similar to creating tables but it does not contain any data as it derives its data from the underlying tables Created by using CREATE VIEW statement Syntax: CREATE VIEW view_name [(column_name [, column_name]...)] [WITH ENCRYPTION] [, SCHEMABINDING]] AS select_statement [WITH CHECK OPTION] Let’s see how… Ver. 1.0 Session 10 Slide 2 of 31 ¤NIIT Instructor Inputs 10.3 In this slide, you will explain the concept of views to the students. Explain to the students that views are used for two reasons, to simplify complex queries for users and to restrict users from viewing data directly from the table. While describing views to the students, you can use the example of the small window on your classroom door. A person peeping through the window will only be able to see only a small portion of the room and will think that the visible portion is the entire classroom. Similarly, for a view which displays only four columns of a table, the user will perceive that the table contains only four columns. You need to emphasize that views cannot store data by themselves, they obtain the data from the base tables. A view is nothing but a query stored as an object. Use the examples given in the Student Guide to demonstrate how to create the view. Explain to the students that it is a good practice to follow the naming conventions while creating views. Prefix the name of the view by using ‘vw’. If you want to restrict users from seeing the definition of the view by using the sp_helptext procedure, you can encrypt the definition by using the WITH ENCRYPTION option in the CREATE VIEW statement. Slide 3 Querying and Managing Data Using SQL Server 2005 Indexing Views Indexing of Views: Is done when the volume of data in the underlying table is large and not updated frequently Improves query performance Is performed by creating an unique clustered index on a view and afterwards nonclustered index can also be created Is performed by using CREATE INDEX statement Let’s see how… Ver. 1.0 Session 10 Slide 3 of 31 In this slide, you need to explain the concept, importance, and benefit of indexing the views. Use the examples given in the Student Guide to demonstrate how to create indexed views. 10.4 Instructor Inputs ¤NIIT Slide 4 Querying and Managing Data Using SQL Server 2005 Just a minute In which of the following conditions will you NOT create an indexed view: 1. When the data is large 2. When the data is regularly updated 3. When you need to improve the performance of the view Answer: 2. When the data is regularly updated Ver. 1.0 Session 10 Slide 4 of 31 Reiterate the concepts taught in the preceding slides by asking the question. Slide 5 Querying and Managing Data Using SQL Server 2005 Managing Views Managing a view involves altering, dropping, or renaming. Altering a view involves modifying a view without dropping it. Syntax: ALTER VIEW view_name [(column_name)] WITH ENCRYPTION] AS select_statement WITH CHECK OPTION] Dropping a view involves deleting the view when it is no longer required. Syntax: DROP VIEW view_name Ver. 1.0 Session 10 Slide 5 of 31 In this slide, you need to explain how to manage views. This involves altering the definition and dropping a view. Use the examples given in the Student Guide to demonstrate how to manage views. ¤NIIT Instructor Inputs 10.5 ... - tailieumienphi.vn