CAD/MCSE/MCDBA Self-Paced Training Kit [Electronic resources]: Microsoft SQL Server 2000 Database Design and Implementation, Second Edition (Exam 70-229)

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 
ERROR CODE: 
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can
 contain nulls or have a DEFAULT definition 
specified. Column 'test' cannot be added to table 'customers'
 because it does not allow nulls and does not specify a 
DEFAULT definition. 

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 
Server: Msg 5074, Level 16, State 1, Line 1 
The object 'DF__customers__privileged__5441852A' 
is dependent on column 'privileged'. 
Server: Msg 4922, Level 16, State 1, Line 1 

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 
1, Procedure vTesting123, Line 1 
Invalid object name 'Testing123'.
Server: Msg 4413, Level 16, State 1, Line 1 
Could not use view or function 
'vTesting123' because of binding errors. 

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.