SQL Server Interview Questions - 14

1. The following index operations require no additional disk space :

A. ALTER INDEX

B. DROP INDEX

C. CREATE INDEX

D. All of the mentioned

Correct Answer is : All of the mentioned

2. Which of the following index operation require additional disk space ?

A. UPDATE INDEX

B. DROP INDEX MOVE TO

C. ALTER INDEX ADD CONSTRAINT

D. All of the mentioned

Correct Answer is : DROP INDEX MOVE TO

3. The SORT_IN_TEMPDB option cannot be set for ________ statements.

A. ALTER INDEX

B. DROP INDEX

C. CREATE INDEX

D. All of the mentioned

Correct Answer is : DROP INDEX

4. Which of the following query will disable the index on Employee table ?

A. ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode Employee

B. ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources

C. CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee

D. None of the mentioned

Correct Answer is : ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode Employee

5. SSMS provides different options to develop your SSIS package starting with ___________ wizard.

A. Import and Export

B. Copy and Import

C. Export and BulkInsert

D. None of the mentioned

Correct Answer is : Import and Export

6. Point out the wrong statement :

A. Prior to SSIS, Data Transformation Services (DTS) in SQL Server 2000 performs the tasks with fewer features

B. SSIS is used to perform operations like loading the data based on the need, performing different transformations on the data like doing calculations

C. Business Intelligence Development Studio (BIDS) is a tool which can be used to develop only SSIS packages

D. All of the mentioned

Correct Answer is : Business Intelligence Development Studio (BIDS) is a tool which can be used to develop only SSIS packages

7. Which of the following is the best practice related to SSIS ?

A. Plan for capacity by understanding resource utilization.

B. SSIS is an in-memory pipeline, so ensure that all transformations occur in memory

C. Baseline source system extract speed

D. All of the mentioned

Correct Answer is : Plan for capacity by understanding resource utilization.

8. If SSIS is not able to drive close to 100% CPU load, this may be indicative of:

A. Software Contention

B. Application contention

C. Processor contention

D. None of the mentioned

Correct Answer is : Application contention

9. Point out the wrong statement :

A. SSIS moves data as fast as your network is able to handle it

B. it is important to understand your network topology and ensure that the path between your source and target have both low latency and high throughput

C. Integration Services can be tuned beyond the speed of your source

D. All of the mentioned

Correct Answer is : Integration Services can be tuned beyond the speed of your source

10. The key counters for Integration Services and SQL Server are :

A. Process Bytes

B. Public Bytes

C. Private Set

D. All of the mentioned

Correct Answer is : Process Bytes

11. _________ represents to total memory pressure on the system in SSIS.

A. Page sec

B. Page Reads

C. Memory reads

D. All of the mentioned

Correct Answer is : All of the mentioned

12. The following Network perfmon counters can help you tune your topology :

A. Total Bandwidth

B. Current Bandwidth

C. Bandwidth bound

D. All of the mentioned

Correct Answer is : Current Bandwidth

13. What do you understand by design limitation in SSIS package ?

A. Making use of parallelism

B. Not making use of parallelism

C. Package uses few single-threaded tasks

D. None of the mentioned

Correct Answer is : Not making use of parallelism

14. ___________ represents how many network transfers per second are occurring.

A. Network Bytes

B. Network Transfers

C. Memory reads

D. None of the mentioned

Correct Answer is : Network Transfers

15. To increase this Rows / sec calculation in SSIS, you can do the following:

A. Improve drivers and driver configurations

B. Start single connections

C. Use single NIC cards

D. None of the mentioned

Correct Answer is : Improve drivers and driver configurations

16. Point out the correct statement :

A. If the network is your bottleneck, then a potential solution is to use single NIC card per server

B. To overcome limitations of drivers, you can try to start multiple connections to your data source

C. For 64-bit systems, at design time you may be loading 32-bit drivers; ensure that at run time you are using 32-bit drivers

D. None of the mentioned

Correct Answer is : To overcome limitations of drivers, you can try to start multiple connections to your data source

17. When you execute SQL statements within Integration Services, the following optimization can be made ?

A. If Integration Services and SQL Server run on the different server, use the SQL Server destination instead of the OLE DB destination to improve performance

B. In SQL Server 2008 Integration Services, there is a new feature of the shared lookup cache

C. If you cannot use commit size 1, use the highest possible value of commit size to reduce the overhead of multiple-batch writing

D. All of the mentioned

Correct Answer is : In SQL Server 2008 Integration Services, there is a new feature of the shared lookup cache

18. Which of the the following steps should be carried out using data types in SSIS for efficient results ?

A. Make data types as wide as possible so you will allocate more memory for your transformation

B. Do not perform excessive casting of data types

C. Do not watch precision issues when using the money, float, and decimal types

D. None of the mentioned

Correct Answer is : Do not perform excessive casting of data types

19. Point out the wrong statement :

A. Heap inserts are typically slower than using a clustered index

B. Use partitions and partition SWITCH command; i.e., load a work table that contains a single partition and SWITCH it in to the main table after you build the indexes and put the constraints on

C. Use the NOLOCK or TABLOCK hints to remove locking overhead

D. Use a commit size of <5000 to avoid lock escalation when inserting; note that in SQL Server 2008 you can now enable/disable lock escalation at the object level, but use this wisely

Correct Answer is : Heap inserts are typically slower than using a clustered index

20. Which of the following error message indicates that the package cannot run in SSIS ?

A. DTS_E_CANTINSERTCOLUMNTYPE

B. DTS_E_CACHELOADEDFROMFILE

C. DTS_E_CANNOTCONVERTBETWEENUNICODEANDNONUNICODESTRINGCOLUMN

D. DTS_E_CONNECTIONREQUIREDFORMETADATA

Correct Answer is : DTS_E_CANTINSERTCOLUMNTYPE

SQL Server interview questions part 14

SQL Server interview questions part 14

Take as many assements as you can to improve your validate your skill rating

Total Questions: 20

1. The following index operations require no additional disk space :

2. Which of the following index operation require additional disk space ?

3. The SORT_IN_TEMPDB option cannot be set for ________ statements.

4. Which of the following query will disable the index on Employee table ?

5. SSMS provides different options to develop your SSIS package starting with ___________ wizard.

6. Point out the wrong statement :

7. Which of the following is the best practice related to SSIS ?

8. If SSIS is not able to drive close to 100% CPU load, this may be indicative of:

9. Point out the wrong statement :

10. The key counters for Integration Services and SQL Server are :

11. _________ represents to total memory pressure on the system in SSIS.

12. The following Network perfmon counters can help you tune your topology :

13. What do you understand by design limitation in SSIS package ?

14. ___________ represents how many network transfers per second are occurring.

15. To increase this Rows / sec calculation in SSIS, you can do the following:

16. Point out the correct statement :

17. When you execute SQL statements within Integration Services, the following optimization can be made ?

18. Which of the the following steps should be carried out using data types in SSIS for efficient results ?

19. Point out the wrong statement :

20. Which of the following error message indicates that the package cannot run in SSIS ?

Similar Interview Questions

Search for latest jobs

For Employers

For Partner

For Jobseekers

Help

Follow Us

snaprecruit

Download mobile appsJust Swipe to apply for jobs. Try it out !