Programmable Objects Flashcards
How do you declare and instantiate a variable in T-SQL?
DECLARE @ AS
How is a variable set using T-SQL?
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’);
Define batch.
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.
What is used to signal the end of a batch?
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.
True or False: A variable is not local to the batch in which it is defined.
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.
List statements which require their own batch
REATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW.
True or False: One best practice you can follow to avoid such problems is to separate DDL and DML statements into different batches.
True
What is a Flow Element?
Flow elements control the flow of your code.
What are the main flow elements T-SQL provides?
IF, ELSE, WHILE
How are the boundaries of a statement block defined?
BEGIN marks the start of the statement block, and END; marks the end of the block.
True or False: If you need to run more than one statement in the IF or ELSE sections, a statement block is not required.
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;
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.
False
When the predicate is FALSE or UNKNOWN, the loop terminates.
What is the name of the built-in looping element provided by T-SQL?
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;
T-SQL supports the BREAK and CONTINUE statements
True
How is a cursor defined in the context of T-SQL?
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.
Reasons why the cursor object should be avoided if possible.
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).
List the major steps involved when working with a cursor:
- (optional) Declare variables if any modified or returned based off of operations involving the cursor.
- Declare the cursor based on a query.
- Open the cursor.
- Fetch attribute values from the first cursor record into variables.
- Iterate though cursor records while @@FETCH_STATUS = 0
- Close the cursor
- Deallocate the cursor.
SQL Server supports three types of temporary tables, name them.
- local temporary tables
- global temporary tables
- table variables
How is a local temporary table created?
A local temporary table is created by naming it with a pound sign as a prefix, such as #T1.
What are the types of Routines which SQL Server supports?
user-defined functions, stored procedures, and triggers.
Parts of creating a function
- DROP IF EXISTS
- CREATE FUNCTION ( @parameter(s) )
- RETURNS
- AS
- end with GO
What does the following statement accomplish?
SET NOCOUNT ON
This command is used to suppress messages indicating how many rows were affected by DML statements.
What is a trigger?
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.
SQL Server supports the associate of triggers with two kinds of events, name these events.
DML events such as an INSERT, UPDATE, or DELETE statement; and DDL events such as CREATE TABLE
True or False: A trigger is considered part of the transaction that includes the event that caused the trigger to fire.
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.
SQL Server supports two types of DML triggers, name them.
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.
What are the components of creating a new trigger?
CREATE TRIGGER FOR AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO dbo.T1_Audit(keycol, datacol)
SELECT keycol, datacol FROM inserted;
GO
