Database Programming Final Exam

What can be done to avoid deadlocks?

1. Use your objects in the same order; 2. Keep your transactions as short as possible and in one batch; 3. Use the lowest transaction isolation level necessary; Do not allow open-ended interruptions within the same transaction

Returns the error number of the last T-SQL statement executed on the current connection. Returns 0 if no error.

@@ERROR

Returns the number of rows affected by the last statement.

@@ROWCOUNT

What is a correlated subquery?

A subquery where the inner query runs on information provided by the outer query and vise versa.

What is the biggest difference between using ALTER PROC and just performing a DROP PROC and then a CREATE PROC?

ALTER PROC retains any permissions that have been established for the PROC

When does a FOR trigger run?

After constraints are checked it can also be declared with the AFTER keyword.

By default members of ___________ can create objects in the database.

All of the above

Why is varchar not the best data type for a two character state code attribute?

All of the values are the same size and the max size (two) is small, the overhead associated with varchar is not justified.

__________ can exist within the context of an element.

Attributes

The __________ is the point to which all database activity will be rolled back if for some reason we do not want to commit the transaction.

BEGIN TRAN

When does the INSTEAD of trigger run?

Before constraints are checked, you actually run your code in place of whatever the user requested

What constraint can be used to limit the allowable values that can be entered into a column (attribute)?

CHECK

From a read perspective what is more efficient a clustered index or a non-clustered index?

CLUSTERED

If you want to select a particular case sensitivity for a database you do this via the ____________.

COLLATE

At the point you issue the ____________ the transaction is considered to be what is called durable. That is the effect of the transaction is now permanent.

COMMIT TRAN

A _________ is a restriction. Placed at either column or table level, a ____________ensures that your database meets certain data integrity rules.

CONSTRAINT

What is a better alternative from a performance perspective a TRIGGER or a CONSTRAINT?

CONSTRAINT

What event can you attach triggers to?

CREATE, ALTER, DROP

What is a common use of triggers?

Can be used to enforce Data Integrity Rules, Check the Delta of an Update, Custom Error Messages, Updating summary information, Feeding denormalized tables for reporting, and Setting condition flags.

What must you do if you wish to leave the object owner (schema) off, but you wish to provide the databaseor server name when specifying a table in a query?

Code an extra "."

What is contained in the database log file?

Committed transactions

Within a XML document ________ define clear start and end points for your descriptive information.

ELEMENTS

An issue with the EXEC is ________________

EXEC cannot be used inside a User Defined Function. The calling code can't reference variables inside the EXEC statement

When the number of locks being maintained reaches a certain threshold, then SQL Server 2012 _____________.

Escalates the lock to the next highest level

The _____________ clause can be used with an SQL Select statement to request the results be returned in XML format.

FOR XML

A trigger is never implicitly part of the same transaction as the statement that caused the trigger to fire.

False

Forgetting to include the OUTPUT keyword when executing a proc with an OUTPUT variable will create a run time error (you will get an error message).

False

Generally speaking stored procedures are bad for system performance.

False

If a computed column refers to a non-deterministic function, an index will be allowed on that column.

False

Only one batch can exist in a script.

False

________ is a reason for using views.

Filtering (rows and columns), Protecting sensitive data, Reducing complexity, To support additional triggers

Assuming there is a variable named @myvar how should it be tested to see if it contains nulls

IF @myvar IS NULL

What does the datetime data type provide that the smalldatetime does not?

IT provides for an expanded data range (say more than 50 years back and 100 years ahead) and it provides for storingfractional seconds

What is the impact of creating a view WITH SCHEMABINDING?

It prevents someone from making alterations to objects that the view is dependent upon unless they drop the schema-bound view first

What happens if you leave the schema (ownership) off when specifying a table name in a query?

It will be resolved first using the current user's default schema, and then, if the object name in questiondoes not exist within the current user's default schema, it will look in the databases default schema (dbo).

If you issue a DROP will SQL server, ask "Are you sure?"

NO

What are the four referential integrity actions that can be specified with ON UPDATE or ON DELETE?

NO ACTION, CASCADE, SET TO NULL, SET DEFAULT

When you declare a variable the value of the variable is __________ until you explicitly set it to some other value.

NULL

What issolation level is the default?

READ COMMITTED

With _____________ if your transaction performs an UPDATE, DELETE, or INSERT the locks acquired for those statements will be held until the transaction ends.

READ COMMITTED

If a transaction is involved in producing a report where the numbers do not have to be 100% accurate theisolation level can be set to ______________ and thereby avoid delays caused by acquiring and waiting on locks.

READ UNCOMMITTED

The ___________ statement can be used take you back to the beginning of a transaction or to a save pointwithin the transaction.

ROLLBACK

What type of column is good to ensure that data on a distributed table remains uniquely identified?

ROWGUIDCOL

If you do not supply a default value for a stored procedure parameter it is __________.

Required

How do we change the value in a variable?

