Ssdo Quiz For Aptech (SQL Server 2005)

Approved & Edited by ProProfs Editorial Team

The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.

Learn about Our Editorial Process

| By Vietha

Vietha

Community Contributor

Quizzes Created: 11 | Total Attempts: 9,614

Questions: 84 | Attempts: 1,885 | Updated: Jan 12, 2024

Questions

Settings

Feedback

During the Quiz End of Quiz

Play as

Quiz Flashcard

Create your own Quiz

How to learn well SSDO

Questions and Answers

1.

The Stored Procedure - 2 true choices.
- A.
  
  Is a set of pre-compiled T-SQL statements executed as a single unit.
- B.
  
  Is a set of T-SQL that are executed as a single block of code that performs a specific task.
- C.
  
  Is used in database Administrative and information activities.
- D.
  
  Can be created to carry out repetitive
- E.
  
  All of about.
Correct Answer(s)
B. Is a set of T-SQL that are executed as a single block of code that performs a specific task.
D. Can be created to carry out repetitive

Explanation
A stored procedure is a set of T-SQL statements that are executed as a single block of code that performs a specific task. It is commonly used in database administration and information activities. Additionally, stored procedures can be created to carry out repetitive tasks efficiently.

Rate this question:
2.

All information about tables in user Database is stored in a set of tables called the System catalog.that can be accessed using........- 1 true choice.
- A.
  
  Sercurity Stored Procedures
- B.
  
  Catalog Stored Procedures
- C.
  
  Distributed Query Stored Procedures
- D.
  
  A and B
- E.
  
  User-define Stored Procedures
Correct Answer
B. Catalog Stored Procedures

Explanation
The System catalog is a set of tables that stores information about tables in the user Database. Catalog Stored Procedures are used to access this System catalog. Therefore, the correct answer is Catalog Stored Procedures.

Rate this question:
3.

The Stored procedures use to manage the security of the database. They are:-1 true choice.
- A.
  
  Database Mail and SQL mail stored Procedures
- B.
  
  Cursor Stored Procedures.
- C.
  
  Sercurity Stored Procedures
- D.
  
  Database Mail and SQL mail stored Procedures.
- E.
  
  Distributed Query Stored Procedures
Correct Answer
C. Sercurity Stored Procedures

Explanation
Security stored procedures are used to manage the security of the database. These procedures help in controlling access to the database, managing user permissions, and enforcing security policies. They can be used to create and manage user accounts, grant or revoke privileges, and implement security measures such as encryption and authentication. By using security stored procedures, administrators can ensure that only authorized users have access to the database and that sensitive data is protected.

Rate this question:
4.

Where are Catalog Stored Procedures ?-2 true choices
- A.
  
  Sp_column
- B.
  
  sp_addalias
- C.
  
  Sp_describe_cursor
- D.
  
  Sp_database and sp_statistics
- E.
  
  A and C
Correct Answer(s)
A. Sp_column
D. Sp_database and sp_statistics

Explanation
The correct answer is A and C, which means that the catalog stored procedures can be found in sp_column, sp_database, and sp_statistics. These stored procedures are used for various purposes related to managing and querying database catalogs. They provide functionality such as retrieving information about columns, databases, and statistics, which are essential for database administrators and developers.

Rate this question:
5.

The ........is used to change the owner of the current database.-1 true choice.
- A.
  
  Sp_cursor_list
- B.
  
  Sp_changedbowner
- C.
  
  Sp_addalias
- D.
  
  Sp_describe_column
- E.
  
  A and D
Correct Answer
B. Sp_changedbowner

Explanation
The correct answer is "sp_changedbowner". This stored procedure is used to change the owner of the current database. It allows the user to specify a new owner for the database, which can be useful in situations where ownership needs to be transferred or updated.

Rate this question:
6.

The Cursor Stored Procedures are.........- 2 true choices.
- A.
  
  sp_describe_column
- B.
  
  Sp_indexs
- C.
  
  Sp_primarykeys
- D.
  
  Sp_cursor_list
- E.
  
  A and D
Correct Answer(s)
A. sp_describe_column
D. Sp_cursor_list

Explanation
The correct answer is "sp_describe_column" and "sp_cursor_list". These are both stored procedures that are used in cursor operations. "sp_describe_column" is used to retrieve information about the columns in a specified table or view, while "sp_cursor_list" is used to retrieve information about the open cursors on a specified server. Both of these stored procedures are commonly used in cursor operations to gather information about the columns and cursors being used.

Rate this question:
7.

Database Mail and SQL mail stored Procedures.- 1 true choice.
- A.
  
  Used to pefrom email operations from with in the SQL server.
- B.
  
  Use in the management of distributed queries.
- C.
  
  Use to implement the fuctionality of a Cursor.
- D.
  
  Use to manage the security of the DB.
