Quiz Me SQL

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 Maheshc

Maheshc

Community Contributor

Quizzes Created: 1 | Total Attempts: 870

Questions: 44 | Attempts: 870 | Updated: Mar 22, 2023

Questions

Settings

Feedback

During the Quiz End of Quiz

Play as

Quiz Flashcard

Create your own Quiz

Questions regarding sql server 2005. Mainly from stored procedures, triggers and indexes.

Questions and Answers

1.

Which of the following statemens are true?
- A.
  
  Procedures and functions must return values
- B.
  
  Procedures and functions may return value
- C.
  
  Procedures may return values
- D.
  
  Functions must return values
Correct Answer(s)
C. Procedures may return values
D. Functions must return values

Explanation
functions should return value bt not mandatory for procedures

Rate this question:

3
0
2.

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
Correct Answer(s)
A. Is a set of pre-compiled T-SQL statements executed as a single unit.
D. Can be created to carry out repetitive

Explanation
The correct answer is that a stored procedure is a set of pre-compiled T-SQL statements executed as a single unit. This means that all the statements within the stored procedure are compiled and executed together, providing better performance and efficiency. Additionally, stored procedures can be created to carry out repetitive tasks, allowing for code reusability and reducing the need for redundant code.

Rate this question:

0
1
3.

Advantages of The Store Procedures are:
- A.
  
  Reduced client/server traffic
- B.
  
  Reuse of code.
- C.
  
  Implicit invockation
Correct Answer(s)
A. Reduced client/server traffic
B. Reuse of code.

Explanation
The advantages of stored procedures include reduced client/server traffic and reuse of code. By executing the code on the server side, stored procedures can minimize the amount of data transferred between the client and server, thus reducing network traffic. Additionally, stored procedures allow for the reuse of code, as they can be called multiple times by different clients or applications, avoiding the need to rewrite the same code multiple times. Implicit invocation, however, is not mentioned as an advantage of stored procedures in the given options.

Rate this question:
4.

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

Explanation
Local Temporary Procedures are created for temporary use within a session. These procedures are only accessible within the session that created them and are automatically dropped when the session ends. They are useful for creating temporary procedures that are needed for a specific task or session but do not need to be available for other sessions or for long-term use.

Rate this question:
5.

The..............are used to perform tasks that are unable to be perform using standard T-SQL statement.- 1 true choice
- A.
  
  Extended or CLR Stored Procedures
- B.
  
  T-SQL Stored Procedures
- C.
  
  Local stored Procedures
Correct Answer
A. Extended or CLR Stored Procedures

Explanation
Extended or CLR Stored Procedures are used to perform tasks that are unable to be performed using standard T-SQL statements. These procedures allow for the use of external programming languages such as C# or VB.NET to be incorporated into the SQL Server environment, enabling more complex and specialized operations to be executed. T-SQL Stored Procedures, on the other hand, are limited to the capabilities of the T-SQL language and cannot perform tasks that require external programming languages. Local Stored Procedures are not relevant to the question as they do not provide the necessary functionality for tasks that cannot be performed using standard T-SQL statements.

Rate this question:
6.

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.
  
  CLR Stored Procedures
- C.
  
  Custom Stored Procedures
Correct Answer
B. CLR Stored Procedures

Explanation
CLR (Common Language Runtime) Stored Procedures are not residents of SQL Server. They are implemented as Dynamic Link Libraries (DLL) executed outside the SQL Server environment. CLR Stored Procedures allow developers to write stored procedures using any .NET language and execute them within SQL Server. This provides more flexibility and functionality compared to traditional T-SQL stored procedures.

Rate this question:
7.

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

Explanation
Local stored procedures are created in individual user databases and cannot be accessed by any user other than the one who has created it. This means that these stored procedures are specific to the user and cannot be accessed or modified by any other user in the database.

Rate this question:

1
0
8.

The Local Temporary Procedures are............- 2 true choices
- A.
  
  Visible only to the user that create them.
- B.
  
  Use # prefix before the procedure name
- C.
  
  Visible for all users
- D.
  
  Use ##
- E.
  
  Can be used any user
Correct Answer(s)
A. Visible only to the user that create them.
B. Use # prefix before the procedure name

