How to use Output Clause?

It returns information from each row affected by any DML operation [INSERT/UPDATE/DELETE], it means you can use it with only DML statements. These results can be retuned to the processing application as confirmation message or archiving purpose, and they can be inserted into a physical or temporary or memory table.

You can get all columns [*] or you can specify the columns to be returned by this clause.

We have two column prefixes DELETED and INSERTED. DELETED returns deleted rows by UPDATE and DELETE operations, while INSERTED returns newly inserted values/rows by INSERT or UPDATE statement.

Syntax:
OUTPUT [column list to be returned] INTO [table/table variable/temporary table] [(Column list)]

Note: You can use OUTPUT clause without INTO clause, just to return data to the processing application, if you want to save/insert affected data into any historical or archival table then you have to use INTO clause.

Example:
-- Create Temporary Table
Create Table #Table1
(
ID int
,val1 int
,val2 int
)

-- Create history table
Create Table #Actions_Performed_ON_Table1
(
ID int
,val1 int
,val2 int
,Actions varchar(20)
,Date DateTime
)

-- INSERT Dummy Data
BEGIN
Declare @ID int, @val1 int, @val2 int
SELECT @ID=isnull(@ID,0)+1, @Val1=isnull(@Val1,0)+1, @Val2=isnull(@val2,0)+1
INSERT into #Table1
Select @ID+ID, @val1+val1, @val2+val2 From #Table1
Go 5
END

-- Using output clause with INSERT Statement to capture new inserted values
INSERT into #table1(ID,val1,val2)
Output INSERTED.ID, INSERTED.val1, INSERTED.val2, 'INSERT', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
values(10,10,10)

Select * From #table1 Where Id = 10
Select * From #Actions_Performed_ON_Table1

-- Using output clause with UPDATE Statement to capture deleted data
Update t Set Id = ID + 1, val1 = Val1 + 1, val2 = val2 + 1
Output DELETED.ID, DELETED.val1, DELETED.val2, 'UPDATE', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
From #table1 t
Where Id=10

Select * From #table1 Where Id = 11
Select * From #Actions_Performed_ON_Table1

-- Using output clause with UPDATE Statement to capture new inserted data
Update t Set Id = ID - 1, val1 = Val1 - 1, val2 = val2 - 1
Output INSERTED.ID, INSERTED.val1, INSERTED.val2, 'UPDATE', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
From #table1 t
Where Id=11

Select * From #table1 Where Id = 10
Select * From #Actions_Performed_ON_Table1

-- Using output clause with Delete Statement to capture deleted data
Delete From #table1
Output DELETED.ID, DELETED.val1, DELETED.val2, 'DELETE', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
Where Id=10

Select * From #table1 Where Id = 10
Select * From #Actions_Performed_ON_Table1

-- Drop temporary tables
Drop Table #table1
Drop Table #Actions_Performed_ON_Table1

Programming Replication Distribution Agent Executable

Distribution replication agent can be controlled by programmatically described below,
You can copy and paste below code in a batch file and then execute that batch file after replacing the required information with your actual parameters. It will execute the Distribution agent.

Required Parameters:
Distributor: Distributor instance name
DistributorSecurityMode: 0 for SQL Server authentication that is default, and 1 for window authentication

Publisher: Publisher instance name
PublisherDB: Publinsher's database
Publicaiton: Publication name

Subscriber: Subscriber instance name
SubscriptionDB: Subscriber's database
SubcriberSecurityMode: 0 for SQL Server authentication that is default, and 1 for window authentication
SubscriptionType: Subcriber's connection used by the agent, 0=SQL Server, 1=ODBC Datasource, 2=OLE DB Datasource
SubscriptionStream: number of connection per distribtion agent to apply batches of changes in parallel to a subscriber (1 to 64)

Script:

REM -- Declare the variables.
SET Publisher=instancename
SET Subscriber=instancename
SET PublicationDB=AdventureWorks
SET SubscriptionDB=AdventureWorksReplica
SET Publication=AdvWorksProductsTran

