Question 1.
How many parameters can be returned from a stored procedure?

A. 256
B. 512
C. 1024
D. 2048

Answer: D

Question 2.
Table 't' and trigger 'trin'are created as follows:
create table t (a int)
go
create trigger trig on t for insert, update, delete
as
begin
if @@rowcount > 1
rollback tran
go
end
The following transaction is now executed:
begin tran
insert t values (1)
insert t values (2)
insert t select* from t
commit
go

After completing this transaction, how many row will table 't' contain?

A. 0
B. 1
C. 2
D. 3
E. 4
F. 5

Answer: A

Question 3.
What does the statement ROLLBACK TRIGGER do?

A. undo only the changes done within a trigger.
B. undo all changes done within the trigger and the statement that fired the trigger.
C. Rollback the whole transaction
D. Rollbacl only the statement that fired the trigger.

Answer: B

Question 4.
Which T-SQL Elements can be used in a stored procedure? (Choose Three)

A. 'return', without a return value.
B. 'return', with a return value.
C. 'rollback transaction'
D. 'rollback trigger'
E. the build-in function 'update()'

Answer: A, B, C

Question 5.
Which stored procedure will display the source code for trigger?

A. sp_depends
B. sp_help
C. sp_helptext
D. sp_rename

Answer: C

Question 6.
Which of the following commands cause trigger to fire? (Choose Two)

A. Fast bcp in.
B. Slow bcp in.
C. DELETE TABLE
D. TRUNCATE TABLE
E. INSERT to a user table.
F. INSERT to a temporary table.

Answer: C, E

Question 7.
Create table emplyees
(emp_id int constraint chk_emp_id check(emp_id>500),
dept_id int default 1300,
name varchar(40))

Which on of the insert statement fails?

A. insert into employees (emp_id, dept_id, name)
    values(252, 1200, Tony Adams)
B. insert into employees (emp_id, dept_id, name)
    values(719, 1500, Lee Dixon)
C. insert into employees (emp_id, dept_id, name)
    values(801, default, Denis Bergkamp)
D. insert into employees (emp_id, dept_id, name)
    values(601, 1400, Ashley Cole)

Answer: A

Question 8.
Which statement is true about a domain rule?

A. ANSI complaint
B. Can do multi-couumn checks
C. Checked when data is inserted.
D. Cannot bind to user defined datatype

Answer: C

Question 9.
Which of the following are true about primary key constraints? (Choose Two)

A. It creates a unique index on the specified columns
B. It can be created on a column that allows null values.
C. Up to 255 primary key constraints can be created on a single table.
D. It must be dropped using the alter table command
E. It creates a non unique index on the specified columns

Answer: A, D

Question 10.
Which statements are true about a PRIMARY KEY constraint? (Choose Two)

A. Allow NULLs
B. Can be a composite key
C. by default, creates a unique clustered index
D. Can be dropped by the DROP INDEX command.

Answer: B, C

	Web www.certsbraindumps.com