SQL Server Development

How to use OFFSET and FETCH NEXT ROWS in SQL Server 2012?

--SQL Server 2012
WITH CTE_SoldProduct
AS
(
SELECT SUM(Quantity) QNT,ProductID FROM dbo.TBL_Sales
GROUP BY ProductID
)

SELECT 
      P.NAME 
      ,P.Price
      ,SP.QNT
    FROM CTE_SoldProduct SP
    INNER JOIN dbo.TBL_Product p ON SP.ProductID = p.ID
ORDER BY SP.QNT DESC
offset 1 rows FETCH NEXT 2 ROWS ONLY;

--SQL Server 2008
 WITH CTE_SoldProduct
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY SUM(Quantity) DESC) AS num, SUM(Quantity) QNT,ProductID FROM dbo.TBL_Sales
GROUP BY ProductID
)

SELECT 
      P.NAME 
      ,P.Price
      ,SP.QNT
    FROM CTE_SoldProduct SP
    INNER JOIN dbo.TBL_Product p ON SP.ProductID = p.ID
WHERE num >=2

Cursor in SQL Server

SQL Server Cursor is a row base operator but it does not have good performance

because it create different execution plan per each rows, personality

I do not like use this operator so always I look for standards operators same as JOIN,SELECT,GROUP,…

and Loop operators instead of cursor.

By the way few times the cursor is useful;

please pay attention to below example:

Simple Sample:

USE Arm_DB 
GO

DECLARE @ProductID INT
DECLARE @ProductName NVARCHAR(50)
DECLARE @ProductPrice DECIMAL(10,2)

DECLARE Product_Cursor CURSOR FOR 
                                SELECT ID,NAME,Price 
                                    FROM dbo.TBL_Product

OPEN Product_Cursor FETCH FROM Product_Cursor INTO  @ProductID
                                                   ,@ProductName
                                                   ,@ProductPrice
    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @ProductName ProductName
             ,SUM(Quantity)*@ProductPrice AS TotalSales 
            FROM dbo.TBL_Sales
            WHERE ProductID = @ProductID

        FETCH FROM Product_Cursor INTO @ProductID
                                      ,@ProductName
                                      ,@ProductPrice  
    END      
CLOSE Product_Cursor
DEALLOCATE Product_Cursor

Optimal Option: Use JOIN and GROUP BY instead of CURSOR

SELECT 
      P.NAME AS ProductName 
     ,SUM(S.Quantity) * P.Price AS TotalSales
 FROM dbo.TBL_Sales S
  INNER JOIN dbo.TBL_Product P ON S.ProductID = P.ID
GROUP BY P.NAME,P.Price

Useful sample: Get Backup from all user’s databases in SQL Server instance

USE Master 
Go

DECLARE @Path NVARCHAR(600) = 'C:\Arman\Mine\MyBlog\CodingTips\DBbackup\'
DECLARE @FileDate NVARCHAR(50) = CONVERT(NVARCHAR,GETDATE(),112)
DECLARE @DBname NVARCHAR(50) = ''
DECLARE @FileName NVARCHAR(1000) = ''

DECLARE DB_Cursor CURSOR For 
                            SELECT name 
                            FROM sys.databases  
                            WHERE database_id > 4 --Start From User Database

OPEN  DB_Cursor FETCH NEXT FROM DB_Cursor INTO @DBname
     WHILE @@FETCH_STATUS = 0
      BEGIN

        SET @FileName = @Path + @DBname + '_' + @FileDate + '.Bak'    
        BACKUP DATABASE @DBName TO DISK = @FileName    
        FETCH NEXT FROM DB_Cursor INTO @DBname         
      END 
CLOSE DB_Cursor
DEALLOCATE DB_Cursor

How to create table in SQL Server

USE Arm_DB
Go

 IF (OBJECT_ID('TBL_Product','U') IS NOT NULL)
   DROP TABLE TBL_Product

Go

CREATE TABLE TBL_Product
(
ID INT IDENTITY(1,1)
,NAME NVARCHAR(50)
,Price DECIMAL(10,2)
,[Desc] NVARCHAR(MAX)
,InsertedDate DATETIME DEFAULT SYSDATETIME()
,MOdifiedDate DATETIME DEFAULT SYSDATETIME()
CONSTRAINT PK_TBL_Product PRIMARY KEY (ID)
)

GO 

 IF (OBJECT_ID('TBL_Sales','U')IS NOT NULL )
    DROP TABLE TBL_Sales

