Ssdo Quiz For Aptech (SQL Server 2005)

84 Questions | Total Attempts: 1706

SettingsSettingsSettings
Please wait...
SQL Server Quizzes & Trivia

How to learn well SSDO


Related Topics
Questions and Answers
  • 1. 
    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

  • 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

  • 3. 
    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

  • 4. 
    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

  • 5. 
    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.

  • 6. 
    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.

  • 7. 
    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

  • 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

  • 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

  • 10. 
    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

  • 11. 
    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

  • 12. 
    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

  • 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

  • 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

  • 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

  • 16. 
    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

  • 17. 
    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

  • 18. 
    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

  • 19. 
    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

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

      False

    • B. 

      True

  • 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

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

      True

    • B. 

      False

  • 23. 
    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 changeed
    • A. 

      True

    • B. 

      False

  • 24. 
    Everyone can  modify or rename all procedure.
    • A. 

      True

    • B. 

      False

  • 25. 
    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

  • 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

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

      True

    • B. 

      False

  • 28. 
    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

  • 29. 
    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

  • 30. 
    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

  • 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

  • 32. 
    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

  • 33. 
    Displays the default error message for an error.-1 choice.
    • A. 

      MESSAGE_ERROR()

    • B. 

      RETURN_ERROR()

    • C. 

      ERROR_MESSAGE()

    • D. 

      @@ERROR

    • E. 

      @@ERROR_LINE

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

      VALUE_RETURN()

    • B. 

      RETURN()

    • C. 

      RETURN

    • D. 

      RETURN_INT()

  • 35. 
    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

  • 36. 
     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.

  • 37. 
    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.

  • 38. 
    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

  • 39. 
    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.

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

      True

    • B. 

      False

  • 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,

  • 42. 
    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.

  • 43. 
    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

  • 44. 
    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.

  • 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

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

      True

    • B. 

      False

  • 47. 
    DDL Triggers are...
    • A. 

      UPDATE trigger

    • B. 

      DROP Trigger

    • C. 

      INSERT Trigger

    • D. 

      DELETE Trigger

    • E. 

      CREATE and ALTER Trigger

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

      True

    • B. 

      False

  • 49. 
    Reseves space on the intermediate level of an index.
    • A. 

      FILLFACTOR

    • B. 

      PAD_INDEX

    • C. 

      Computed

    • D. 

      Unique

  • 50. 
    ...............index stores data in a sorted manner.
    • A. 

      Nonclustered

    • B. 

      Clustered

    • C. 

      Unique

  • 51. 
    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

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

      Full-Text Index.

    • B. 

      Unique Index

    • C. 

      XML Index

  • 53. 
    Too many Indexes descrease the performance of ............- 3 choices
    • A. 

      CREATE

    • B. 

      INSERT

    • C. 

      DROP

    • D. 

      UPDATE

    • E. 

      DELETE

  • 54. 
    What option to reserve space on the leaf page of an index for adding additional data at a later stage?
    • A. 

      FILLFACTOR

    • B. 

      PAD_INDEX

  • 55. 
    An Index can have a max of ....columns
    • A. 

      26

    • B. 

      32

    • C. 

      24

    • D. 

      16

    • E. 

      No limit

  • 56. 
    FILLFACTOR value ranges from 0-100?
    • A. 

      True

    • B. 

      False

  • 57. 
    The properties of an Object such as a table or a view, are stored in special system data. These properties are........................
    • A. 

      System Views

    • B. 

      Metadata

    • C. 

      Index

    • D. 

      Constraints

    • E. 

      Inserted,deleted

  • 58. 
    A View is created using horizontally partittioned data from one or more tables.
    • A. 

      Indexed View

    • B. 

      Partitioned View

    • C. 

      Standard view

  • 59. 
    A View is created in everywhere databases.
    • A. 

      True

    • B. 

      False

  • 60. 
    A View can be created on temporary table.
    • A. 

      True

    • B. 

      False

  • 61. 
    Clustered index, nonlustered index can be created on the view.
    • A. 

      True

    • B. 

      False

  • 62. 
    A View definition can not contain keywords:
    • A. 

      Insert

    • B. 

      Compute by

    • C. 

      Group By

    • D. 

      Delete

    • E. 

      Into

  • 63. 
    A View definition can not contain keywords:
    • A. 

      Update

    • B. 

      Compute

    • C. 

      Drop

    • D. 

      Default

    • E. 

      Top..........Group by

  • 64. 
    A View may be have a FullText index.
    • A. 

      True

    • B. 

      False

  • 65. 
    View can  reference more than 1024 columns.
    • A. 

      True

    • B. 

      False

  • 66. 
    The create view statement can be combined with other T-SQL statements in a single batch.
    • A. 

      True

    • B. 

      False

  • 67. 
    A View can not reference a temporary  table.
    • A. 

      True

    • B. 

      False

  • 68. 
     View can retrieve information from  xml data type  using ........... method.
    • A. 

      Object_id()

    • B. 

      Value()

    • C. 

      Return()

  • 69. 
    Prevents invalid data from being entered into the column.- 1 choice.
    • A. 

      Validity

    • B. 

      Data integrity

    • C. 

      Business Rules

    • D. 

      Constraint

  • 70. 
    Refers to validity and consistency of data- 1choice.
    • A. 

      Data integrity

    • B. 

      Constraint

    • C. 

      Business Rules

    • D. 

      Validity

  • 71. 
    A column with this constraint  does not allow  duplicate values, but  allows null values  to be inserted.- 1 choice
    • A. 

      Primary key

    • B. 

      Check

    • C. 

      Foreign key

    • D. 

      Unique

  • 72. 
    Which statements are True?- 3 choices
    • A. 

      Constraints assist in ensuring data integrity

    • B. 

      Triggers are values that you can define on a column or a group of column

    • C. 

      Rules are the constraints that you can apply to multiple tables

    • D. 

      Rules are independent of table definitions.

    • E. 

      Default are the codes automatically executed when a a specified event occurs.

  • 73. 
    Entity Integrity are:-3 choices
    • A. 

      Foreign key constraint

    • B. 

      Primary key constraint

    • C. 

      Unique constraint

    • D. 

      Index, Identity property

    • E. 

      Check, Default constraint

  • 74. 
    Refers to policies and standards adhered to by an organization in running its operations.
    • A. 

      Data integrity

    • B. 

      Constraint

    • C. 

      Business Rules

    • D. 

      Validity

  • 75. 
    Ensure that the values in a column are within a specified range-1 choice.
    • A. 

      Referential Integrity

    • B. 

      Domain Integrity

    • C. 

      User-defined

    • D. 

      Entity integrity

  • 76. 
    A column with this constraint  does not allow null values or duplicate value to be inserted.- 1 choice.
    • A. 

      Unique

    • B. 

      Foreign key

    • C. 

      Primary key

  • 77. 
    Refers to constraints defined by user.- 1 choice.
    • A. 

      Referential Integrity

    • B. 

      Entity Integrity

    • C. 

      Domain Integrity

    • D. 

      User-define Integrity

  • 78. 
    Domain integrity include:- 3 choices
    • A. 

      Primary key constraint, Default constraint

    • B. 

      Foreign key constraint, Default constraint

    • C. 

      Unique constraint

    • D. 

      Check constraint

    • E. 

      Not null definition, rules , Data types

  • 79. 
    Maintaining consistency of data across tables that related through common columns.- 1 choice.
    • A. 

      Entity Integrity

    • B. 

      Domain Integrity

    • C. 

      Referential Integrity

  • 80. 
    In a table , when no two rows have the exact same values in all columns.- 1 choice.
    • A. 

      Domain Integrity

    • B. 

      Entity Integrity

    • C. 

      Referential Integrity

    • D. 

      User-defined

  • 81. 
    Which statements are true?- 3 choices.
    • A. 

      A row in a table represents the instance of an entity

    • B. 

      Referential integrity is implemented using UNIQUE constraint

    • C. 

      A domain defines the range of values for columns in a table

    • D. 

      User-defined integrity is maintained using default constraints

    • E. 

      Default definitions specify default values for columns that do not accept null values.

  • 82. 
    Maintains relationship between tables in a database, and Ensure consistency of data across related tables.- 1 choice
    • A. 

      User-define

    • B. 

      Entity integrity

    • C. 

      Referential Integrity

    • D. 

      Domain Integrity

  • 83. 
    Which statements are true?- 2 choices
    • A. 

      The PRIMARY KEY constraint allows a null value to be entered once in a column.

    • B. 

      Constraints are used to ensure validity of data in a table and consistency of data across tables

    • C. 

      A column specified with a UNIQUE constraint should have reference to the Primary Key column.

    • D. 

      The Foreign key constraint helps in maintaining referential integrity in the database

  • 84. 
    Ensures that each record in a table is unique.- 1 choice.
    • A. 

      User-define Integrity

    • B. 

      Referential Integrity

    • C. 

      Entity Integrity

    • D. 

      Domain Integrity