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

Script for swapping gender in sql server through Cursor

Step 1:Create table ,here GenderConvert

--create table GenderConvert(name nvarchar(20),gender nvarchar(1))

Step 2:Insert some Values in table

--insert into genderconvert values('ashish', 'm' )

--insert into genderconvert values('sonam', 'f' )

--insert into genderconvert values('priya', 'f' )

--insert into genderconvert values('sonu', 'm' )

--insert into genderconvert values('anshu', 'f' )

--insert into genderconvert values('vinod', 'm' )

--insert into genderconvert values('pappu ', 'm' )

--insert into genderconvert values('pavan', 'm' )

--insert into genderconvert values('priynka', 'f' )

--insert into genderconvert values('sonali', 'f' )

--insert into genderconvert values('guru', 'm' )

STEP 3:Execute this script (cursor)

DECLARE @name nvarchar(20)

DECLARE @gender nvarchar(1)

DECLARE @MyCursor CURSOR

--Assign table to cursor variable

SET @MyCursor = CURSOR FOR 

SELECT name,gender FROM genderconvert

--Open cursor

OPEN @MyCursor

--Fetch cursor row on by one

FETCH NEXT 

FROM @MyCursor INTO @name , @gender

WHILE @@FETCH_STATUS = 0

BEGIN

if(@gender = 'm' )

BEGIN 

UPDATE  genderconvert set gender='f' where name=@name

END

else

BEGIN 

UPDATE  genderconvert set gender='m' where name=@name

END

FETCH NEXT FROM @MyCursor into  @name,@gender

END

           After executing this script gender will be swap.Please give your valuable review if this post helpful for you.

CURSOR IN SQL SERVER 2008

-----------     Cursor    -------------------

            Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.

            Some time we need to do operation on a particular row of result set or small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.

           

To use cursors in SQL procedures, you need to do the following:

1:Declare a cursor that defines a result set.

2:Open the cursor to establish the result set.

3:Fetch the data into local variables as needed from the cursor, one row at a time.

4:Close the cursor when done

5:Deallocate the cursor

1:DECLARE CURSOR(T-SQL)

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]

     [ FORWARD_ONLY | SCROLL ]

     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

     [ TYPE_WARNING ]

     FOR select_statement

     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

[;]

LOCAL:

         Scope of Local cursor is only to batch , stored procedure , or trigger in   which it is declared. And LOCAL cursor automatically deallocated when container block ,sp or trigger terminated.

GLOBAL:

         Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is     onl   implicitly    deallocated at disconnect.

FORWARD_ONLY

          - Specifies that cursor can only fetch data sequentially from the first to the last row. FETCH NEXT is the only fetch option supported. It is the fastest cursor that you can update.

             The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor. Because the cursor cannot be scrolled backward, changes made to rows in the database after the row was fetched are not visible by using the cursor.                                                                
 Note: If cursor is not declared forward-only OR scroll  , then by default it is forward-only.
STATIC: Defines a cursor that makes a temporary copy of the data in tempdb to be used by the cursor. Therefore modification made to base table does not reflect to temporary table , and this cursor does not allow modification.
DYNAMIC: Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The FETCH ABSOLUTE option is not supported with dynamic cursors.
FAST_FORWARD: Specify forward-only , read-only cursor. FAST_FORWARD can not be applied if SCROLL or  FOR_UPDATE is specified.
READ_ONLY:  Prevent updates made through this cursor.
SCROLL_LOCK: specify that updation  or deletion made through this cursor guarantee to succeed. Because SQL server lock the corresponding row.  
OPTIMISTIC: Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor.
TYPE_WARNING - Specifies that if the cursor will be implicitly converted from the requested type to another, a warning message will be sent to the client.

FOR UPDATE [OF column_name [,...n]]

            Defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

2:OPEN   CURSOR 

Syntax for opening cursor is

Open <cursor_name>

3:FETCH ROW FROM CURSOR

FETCH [NEXT | PRIOR | FIRST | LAST |ABSOLUTE{n}|RELATIVE{n}]                                                                      [FROM]    <cursor_name>   [INTO @variable_name[1,2,…n variable]]

Let us define the terms used in syntax---

NEXT: Returns the immediate  next row .And increment current row pointer to  that row.

PRIOR: Returns the immediate  previous row .And increment current row pointer to  that row. 

FIRST: Returns the first row in the cursor and makes it the current row.

LAST: Returns the last row in the cursor and makes it the current row.

ABSOLUTE{ n}          :Return nth row of result set. If n is positive, counting start from top of result set .If n is negative , counting start from bottom of result set. In both situation returned row is made as new current row. If n is 0 no row is returned.

REALTIVE { n }: Same           functionality as absolute , but only difference is that counting start from current row ,forward of backward ,according to sign of n.                                               

NOTE:

J If either FORWARD_ONLY or FAST_FORWARD is specified, NEXT is the only FETCH option supported.

JIf DYNAMIC, FORWARD_ONLY or FAST_FORWARD are not specified, and one of KEYSET, STATIC, or SCROLL are specified, all FETCH options are supported.

JDYNAMIC SCROLL cursors support all the FETCH options except ABSOLUTE.

4: CLOSE CURSOR

 Closes an open cursor by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned. CLOSE leaves the data structures available for reopening, but fetches and positioned updates are not allowed until the cursor is reopened. CLOSE must be issued on an open cursor; CLOSE is not allowed on cursors that have only been declared or are already closed.

SYNTAX:

       CLOSE   <cursor_name>

5: DEALLOCATE CURSOR

Removes a cursor reference. When the last cursor reference is deallocated, the data structures comprising the cursor are released by Microsoft SQL Server.

SIMPLE EXAMPLE:

DECLARE @a  nvarchar(50)

DECLARE @myCursor  CURSOR

SET  @myCursor=CURSOR FOR SELECT TOP 10  propertyid FROM properties

OPEN @myCursor

FETCH NEXT

FROM  @myCursor into @a

WHILE @@FETCH_STATUS =0

BEGIN

PRINT @a

FETCH NEXT

FROM  @myCursor into @a

END

CLOSE @myCursor

DEALLOCATE @myCursor

WHAT @@FETCH_STATUS DO HERE?

            @@FETCH_STATUS              is called cursor function, which is in build function.

It return the status  of last cursor FETCH statement.

If it is 0; the FETCH statement was successful

If it is -1; failed or row was beyond the result set

If it is -2;the row fetched is missing