ROLLUP in SqlServer

ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy. 

CREATE TABLE [dbo].[Emp](

      [EmpID] [varchar](5) NULL,

      [EmployeeName] [varchar](50) NULL,

      [Amount] [decimal](18, 2) NULL

) ON [PRIMARY]

GO

INSERT emp(EmpID,EmployeeName,Amount)       VALUES('100','Shan.R','50000.00')

INSERT emp(EmpID,EmployeeName,Amount)       VALUES('101','Ramya','20000.00')

INSERT emp(EmpID,EmployeeName,Amount)       VALUES('102','Raju','90000.00')

INSERT emp(EmpID,EmployeeName,Amount)       VALUES('103','Karthi','5000.00')

INSERT emp(EmpID,EmployeeName,Amount)       VALUES('104','Suresh','6000.00')

Go

SELECT

CASE WHEN EmpID IS NULL THEN 'Total' ELSE EmpID END AS EmpID ,sum(Amount) AS  Amount FROM Emp

GROUP BY EmpID

WITH ROLLUP

Go

Result:

The Size Of All Tables In a SQL Server Database

     Ever wonder how big a table really is in your database? You know there are a million rows in the table, but how much space is that really taking?

   SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes. which might surprise you.

-- DB size.
EXEC sp_spaceused

-- Particular one table size.
sp_spaceused 'Employee'

-- Table row counts and sizes.

CREATE TABLE TableSize
( 
    TableName VARCHAR(150),
    RowsCount CHAR(11),
    Reserved VARCHAR(18), 
    Data VARCHAR(18), 
    Index_Size VARCHAR(18),
    Unused_Size VARCHAR(18)
) 

INSERT TableSize EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

-- Select all table with size

SELECT * FROM TableSize 

-------------------------

Calender

Magic Table in SQL Server

In SQL server magic table is nothing more than an internal table which is created by the SQL server to recover recently inserted, deleted and updated data into SQL server database. That is when we insert or delete any record from any table in SQL server then recently inserted or deleted data from table also inserted into inserted magic table or deleted magic table with help of which we can recover data which is recently used to modify data into table either use in delete, insert or update to table. Basically there are two types of magic table in SQL server namely: inserted and deleted, update can be performed with help of these twos. Generally we cannot see these two table, we can only see it with the help Trigger’s in SQL server. Let’s see the following example:

INSERTED Magic Table:

When we insert record into table then SQL server automatically created ‘inserted’ magic table and recently inserted record are available in this table, If we want to recover this data which are recently inserted then we can access this record with the help of Trigger’s. Let’s see the demonstration of inserted magic table in SQL Server.

Example:

--- CREATE TABLE TO DEMONSTRATION OF INSERT MAGIC TABLE ----

CREATE TABLE INSERT_MAGIC

( ID INT,TRIGGER_MESSAGE VARCHAR(50) )

-------DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE

CREATE TRIGGER TRI_MAGIC_INSERT

ON USERLOGIN

INSTEAD OF INSERT

AS

BEGIN

DECLARE @ID INT

SELECT @ID = (SELECT ID FROM inserted)

INSERT INTO INSERT_MAGIC VALUES (@ID,'RECORD ADDED')

END

GO

Note: Here ‘inserted’ is insert magic table

DELETED Magic Table:

When we delete record from the table then SQL automatically create a deleted magic table which holds are deleted record from original table if we want to recover it then we can access that data from deleted magic table. Let’s see demonstration of recover data from deleted magic table.

Example: Creating Trigger for Deleted Magic table

-------DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE

CREATE TRIGGER TRI_MAGIC_DELETE

ON USERLOGIN

INSTEAD OF INSERT

AS

BEGIN

DECLARE @ID INT

DECLARE @NAME VARCHAR(50)

SELECT @ID = (SELECT ID FROM deleted)

SELECT @NAME = (SELECT NAME FROM deleted)

INSERT INTO INSERT_MAGIC VALUES (@ID,@NAME)

END

Go

What is CURSOR? Definition of CURSOR in SQL Server

CURSOR is a server side tool and completely different from ADO.NET’s cursor. It is giving row-by-row solution to the result set and let me tell you that SQL Server impressive with handling set of rows, not row-by-row. This is useful for those who came from procedural background and don’t much familiar with set-based relational algebra.

You can define CURSOR as read only or update but read only is fast as compare with update as read only will gives your data and won’t remember it.

