databases Online Quiz - 148

Description: databases Online Quiz - 148
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0

What are the situations when an index might not be picked up?

  1. Leading column in the index is not part of the search arguments

  2. Data types are not matching while doing a join

  3. Optimizer decides that table scan is better

  4. Index name is improper

Correct Option: A,B,C

Microsoft recommends a clustered index in every SQL Server table

  1. True

  2. False

Correct Option: A

A column in a table, having millions of rows, contains the name of States in India. It is defined as CHAR(50). What performance improvements can be made with respect to the data type?

  1. Change it to CHAR(10)

  2. Change it to VARCHAR(50)

  3. Change it to NVARCHAR(50)

  4. Change it to CHAR(100)

Correct Option: B

ABC Corp is an organization, where company raises purchase orders, process payments, captures transaction and reconciles the information. The products involved in this are (choose all applicable)

  1. Istore

  2. GL

  3. Cash Management

  4. Purchasing

  5. Inventory

  6. Order Management

Correct Option: B,C,D,E

Numeric columns are best suited to define an index (for faster access)

  1. True

  2. False

Correct Option: A

In a SQL Server 2005 query plan, the presence of which of the below items are indicators of potential performance issues?

  1. Index scan

  2. Table scan

  3. Bookmark lookup

  4. Index seek

Correct Option: A,B,C

During performance tuning, it is imperative that we have data regarding the pain points (slow performing queries, high CPU utilizing queries, etc.). What tools are useful in getting these info?

  1. Dynamic Management Views (DMV)

  2. DBCC commands

  3. Query plans

  4. BCP

Correct Option: A,B,C

In a query, Table_A is joined with Table_B based on the join criteria (Table_A.Column_1 = Table_B.Column_1). The SME in the team mentions that Table_B is the child of Table_A based on Column_1 (one-to-many relationship) and should always have value. Given these considerations, which of the following doesn't apply to this situation?

  1. There needs to be a Foreign Key relationship between the two tables.

  2. Table_B.Column_1 shouldn't have NULL values.

  3. There should be a clustered index on Table_A.Column_1 and a non-clustered index on Table_B.Column_1.

  4. Table_B.Column_1 should be unqiue.

Correct Option: D

In Accounts_History table, among many columns, I have the Account_ID (Primary Key) and Trans_Date. There is a query that runs frequently to know the latest transaction that was done on the account. Which one of the below approaches will be most beneficial in this case?

  1. Defining a clustered index on Account_ID

  2. Defining a clustered index on Account_ID, Trans_Date

  3. Defining a non-clustered index on Account_ID, Trans_Date

  4. Don't define any indexes

Correct Option: B

There are 10 tables with similar structure but mutually exclusive records. A query needs to fetch some records from each of these tables. Which of the following is the best approach to do this?

  1. Use UNION between the 10 SELECT statements

  2. Execute separate SQL statements and let the front-end merge the results

  3. Use UNION ALL between the 10 SELECT statements

  4. Define a temporary table and populate it with data from the 10 SELECT statements and then do a single SELECT from this table

Correct Option: C

Always use hints while writing queries (e.g. WITH NOLOCK, WITH UPDLCK) since it will help the query optimizer

  1. True

  2. False

Correct Option: B

Please see the below query: create table employee (emp_id number(6) primary key, emp_name varchar2(20)); rollback; What will the result of the below query: desc employee;

  1. It will describe the table.

  2. It will return ar error:'Table or view does not exist'

  3. The query is not correct.

  4. None of the above

Correct Option: A

We can use group by clause with:

  1. Non aggregate functions only

  2. Aggregate functions only

  3. Both 1 & 2

  4. None of the above

Correct Option: B

Which of the below is the correct query to update the columns of a table:

  1. update table TABLE_NAME set COMLUMN_NAME= where

  2. update TABLE_NAME set COMLUMN_NAME= where

  3. Both 1 & 2 are correct

  4. None of the above

Correct Option: B

UNION query returns only distinct rows and UNION ALL may return duplicates.

  1. True

  2. False

Correct Option: A

Bitmap index is suitable for the tables having huge and repeatative(duplicates) data.

  1. True

  2. False

Correct Option: A

SPLIT function is available in SQL server 2005?

  1. True

  2. False

Correct Option: B

DDL means?

  1. Data Dynamic Link

  2. Data Description Language

  3. Data Definition Language

  4. Data Definition Link

Correct Option: C

Which inbuilt SP is used to parse the values from XML document

  1. sp_xml_postdocument

  2. sp_xml_readdocument

  3. sp_xml_loaddocument

  4. sp_xml_preparedocument

Correct Option: D

Which command is used to Check integrity of a table or indexed view?





Correct Option: B
- Hide questions