Term
| What are three things you cannot do with the ALTER TABLE statment? |
|
Definition
1) Change a column name.
2) Add an identity property.
3) Remove an identity property. |
|
|
Term
| True or False: You want to change a constraint or computed column on a table. The ALTER TABLE statement will work for this. |
|
Definition
| False! You need to DROP / ADD COLUMN/CONSTRAINT for these special cases. |
|
|
Term
| If you see a test question that shows two IDENTITY columns in a CREATE TABLE statement, what does this tell you? |
|
Definition
| That the answer is wrong! You can't have multiple IDENTITY columns on a table. |
|
|
Term
| What is the command you need to run to change a table's schema? |
|
Definition
| ALTER SCHEMA TRANSFER .tablename; |
|
|
Term
| What does the PERSISTED option on a computed column do? |
|
Definition
| This does not calculate the column on the fly, but stores the value of the computed column in the database. |
|
|
Term
| What is a deterministic function? Can you set PERSISTED onto a computed column using deterministic functions? |
|
Definition
A deterministic function will ALWAYS return the same result given the same set of inputs. A non-deterministic will not even if it has the same inputs . (E.g. GETDATE() will always change in a function)
Yes, you must make sure a PERSISTED computed column uses deterministic functions. |
|
|
Term
| What does the IDENTITY_INSERT property of a table do? |
|
Definition
This allows you to explicitly INSERT IDENTITY values into a table that has an IDENTITY column.
(E.g. I tell SQL Server to insert '1048576' as the value for my IDENTITY field) |
|
|
Term
| You try to run an ALTER TABLE ADD CONSTRAINT PK_PriKey PRIMARY KEY(column) on a column that has NULL values in it. What happens? |
|
Definition
| The command fails. NULLs are not valid with a primary key constraint. |
|
|
Term
| True or false: Multiple columns can be part of a primary key on a table. |
|
Definition
|
|
Term
| True or false - the UNIQUE constraint on a table allows a NULL value to exist. |
|
Definition
|
|
Term
| What will happen if you try to make a PRIMARY KEY constraint with the name PK_ID in database schemas 'src' and 'bak'? |
|
Definition
| The command will succeed because the PK has the same name in different schemas. If you were trying to set two PK's with the same name in the same schema, you'd be boned. |
|
|
Term
| What is the ALTER TABLE statement to add a FOREIGN KEY constraint on a column? |
|
Definition
| ALTER TABLE
ADD CONSTRAINT FK_ForeignKey FOREIGN KEY()
REFERENCES () |
|
|
Term
| If you use the WITH CHECK option during an alter table statement, what does the DBMS do? |
|
Definition
| This tells the DBMS to check if your new constraint violates the new constraint's rules. For instance, if a new FOREIGN KEY doesn't actually exist in the foreign table. |
|
|
Term
| When defining a FOREIGN KEY, what other constraints are the foreign column(s) required to have? |
|
Definition
| Foreign key columns in the original table must either be unique or the primary key. |
|
|
Term
| You are creating a FOREIGN KEY constraint on a table. One column has a data type of INT and the other has a data type of DECIMAL. What is the result of the command? |
|
Definition
| Fails. Foreign keys must be the same data type and collation. |
|
|
Term
| What's a tip to improve join performance on FOREIGN KEY columns? |
|
Definition
| You can add indexes on the referencing table's foreign key column. (E.g. ProductCategoryID on the 60m record OrderDetails table should increase performance) |
|
|
Term
You want to make sure that the UnitPrice column in your ProductPricing table is never negative. What is the command you use to implement this?
Bonus: How are NULL values handled by this command? |
|
Definition
ALTER TABLE ProductPricing WITH CHECK
ADD CONSTRAINT CHK_NegativePricing CHECK (UnitPrice >= 0);
Bonus: NULL values could return true for the above CHECK. You might want to add an ISNULL(UnitPrice,0) function to the CHECK statement for this reason. |
|
|
Term
| Can a CHECK constraint be used to ensure an UPDATE statement went according to plan? |
|
Definition
| No! You must use a trigger instead. |
|
|
Term
| True or false: you can customize the error that is returned by SQL server if a CHECK constraint is violated. |
|
Definition
| False! Triggers allow this, though. |
|
|
Term
| When working with views, what does the SCHEMABINDING option indicate? |
|
Definition
| SCHEMABINDING guarantees that the underlying table structure of a view cannot be changed without dropping the view, first. |
|
|
Term
| When using the CREATE VIEW statement, can you have other commands before it in a SQL batch? Can you conditionally CREATE VIEW with an IF statement? |
|
Definition
| No to both - CREATE VIEW must be used on its own in a batch. |
|
|
Term
| When creating views, what does the WITH ENCRYPTION option do? How strong is the encryption? |
|
Definition
| WITH ENCRYPTION simply obfuscates the SELECT code that makes up a view. This is not strong encryption, it's just code obfuscation. |
|
|
Term
| What does the WITH VIEW_METADATA option with the CREATE VIEW statement do? |
|
Definition
| When a query against the view is executed, the metadata defined by the view (data types and column names) will be returned to the querying client. Otherwise, the metadata from the base table(s) will be returned. |
|
|
Term
| What does the WITH CHECK OPTION do when creating a view? Is it important? |
|
Definition
| This prevents you from updating/deleting data outside of a view's WHERE filter. This is VERY important because if a VIEW is only meant to return records for EmployeeID 123, you could still UPDATE records for other employees if the WITH CHECK OPTION is not specified. |
|
|
Term
| What does the ORDER BY statement do in a CREATE VIEW command? |
|
Definition
| TRICK QUESTION!! You can't specify an ORDER BY condition in a view's definition (or CTE)! |
|
|
Term
| Can you pass parameters to a view? |
|
Definition
| No! You must create a table-valued function to handle this scenario. |
|
|
Term
| You have a temporary table named #Store_Me_Somewhere. You know this temporary table will exist when running a stored procedure. Can you create a view that references the temp table? |
|
Definition
| Nope! Views require permanent tables in their SELECT commands. |
|
|
Term
| You create a VIEW with an 'OFFSET 10 FETCH NEXT 10 ROWS ORDER BY FirstNm' clause. Will your view be ordered when you query it? |
|
Definition
| No! Views can never be ordered. You must add an ORDER BY statement whenever querying a view. |
|
|
Term
| When you issue an ALTER VIEW statement, what happens to the view? |
|
Definition
| This statement reissues the entire view definition - you aren't just modifying an existing piece of code. |
|
|
Term
| You can run INSERTS/UPDATES/DELETES on views. What happens if you run one of these statements on a view that uses multiple tables in its definition? |
|
Definition
| The statement will fail - a DML statement must be run against a single table. |
|
|
Term
| You are running an UPDATE statement on a view - one of the columns in the view is an expression adding two columns together in the base table. What happens when you execute the UPDATE? |
|
Definition
| The statement fails - you can only change columns that are not from an expression or aggregate function. |
|
|
Term
| You are creating a view with an DISTINCT and OFFSET clause in it. When you want to run UPDATEs on the view in the future, what do you need to keep in mind? |
|
Definition
| TRICK QUESTION! You can't UPDATE a view that uses groupings like DISTINCT or has OFFSETS in it. |
|
|
Term
| What is the best option to use when you want to UPDATE a view? |
|
Definition
| The best way to do this is to use an INSTEAD OF trigger on the view when something happens to it. |
|
|
Term
| What is a partitioned view? |
|
Definition
| This is a view that combined multiple large tables together using UNION statements. This is a manual method of partitioning tables if you don't have a higher level version of SQL Server Enterprise. |
|
|
Term
| What T-SQL command can be used to show metadata about views? |
|
Definition
|
|
Term
| What is an indexed view? What makes it different from regular views? |
|
Definition
| An indexed view uses a unique clustered index that stores more than the view definition in the database. It stores actual results of the view (according to the index) in the database. |
|
|
Term
| What is a SYNONYM in the context of SQL Server? |
|
Definition
| This is an abstraction layer that allows users to query a simple name rather than a 'complex' one that refers to something else. |
|
|
Term
| What types of items can a SYNONYM refer to? |
|
Definition
| Tables, Views, User defined functions, Stored Procedures, table-valued, scalar, and aggregate functions. |
|
|
Term
| Can you create a SYNONYM that refers to a non-existent object? |
|
Definition
| Yes, SQL Server checks to see that the synonym resolves at runtime. |
|
|
Term
| Can a synonym refer to another synonym? |
|
Definition
| No, synonym chasing isn't allowed. |
|
|
Term
| You want to change the SYNONYM 'Derp' to refer to dbo.Derp instead of lkp.Derp - how do you do this? |
|
Definition
| You must DROP and CREATE the SYNONYM in order to change it. |
|
|
Term
| True or False: Synonyms can be names the same thing as the object they map to. |
|
Definition
|
|
Term
| What does the SET NOCOUNT option in a stored procedure do? What benefit does it have? |
|
Definition
| This tells SQL Server not to return the row count of the executed statement to the client. This can impact performance if calling a very commonly used stored procedure. |
|
|
Term
| What does the RETURN statement in a stored proc do? How would it affect when a stored procedure exits? |
|
Definition
| Causes a stored procedure to exit when called. Otherwise, the stored procedure will exit at the end of a T-SQL batch. |
|
|
Term
| What should you do to identify errors that happen in a stored proc? |
|
Definition
| You should use the @@ERROR or ERROR_NUMBER() in a TRY/CATCH block in the stored Proc to return what went wrong. |
|
|
Term
| Why do you want to use the EXEC command when executing a stored proc? |
|
Definition
| Stored procs must be the first statement in a batch if you don't use the EXEC command. |
|
|
Term
| You execute a stored procedure called Sales.GetSalesCountry - this proc needs three input parameters: @Country @Date @Location. What is the proper syntax to use? Does the location of the parameters in your statement matter? |
|
Definition
| EXEC Sales.GetSalesCountry @Date = 2011-01-12, @Location= 'Parthanon', @Country = 'Greece'; When you call these parameters specifically, it does not matter what order you put them in when executing the proc. |
|
|
Term
| What does the OUTPUT keyword when placed in a stored procedure? Does this behavior always work when calling the proc? |
|
Definition
The OUTPUT keyword tells SQL Server that you want to return an internal proc variable to the caller via another variable.
For example:
EXEC TestProcOutput @UserID = 12, @rowcount = @rowsreturned OUTPUT;
SELECT @rowsreturned;
And yes, you MUST put the OUTPUT keyword in BOTH the proc and the EXEC SQL statement. |
|
|
Term
| When using an IF/ELSE statement in a T-SQL batch, what pair of commands should you place next to each control flow operator? Why is this? |
|
Definition
You should put BEGIN and END statements that apply to both the IF and ELSE statements. You should do this because otherwise SQL Server will only process one statement after the IF / ELSE executes instead of the whole batch.
Example:
IF @var1 = @var2
BEGIN
PRINT 'They match!'
PRINT 'Hooray!'
END
ELSE
BEGIN
PRINT 'No matching here :('
PRINT 'But I will still execute both of these statements!'
END |
|
|
Term
| What is critical to include in a WHILE loop? |
|
Definition
| Something that will eventually terminate the loop! |
|
|
Term
| You have a stored procedure with an OUTPUT parameter. You attempt to pass a value into this parameter and feed it to the proc. Does this action succeed? |
|
Definition
| Yes, you can both pass data into an OUTPUT parameter in a proc and also return data from it. |
|
|
Term
| What two types of triggers are there? What types of objects can they be applied to? |
|
Definition
| There are AFTER and INSTEAD OF triggers. The AFTER trigger can only be placed on permanent tables. The INSTEAD OF trigger can be placed on both permanent tables and views. |
|
|
Term
| When a trigger is executed by a DML statement firing, what scope is the trigger in regarding transactions? |
|
Definition
| Triggers participate in the same transaction as the DML statement that made them fire. Meaning, if you roll back a transaction in a trigger, it will roll back EVERYTHING, including the DML statement that fired it. |
|
|
Term
| What is a better option to use than ROLLBACK TRAN inside of a trigger? |
|
Definition
| THROW or RAISERROR are the best options as they let you decide what should happen if a trigger fails. |
|
|
Term
In the following statement, what type of trigger will be created?
CREATE TRIGGER TriggerName
ON TableName
FOR DELETE, INSERT, UPDATE
AS
BEGIN
SELECT 1
END |
|
Definition
| An AFTER trigger is the default when using the FOR statement. Replace FOR with AFTER or INSTEAD OF to verbosely specify the trigger. |
|
|
Term
| Say a trigger fires after an UPDATE statement. This statement affected no rows. What could have been done to keep this trigger from using server resources when the DML statement didn't affect anything? |
|
Definition
| Run an @@ROWCOUNT test on the first line of the trigger to see how many lines the previous statement affected. If it was more than 0, the trigger can proceed. |
|
|
Term
| Can you return result sets from triggers? |
|
Definition
| Yes, BUT... This is not a good idea. They are unreliable and the feature will be disallowed on future versions of SQL Server. |
|
|
Term
| If Table A has an AFTER trigger that runs DML on Table B that has an AFTER trigger, does Table B's trigger fire? |
|
Definition
| If the sp_configure 'nested triggers' option is enabled, yes. Only 32 nested triggers are allowed to execute. |
|
|
Term
| After executing a DML statement, what is contained in the INSERTED and DELETED tables? |
|
Definition
| UPDATE data will be placed in both the INSERTED and DELETED tables, INSERT data will only appear in the INSERTED table, and DELETE data will only appear in the DELETED table. |
|
|
Term
| What is a user defined function not allowed to do? |
|
Definition
| UDF's cannot run DDL statements - they cannot create or alter tables/indexes. |
|
|
Term
| What is the difference between a scalar UDF and a table-valued UDF? What two types of table valued functions are there? |
|
Definition
A scalar UDF only returns a single result when called, a Table Valued Function returns a table.
There are two types of table valued functions: IF = Inline Table Valued Function; TF = Table Valued Function. An IF function is only one SQL statement. |
|
|
Term
| How do you query a table valued function? |
|
Definition
| You can put these in the FROM clause of a SQL statement. |
|
|
Term
| What is the difference between an IF and TF table valued function? |
|
Definition
An IF table valued function is the only UDF that does not require the BEGIN/END block.
Also, while an IF function is a single SQL statement that returns a result set, a TF requires that you define a table variable and insert data into that variable to return data. |
|
|
Term
| Can a UDF call a stored procedure? |
|
Definition
|
|
Term
| In XML, what is attribute centric presentation vs. element centric presentation? |
|
Definition
Attribute centric encloses attributes of elements in quotations:
Element centric presentation is as follows:
|
|
|
Term
| What is an XML namespace? How is it used? |
|
Definition
| XML namespaces are declared at the root element of an XML document: |
|
|
Term
| How can you establish a predetermined set of metadata for a series of XML documents? |
|
Definition
| You can use an 'XSD' document, called an 'XML Schema Description' document. |
|
|
Term
When you query SQL Server and specify to return XML RAW data, how is a result set returned?
Can you modify the output of XML RAW to appear differently? |
|
Definition
The result set is similar to what you see in a result set grid; each row is its own element, and each column is an attribute:
|
Yes, you can modify what XML RAW produces by using the ELEMENTS operator:
FOR XML RAW, ELEMENTS
This returns:
1NRZBB12345 |
|
|
Term
| Why is the ORDER BY clause important for an XML query in SQL Server? |
|
Definition
| The ORDER BY clause determines what order the XML elements and attributes show up in the query result. Without an ORDER BY clause, your XML will be random and not comform to a standard XML format. |
|
|
Term
| What is the difference between using FOR XML RAW and FOR XML AUTO? |
|
Definition
XML RAW returns one element with attributes (columns) of that row in that element tag. XML AUTO creates nested elements in your result set.
Example of XML RAW:
|
Example of XML AUTO:
|
|
|
Term
| How do you tell SQL Server to include an XML Namespace in your XML output using FOR XML AUTO or FOR XML RAW? |
|
Definition
Use the WITH XMLNAMESPACES('' AS ex) operator before the SELECT statement. This will add the namespace you want into the generated XML.
If you want to use your namespace in your query, change your table and column aliases to use the namespace like so:
WITH XMLNAMESPACES('CustomerOrders' AS co)
SELECT [co:Customers].customerName AS [co:CustomerName]
FROM Customers [co:Customers] |
|
|
Term
| How can you tell SQL Server to return the XSD (XML Schema) with a result set? Does your FOR XML option need to be set to something specific? |
|
Definition
| You use the XMLSCHEMA('namespace') command in the FOR XML option after your query. This only works if you are using FOR XML AUTO or FOR XML RAW |
|
|
Term
| How can you return an XML Schema using FOR XML AUTO/RAW without returning any data? |
|
Definition
| Specify your query with the FOR XML AUTO/RAW, XMLSCHEMA('namespace') and make the WHERE clause of your query not match any record (e.g. WHERE 1 = 34) |
|
|
Term
| What is the correct process to shred an XML file into something you can query? |
|
Definition
| You must use a combination of the OPENXML() function and the sys.sp_xml_prepareddocument stored procedure. |
|
|
Term
What is the syntax to take a prepared XML document and query it?
How do you tell if this query uses an attribute centric or element centric way of reading the XML? |
|
Definition
| EXEC sys.sp_xml_preparedocument @DocumentReference OUTPUT, ;
SELECT *
FROM OPENXML(@DocumentReference, '/XMLNode/Element',2)
WITH(
,
)
The OPENXML function integer value in the parameters tells the function if the XML is element or attribute based (or both). 1 = attribute based, 2 = element based, 8 = both. |
|
|
Term
| True or false, you can only have one XML file or variable loaded for shredding. |
|
Definition
| True - you must 'open' the XML you wish to shred using the sys.sp_xml_preparedocument @DocumentReference and then close it using sys.sp_xml_removedocument @DocumentReference after you are done shredding. |
|
|
