Query to retrieve Schema/Script of a user table

/*In below SQL Script enter user defined table name i.e. table Employee*/

DECLARE @table_name SYSNAME
SELECT @table_name = 'dbo.Employee'

DECLARE
      @object_name SYSNAME
    , @object_id INT


SELECT
      @object_name = '[' + s.name + '].[' + o.name + ']'
    , @object_id = o.[object_id]
FROM sys.objects o WITH (NOWAIT)
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE s.name + '.' + o.name = @table_name
    AND o.[type] = 'U'
    AND o.is_ms_shipped = 0

DECLARE @SQL NVARCHAR(MAX) = ''

;WITH index_column AS
(
    SELECT
          ic.[object_id]
        , ic.index_id
        , ic.is_descending_key
        , ic.is_included_column
        , c.name
    FROM sys.index_columns ic WITH (NOWAIT)
    JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
    WHERE ic.[object_id] = @object_id
),
fk_columns AS
(
     SELECT
          k.constraint_object_id
        , cname = c.name
        , rcname = rc.name
    FROM sys.foreign_key_columns k WITH (NOWAIT)
    JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id
    JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
    WHERE k.parent_object_id = @object_id
)
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
    SELECT CHAR(9) + ', [' + c.name + '] ' +
        CASE WHEN c.is_computed = 1
            THEN 'AS ' + cc.[definition]
            ELSE UPPER(tp.name) +
                CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
                       THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                     WHEN tp.name = 'decimal'
                       THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                    ELSE ''
                END +
                CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
                CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END +
                CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END
        END + CHAR(13)
    FROM sys.columns c WITH (NOWAIT)
    JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
    LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
    LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
    LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
    WHERE c.[object_id] = @object_id
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
    + ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' +
                    (SELECT STUFF((
                         SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
                         FROM sys.index_columns ic WITH (NOWAIT)
                         JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
                         WHERE ic.is_included_column = 0
                             AND ic.[object_id] = k.parent_object_id
                             AND ic.index_id = k.unique_index_id  
                         FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
            + ')' + CHAR(13)
            FROM sys.key_constraints k WITH (NOWAIT)
            WHERE k.parent_object_id = @object_id
                AND k.[type] = 'PK'), '') + ')'  + CHAR(13)
    + ISNULL((SELECT (
        SELECT CHAR(13) +
             'ALTER TABLE ' + @object_name + ' WITH'
            + CASE WHEN fk.is_not_trusted = 1
                THEN ' NOCHECK'
                ELSE ' CHECK'
              END +
              ' ADD CONSTRAINT [' + fk.name  + '] FOREIGN KEY('
              + STUFF((
                SELECT ', [' + k.cname + ']'
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')' +
              ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
              + STUFF((
                SELECT ', [' + k.rcname + ']'
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')'
            + CASE
                WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE'
                WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
                WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT'
                ELSE ''
              END
            + CASE
                WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
                WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
                WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'
                ELSE ''
              END
            + CHAR(13) + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name  + ']' + CHAR(13)
        FROM sys.foreign_keys fk WITH (NOWAIT)
        JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
        WHERE fk.parent_object_id = @object_id
        FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
    + ISNULL(((SELECT
         CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END
                + ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_name + ' (' +
                STUFF((
                SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
                FROM index_column c
                WHERE c.is_included_column = 0
                    AND c.index_id = i.index_id
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
                + ISNULL(CHAR(13) + 'INCLUDE (' +
                    STUFF((
                    SELECT ', [' + c.name + ']'
                    FROM index_column c
                    WHERE c.is_included_column = 1
                        AND c.index_id = i.index_id
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '')  + CHAR(13)
        FROM sys.indexes i WITH (NOWAIT)
        WHERE i.[object_id] = @object_id
            AND i.is_primary_key = 0
            AND i.[type] = 2
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
    ), '')

PRINT @SQL

Query to get User tables Information

Query to retrieve Rows, Pages and Size of Tables in SQL Server

SELECT
ObjectId= p.[Object_id]
,TableName = OBJECT_NAME(p.[object_Id])
, TotalRows = MAX(p.[rows])
, TotalPages = SUM(a.total_pages)
, UsedPages = SUM(a.used_pages)
, Pages = SUM(
CASE
WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0
WHEN a.[type]! = 1 AND p.index_id < 2 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages ELSE 0
END
)

, SizeInMB = (SUM(
CASE
WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0
WHEN a.[type]! = 1 AND p.index_id < 2 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages ELSE 0
END
)*8.)/1024.0

FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
LEFT JOIN sys.internal_tables it ON p.[object_id] = it.[object_id]
GROUP BY p.[object_id]
HAVING OBJECTPROPERTY(p.[object_id],'IsUserTable') = 1

Please suggest your valuable inputs.

Page and Extent in SQL Server

SQL server stores everything inside pages in different types of pages.

Disk space is allocated to data files (.MDF/.LDF/.NDF) is logically organized in contiguous pages (0-n)

Disk operation is perform at page level to read /write

1 Page                     = 8 KB

1 Extent                   = 8 Pages/ 64 KB

1 MB                       =128 Pages

Each page has 96 byte header which contains system information which is Page number, Page type, free space on page and allocation unit id of object which contain page and Row offset which contain row number info growing from end of the page

                                                    Page: 

 

Pages are following types:

S#	Page type	Description
1	Data page	Data records, Leaf of Clustered Index
2	Index page	Non Clustered index, Non leaf of Clustered Index
3	Text/Image Page	for large object data types(text,ntext,images,varbinary etc.)
4	GAM/SGAM	info for extent tracking
5	Page Free Space	Info about Page allocation and free space
6	IAM	info for extent usage for table or index
7	Bulk Changed map	info for extent modification by bulk operation since last log bkp per allocation unit
8	Differential Changed map	info for extent modification since last db bkp per allocation unit
9	Sort Page	Temp page for sorting
10	Boot Page	Info about Page
11	File Header Page	info about file
12	Server Config Page	page for sp_configure

Extent

Extent is used to manage space, extent are formed by eight contiguous pages

A new table or index generally consist mixed extent once its grows to accommodate eight pages then it switches to uniform extent. If we create index on large table which can accommodate eight contiguous pages then it allocate pages to uniform extents.

PAD_INDEX in SQL Server

PAD_INDEX is the option we specify while index creation/Rebuild as below.

Pad_Index {ON|OFF}

Pad_Index is only useful when fill factor is specified in index definition, if PAD_Index is ON, it means fill factor value will be applied to intermediate nodes of balance tree formed by index.

If PAD_Index is OFF, it means fill factor value will not be applied to intermediate nodes of balance tree.

/* PRACTICAL SCENARIOS

 CREATE TEST TABLE EMPLOYEE_PADINDEX  */

CREATE TABLE Employee_PadIndex

(Id INT NOT NULL,

Name VARCHAR(20)

)

/*INSERT SOME TEST RECORDS*/

INSERT INTO Employee_PadIndex

SELECT 1,'Ram'

UNION ALL

SELECT 2,'Milan'

UNION ALL

SELECT 3,'Sandy'

UNION ALL

SELECT 4,'Babu'

UNION ALL

SELECT 5,'Allen'

/* HERE FILL FACTOR WILL NOT BE APPLICABLE FOR CLUSTERED INDEX INTERMEDIATE NODES AS PAD_INDEX  = OFF */

CREATE CLUSTERED INDEX  Idx_Employees_ID on Employee_PadIndex(id)

WITH (FILLFACTOR = 80, PAD_INDEX  = OFF) ON [PRIMARY]

/* HERE FILL FACTOR WILL BE APPLICABLE FOR NONCLUSTERED INDEX INTERMEDIATE NODES AS PAD_INDEX  = ON */

CREATE NONCLUSTERED INDEX  Idx_Employees_NAME on Employee_PadIndex(NAME)

WITH (FILLFACTOR = 80, PAD_INDEX  = ON) ON [PRIMARY]

/* HERE FILL FACTOR WILL BE APPLICABLE FOR CLUSTERED INDEX INTERMEDIATE NODES AS PAD_INDEX  = ON */

ALTER INDEX  Idx_Employees_ID on Employee_PadIndex

REBUILD

WITH (FILLFACTOR = 80, PAD_INDEX  = ON ) 

/* DROP ABOVE CREATED CLUSTERED INDEX */

DROP INDEX  Idx_Employees_ID on Employee_PadIndex

/* CREATE PRIMARY KEY WITH CLUSTERED INDEX WITH OPTIONS

HERE FILL FACTOR WILL NOT BE APPLICABLE FOR INTERMEDIATE NODES AS PAD_INDEX  = OFF */

ALTER TABLE Employee_PadIndex

ADD CONSTRAINT [PK_Employees_Id] PRIMARY KEY CLUSTERED 

(

    [Id] ASC

) WITH (FILLFACTOR = 80,PAD_INDEX  = OFF) ON [PRIMARY]

    

/* DROP TEST TABLE EMPLOYEE_PADINDEX */

DROP TABLE Employee_PadIndex

Note:

Please try above scenarios in Test/Development Environment only.

Date & Time Data Types

Simple Look/Understanding of Date & Time data types in SQL Server

Execute below SQL & Notice output

SELECT CAST(GETDATE() AS TIME) TIME
       /* Accuracy upto 100 nanosecond*/

SELECT CAST(GETDATE() AS DATE)       DATE                                                                                   /* Accuracy upto 1 day*/

SELECT CAST(GETDATE() AS SMALLDATETIME)  SMALLDATETIME                                                   /* Accuracy upto 1 Minute */

SELECT CAST(GETDATE() AS DATETIME)       DATETIME                                                                  /* Accuracy upto 1 Minute 0.00333 second */

SELECT CAST(GETDATE() AS DATETIME2)      DATETIME2                                                                 /* Accuracy upto 100 nanoseconds */

SELECT CAST(GETDATE() AS DATETIMEOFFSET)  DATETIMEOFFSET                                                /*  Accuracy upto 100 nanoseconds */

Recursive CTE in SQL Server

-- Create  test table Temp_Employees
CREATE TABLE [dbo].[Temp_Employees](
[EmpId] [int] NULL,
[Name] [varchar](100) NULL,
[DeptId] [int] NULL,
[MgrId] [int] NULL
)

-- Insert some dummy Records in test table Temp_Employees
INSERT INTO Temp_Employees (EmpId,Name ,DeptId,MgrId ) Values
(1,'John',   1,    NULL ),
(2,'Dinesh', 1,    1),
(3,'Rohan',  1,    1),
(4,'Babu',   1,    2),
(5,'Sandy',  1,    2),
(6,'Allan',  1,    5),
(7,'Duminy', 1,    5),
(8,'Ram',    1,    4),
(9,'Mark',   3,    2),
(10,'Dough', 6,    1)

-- select/check inserted records in table Temp_Employees
select * FROM Temp_Employees

-- write recursive CTE to show Employee, manager and level of Employee in organization
; WITH RecursiveCTE (Empid,EmpName ,Manager,LevelInOrganization)
AS(

 SELECT Empid ,Name as EmpName, cast(' ' as varchar) AS Manager,0 FROM Temp_Employees where MgrId IS NULL
UNION ALL
SELECT e.Empid ,e.Name as EmpName ,CAST(Rc.EmpName as varchar) AS Manager,LevelInOrganization + 1
FROM Temp_Employees E
INNER JOIN RecursiveCTE Rc on e.MgrId  = rc.Empid
)
select * FROM RecursiveCTE

Drop table Temp_Employees

Note: Please  check/execute above queries in development/test environment only

SQL Server Query Execution

SQL Server Query Execution process

For Algebrizer step, Please visit link.

http://blog.dbwithdb.com/2012/10/what-is-algebrizer.html

FileTable in SQL Server

File table is special type of table to store files and folder in SQL Server 2012 onwards, these files and folder are easily accessible through windows explorer it as good as windows file system. We can add, delete file to windows directory by copy and paste then it will shows those files in table FileTable

We can manage files from windows directory as well as using SQL server management studio i.e. delete/truncate/update/select etc.
File tables have specific set of columns while creation of filetable we no need to specify list of columns.
To create filetable database should have file stream file & file group

File Tables provide following features

1. Provides hierarchy of files and folders
2. Every rows represent file or folder
3. We can perform DML operation on selected columns i.e. non computed columns
4. Every rows hold followings
            (i) File stream column for stream data and file_id as GUID
            (ii) Path and parent path locator as an hierarchy
            (iii) Ten file attribute i.e. file creation/access/modification etc.
            (iv) Type column which support full text and semantic search

5. We cannot change existing table to file table
6. We must need to specify parent directory(i.e. Dineshbabuverma) at database level
7. Must have valid file stream file and file group
8. We cannot create table level constraints while creation of table but we can add constraints after creation of table
9. We cannot create table filetable in database tempdb.

Let us perform steps to create table filetable consider following scenarios
Table Name : MyFileTable
Database Name : MyFileTableDB
Directory Name : Dineshbabuverma
File Group Name : FIleTableFG
FileTable File Name : FileTableFile


(i) Create Database with given filegroup and filename : MyFileTableDB

CREATE DATABASE [MyFileTableDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'MyFileTableDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyFileTableDB.mdf' , SIZE = 15360KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [FIleTableFG] CONTAINS FILESTREAM DEFAULT
( NAME = N'FIleTableFile', FILENAME = N'C:\FIleTableFile' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'MyFileTableDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyFileTableDB_logFile.ldf' , SIZE = 38208KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

If we have existing database we can file group and file as type filestream using below commands

ALTER DATABASE MyFileTableDB
ADD FILEGROUP MyFileTableFG contains filestream;
GO

ALTER DATABASE MyFileTableDB
ADD FILE
( NAME = 'MyFileTableFile', FILENAME = 'C:\MyFileTableFile'
)
TO FILEGROUP MyFileTableFG;
GO



(ii) Enable Filestream
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO


(iii) Set Filestream Details as below
ALTER DATABASE MyFileTableDB
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'Dineshbabuverma')


Same we can set Using UI as per below screen, marked in color yellow



(iv) Enable Filestream for Transact SQL Access: first go to SQL Server configuration manager, Right click on SQL Server Instance Properties Filestream tab and enable filestream access as below


select DB_NAME(database_id ) DatabaseName ,* FROM sys.database_filestream_options
where directory_name is not null;


Output of query:


Now we can create file Table and explore the file table directory according to below figure.
CREATE TABLE MyFIleTable AS FILETABLE

output of the select command
select * FROM MyFIleTable






Once we click on explore filetable directory it open windows file directory DineshBabuverma of table Myfiletable


Now let us just copy and paste some files as below


Now execute again select statement, we can see our files in table MyFileTable
select * FROM MyFIleTable



/*Below delete command will delete file IMG_20150829_161804067.jpg from windows directory, first execute then check in filedirectory*/
DELETE FROM MyFIleTable
WHERE stream_id ='869D2F26-2A61-E511-BE9B-4487FCD82C05'

select * FROM MyFIleTable



Now if we check files in windowsfiledirectory DineshBabuverma, we will see only two files

We can insert file into FileTable using below command

INSERT INTO [dbo].MyFIleTable
([name],[file_stream])
SELECT
'DP1.png', * FROM OPENROWSET(BULK N'D:\DP1.png', SINGLE_BLOB) AS FileData
;


For inserted file we can see additional one row in above result set, marked in color yellow.

Note: Please try above scenarios in development/test environment.

Data Compression in SQL Server

SQL Server support three types of Data compression which help to save storage space by reducing the size of the database, data compression has overhead on CPU, it is because for usage purpose CPU has to decode the compressed data. Data compression is not applicable on system tables. Enterprise and developer edition support Database compression.

Types of Data compression:

1. Row Level Compression
2. Page Level Compression
3. Unicode Compression

Data Compression can be configured on following configurations.
a. Clustered Table
b. Heap Table(A table without cluster index)
c. Non Clustered indexes
d. Indexed Views
e. On Individual partition of partitioned table or index

Row Level Compression
(i) Row level compression work by storing fixed length Char data types albeit they were stored as Variable length Varchar data types
(ii) It won’t stores NULL and 0 values
(iii) Reducing Meta data required for storing of data.

We can use following SQL commands for Row level compression on table Employee
ALTER TABLE Employee REBUILD WITH (DATA_COMPRESSION=ROW)

For partitioned table use below command
ALTER TABLE Employee REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)

For index, row level compression use below command assuming index idx_Employee_Id on table Employee
ALTER INDEX idx_Employee_Id ON Employee REBUILD WITH (DATA_COMPRESSION=ROW)


Page Level Compression
Page level compression, compress data by storing repeating or common value at one place and making reference to that value in table, when page compression applied then row level compression automatically applied.

Page level compression uses following techniques

(i) Row level compression is applied to store maximum number of rows on page
(ii) Column prefix compression is applied to store repeating values in header and reference is mapped with repeating/common values.
(iii) Dictionary compression scans for repeating values and store scanned info in page header


We can use following SQL commands for Page level compression on table Employee.
ALTER TABLE Employee REBUILD WITH (DATA_COMPRESSION=PAGE)

For partitioned table use below command.
ALTER TABLE Employee REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

For index, page level compression use below command assuming index idx_Employee_Id on table Employee.
ALTER INDEX idx_Employee_Id ON Employee REBUILD WITH (DATA_COMPRESSION=PAGE)


Unicode Compression
Unicode compression enables database engine to compress Unicode values(NChar, NVarchar) stored in row and page compressed objects, we can use Unicode compression with fixed length NChar and NVarchar data types.
Unicode compression get automatically used accordingly when we use row/page compression.


If we want to implement data compression using wizard we can use following steps
1. Right click on table Employee then click storage and then manage compression as below



2. It will populate below window



3. From the below screen select row/page compression.


4. Select compression type as row then click next, it will populate below window



5. Click finish then it will the command in new window or we can use RUN immediately from above screen, here we have selected create script, then click finish.



USE [Capital]
ALTER TABLE [dbo].[Employee] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = ROW
)

it will generate above script as per our selection, we can execute script for row level compression.

Queries to Delete Duplicate Records in SQL Server

Queries to Delete Duplicate records

/*Start Create Table with Duplicate records */
CREATE TABLE DeleteDuplicate
(
ID INT,
NAME VARCHAR(88)
)

INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4

/*End Create Table with Duplicate records */


/*Method#01 Start DELETE Duplicate Rows using while loops*/
WHILE ( 1 = 1 )
BEGIN
SET ROWCOUNT 1

DELETE c
FROM DeleteDuplicate c
INNER JOIN (SELECT id,
NAME
FROM DeleteDuplicate
GROUP BY id,
NAME
HAVING Count(*) > 1) CC
ON C.id = CC.id
AND C.NAME = CC.NAME

IF @@ROWCOUNT = 0
BEGIN
SET ROWCOUNT 0
BREAK;
END
END
/*Method#01 End DELETE Duplicate Rows using while loops*/


/*Method#02 Start DELETE Duplicate Rows using Go statement*/
INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4
GO

DECLARE @DupRowCount INT = 0;
SELECT @DupRowCount =@DupRowCount +( Count(*) -1)
FROM DeleteDuplicate
GROUP BY id,
NAME
HAVING Count(*) > 1
select @DupRowCount

SET Rowcount 1
DELETE c
FROM DeleteDuplicate c
INNER JOIN (SELECT id,
NAME
FROM DeleteDuplicate
GROUP BY id,
NAME
HAVING Count(*) > 1) CC
ON C.id = CC.id
AND C.NAME = CC.NAME
GO 20
/* above we need to specify value of duplicate records. which got from variable @DupRowCount*/
SET RowCount 0
GO
/*Method#02 End DELETE Duplicate Rows using Go statement*/



/*Method#03 Start DELETE Duplicate Rows using CTE*/

INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4
GO


;WITH DELETECTE(Id,Name,DeleteCount )
as
(select id,name ,
r = row_number() Over(Partition by id,name order by id,name)
from DeleteDuplicate )
DELETE FROM DELETECTE
WHERE DeleteCount<>1
GO
/*Method#03 End DELETE Duplicate Rows using CTE*/

/*Method#04 Start DELETE Duplicate Rows using Physical Address(physloc)[Undocumented vartual column or pseudo  column]*/


INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4
GO


delete from DeleteDuplicate
where %%physloc%% not in
(select min(%%physloc%%) from DeleteDuplicate
group by ID,Name
)

/*Method#04 End DELETE Duplicate Rows using Physical Address(physloc)[Undocumented vartual column or pseudo  column]*/



DROP TABLE DeleteDuplicate


Note: Please try above SQL Script in Testing environment only.

WHO EXECUTED WHICH QUERY?

WHO EXECUTED WHAT?

SELECT db.[transaction name],
sl.NAME 'LoginName',
dp.NAME LOGINROLE,
[current lsn],
[operation],
[transaction id],
[description],
spid,
[begin time],
[transaction sid],
tex.text Most_Recent_SQL_Executed_in_That_Session
FROM Fn_dblog (NULL, NULL) db
LEFT JOIN sys.syslogins sl
ON db.[transaction sid] = sl.sid
LEFT JOIN sys.database_principals dp
ON db.[transaction sid] = dp.sid
LEFT JOIN sys.dm_exec_connections dec
ON db.spid = dec.session_id
LEFT JOIN sys.dm_exec_sessions des
ON db.spid = des.session_id
CROSS apply sys.Dm_exec_sql_text(dec.most_recent_sql_handle) tex
WHERE db.[transaction name] LIKE '%DROPOBJ%'


Using the above query we can find out who executed which query(INSERT/UPDATE/DELETE/DROP etc.), based on the transaction log entry.
Note: we can only get Last executed query in particular session based on column most_recent_sql_handle of system view sys.dm_exec_connections.

Please do not get confuse if you are getting INSERT query but in log you have searched for UPDATE, this is because you have executed INSERT query after UPDATE query in that session,

Book Mark Lookup Key lookup and RID Lookup

In SQL Server execution plans many times we see Key lookup and RID lookup, let us excavate in details what these lookups are and why they appear how we can remove them from execution plans.
1. RID Lookup: when we SELECT any columns (in HEAP Table), which are not part of any indexes but the columns which are in WHERE clause are part of non clustered index then RID (row identifier) lookup happens.
HEAP Table: A Table without clustered index is called HEAP Table

2. Key Lookup: when we SELECT any columns(on Clustered Table), which are not part of any indexes but the columns which are in WHERE clause are part of non clustered index, then Key lookup happens.
When we create a clustered index on a table then it reorder the way records and leaf level of clustered index stores actual data pages with all column values.

When we create non clustered indexes on clustered table (a table with clustered index) then leaf level of each non clustered index store key value of clustered index and key/non key columns values of non clustered index, using this key value of clustered index it perform key lookup and clustered index scan.

When we create non clustered indexes on heap table then, leaf level of each non clustered index store key/non key columns values of non clustered index and 8 bytes row identifier for each row of each non clustered indexes for RID lookup.

Let us consider/check below scenarios.
/*Note: in scenarios i have given perticular blog statement wise like stmt1START,stmt1End and so on*/

/*stmt1START (CREATE Test lookup table)*/

CREATE TABLE TESTLOOKUP
(
EmpId INT,
EmpName VARCHAR(100),
DeptName VARCHAR(100),
Addr VARCHAR(100),
Phone INT
)
/*stmt1END (CREATE Test lookup table)*/

/*stmt2START (INSERT some test rows to verify scenarios)*/
INSERT INTO TESTLOOKUP
SELECT 1 AS ID,'Dinesh' AS NAME, 'IT' AS DEPT, 'DELHI' AS LOC, 1237891 AS PHONE
UNION ALL
SELECT 2, 'Babu', 'COMPUTER', 'MUMBAI', 1236391
UNION ALL
SELECT 3, 'VERMA', 'MGMT', 'PUNE', 1236471
UNION ALL
SELECT 4, 'ALLEN', 'IT', 'NEWYORK', 1647891
UNION ALL
SELECT 5, 'AMIT', 'SALES', 'HYD', 1254891
UNION ALL
SELECT 6, 'ROHAN', 'RETAIL', 'BANGALORE',1234891
UNION ALL
SELECT 7, 'SOHAN', 'IT', 'DELHI', 1236352
UNION ALL
SELECT 8, 'BRYON', 'MGMT', 'GGN', 3456691
GO

INSERT INTO TESTLOOKUP
SELECT TOP 1 Max(EmpId) + 1, 'Dinesh ', 'IT','MUMBAI',1237891 FROM TESTLOOKUP
go 100

INSERT INTO TESTLOOKUP
SELECT TOP 1 Max(EmpId) + 1,'VERMA', 'ITT','DELHI',1237891
FROM TESTLOOKUP
go 3000

/*stmt2END (INSERT some test rows for testing purpose)*/

/*stmt3START (select and check number of records(3108) */
in execution plan, table scan happens below)*/
SELECT *
FROM TESTLOOKUP

/*Execution Plan1*/

/*stmt3END (select and check number of records in execution plan table scan happens)*/

/*stmt4START (select records from table, a table without any indexes,
in execution plan we see table scan for every select stmt) */
SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'SOHAN'

/*Execution Plan2*/

SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'VERMA'

/*Execution Plan3*/

/*stmt4END(select records from table, a table without any indexes,
in execution plan we see table scan for every select stmt) */

/*stmt5START (create non clustered index )*/
CREATE NONCLUSTERED INDEX idxNonClsEmpName
ON TESTLOOKUP(EmpName)

/*stmt5END (create non clustered index )*/

/*stmt6START (select records from the table based on the least value like SOHAN, in execution plan we see RID lookup)*/
SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'SOHAN'

/*Execution Plan4*/

/*stmt6END(select records from the heap table based on the least value like SOHAN, in execution plan we see RID lookup)*/

/*stmt7START (select records from the heap table based on the large value like VERMA, in execution plan we see TABLE SCAN)*/
SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'VERMA'


/*Execution Plan5*/

/*stmt7END (select records from the table based on the large value like VERMA,
in execution plan we see TABLE SCAN)
*/

/* WHEN LOOKUP & WHEN SCAN?
Now here we have question, like when we search EmpName ='SOHAN' then RID LOOKUP and when we search EMPNAME='VERMA' then table scan, why?
Ans: this is because for SOHAN, selectivity is very high for SOHAN means we have less records like in current data distribution we have only 1 record for SOHAN but for VERMA, selectivity is very less means we have huge records >3000 for VERMA.
when we have very high selectivity optimizer go for LOOKUP but when we have very low selectivity optimizer go for scan and
scan whole table/index rather than searching/lookup a values.
*/


/*stmt8START (create clustered index)*/
CREATE CLUSTERED INDEX IdxClsEmpId
ON TESTLOOKUP(EmpId ASC )

/*stmt8END (create clustered index)*/

/*stmt9START(select records from clustered table based on the least value like SOHAN,
in execution plan we see Key lookup )*/
SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'SOHAN'
/*Execution Plan6*/

/*stmt9END (select records from clustered table based on the least value like SOHAN,
in execution plan we see Key lookup )*/

/*stmt10START (select records from clustered table based on the large value like VERMA,
in execution plan we see clustered index scan)*/
SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'VERMA'

/*Execution Plan7*/

/*stmt10END(select records from clustered table based on the large value like VERMA,
in execution plan we see clustered index scan)*/

/*LET US REMOVE KEY lookup, to remove key lookup add DeptName in
non clustered index or create covering index*/
DROP INDEX idxNonClsEmpName on TESTLOOKUP
CREATE NONCLUSTERED INDEX idxNonClsEmpNameDeptName
ON TESTLOOKUP(EmpName, DeptName)


SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'SOHAN'
/*Execution Plan8*/

SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'VERMA'

/*Execution Plan9*/

Note: Please try above SQL statements in Test/development environment, this is must know information for performance optimization

Transaction in SQL Server

Transaction in SQL Server

Executing below piece of SQL Code, we will know why we should use Transaction in SQL Server.
according to below code when we execute insert tSQL script with transaction it gives around 25 times more performance comparing to without Transaction tSQL Script.

we should always have Transaction scope AS SMALL AS POSSIBLE based on requirements, as it locks the resources which are part of the transactions till the Transaction is COMMIT/ROLLBACK.

having the transaction with small scope help us to get better performance, fewer locking/blocking and better data/referential integrity.


CREATE TABLE TestTran
(
TranId INT IDENTITY(1, 1),
TranName VARCHAR(100)
)

SET NOCOUNT ON

DECLARE @StartTime1 DATETIME
DECLARE @StartTime2 DATETIME
DECLARE @EndTime1 DATETIME
DECLARE @EndTime2 DATETIME

/*Start SQL Code With Transaction*/
SET @StartTime1= Getdate()
DECLARE @WithTranI INT =1
BEGIN TRAN

WHILE ( @WithTranI <= 100000 ) /*>*/
BEGIN
INSERT INTO TestTran
(TranName)
SELECT 'Transaction ' + Cast(@WithTranI AS VARCHAR)

SET @WithTranI =@WithTranI + 1
END
COMMIT
SET @EndTime1= Getdate()

SELECT Datediff(millisecond, @StartTime1, @EndTime1) AS TimediffWithTranInMilliSecond
SELECT Datediff(second, @StartTime1, @EndTime1) AS TimediffWithTranInSecond
/*End SQL Code With Transaction*/


/*Start SQL Code Without Transaction*/
SET @StartTime2= Getdate()
DECLARE @WithOutTranI INT =1

WHILE ( @WithOutTranI <= 100000 ) /*>*/
BEGIN
INSERT INTO TestTran
(TranName)
SELECT 'Transaction ' + Cast(@WithOutTranI AS VARCHAR)

SET @WithOutTranI =@WithOutTranI + 1
END

SET @EndTime2= Getdate()

SELECT Datediff(millisecond, @StartTime2, @EndTime2) AS TimediffWithOutTranInMilliSecond
SELECT Datediff(second, @StartTime2, @EndTime2) AS TimediffWithOutTranInSecond
/*End SQL Code Without Transaction*/


DROP TABLE TestTran

GO


Note: Please execute this script in development/test environment and use transaction scope wisely.

Nth Highest Salary in SQL Server

Please find Below Script to get 14 different ways to find Nth Highest salary.

In script, I have selected 3rd highest salary.
For any other highest salary, Please modify queries accordingly
i.e in below Queries for 5th Highest 3 need to replace by 5 except Nested Sub Queries.

There might be some other ways too, as of now I was able to figure out below.


CREATE TABLE Employees
(
Empid INT IDENTITY(1, 1),
NAME VARCHAR(20),
Salary INT,
DeptId INT
)

INSERT INTO Employees
(NAME,
Salary,
DeptId)
VALUES ('Dinesh', 5000, 1),
('Babu' , 4000, 1),
('Verma' , 6000, 2),
('Allen' , 7000, 3),
('Joseph', 8000, 1),
('Denny' , 1000, 5),
('Ramy' , 2000, 5),
('Ramesh', 2000, 1),
('John' , 9000, 2),
('Mohan' , 5000, 1),
('Sohan' , 5000, 1),
('Brant' , 8000, 1),
('Hogg' , 6000, 3)


-- First Check 3rd Highest Salary using query with Order by Clause
SELECT *
FROM Employees
ORDER BY Salary DESC
-- According to Above query Output, 3rd highest salary is 7000

--1. Using Analytical Dense_Rank Function
SELECT TOP 1 Salary
FROM (SELECT TOP 100 PERCENT NAME,
Salary,
Dense_rank()
OVER(
ORDER BY Salary DESC) AS SalRank
FROM Employees
ORDER BY Salary DESC) EmpSalary
WHERE salrank = 3

--2. Using Nested Sub Queries
SELECT Max(Salary) AS Salary
FROM Employees
WHERE Salary < (SELECT Max(Salary) FROM Employees WHERE Salary < (SELECT Max(Salary) FROM Employees)) /*/>/>*/

--3. Using Corelated Sub Queries
SELECT TOP 1 Salary
FROM Employees e
WHERE 3 = (SELECT Count(DISTINCT Salary)
FROM Employees ee
WHERE e.Salary <= ee.Salary) /*/>/>*/

--4. Using Min and Top Clause
SELECT Min(Salary) AS Salary
FROM (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal

--5. Using Min,Top and Order by Clause
SELECT TOP 1 Salary
FROM (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ORDER BY Salary ASC

--6. Using MAX, Top, NOT IN and Order BY Clause
SELECT Max(Salary) AS Salary
FROM Employees
WHERE Salary NOT IN (SELECT DISTINCT TOP (3-1) Salary
FROM Employees e
ORDER BY Salary DESC)

--7. Using Top, NOT IN and Order BY Clause
SELECT TOP 1 Salary
FROM Employees
WHERE Salary NOT IN (SELECT DISTINCT TOP (3-1) Salary
FROM Employees e
ORDER BY Salary DESC)
ORDER BY Salary DESC

--8. Using MIN, INNER JOIN,Top and Order BY Clause
SELECT Min(e.Salary) AS Salary
FROM Employees e
INNER JOIN (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary

--9. Using MIN, INNER JOIN,Top and Order BY Clause
SELECT TOP 1 e.Salary
FROM Employees e
INNER JOIN (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary
ORDER BY e.Salary ASC

--10. Using LEFT JOIN,MAX, Top and Order BY Clause
SELECT Max(e.Salary) AS Salary
FROM Employees e
LEFT JOIN (SELECT DISTINCT TOP (3-1) Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary
WHERE Sal.Salary IS NULL

--11. Using LEFT JOIN,Top and Order BY Clause
SELECT TOP 1 e.Salary
FROM Employees e
LEFT JOIN (SELECT DISTINCT TOP (3-1) Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary
WHERE Sal.Salary IS NULL
ORDER BY e.Salary DESC

--12. Using RIGHT JOIN,Top and Order BY Clause
SELECT TOP 1 Sal.Salary
FROM Employees e
RIGHT JOIN (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary
ORDER BY e.Salary ASC

--13. Using MIN, RIGHT JOIN,Top and Order BY Clause
SELECT Min(Sal.Salary) AS Salary
FROM Employees e
RIGHT JOIN (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary


--14. Using OffSET, FETCH and Order BY Clause
SELECT DISTINCT Salary FROM Employees
ORDER BY salary DESC
OFFSET 3-1 ROWS
FETCH NEXT 1 ROWS ONLY


Please make sure you are trying this in development environment.

xp_readerrorlog/sp_readerrorlog in SQL Server

xp_readerrorlog/sp_readerrorlog

SQL Server support undocumented extended stored procedures (xp_readerrorlog) to read error logs, we can also see proc sp_readerrorlog, which ultimately uses procedure xp_readerrorlog in its definition

t-SQL code of sp_readerrorlog:



create proc sys.sp_readerrorlog(
@p1 int = 0,
@p2 int = NULL,
@p3 nvarchar(4000) = NULL,
@p4 nvarchar(4000) = NULL)
as
begin

if (not is_srvrolemember(N'securityadmin') = 1)
begin
raiserror(15003,-1,-1, N'securityadmin')
return (1)
end

if (@p2 is NULL)
exec sys.xp_readerrorlog @p1
else
exec sys.xp_readerrorlog @p1,@p2,@p3,@p4
end




When we execute xp_readerrorlog/sp_readerrorlog it returns sample result as below.




As per definition of sp_readerrorlog, procedure will accept four parameters (@p1, @p2, @p3, and @p4)
Where parameters requires values as below.
@p1 [Log Number] : 0 for current log, 1 for log achieve#1, 2 for log achieve#2 etc…
@p2 [Log Type] : 1  Read SQL server error log, 2  Read SQL server agent error log.
@p3 [Search String1]: first parameter value to search in field Text
@p4 [Search String4]: second parameter value to search in field Text

All above parameters also have default values as 0 or NULL.

Let us take below example for sp_readerrorlog
EXEC sys.sp_readerrorlog 0, 1 ,'failed','user'
According to above statement procedure will read errors from current SQL Server error Log.

In real, actual extended stored procedure is XP_readerrorlog, it accept three more parameters
StartTime,EndTime,SortOrder these three parameters satisfy column LogDate from procedure result

Example:
EXEC sys.xp_readerrorlog 0, 1 , N'failed',N'user','2015-05-25 15:13:02.410','2015-06-15 13:54:10.550','ASC';

According to above statement procedure will read errors from current SQL Server error Log and will have filter criteria on LogDate column as startDate and EndDate in Ascending order

Procedure with required sample parameters name are:

EXEC XP_readerrorlog [LogNumber],[LogType],[SearchStr1],[SearchStr2],[StartTime],[EndTime],[SortOrder]

SQL Server 2016 New Features

SQL Server 2016 New Features (welcome updates)

1. Always Encrypted
Always encrypted is designed to protect data while retrieving or when stored in
Databases, encryption/decryption keys reside in applications in customer trusted environment. SQL Server can perform operations on encrypted data. This help to hide data from DBA and SQL developer. Encryption/decryption of data happens transparently in application which helps for minimal code changes to existing application.

2. Dynamic Data Masking
This feature helps to mask/hide actual values of a sensitive columns and keeping rest as it. i.e. Email/Phone/Salary etc. this can be maintained at user/role level
This is also available in Azure SQL DB V12

3. Row level security
This help to maintain the policies which help to filter specific rows based on the user queries
This is also available in Azure SQL DB V12

4. Stretch Database
This feature helps to move cold or history data to move in azure environment at lower cost and hot and operational data in on-premises. Always encrypted feature is available in azure environment.

5. Real-time Operational Analytics
This feature combines in-memory OLTP with in-memory column store for real time analysis.
It helps to optimize system for optimal transactional performance and increased workload concurrency.

6. PolyBase into SQL Server
This feature help to integrate sql server with hadoop

7. Native JSON Support
8. Java script object notation (JSON) currently not supported natively by sql server to support it has to have t-SQL,SQLCLR, JavaSupport. Now in this release JSON Support feature help to integrate SQL server directly like xml.

9. Enhancements to AlwaysOn
This release will have up to three synchronous replicas and will support DTC and also support for round robin load balancing for secondary replicas. And will also have automatic fail over based on database health.

10. Enhanced In-Memory OLTP
This release will support more memory (in TB) and greater number of parallel CPU’s and will also support for extended t-SQL surface areas.

11. Revamped SQL Server Data Tools
This release will consolidate different version of SQL Server data tools into one.

SQL Server System Databases

MS SQL Server internally uses system databases to manage/maintain every SQL Server activity.
When we install SQL server following system databases created with default recovery model

1. Master(Simple): Records all system level info for an instance of SQL server, SQL server logins, link server
Configuration, service broker end points, system stored proc and functions
Full recovery model for master database is supported and can be changed if master database corrupted rest of the databases will also not work we need to run setup.exe to rebuild the instance of sql server.


2. Model(configurable: Is a template database, when we create new database this new database always contain the exact object/configuration of model database.


3. Msdb(Simple): Is used by SQL Server agent also by alert and jobs when we take backup(Log/diff/tran)
Respective entries also get stored in MSDB..BACKUPSET


4. Tempdb(Simple): Is a temporary database which generally hold temporary objects like (local/global temp table etc) and also intermediate result set. On start of SQL server instance tempdb database get re created & log space always reclaimed. We cannot backup tempdb database.


5. Distribution: When we configure any replication (i.e. Transactional, merge etc) a new Distribution database gets created having data file distribution.mdf and log file distribution_log.ldf


6. Resource: SQL server also uses read only Resource database to maintain all system level information under sys schema. These all system objects physically reside in resource database but logically appear in all databases under sys schema like sys.objects.

Resource database always has database id 32767 & uses mssqlsystemresource.ldf log and mssqlsystemresource.mdf as data file which always present at. :\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Binn\
SQL server cannot backup/restore detach/attach resource database like other databases, its .ldf & .mdf file should be copied and paste according to system requirement (copy paste is not possible with other databases).
Resource database info can be checked using below query.

SELECT
'Resource Database' AS 'Database Name'
, NAME AS [Database File]
, FILENAME AS [Database File Location]
FROM sys.sysaltfiles
WHERE DBID = 32767

SQL Server Copy-Only Backups

SQL Server support copy-only backups from SQL 2005 onwards, this is irrespective of traditional database sequences. When we take database backups it updates LSN (Log sequence number) in system table MSDB.dbo.BACKUPSET
When we take differential backup then it maintain differential_base_lsn which is after Database full backup without copy only, if we again take Database full backup then differential_base_lsn get changes.

With copy-only backup approach it won’t change differential_base_lsn remain same when we take next differential backup, so copy-only backup preserve existing log archive point.
Transaction log never truncated after copy-only backups, it is maintained by column is_copy_only Of system table MSDB.dbo.BACKUPSET.

Copy-only backup is possible using t-SQL code, power shell and SSMS.
A. t-SQL Code for full and transaction backup
Full Backup:
BACKUP DATABASE MyCopyOnlyDatabase TO disk='c:\copyonlydatabase.bak' WITH COPY_ONLY

Transaction Log Backup:
BACKUP LOG MyCopyOnlyDatabase TO disk='c:\Database\copyonlydatabasetran.trn' WITH COPY_ONLY

B. Using PowerShell
Use the Backup-SqlDatabase cmdlet with the -CopyOnly parameter.

C. SSMS

Copy-only backup is not supported for differential backup if we take differential backup then above copy-only backup checkbox disables.