The answers to these questions are best guesses of the contributor and may not be correct

80

CREATE TABLE is used …
  • For creation of tables and constraints

Books-online: CREATE TABLE statement

 

81

How will you delete a column?
  • Column cannot be deleted with the delete command - only rows

 

62

How shall a column be referenced in INSERT? (Choose 2)

????

  • Column that allows NULL or has default needn't be referenced
  • Default values clause in insert statement will insert the values of a default bound to a column or the statement inserts NULL if the columns allows null and there is no default.
  • timestamp and identity column can be explicitly referenced
  • Column not explicitly reference in Insert statement will automatically have a null value

 

18

How to use the INSERT statement with an identity column?
  • Using column name
  • Using IDENTITYCOL
  • @@IDENTITY
  • leave out of inset list

 

 

 

Indexes & Using Views, Defaults, and Rules

 

125

What is the reason for creating an Index?
  • Column is used for sorting results.
  • Quick search.
  • For data specified in the WHERE clause.

 

 

41

There is one organization, which deals with bank transaction. All account heads are stored in ACCT_HEAD, while all transactions are stored in DAILY_TRAN. There is heavy traffic involved in sales. Since it involves an online update the ACCT_HEAD is ….

????

  • Unique clustered index on ACCT_HEAD

 

42

Data and leaf pages are one and same in case of:
  • Clustered index

 

 

43

There is file Reservation which involved in on-line reservation, cancellation and booking . There is already an non-clustered index existing. Due to heavy transaction it is taking a very long time to insert the data. What is the best way to solve?

????

  • Drop the index , create it again with low fill factor

 

93

The advantage of composite index is …
????
  • When the first key is used with the where clause the index is used

 

48

______ page is unaffected by fillfactor.
  • Data

 

 

115

What type of lock is created when an Index is created?
  • An exclusive lock is created when a clustered index is created.
  • A shared lock is created when a non-clustered index is created.

 

 

44

The 2 ways to force an index reference to the option is?
  • (index = ind_number) in from clause
  • (index = ind_name) in from clause

 

29

What happens to the data in a table when a clustered index is created?
  • The data is sorted and becomes a part of the index at the leaf page
  • The data is sorted and put on a device separate from the index
  • The data is scanned and pointed to each data page that are create at the leaf page

 

31

???

You are working with as application that contains customer table and sales items table, most of the traffic in sales items involves searching customer records and there are 100 to 10000 rows per record. Although a high number of rows are often in

or

A scenario question where 100-1000 rows get entered at a time, with NO changes after info entered. The table is sales items for and order. You are inserting rows into sales items. Create index on sales items.

????

  • Clustered index with high fill factor
  • Clustered index with low fill factor
  • Non clustered index with high fill factor
  • Non clustered index with low fill factor

 

33

A composite index of 200 bytes width is created. The first logical column in the index has very few unique data, whereas the second column has many. How can you change the index so that the performance increases?

  • Make the second column as the first logical column and decrease the width of the index
  • decreasing key length & not changing order of columns in key
  • leave length same and make second column first in index
  • can't do anything

 

34

How does SQL Server implement PK constraint in a CREATE TABLE ?
  • by creating a unique index or unique nonclusted index on the columns identified by the constraint
  • by creating a unique index on the column identified in the constraint and then by executing the sp_primarykey stored procedure

 

18

How to encrypt a view?
  • CREATE VIEW view_name

….

WITH ENCRYPTION

AS select_statement

[WITH CHECK OPTION]

 

 

 

127

How can you hide the view definitions?
  • Use the WITH ENCRYPTION option

 

5

How data is store in a view?
  • data is not stored in a view

 

32

Which statements are true about binding and unbinding defaults?
  • Default can be bound to future users to a user-defined datatype and current users of datatype can be ignored
  • A default cannot be bound to a column if another default exists on the column

 

24

