Retrieve data after execute TRUNCATE command by the help of Transaction

 

We do use truncate table, but we don't get data after execute this command, 

But how we can  retrieve data after execute TRUNCATE command, so follow the below steps.

-- Create Test Table with "TruncateDemoTable" name

CREATE TABLE TruncateDemoTable (ID INT)

INSERT INTO TruncateDemoTable (ID)

SELECT 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

GO

-- Check the data before truncate

SELECT * FROM TruncateDemoTable

GO

-- Begin Transaction

BEGIN TRAN

-- Truncate Table

TRUNCATE TABLE TruncateDemoTable

GO

-- Check the data after truncate

SELECT * FROM TruncateDemoTable

GO

-- Rollback Transaction

ROLLBACK TRAN

GO

-- Check the data after Rollback

SELECT * FROM TruncateDemoTable

GO

-- Clean up

DROP TABLE TruncateDemoTable

GO

 

Fastest way to get ROWCOUNT of any table..

During performance health check, we generally use below query to get total number of rows in any table.

SET STATISTICS IO ON 

SELECT COUNT(*) FROM CustomerTbl WITH(NOLOCK)

But this can be take lot of time to scan whole table if table is having TB's of data.

So, below is the alternative query to get row count of all the tables of your database in one go, and if you want, you can search for a particular table as well.

SELECT  SCHEMA_NAME(t.schema_id) SchemaName,

        t.[name] TableName, 

        SUM(p.row_count) TotalRows

FROM sys.tables t

INNER JOIN sys.dm_db_partition_stats p

ON t.object_id = p.object_id AND t.type_desc = 'USER_TABLE'  AND p.index_id IN (0,1)

-- WHERE t.[name] = 'CustomerTbl'

GROUP BY t.schema_id, t.[name]

ORDER BY TotalRows DESC

Use of recursive CTE to get user tree hierarchy (Top to Bottom)

Below script can be use with recursive CTE to get user hierarchy  in SQL Server..

DECLARE @userType INT = 3

DECLARE @emp AS TABLE (Id INT, FName VARCHAR(100),LName VARCHAR(100), ReportTo INT)

INSERT INTO @emp (Id,  FName, LName, ReportTo) VALUES

(1, 'Tom','Joe', 5)

,(2, 'Peter','Pep', 4)

,(3, 'Sem','Leo', 4)

,(4, 'Neeraj','Gusain', 0)

,(5, 'Mark','Pettit', 3)

,(6, 'Sara','Thomas', 2)

,(7, 'Jobb','Mular', 2)

,(8, 'Mark','Rekhei', 5)

,(9, 'Sara','Leon', 3)

,(10, 'Peter','James', 8)

;WITH CTE 

AS

(

SELECT id, FName, LName, ReportTo, 0 as [level]

FROM @emp

WHERE (ReportTo = 0 AND  @userType = 0) OR id = @userType

    UNION ALL

SELECT e.id, e.FName, e.LName, e.ReportTo, (ct.[level] + 1) AS [level]

FROM @emp e

JOIN CTE ct ON ct.id = e.ReportTo

WHERE e.ReportTo <> 0 

)

SELECT * FROM CTE 

ORDER BY id

Processes in SQL Server and script to Kill All Inactive Sessions – Kill Sleeping Sessions from sp_who2

We have different kind of Process status in SQL Server, which we can find by using below query

  SELECT * FROM master.dbo.sysprocesses

  OR 

  SP_WHO2

As we know every process need Disk, Thread and CPU to run. so below is the explanation to understand each process.

Process Status	THREAD	CPU	DISK	Description
Pending	Not available	Not available	Available	The Process has no thread, no CPU, but waiting for them to available.
Runnable	Available	Not available	Available	The Process has thread but no CPU, but waiting for CPU to available.
Running	Available	Available	Available	The Process has all three and is in running state
Suspended	Available	Available	Not available	The Process is waiting for some event to get completed (May be lock or IO is not available)
Sleeping	Not available	Not available	Not available	The Process is doing nothing and waiting for further commands
Dormant				The SQL Server is reseting this process
Background				These background process run by SQL Server for any job
SpinLock				This Process is busy waiting in CPU for it's own turn.