SELECT or SET

Using an Isolation level of _________________ provides the highest level of consistency, but the lowestlevel of concurency.

SERIALIZABLE

An important thing to remember about _________ is that once a ROLLBACK is issued they are cleared.

Save points

User defined functions can return __________________

Scalar value or Tables

What are the levels of table naming conventions in SQL Server 2012?

Server Name, Database Name and Schema Name

Concerning using a trigger to enforce data integrity rules verses using a check constraint if both can perform the desired action which would be the best route from a performance perspective?

The check constraint

When you create a new databse if you do not specify a SIZE how big will the new database initially be?

The same size as the model database

If you do not specify a MAXSIZE, when defining a database what will happen?

There is considered to be no maximum. The practical maximum becomes when your disk is full.

Why is it often a good idea to put a USE statement at the top of a script file?

To affect the value that will be used as the database name when we are making use of the default value for the database portion of an object name

Why is an IN operator often used with nested subqueries?

To deal with the possibility that the nested query will return a set of values not just one value

What is a domain or lookup table used for?

To provide a limited list of acceptable values.

What is the purpose of the GO statement?

To seperate a script into multiple batches

A FOREIGN KEY can be temporarily deactivated.

True

A GO statement causes all statements since the beginning of the script or the last GO statement (whichever is closer) to be compiled into one execution plan and sent to the server independently of any other batches.

True

A batch is a grouping of T-SQL statements into one logical unit.

True

A trigger inherits the locks already open on the transaction they are part of.

True

All of the statements within a batch are combined into one execution plan, so all of the statements in a batch must pass a validation of the syntax or none of the statements will execute.

True

By default, only users who are members of the sysadmin server level role, or the db_owner or db_ddladmin database roles, can create objects in a database.

True

From outside the stored procedure parameters can be passed in either by position or by reference.

True

If you are using an OUTPUT variable to pass non-record set information out of a proc the OUTPUT keyword must be used in the proc when declaring the parameter and also when calling the proc.

True

It is best to use SET for simple variable assignment that does not involve pulling data from a table.

True

Stored Procedures can be a security tool in that they can return a record set without the user having to have authority to the underlying tables used by the stored procedure.

True

The ALTER statement can be used to change database parameters including SIZE, MAXSIZE, FILEGROWTH.

True

The Database, Table, Extent, Page, Key, Row or Row Identifier (RID) are a lockable resource.

True

The FLOOR function takes the value supplied and rounds down to the nearest integer.

True

User defined functions can be Deterministic or non Deterministic.

True

With small datasets there is a very high possibility that SQL Server will totally ignore your index in favor of the first execution plan that it comes upon.

True

You want to use SELECT when you are basing the assignment of your variable on a query.

True

In an Update trigger, it is possible to check to see if the column(s) we are interested in have been changed by using the _________________ or ______________________.

UPDATE(), COLUMNS_UPDATED()

A ______ is composed of a stored query and in most respects will function like a table.

View

if our stored procedure contains dynamic elements (IF statements that control various selects executing, queries built using the EXEC command, etc) system performance is likely to benefit from using the ___________ option within our stored procedure.

WITH RECOMPILE

Within SQL Server 2012 Management Studio you can __________.

Write Queries, Create a Database, Create a Table, and Create a Database Diagram

_____ is a markup language, and looks a lot like HTML to the untrained eye.

XML

Can a CHECK constraint be added to an existing table without fixing data on the table that is in violationof the new constraint?

YES

Can adding an index to a table hurt the performance of insert operations?

YES

Can you join data across databases?

YES

Can adding an index to a table improve a queries performance?

Yes

How do you decide if a foreign key should be defined as NOT NULL or NULL?

You evaluate the relationship that it establishes, if the record with the FOREIGN KEY must berelated to a record on the other table you should define the FOREIGN KEY as NOT NULL.

What is a DDL trigger?

a trigger that fires in response to someone changing the structure of a database (CREATE, ALTER, DROP)

With ALTER PROC you ____________

completely replace the existing stored procedure.

With ALTER VIEW you ____________

completely replaces the view (like dropping and recreating the view). but its still has the same permissions and dependency information

When the actual owner of the database creates a table called explore in the databasethe schema (owner) qualified table name will be _____________.

dbo.explore

In order for a function to be considered deterministic it must _________________

if it always returns the same result set when it's called with the same set of input values.

By default the length of one transaction is______________.

one statement

What type of parameter would you declare if you wanted to use it to pass a value back to the code that calls the stored procedure?

output

Assuming a view has been created without use of WITH ENCRYPTION what are two ways of seeing the actual view definition?

sp_helptext and the syscomments system table

How can you make a stored procedures parameter optional?

supply a default value

A user defined function is very similar to a stored procedure, but a big difference is ________________

the return value coming out of a user defined function is much more robust.

A _____________ is all about atomicity. It is the smallest grouping of one or more statements that should be considered to be all or nothing.

transaction