- E.
  
  Send mail to the SQL server.
Correct Answer
A. Used to pefrom email operations from with in the SQL server.

Explanation
The correct answer is "Used to perform email operations from within the SQL server." Database Mail and SQL Mail Stored Procedures are used to send emails directly from the SQL server. This functionality allows users to send notifications, alerts, or reports via email without the need for external applications or tools. It simplifies the process of integrating email functionality into SQL server applications and automates the sending of emails based on certain conditions or triggers within the database.

Rate this question:

1
0
8.

Advantages of The Store Procedures are:
- A.
  
  Reduced client/server traffic
- B.
  
  Can be created to carry out repetitive
- C.
  
  Reuse of code.
- D.
  
  A and B
- E.
  
  B and D
Correct Answer(s)
A. Reduced client/server traffic
C. Reuse of code.

Explanation
The advantages of stored procedures include reduced client/server traffic and reuse of code. By executing stored procedures on the server side, the amount of data transmitted between the client and server is minimized, resulting in reduced network traffic. Additionally, stored procedures allow for the reuse of code, as they can be created to carry out repetitive tasks, eliminating the need to rewrite the same code multiple times.

Rate this question:
9.

Which are not System Stored Procedures?-2 true choices.
- A.
  
  Distributed Query Stored Procedures
- B.
  
  Extended Stored Procedures
- C.
  
  Temporary Stored Procedures
- D.
  
  Database Mail and SQL mail stored Procedures.
- E.
  
  Cursor Stored Procedures
Correct Answer(s)
B. Extended Stored Procedures
C. Temporary Stored Procedures

Explanation
Extended Stored Procedures and Temporary Stored Procedures are not System Stored Procedures. System Stored Procedures are pre-defined stored procedures that are included with the SQL Server software and are used for various administrative tasks. Extended Stored Procedures are custom procedures that are created by users and are used to extend the functionality of SQL Server. Temporary Stored Procedures are created and used within a specific session or connection and are automatically dropped when the session or connection is closed.

Rate this question:
10.

The................are not residents of SQL server. they are implemented as Dynamic Link Libraries(DLL) executed outsite the SQL Sever Environment.- 1 true choice.
- A.
  
  Remote Stored Procedures
- B.
  
  Temporary Stored Procedures
- C.
  
  Extended Stored Procedures.
- D.
  
  B and C
- E.
  
  Customer Stored Procedures
Correct Answer
C. Extended Stored Procedures.

Explanation
Extended Stored Procedures are not residents of SQL server. They are implemented as Dynamic Link Libraries (DLL) executed outside the SQL Server environment.

Rate this question:
11.

The Stored procedures are created for temporary use with a session are called...- 1 true choice.
- A.
  
  Local Temporary Procedures
- B.
  
  Global Temporary Procedures
- C.
  
  Remote Stored Procedures
- D.
  
  Temporary Stored Procedures
- E.
  
  Customer Stored Procedures
Correct Answer
D. Temporary Stored Procedures

Explanation
Temporary stored procedures are created for temporary use within a session. They are not permanently stored in the database and are only available for the duration of the session. These procedures can be used to perform specific tasks or calculations within a session and are useful when there is a need for temporary logic or data manipulation. Unlike customer stored procedures, temporary stored procedures are not meant to be reused or accessed by other sessions or users.

Rate this question:
12.

The..............are used to perform tasks that are unable to be perform using standard T-SQL statement.- 1 true choice
- A.
  
  Extended Stored Procedures.
- B.
  
  T-SQL Stored Procedures
- C.
  
  Local stored Procedures
- D.
  
  B and C
- E.
  
  Not of about
Correct Answer
A. Extended Stored Procedures.

Explanation
Extended Stored Procedures are used to perform tasks that are unable to be performed using standard T-SQL statements. T-SQL Stored Procedures and Local Stored Procedures are both types of stored procedures that can be executed within the database, but they do not provide the same level of functionality as Extended Stored Procedures. Extended Stored Procedures allow for the execution of external programs or scripts from within the database, providing additional capabilities beyond what can be achieved with standard T-SQL statements. Therefore, the correct answer is Extended Stored Procedures.

Rate this question:
13.

The ...................are created individual user databases.And can not be accessed by any user other than the one who has created it.- 1 true choice
- A.
  
  Extended Stored Procedures
- B.
  
  Temporary Stored Procedures
- C.
  
  Local stored Procedures
- D.
  
  Global Temporary Procedures
- E.
  
  B and C
Correct Answer
C. Local stored Procedures

Explanation
Local stored procedures are created individual user databases and can not be accessed by any user other than the one who has created it. This means that these stored procedures are specific to a particular user and cannot be accessed or modified by other users in the database. They provide a level of security and privacy for the user who created them.

Rate this question:
14.

