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