Sql Interview Questions

Q What is the diffrence between delete and truncate?

A • 1)The deletion of each row gets logged in the transaction log, which makes it slow.

2)Truncate table deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, truncate table cannot be rolled back., but the table structure and its columns, constraints, indexes etc., remains as it is.

3)Truncate table is functionally identical to delete statement with no “where clause” both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete.

4)You cannot use truncate table on a table referenced by a foreign key constraint; instead, use delete statement without a where clause. Because truncate table is not logged, it cannot activate a trigger.

5)Truncate table may not be used on tables participating in an indexed view.

Q) What are basic SQL Optimization Tips ?

1)Use views and stored procedures instead of heavy-duty queries.
2)Try to avoid using SQL Server cursors, whenever possible.
3)Try to avoid using the DISTINCT clause, whenever possible.
4)Try to restrict the queries result set by using the WHERE clause.
5)Try to avoid the HAVING clause, whenever possible.
6)Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
7)If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.
8)Try to use constraints instead of triggers, whenever possible.
9)Use table variables instead of temporary tables.
10)Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
11)Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.

Q)What are the advantages of Stored Procedures?

1)Stored Procedures are stored in the database and there is no need to transfer the code from the client to the database server,this results in much less network traffic and again improves scalability.

2)Stored Procedures are pre-compiled and executes on the database server side which is likely to me more faster and powerful

Q)How SQL Performance Tuning using Indexes?

A) Indexes directly improve the performance of database applications and looks for a specific record or set of records with a join condition,where clause,group by and order by.