Floating FB popout byF5debug

Interview Questions and Answers on .Net Framework, OOPS, ASP.Net, C#.Net, SQL Server, WCF Series – Part 30


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
Primary key creates clustered index  Unique key creates non clustered index
Can have only 1 Primary key in a table Can have many Unique key in a table
It cannot contain NULL values Can have NULL values, even more than 1 null values


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

Does not reset the identity of the table Resets identity of the table
Its DML Command Its DDL Command
It can be rolled back It cannot be rolled back
Its Slower (Removes row one by one) Its Faster (Uses Fewer systems)
WHERE Condition can be used WHERE Condition can’t be used
Records entry in transaction log for each deleted row Removes the data by de-allocating the data pages
Trigger can be activated Trigger can’t be activated


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
can have only one clustered index on a table we can have as many non-clustered indexes(255)
The leaf level of a clustered index is theactual data non-clustered index the leaf level is actually a pointer to the data in rows
The RowLocator in Clustered Index is the clustered Index key The row locator in Non Clustered Index is a pointer to the row. ROW ID (RowLocator)= file identifier + page number + row number on the page
It doesn’t allow null values It allow one null values
Assigned for primary key Assigned for unique key
Cluster index exists on the physical level They are not created on the physical level but at the logical level
It sorts the data at physical level It does not sort the data at physical level
A clustered index requires no separate storage than the table storage requires separate storage than the table storage to store the index information


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.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

  • Comment
  • Preview

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters


This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter