Session 3 - Constraints, views, triggers

Question 1

What is the syntax for creating a view?

Answer 1

CREATE VIEW viewName as

SELECT STATEMENT

Question 2

What is the syntax you can use in SQL 2016 sp1 for creating a view?

Answer 2

CREATE OR ALTER VIEW viewName

Question 3

What can’t you have when creating a view?

Answer 3

Order By unless you use TOP

Question 4

What is the syntax for finding out if a view exists and dropping it if it does?

Answer 4

if exists (select * from sys.views where name = ‘viewName’)
DROP VIEW viewName
GO

Question 5

What query would you use to see all stored procedures and views?

Answer 5

SELECT * FROM SYS.SYSCOMMENTS
OR
SYS.SQL_MODULES

Question 6

How do you secure the creation of a view or stored procedure?

Answer 6

Use WITH ENCRYPTION

CREATE VIEW viewName WITH ENCRYPTION AS
select statement

Question 7

How do you secure CRUD operations within View?

Answer 7

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

Question 8

What are the limitations of inserting or deleting data from a view

Answer 8

It can only insert or delete from a single table.

Question 9

What is the syntax for creating an index on a view

Answer 9

The first index needs to be clustered (basically like a primary key)

CREATE UNIQUE CLUSTERED INDEX indexName ON schema.viewName (columns to include)

Question 10

What are all elements that cannot be contained in a view index?

Answer 10

• -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

Question 11

What is the syntax for creating a trigger?

Answer 11

CREATE TRIGGER triggerName ON tableName
INSTEAD OF ( OR FOR/AFTER) UPDATE, DELETE, INSERT (OR 1, 2 OF THOSE)
AS
BEGIN
QUERY
END

Question 12

What’s the variable name for finding the how nested a trigger is

Answer 12

@@NESTLEVEL

Question 13

What does DML stand for in DML trigger?

Answer 13

DATA MANIPULATION LANGUAGE (IE: Insert, Update, Delete)

Question 14

What does DDL stand for in DDL Trigger?

Answer 14

DATA DEFINITION LANGUAGE (IE: Create, Alter and Drop)

Question 15

What does SET NOCOUNT ON do?

Answer 15

Suppresses the (#Row(s) Affected) message from appearing when a script is done.

Question 16

WHAT IS THE STORED PROCEDURE AND SYNTAX FOR GETTING THE CONFIGURATION OF NESTED TRIGGERS?

Answer 16

EXEC SP_CONFIGURE ‘NESTED TRIGGERS’;
TO UPDATE:
EXEC SP_CONFIGURE ‘NESTED TRIGGERS’, 0;
RECONFIGURE –SETS THE RUN VALUE

Question 17

Within a trigger, how can you check if a column has been updated and if so run a query?

Answer 17

USE IF UPDATE(columnName)
BEGIN
QUERY
END

Question 18

How is COLUMNS_UPDATED function used?

Answer 18

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

Question 19

What are the types of triggers?

Answer 19

INSTEAD OF and FOR / AFTER

Question 20

What is a Foreign Key

Answer 20

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.

Question 21

What are the two ways that SQL finds data in a table? Which is more efficient?

Answer 21

SEEK - Used when an Index exists. MUCH more efficient.

SCAN - Used when no Index exists. Has to search entire table.

Question 22

What is the syntax for creating a Foreign Key?

Answer 22

ALTER TABLE

ADD CONSTRAINT ConstraintName FOREIGN KEY (ColumnName) REFERENCES schema.TableName(ColumnName)

Question 23

How do you tell the system how to handle the Primary Key change with regard to the Foreign Key?

Answer 23

At the end of the Foreign Key creation query, you need to add:

ON [UPDATE, DELETE] [CASCADE, NO ACTION, SET NULL, SET DEFAULT]

Question 24

Can you have multiple columns in a single Foreign Key?

Answer 24

Yes, this is called a COMPOUND FOREIGN KEY

Question 25

How do you suppress the number of rows updated messages?

Answer 25

SET NOCOUNT ON

Question 26

What are the three ways to find out what number the IDENTITY is on in a table?

Answer 26

@@IDENTITY
SCOPE_IDENTITY()
IDENT_CURRENT(TableName)

Question 27

What does @@IDENTITY show you?

Answer 27

The global variable for the last value that was returned for the last Identity globally.

Question 28

What does SCOPE_IDENTITY() show you?

Answer 28

The last identity value that was returned within a scope (function, stored procedure, etc.)

Question 29

What does IDENT_CURRENT(TableName) show you?

Answer 29

The last value returned by the identity in the table specified.

Question 30

How can you order a view and return all rows?

Answer 30

SELECT TOP(100) PERCENT [COLUMNS] FROM [TABLE] ORDER BY [COLUMNS]

Question 31

What is the function used to get an object ID out of SQL?

Answer 31

OBJECT_ID(‘Object Name’)

Question 32

What is the function for getting the View, Stored Procedure or Function definition?

Answer 32

OBJECT_DEFINITION(ObjectId)

Question 33

What access does a view have?

Answer 33

All data that can be returned from the view from any of the tables AS LONG AS THE TABLES ARE IN THE SAME SCHEMA

Question 34

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?

Answer 34

WITH SCHEMABINDING:

CREATE VIEW [SCHEMA].[VIEWNAME] WITH SCHEMABINDING AS
SELECT STATEMENT

Question 35

Can you create a UNIQUE CLUSTERED INDEX on a VIEW when the view returns duplicate values?

Answer 35

NO. It is like creating a primary key, the values all have to be unique.

Question 36

Can you drop a table that has a VIEW attached to it?

Answer 36

No, you have to drop the view first, then drop the table.

Question 37

What are the two special tables created within a trigger?

Answer 37

INSERTED
DELETED

These allow you to see the data that was inserted or deleted from a table during the table modification.

Question 38

What’s the maximum nest trigger value?

Answer 38

32

Question 39

How do you set up AFTER trigger nesting?

Answer 39

EXEC SP_CONFIGURE ‘NESTED TRIGGERS’;

RECONFIGURE – NEEDED TO COMPLETE CHANGE

Question 40

What are the steps needed to be able to insert into an identity column?

Answer 40

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