Q Explain what an indexed view is and why it is beneficial.
A With indexed views, you provide a unique, clustered index for a view. This means that SQL server stores the data in the designated order at the time that you save the view. It updates the index as you add, update, and delete data. Therefore, there is no need for SQL Server to create a result set dynamically each time the view is referenced.
Q Describe the benefits of user-defined functions when working with views.
A User-defined functions enable you to pass parameters to functions as if they were stored procedures, but to work with the results as if they were views.
Q Explain how you can use a view to secure data.
A You first create a view containing the appropriate columns. You then give the users rights to the view. Although they have no rights to the underlying tables, they are able to work with the data displayed in the view.
Q. What happens if you forget to use BEGIN and END with the IF...ELSE...ENDIF construct?
A. If you forget to place a BEGIN...END construct on the line immediately following the IF statement or the ELSE statement, only the first statement after the IF or the ELSE executes.
Q. What does the RETURN statement do?
A. The RETURN statement unconditionally exits a stored procedure without executing any other statements.
Q. Why would you use the CASE statement?
A. You use the CASE statement to compare a result from a SQL statement against a set of simple responses. For example, a CASE statement might evaluate the contents of the ContactTitle field and return an appropriate string based on those contents.
Q. Why would you use the SET NOCOUNT statement?
A. When it is set to ON, SET NOCOUNT eliminates the xx row(s) affected message in the SQL Express Manager window and the DONE_IN_PROC communicated from SQL Server to the client application. This improves the performance of the stored procedure.
Q. The @@ functions are actually variables (true/false).
A. False. You cannot assign values to them or work with them like normal variables. They are instead functions that return information about SQL Server.
Q. Describe the parameters used when a stored procedure is used to insert data.
A. You generally have one input parameter for each field in the underlying table. It is also common to have output parameters that return error and status information, as well as the identity value of the row that the stored procedure inserted.
Q. Why would you create temporary tables?
A. You create temporary tables to share data between procedures or to help you to accomplish complex tasks.
Q. Explain what a trigger is.
A. A trigger is like an event procedure that runs when data changes. You create triggers that respond to inserts, updates, and deletes.
Q. Explain why you use triggers.
A. Developers use triggers to enforce business rules and even to perform tasks such as inserting data into any audit log.
Q. Explain why ADO.NET is superior to its predecessors.
A. ADO.NET is superb at dealing with disparate data formats and scalability in enterprise situations. It provides one programming model for accessing all types of data, no matter in what format it is stored and where it is stored. It utilizes XML to accomplish this task. Finally, ADO.NET is focused on disconnected operations, contributing to its scalability.
Q. Name four methods of the SqlCommand object and explain what they do.
A. The ExecuteReader method returns a forward-only DataReader object. The ExecuteScalar method returns a result of one column and one row. The ExecuteNonQuery method returns no results (you use it, for example, to update data). The ExecuteXLMReader method returns an XML version of a DataReader object.
Q. Explain the benefits of the SqlDataReader object.
A. It provides the fastest means for retrieving read-only sets of data. It supplies a forward-only, read-only, server-side cursor, loading only what it needs into memory.