The below script to kill all inactive sessions. To kill sleeping sessions from sp_who2 can also use this script.

DECLARE @user_spid INT

DECLARE CurSPID CURSOR FAST_FORWARD

FOR

       SELECT SPID

       FROM master.dbo.sysprocesses (NOLOCK)

       WHERE spid>50 -- avoid system threads

       AND status='sleeping' -- only sleeping threads

       AND DATEDIFF(HOUR,last_batch,GETDATE())>=24 -- thread sleeping for 24 hours

       AND spid<>@@spid -- ignore current spid

       OPEN CurSPID

 

       FETCH NEXT FROM CurSPID INTO @user_spid

       WHILE (@@FETCH_STATUS=0)

       BEGIN

              PRINT 'Killing '+CONVERT(VARCHAR,@user_spid)

              EXEC('KILL '+@user_spid)

              FETCH NEXT FROM CurSPID INTO @user_spid

       END

       CLOSE CurSPID

       DEALLOCATE CurSPID

GO

Move TempDB location for best SQL Server performance

We should move our TempDB to a faster drive for better performance.

Generally , when we install SQL Server by default and we do not customize it, it is quite possible that our TempDB is located on the same drive where our Operating System is installed. It is never a good idea to install any database on the same drive as Operating System. so we should move that TempDB to the another faster drive location.

To check the location of our TempDB, execute below store procedure.

USE TempDB
GO
EXEC sp_helpfile
GO

For moving this TempDB file location execute below query

Use Master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME= tempdev, FILENAME= 'D:\tempdb_mssql_1.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME= templog, FILENAME= 'D:\templog.ldf')
GO

After moving file to D:\ we should restart the SQL Server, other wise system will still pointing the old location. 

Note: Remember to check the security or permission of the new TempDB location.

SQL Server Cursor

What is a SQL Server Cursor

A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time.  The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner.  SQL Server cursors are used for Development, DBA and ETL processes.

How to Write a Cursor in SQL Server

Creating a SQL Server cursor is a consistent process.  Once you learn the steps you are easily able to duplicate them with various sets of logic to loop through data. Let's walk through the steps:

1. Declare your variables (file names, database names, account numbers, etc.) that you need in the logic and initialize the variables.
   • This logic would be updated based on your needs.
2. Declare cursor with a specific name (i.e. db_cursor in this tip) that you will use throughout the logic along with the business logic (SELECT statement) to populate the records the cursor will need. The cursor name can be anything meaningful.  This is immediately followed by opening the cursor.
   • This logic would be updated based on your needs.
3. Fetch a record from cursor to begin the data processing.
   • NOTE - There are an equal of number of variables declared for the cursor, columns in the SELECT statement and variables in the Fetch logic.  In the example in this tip there is only one variable, one column selected and variable fetched, but if five pieces of data were needed for the cursor then five variables would need to be selected and fetched as well.
4. The data process is unique to each set of logic. This could be inserting, updating, deleting, etc. for each row of data that was fetched. This is the most important set of logic during this process that is performed on each row.
   • This logic would be updated based on your needs.
5. Fetch the next record from cursor as you did in step 3 and then step 4 is repeated again by processing the selected data.
6. Once all of the data has been processed, then you close cursor.
7. As a final and important step, you need to deallocate the cursor to release all of the internal resources SQL Server is holding.

From here, check out the examples below to get started on knowing when to use SQL Server cursors and how to do so.