Explanation
Local Temporary Procedures are procedures that are only visible to the user who creates them. They are not accessible or visible to other users. To create a local temporary procedure, the user needs to use the # prefix before the procedure name. This ensures that the procedure is only accessible to the user who created it and not to other users.

Rate this question:
9.

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

Explanation
The correct answer is that Global Temporary Procedures are dropped at the end of the last session, can be used by any user, and are visible for all users. This means that these procedures are temporary and are automatically deleted once the last session ends. They can be accessed and used by any user and are visible to all users on the system.

Rate this question:
10.

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
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
OBJECT_DEFINITION() is a system function used to display the definition of a stored procedure. It can be used without specifying the object ID of the procedure, in which case it will display the definition of the current stored procedure. It can also be used by specifying the object ID of a specific procedure to display its definition.

Rate this question:
11.

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

Explanation
The "OUTPUT" clause is used to return information from each row on which the INSERT, UPDATE, and DELETE operations have been executed. It is particularly useful when you need to retrieve the value of an identity or computed column after an INSERT or UPDATE operation. This allows you to access the modified data and perform further actions based on it.

Rate this question:
12.

When a local temporary table is created inside a stored procedures, the table disappears when the procedure is exited.
- A.
  
  True
- B.
  
  False
Correct Answer
A. 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 local temporary table is automatically dropped and no longer exists. This is different from global temporary tables, which are accessible across multiple sessions and remain in existence until they are explicitly dropped or the session ends.

Rate this question:
13.

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

Explanation
The correct answer is sp_helptext. This system stored procedure is used to display the definition of a stored procedure. It retrieves the text of the stored procedure from the system catalog and returns it as a result set. By using sp_helptext, users can easily view the code and logic of a stored procedure, helping them understand its functionality and make any necessary modifications or improvements.

Rate this question:
14.

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 pre-compiled SQL statements that are stored in the database and can be executed repeatedly. It can reference tables, views, user-defined functions, and other database objects to perform complex operations and return results. This allows for modular and reusable code, improving efficiency and maintainability. Therefore, the given answer "true" is correct.

Rate this question:
15.

The permission associated with the stored procedure are not lost when a store procedure is re-created. And when a stored procedure is altered, the permissions defined for the stored procedure remain the same even though the procedure definetion is changed
- A.
  
  True
- B.
  
  False
Correct Answer
A. True

Explanation
When a stored procedure is re-created, the permissions associated with it are not lost. This means that even if the stored procedure is dropped and then recreated, the permissions that were previously defined for it will still be in place. Similarly, when a stored procedure is altered, the permissions defined for it will remain the same, regardless of any changes made to the procedure's definition. Therefore, the answer "true" is correct.

Rate this question:
16.

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

Explanation
The given statement "Everyone can modify or rename all procedure" is false. This means that not everyone has the ability to modify or rename all procedures. There are likely restrictions or permissions in place that limit who can make changes to procedures.

Rate this question:
17.

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
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 therefore cannot execute it.

Rate this question:
18.

Before dropping a procedure, execute the .......................... system stored procedure to determine which objects depend on the procedure- 1 choice
- A.
  
  Sp_depends
- B.
  
  Sp_help
- C.
  
  Sp_helptext
Correct Answer
A. 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 rely on the specified procedure, allowing the user to assess the potential impact of dropping it.

Rate this question:
19.

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 RETURN statement in T-SQL is used to exit a stored procedure or function and return a value to the calling program. Once the RETURN statement is executed, control is passed back to the calling program and any T-SQL statements following the RETURN statement are not executed. Therefore, the given statement is false.

Rate this question:
20.

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
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 any code or program that previously called the dropped procedure will now call the new procedure and it will be executed without any issues or error messages.

Rate this question:
21.

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-zero
- B.
  
  A non-zero .................a zero
- C.
  
  A non-zero ........... a greater than zero
Correct Answer
A. 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.

Rate this question:
22.

.....................are defined at the time of creation of procedure.- 2 choice
- A.
  
  Input parameters
- B.
  
  Output parameters
- C.
  
  Name of stored procedure
- D.
  
  Nothing
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 accept 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. These parameters provide flexibility and allow the procedure to be customized based on the specific values provided during execution. The name of the stored procedure is not defined at the time of creation, as it can be assigned later.