Personally I used to avoid cursor as long as it is possible as it uses lots of resources of server and reduce the performance. Whenever it is possible use temp table, derived table, sub-query, CASE statement but finally you draw conclusion that any of the set-based operation won’t work for your problem than and than go for CURSOR solution but make sure you are not selecting more than necessary rows in CURSOR. Lesser the row, higher the performance!!!!

The main reason for writing this article is to help those who want to understand the concept of CURSOR and another reason CURSOR is not something that you can completely ignore. There may be some situation where you have to use CURSOR, may be some complex logic or dynamic code iteration especially while making code generator.

Type of CURSOR:

Static: This is lowest type of CURSOR and used to use for finding data and generating reports. Once getting the data into the CURSOR you will not identify any modification done in data after retrieving it because it make a copy of your data into the temp table of tempDB.

Forward_Only: This is the default type of CURSOR and really very similar to Static CURSOR the only difference you will find it, it will move forward only. In short, this CURSOR will scroll from first to last no other movement supported.

Fast_Forward: this is a mixture of Forward_Only and Read_Only.

Dynamic: this CURSOR will be bit slow as it will accept any data modification done by any user even after you fetch the data because you scroll around the CURSOR. Data membership, value and its order will be changed in each FETCH if any data modification has been done in record set.

Keyset-Driven: when you open this CURSOR, membership key and order of row are fixed in CURSOR.

Steps for CURSOR:

DECLARE: Defines a CURSOR with standard SELECT statement. This must be done before you open the CURSOR.

OPEN: physically open the CURSOR and received the record set exist in table at the time of opening the CURSOR.

FETCH: CURSOR always points to one row at a time and FETCH is retrieve the value from that row to manipulate it further.

CLOSE: CLOSE will release the lock on table made by CURSOR. If you wish than you can re-open CURSOR after closes it.

DEALLOCATE: Once you are done with CURSOR, do DEALLOCATE it and removes the memory from the server. You can open the CURSOR once you close it but can’t re-open CURSOR once you DEALLOCATE it.

You can run following query for dynamic management function in-order to get information about CURSOR running on your server.

select * from sys.dm_exec_cursors(0)
go
-------------

What is cursor in SQL Server? 

• A Cursor is a database object that represents a result set and is used to manipulate data row by row.
• When a cursor is opened, it is positioned on a row and that row is available for processing.
• SQL Server supports three types of cursor namely Transact-SQL server cursor, API server cursor, and client cursor.
• Transact-SQL Server cursors use Transact-SQL statements and are declared using DECLARE CURSOR statement.
• Transact-SQL Server cursors can be used in Transact-SQL scripts, stored procedures, and triggers.
• Transact-SQL cursors are implemented on the server.
• You can fetch only one row at a time in Transact-SQL Server cursors.
• You can use FETCH statements with Transact-SQL cursors to retrieve rows from a cursor’s result set.
• API server cursors support the API cursor functions.
• API server cursors are implemented on the server.
• API server cursors support fetching blocks of rows with each fetch.
• A cursor fetches multiple rows at a time is called a block cursor 

What are cursors?

Answer
A cursor is used to access the result set stored in the memory on execution of a query. It is a special programming construct that allows data to be manipulated on a row-by-row basis. They point to a certain location within a record set and allow the operator to move forward (and sometimes backward, depending upon the cursor type) through the results one record at a time.

Define the steps to use Transact-SQL Cursor. 

Declare the cursor,
Open the cursor,
Fetch record row by row,
Close cursor,
Deallocate cursor.

Example of a cursor

DECLARE @EmpID char(11)

DECLARE c1 CURSOR READ_ONLY

FOR

SELECT Employee_ID FROM Employee

OPEN c1

FETCH NEXT FROM c1

INTO @EmpID

PRINT @EmpID

WHILE @@FETCH_STATUS = 0

BEGIN

      PRINT @EmpID

      FETCH NEXT FROM c1

      INTO @EmpID

END

CLOSE c1

DEALLOCATE c1

Explain the cursor types. 

DYNAMIC: It reflects changes happened on the table while scrolling through the row.
STATIC: It works on snapshot of record set and disconnects from the server. This kind doesn’t reflects changes happened on the table while scrolling through the row.
KEYSET: In this kind, new record is not reflected, but data modification can be seen 

Explain different types of cursors.

Different types of cursors:

Implicit cursors: these cursors are invoked implicitly. User need not create, open, fetch or close the cursor.

Explicit cursors: these cursors are not invoked implicitly. User needs to create, open, fetch or close the cursor.

Static Cursor: Stores a complete copy of the result set. Used mostly where scrolling is required. Static cursors don’t support updates.

Forward – only cursors: This cursor supports updates but not scrolling. It supports only fetching serially. Rows are not retrieved from the database until they are fetched.

Forward – only cursors / Read only cursor: These are the fastest of the cursors and cannot be updated. They cannot be created on query that returns only read only columns.

Key set driven : It is a scrollable cursor that cannot be updated. These cursors are controlled by a set of physical identifiers called as key set. The keyset is built in a temporary table when the cursor is opened.

Define the cursor lock types. 

Three types of locks

READ ONLY: This prevents any updates on the table.
SCROLL LOCK: This allows you to make changes to the table.
OPTIMISTIC: This checks if any change in the table record since the row fetched before updating.
If there is no change, the cursor can update 

Explain in brief the cursor optimization tips. 

Close cursor when it is not required.
You shouldn’t forget to deallocate cursor after closing it.
You should fetch least number of records.
You should use FORWARD ONLY option when there is no need to update rows.

SQL Server 2005 Remove Dups – CTE

No more selecting DISTINCT into a temp table and then inserting back into the cleaned out table. This does it all for you in two statements.
Deleting from the CTE actually changes the underlying table. Be careful how you setup your CTE. You could have some unintended deletes without the right logic
CREATE TABLE #prod(
    Product_Code varchar(10),
    Product_Name varchar(100)
)

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('123','Product_1')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('234','Product_2')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('345','Product_3')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('345','Product_3')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('456','Product_4')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('567','Product_5')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('678','Product_6')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('789','Product_7')

SELECT *
FROM #prod;


With Dups as
(
    select *, row_number() over (partition by Product_Code order by Product_Code) as RowNum
    from #prod
)


Delete from Dups where rownum > 1;

--Note duplicate record 345 Product_3 has been removed.
SELECT *
FROM #prod;

Kill the Sql Connection

/*****************************************************************

*** Procedure: sp_KillConnections

*** Usage: sp_KillConnections @dbName = 'Database Name'

*** Description: Drop all connections from a specific database

*** Input: @dbName - REQUIRED - Name of the database

*** Output: Outputs the results of the proccess

******************************************************************/

CREATE PROCEDURE sp_KillConnections

      @dbName VARCHAR(128)

AS

DECLARE @spid varchar(5)

DECLARE @LoginName nvarchar(128)

DECLARE @intErrorCode int

DECLARE @intOk int

DECLARE @intError int

DECLARE @intTotal int

SET @intErrorCode = 0

SET @intOk = 0

SET @intError = 0

SET @intTotal = 0

SELECT @intTotal = Count(sp.spid) FROM master..sysprocesses sp JOIN master..sysdatabases sd ON sp.dbid = sd.dbid WHERE sd.name = @dbName

DECLARE KILL_CONS CURSOR FOR

SELECT CAST(sp.spid as varchar(5)),RTrim(sp.loginame) FROM master..sysprocesses sp JOIN master..sysdatabases sd ON sp.dbid = sd.dbid WHERE sd.name = @dbName

OPEN KILL_CONS

FETCH NEXT FROM KILL_CONS INTO @spid,@LoginName

WHILE @@FETCH_STATUS = 0

BEGIN

      EXEC('Kill '+ @spid + '')

      SELECT @intErrorCode = @@ERROR

      IF @intErrorCode = 0

      BEGIN

            SET @intOk = @intOk + 1

            PRINT 'Process ' + @spid + ' from login ' + @LoginName + ' has been ended.'

      END

      ELSE

      BEGIN

            SET @intError = @intError + 1

            PRINT 'Process ' + @spid + ' from login ' + @LoginName + ' could not be ended.'

      END

      FETCH NEXT FROM KILL_CONS INTO @spid,@LoginName

END

CLOSE KILL_CONS

DEALLOCATE KILL_CONS

PRINT 'Total number of processes from database ' + @dbName + ': ' + CAST(@intTotal as varchar)

PRINT 'Processes ended normally: ' + CAST(@intOk as varchar)

PRINT 'Processes could not be ended: ' + CAST(@intError as varchar)

GO