Objective 2.4: Troubleshoot failed object creation.
The first step in troubleshooting failed object creation is to check the syntax of your Transact-SQL statement. If the syntax is correct, check the relationships of the SQL Server objects involved in the operation. Usually the problem lies either in the object relationship or in not providing enough information for SQL Server to fulfill your request. Dropping an object is most commonly interrupted because of the relationship with other objects such as foreign keys or constraints such as NOT NULL. Modifying a column can be interrupted because the change would cause data corruption or truncation. Expanding a table with new columns could be interrupted because you did not supply enough information even though the syntax is correct. A good example of this is adding a new column to a table with the NOT NULL attribute but without including a default value. Troubleshooting failed object creation is much more difficult than creating new objects. You have to look at existing objects and their interactions with changes and new objects.
Objective 2.4 Questions
1. | 70-229.02.04.001 You have an existing Customers table with customers' information. You need to add a column called Privileged with a data type of int. This column cannot have the value of NULL. You execute the following Transact-SQL statement and receive the following error code. How can you modify your statement to execute successfully and achieve the desired result? ALTER TABLE Customers ADD Test INT NOT NULL You cannot modify the statement to achieve the required result without removing all the data from the table, making the changes, and then repopulating the table. If a default is defined, SQL Server will be able to update all existing records to this value, so it will not have to break the NOT NULL constraint. You cannot modify the statement because you cannot add a NOT NULL column to a table. You have to modify the statement as follows: ALTER TABLE Customers ADD Test INT NOT NULL WITH FORCE. You have to define a default value. | |
2. | 70-229.02.04.002 You have modified your Customer table with the following Transact-SQL statement: ALTER TABLE Customers ADD Privileged INT NOT NULL DEFAULT (0). The business requirements have changed and you no longer need the Privileged column. You execute the following statement and receive the following error message. How can you resolve the problem? ALTER TABLE DROP COLUMN Privileged You have to drop the foreign key constraint referencing the Privileged column before you can drop the column itself. You have to drop the default value (DF) before you can drop the column itself. You have to drop the constraint (NOT NULL) before you can drop the column itself. | |
3. | 70-229.02.04.003 You created a view on a table called Testing123 with the following code: CREATE VIEW vTesting123 AS SELECT * FROM Testing123. After a few days you receive e-mails from the users with the following error message: Server: Msg 208, Level 16, State What could cause this error? Someone has modified the Testing123 table and did not rebind it with the view. The view still has the previous compilation and could not bind to the table. Testing123 has been removed or renamed from the database, and the view cannot bind to it because it no longer exists. Access rights have been changed, and users have no longer have access to the Testing123 table, only to the vTesting123 view. |
Answers
1. | D. If you define a default value, SQL Server will be able to add the new column to the table. SQL Server will use this value and update all existing records. This value will also be used when new records are inserted into the table and no value has been defined for the new record. |
2. | C. SQL Server reminded you that you still have a dependency issue before you can remove the column. The NOT NULL clause caused SQL Server to place a constraint on the column when it was created. Dropping the constraint will allow you to drop the column from the table. |
3. | B. Someone has removed or renamed the Testing123 table. As a result, when the view is executed, SQL Server cannot find the table and the execution fails. |