Rate this question:
23.

Which statement is true?
- A.
  
  If the OUTPUT key word is omitted, the procedure is still exceuted
- B.
  
  If the OUTPUT key word is omitted, the procedure is still exceuted but does not return a value
- C.
  
  If the OUTPUT key word is omitted, the procedure is not exceuted but is not return a value
- D.
  
  If the OUTPUT key word is omitted, the procedure is still exceuted and return a value
Correct Answer
B. If the OUTPUT key word is omitted, the procedure is still exceuted but does not return a value

Explanation
If the OUTPUT keyword is omitted, the procedure is still executed but does not return a value. This means that the procedure will run and perform its intended tasks, but it will not provide any output or result that can be used or accessed by other parts of the program. The absence of the OUTPUT keyword indicates that the procedure is meant to perform some internal operations or modifications without producing a specific output value.

Rate this question:
24.

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.
  
  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. This means that if a stored procedure attempts to return a null value, it will not be allowed and instead, the value zero will be returned. This is important to note because it affects the behavior and output of the stored procedure.

Rate this question:
25.

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

Explanation
The correct answer is ERROR_MESSAGE(). This function is used to display the default error message for an error. It retrieves the message text associated with the error that caused the CATCH block of a TRY...CATCH construct to be run.

Rate this question:
26.

Which statements are true?- 2 choices
- A.
  
  The OUTPUT parameters can be of TEXT and IMAGE data type.
- B.
  
  The calling statement must contain a variable to receive the return value
- C.
  
  The variable can be used in subsequent T-SQL statements in the batch or the calling procedure
- D.
  
  Output parameter can not be cursor placeholders
Correct Answer(s)
B. The calling statement must contain a variable to receive the return value
C. The variable can be used in subsequent T-SQL statements in the batch or the calling procedure

Explanation
The first statement is incorrect because OUTPUT parameters can only be of TEXT or IMAGE data type. The second statement is correct as the calling statement must contain a variable to receive the return value. The third statement is also correct as the variable can be used in subsequent T-SQL statements in the batch or the calling procedure. The fourth statement is incorrect as output parameters can be cursor placeholders.

Rate this question:
27.

Which statement are false?
- A.
  
  The stored procedure can not be nested
- B.
  
  The maximum level of nesting is 32
- C.
  
  There is no limit as to the number of stored procedure that can be called from a given stored procedure
- D.
  
  None above
Correct Answer(s)
A. The stored procedure can not be nested
C. There is no limit as to the number of stored procedure that can be called from a given stored procedure

Explanation
The statement "The stored procedure can not be nested" is false because stored procedures can be nested within other stored procedures. The statement "There is no limit as to the number of stored procedure that can be called from a given stored procedure" is also false because there is a maximum level of nesting, which is 32.

Rate this question:
28.

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

Explanation
The given correct answer is "RETURN". In stored procedures, the RETURN statement is used to specify an integer value that will be returned when the stored procedure is executed. It is commonly used to indicate the success or failure of the procedure or to return a specific value to the calling program.

Rate this question:
29.

Which statements are true?- 3 choices
- A.
  
  Trigger can not be executed directly nor do they pass or receive parameters.
- B.
  
  Trigger can not be executed directly nor do they pass or receive parameters.
- C.
  
  DML 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. DML 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 also cannot pass or receive parameters.

The second statement is true because a DML trigger is a stored procedure that is executed when data in a specified table is modified.

The last statement is true because triggers are often created to enforce referential integrity among logically related data in different tables.

Rate this question:
30.

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.
  
  The return code indicates the execution status of the stored procedure
- D.
  
  @@MESSAGE_ERR is available
- E.
  
  @ERR_MESSAGE() is used for display error message
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
C. The return code indicates the execution status of the stored procedure

Explanation
The given answer is correct 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. However, there is no information provided about @@MESSAGE_ERR or @ERR_MESSAGE(), so it cannot be determined if they are true or not.

Rate this question:
31.

The DDL Triggers....
- A.
  
  Are used to check and control database operations.
- B.
  
  Operate only after the table or view is modified.and are defined either at the database or the server level
- C.
  
  Are defined at the database level