-- 1 - Declare Variables -- * UPDATE WITH YOUR SPECIFIC CODE HERE * DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name -- Initialize Variables -- * UPDATE WITH YOUR SPECIFIC CODE HERE * SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) -- 2 - Declare Cursor DECLARE db_cursor CURSOR FOR -- Populate the cursor with your logic -- * UPDATE WITH YOUR SPECIFIC CODE HERE * SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') -- Open the Cursor OPEN db_cursor -- 3 - Fetch the next record from the cursor FETCH NEXT FROM db_cursor INTO @name -- Set the status for the cursor WHILE @@FETCH_STATUS = 0 BEGIN -- 4 - Begin the custom business logic -- * UPDATE WITH YOUR SPECIFIC CODE HERE * SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName -- 5 - Fetch the next record from the cursor FETCH NEXT FROM db_cursor INTO @name END -- 6 - Close the cursor CLOSE db_cursor -- 7 - Deallocate the cursor DEALLOCATE db_cursor

Explanation of Cursor Syntax in SQL Server

Based on the example above, cursors include these components:

• DECLARE statements - Declare variables used in the code block
• SET\SELECT statements - Initialize the variables to a specific value
• DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
  • NOTE - There are an equal number of variables in the DECLARE 
• OPEN statement - Open the cursor to begin data processing
• FETCH NEXT statements - Assign the specific values from the cursor to the variables to match the DECLARE CURSOR FOR and SELECT statement
  • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
• WHILE statement - Condition to begin and continue data processing
• BEGIN...END statement - Start and end of the code block
  • NOTE - Based on the data processing, multiple BEGIN...END statements can be used
• Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE statement - Destroys the cursor

Why Use a Cursor in SQL Server

Although using an INSERT, UPDATE or DELETE statement to modify all of the applicable data in one transaction is generally the best way to work with data in SQL Server, a cursor may be needed for:

• Iterating over data one row at a time
• Completing a process in a serial manner such as SQL Server database backups
• Updating data across numerous tables for a specific account
• Correcting data with a predefined set of data as the input to the cursor

Find First Sunday of Next Month using EOMONTH is SQL Server

This below query to get First Sunday of Next Month.

DECLARE @tbl_Test_EOMONTH AS TABLE

(

SampleDate DATETIME

)

 

INSERT INTO @tbl_Test_EOMONTH VALUES ('2015-12-20')

INSERT INTO @tbl_Test_EOMONTH VALUES ('2015-11-08')

INSERT INTO @tbl_Test_EOMONTH VALUES ('2015-10-16')

INSERT INTO @tbl_Test_EOMONTH VALUES ('2015-09-26')

INSERT INTO @tbl_Test_EOMONTH VALUES ('2016-01-31') 

SELECT DATEADD(DAY,8-DATEPART(WEEKDAY,DATEADD(DAY,0,EOMONTH([SampleDate])))

,EOMONTH([SampleDate])) AS FirstSunday_ofTheNextMonth

FROM @tbl_Test_EOMONTH

GO

Script to find a list of Weekends between two Dates in SQL Server

This below query to get all weekends between the given date range.

DECLARE @beginDate DATE = '20201201'

DECLARE @endDate DATE = '20201231'  

 

DECLARE @Weekend TABLE

(

 Weekend DATE PRIMARY KEY

,IsWeekend BIT

)

 

WHILE @beginDate <= @endDate 

BEGIN 

INSERT INTO @Weekend 

SELECT @beginDate AS Weekend 

,(CASE WHEN DATEPART(WEEKDAY, @beginDate) In (7, 1) THEN 1 ELSE 0 END) AS IsWeekend 

SET @beginDate = DateAdd(Day, 1, @beginDate) 

END

 

SELECT Weekend FROM @Weekend WHERE IsWeekend = 1

List all Dates between two dates in SQL Server

This below query to get all dates between the given date range.

DECLARE @StartDateTime DATETIME

DECLARE @EndDateTime DATETIME

 

SET @StartDateTime = '2020-01-01'

SET @EndDateTime = '2020-01-12';

 

WITH DateRange(DateData) AS 

