Difference between Truncate And Drop

TRUNCATE TABLE:

TRUNCATE will reset any identity columns to the default seed value. This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns. After TRUNCATEing your table, when you insert a new record into the empty table, the identity column will have a value of 1. DELETE will not do this.

What are triggers? How to invoke a trigger on demand?

               Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. . It can be used to maintain referential integrity. A trigger can call stored procedure.

Where SQL Server Actually Store Data

SQL server by default stores the data in the files with extensions .MDF and .LDF under the path 
"C:\Program Files\Microsoft SQL Server\MSSQL\Data\"

What are the Global Temporary Tables

We can create global temporary tables but these are not using much in sql an the name of these table start with two pound signs. For example, ##interviewqsn is a global temporary table.As the name suggest these table is Global temporary tables and visible to all SQL Server connections. When we create any one of these all users can see it.

How can you raise custom errors from stored procedure ?

The RAISERROR statement is used to produce an ad hoc error message or to retrieve a
custom message that is stored in the sysmessages table. You can use this statement with
the error handling code presented in the previous section to implement custom error

How can you increase SQL performance ?

Following are tips which will increase your SQl performance :-


√ Every index increases the time in takes to perform INSERTS, UPDATES and
DELETES, so the number of indexes should not be very much. Try to use
maximum 4-5 indexes on one table, not more. If you have read-only table,
then the number of indexes may be increased.