Question 291 – What is Log shipping?

• It is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server.
• Enterprise Editions only supports log shipping.
• In log shipping the transactional log file from one server is automatically updated into the backup database on the other server.
• If 1 server fails, the other 1 will have the same db and can be used as the Disaster Recovery plan.
• The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.
• This in effect keeps the two SQL Servers in “synch”. Should the production server fail, all you have to do is point the users to the new server, and you are all set.

Question 292 – What is an Index?

• An index is a physical structure containing pointers to the data.
• Indices are created in an existing table to locate rows more quickly and efficiently.
• It’s possible to create index on one or more columns of a table, and each index is given a name.
• The users cannot see the indexes; they are just used to speed up queries.
• Effective indexes are one of the best ways to improve performance in a database application.
• A table scan happens when there is no index available to help a query.
• Table scans are sometimes unavoidable, but on large tables, it has a terrific impact on performance.
• A table can have one of the below indexes combinations
• No Indexes
• A clustered index
• A clustered index and many non-clustered indexes
• A non-clustered index
• Many non-clustered indexes

Question 293 – How to Create an Effective Index?

• Use the following guidelines to help create efficient indexes
• Create indexes based on use.
• Keep clustered index keys as small as possible.
• Consider range data for clustered indexes.
• Create an index on all foreign keys.
• Create highly selective indexes.
• Consider a covering index for often-used, high-impact queries.
• Use multiple narrow indexes rather than a few wide indexes.
• Create composite indexes with the most restrictive column first.
• Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
• Remove unused indexes.
• Use the Index Tuning Wizard.

Question 294 – What are the Types of Indexes available?

• Clustered Index
• Non Clustered index
• Primary Key index
• Unique index
• Bitmap index
• Hash index
• Function Based index
• B-Tree index
• Virtual index
• Composite index
• Covering index

Question 295 – What is the difference between Primary Key and a Unique Key?

Primary Key

Unique Key

 
Question 296 – What is the difference between Delete and a Truncate?

Delete

Truncate

Question 297 – What is a Clustered Index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. The leaf nodes of a clustered index contain the data pages. Clustered index is unique for any given table

Question 298 – What is a Non Clustered Index?

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Question 299 – What is the difference between Clustered and Non Clustered Index?

Clustered Index

Non Clustered Index

Question 300 – What is BCP used in SQL?

• BCP (Bulk Copy Program) is a command line utility by which you can import and export large amounts of data in and out of SQL SERVER database.
• To import or export a set of columns WHERE clause can be used with bcp commands and all the conditions can be mentioned in the query to generate the set of rows you want to copy.

Disclaimer – F5debug Interview Questions & Answers Series:

You may recopy extracts from these pages (“the material”) to individual third party websites or to any intranet websites, but only if:
You acknowledge www.f5debug.net as the source of the material. Such acknowledgment should include reference to www.f5debug.net in the copy of the material and should also include “© Karthikeyan Anbarasan, www.f5debug.net “. You inform the third party that these conditions apply to him/her and that he/she must comply with them.