- D.
  
  Are used to enforce business rules when data is modified in tables or views
- E.
  
  Are used to enforce business rules when data is modified in tables or views
Correct Answer(s)
A. Are used to check and control database operations.
B. 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. They operate only after the table or view is modified and can be defined either at the database or the server level. These triggers are commonly used to enforce business rules when data is modified in tables or views.

Rate this question:
32.

The UPDATE triggers are created either at the column level or at the table.
- A.
  
  True
- B.
  
  False
Correct Answer
A. True

Explanation
UPDATE triggers can be created either at the column level or at the table level. At the column level, the trigger is fired only when a specific column is updated. This allows for more specific and targeted triggers. At the table level, the trigger is fired whenever any column in the table is updated. This provides a more general trigger that captures any updates to the table. Therefore, the statement that UPDATE triggers can be created either at the column level or at the table level is true.

Rate this question:
33.

Which are true for DELETE Trigger?- 2 choices
- A.
  
  The record is deleted from the trigger table and inserted in the inserted table
- B.
  
  The record is deleted from the trigger table and inserted in the deleted table
- C.
  
  The deleted record stored in the deleted table is copied back to the Trigger table
- D.
  
  The deleted record stored in the deleted table is lost when Trigger is executed
Correct Answer(s)
B. The record is deleted from the trigger table and inserted in the deleted table
C. The deleted record stored in the deleted table is copied back to the Trigger table

Explanation
When a DELETE trigger is executed, the record is deleted from the trigger table and inserted into the deleted table. Additionally, the deleted record stored in the deleted table is copied back to the Trigger table. This means that the record is not lost and can be accessed again in the trigger table.

Rate this question:
34.

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.
  
  Are executed before constraint checks are performed on the table.and They executed after creating Inserted and Deleted tables,
- D.
  
  A tables or a view can have any INSTEAD OF trigger defined for INSERT, UPDATE, and DELETE.
Correct Answer(s)
A. Is executed in place of the INSERT, UPDATE or DELETE operation
C. 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 are executed before constraint checks are performed on the table and after creating Inserted and Deleted tables. These triggers can be defined for both tables and views.

Rate this question:
35.

Which all 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 answer is correct. Each triggering action can indeed have multiple AFTER triggers. Two triggers action on a table can have the same first and last triggers. Trigger definition can be viewed if the information is not encrypted. DML trigger definition can be modified by dropping and creating the trigger.

Rate this question:
36.

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 data structures that are created on database tables to improve the performance of queries. They allow for faster retrieval of data by creating a sorted reference to the data in the table, which reduces the amount of time it takes to search for specific records. By using indexes, the database can quickly locate the requested data, resulting in faster query execution and improved overall database performance. Therefore, the statement that indexes are used for faster retrieval of data and improve the speed of queries in a database is true.

Rate this question:
37.

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 a clustered index determines the physical order of data in a table. It organizes the data based on the values of the indexed column(s) and therefore can only exist once in a table. On the other hand, a table can have multiple nonclustered indexes which provide an additional way to access the data in the table. However, there is a limit to the number of nonclustered indexes that can be created on a table, which is typically 249. Therefore, the statement that a table can have only one clustered index and 249 nonclustered indexes is true.

Rate this question:
38.

Reserves space on the intermediate level of an index.
- A.
  
  Fillfactor
- B.
  
  Pad_index
- C.
  
  Unique
- D.
  
  Computed
Correct Answer
B. Pad_index

Explanation
The correct answer is "pad_index". This option refers to a feature in indexing where space is reserved on the intermediate level of an index. This can help improve the performance of queries by reducing fragmentation and improving data access.

Rate this question:
39.

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
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. This means that the index is created by combining multiple columns together, allowing for more efficient searching and sorting of data based on multiple criteria. By using a composite index, the database can quickly locate and retrieve data based on the values of multiple columns, improving overall query performance.

Rate this question:
40.

Too many Indexes descrease the performance of ...........
- A.
  
  Create
- B.
  
  Insert
- C.
  
  Drop
- D.
  
  Update
- E.
  
  Delete
Correct Answer(s)
B. Insert
D. Update
E. Delete

