TSQL Script to Clear SQL Server Cache

Below script that can be used to clear SQL Server Cache. Helpful while working on performance tuning of SQL Scripts.


CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
GO

Note that this script is solely for running on Development environments, do not run in Production as it would cause issues to queries that depend on cached data.

HOW TO CONVERT TABLE DATA TO XML AND XML TO TABLE

HOW TO CONVERT TABLE DATA TO XML AND VICEVERSA.

Table to XML and XML to Table

The below code explains how to convert the data in a table to xml form and then convert the xml back into table data.

Creating sample table with data:

CREATE TABLE tmpEmployee(ID INT,NAME VARCHAR(100))
GO

INSERT INTO tmpEmployee
SELECT 1,'Devi'
union
SELECT 2,'Prasad'
GO


SELECT * FROM tmpEmployee
GO

Below code converts data and schema of table tmpEmployee into XML, Then it uses the XML to convert it back into the table:

/*Below code converts Table SCHEMA & Data to XML*/

DECLARE @TableData XML,@TableSchema XML

SELECT @TableSchema = (
select column_name,
data_type,
case(is_nullable)
when 'YES' then 'true'
else 'false'
end as is_nullable,
CHARACTER_MAXIMUM_LENGTH as Charlen
from information_schema.columns [column]
where table_name = 'tmpEmployee'
for xml auto,root('Table')
)

SELECT @TableData = (
SELECT *
FROM tmpEmployee Row
FOR XML AUTO, BINARY BASE64,root('TableData')
)

SELECT @TableSchema,@TableData

/*Below code converts XML to Table*/

if object_id('tempdb..#XMLColumns') is not null
drop table #XMLColumns

SELECT x.value('@column_name', 'sysname') AS column_name
,x.value('@data_type', 'sysname') AS data_type
,x.value('@is_nullable', 'VARCHAR(20)') AS is_nullable
,x.value('@Charlen', 'VARCHAR(20)') AS Charlen
into #XMLColumns
FROM @TableSchema.nodes('/Table/column') TempXML (x)

select * from #XMLColumns

DECLARE @SQL nVARCHAR(MAX) = 'SELECT '

SELECT @SQL = @SQL + '
x.value(''@'+column_name+''', '''+data_type+case when Charlen is null then '' else '('+Charlen+')' end + ''''+') AS ['+column_name+'],'
from #XMLColumns

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)

SELECT @SQL = @SQL + ' FROM @TableData.nodes(''/TableData/Row'') TempXML (x)'

EXEC sp_executeSQl @SQL,N'@TableData xml',@TableData=@TableData

Output:

tmpEmployee:

ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 Devi
2 Prasad


(2 row(s) affected)

@TableSchema:

<Table>
<column column_name="ID" data_type="int" is_nullable="true" />
<column column_name="NAME" data_type="varchar" is_nullable="true" Charlen="100" />
</Table>

@TableData:

<TableData>
<Row ID="1" NAME="Devi" />
<Row ID="2" NAME="Prasad" />
</TableData>

Table generated from XML:

ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 Devi
2 Prasad


(2 row(s) affected)

Impact of TEMPDB Collations on Other Databases

When you create databases with different collations then you have to be very careful when using the temporary tables.

In the below script let us analyze such a scenario.

Creating Sample database with case sensitive collation
[sql]
CREATE DATABASE TESTCOLLATION
COLLATE SQL_Latin1_General_CP1_CS_AS ;
GO
[/sql]

Let us create a sample table in a database and insert data.
[sql]
USE TESTCOLLATION
GO

CREATE TABLE TestTable ( NAME VARCHAR(100))
GO

INSERT INTO TestTable
SELECT ‘TestName’

GO

[/sql]

In the below queries let us query the table.
[sql]
USE TESTCOLLATION
GO
SELECT * FROM TestTable

SELECT * FROM TestTable where NAME = ‘TestName’

SELECT * FROM TestTable where NAME = ‘testname’

GO
[/sql]

output:
[text]
NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-

(0 row(s) affected)
[/text]

It can be found that the third select statement above does not return any rows as the database and the columns in the table are with case sensitive collation.

Now let us create a temp table with same data as above and check the results.

[sql]
USE TESTCOLLATION
GO

CREATE TABLE #TestTable ( NAME VARCHAR(100))
GO

INSERT INTO #TestTable
SELECT ‘TestName’

GO

SELECT * FROM #TestTable

SELECT * FROM #TestTable where NAME = ‘TestName’

SELECT * FROM #TestTable where NAME = ‘testname’
[/sql]

Output:
[text]