What is true about DEFAULTS?
  • A default automatically unbinds from all columns and datatypes when default is dropped
  • A default is automatically dropped when a table to which the default is bound is dropped
  • Doesn’t get deleted when a table is dropped

 

6

You create a default on a column. How do you use this with data modification statements? (select several)
  • UPDATE statement with column no mentioned
  • Use DEFAULT in values list for INSERT
  • Use DEFAULT in set list for UPDATE
  • Use DEFAULT VALUES for INSERT

 

65

Where all Defaults can be used?
  • Insert table DEFAULT VALUES
  • Update Table
  • Set column_name = DEFAULT

 

 

 

27

What is TRUE about RULE?
  • The variables used needs to be declared using the DECLARE statement
  • A rule can be created, bound to a column, and executed in the same SQL batch
  • Rules bound to a column cannot be dropped
  • A rule bound to a specific column overrides the rule bound to the user-defined datatype on which the columns is based
  • A rule that is not compatible with the datatype of the column will be rejected at the time of binding

 

10

Will Rules be bound automatically to all tables with user-defined datatype after Rule has been bind to the datatype?
  • Rule will be effective for all future tables that have columns with that particular user-defined datatype
  • Rule will be effective for all tables that have columns with that particular user-defined datatype

 

97

A rule is bound to a datatype, so …
  • All existing table that have the datatype will be effected.

 

99

I have created a view on a table, dropping the table will result in
  • View will not be dropped but will result in an error when used

 

3

Which object is dropped when a table is dropped?
  • Index
  • Rule
  • Stored procedure
  • View

 

83

If a table is dropped, which is getting dropped automatically?
  • Index
  • Constraints
  • Rule
  • Triggers

 

1

Rules and triggers are enforced on:
  • Slow bcp
  • Fast bcp
  • Both
  • None

 

Triggers & Programmability

30

How can you access the data being modified during the execution of a trigger?
  • Access the rows already modified in the deleted table to see the version of the rows before modification, and the inserted table to see the version of the rows after modification.

 

130

How can you use triggers to maintain data integrity?
  • With the help of the audit and summary tables.
  • Use trigger to enforce integrity prior to data modification
  • Use trigger to enforce business rule
  • Use trigger to detect duplicate in unique column without rolling back

 

64

Where are the modified data stored when a update trigger is executed?
  • deleted, inserted
  • delete, insert
  • table, insert
  • delete, table

 

12

Where is the location inserted and deleted tables?
  • master database
  • tempDB
  • memory
  • current database

 

 

102

Triggers get executed in response to a ________ getting modified
  • Table

 

103

How does SQL server allow you to take a corrective action?
  • Triggers

 

104

Whenever corrective action needs to be taken and business rules is to be used …
  • Triggers needs to be used

 

35

How do you ensure business rules take corrective action?
  • Trigger

 

38

Triggers are best used for?
  • Modification of row
  • Modification of table
  • Modification of column

 

23

When does a trigger fire?
  • Automatically after the table modification
  • Before the table modification

 

28

Triggers are created for data integrity and operate at:
  • Table level
  • Column level
  • Row level

 

8

How is EXECUTE statement used other than executing stored procedure?
  • System Function
  • Method of view
  • Command stored inside variable
  • Executable files

 

39

The c1 cursor is based on customer table. Which UPDATE statement should you use to update the current row in c1?
  • UPDATE customer SET ytd_sales = 0 FROM c1 WHERE CURRENT_ROW
  • UPDATE customer SET ytd_sales = 0 WHERE CURRENT OF c1
  • UPDATE customer SET ytd_sales = 0 FROM CURRENT OF c1
  • UPDATE customer SET ytd_sales = 0 FROM c1

 

40

Using FETCH NEXT statement you are retrieving a row. Now delete the row returned by the FETCH statement.
  • DELETE table_name WHERE CURRENT OF cursor_name

 

63

How are user defined stored procedure created and stored as database objects?

????

  • Written as rows to system tables in the user defined db
  • Allocated space in extents (???????)

 

 

