.Net Material

SELECT max(sal) FROM  tbltemp WHERE sal NOT IN (SELECT distinct top 2 sal FROM tbltemp ORDER BY sal desc) for eg. for 3rd highest top will be 3-1 =2  (top 2) therefore top N-1 Or SELECT TOP 1 sal  FROM (                SELECT DISTINCT TOP 2 sal                FROM tbltemp               ORDER BY sal DESC               ) A ORDER BY sal

-To fetch row by row data Declare Cursor Open Fetch first time fetch data until @@Fetch_Status = 0 Close Deallocate Disadvantage: - Time Consuming - required lot of resources & temporary storage.

Referred as "Virtual Table" Can not store data (except for indexed views) rather than only referto data present in table. e.g. Create View vwsample [optional] With Encryption OR With Schemabinding [/optional] AS Select Cid, CName FRom Customer GO With Encryption - You can see view query (its encrypted) With Schemabinding - You are not able to alter table on which it's created.

Definition: Are special type of stored procedure that are defined to execute automatically, In place of or after data modification when Insert, Update, Delete triggering actions occurred on that table. 1) After Trigger Fired the triggering action. Executed automatically before the transaction is committed or rolled back. e.g. CREATE TRIGGER trgCheckStock ON [products] FOR UPDATE AS IF( Select inStockFrom inserted ) 2) Instead Of Trigger Fire in place of the triggering action. Executed automatically before primary key and foreign key constraints are checked. e.g. CREATE TRIGGER trgCantDelete ON table1 INSTEAD OF DELETE AS PRINT 'you cannot delete this data' GO

Truncate 1) Delete all rows. 2) Identity counter reset. 3) Don't make entry to transaction log. therefore it is faster. 4) DDL command. 5) can not rollback 6) do not activate trigger. Delete 1) Delete 1 or more row depend on where clause. 2) retain Identity counter. 3) Make entery to transaction log. so it is slower. 4) DML command. 5) Can be rollback. 6) Activate trigger.

Stored Procedure 1) If error occurred then execute next statement. 2) Use EXECUTE or EXEC to run.3) Can return values not mandatory. 4) SP can not be called from UDF. 5) Have transaction. 6) Can use XML FROM Clause. UDF 1) If error occurred then can not execute next statement. 2) Execute from sql SELECT or SQL action queries. 3) Must return 1 Value or table. 4) UDF can be called from SP. 5) Don't have transaction because don't have DML queries. 6) Can't use XML FROM Clause.