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
How do you suppress the number of rows updated messages?
SET NOCOUNT ON
What are the three ways to find out what number the IDENTITY is on in a table?
@@IDENTITY
SCOPE_IDENTITY()
IDENT_CURRENT(TableName)
What does @@IDENTITY show you?
The global variable for the last value that was returned for the last Identity globally.
What does SCOPE_IDENTITY() show you?
The last identity value that was returned within a scope (function, stored procedure, etc.)
What does IDENT_CURRENT(TableName) show you?
The last value returned by the identity in the table specified.
How can you order a view and return all rows?
SELECT TOP(100) PERCENT [COLUMNS] FROM [TABLE] ORDER BY [COLUMNS]
What is the function used to get an object ID out of SQL?
OBJECT_ID(‘Object Name’)
What is the function for getting the View, Stored Procedure or Function definition?
OBJECT_DEFINITION(ObjectId)
What access does a view have?
All data that can be returned from the view from any of the tables AS LONG AS THE TABLES ARE IN THE SAME SCHEMA
When creating a view that is going to have an index attached to it while also having a join, what option needs to be added?
WITH SCHEMABINDING:
CREATE VIEW [SCHEMA].[VIEWNAME] WITH SCHEMABINDING AS
SELECT STATEMENT
Can you create a UNIQUE CLUSTERED INDEX on a VIEW when the view returns duplicate values?
NO. It is like creating a primary key, the values all have to be unique.
Can you drop a table that has a VIEW attached to it?
No, you have to drop the view first, then drop the table.
What are the two special tables created within a trigger?
INSERTED
DELETED
These allow you to see the data that was inserted or deleted from a table during the table modification.
What’s the maximum nest trigger value?
32
How do you set up AFTER trigger nesting?
EXEC SP_CONFIGURE ‘NESTED TRIGGERS’;
RECONFIGURE – NEEDED TO COMPLETE CHANGE
What are the steps needed to be able to insert into an identity column?
SET IDENTITY_INSERT TableName ON;
INSERT INTO TABLE (columns) VALUES (values) –You HAVE to expressly label the columns that you are going to insert into for an identity column even with identity_insert ON
