Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL 70-761 > Program Databases by Using T-SQL > Flashcards

Program Databases by Using T-SQL Flashcards

1
Q

What are some things you can do with a stored procedure that you can’t do with a user-defined function?

Can you embed stored procedures in queries the way you can with functions?

A

Stored procedures allow you to modify data in the database, apply data definition changes to database objects, use temporary tables, dynamic SQL, and error handling.

No, you cannot embed a stored procedure in a query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Syntax to execute a stored procedure with two input parameters

A

EXEC schema.procname @parameter1 = x, @parameter2 = y;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Syntax to execute a stored procedure with one input parameter and one output parameter

A

DECLARE @outputparameter AS datatype;

EXEC schema.procname @inputparameter, @outputparameter OUTPUT;

SELECT @outputparameter;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Syntax to create a stored procedure with one input parameter and one output parameter

A

CREATE PROC procname (@inputparameter AS datatype, @outputparameter AS datatype OUTPUT)

AS

SELECT….

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

How do you make input parameters optional?

A

You make input parameters optional by defining them with a default NULL:

@parameter AS datatype = NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is dynamic SQL?

A

It’s a technique where you put together code as a character string, usually in a variable, and then tell SQL Server to execute the code that resides in that variable.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What are 2 ways to dynamically execute SQL statements?

What are 3 advantages of one over the other?

A

EXEC (‘sqlquery’) and sp_executesql

  1. EXEC doesn’t accept parameters, but sp_executesql does.
  2. sp_execute minimizes risk of SQL injection (security risk)
  3. sp_execute can perform better than EXEC because of query plan reuse
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Syntax to use sp_executesql with 1 parameter.

A

DECLARE @sqlcode AS NVARCHAR(MAX) = N’SELECT … WHERE col1 = @param1’;

EXEC sys.sp_executesql

@stmt = @sqlcode

@params = N’@param1 AS datatype’

@param1 = something;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Syntax to declare and initialize two variables with one statement

A

DECLARE @variable1 datatype = x, @variable2 datatype = y;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is the scope of variables?

A

Variables are always local to the batch in which they’re declared and go out of scope when the batch ends.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What are 3 ways of assigning values to variables?

A
  1. Initialize the variable with a value using the DECLARE statement
  2. Use SET statement, e.g. SET @variable = 1;
  3. Use a SELECT statement, e.g. SELECT @variable = COUNT(*) FROM tablename

Note: the query has to return exactly 1 row

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Syntax for creating a synonym for a stored procedure

A

CREATE SYNONYM schema.procname FOR db.schema.procname;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is a SYNONYM is SQL Server?

What types of things can a synonym point to?

How are synonyms useful?

A

A synonym is an alias or link to an object stored either on the same SQL server instance or a linked server.

A synonym can point to tables, views, procedures, and functions.

Synonyms can be used for referencing remote objects as though they were located locally, or for providing alternative names to other local objects.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

RAISERROR function syntax

RAISERROR syntax to raise error using the following custom error:

sp_addmessage 50001, 10, N’This is an exception: %s %d %s’

How do you add error to event log?

Does RAISERROR terminate the batch or the transaction?

A

Part 1:

RAISERROR(N’%s %d’, severity, state, N’MessageText’, ErrorNum);

%s is placeholder for MessageText

%d is placeholder for ErrorNum (which is an integer)

severity is an int

state = 1

Example: RAISERROR(N’%s %d’, 10, 1, N’Custom error message number’, 2);

Returns: Custom error message number 2

Part 2:

sp_addmessage 50001, 10, N’This is an exception: %s %d %s’

RAISERROR(50001, 10, 1, N’Error number:’, 123, N’- No action needed’)

Returns: This is an exception: Error number: 123 - No action needed

Part 3:

Add WITH LOG, e.g.

RAISERROR(N’%s %d’, 10, 1, N’Custom error message number’, 2) WITH LOG;

Part 4: RAISERROR does not terminate the batch or the transaction

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Where do you use THROW without parameters? What does it do (2 things)?

