Q1: The Cursor Stored Procedures are......... (choose 2 answers)
sp_describe_column
sp_indexs
sp_primarykeys
sp_cursor_list
1 and 4
Q2: All information about tables in user Database is stored in a set of tables called the System catalog.that can be accessed using........
Sercurity Stored Procedures
Catalog Stored Procedures
Distributed Query Stored Procedures
1 and 2
User-define Stored Procedures
Q3: Where are Catalog Stored Procedures? (choose 2 answers)
sp_column
sp_addalias
sp_describe_cursor
sp_database and sp_statistics
1 and 3
Q4: The Stored procedures use to manage the security of the database. They are:
Database Mail and SQL mail stored Procedures
Cursor Stored Procedures
Sercurity Stored Procedures
Database Mail and SQL mail stored Procedures
Distributed Query Stored Procedures
Q5: Database Mail and SQL mail stored Procedures
Used to pefrom email operations from with in the SQL server
Use in the management of distributed queries
Use to implement the fuctionality of a Cursor
Use to manage the security of the DB
Send mail to the SQL server
Q6: The Stored Procedure (choose 2 answers)
is a set of pre-compiled T-SQL statements executed as a single unit
is a set of T-SQL that are executed as a single block of code that performs a specific task
is used in database Administrative and information activities
can be created to carry out repetitive
All of about
Q7: The ........is used to change the owner of the current database
sp_cursor_list
sp_changedbowner
sp_addalias
sp_describe_column
1 and 4
Q8: Advantages of The Store Procedures are: (choose 2 answers)
Reduced client/server traffic
can be created to carry out repetitive
Reuse of code
1 and 2
2 and 4
Q9: Which are not System Stored Procedures? (choose 2 answers)
Distributed Query Stored Procedures
Extended Stored Procedures
Temporary Stored Procedures
Database Mail and SQL mail stored Procedures
Cursor Stored Procedures
Q10: The Stored procedures are created for temporary use with a session are called...
Local Temporary Procedures
Global Temporary Procedures
Remote Stored Procedures
Temporary Stored Procedures
Customer Stored Procedures
Q11: The..............are used to perform tasks that are unable to be perform using standard T-SQL statement
Extended Stored Procedures
T-SQL Stored Procedures
Local stored Procedures
2 and 3
Not of about
Q12: The................are not residents of SQL server. they are implemented as Dynamic Link Libraries(DLL) executed outsite the SQL Sever Environment
Remote Stored Procedures
Temporary Stored Procedures
Extended Stored Procedures
2 and 3
Customer Stored Procedures
Q13: The ...................are created individual user databases.And can not be accessed by any user other than the one who has created it
Extended Stored Procedures
Temporary Stored Procedures
Local stored Procedures
Global Temporary Procedures
2 and 3
Q14: The Local Temporary Procedures are............ (choose 2 answers)
Visible only to the user that create them
visible for all users
use # prefix before the procedure name
use ##
can be used any user
Q15: The Global Tem Procedures .... (3 true choices)
are dropped at the end of the last session
are dropped at the end of the current session
Can only be used by its owner
can be used any user
are visible for all users
Q16: Temporary Stored Procedures include: (choose 2 answers)
Local Temporary Procedures
Global Temporary Procedures
Local stored Procedures
Customer Stored Procedures
1 and 3
Q17: EXECUTE xp_fileexists 'C:\sample.txt'
check file sample.txt exists
check file sample.txt exists or not
Execute a file is named sample.txt
1 and 3
Not all about
Q18: OBJECT_DEFINITION() (choose 2 answers)
system stored procedure use to display the dependencies of a stored procedure
system function used to display the definition of a stored procedure
system view used to display definition of a stored procedure
system function used to display the definition of a stored procedure by specifying the object ID of the procedure
1 and 4
Q19: 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
Extended Stored Procedures
Customer Stored Procedures
T-SQL Stored Procedures
System Stored Procedures
1 and 4
Q20: Using "OUTPUT" clause (choose 2 answers)
Return information from each row on which the INSERT, UPDATE, and DELETE have been executed
Create stored procedures for performance of various tasks, they are referred to as user-defined or custom stored procedures
Useful to retrieve the value of an identity or computed column after an INSERT or UPDATE operation
Print out a Variable
1 and 3
Q21: When a local temporary table is created inside a stored procedures, the table disappears when the procedure is exited
False
True
Q22: System stored procedure used to display the definition of a stored procedure?
Object_definition()
sp_helptext
sp_depends
sys.sql_modules
sp_display
Q23: A Stored Procedure can reference tables,views, user-define functions and other
True
False
Q24: 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
true
false
Q25: Everyone can modify or rename all procedure
true
false
Q26: A Stored procedure can be modified to change the procedure name as well as the procedure definition
True
False
Q27: Stored procedures can be dropped if they are no longer needed. if another stored procedure calls a deleted procedure
a replaced procedure is dipslay
an error message is display
No procedure display
2 and 3
none about
Q28: Before dropping a procedure, execute the .......................... system stored procedure to determine which objects depend on the procedure
sys.sql_modules
sp_depends
sp_helptext
sp_check
1 and 2
Q29: The RETURN statement passes control back to the calling program. any T-SQL statements following the RETURN statement are executed
True
False
Q30: 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
Denied immediately
executed sucessfully
Had some error messages
No Action
None about
Q31: 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
a values zero ....................a non - one
a non-zero .................a zero
a non-zero ........... a greater than zero
a values zero...................a non-zero
1 and 4
Q32: When a stored procedure is created using options, these options should be included in the ALTER PROCEDURE statement to retain their function
false
True
Q33: .....................are defined at the time of creation of procedure (choose 2 answers)
Input parameters
Output parameters
1 and 2
Name of stored procedure
Q34: Which statement is true?
If the OUPUT key word is omitted, the procedure is still exceuted
If the OUPUT key word is omitted, the procedure is still exceuted but is not return a value
If the OUPUT key word is omitted, the procedure is not exceuted but is not return a value
1 and 2
If the OUPUT key word is omitted, the procedure is still exceuted and return a value
Q35: Which statement is true?
When the RETURN statement is used in a stored procedure, It can return a null value. if a procedure tries to return a null value, a warning message is generated and the value zero is returned
When the RETURN statement is used in a stored procedure, It can not return a null value. if a procedure tries to return a null value, a warning message is generated and the value zero is returned
When the RETURN statement is used in a stored procedure, It can not return a null value. if a procedure tries to return a null value, a warning message is generated and the value one is returned
None above
1 and 3
Q36: Which statements are true? (choose 2 answers)
The OUTPUT parameters can be of TEXT and IMAGE data type
The calling statement must contain a variable to receive the return value
The variable can be used in subsequent T-SQL statements in the batch or the calling procedure
Output parameter can not be cursor placeholders
Q37: Displays the default error message for an error
MESSAGE_ERROR()
RETURN_ERROR()
ERROR_MESSAGE()
@ERROR
@ERROR_LINE
Q38: Which statement are false? (choose 2 answers)
The stored procedure can not be nested
The maximum level of nesting is 32
There is no limit as to the number of stored procedure that can be called from a given stored procedure
None above
Q39: Specifies an integer value to be returned though the stored procedure
VALUE_RETURN()
RETURN()
RETURN
RETURN_INT()
Q40: Which statement is not True? (choose 2 answers)
When stored procedure is executed from other stored procedure, the procedures are said to be nested
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
When the @ERROR funtion is called, an error message is returned for the error occuring in the last executed statement
1 and 3
Q41: Which statements are true? (choose 3 answers)
Trigger can not be executed directly nor do they pass or receive parameters
A Trigger is executed when the table is created
Trigger is a stored procedure that executed when data in a specified table is modified
Trigger is the same as Check constraint
Trigger are often created to enforce referential integrity among logically related data in different table
Q42: which are true? (choose 3 answers)
@ERROR_LINE : Returns the line number that caused the error
@ERROR : Returns the error number for the error in the last T_SQL statment
@NESTLEVEL : Specifies the level of nesting of the current procedure
The RETURN funtion specifies the return value for a stored procedure
The return code indicates the execution status of the stored procedure
Q43: The DDL Triggers.... (choose 2 answers)
are used to check and control database operations
are used to enforce business rules when data is modified in tables or views
are defined at the database level
execute either while modifying the data or after the data is modified
operate only after the table or view is modified.and are defined either at the database or the server level
Q44: DML Trigger is executed when ....
a session is established with a LOGON event
data is inserted,modified or delete in a table or a View using the INSERT,UPDATE or DELETE statements
a table or a view is created, modified, or delete using the CREATE,ALTER, or DROP statements
Q45: The OUTPUT keyword specifies that the variables are involved in passing values from the called procedure to the callling program
True
False
Q46: The UPDATE triggers are created either at the column level or at the table
True
false
Q47: Which are true for DELETE Trigger? (choose 2 answers)
The record is deleted from the trigger table and inserted in the inserted table
The record is deleted from the trigger table and inserted in the deleted table
the deleted record stored in the deleted table is copied back to the Trigger table
the deleted record stored in the deleted table is lost when Trigger is executed
Q48: INSTEAD OF Triggers (choose 2 answers)
is executed in place of the INSERT, UPDATE or DELETE operation
can not be created on views, It is only on tables
A tables or a view can have any INSTEAD OF trigger defined for INSERT, UPDATE, and DELETE
Are executed before constraint checks are performed on the table.and They executed after creating Inserted and Deleted tables
Q49: which statements are true? (choose 2 answers)
An AFTER trigger is executed on completion of DROP, ATLTER and CREATE
AFTER triggers can be created on tables and columns
A table can have multiple AFTER trigger defined for each INSERT, UPDATE, and DELETE operation
An AFTER trigger is executed before the constraint check in the table is completed
The Trigger is executed after the inserted and Deleted tables are created
Q50: which statement is true?
Update triggers do not use the Deleted table to update records in a table
Delete trigger do not use the Inserted table to delete records from atable
Q51: Where are true?
Each triggering action can 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
Q52: Which statements are false?
DDL trigger for DROP operation can be created as an INSTEAD OF trigger
a DDL trigger for ALTER event can be invoked only after the modification operations are completed
a DDL trigger definition can be display using as_helptext
a DD L trigger can be removed from the current database using the DROP TRIGGER
Q53: Indexes are used for faster retrieval of data.and improve the speed of query when accessing data a database
True
false
Q54: DDL Triggers are...
UPDATE trigger
DROP Trigger
INSERT Trigger
DELETE Trigger
CREATE and ALTER Trigger
Q55: A table can have only one Clustered index and 249 nonclustered indexs
True
False
Q56: Reseves space on the intermediate level of an index
FILLFACTOR
PAD_INDEX
Computed
Unique
Q57: ...............index stores data in a sorted manner
Nonclustered
Clustered
unique
Q58: A ...........is created on 2 or more columns. Both clustered index and nonclustered index can be ............
Composite Index
Full-Text Index
Xml Index
Unique Index
Q59: A ..............can be defined on a column with no duplicate values
Full-Text Index
Unique Index
XML Index
Q60: Too many Indexes descrease the performance of ............ (3 choices)
CREATE
INSERT
DROP
UPDATE
DELETE
Q61: Noncluster indexes do not physiscally rearrange the data in the database. and cluster index causes records to be physically sorted or sequential order
True
False
Q62: What option to reserve space on the leaf page of an index for adding additional data at a later stage?
FILLFACTOR
PAD_INDEX
Q63: An Index can have a max of ....columns
26
32
24
16
no limit
Q64: FILLFACTOR value ranges from 0-100?
True
False
Q65: Way to view index?
SP_helptext 'index_name'
sp_textindex 'table_name'
sys.sql_modules index_name'
sp_helpindex 'index_name'
sp_helpindex 'table_name'
Q66: A View is defined with an unique clustered index, is useful for queries that aggregate many rows, but is not suitable for tables that are frequently updated
Standdard View
Indexed View
Partitioned View
Q67: The properties of an Object such as a table or a view, are stored in special system data. These properties are........................
System Views
Metadata
Index
constraints
inserted,deleted
Q68: A View is created using horizontally partittioned data from one or more tables
indexed View
Partitioned View
Standard view
Q69: A View is created using column from one or more table and providing specific access to data for viewing and manipulation
Indexed View
Staddard View
Partitioned View
Q70: A View is created in everywhere databases
True
False
Q71: A View can be created on temporary table
True
False
Q72: Clustered index, nonlustered index can be created on the view
True
False
Q73: A View definition can not contain keywords: (choose 2 answers)
Insert
Compute by
Group By
Delete
Into
Q74: A View definition can not contain keywords: (choose 2 answers)
Update
Compute
drop
Default
Top..........Group by
Q75: A View may be have a FullText index
True
False
Q76: View can reference more than 1024 columns
True
False
Q77: The create view statement can be combined with other T-SQL statements in a single batch
True
False
Q78: DML Triggers are: (choose 3 answers)
Used to enforce business rule when data is modified in table or view
defined either at the Database or in the server level
defined at the database
executed after the table or view is modified
executed either while or after the table or view (data) is modified
Q79: A View can not reference a temporary table
True
False
Q80: View can retrieve information from xml data type using ........... method
Object_id()
value()
Return()
Q81: Prevents invalid data from being entered into the column
Validity
Data integrity
Business Rules
Constraint
Q82: Refers to validity and consistency of data
Data integrity
Constraint
Business Rules
Validity
Q83: Ensure that data lies within a specified type or range constraints
Validity
Business Rules
Constraint
Data integrity
Q84: A column with this constraint does not allow duplicate values, but allows null values to be inserted
primary key
check
foreign key
unique
Q85: Which statements are True? (choose 3 answers)
Constraints assist in ensuring data integrity
Triggers are values that you can define on a column or a group of column
Rules are the constraints that you can apply to multiple tables
Rules are independent of table definitions
Default are the codes automatically executed when a a specified event occurs
Q86: Entity Integrity are: (choose 3 answers)
Foreign key constraint
Primary key constraint
Unique constraint
index, Identity property
Check, Default constraint
Q87: Refers to policies and standards adhered to by an organization in running its operations
Data integrity
Constraint
Business Rules
Validity
Q88: Ensure that the values in a column are within a specified range
Referential Integrity
Domain Integrity
User-defined
Entity integrity
Q89: A column with this constraint does not allow null values or duplicate value to be inserted
Unique
foreign key
primary key
Q90: Refers to constraints defined by user
Referential Integrity
Entity Integrity
Domain Integrity
User-define Integrity
Q91: Domain integrity include: (choose 3 answers)
Primary key constraint, Default constraint
Foreign key constraint, Default constraint
Unique constraint
Check constraint
Not null definition, rules , Data types
Q92: Maintaining consistency of data across tables that related through common columns
Entity Integrity
Domain Integrity
Referential Integrity
Q93: In a table , when no two rows have the exact same values in all columns
Domain Integrity
Entity Integrity
Referential Integrity
user-defined
Q94: Which statements are true? (choose 3 answers)
A row in a table represents the instance of an entity
Referential integrity is implemented using UNIQUE constraint
A domain defines the range of values for columns in a table
User-defined integrity is maintained using default constraints
Default definitions specify default values for columns that do not accept null values
Q95: Maintains relationship between tables in a database, and Ensure consistency of data across related tables
User-define
Entity integrity
Referential Integrity
Domain Integrity
Q96: Which statements are true? (choose 2 answers)
The PRIMARY KEY constraint allows a null value to be entered once in a column
Constraints are used to ensure validity of data in a table and consistency of data across tables
A column specified with a UNIQUE constraint should have reference to the Primary Key column
The Foreign key constraint helps in maintaining referential integrity in the database
Q97: Ensures that each record in a table is unique
ser-define Integrity
Referential Integrity
Entity Integrity
Domain Integrity
Bản quyền thuộc về V1Study.com. Cấm sao chép dưới mọi hình thức!