| 1. | A, C, D. The purpose of the UPDATE command is exactly what you want to avoid. You should be able to increase the data storage size and alter a column name without affecting the internal data. However, a decrease in the data storage size results in data truncation or loss. INSERT, used appropriately, adds data but does not alter any existing values. For more information, see the section "Inserting Data." | | 2. | A, B, D. You want to exclude sales records from all but the last year before the grouping. This requires a WHERE clause, not HAVING. A HAVING condition would be needed if you were limiting to a total value of sales last year. BETWEEN would be used for the date range condition. There is no multiple-table scenario that we know of that would need a JOIN. There is also no mention or need for checking for NULL values. | | 3. | D. Processing a random sample of records is exactly what TABLESAMPLE accomplishes. The answer for previous SQL Server versions may have been B, as using the TOP clause to get 1,000 records would sort of accomplish the goal. Answers A, B, and C would not give you truly random selection, although they would be able to limit the output to 1,000 records. RND is a random number generator and would not accomplish random selection of records. For more information, see the section "Querying a Sampling of the Data Stored." | | 4. | A, B. The best solution would involve creating a new NOT NULL column because this is a mandatory data element. You cannot create such a column in a table that has existing data without supplying a value for the field. N/A would supply that value until a correct one can be entered. NEWID() would generate a value too large to go into the field, and the value would be meaningless. Using an identity column would not be a valid way to initialize a serial number field. | | 5. | A, B, E, F, G. A random number generator will produce duplicates on a regular basis and will not always be unique. The CLR routine could work if it also has some method of checking uniqueness. Every other option produces unique content or does not allow duplicates. | | 6. | D. The purpose of the CATCH block is to isolate error handling. Within the CATCH, code is placed that reacts to errors that may have occurred while executing the code in the TRY block. Normally, this reaction would be a rollback of anything that had occurred. For more information, see the section "Using the CLR Within Stored Procedures." | | 7. | C. Because there is no data access in the process, a cursor is unnecessary. Any process with a set of complex mathematical operations to perform will probably operate more efficiently by using a CLR procedure. For more information, see the section "Using the CLR Within Stored Procedures." |
|
