Session 3 - Constraints, views, triggers Flashcards
What is the syntax for creating a view?
CREATE VIEW viewName as
SELECT STATEMENT
What is the syntax you can use in SQL 2016 sp1 for creating a view?
CREATE OR ALTER VIEW viewName
What can’t you have when creating a view?
Order By unless you use TOP
What is the syntax for finding out if a view exists and dropping it if it does?
if exists (select * from sys.views where name = ‘viewName’)
DROP VIEW viewName
GO
What query would you use to see all stored procedures and views?
SELECT * FROM SYS.SYSCOMMENTS
OR
SYS.SQL_MODULES
How do you secure the creation of a view or stored procedure?
Use WITH ENCRYPTION
CREATE VIEW viewName WITH ENCRYPTION AS
select statement
How do you secure CRUD operations within View?
Create view viewName as SELECT STATEMENT WITH CHECK OPTION
It makes it so it secures the view from updates, deletes and modifications to within the WHERE clause in the select statement
What are the limitations of inserting or deleting data from a view
It can only insert or delete from a single table.
What is the syntax for creating an index on a view
The first index needs to be clustered (basically like a primary key)
CREATE UNIQUE CLUSTERED INDEX indexName ON schema.viewName (columns to include)
What are all elements that cannot be contained in a view index?
- -COUNT
- -ROWSET FUNCTIONS (OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML)
- -OUTER JOINS (LEFT RIGHT FULL)
- -DERIVED TABLE
- -STDEV, STDEVP, VAR, VARP OR AVG
- -CTE
- -FLOAT, TEXT, NTEXT, IMAGE, XML, OR FILESTREAM COLUMNS
- -SUBQUERY
- -OVER CLAUSE (INCLUDES RANKING OR AGGREGATE WINDOW FUNCTIONS)
- -FULL-TEXT PREDICATES (CONTAINS, FREETEXT)
- -SUM FUNCTION THAT REFERENCES A NULLABLE EXPRESSION
- -ORDER BY
- -CLR USER-DEFINED AGGREGATE FUNCTION
- -TOP
- -CUBE, ROLLUP, OR GROUPING SETS OPERATORS
- -MIN, MAX
- -UNION, EXCEPT, OR INTERSECT OPERATORS
- -TABLESAMPLE
- -TABLE VARIABLES
- -OUTER APPLY OR CROSS APPLY
- -PIVOT, UNPIVOT
- -SPARSE COLUMN SETS
- -INLINE (TVF) OR MULTI-STATEMENT TABLE-VALUED FUNCTIONS (MSTVF)
- -OFFSET
- -CHECKSUM_AGG
What is the syntax for creating a trigger?
CREATE TRIGGER triggerName ON tableName INSTEAD OF ( OR FOR/AFTER) UPDATE, DELETE, INSERT (OR 1, 2 OF THOSE) AS BEGIN QUERY END
What’s the variable name for finding the how nested a trigger is
@@NESTLEVEL
What does DML stand for in DML trigger?
DATA MANIPULATION LANGUAGE (IE: Insert, Update, Delete)
What does DDL stand for in DDL Trigger?
DATA DEFINITION LANGUAGE (IE: Create, Alter and Drop)
What does SET NOCOUNT ON do?
Suppresses the (#Row(s) Affected) message from appearing when a script is done.
WHAT IS THE STORED PROCEDURE AND SYNTAX FOR GETTING THE CONFIGURATION OF NESTED TRIGGERS?
EXEC SP_CONFIGURE ‘NESTED TRIGGERS’;
TO UPDATE:
EXEC SP_CONFIGURE ‘NESTED TRIGGERS’, 0;
RECONFIGURE –SETS THE RUN VALUE
Within a trigger, how can you check if a column has been updated and if so run a query?
USE IF UPDATE(columnName)
BEGIN
QUERY
END
How is COLUMNS_UPDATED function used?
EACH COLUMN IS IN ORDER AND GOES FROM 1,2,4,8,16,32,64,128 SO YOU TEST FOR THE SUM OF COLUMNS UPDATED IE:
IF COLUMNS UPDATED() & 3 = 3 –MEANING THAT COLUMNS 1 AND 2 WERE UPDATED
BEGIN
QUERY
END
What are the types of triggers?
INSTEAD OF and FOR / AFTER
What is a Foreign Key
A column that references a primary key or unique constraint in another table. You can only input a field if it exists in the referenced primary key or unique constraint.
What are the two ways that SQL finds data in a table? Which is more efficient?
SEEK - Used when an Index exists. MUCH more efficient.
SCAN - Used when no Index exists. Has to search entire table.
What is the syntax for creating a Foreign Key?
ALTER TABLE
ADD CONSTRAINT ConstraintName FOREIGN KEY (ColumnName) REFERENCES schema.TableName(ColumnName)
How do you tell the system how to handle the Primary Key change with regard to the Foreign Key?
At the end of the Foreign Key creation query, you need to add:
ON [UPDATE, DELETE] [CASCADE, NO ACTION, SET NULL, SET DEFAULT]
Can you have multiple columns in a single Foreign Key?
Yes, this is called a COMPOUND FOREIGN KEY
