sql queries - view, stored procedures, index

view

• Returns a table based on the result of a SQL statement

• pro

  • can reuse queries

  • can write complex queries w/out subqueries

  • security purposes

  • no space on hardware

• con

  • can take time to load

• Format

CREATE VIEW name of the view AS

SELECT column1, column2

FROM  name of the table

WHERE set the condition

ex. a VIEW for 'Sales' showing salesperson id, product id, and quantity

create view Sales_V as

select sp.SalespersonName, ol.ProductID, sum(ol.OrderedQuantity) [quantity]

from Salesperson_T sp inner join Order_T o on sp.SalespersonID = o.SalesPersonID

inner join OrderLine_T ol on o.OrderID=ol.OrderID

group by sp.SalespersonName,ol.ProductID

 

materialized view

• Copies data that is actually stored

• Needs to be refreshed periodically to match the base tables

dynamic view

•  dynamically created table upon request

• This data is not stored

• Data from the base tables are made available

• Based on the select statement involving base tables or other views

• Pros

  • Simplifies query commands

  • Security

  • Productivity

  • Always has the most current based table data

  • Text upload space

  • The view is more customized to the user

  • Independent from physical data

• Cons

  • Each time this view is referenced, it takes time to process

  • Not always directly updatable

routines

• Modules that execute on demand

• Functions

  • Returns values and takes input parameters

• Stored procedures

  • Does not return values but can take input or output parameters

  • Instead of writing a query over and over again

  • EX.

create procedure show_customers_and_employees

 as

begin

select* from Customer_T;

select * from Employee_T;

end

 

exec show_customers_and_employees

• ex. with parameters

create procedure SelectCustomerByState @state varchar(50)

 as

begin

select* from Customer_T

where CustomerState - @state;

end

triggers

• They happen in response to a database event

• Like INSERT, UPDATE, DELETE

index

• Clustered

  • Data in the table or view is sorted and stored based on their key values

  • Format:

Create index index1 ON table 1(column1);

• Non clustered

  • The structure is separate from the data rows

  • Each key value has a pointer to the data row that contains the key value

metadata in SQL server

• It can't be queried

• It is exposed in two ways

  • information schema views

  • Catalog views

    • Located in the sys schema

    • ex.

Select*

From sys.objects

Where type_desc = 'USER_TABLE'

• Information schema views

  • Standard method for accessing metadata across different databases

  • Information is more limited than catalog views

  • ex.

Select*

From information_schema.tables