Max Number of indexes Per Table

In sql server 2005 
Clustered Index 1 + Non clustered index 249 = 250 index

 In sql server 2008/2008 R2/2012

 Clustered Index 1 + Non clustered index 999 = 1000 index

Reference: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.110).aspx

Truncate can/can't be roll backed!!!!!!!!!!!!!

One major difference(its partially myth) between truncate and delete is that truncate can't be rolled back while delete can be rolled back.

But its  a myth .Truncate can also be rolled back but only if it is in Transaction statement.I will try to explain with some example .

 Lets a table users.

Case 1:

BEGIN TRAN

  TRUNCATE TABLE users    

  ROLLBACK

END TRAN

Now execute -

SELECT * FROM Users ; will return all data .

Obviously DELETE will return same result means all data will be returned.

Case 2:

Now execute 

TRUNCATE TABLE users    

and then execute immediately 

ROLLBACK  (here rollback without begin transaction)

Then execute 

SELECT * FROM Users ; NO DATA will be returned,means here table is not rolled back

But DELETE can be rolled back in this situation

Now execute 

DELETE TABLE users    

and then execute immediately 

ROLLBACK  (here rollback without begin transaction)

Then execute 

SELECT * FROM Users ;  All data will be returned i.e. table rolled back successfully.

Conclusion:

TRUNCATE can be rolled back when used with in TRANSACTION,while DELETE can be rolled back either used within transaction or outside transaction block.

Find nth Highest salary(value) from table

Presume: Let there is a table name Employee and it has a field name salary(bigint).
Problem: I want to get 5th(nth) highest salary.
Solution:Here is the query to retrieve 5th highest salary from employee table


                      SELECT TOP 1 Salary FROM Employee WHERE Salary IN
                              (SELECT TOP 5 DISTINCT( Salary) FROM Employee ORDER BY  Salary DESC )
                                ORDER BY Salary ASC

Note: Above query can be generalized to retrieve nth highest or lowest record from any table.

Delete duplicate row from table

To delete duplicate row from table execute this query.

DELETE FROM MyTable WHERE AUTOID NOT IN 

(SELECT MIN(AUTOID) 
 FROM MyTable  GROUP BY col1,col2) 

Where autoId is  auto increment id.

Script for swapping gender in sql server through CASE (Without Using Cursor)

I want to share you my experience about an interview where i was asked to write script for swapping gender using cursor and without using cursor.
   In my previous post I mentioned "Script for swapping gender through CURSOR" .Here I am sharing swapping gender using CASE .

Step 1: First of all create a table ,here "genderTest"

            CREATE TABLE  genderTest (id nvarchar(20) PRIMARY KEY,empname nvarchar(20),gender           nvarchar(1))

Step 2:Now insert some values in table

       INSERT INTO gendertest VALUES ('1','ashish','m')
       INSERT INTO gendertest VALUES ('2','nandani','f')
       INSERT INTO gendertest VALUES ('3','chandrasen','m')
       INSERT INTO gendertest VALUES ('4','priynka','f')
       INSERT INTO gendertest VALUES ('5','sonam','f')
       INSERT INTO gendertest VALUES ('6','guru','m')
       INSERT INTO gendertest VALUES ('7','faisal','m')
       INSERT INTO gendertest VALUES ('8','priti','f')

Step 3: Execute following query


           UPDATE genderTest 
           SET gender=
          CASE gender
          WHEN 'f' THEN 'm'
          ELSE 'f'
      END
   Above script will swap gender.
    

Technology Improver: Script for swapping gender in sql server through Cursor

Technology Improver: Script for swapping gender in sql server through Cursor