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

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

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

Microsoft Corporation

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
توضیحات
افزودن یادداشت جدید


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 
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.




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.


/ 223