Explanation
Having too many indexes can decrease the performance of the insert, update, and delete operations. Indexes are used to improve the speed of data retrieval, but they come with a cost. When performing these operations, the indexes need to be updated, which can be time-consuming and resource-intensive. The more indexes there are, the more time it takes to update them, leading to decreased performance. Therefore, having too many indexes can negatively impact the efficiency of insert, update, and delete operations.

Rate this question:
41.

Noncluster indexes do not physiscally rearrange the data in the database. and cluster index causes records to be physically sorted or sequential order.
- A.
  
  True
- B.
  
  False
Correct Answer
A. True

Explanation
Nonclustered indexes in a database do not physically rearrange the data, meaning that the order of the records in the table remains the same. These indexes create a separate structure that references the data in the table, allowing for faster retrieval of specific records based on the indexed columns. On the other hand, a clustered index does physically rearrange the data in the table to match the order of the indexed column(s), resulting in a physically sorted or sequential order. Therefore, the given statement that nonclustered indexes do not physically rearrange the data while a cluster index does is true.

Rate this question:
42.

What option to reserve space on the leaf page of an index for adding additional data at a later
- A.
  
  Fill factor
- B.
  
  Pad_index
- C.
  
  Nestlevel
Correct Answer
A. Fill factor

Explanation
The fill factor option is used to reserve space on the leaf page of an index for adding additional data at a later time. It specifies the percentage of space that should be filled with data on each leaf page of the index. A lower fill factor leaves more space available for future data, while a higher fill factor maximizes the amount of data that can be stored on each page. By setting a lower fill factor, space is reserved for future data growth without requiring frequent page splits or index reorganization.

Rate this question:
43.

An Index can have a max of ....columns
- A.
  
  16
- B.
  
  25
- C.
  
  32
Correct Answer
A. 16

Explanation
An index in a database is used to improve the performance of queries by allowing faster retrieval of data. The maximum number of columns that an index can have determines the complexity and efficiency of the index. In this case, the correct answer is 16, which means that an index can have a maximum of 16 columns. This limitation ensures that the index remains manageable and does not become overly complex, while still providing efficient query performance.

Rate this question:
44.

Way to view index?
- A.
  
  SP_helptext 'index_name'
- B.
  
  Sp_helpindex 'table_name'
- C.
  
  Sp_textindex 'table_name'
- D.
  
  Sp_helpindex 'index_name'
Correct Answer
B. Sp_helpindex 'table_name'

Explanation
The correct answer is "sp_helpindex 'table_name'". This stored procedure is used to view the index information for a specified table. It provides details such as the index name, index type, column names included in the index, and the index description. It is a useful tool for understanding the indexing strategy and performance of a table.

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
Mar 22, 2023

Quiz Edited by
ProProfs Editorial Team
Feb 03, 2010

Quiz Created by
Maheshc

Quiz Me SQL

Which of the following statemens are true?

The Stored Procedure - 2 true choices.

Advantages of The Store Procedures are:

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 not residents of SQL server. they are implemented as Dynamic Link Libraries(DLL) executed outsite the SQL Sever Environment.- 1 true choice.

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

The Local Temporary Procedures are............- 2 true choices

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

OBJECT_DEFINITION() - 2 choices

Using "OUTPUT" clause.- 2 true choice

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?

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

The permission associated with the stored procedure are not lost when a store procedure is re-created. And when a stored procedure is altered, the permissions defined for the stored procedure remain the same even though the procedure definetion is changed

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.

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

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

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

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

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

Which statement is true?

Which statement is true?- 1 choice

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

Which statements are true?- 2 choices

Which statement are false?

Specifies an integer value to be returned though the stored procedure

Which statements are true?- 3 choices

Which are true?- 3 choice

The DDL Triggers....

The UPDATE triggers are created either at the column level or at the table.

Which are true for DELETE Trigger?- 2 choices

INSTEAD OF Triggers.- 2 choices

Which all are true

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

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

Reserves space on the intermediate level of an index.

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

Too many Indexes descrease the performance of ...........

Noncluster indexes do not physiscally rearrange the data in the database. and cluster index causes records to be physically sorted or sequential order.

What option to reserve space on the leaf page of an index for adding additional data at a later

An Index can have a max of ....columns

Way to view index?