Programmable Objects

Question 1

How do you declare and instantiate a variable in T-SQL?

Answer 1

DECLARE @ AS

Question 2

How is a variable set using T-SQL?

Answer 2

There are many ways to set a variable, it can be set when it is declared, it can be set using the SET statement after a variable is declared, or multiple variables can be set using the SELECT statement (this option has a larger potential for errors.

Example:

DECLARE @i AS INT = 10;

DECLARE @b AS INT;
SET @b = 10;
SET @B = (SELECT empid FROM HR.Employees WHERE firstname = ‘Josh’ AND lastname = ‘Karr’);

Question 3

Define batch.

Answer 3

A batch is one or more T-SQL statements sent by a client application to SQL Server for execution as a single unit.

Additionally, a batch is a set of commands that are parsed and executed as a unit.

If the parsing is successful, SQL Server then attempts to execute the batch.

In the event of a syntax error in the batch, the whole batch is not submitted to SQL Server for execution.

Question 4

What is used to signal the end of a batch?

Answer 4

SSMS provides a client tool command called GO that signals the end of a batch.

Note that the GO command is a client tool command and not a T-SQL Server command.

Question 5

True or False: A variable is not local to the batch in which it is defined.

Answer 5

False

If you refer to a variable that was defined in another batch, you’ll get an error saying that the variable was not defined.

Question 6

List statements which require their own batch

Answer 6

REATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW.

Question 7

True or False: One best practice you can follow to avoid such problems is to separate DDL and DML statements into different batches.

Answer 7

True

Question 8

What is a Flow Element?

Answer 8

Flow elements control the flow of your code.

Question 9

What are the main flow elements T-SQL provides?

Answer 9

IF, ELSE, WHILE

Question 10

How are the boundaries of a statement block defined?

Answer 10

BEGIN marks the start of the statement block, and END; marks the end of the block.

Question 11

True or False: If you need to run more than one statement in the IF or ELSE sections, a statement block is not required.

Answer 11

False

Multiple IF/ELSE statements require a statement block, same as any OOP language.

Example:

IF DAY(SYSDATETIME()) = 1
BEGIN
PRINT ‘Today is the first day of the month.’;
PRINT ‘Starting first-of-month-day process.’;
/* … process code goes here … */
PRINT ‘Finished first-of-month-day database process.’;
END;

Question 12

True or False: A WHILE statement will continue to execute if the statement evaluates to UNKNOWN, and only terminates if the statement evaluates to FALSE.

Answer 12

False

When the predicate is FALSE or UNKNOWN, the loop terminates.

Question 13

What is the name of the built-in looping element provided by T-SQL?

Answer 13

Trick question. There is no element provided; however, it’s easy to mimic such an element with a WHILE loop and a variable.

EXAMPLE:

DECLARE @i AS INT = 1;
WHILE @i <= 10
BEGIN
  PRINT @i;
  SET @i = @i + 1;
END;

Question 14

T-SQL supports the BREAK and CONTINUE statements

Answer 14

True

Question 15

How is a cursor defined in the context of T-SQL?

Answer 15

A cursor is a non-relational result with order guaranteed among rows.

It is also an object which can be used to process rows from a result of a query one at a time and in a requested order.

Question 16

Reasons why the cursor object should be avoided if possible.

Answer 16

First and foremost, when you use cursors you pretty much go against the relational model, which is based on set theory.

Given a set-based query and cursor code that do similar physical processing behind the scenes, the cursor code is usually many times slower than the set-based code.

With cursors, you write imperative solutions—in other words, you’re responsible for defining how to process the data (declaring the cursor, opening it, looping through the cursor records, closing the cursor, and deallocating the cursor).

Question 17

List the major steps involved when working with a cursor:

Answer 17

0. (optional) Declare variables if any modified or returned based off of operations involving the cursor.
1. Declare the cursor based on a query.
2. Open the cursor.
3. Fetch attribute values from the first cursor record into variables.
4. Iterate though cursor records while @@FETCH_STATUS = 0
5. Close the cursor
6. Deallocate the cursor.

Question 18

SQL Server supports three types of temporary tables, name them.

Answer 18

1. local temporary tables
2. global temporary tables
3. table variables

Question 19

How is a local temporary table created?

Answer 19

A local temporary table is created by naming it with a pound sign as a prefix, such as #T1.

Question 20

What are the types of Routines which SQL Server supports?

Answer 20

user-defined functions, stored procedures, and triggers.

Question 21

Parts of creating a function

Answer 21

1. DROP IF EXISTS
2. CREATE FUNCTION ( @parameter(s) )
3. RETURNS
4. AS
5. end with GO

Question 22

What does the following statement accomplish?

SET NOCOUNT ON

Answer 22

This command is used to suppress messages indicating how many rows were affected by DML statements.

Question 23

What is a trigger?

Answer 23

A trigger is a special kind of stored procedure which cannot be executed explicitly; rather, it’s attached to an event. Whenever the event takes place, the trigger fires and the trigger’s code runs.

Question 24

SQL Server supports the associate of triggers with two kinds of events, name these events.

Answer 24

DML events such as an INSERT, UPDATE, or DELETE statement; and DDL events such as CREATE TABLE

Question 25

True or False: A trigger is considered part of the transaction that includes the event that caused the trigger to fire.

Answer 25

True

Issuing a ROLLBACK TRAN command within the trigger’s code causes a rollback of all changes that took place in the trigger, and also of all changes t hat took place in the transaction associated with the trigger.

Question 26

SQL Server supports two types of DML triggers, name them.

Answer 26

after, instead of

An after trigger fires after the event it’s associated with finishes and can be defined only on permanent tables.

An instead of trigger fires instead of the event it’s associated with and can be defined on permanent tables and views.

Question 27

What are the components of creating a new trigger?

Answer 27

CREATE TRIGGER FOR AFTER INSERT
AS
SET NOCOUNT ON;

INSERT INTO dbo.T1_Audit(keycol, datacol)
SELECT keycol, datacol FROM inserted;
GO