Questions regarding sql server 2005. Mainly from stored procedures, triggers and indexes.
Procedures and functions must return values
Procedures and functions may return value
Procedures may return values
Functions must return values
Is a set of pre-compiled T-SQL statements executed as a single unit.
Is a set of T-SQL that are executed as a single block of code that performs a specific task.
Is used in database Administrative and information activities.
Can be created to carry out repetitive
Reduced client/server traffic
Reuse of code.
Implicit invockation
Local Temporary Procedures
Temporary Stored Procedures
Remote Stored Procedures
Global Temporary Procedures
Extended or CLR Stored Procedures
T-SQL Stored Procedures
Local stored Procedures
Remote Stored Procedures
CLR Stored Procedures
Custom Stored Procedures
Local stored Procedures
Temporary Stored Procedures
Extended Stored Procedures
Visible only to the user that create them.
Use # prefix before the procedure name
Visible for all users
Use ##
Can be used any user
Are dropped at the end of the last session
Can be used any user
Are visible for all users
Can only be used by its owner
Are dropped at the end of the current session
System stored procedure use to display the dependencies of a stored procedure
System function used to display the definition of a stored procedure
System view used to display definition of a stored procedure
System function used to display the definition of a stored procedure by specifying the object ID of the procedure
Return information from each row on which the INSERT, UPDATE, and DELETE have been executed
Useful to retrieve the value of an identity or computed column after an INSERT or UPDATE operation
Print out a Variable
Create stored procedures for performance of various tasks, they are referred to as user-defined or custom stored procedures
True
False
Object_definition()
Sp_helptext
Sp_depends
Sp_display
True
False
True
False
True
False
A replaced procedure is dipslay.
An error message is display
No procedure display
B and C
Sp_depends
Sp_help
Sp_helptext
True
False
Denied immediately
Executed sucessfully
Had some error messages
No Action
A values zero...................a non-zero
A non-zero .................a zero
A non-zero ........... a greater than zero
Input parameters
Output parameters
Name of stored procedure
Nothing
If the OUTPUT key word is omitted, the procedure is still exceuted
If the OUTPUT key word is omitted, the procedure is still exceuted but does not return a value
If the OUTPUT key word is omitted, the procedure is not exceuted but is not return a value
If the OUTPUT key word is omitted, the procedure is still exceuted and return a value
When the RETURN statement is used in a stored procedure, It can return a null value. if a procedure tries to return a null value, a warning message is generated and the value zero is returned.
When the RETURN statement is used in a stored procedure, It can not return a null value. if a procedure tries to return a null value, a warning message is generated and the value zero is returned
When the RETURN statement is used in a stored procedure, It can not return a null value. if a procedure tries to return a null value, a warning message is generated and the value one is returned
A and C
MESSAGE_ERROR()
ERROR_MESSAGE()
@@ERROR
@@ERROR_LINE