The Global Tem Procedures ....- 3 true choices
- A.
  
  Are dropped at the end of the last session.
- B.
  
  Are dropped at the end of the current session
- C.
  
  Can only be used by its owner
- D.
  
  Can be used any user
- E.
  
  Are visible for all users
Correct Answer(s)
A. Are dropped at the end of the last session.
D. Can be used any user
E. Are visible for all users

Explanation
The correct answer choices are "are dropped at the end of the last session," "can be used any user," and "are visible for all users." This suggests that the Global Tem Procedures are not retained after the current session ends, they can be used by any user, and they are visible to all users.

Rate this question:
15.

Temporary Stored Procedures include:- 2 true choices
- A.
  
  Local Temporary Procedures
- B.
  
  Global Temporary Procedures
- C.
  
  Local stored Procedures
- D.
  
  Customer Stored Procedures
- E.
  
  A and C
Correct Answer(s)
A. Local Temporary Procedures
B. Global Temporary Procedures

Explanation
Temporary stored procedures are procedures that are created and stored in the temporary database of a database management system. They are used for a specific session or connection and are automatically deleted when the session or connection ends. Local temporary procedures are created within a specific session and can only be accessed by that session. Global temporary procedures are created within a specific database and can be accessed by multiple sessions. Therefore, the correct answer is Local Temporary Procedures and Global Temporary Procedures.

Rate this question:
16.

The...............create stored procedures for performance of various tasks, they are referred to as user-defined or custom stored procedures and can be set to override the default system procedures.- 1 true choice.
- A.
  
  Extended Stored Procedures
- B.
  
  Customer Stored Procedures
- C.
  
  T-SQL Stored Procedures
- D.
  
  System Stored Procedures
- E.
  
  A and D
Correct Answer
B. Customer Stored Procedures

Explanation
Customer stored procedures are created by users to perform specific tasks and can override the default system procedures. These procedures are customized and tailored to meet the specific needs of the user. Extended stored procedures, T-SQL stored procedures, and system stored procedures are not specifically mentioned in the question and do not match the given explanation. Therefore, the correct answer is customer stored procedures.

Rate this question:
17.

EXECUTE xp_fileexists 'C:\sample.txt' - 1 true choice.
- A.
  
  Check file sample.txt exists.
- B.
  
  Check file sample.txt exists or not
- C.
  
  Execute a file is named sample.txt
- D.
  
  A and C
- E.
  
  Not all about
Correct Answer
B. Check file sample.txt exists or not

Explanation
The correct answer is "check file sample.txt exists or not". This is because the given SQL statement "EXECUTE xp_fileexists 'C:\sample.txt'" is used to check whether the file "sample.txt" exists or not at the specified path "C:\sample.txt". Therefore, the correct answer accurately describes the purpose of the SQL statement.

Rate this question:
18.

Using "OUTPUT" clause.- 2 true choice
- A.
  
  Return information from each row on which the INSERT, UPDATE, and DELETE have been executed.
- B.
  
  Create stored procedures for performance of various tasks, they are referred to as user-defined or custom stored procedures
- C.
  
  Useful to retrieve the value of an identity or computed column after an INSERT or UPDATE operation
- D.
  
  Print out a Variable.
- E.
  
  E and C
Correct Answer(s)
A. Return information from each row on which the INSERT, UPDATE, and DELETE have been executed.
C. Useful to retrieve the value of an identity or computed column after an INSERT or UPDATE operation

Explanation
The correct answer is "Return information from each row on which the INSERT, UPDATE, and DELETE have been executed" and "Useful to retrieve the value of an identity or computed column after an INSERT or UPDATE operation". The OUTPUT clause in SQL is used to return information from each row that has been affected by an INSERT, UPDATE, or DELETE statement. It can be used to retrieve the value of an identity or computed column after an INSERT or UPDATE operation.

Rate this question:
19.

A Stored Procedure can reference tables,views, user-define functions and other
- A.
  
  True
- B.
  
  False
Correct Answer
A. True

Explanation
A stored procedure is a set of SQL statements that are stored in the database and can be executed as a single unit. It can reference tables, views, user-defined functions, and other database objects to perform complex operations or calculations. This allows for better organization and reusability of code, as well as improved performance since the database can optimize the execution of the stored procedure. Therefore, the statement "A Stored Procedure can reference tables, views, user-define functions and other" is true.

Rate this question:
20.

When a local temporary table is created inside a stored procedures, the table disappears when the procedure is exited.
- A.
  
  False
- B.
  
  True
Correct Answer
B. True

Explanation
When a local temporary table is created inside a stored procedure, it is only accessible within that specific procedure. Once the procedure is exited, the temporary table is automatically dropped and no longer exists. This is because local temporary tables are tied to the scope of the procedure and are meant to be used for temporary storage within that particular execution context.

Rate this question:
21.