Syntax for THROW statement with parameters. Where can you use this? Describe its behavior when you use it outside a TRY-CATCH construct, inside a TRY block, in an open transaction with XACT_ABORT off, in an open transaction with XACT_ABORT on and not using TRY-CATCH, in an open transaction with XACT_ABORT on and using TRY-CATCH.

Errors raised by a THROW statement are always severity ___.

A

You use THROW without parameters in a CATCH block to re-throw the error that originally activated that CATCH block. It aborts the batch so any code below it doesn’t run.

THROW ErrorNum, ‘MessageText’, state;

Example: THROW 50001, ‘An Error Occurred’, 0;

You can use this inside or outside a CATCH block. When you use it:

  1. Outside a TRY-CATCH construct: it aborts the batch.
  2. Inside a TRY block: it causes the corresponding CATCH block to be activated.
  3. In an open tran with XACT_ABORT off: the transaction remains open and commitable
  4. In an open tran with XACT_ABORT on, no TRY-CATCH: it aborts the transaction
  5. In an open tran with XACT_ABORT on, with TRY-CATCH: it dooms the transaction

Errors raised by a THROW statement are always severity 16.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is an advantage of using THROW over RAISERROR?

What can you do with RAISERROR that you can’t with THROW?

A

An advantage of using THROW over RAISERROR is that for custom errors, it does not require defining errors in the sys.messages table.

RAISERROR has the option to add error to event log while THROW does not.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Between THROW and RAISERROR, which one allows you to re-throw an original error that was caught by a TRY-CATCH construct?

A

THROW

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

THROW vs RAISERROR

Complete the following table

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

How can you add your own custom application errors?

Error codes above ______ are custom error codes.

A

Use the sp_addmessage procedure:

sp_addmessage ErrorNum, SeverityNum, ‘MessageText’

50,000

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What does the @@ERROR system function do?

What should you do if you want to capture an error?

A

Returns last error code

Store @@ERROR in a variable

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

TRY-CATCH syntax

How does it work?

A

BEGIN TRY

….regular code…

END TRY

BEGIN CATCH

…error handling…

END CATCH;

If any error occurs in the regular code, execution is transferred to the CATCH block, and the error-handling code is executed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is one important limitation of TRY-CATCH?

What is the output of the following:

CREATE PROC inner_sp

AS

BEGIN TRY

PRINT ‘This prints’

SELECT * FROM NoSuchTable

END TRY

BEGIN CATCH

PRINT ‘And nor does this print’

END CATCH

go

EXEC inner_sp

What is a way to catch this error?

A

It does not catch compilation errors that occur in the same scope.

The output is:

This prints

Msg 208, Level 16, State 1, Procedure inner_sp, Line 4 Invalid object name ‘NoSuchTable’

To catch the error, you can use an outer scope:

CREATE PROC outer_sp

AS

BEGIN TRY

EXEC inner_sp

END TRY

BEGIN CATCH

PRINT ‘The error message is: ‘ + error_message()

END CATCH

go

EXEC outer_sp

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

BEGIN TRANSACTION marks the starting point of an explicit, user-defined transaction. How long does the transaction last?

A

Transactions last until a COMMIT statement is issued, a ROLLBACK is manually issued, or the connection is broken and the system issues a ROLLBACK

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Transaction syntax with error-handling

A

BEGIN TRY

BEGIN TRAN

….code….

COMMIT TRAN

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

;THROW

END CATCH;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

SET XACT_ABORT ON;

What does this do?

A

When a runtime error occurs, the entire transaction is terminated and automatically rolled back, unless error occurs in a TRY block.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Which statement should you always be at the beginning of your stored procedures? What does this do?

A

SET XACT_ABORT, NOCOUNT ON

By default, SQL Server doesn’t always roll back transactions when there’s an error. XACT_ABORT ensures that this happens. NOCOUNT suppresses messages like (1 row affected) that you see in the Message tab in SSMS. While these rows counts can be useful when you work interactively in SSMS, they can degrade performance in an application because of the increased network traffic.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What is @@TRANCOUNT useful for?

A

