- 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.
- 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
- 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.
- 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
|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|
|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|
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
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.
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|
- 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.