System stored procedure used to display the definition of a stored procedure?-1 true choice.
- A.
  
  Object_definition()
- B.
  
  Sp_helptext
- C.
  
  Sp_depends
- D.
  
  Sys.sql_modules
- E.
  
  Sp_display
Correct Answer
B. Sp_helptext

Explanation
The correct answer is sp_helptext. The sp_helptext system stored procedure is used to display the definition of a stored procedure in SQL Server. It takes the name of the stored procedure as a parameter and returns the text of the stored procedure's definition. This can be useful for viewing the code of a stored procedure and understanding its functionality.

Rate this question:
22.

OBJECT_DEFINITION() - 2 choices
- A.
  
  System stored procedure use to display the dependencies of a stored procedure
- B.
  
  System function used to display the definition of a stored procedure.
- C.
  
  System view used to display definition of a stored procedure.
- D.
  
  System function used to display the definition of a stored procedure by specifying the object ID of the procedure.
- E.
  
  A and D
Correct Answer(s)
B. System function used to display the definition of a stored procedure.
D. System function used to display the definition of a stored procedure by specifying the object ID of the procedure.

Explanation
The correct answer is A and D. OBJECT_DEFINITION() is a system function used to display the definition of a stored procedure. It can be used without specifying the object ID to display the definition of the current stored procedure, or it can be used with the object ID to display the definition of a specific stored procedure. Therefore, both options A and D are correct explanations for the given answer.

Rate this question:
23.

Everyone can modify or rename all procedure.
- A.
  
  True
- B.
  
  False
Correct Answer
B. False

Explanation
The statement suggests that everyone has the ability to modify or rename all procedures. However, the correct answer is false, indicating that not everyone has this capability. This implies that there are restrictions or limitations in place regarding the modification or renaming of procedures, possibly due to security or organizational policies.

Rate this question:
24.

Stored procedures can be dropped if they are no longer needed. if another stored procedure calls a deleted procedure,- 1 choice.
- A.
  
  A replaced procedure is dipslay.
- B.
  
  An error message is display.
- C.
  
  No procedure display
- D.
  
  B and C
- E.
  
  None about
Correct Answer
B. An error message is display.

Explanation
If a stored procedure is dropped and another stored procedure calls the deleted procedure, an error message will be displayed. This is because the calling procedure is unable to find the deleted procedure and cannot execute it. Therefore, attempting to call a deleted procedure will result in an error being displayed.

Rate this question:
25.

If a new procedure is created using the same name as well as the same parameters as the drop procedure,all calls to the dropped procedure will be - 1 choice
- A.
  
  Denied immediately
- B.
  
  Executed sucessfully
- C.
  
  Had some error messages
- D.
  
  No Action
- E.
  
  None about
Correct Answer
B. Executed sucessfully

Explanation
If a new procedure is created using the same name as well as the same parameters as the dropped procedure, all calls to the dropped procedure will be executed successfully. This means that the new procedure will be able to handle the calls that were previously made to the dropped procedure without any issues or errors.

Rate this question:
26.

Before dropping a procedure, execute the .......................... system stored procedure to determine which objects depend on the procedure- 1 choice
- A.
  
  Sys.sql_modules
- B.
  
  Sp_depends
- C.
  
  Sp_helptext
- D.
  
  Sp_check
- E.
  
  A and B
Correct Answer
B. Sp_depends

Explanation
To determine which objects depend on a procedure before dropping it, the correct system stored procedure to execute is sp_depends. This procedure will provide a list of all the objects that depend on the specified procedure, allowing the user to assess the potential impact of dropping it. The other options listed, sys.sql_modules, sp_helptext, and sp_check, are not specifically designed for this purpose.

Rate this question:
27.

When a stored procedure is created using options, these options should be included in the ALTER PROCEDURE statement to retain their function.
- A.
  
  False
- B.
  
  True
Correct Answer
B. True

Explanation
When a stored procedure is created with options, these options need to be included in the ALTER PROCEDURE statement in order to maintain their functionality. This means that if any changes need to be made to the stored procedure, the options should be included in the ALTER PROCEDURE statement to ensure that the procedure still operates as intended. Therefore, the statement "True" is correct.

Rate this question:
28.

If a stored procedure is executed sucessfully, It returns ...........by defaul. If errors are encountered and the procedure is not successfully executed, ...............interger value is returned.- 1 choice
- A.
  
  A values zero ....................a non - one
- B.
  
  A non-zero .................a zero
- C.
  
  A non-zero ........... a greater than zero
- D.
  
  A values zero...................a non-zero
- E.
  
  A and D
Correct Answer
D. A values zero...................a non-zero

Explanation
When a stored procedure is executed successfully, it returns a value of zero by default. However, if errors are encountered and the procedure is not successfully executed, a non-zero integer value is returned. In this case, the correct answer states that a value of zero is returned when the stored procedure is executed successfully, and a non-zero value is returned when the procedure encounters errors and is not successfully executed.

Rate this question:
29.

The RETURN statement passes control back to the calling program. any T-SQL statements following the RETURN statement are executed.
- A.
  
  True
- B.
  
  False
Correct Answer
B. False

Explanation
The explanation for the given correct answer is that the RETURN statement in T-SQL does not execute any statements following it. Once the RETURN statement is encountered, it immediately passes control back to the calling program or stored procedure, and any statements after the RETURN statement are not executed. Therefore, the statement "any T-SQL statements following the RETURN statement are executed" is incorrect.

Rate this question:
30.

Which statement is true?- 1 choice
- A.
  
  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.
- B.
  
  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.
- C.
  
  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.
- D.
  
  None above
- E.
  
  A and C
Correct Answer
B. 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.

Explanation
The correct answer is that when the RETURN statement is used in a stored procedure, it cannot return a null value. If a procedure tries to return a null value, a warning message is generated and the value zero is returned.

Rate this question:
31.

.....................are defined at the time of creation of procedure.- 2 choice
- A.
  
  Input parameters
- B.
  
  Output parameters
- C.
  
  A and B
- D.
  
  Name of stored procedure
Correct Answer(s)
A. Input parameters
B. Output parameters

Explanation
Input parameters and output parameters are defined at the time of creation of a procedure. These parameters allow the procedure to receive input values and return output values when it is executed. Input parameters are used to pass values into the procedure, while output parameters are used to return values from the procedure. By defining these parameters, the procedure can be customized and made more flexible, as it can accept different inputs and produce different outputs based on the values passed to it. The other options, "A and B" and "Name of stored procedure", are not directly related to the parameters of a procedure.

Rate this question:
32.

Displays the default error message for an error.-1 choice.
- A.
  
  MESSAGE_ERROR()
- B.
  
  RETURN_ERROR()
- C.
  
  ERROR_MESSAGE()
- D.
  
  @@ERROR
- E.
  
  @@ERROR_LINE
Correct Answer
C. ERROR_MESSAGE()

Explanation
The correct answer is ERROR_MESSAGE(). This function is used to display the default error message for an error. It returns the error message text as a result. The other options, MESSAGE_ERROR(), RETURN_ERROR(), @@ERROR, and @@ERROR_LINE, do not specifically display the default error message for an error.

Rate this question:
33.

Specifies an integer value to be returned though the stored procedure.- 1 choice
- A.
  
  VALUE_RETURN()
- B.
  
  RETURN()
- C.
  
  RETURN
- D.
  
  RETURN_INT()
Correct Answer
C. RETURN

Explanation
The correct answer is RETURN. In stored procedures, the RETURN statement is used to specify an integer value that will be returned. This value can be used to indicate the success or failure of the stored procedure or to return any other relevant information. The RETURN statement is commonly used in programming languages and databases to control the flow of execution and return values from functions or procedures.

Rate this question:
34.

Which are true?- 3 choice
- A.
  
  @@ERROR_LINE : Returns the line number that caused the error
- B.
  
  @@ERROR : Returns the error number for the error in the last T_SQL statment.
- C.
  
  @NESTLEVEL : Specifies the level of nesting of the current procedure.
- D.
  
  The RETURN funtion specifies the return value for a stored procedure
- E.
  
  The return code indicates the execution status of the stored procedure.
Correct Answer(s)
A. @@ERROR_LINE : Returns the line number that caused the error
B. @@ERROR : Returns the error number for the error in the last T_SQL statment.
E. The return code indicates the execution status of the stored procedure.

Explanation
The correct answer is @@ERROR_LINE : Returns the line number that caused the error, @@ERROR : Returns the error number for the error in the last T_SQL statement, and The return code indicates the execution status of the stored procedure. These options are true because @@ERROR_LINE returns the line number that caused the error, @@ERROR returns the error number for the error in the last T_SQL statement, and the return code indicates the execution status of the stored procedure.

Rate this question:
35.

The OUTPUT keyword specifies that the variables are involved in passing values from the called procedure to the callling program.
- A.
  
  True
- B.
  
  False
Correct Answer
A. True

Explanation
The OUTPUT keyword is used to indicate that the variables in a called procedure will be used to pass values back to the calling program. This means that any changes made to the variables within the called procedure will be reflected in the calling program after the procedure is executed. Therefore, the statement "The OUTPUT keyword specifies that the variables are involved in passing values from the called procedure to the calling program" is true.

Rate this question:
36.

Which statement is not True?- 2 choice
- A.
  
  When stored procedure is executed from other stored procedure, the procedures are said to be nested.
- B.
  
  When an error occurs in the TRY block, the statements following the statement that caused the error are executed only after CATCH block is executed.