It’s useful to figure out whether you’re currently in an open transaction or not. If you’re in an open transaction, the function returns a value greater than zero, otherwise, it returns zero.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Transactional statements include DML statements (SELECT against a table, INSERT, UPDATE, DELETE, TRUNCATE, MERGE), many DDL statements, DCL statements like GRANT and REVOKE. What are examples of non-transactional operations?

If you applied these in a transaction that ended up rolling back, what would happen to them?

A

Assigning values to variables and modifying data in table variables are not transactional operations.

If the transaction rolls back, these things would not be undone.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What is the output of the following code?

CREATE TABLE dbo.T1(col1 INT);

PRINT @@TRANCOUNT

BEGIN TRAN;

PRINT @@TRANCOUNT

BEGIN TRAN;

PRINT @@TRANCOUNT

BEGIN TRAN;

PRINT @@TRANCOUNT

INSERT INTO dbo.T1 VALUES(1), (2), (3);

COMMIT TRAN;

PRINT @@TRANCOUNT

ROLLBACK TRAN;

PRINT @@TRANCOUNT

SELECT col1 FROM dbo.T1

A

Output:

0

1

2

3

2

0

(col1 is empty)

Every BEGIN TRAN statement increases @@TRANCOUNT by 1. Every COMMIT TRAN statement decreases @@TRANCOUNT by 1. However, SQL Server truly commits the transaction only if you issue a COMMIT TRAN statement when @@TRANCOUNT is 1. So, when we issued the ROLLBACK TRAN statement, we roll back the entire thing. That’s why the values were not inserted in the table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

You need to create a table type to use as the data type for table variables and table valued parameters. What is the syntax to create a table type?

A

CREATE TYPE schema.name AS TABLE

(col1 datatype NOT NULL PRIMARY KEY

col2 datatype NOT NULL CHECK…);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

In terms of transactions, what is a savepoint?

How do you make a savepoint and how do you roll back to it?

A

A savepoint is a marker within an open transaction that you can later roll back to, undoing only the changes that took place since the savepoint, and not the rest of of the changes that took place in the transaction.

Make a savepoint with SAVE TRAN savepointname;

Roll back to a savepoint with ROLLBACK TRAN savepointname;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

What are the 6 error functions in T-SQL?

A
  1. ERROR_NUMBER()
  2. ERROR_MESSAGE()
  3. ERROR_SEVERITY()
  4. ERROR_STATE()
  5. ERROR_LINE()
  6. ERROR_PROCEDURE()
33
Q

What 3 things should you keep in mind when using error functions?

A
  1. If you invoke the functions not within a CATCH block, they all return NULLs.
  2. If you nest TRY-CATCH constructs, whether directly or indirectly when you call one procedure from another, these functions return error information about the innermost error.
  3. Some failures generate a chain of errors. These functions only return information about the last error in the chain.
34
Q

Parsing ambiguity

What would be the output of the following CATCH blocks?

BEGIN CATCH

SELECT ‘Hey There’

THROW;

END CATCH;

BEGIN CATCH

IF @@TRANCOUNT > 0 ROLLBACK TRAN

THROW;

END CATCH;

To avoid these ambiguities, it’s good practice to precede THROW with ___.

A

The output is a column named “THROW” with value “Hey There” because SQL Server assumes THROW is an alias for the preceding expression.

The output would be an error because SQL Server is looking for the a transaction or savepoint named “THROW”, which doesn’t exist.

To avoid these ambiguities, it’s good practice to precede THROW with ;

35
Q

What severity of errors are informational and are not catchable?

What severity of errors are catchable?

What severity of errors terminate the connection?

A

0-10 are informational and not catchable

11-19 are catchable

20-25 terminate the connection

36
Q

If you modify a view, for example, by adding a row, will the underlying table be affected?

A

Yes, this will add a row to the underlying table.

37
Q

Syntax to create an index on a table

Syntax to drop an index on a table

A

CREATE INDEX IX_tablename_indexedcol ON tablename (indexedcol desc)

DROP INDEX tablename.indexname

38
Q

What is a clustered index? How many clustered indexes can a table have?

Syntax for creating a clustered index on two columns