(1 row(s) affected)
NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

[/text]

It can be found that the third select statement above gives ‘TestName’ as output.

So when we go with an assumption that the temporary tables have the same collation as the database in which it is created then the code would fail.

Resolution:
[sql]
SELECT * FROM #TestTable WHERE NAME collate SQL_Latin1_General_CP1_CS_AS = ‘TestName’

SELECT * FROM #TestTable WHERE NAME collate SQL_Latin1_General_CP1_CS_AS = ‘testname’
[/sql]

Output:
[text]
NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-

(0 row(s) affected)

[/text]

Cleanup:
[sql]
DROP DATABASE TESTCOLLATION
GO
[/sql]

Interesting Observation – Declaring Table Variables impact on Tempdb

Following was an intresting observation when creating table variables.
1. Table variables are created in tempdb.
2. Table variables are created in tempdb even before the declare table is called.

Steps to find that the Table variables are created in tempdb:
1. Restart your local SQL Server instance. (So that the tempdb gets cleared)
2. Connect to the SQL Server in SQL Server management studio and check if there are any records in sys.objects in tempdb.
[sql]
SELECT * FROM tempdb.sys.tables
[/sql]
You will find there are 0 records(ideally)
3. In a new query window run the below script.
[sql]
USE MASTER
GO

DECLARE @TableVariable TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @TableVariable
select 1,’A’

WAITFOR DELAY ’00:02:00′
[/sql]
4. While the above code is getting executed run the below script in other query window.
[sql]
SELECT * FROM tempdb.sys.tables
–Take objectid from the above resultset
SELECT * FROM tempdb.sys.columns where OBJECT_ID = 117575457
[/sql]

Result: It can be found that the tempdb.sys.tables and tempdb.sys.columns has records for the table variable. This proves that the table variable is created in tempdb.

Steps to find that the Table variables are created in tempdb even before the declare table is called:
1. Restart your local SQL Server instance. (So that the tempdb gets cleared)
2. Connect to the SQL Server in SQL Server management studio and check if there are any records in sys.objects in tempdb.
[sql]
SELECT * FROM tempdb.sys.tables
[/sql]
You will find there are 0 records(ideally)
3.In a new query window run the below script.

[sql]
SELECT * FROM tempdb.sys.tables

DECLARE @TableVariable TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @TableVariable
select 1,’A’
[/sql]

Result: It can be found that there is a record in the tempdb.sys.tables even before the DECLARE Statement is executed. This proves that the table variables are created in tempdb even before the declare table is called.

Wishing all the blog readers advanced happy new year. 🙂

TSQL Script to remove TAB, LINE FEED and CARRIAGE RETURN

The below script removes the TAB(Horozontal Tab), Line feed(New line), Carriage Return Characters in a variable @String

[sql]
SET NOCOUNT ON
DECLARE @String VARCHAR(100)
DECLARE @CorrectedString VARCHAR(100)

SELECT @String = ‘AB C
D’

PRINT @String

SELECT @CorrectedString = REPLACE(@String, CHAR(9),”)
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),”)
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),”)
PRINT @CorrectedString
[/sql]

Extended script which also provides length of the string:
[sql]
SET NOCOUNT ON
DECLARE @String VARCHAR(100)
DECLARE @CorrectedString VARCHAR(100)

SELECT @String = ‘AB C
D’

PRINT @String
PRINT ‘LENGTH=’+CAST(LEN(@String) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@String, CHAR(9),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))
[/sql]

Concatenating Data From Different Rows into Single Column Row

Consider a scenario where a table has multiple rows, and each of these rows belong to a group and the data from all the rows should be concatenated based on the group. This post explains different ways to accomplish this.

Sample data creation:
[sql]

CREATE TABLE GroupMembers(GroupName VARCHAR(100),MemberName VARCHAR(100))
GO

INSERT INTO GroupMembers
SELECT ‘Group1′,’A’
union
SELECT ‘Group1′,’B’
union
SELECT ‘Group1′,’C’
union
SELECT ‘Group1′,’D’
union
SELECT ‘Group1′,’E’
union
SELECT ‘Group1′,’F’
union
SELECT ‘Group1′,’G’
union
SELECT ‘Group1′,’H’
union
SELECT ‘Group2′,’A1’
union
SELECT ‘Group2′,’B1’
union
SELECT ‘Group2′,’C1’
union
SELECT ‘Group2′,’D1’
union
SELECT ‘Group2′,’E1’
union
SELECT ‘Group2′,’F1’
union
SELECT ‘Group2′,’G1’
union
SELECT ‘Group2′,’H1’
GO
SELECT GroupName,MemberName FROM GroupMembers
GO
[/sql]