REM -- Start the Distribution Agent with four subscription streams.
"C:\Program Files\Microsoft SQL Server\90\COM\DISTRIB.EXE" -Subscriber %Subscriber% -SubscriberDB %SubscriptionDB% -SubscriberSecurityMode 1 -Publication %Publication% -Publisher %Publisher% -PublisherDB %PublicationDB% -Distributor %Publisher% -DistributorSecurityMode 1 -Continuous -SubscriptionType 0 -SubscriptionStreams 4

It will execute the subscriber’s distribution agent for specified publication with SQL Server subscription type having four Parallel threads to replicate the change in windows authentication mode.

Programming Replication Snapshot Agent Executable

Snapshot replication agent can be controlled by programmatically described below,
You can copy and paste below code in a batch file and then execute that batch file after replacing the required information with your actual parameters. It will execute the snapshot agent.

Required Parameters:
Publisher: instance name
PublicationDB: publisher database name
Publication: publicaion name
Replicationtype: 1 for Transactional Replicaion, 2 for Merg Replication, skip it for Snapshot replication
Distributor: Distributor instance name
DistributorSecurityMode: 0 for SQL Server authentication that is default, and 1 for window authentication
OutputVerboseLevel: 0 for print only Error Messages, 1 for to print whole Progress report, and 2 for to print Both Error and Progress report Messages

Script for batch file:

REM -- Declare variables
SET Publisher=InstanceName
SET PublicationDB=AdventureWorks
SET Publication=AdvWorksSalesOrdersMerge

REM --Start the Snapshot Agent to generate the snapshot
"C:\Program Files\Microsoft SQL Server\90\COM\SNAPSHOT.EXE" -Publication %Publication% -Publisher %Publisher% -Distributor %Publisher% -PublisherDB %PublicationDB% -ReplicationType 2 -OutputVerboseLevel 1 -DistributorSecurityMode 1

It will execute the publication’s snapshot agent for merge type replication by using windows authentication mode and printing the whole progress report. Distributor and publisher are same, you can use different by defining another parameter for distributor.

Ranking rows of a result set...

Rows can be ranked in a result set [Selection from one table or multiple tables by using joins] by using Rank() function. If two or more rows tie for a rank, tied rows will get the same rank.

Example:
Two temporary tables created, and populated with test data, two queries created one for single table selection and second query to show the results from two tables by using joins.

CREATE TABLE #TEMP
(ID INT
,VAL1 VARCHAR(1)
,VAL2 VARCHAR(10)
,VAL3 VARCHAR(5)
)

CREATE TABLE #TEMP1
(ID1 INT
,VAL11 VARCHAR(1)
,VAL21 VARCHAR(10)
,VAL31 VARCHAR(10)
)

INSERT INTO #TEMP VALUES(1,'A','ABC','TEST1')
INSERT INTO #TEMP VALUES(1,'F','ADC','TEST2')
INSERT INTO #TEMP VALUES(1,'G','ABD','TEST3')
INSERT INTO #TEMP VALUES(2,'H','ABC','TEST4')
INSERT INTO #TEMP VALUES(2,'K','ADC','TEST5')
INSERT INTO #TEMP VALUES(3,'L','ABD','TEST6')

INSERT INTO #TEMP1 VALUES(1,'Z','ABC','TESTING1')
INSERT INTO #TEMP1 VALUES(2,'R','ABC','TESTING4')
INSERT INTO #TEMP1 VALUES(3,'P','ABD','TESTING6')

Single table selection:
SELECT *,RANK() OVER(PARTITION BY ID ORDER BY VAL1) RANK
FROM #TEMP

ID	val1	val2	val3	RANK
1	A	ABC	Test1	1
1	F	ADC	Test2	2
1	G	ABD	Test3	3
2	H	ABC	Test4	1
2	K	ADC	Test5	2
3	L	ABD	Test6	1

Multiple tables selection:
SELECT *,RANK() OVER(PARTITION BY VAL21 ORDER BY VAL1) RANK
FROM #TEMP T
INNER JOIN #TEMP1 T1 ON T.ID=T1.ID1