(

    SELECT @StartDateTime as Date

    UNION ALL

    SELECT DATEADD(d,1,DateData)

    FROM DateRange 

    WHERE DateData < @EndDateTime

)

SELECT DateData

FROM DateRange

OPTION (MAXRECURSION 0)

GO

Import data from .sql file to SQL Server database using sqlcmd utility

sqlcmd is a command line utility that is part of the standard installation of SQL Server 2005 or higher, which enables interactive interface with any SQL Server instance to help perform the following tasks:

1. Execute Transact-SQL (T-SQL) statements in SSMS
2. Call a T-SQL script file
3. Use of environment variables in command mode
4. Store the output results of executed queries in a specified text file

How to use SQLCMD

This utility can be download and install from below link 

https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?redirectedfrom=MSDN&view=sql-server-ver15

Example:

After installation of sqlcmd utility

Step 1. Make a database in you SQL server with student table (id int, name varchar(100))

Step 2. make a TEST.sql script file with below code

           USE TestDB;

           INSERT INTO student (id, name)

           VALUES (1,'Neeraj Gusain')

Step 3. open command prompt and write below line to run mentioned script in .sql

            sqlcmd -S 10.100.0.5\SQLEXPRESS -U sa -P admin@123 -i D:\TEST.sql

sqlcmd is a simple, yet powerful scripting environment that helps with the automation of several tasks related to SQL Server. For example, you can write and execute a script that logs you into a specific instance of SQL Server, executes a script from a specified path, and redirects the output to a certain file.

Most of us SQL database administrators are already using PowerShell. The Invoke-SqlCmd cmdlet, from the sqlserver module that’s part of every SQL Server distribution since 2008, packs most of the capabilities of sqlcmd. Also, in SQL Server Management Studio, we have a SQLCMD mode, that simulates the capabilities of sqlcmd, and accepts some of the commands that are not part of the T-SQL language.

sqlcmd was an extension of the osql and isql command line utilities, that contains a rich set of parameters and options to enable automation of various administrative tasks.

1. It’s simple to bind SQL in a .bat file.
2. It runs interactively across various OS platforms.
3. It’s easy to pass command line arguments to a SQL file using sqlcmd.

How to enable SQLCMD

OK, so sqlcmd is a great tool; of that, we’re all convinced. Let’s now talk about how the query editor in SSMS can be used to write statements as sqlcmd scripts. The following example talks about the integration of Windows system commands and T-SQL statements into a single script.

It’s importatnt to remember that sqlcmd mode is not enabled by default. To enter into the sqlcmd mode in the SSMS query editor, follow the steps below:

1. In the Object Explorer right click the SQL Server and then click New Query
   OR
   Open a new Database Engine Query Editor window.
2. On the Query editor, click SQLCMD Mode.

To enable SQLCMD scripting mode:

1. On the SSMS, go to the Tools menu, select Options.
2. Expand the Query Execution tab, and select SQL Server, click the General page.
3. Check the box against By default open new queries in SQLCMD Mode.

Some important points to remember

1. SQLCMD commands must be prefixed with a colon. This makes the difference between SQLCMD commands and Transact-SQL clear.
2. The :CONNECT keyword is used to connect to a different server using the same session context.
3. The Operating system commands (in this case, if exists) and del commands used for the demo must start with two exclamation points (!!). The !! indicate that the commands are OS command, which will be executed using the cmd.exe command processor. In this example, the OS command, if exist $(file1) del $(file1) is passed as arguments to cmd.exe.
   
4. The variables that are used as part of the SQLCMD script are case-sensitive. For example, if we query the environment variables, we can see that COMPUTERNAME and computername are two different variables in the output. In the first, the environment variable is queried to return the name of the computer, where as in the second, SSMS isn’t able to resolve the variable and reports an error.

:connect hqdbsp18 PRINT '$(COMPUTERNAME)'   GO :connect hqdbsp17 PRINT '$(computername)'

Examples of SQLCMD

The following example defines an integration of operating system commands and SQL statements together.