True or False: Creating a primary key automatically creates a clustered index

A

A clustered index determines teh physical order of data in a table. For this reason, a table can have only one clustered index.

CREATE CLUSTERED INDEX IX_tablename_col1_col2 ON tablename (col1 desc, col2 asc)

True (if no clustered index already exists on the table)

39
Q

An nonclustered index is analogous to _______. Where is it stored relative to the data?

Can you have more than one nonclustered index on a table?

Does a nonclustered index affect the order of the data?

Which one is faster, a clustered or nonclustered index?

Syntax for a unique, nonclustered index

A

A nonclustered index is analogous to an index in a textbook. The data is stored in one place, the index in another place. The index will have pointers to the storage location of the data.

Yes, you can have more than one nonclustered index on a table.

It does not affect the order of the data in the table.

A clustered index is faster than a non-clustered index because the nonclustered index has to refer back to the table if the selected column in not present in the index.

CREATE UNIQUE NONCLUSTERED INDEX UIX_tablename_col ON tablename(col)

40
Q

What is the difference between a unique constraint and unique index?

A

There are no major differences between the two. In fact, when you add a unique constraint, a unique index gets created behind the scenes

41
Q

When you create an index on a view, the view gets _________. This means the the view is now capable of _________.

A

When you create an index on a view, the view gets materialized. This means the the view is now capable of storing data.

42
Q

What are 6 things necessary to create an indexed view?

A
  1. Create view with the SCHEMABINDING option
  2. If there’s a possiblity that an aggregate function in the SELECT list may return a NULL, then a replacement value should be specified
  3. If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression
  4. The base tables in the view should be referenced with a 2 part name
  5. The first index you create on the view has to be clustered and unique
  6. You’re not allowed to manipulate the result of an aggregate function, e.g. CAST(SUM())
43
Q

What are 3 types of user-defined functions?

A

Scalar, inline table valued, and multi-statement table valued

44
Q

What are 5 limitations on user-defined functions?

A

You cannot:

  1. Use error handling
  2. Modify data (other than in table variables)
  3. Use data definition language (DDL)
  4. Use temporary tables
  5. Use dynamic SQL
45
Q

Syntax for a scalar user-defined function

A

CREATE OR ALTER FUNCTION schema.fxname(@param AS datatype)

RETURNS datatype

AS

BEGIN

….code…

END;

46
Q

What’s the difference between user-defined functions and store procedures when it comes to setting a default value for a parameter?

A

With stored procedures, you can just omit the parameter to use the default value. With user-defined functions, you must specify the keyword DEFAULT instead of passing a value.

47
Q

What’s the difference between invoking a built-in nondeterministic function like SYSTDATETIME directly in a query versus invoking it from a user-defined function?

A

When you invoke a nondeterministic function directly in a query, SQL Server executes it only once for the entire query, i.e. all rows would have the same value. When you invoke it inside a user-defined function and then invoke the user-defined function in a query, the function gets executed once per row.

48
Q

Which two nondeterministic functions are you not allowed to use inside a user-defined function?

What’s a way to circumvent this for one of them?

A

NEWID and RAND

You can use a view as an intermediate step for RAND:

CREATE VIEW dbo.VRAND
AS
SELECT RAND() AS myrand;
GO

CREATE FUNCTION dbo.MyRAND() RETURNS FLOAT
AS
BEGIN
RETURN (SELECT myrand FROM dbo.VRAND);
END;

GO

49
Q

What is a persisted column?

A

It’s a computed column where SQL Server physically stores the data of the column on disk

50
Q

In order to use a user-defined function in a persisted computed column or in an indexed view, the function needs to be ________. This means that the user-defined function must not call __________ functions, plus you need to define it with the __________ attribute.

A

In order to use a user-defined function in a persisted computed column or in an indexed view, the function needs to be deterministic. This means that the user-defined function must not call nondeterministic functions, plus you need to define it with the SCHEMABINDING attribute.

51
Q

What does the SCHEMABINDING option do?

A

Prevents structural changes to dependent objects, for example the underlying table.

52
Q