ID	val1	val2	val3	ID1	val11	val21	val31	RANK
1	A	ABC	Test1	1	Z	ABC	Testing1	1
1	F	ADC	Test2	1	Z	ABC	Testing1	2
1	G	ABD	Test3	1	Z	ABC	Testing1	3
2	H	ABC	Test4	2	R	ABC	Testing4	4
2	K	ADC	Test5	2	R	ABC	Testing4	5
3	L	ABD	Test6	3	P	ABD	Testing6	1

comparing current row with previos and next row in a table

you must define identity column or there should be an integer column to keep the sequence of the rows, or you can import the data into a temporary table with an additional identity column.

here is the script:

Create Table #Temp
(I_Col int identity(1,1)
,val1 int
)

Insert into #Temp values(10)
Insert into #Temp values(25)
Insert into #Temp values(67)
Insert into #Temp values(98)
Insert into #Temp values(11)

Select * From #Temp

I_Col (identity column)	Val1
1	10
2	25
3	67
4	98
5	11

Select Curr.val1 Current_Value
,isnull(Prev.val1,0) Prev_Value
, isnull(_Next.val1,0) Next_Value
From #Temp Curr
Left outer Join #Temp Prev On Prev.I_Col = Curr.I_Col -1
Left Outer Join #Temp _Next On _Next.I_Col = Curr.I_Col + 1

Current_Value	Prev_Value	Next_Value
10	0	25
25	10	67
67	25	98
98	67	11
11	98	0

how to view association among File Groups, System Logical and Physical Files and Tables

You can use this code to view file group, logical filename and physical filename associated to a table. It can be beneficial if you have large number of database files and want to see objects (tables) in a file or to view the file associated to a table.

To see whole list of tables in each data file
A table can have only one value at a time in index table (0 or 1), 1 for clustered index and 0 for heap, so you can use both values, if a table has clustered index it will not have heap associated value in index table and vice versa.

SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SI.INDEX_ID IN (0,1)

To see whole list of tables in a specific data file
You can get file id or file name or logical file name from below query or you can use FILE_ID() function if you know logical file name.

SELECT FILEID, FILENAME, NAME FROM SYS.SYSFILES

SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SF.FILEID = FILE_ID('Logical File Name')

To see filename for a table
SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SI.OBJECT_ID = OBJECT_ID('Table Name')

Getting All Disks Free Space information on an instance

you must define linked servers on the instance executing this code if it has to calculate the free disk space information from remote servers.

BEGIN
SET NOCOUNT ON
DECLARE @TOTALCOUNT INT
,@I INT
,@SERVER_NAME NVARCHAR(100)
,@DESCR NVARCHAR(100)
,@STMT NVARCHAR(200)

/* Temporary Table holding Server names */
CREATE TABLE #SERVERS
(PID INT IDENTITY(1,1)
,SERVER_NAME VARCHAR(100)
,DESCR VARCHAR(100))

/* List of All Servers under action */
INSERT INTO #SERVERS(SERVER_NAME,DESCR)
VALUES('SERVER1','FIRST TEST SERVER')

INSERT INTO #SERVERS(SERVER_NAME,DESCR)
VALUES('SERVER2','SECOND TEST SERVER')

/* Total Number of Servers */
SELECT @TOTALCOUNT=COUNT(*) FROM #SERVERS

/* Temporary global table holding Server, Drive and its free space information*/
CREATE TABLE ##MYFREESPACE
(SERVER_NAME VARCHAR(30)
,DRIVE VARCHAR(50)
,FREESPACE BIGINT)

/* Iteration to perform action on each server */
SET @I=1
WHILE (@I<=@TOTALCOUNT)
BEGIN
SELECT @SERVER_NAME = SERVER_NAME,@DESCR=DESCR FROM #SERVERS WHERE PID=@I

/* Try & Catch Block in case of any error */
BEGIN TRY
INSERT INTO ##MYFREESPACE(SERVER_NAME,DRIVE) VALUES(@SERVER_NAME,@DESCR)

SET @STMT = LTRIM(RTRIM(@SERVER_NAME)) + '.MASTER.SYS.XP_FIXEDDRIVES'