Variable are defined using SETVAR, connection is built using CONNECT the keyword, operating system commands are defined using !!, the output file is declared using the OUT keyword, and SQL Statements are placed to fetch the results based on the connection string.

--variable declartion :setvar subscriber1 HQMESRP01   :setvar subscriber2 HQMESRP02 :setvar file1 c:\Data1.txt :setvar file2 c:\Data2.txt   --check for the file and delete if exists !!if exist $(file1) del $(file1) !!if exist $(file2) del $(file2)   --connect to to the subscription 1   :Connect $(subscriber1) --redirect the SQL output to file :out $(file1) select * from [MES_REPL_Rpt_AP]..tb_F4801_woheader where wadoco=2520079 select * from [MES_REPL_Rpt_AP]..tb_F4801_woheader_MES where WorkOrderNumber=2520079 GO   --connect to to the subscription 2   :Connect $(subscriber2)   --redirect the SQL output to file :out $(file2) select * from [MES_HIST_AP]..tb_F4801_woheader where wadoco=2520079 select * from [MES_HIST_AP]..tb_F4801_woheader_MES where WorkOrderNumber=2520079

SQLCMD with scripting variables, and Windows scripting in SSMS

In this example, login is created across multiple servers. T-SQL, sqlcmd and Windows scripting are all integrated, and run using the SSMS interface to create logins.

1. Enable XP_CMDSHELL
2. Define the T-SQL in a SQL file
3. Build the dynamic SQL
4. Verify the output

STEP 1: In this step, xp_cmdshell is enabled. It is not a recommended option, though. This has only been enabled for the purpose of the demo.

sp_configure 'show advanced options', 1;   GO   RECONFIGURE;   GO   sp_configure 'xp_cmdShell', 1;   GO   RECONFIGURE;   GO

STEP 2: A SQL file, SysAdminLoginCreation.sql is created with the create login SQL statement.

USE [master] GO CREATE LOGIN [SQLShackTest] WITH PASSWORD=N'SQLShackDemo123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO

The file looks like below

We create a for loop to loop through each SQL instances.

1. Iterate through the input file.
2. FOR Parameter %j defines the name of the SQL instance.
3. Call sqlcmd using the parameters as part of the command.
4. Repeat for each data item.

MASTER..XP_CMDSHELL 'for /f %j in (\\networkshare\IsDba\server.txt) do sqlcmd -S %j -i \\networkshare \IsDba\SysAdminLoginCreation.sql -E'

sqlcmd executed successfully!

Let’s check whether the logins are created on the those servers

:connect hqdbsp18 select * from sys.syslogins where name='SQLShackTest' GO :connect hqdbsp17 select * from sys.syslogins where name='SQLShackTest'

The logins have also been created for the listed servers.

Passing variables (or argument) as T-SQL and SQL Script file

We can pass the variables as an input to the T-SQL or the SQL script file in the sqlcmd command console. The scripting variables are declared using :SETVAR keyword. The corresponding parameter values passed to T-SQL or SQL script file. The values are enclosed in $(variable name) are fed to the SQLs during the run time. Using -v (version) switch, the parameters are declared and fed to the script. We can list any number of variables with no delimiter in between the parameters. Lets follow the example to understand the mechanism better:

1. The salesorderdetails is the name of table assigned to tablename variable
2. The select statement is prepared along with the tablename variable

C:\Users\ccov648>sqlcmd -S hqdbt01\SQL2017 -E 1> use Python2017 2> :SETVAR tablename salesorderdetail 2> select top(10) * from $(tablename); 3> go

We can assume that database name will be provided as a SQLCMD variable during the deployment process; we can have exactly the same file deployed to all environments. The following example has two parameters, db and tablename. The input parameters are separated by a space in between.

C:\Users\ccov648>sqlcmd -S hqdbt01\SQL2017 -E -i n:\IsDba\SalesOrderDetails.sql -v db=Python2017  tablename=salesorderdetail

Summary