The above created table has data that belongs to two groups Group1 and Group2.

Below TSQL Queries concatenate this data into single column/row for each group.
[sql]
–Sol1:
–Using XML
SELECT GroupName,
(SELECT MemberName
FROM GroupMembers WHERE GroupName=A.GroupName
FOR XML PATH(”))
FROM GroupMembers A
GROUP BY GroupName
GO

–Sol2:
–With out seperation between member names
–Using XML, Without XML Attributes
SELECT GroupName,
(SELECT MemberName AS [text()]
FROM GroupMembers WHERE GroupName=A.GroupName
FOR XML PATH(”))
FROM GroupMembers A
GROUP BY GroupName

–Sol3:
–With comma seperation between member names
–Using XML, Without XML Attributes
SELECT GroupName,
(SELECT MemberName+ ‘,’ AS [text()]
FROM GroupMembers WHERE GroupName=A.GroupName
FOR XML PATH(”))
FROM GroupMembers A
GROUP BY GroupName

–Sol4:
–With comma seperation between member names(no ending comma)
–Using XML, Without XML Attributes
SELECT G.GroupName,Left(G.Members,Len(G.Members)-1)
FROM (
SELECT GroupName,
(SELECT MemberName+ ‘,’ AS [text()]
FROM GroupMembers WHERE GroupName=A.GroupName
FOR XML PATH(”)) AS Members
FROM GroupMembers A
GROUP BY GroupName
) G
[/sql]

TSQL Script to fetch the code that is executed by an SPID

Below is the TSQL Script to fetch the code that is executed by an SPID

[sql]
DECLARE @sql_handle AS VARBINARY (1000)

SELECT @sql_handle = SQL_HANDLE
FROM sys.sysprocesses WITH (NOLOCK)
WHERE spid = 56; –To Do: Update with SPID OF the Process

SELECT *
FROM sys.dm_exec_sql_text (@sql_handle)
[/sql]

How to call or execute a Stored Procedure from inside a Select Statement

How to call or execute a Stored Procedure from Select Statement in SQL Server?

It is really simple to call a stored procedure from a select statement Using OPENROWSET.

Below TSQL Query calls the procedure sp_who from the select statement.

[sql]
SELECT * FROM
OPENROWSET(‘SQLNCLI’
,’Server=(local);Trusted_Connection=Yes;Database=Master’
,’EXEC dbo.sp_Who’)
[/sql]

Syntax:
[sql]
SELECT * FROM
OPENROWSET(‘SQLNCLI’
,’Server=(local);Trusted_Connection=Yes;Database=Master’
,’EXEC [procedurename]’)
[/sql]

Additional Information:
The data that is coming from the procedure can also be filtered in the where clause.

[sql]
SELECT * FROM
OPENROWSET(‘SQLNCLI’
,’Server=(local);Trusted_Connection=Yes;Database=Master’
,’EXEC dbo.sp_Who’)
where dbname = ‘master’
[/sql]

The data from the execution of a select statement can be directly pushed into a table.

[sql]
SELECT * INTO who2_table FROM
OPENROWSET(‘SQLNCLI’
,’Server=(local);Trusted_Connection=Yes;Database=Master’
,’EXEC dbo.sp_Who’)
where dbname = ‘master’
[/sql]

If the openrowset is disabled in the system, learn how to enable it using this link enable openrowset

Related to SQL Server 2005,SQL Server 2008,SQL Server 2012

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

When you try to convert varchar datatype to datetime you would get this error when the value in varchar datatype does not represent the correct range of date.

Example Queries:
[sql]
SELECT CAST( ‘2012-04-31’ AS DATETIME)
[/sql]

Result:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Other Sample queries:
[sql]
SELECT CAST( ‘2012-04-30 24:00:00.000’ AS DATETIME)
[/sql]

[sql]
SELECT CAST( ‘2012-04-30 00:60:00.000’ AS DATETIME)
[/sql]

[sql]
SELECT CAST( ‘2012-04-30 00:00:60.000’ AS DATETIME)
[/sql]

[sql]
SELECT CAST( ‘2012-04-30 24:00:00.000’ AS DATETIME)
[/sql]

Fix:
– Check if the values in the varchar data type are in the range of datetime datatype. Use this link to check the same: Range of datetime data type
– Check if the values for month, day, hours, minutes, seconds, milliseconds are valid.

Applicable to: SQL Server 2005, SQL Server 2008,SQL Server 2008 r2,SQL Server 2012, ASP, C#