CREATE TABLE TBL_Sales
(
ID INT IDENTITY(1,1)
,ProductID INT 
,Quantity INT
,SalesDate DATETIME
,InsertedDate DATETIME DEFAULT SYSDATETIME()
,ModifiedDate DATETIME DEFAULT SYSDATETIME()
CONSTRAINT PK_TB_Sales PRIMARY KEY (ID)
CONSTRAINT FK_TBL_Sales_TBL_Product 
     FOREIGN KEY (ProductID) REFERENCES dbo.TBL_Product(ID)
)

GO

INSERT dbo.TBL_Product   ( NAME ,Price )
SELECT 'Book',32.4
UNION ALL
SELECT 'Pen',5.2
UNION ALL
SELECT 'Notebook',8.7

Go

INSERT INTO dbo.TBL_Sales( ProductID , Quantity ,SalesDate)
SELECT 1,4,'2013-Apr-10'
UNION ALL
SELECT 1,3,'2013-Apr-11'
UNION ALL
SELECT 1,8,'2013-Apr-13'
UNION ALL
SELECT 1,1,'2013-Apr-18'
UNION ALL
SELECT 2,4,'2013-Apr-10'
UNION ALL
SELECT 2,3,'2013-Apr-11'
UNION ALL
SELECT 2,8,'2013-Apr-13'
UNION ALL
SELECT 2,1,'2013-Apr-18'
UNION ALL
SELECT 3,4,'2013-Apr-10'
UNION ALL
SELECT 3,5,'2013-Apr-11'
UNION ALL
SELECT 3,3,'2013-Apr-13'
UNION ALL
SELECT 3,9,'2013-Apr-18'

SELECT * FROM dbo.TBL_Product

SELECT * FROM dbo.TBL_Sales

Update from in SQL Server (lookup)

USE Arm_DB
Go

CREATE TABLE TBL_ARM1
(
ID INT IDENTITY(1,1)
,Title Nvarchar(50)
)

Go

CREATE TABLE TBL_Arm2
(
ID INT Identity(1,1)
,Name Nvarchar(50)
,Arm1_ID Int
,Arm_Title Nvarchar(50)
)


GO 
INSERT INTO TBL_ARM1 VALUES ('Arm1_1')
INSERT INTO TBL_ARM1 VALUES ('Arm1_2')
INSERT INTO TBL_ARM1 VALUES ('Arm1_3')

Go

INSERT INTO TBL_Arm2 (Name ,Arm_Title) VALUES('Mehran','Arm1_1')
INSERT INTO TBL_Arm2 (Name ,Arm_Title) VALUES('Mehran2','Arm1_2')
INSERT INTO TBL_Arm2 (Name ,Arm_Title) VALUES('Mehran3','Arm1_3')



SELECT * FROM TBL_Arm2


UPDATE TBL_Arm2
SET Arm1_ID = A.ID
FROM TBL_ARM1 A
WHERE A.Title = Arm_Title

How to modify and add column in SQL Server’s table with TSQL

USE Arm_DB
Go
IF OBJECT_ID('TBL_Persons','U') IS NOT NULL
 DROP TABLE TBL_Persons

CREATE TABLE TBL_Persons
 (
   ID INT IDENTITY PRIMARY KEY
   ,NAME Char(50)
 )

GO

     ALTER TABLE TBL_Persons
     ADD Lname Nvarchar(70)
Go
    ALTER TABLE TBL_Persons
    ALTER COLUMN NAME NVARCHAR(50)
GO
    INSERT INTO TBL_Persons(Name,Lname)
     VALUES ('Arman','Nas')
GO
    SELECT * FROM TBL_Persons

How to save image or any others file type directly from file in SQL Server with binary format

USE Arm_DB
GO

IF OBJECT_ID('TBL_Image','U') IS NOT NULL
 DROP TABLE TBL_Image

 CREATE TABLE TBL_Image 
  (
  [ID] INT IDENTITY(1,1)
 ,[Image] VARBINARY(MAX)
   CONSTRAINT PK_TBL_Image PRIMARY KEY CLUSTERED (ID) 
  )
Go
INSERT INTO TBL_Image(Image)
SELECT * FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Koala.JPG',SINGLE_BLOB  ) i

How to create Database in SQL Server with TSQL

USE master
GO
CREATE DATABASE Arm_DB 
 ON 
   (
     NAME = Arm_DB_Dat
     ,FILENAME = 'C:\Arman\Mine\MyBlog\Database\Arm_DB_Dat.MDF'
     ,SIZE = 20
     ,MAXSIZE = 200
     ,FILEGROWTH = 8
   ) 
 LOG 
  ON 
    (
     NAME = Arm_DB_Log
     ,FILENAME = 'C:\Arman\Mine\MyBlog\Database\Arm_DB_Log.LDF'
     ,SIZE = 15MB
     ,MAXSIZE = 125MB
     ,FILEGROWTH = 5MB   
    )

GO