95

How can I achieve maximum security when I have a stored procedure?
  • Remove all permission on related object and give exec permission to the procedure

 

111

Stored procedures are a collection of …
  • Precompiled SQL statement
  • Is a database object

 

112

Sending error message to client with column name in message
  • RAISERROR (50001, col_name())
  • RAISERROR (50001, 16, 1, @col_name)

 

 

113

If @objname references a table name, what happens with the folllowing statment:

exec ('exec sp_spaceused ' + @objname + ", @updateusage = true')

  • will execute OK

 

Replication & Application Development and Open Data Services

98

Collection point for all public data is …
  • Distribution server
  • Publishing server
  • Subscription server
  • SQL server

 

4

In a Central Subscriber scenario, how is data integrity maintained?
  • Composite key with location specific column
  • Use triggers to update information in the subscriber database
  • Replicate data to 4 different tables on subscriber

 

113

How does subscription server maintain data integrity?
  • By receiving articles as one unit

 

12

How do you maintain data integrity for replication when there are multiple publication servers and a single subscription server scenario? (choose two)
  • Use subscription option that allows you to update anywhere
  • Use location specific primary key
  • Update data on publications only
  • Set database option on subscriber to read-only

 

72

How does the publisher maintain the data integrity?
??
  • By maintaining the data as Read only on all the subscribers.
  • By sending articles

 

129

You are working on an environment that has heavy online transaction processing. How should you manage the distribution process to ensure the workload is reduced?
  • Set aside a separate server for the distribution and another for the subscription

 

131

You are copying data from a mainframe through bcp. It has to be sent to 20 SQL servers on the network. What has to be done?
  • Replication through Horizontal Partitioning.

 

132

When does synchronization happen?
??
  • Only the first time when you subscribe to a publication.

 

131

You are in the middle of a replication process, and the transaction log on the publisher becomes full. What should be done?
??
  • Execute sp_repldone
  • DUMP TRANSACTION dbname WITH NO_LOG
  • Unsubscribe and subscribe subscribers

 

 

106

You have 2 hours in hand to update the transaction. What will you do?
??
  • Replication with loose consistency

 

70

Mail feature of SQL Server is implemented by using…
  • Extended stored procedure
  • NT Mail
  • xp with NT mail API
  • SQL Executive Service using xp and NT Mail

 

112

You access the SQL server with an application using DMF. Which one of the following are you allowed to access?
?????
  • The alert manager and the task manager through the SQL -Executive.
  • The Mail API with Microsoft Mail.
  • Extended stored procedure using Windows NT.

 

71

What is ODBC?
  • Client side API used by window application.

 

105

Multiple servers are managed by SQL server using …
  • SQL Enterprise manager

 

88

How can OLE automation objects be used?
?????
  • With DMO and with VBA

 

61

What shall be included to take advantage of OLE?

????

  • Properties and methods of SQL DMO
  • Extended stored procedures calls
  • n/w libraries
  • call level API

 

68

DMO object is used for?
  • 32 bit OLE Automation programs registered in the Windows NT registry.
  • Data Administering of SQL Objects using Microsoft Access, Excel and Visual Basic

 

 

7

How do you use SQL-DMO to schedule tasks and alerts?

????

  • Use extended stored procedures
  • Start SQL monitor and use his properties and methods
  • Use properties and methods of alert objects and task objects
  • Use properties and methods of SQL Executive object

 

123

What is DMO (Distributed Management Object)?
  • It is an API which help in developing Applications for other operating systems.
  • It is Server side API used to develop applications using c++ and MS Visual Basic
  • Functions and objects used to develop applications for administrative functions.
  • Used to develop applications like MS Excel and MS Access to access data from SQL-Server.

 

87

DMO objects to enable affecting tasks & events
  • Use alert & task objects of SQL executive

 

128

You want to take advantage of DMO objects what will you do?
?????
  • Use OLE Automation with VB and Excel

 

 

1