- C.
  
  When the @@ERROR funtion is called, an error message is returned for the error occuring in the last executed statement.
- D.
  
  A and C
Correct Answer(s)
B. When an error occurs in the TRY block, the statements following the statement that caused the error are executed only after CATCH block is executed.
C. When the @@ERROR funtion is called, an error message is returned for the error occuring in the last executed statement.

Explanation
The given correct answer states that the statements following the statement that caused the error in the TRY block are executed only after the CATCH block is executed. However, this statement is not true. In reality, when an error occurs in the TRY block, the statements following the statement that caused the error are not executed. The control is immediately transferred to the CATCH block. Additionally, the answer also states that the @@ERROR function returns an error message for the error occurring in the last executed statement, which is also not true. The @@ERROR function returns the error number, not the error message.

Rate this question:
37.

Which statements are true?- 3 choices
- A.
  
  Trigger can not be executed directly nor do they pass or receive parameters.
- B.
  
  A Trigger is executed when the table is created.
- C.
  
  Trigger is a stored procedure that executed when data in a specified table is modified.
- D.
  
  Trigger is the same as Check constraint.
- E.
  
  Trigger are often created to enforce referential integrity among logically related data in different table.
Correct Answer(s)
A. Trigger can not be executed directly nor do they pass or receive parameters.
C. Trigger is a stored procedure that executed when data in a specified table is modified.
E. Trigger are often created to enforce referential integrity among logically related data in different table.

Explanation
The first statement is true because triggers cannot be executed directly and they do not pass or receive parameters.
The second statement is true because a trigger is executed when data in a specified table is modified.
The fifth statement is true because triggers are often created to enforce referential integrity among logically related data in different tables.
However, the fourth statement is false because a trigger is not the same as a check constraint.

Rate this question:
38.

DML Trigger is executed when ....- 1 choice
- A.
  
  a session is established with a LOGON event.
- B.
  
  Data is inserted,modified or delete in a table or a View using the INSERT,UPDATE or DELETE statements
- C.
  
  a table or a view is created, modified, or delete using the CREATE,ALTER, or DROP statements.
Correct Answer
B. Data is inserted,modified or delete in a table or a View using the INSERT,UPDATE or DELETE statements

Explanation
DML triggers are executed when data is inserted, modified, or deleted in a table or a view using the INSERT, UPDATE, or DELETE statements. This means that whenever any of these statements are used to make changes to the data in a table or view, the DML trigger associated with that table or view will be triggered and its code will be executed.

Rate this question:
39.

The DDL Triggers....
- A.
  
  Are used to check and control database operations.
- B.
  
  Are used to enforce business rules when data is modified in tables or views.
- C.
  
  Are defined at the database level.
- D.
  
  Execute either while modifying the data or after the data is modified.
- E.
  
  Operate only after the table or view is modified.and are defined either at the database or the server level
Correct Answer(s)
A. Are used to check and control database operations.
E. Operate only after the table or view is modified.and are defined either at the database or the server level

Explanation
DDL triggers are used to check and control database operations, operating only after the table or view is modified. These triggers can be defined either at the database or the server level. They are not specifically used to enforce business rules when data is modified, as that is the role of DML triggers.

Rate this question:
40.

Which statements are true?- 2 choices
- A.
  
  An AFTER trigger is executed on completion of DROP, ATLTER and CREATE.
- B.
  
  AFTER triggers can be created on tables and columns
- C.
  
  A table can have multiple AFTER trigger defined for each INSERT, UPDATE, and DELETE operation.
- D.
  
  An AFTER trigger is executed before the constraint check in the table is completed
- E.
  
  The Trigger is executed after the inserted and Deleted tables are created.
Correct Answer(s)
C. A table can have multiple AFTER trigger defined for each INSERT, UPDATE, and DELETE operation.
E. The Trigger is executed after the inserted and Deleted tables are created.

Explanation
This answer is correct because it accurately states that a table can have multiple AFTER triggers defined for each INSERT, UPDATE, and DELETE operation. It also correctly states that the trigger is executed after the inserted and deleted tables are created.

Rate this question:
41.

INSTEAD OF Triggers.- 2 choices
- A.
  
  Is executed in place of the INSERT, UPDATE or DELETE operation.
- B.
  
  Can not be created on views, It is only on tables
- C.
  
  A tables or a view can have any INSTEAD OF trigger defined for INSERT, UPDATE, and DELETE.
- D.
  
  Are executed before constraint checks are performed on the table.and They executed after creating Inserted and Deleted tables,
Correct Answer(s)
A. Is executed in place of the INSERT, UPDATE or DELETE operation.
D. Are executed before constraint checks are performed on the table.and They executed after creating Inserted and Deleted tables,

Explanation
INSTEAD OF triggers are executed in place of the INSERT, UPDATE, or DELETE operation. They allow the user to define custom actions to be performed instead of the default database operations. These triggers can be created on tables but not on views. They are executed before constraint checks are performed on the table and after creating the Inserted and Deleted tables, which are used to access the data being modified.

Rate this question:
42.

Which statement is true?
- A.
  
  Update triggers do not use the Deleted table to update records in a table
- B.
  
  Delete trigger do not use the Inserted table to delete records from atable
Correct Answer
B. Delete trigger do not use the Inserted table to delete records from atable

Explanation
This statement is true because delete triggers do not use the Inserted table to delete records from a table. The Inserted table is used in delete triggers to capture the rows that are being deleted, while the Deleted table is used in update triggers to capture the rows that are being updated. Therefore, the correct answer is that delete triggers do not use the Inserted table.

Rate this question:
43.

Where are true?
- A.
  
  Each triggering action can have multiple AFTER triggers
- B.
  
  Two triggers action on a table can have the same first and last triggers
- C.
  
  Trigger definition can be viewed if the information is not encrypted
- D.
  
  DML trigger definition can be modified by dropping and creating the trigger.
Correct Answer(s)
A. Each triggering action can have multiple AFTER triggers
C. Trigger definition can be viewed if the information is not encrypted
D. DML trigger definition can be modified by dropping and creating the trigger.

Explanation
The given correct answer includes three statements that are true. First, each triggering action can have multiple AFTER triggers, meaning that multiple triggers can be executed after a specific action occurs on a table. Second, the trigger definition can be viewed if the information is not encrypted, allowing users to see the details of how a trigger is defined. Third, the DML (Data Manipulation Language) trigger definition can be modified by dropping and creating the trigger, providing the ability to make changes to the trigger's functionality.

Rate this question:
44.

DDL Triggers are...
- A.
  
  UPDATE trigger
- B.
  
  DROP Trigger
- C.
  
  INSERT Trigger
- D.
  
  DELETE Trigger
- E.
  
  CREATE and ALTER Trigger
Correct Answer(s)
B. DROP Trigger
E. CREATE and ALTER Trigger

Explanation
DDL triggers are special types of triggers in a database management system that are fired in response to specific Data Definition Language (DDL) statements. These triggers are used to perform actions before or after certain DDL statements are executed.

The given correct answer includes DROP Trigger, CREATE and ALTER Trigger. This means that DDL triggers can be created or altered using the CREATE and ALTER statements, and they can also be dropped or deleted using the DROP statement. These actions allow for the management and customization of DDL triggers in a database system.

Rate this question:
45.

Which statements are false?
- A.
  
  DDL trigger for DROP operation can be created as an INSTEAD OF trigger.
- B.
  
  A DDL trigger for ALTER event can be invoked only after the modification operations are completed
- C.
  
  A DDL trigger definition can be display using as_helptext.
- D.
  
  a DD L trigger can be removed from the current database using the DROP TRIGGER
Correct Answer(s)
A. DDL trigger for DROP operation can be created as an INSTEAD OF trigger.
C. A DDL trigger definition can be display using as_helptext.

Explanation
The statement "DDL trigger for DROP operation can be created as an INSTEAD OF trigger" is false. INSTEAD OF triggers are used for DML operations (INSERT, UPDATE, DELETE), not for DDL operations like DROP.

The statement "a DDL trigger definition can be displayed using as_helptext" is also false. The correct syntax to display the definition of a DDL trigger is sp_helptext, not as_helptext.

Rate this question:
46.

Indexes are used for faster retrieval of data.and improve the speed of query when accessing data a database.
- A.
  
  True
- B.
  
  False
Correct Answer
A. True

Explanation
Indexes are indeed used for faster retrieval of data and to improve the speed of queries when accessing data in a database. By creating indexes on specific columns, the database can organize and sort the data in a way that allows for quicker searching and retrieval. Without indexes, the database would have to scan through the entire table to find the requested data, which can be time-consuming and inefficient. Therefore, using indexes can greatly enhance the performance and efficiency of database operations.

Rate this question:
47.

...............index stores data in a sorted manner.
- A.
  
  Nonclustered
- B.
  
  Clustered
- C.
  
  Unique
Correct Answer
B. Clustered

Explanation
A clustered index stores data in a sorted manner based on the values of the indexed column(s). This means that the physical order of the data in the table corresponds to the order of the clustered index. This can improve the performance of queries that involve range scans or ordered retrieval of data. In contrast, a nonclustered index does not dictate the physical order of the data and is stored separately from the table. Unique indexes ensure that the indexed column(s) contain unique values, but they do not necessarily store data in a sorted manner.

Rate this question:
48.

A table can have only one Clustered index and 249 nonclustered indexs.
- A.
  
  True
- B.
  
  False
Correct Answer
A. True