INSERT INTO ##MYFREESPACE(DRIVE,FREESPACE)
EXEC SP_EXECUTESQL @STMT
END TRY

BEGIN CATCH
INSERT INTO ##MYFREESPACE(SERVER_NAME,DRIVE) VALUES(@SERVER_NAME,LEFT(CONVERT(VARCHAR,@@ERROR)+'-'+ERROR_MESSAGE(),50))
END CATCH

SET @I = @I + 1

END

/* Send Email by attaching the whole manipulated information */
EXEC MSDB.DBO.SP_SEND_DBMAIL
@PROFILE_NAME = 'TEST ALERTS',
@RECIPIENTS = 'TEST@TEST.COM',
@QUERY = ' SELECT ISNULL(SERVER_NAME,'''') SERVER_NAME, DRIVE,CAST(ISNULL(FREESPACE,0.00)/1024.00 AS DECIMAL(20,3)) FREESPACE_GB FROM ##MYFREESPACE' ,
@SUBJECT = 'FREE DISK SPACE INFORMATION',
@ATTACH_QUERY_RESULT_AS_FILE = 0 ;

/* Drop Temporary Tables */
DROP TABLE #SERVERS
DROP TABLE ##MYFREESPACE

END

How to get list of all stored procedures?

You can use following stored procedure with four optional input parameters to get a list of all stored procedures in the current environment.

EXEC SP_STORED_PROCEDURES @sp_name = 'procedure name'
, @sp_owner = 'schema name'
, @sp_qualifier = 'database name'
, @fUsePattern = 'fUsePattern'

Note:

1. All parameters are optional.
2. @sp_name and @sp_owner support wildcard pattern matching (underscore “_“, percent “%” and brackets []).
3. @sp_qualifier it will have null or current database name only.
4. @fUsePattern, it can be 0 (wildcard pattern matching is off) or 1 (wildcard pattern matching is on), by default it is 1.

Examples:

1. To see complete list of all stored procedures in current database
   EXEC SP_STORED_PROCEDURES
2. To see complete list of procedures, procedure name starting with “fn” characters
   EXEC SP_STORED_PROCEDURES @sp_name = 'fn%'
3. To see complete list of procedure, procedure name starting with “fn” and schema name starting with “s” characters
   EXEC SP_STORED_PROCEDURES @sp_name = 'fn%', @sp_owner = 's%'

Moving database files to another new location

You can move database data and log files to any other location by following the below steps, these come under Planned Relocation.

1. Check if there is any user connected to DB by executing the below query.
   
   SELECT SPID,LOGINAME,HOSTNAME,PROGRAM_NAME FROM SYS.SYSPROCESSES WHERE DBID=DB_ID('database_name')
   
   If there is any replication agent [Log Reader Agent] is running, stop that agent from Replication Monitor, Kill all other user connection if they are not important as:
   
   KILL @SPID – get spid from above query
   
   
2. Run ALTER DATABASE database_name SET OFFLINE.
3. Move the file to the new location.
4. Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path/os_file_name' )
5. Run ALTER DATABASE database_name SET ONLINE.
6. Run replication agent [Log Reader Agent], if stopped.
   

Example:

ALTER DATABASE DB1 SET OFFLINE

Copy log file from the current location to a new location by using Copy and Paste. E.g. you move File1.ldf from c drive to d drive.

ALTER DATABASE database_name MODIFY FILE ( NAME = 'DB1_Log', FILENAME = 'D:\File1.ldf' )

ALTER DATABASE DB1 SET ONLINE

Table's rows count without using COUNT() function

You can get table's total number of rows as:

SELECT SUM(ROWS) AS Total_Rows
FROM SYS.SYSINDEXES
WHERE ID=OBJECT_ID('Table1')
AND INDID IN (0,1)

OBJECT_ID('Table1'):
This function will return the object id for table "Table1" or any other specified table.

INDID:
It can be 0 for heap and 1 for clustered index, and it will have only one value at a time 0 or 1, greater than 1 values are for non-clustered indexes.