New T-SQL features in SQL Server 2012 – OFFSET and FETCH clause

The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set.

Note:OFFSET-FETCH can be used only with the ORDER BY clause.

Syntax:  
    
syntax for exclude n number of row from the result set:

                SELECT column_names 
                FROM table_name
               ORDER BY column_names
               OFFSET n ROWS

Syntax for exclude first n records and return only the next m records:

                 SELECT column_names 
                FROM table_name
               ORDER BY column_names
               OFFSET n ROWS
               FETCH NEXT m ROWS ONLY

For examples: use emp table to excluded first 2 sorted employee number getting remaining row 

             SELECT * FROM EMP
             ORDER BY empno
             OFFSET 2 ROWS 

example 2: exclude first 2 records and return only the next 3 records

             SELECT * FROM EMP
             ORDER BY empno
             OFFSET 2 ROWS 
             FETCH NEXT 3 ROWS ONLY

Check and Create a Folder Using T-SQL Code

> Check if Folder Exist or Not in our Desired Location Using System Stored Procedure

"master.dbo.xp_fileexist" And if not exists create Directoty Using Below T-SQL Code:

declare @chkdirectory as nvarchar(4000)

    declare @folder_exists as int     

    set @chkdirectory = 'C:\SQLDBPool\SQL\Articles' 

    declare @file_results table

    (file_exists int,

    file_is_a_directory int,

    parent_directory_exists int

    ) 

    insert into @file_results

    (file_exists, file_is_a_directory, parent_directory_exists)

    exec master.dbo.xp_fileexist @chkdirectory

     

    select @folder_exists = file_is_a_directory

    from @file_results     

    --script to create directory       

    if @folder_exists = 0

     begin

        print 'Directory is not exists, creating new one'

        EXECUTE master.dbo.xp_create_subdir @chkdirectory

        print @chkdirectory +  'created on' + @@servername

     end       

    else

    print 'Directory already exists'

GO

Dynamically Create a Folder for Each Database In Server

I Need to Create Folder For Each Database in a particular server.I like to have a specific folder structure to hold the backup files for each database.  See below:

                       D:\Backup\Server\Instance\BabjiDB
                     D:\Backup\Server\Instance\NaveenDB
                     D:\Backup\Server\Instance\JaipalDB

So we need to maintain each DB backup in separate folder,Check Below T-SQL code 

DECLARE

       @BaseFolder nvarchar(128)

      ,@DatabaseFolder nvarchar(256)

      ,@DbName sysname

     

-- Only one input parameter needed from the user.

SET @BaseFolder = 'D:\Backup';

DECLARE Db CURSOR FOR

SELECT name FROM sys.databases WHERE name <> 'tempdb';

OPEN Db;

FETCH NEXT FROM Db INTO @DbName;

WHILE @@FETCH_STATUS = 0

BEGIN

      -- Dynamically create a folder for each database.

      SET @DatabaseFolder = @BaseFolder + '\'

      + CONVERT(nvarchar,SERVERPROPERTY('MachineName')) + '\'

      + ISNULL(CONVERT(nvarchar,SERVERPROPERTY('InstanceName')),'DEFAULT')

      + '\' + @DbName;

      EXEC master.sys.xp_create_subdir @DatabaseFolder;

      FETCH NEXT FROM Db INTO @DbName;

END

-- Clean up

CLOSE Db;

DEALLOCATE Db;

GO