Ssdo Quiz For Aptech (SQL Server 2005)

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.

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.

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.

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.

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.

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.

The FILLFACTOR value is used in SQL Server to determine the percentage of space on each leaf-level page to be filled with data. It ranges from 0 to 100, where 0 means the pages are completely empty and 100 means the pages are completely full. Therefore, the statement "FILLFACTOR value ranges from 0-100" is true.

Clustered index and nonclustered index can be created on a view. A clustered index determines the physical order of the data in a table or view, while a nonclustered index is a separate structure that contains a sorted list of key values with a pointer to the location of the data in the table or view. By allowing the creation of both types of indexes on a view, it provides flexibility in optimizing the performance of queries that involve the view.

A partitioned view is created using horizontally partitioned data from one or more tables. This means that the data in the view is divided into multiple partitions based on a specific criteria, such as a range of values or a specific column. Each partition contains a subset of the data, allowing for better performance and manageability of large datasets. This type of view is commonly used in scenarios where there is a need to distribute data across multiple storage systems or to improve query performance by accessing only the relevant partitions.

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.

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.

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.

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.

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.

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.

A view is a virtual table that is created based on the result of a query. It does not store any data itself, but rather provides a way to access and manipulate data from one or more underlying tables. Since a temporary table is created for temporary storage during a session and is not accessible outside of that session, a view cannot reference it. Therefore, the statement that a view cannot reference a temporary table is true.

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.

A column with the primary key constraint does not allow null values or duplicate values to be inserted. The primary key uniquely identifies each row in a table and ensures data integrity by enforcing uniqueness and non-nullability.

User-defined integrity refers to constraints that are defined by the user. This means that the user has the ability to define and enforce their own integrity rules for their data. These rules can include things like data validation, uniqueness constraints, and referential integrity. By allowing users to define their own integrity rules, it gives them more control over the data and ensures that it meets their specific requirements and standards.

The FILLFACTOR option is used to reserve space on the leaf page of an index for adding additional data in the future. It specifies the percentage of space on each leaf page to be filled with data during the index creation process. By setting a lower FILLFACTOR value, more space is left empty on each leaf page, allowing for future data additions without the need for page splits. This can help improve performance and reduce fragmentation in the index. The PAD_INDEX option, on the other hand, is used to specify whether to enable padding of the index pages, but it does not directly relate to reserving space for future data additions.

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.

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.

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.

Referential integrity is the correct answer because it refers to the property that ensures that relationships between tables in a database are maintained and that data remains consistent across related tables. It enforces rules that prevent actions that would destroy the relationships between tables, such as deleting a record that is referenced by another table. By enforcing referential integrity, the database can maintain the integrity and validity of the data, preventing inconsistencies and preserving the relationships between tables.

Referential Integrity ensures that relationships between tables are maintained by enforcing the existence of a foreign key in one table that corresponds to a primary key in another table. In this case, since the tables are related through common columns, Referential Integrity would ensure that the data in these columns remains consistent across the tables. It prevents the creation of orphaned records and maintains data integrity by ensuring that any changes made to the primary key in one table are reflected in the foreign key in the related table.

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.

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.

Entity Integrity refers to the rule that states that each row in a table must have a unique identifier, such as a primary key, and that no two rows can have the exact same values in all columns. This ensures that each row in the table is uniquely identifiable and eliminates the possibility of duplicate or redundant data. Therefore, in the given scenario, where no two rows have the exact same values in all columns, it aligns with the concept of Entity Integrity.

A column with the "unique" constraint does not allow duplicate values, meaning that each value in the column must be unique. However, it does allow null values to be inserted, which means that the column can have empty or missing values. This constraint is useful when you want to ensure that each value in the column is unique, but also allow some rows to have missing or unknown values.

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.

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.

Entity integrity refers to the rule that ensures that each record in a table is unique. This means that no two records in the table can have the same primary key value. It is a fundamental concept in database design and helps maintain the accuracy and integrity of the data by preventing duplicate entries or inconsistencies. By enforcing entity integrity, the database system guarantees that each record can be uniquely identified and accessed.

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.

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

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.

Data integrity refers to the accuracy, consistency, and reliability of data throughout its lifecycle. It ensures that data remains intact and reliable, free from any unauthorized modifications or corruption. Data integrity is crucial for maintaining the quality and trustworthiness of data, as it ensures that the data is valid and consistent. By enforcing data integrity rules, organizations can prevent data inconsistencies and errors, ensuring that the data is reliable for decision-making and other business processes.

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.

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.

Domain integrity refers to the validation of data in a database column to ensure that it falls within a specified range or set of values. It ensures that the data stored in a column adheres to the defined rules or constraints, such as data type, format, or range of values. In the context of the question, domain integrity ensures that the values in a column are within the specified range, thus making it the correct answer.

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.

Business rules are policies and standards followed by an organization to govern its operations. These rules define how the organization should conduct its business, including processes, procedures, and guidelines. They ensure consistency, accuracy, and integrity in data and operations. Business rules provide a framework for decision-making and help maintain order and efficiency within the organization. Therefore, the correct answer is Business Rules.

The correct answer is "value()". The value() method in SQL Server is used to retrieve information from the xml data type. It allows you to extract a single value or a fragment of XML data from an XML column.

A view is a virtual table based on the result of a query. In most database systems, including SQL, there is a limit on the number of columns a table can have, often set at 1024. Since a view is based on a query and essentially represents a table, it would also be subject to this limit. Therefore, the statement that a view can reference more than 1024 columns is false.

PAD_INDEX is the correct answer because it refers to the option that reserves space on the intermediate level of an index. By enabling PAD_INDEX, space is reserved in the index pages at the intermediate level, which helps to improve the efficiency of index maintenance operations. This option is commonly used when there are frequent index updates or when the index key values are monotonically increasing.

Having too many indexes can decrease the performance of INSERT, UPDATE, and DELETE operations. Indexes are used to speed up data retrieval by creating a separate data structure that allows for quicker access to specific columns. However, maintaining indexes requires additional time and resources. When performing INSERT, UPDATE, or DELETE operations, the indexes need to be updated to reflect the changes, which can slow down the overall performance. Therefore, having too many indexes can negatively impact the efficiency of these operations.

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.

The first statement is false. The PRIMARY KEY constraint does not allow a null value to be entered once in a column. It enforces that each value in the column is unique and not null.

The second statement is true. Constraints are indeed used to ensure the validity of data in a table and consistency of data across tables. They define rules and restrictions on the data that can be stored in a table.

The third statement is false. A column specified with a UNIQUE constraint does not necessarily have to reference the Primary Key column. It just ensures that each value in the column is unique.

The fourth statement is true. The Foreign Key constraint is used to establish a relationship between two tables and helps in maintaining referential integrity in the database. It ensures that values in the foreign key column match values in the primary key column of the referenced table.

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.

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.

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.