Syntax for inline table-valued user-defined function

A

CREATE OR ALTER FUNCTION schema.fxname(@param datatype)

RETURNS TABLE

AS

RETURN

….code….

53
Q

Syntax for multistatement table-valued user-defined function

A

CREATE FUNCTION schema.fxname(@param AS datatype)

RETURNS @tablevariable TABLE

( col1 datatype
col2 datatype….)

AS

BEGIN

….code….

RETURN;

END;

54
Q

What is the difference between fixed data types (e.g. CHAR, NCHAR, BINARY) versus dynamic ones (e.g. VARCHAR, NVARCHAR, VARBINARY)?

A

Fixed types use the specified amount of storage regardless of how many characters are entered. For example, CHAR(30) uses storage for 30 characters whether you actually specify 30 characters or less.

Variable types use the storage for what you enter, plus a couple of bytes for offset information (or 4 bits with row compression).

55
Q

What is the difference between regular character types (CHAR, VARCHAR) versus Unicode types (NCHAR, NVARCHAR)?

A

Regular character types use 1 byte of storage per character and support only one language besides English.

Unicode types use 2 bytes of storage per character and support multiple languages.

56
Q

What is the result of the following?

SELECT

CAST(10.999 AS NUMERIC(12,0)) AS numeric_to_numeric,

CAST(10.999 AS INT) AS numeric_to_int;

A

The first CAST generates 11 (rounds up)

The second CAST generates 10 (truncates)

57
Q

What does the following code output?

DECLARE @s AS CHAR(21) = ‘20170212 23:59:59.999’,
@dt2 AS DATETIME2 = ‘20170212 23:59:59.999999’;

SELECT

CAST(@s AS DATETIME) AS char_to_datetime,

CAST(@dt2 AS DATETIME) AS char_to_datetime;

A

Both output 2017-02-13 00:00:00.000

(rounds up when going from character to datetime and from datetime with higher precision to datetime with lower precision)

58
Q

What is the output of the following code?

DECLARE

@x AS VARCHAR(3) = NULL,

@y AS VARCHAR(10) = ‘1234567890’;

SELECT ISNUL(@x, @y) AS ISNULLxy, COALESCE(@x, @y) AS COASLESCExy;

A

ISNULLxy outputs 123

COALESCExy output 1234567890

For ISNULL, the datatype is determined by the first input, so the string gets truncated.

With COALESCE, teh type of teh result is the type with teh highest precedence among the inputs.

59
Q

What is the output of the following code?

SELECT ISNULL(‘1a2b’, 1234) AS ISNULLstrnum;

SELECT COALESCE(‘1a2b’, 1234) AS COALESCEstrnum;

A

ISNULLstrnum returns 1a2b

COALESCEstrnum returns an error because the input with the highest data type precedence is INT, so it tries to convert the first input to INT before returning it and the conversion fails.

60
Q

In the following code, do the columns ISNULLregioncountry and COALESCEregioncountry allow NULLs? Assume that the region column allows NULLs and the country column does not.

SELECT empid,

ISNULL(region, country) AS ISNULLregioncountry,

COALESCE(region, country) AS COALESCEregioncountry

INTO dbo.TestNULLs

FROM HR.Employees;

What are the general rules for this?

A

ISNULLregioncountry does not allow NULLs.

COALESCEregioncountry allows NULLs.

When using a SELECT INTO statement, the result column of ISNULL is defined as NOT NULL if any of the input expressions is nonnullable. If both inputs are nullable, the result is a column defined as allowing NULLs. With COALESCE, only if all inputs are non-nullable, the result column is defined as NOT NULL, otherwise, it is defined as allowing NULLs.

61
Q

What will be the data type of the result of ISNULL and COALESCE when both inputs are untyped NULL literals?

A

ISNULL will return a result with type INT

COALESCE will give you an error

62
Q

When using a subquery in an ISNULL or COALESCE function, which one has a performance advantage? Why?

e.g.

ISNULL((subquery), 0) or COALESCE((subquery), 0)

A

ISNULL has a performance advantage because it only evaluates the subquery once.