Explanation
A table can have only one clustered index because the clustered index determines the physical order of the data in the table. It defines the way the data is stored on disk, so there can only be one clustered index. On the other hand, a table can have multiple nonclustered indexes, up to a maximum of 249, which are separate structures that provide an alternate way to access the data in the table without changing the physical order. Therefore, the statement is true.

Rate this question:
49.

A ..............can be defined on a column with no duplicate values.
- A.
  
  Full-Text Index.
- B.
  
  Unique Index
- C.
  
  XML Index
Correct Answer
B. Unique Index

Explanation
A unique index is a type of index that can be defined on a column with no duplicate values. This means that each value in the column must be unique and cannot be repeated. The purpose of a unique index is to enforce uniqueness in a column, ensuring that no two rows in the table have the same value for the indexed column. This can be useful in scenarios where data integrity and uniqueness are important, such as when dealing with primary keys or unique identifiers.

Rate this question:
50.

A ...........is created on 2 or more columns. Both clustered index and nonclustered index can be ............
- A.
  
  Composite Index
- B.
  
  Full-Text Index
- C.
  
  Xml Index
- D.
  
  Unique Index
Correct Answer
A. Composite Index

Explanation
A composite index is created on 2 or more columns. Both clustered index and nonclustered index can be composite indexes.

Rate this question:

Quiz Review Timeline +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

Current Version
Jan 12, 2024

Quiz Edited by
ProProfs Editorial Team
Oct 21, 2008

Quiz Created by
Vietha

Ssdo Quiz For Aptech (SQL Server 2005)

The Stored Procedure - 2 true choices.

All information about tables in user Database is stored in a set of tables called the System catalog.that can be accessed using........- 1 true choice.

The Stored procedures use to manage the security of the database. They are:-1 true choice.

Where are Catalog Stored Procedures ?-2 true choices

The ........is used to change the owner of the current database.-1 true choice.

The Cursor Stored Procedures are.........- 2 true choices.

Database Mail and SQL mail stored Procedures.- 1 true choice.

Advantages of The Store Procedures are:

Which are not System Stored Procedures?-2 true choices.

The................are not residents of SQL server. they are implemented as Dynamic Link Libraries(DLL) executed outsite the SQL Sever Environment.- 1 true choice.

The Stored procedures are created for temporary use with a session are called...- 1 true choice.

The..............are used to perform tasks that are unable to be perform using standard T-SQL statement.- 1 true choice

The ...................are created individual user databases.And can not be accessed by any user other than the one who has created it.- 1 true choice

The Global Tem Procedures ....- 3 true choices

Temporary Stored Procedures include:- 2 true choices

The...............create stored procedures for performance of various tasks, they are referred to as user-defined or custom stored procedures and can be set to override the default system procedures.- 1 true choice.

EXECUTE xp_fileexists 'C:\sample.txt' - 1 true choice.

Using "OUTPUT" clause.- 2 true choice

A Stored Procedure can reference tables,views, user-define functions and other

When a local temporary table is created inside a stored procedures, the table disappears when the procedure is exited.

System stored procedure used to display the definition of a stored procedure?-1 true choice.

OBJECT_DEFINITION() - 2 choices

Everyone can modify or rename all procedure.

Stored procedures can be dropped if they are no longer needed. if another stored procedure calls a deleted procedure,- 1 choice.

If a new procedure is created using the same name as well as the same parameters as the drop procedure,all calls to the dropped procedure will be - 1 choice

Before dropping a procedure, execute the .......................... system stored procedure to determine which objects depend on the procedure- 1 choice

When a stored procedure is created using options, these options should be included in the ALTER PROCEDURE statement to retain their function.

If a stored procedure is executed sucessfully, It returns ...........by defaul. If errors are encountered and the procedure is not successfully executed, ...............interger value is returned.- 1 choice

The RETURN statement passes control back to the calling program. any T-SQL statements following the RETURN statement are executed.

Which statement is true?- 1 choice

.....................are defined at the time of creation of procedure.- 2 choice

Displays the default error message for an error.-1 choice.

Specifies an integer value to be returned though the stored procedure.- 1 choice

Which are true?- 3 choice

The OUTPUT keyword specifies that the variables are involved in passing values from the called procedure to the callling program.

Which statement is not True?- 2 choice

Which statements are true?- 3 choices

DML Trigger is executed when ....- 1 choice

The DDL Triggers....

Which statements are true?- 2 choices

INSTEAD OF Triggers.- 2 choices

Which statement is true?

Where are true?

DDL Triggers are...

Which statements are false?

Indexes are used for faster retrieval of data.and improve the speed of query when accessing data a database.

...............index stores data in a sorted manner.

A table can have only one Clustered index and 249 nonclustered indexs.

A ..............can be defined on a column with no duplicate values.

A ...........is created on 2 or more columns. Both clustered index and nonclustered index can be ............