With COALESCE, the expression is translated to:

CASE WHEN (subquery) IS NOT NULL THEN (subquery) ELSE 0 END

So, it executes the subquery twice.

63
Q

Syntax to create an Instead of trigger

A

CREATE TRIGGER tr_tablename_InsteadOfInsert

ON tablename

INSTEAD OF INSERT

64
Q

Example of using INTERSECT to get all matching values in a join including NULLs

A

SELECT A.col1, B.col1

FROM dbo.TableA A

INNER JOIN dbo.TableB B

ON EXISTS( SELECT A.key1, A.key2

INTERSECT

SELECT B.key1, B.key2)

pg 321

65
Q

What are two types of DML triggers? Define them.

A

After triggers, a.k.a. FOR triggers fire after the triggering action (INSERT, DELETE, UPDATE).

Instead of triggers fire instead of the triggering action

66
Q

Syntax to create a DML after trigger

A

CREATE TRIGGER tr_tablename_ForInsert

ON tablename

FOR INSERT

AS BEGIN

SELECT * FROM inserted

END

Note: instead of INSERT, could have DELETE or UPDATE

Note: “inserted” table is created by SQL Server when you do an insert and it is only available in the context of triggers. Similarly, there’s also a “deleted” table

67
Q

Syntax to create an instead of trigger

A

CREATE TRIGGER tr_tablename_InsteadOfInsert

ON tablename

INSTEAD OF INSERT

AS

BEGIN

SELECT * from inserted

END

Note: Can also use UPDATE and DELETE instead of INSERT

68
Q

What does the UPDATE function do? Where is it used?

A

It returns true if the column specified is being updated (is in the SET statement). It can be used when you’re creating a trigger to check whether a column is being updated:

eg. IF(UPDATE(col1))

69
Q

What is database normalization?

A

Database normalization is the process of organizing data to minimize data redundancy (data duplication), which in turn ensures data consistency.

e.g. a table has employee info and columns with department, department head, and department location. There are only 3 departments. Instead of writing out these 3 columns for every employee, you could create another table with the department, department head, and department location that you can refer to with a single department id. So instead of 3 columns in the employee table, you now only have one.

70
Q

What are the 3 rules to have a table in first normal form (1NF)?

A
71
Q

What are 3 rules required for a table to be in second normal form (2NF)?

A
72
Q

What are 2 rules required for a table to be in third normal form (3NF)?

A

Note that Annual Salary also depends on Salary, and DeptHead also depends on the DeptName

73
Q

What are DDL Triggers?

A

DDL = Data Defitnition Language

DDL triggers fire in response to CREATE, ALTER, and DROP (Table, Function, Index, Stored Procedure, etc)

Some system stored procedures that perform DDL-like operations can also fire DDL triggers, e.g. sp_rename system stored procedure

74
Q

When would you use a DDL trigger?

A
  1. If you want to execute some code in response to a specific DDL event
  2. To prevent certain changes to your database schema
  3. Audit the changs that the users are making to the the database structure
75
Q

DDL trigger syntax

A

CREATE TRIGGER trigger_name
ON Scope
FOR EventType1, EventType2, EventType3
AS
BEGIN

–Trigger body

END

  • Scope is ALL SERVER or DATABASE
    e. g.

CREATE TRIGGER trMyFirstTrigger
ON Database
FOR CREATE_TABLE
AS
BEGIN

PRINT ‘New table created’

END

76
Q

How to prevent changes to database schema using a DDL trigger?

A

CREATE TRIGGER trigger_name
ON Database
FOR CREATE_TABLE
AS
BEGIN

ROLLBACK
Print ‘You cannot create a table’

END

77
Q

How do you disable a DDL trigger?

A

DISABLE TRIGGER trigger_name ON Scope

Scope is DATABASE or ALL SERVER

78
Q

By default, in which order will DDL triggers fire?

How can you set the order in which DDL triggers fire?

A

Server triggers always before database triggers (cannot change this). By default, triggers fire in the order they were created.

You can use sp_settriggerorder stored procedure to change the trigger order.

SQL 70-761 (3 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions