SQL Server SSMS stuck on query while truncate the table

 I was going to truncate a table in sqlserver database but it seems to be went in deadlock. Apart from this if issue persist regularly then use delete command instead of truncate command.

List of Deadlock
SELECT
    SESSION_ID
FROM SYS.DM_EXEC_REQUESTS
WHERE BLOCKING_SESSION_ID != 0

Kill the deadlock:
kill SESSION_ID

Procedure | Compare Field Count and Distinct Count From a Database

 This procedure is for SQL Server, please take a idea and feel free to modify and use this procedure as per your need. This Procedure giving 3 fields in Output, 1st field giving column Name, 2nd field giving total count of records in table, and 3rd field having distinct records of a field.

CREATE TABLE #tempCount (
	TableName NVARCHAR(500)
	, AllTableCount NVARCHAR(500)
	, ColumnDistinctCount NVARCHAR(500)
	)

ALTER PROCEDURE countOfAField
AS
DECLARE @table_name NVARCHAR(50)
	, @column_name NVARCHAR(50)
	, @SQL_v NVARCHAR(500)

DECLARE cur_col CURSOR
FOR
SELECT TABLE_NAME
	, column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'XXXXX'

OPEN cur_col

FETCH NEXT
FROM cur_col
INTO @table_name
	, @column_name

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL_v = 'Insert into #tempCount 
					Select 
						''' + @table_name + ''' as TableName
						, count(*) as AllTableCount 
						,count(distinct ' + @column_name + ') AS ColumnDistinctCount 
					from  ' + @table_name

	PRINT @SQL_v

	EXEC sp_executesql @SQL_v

	FETCH NEXT
	FROM cur_col
	INTO @table_name
		, @column_name
END

SELECT *
FROM #tempCount

CLOSE cur_col

DEALLOCATE cur_col

EXEC countOfAField

SQL 17 Delete Duplicate from a table having single column

In requirement we have table having single column, no primary key and foreign key applied on table. It is easier to delete the duplicate data from table if it having primary key applied using CTE or row_number. But if in table no primary key, delete using row_number window function will work cause it will delete all the records from table.

Alternate is 

- we can create new temp table using select distinct Into 
- delete all records from old table
- reinsert distinct record from temp table

But in this case new table create and delete object not allowed to user.

SELECT *
INTO SQL17
FROM (
	SELECT 1 id
	
	UNION ALL
	
	SELECT 2
	
	UNION ALL
	
	SELECT 1
	
	UNION ALL
	
	SELECT 2
	
	UNION ALL
	
	SELECT 3
	
	UNION ALL
	
	SELECT 3
	
	UNION ALL
	
	SELECT 3
	
	UNION ALL
	
	SELECT 4
	) t

DELETE
FROM SQL17
WHERE %%physloc%% IN (
		SELECT loc
		FROM (
			SELECT %%physloc%% loc
				, *
				, row_number() OVER (
					PARTITION BY id ORDER BY id
					) rn
			FROM SQL17
			) t
		WHERE rn > 1
		)

SQL 16 Populate total number of employee working in Department

Here we have Simple basic table structure same as  of Emp dept tables available in Oracle .

Here we need to Find the total number of employees working in Department. I am adding various alternate ways of doing this task. 

Table Employee Data Structure

Id	Name	DeptId
1	A	10
2	B	10
3	C	10
4	D	10
5	E	20
6	F	20
7	G	20
8	H	30
9	I	30

Table Department Data Structure

DeptId	Name
10	Sales
20	Dev
30	Tester

Expected Output

Id	Name	DeptId	Count
1	A	10	4
2	B	10	4
3	C	10	4
4	D	10	4
5	E	20	3
6	F	20	3
7	G	20	3
8	H	30	2
9	I	30	2

Create table Query 

SELECT *
INTO SQL16
FROM (
	SELECT 1 id
		, 'A' name
		, 10 deptid
	
	UNION ALL
	
	SELECT 2
		, 'B'
		, 10
	
	UNION ALL
	
	SELECT 3
		, 'C'
		, 10
	
	UNION ALL
	
	SELECT 4
		, 'D'
		, 10
	
	UNION ALL
	
	SELECT 5
		, 'E'
		, 20
	
	UNION ALL
	
	SELECT 6
		, 'F'
		, 20
	
	UNION ALL
	
	SELECT 7
		, 'G'
		, 20
	
	UNION ALL
	
	SELECT 8
		, 'H'
		, 30
	
	UNION ALL
	
	SELECT 9
		, 'I'
		, 30
	) t

Alternate Solution 1

SELECT *
INTO SQL16_1
FROM (
	SELECT 10 id
		, 'Sale' Dname
	
	UNION ALL
	
	SELECT 20
		, 'Dev'
	
	UNION ALL
	
	SELECT 30
		, 'QA'
	) t1

Alternate Solution 2
SELECT ID
	, NAME
	, DEPTID
	, COUNT(*) OVER (
		PARTITION BY DEPTID ORDER BY DEPTID
		) AS CUMMULATIVE_SUM
FROM SQL16;

Alternate Solution 3
SELECT ID
	, NAME
	, DEPTID
	, (
		SELECT SUM(1) CUMMULATIVE_SUM
		FROM SQL16 CI
		WHERE CI.DEPTID = CO.DEPTID
		GROUP BY DEPTID
		) AS CUMMULATIVE_SUM
FROM SQL16 CO;

Alternate Solution 4
SELECT CO.ID
	, CO.NAME
	, CO.DEPTID
	, (
		CASE 
			WHEN CI.DEPTID = CO.DEPTID
				THEN SUM(1) OVER (
						PARTITION BY CO.DEPTID ORDER BY CO.DEPTID
						)
			END
		) AS CUMMULATIVE_SUM
FROM SQL16 CO
JOIN SQL16 CI ON CO.ID = CI.ID;

Alternate Solution 5
SELECT *
FROM SQL16 p14
LEFT JOIN (
	SELECT count(*) cnt
		, deptid
	FROM SQL16
	GROUP BY deptid
	) t1 ON t1.deptid = p14.deptid

SQL15 Commulative SUM of Sales

Data is given table, we have date along with the sales. We need to calculate commulative sum for all the sales

below are the various ways to perform this task

Data in Table 

Year	Sales
2019-09-01	10
2019-10-01	60
2019-11-01	20
2019-12-01	10
2020-01-01	30
2020-02-01	20
2020-03-01	80
2021-01-01	70
2021-02-01	30
2021-03-01	10

Expected Result

year	sales	Sum_sales
2019-09-01	10	10
2019-10-01	60	70
2019-11-01	20	90
2019-12-01	10	100
2020-01-01	30	130
2020-02-01	20	150
2020-03-01	80	230
2021-01-01	70	300
2021-02-01	30	330
2021-03-01	10	340

SELECT *
INTO SQL15
FROM (
	SELECT '2019-09-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2019-10-01' AS MONTH
		, 60 AS SALES
	
	UNION ALL
	
	SELECT '2019-11-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2019-12-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2020-01-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2020-02-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2020-03-01' AS MONTH
		, 80 AS SALES
	
	UNION ALL
	
	SELECT '2021-01-01' AS MONTH
		, 70 AS SALES
	
	UNION ALL
	
	SELECT '2021-02-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2021-03-01' AS MONTH
		, 10 AS SALES
	) t

SELECT t1.month
	, year(t1.month)
	, t1.sales
	, sum(t2.sales)
FROM sql15 t1
JOIN sql15 t2 ON (t1.month) >= (t2.month)
GROUP BY t1.month
	, t1.sales
	, year(t1.month)
ORDER BY 1
	, 2

Releated post - SQL 14 (Click Here)

Tags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice

SQL14 Commulative SUM of Sales Year wise

Data is given table, we have date along with the sales. We need to calculate commulative sum for every year given in table.

below are the various ways to perform this task

Data in Table 

Year	Sales
2019-09-01	10
2019-10-01	60
2019-11-01	20
2019-12-01	10
2020-01-01	30
2020-02-01	20
2020-03-01	80
2021-01-01	70
2021-02-01	30
2021-03-01	10

Expected Result

year	sales	Sum
2019-09-01	10	10
2019-10-01	60	70
2019-11-01	20	90
2019-12-01	10	100
2020-01-01	30	30
2020-02-01	20	50
2020-03-01	80	130
2021-01-01	70	70
2021-02-01	30	100
2021-03-01	10	110

Table Insert Query

SELECT *
INTO SQL14
FROM (
	SELECT '2019-09-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2019-10-01' AS MONTH
		, 60 AS SALES
	
	UNION ALL
	
	SELECT '2019-11-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2019-12-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2020-01-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2020-02-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2020-03-01' AS MONTH
		, 80 AS SALES
	
	UNION ALL
	
	SELECT '2021-01-01' AS MONTH
		, 70 AS SALES
	
	UNION ALL
	
	SELECT '2021-02-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2021-03-01' AS MONTH
		, 10 AS SALES
	) t

--oracle
SELECT MONTH
	, SALES
	, (
		SELECT SUM(SALES)
		FROM SQL14 I
		WHERE I.MONTH <= O.MONTH
			AND EXTRACT(YEAR FROM TO_DATE(O.MONTH, 'YYYY-MM-DD')) = EXTRACT(YEAR FROM TO_DATE(I.MONTH, 'YYYY-MM-DD'))
		) AS YEARTODATE_SALE
FROM SQL14 O;

--SQLServer Solution 1
SELECT year(month)
	, sum(sales) OVER (
		PARTITION BY year(month) ORDER BY month
		)
	, *
FROM sql14

--SQLServer Solution 2
SELECT MONTH
	, SALES
	, (
		SELECT SUM(SALES)
		FROM SQL14 I
		WHERE I.MONTH <= O.MONTH
			AND YEAR(O.MONTH) = YEAR(I.MONTH)
		) AS YEARTODATE_SALE
FROM SQL14 O;

--SQLServer Solution 3
WITH cte
AS (
	SELECT *
		, year(month) y
	FROM sql14
	)
SELECT DISTINCT *
FROM SQL14 c
CROSS APPLY (
	SELECT DISTINCT sum(sales) AS total
	FROM SQL14
	WHERE (c.month) >= (month)
		AND year(c.month) = year(month)
		--group by year(month)
	) t

--SQLServer Solution 4
SELECT t1.month
	, year(t1.month)
	, t1.sales
	, sum(t2.sales)
FROM sql14 t1
JOIN sql14 t2 ON (t1.month) >= (t2.month)
	AND year(t1.month) = year(t2.month)
GROUP BY t1.month
	, t1.sales
	, year(t1.month)
ORDER BY 1
	, 2

Tags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice

Interview Question 2 - Count of all Occurrence of status/color until it changes to another

 Reproduce table script

select * from (
select '1' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-04-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-03-01' reporting_month ,'GREEN' STATUS union all
select '1' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-04-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-01-01' reporting_month ,'GREEN' STATUS union all
select '3' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-04-01' reporting_month ,'GREEN' STATUS union all
select '4' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-06-01' reporting_month ,'GREEN' STATUS union all
select '5' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-04-01' reporting_month ,'GREEN' STATUS union all
select '5' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-02-01' reporting_month ,'GREEN' STATUS union all
select '6' id ,'2020-01-01' reporting_month ,'RED' STATUS  ) COMMULATIVE_SUM

Need in output , count of Status for first all occurrence of RED color (Occurrence will be end by GREEN color)

--Interview Question (SQL Query)
"Solution 1 "

select 
id
, min(NewRank)-1  
from
(
select 
id
, reporting_month
, STATUS
, case when STATUS = 'GREEN' then TRank else 99 end NewRank
, TRank
from
(
select 
DENSE_RANK() over (partition by id order by id, reporting_month desc) as TRank
, id
, reporting_month
, STATUS
from
COMMULATIVE_SUM
) d
) d2 
where 
NewRank <>99
group 
by id

"Solution 2 "
select 
id
,count(*)
from
(
select 
id,
sum(case when status = 'Red' then 0 else 1 end)
over(partition by id order by reporting_month desc) as r
from 
COMMULATIVE_SUM
)as t
where 
r=0
group by id

How to Find Duplicate Records in Table | Interview Question

When generally asked for duplicate, every time for most of person start thing about the group by, count clause. Which is correct, but these will not work every where. Count(*), Group by , having are the for beginner level. Student get learnt from basics.

On other hand, when we are working in IT industry then window function are usable in real time examples.

What i think Count(*), group by, having are usable for OLAP database, and window function are usable at OLTP level.

Lets see result with some example:

XYZ
ID	FirstName	LastName	MiddleName
1	Gurjeet	Singh
2	Harry	Potter
3	Gurjeet	Singh
4	Prem	Singh	L
5	Harry	Potter

When i use the query 

SELECT count(*), FirstName, LastName, MiddleName
FROM XYZ
GROUP BY  FirstName, LastName, MiddleName

So result will be 

Count	FirstName	LastName	MiddleName
2	Gurjeet	Singh
2	Harry	Potter
1	Prem	Singh	L

I am agree the query return the correct result, but it is providing the Count and Name of Duplicate person. Means Gurjeet is the person exists by 2 times in table.

But for some cases we also need ID along with the duplicate person name, which is not possible with the help of GROUP by. So here we have to use window function.

select * from (
SELECT 
    ID, 
    FirstName, 
    LastName, 
    MiddleName,
    row_number() over(partition by Firstname, LastName, MiddleName order by ID) rowNumber
FROM XYZ
) t where rowNumber > 1

So how query will work, below table represent how the row_number will be assign to records

XYZ
ID	FirstName	LastName	MiddleName	rn
1	Gurjeet	Singh		1
2	Harry	Potter		1
3	Gurjeet	Singh		2
4	Prem	Singh	L	1
5	Harry	Potter		2

When we use the outer query, and apply the filter "where rowNumber > 1" below result set will be appear. Here main focus is column ID.

ID	FirstName	LastName	MiddleName
3	Gurjeet	Singh
4	Prem	Singh	L
5	Harry	Potter

Now in some cases we also need ID along with the all duplicate person name (only Duplicate), which is possible with the help of using Count(*) using Partition by clause.

select * from (
SELECT 
    ID, 
    FirstName, 
    LastName, 
    MiddleName,
    Count(*) over(partition by Firstname, LastName, MiddleName order by ID) TotalCount
FROM XYZ
) t where rowNumber > 1

XYZ
ID	FirstName	LastName	MiddleName	TotalCount
1	Gurjeet	Singh		2
2	Harry	Potter		2
3	Gurjeet	Singh		2
5	Harry	Potter		2

SQL Server Audit Table Total Count Total Nulls Len of Data

Hello engineers, are you guys working on SQL Server. Are you guys working on TSQL. If yes i have written a TSQL program which right example of

• Execute dynamic SQL
• In Parameters/variables
• Out parameters/variables
• IN/Out in dynamic SQL
• Cursor in TSQL
• Audit of all tables data
• How many records in table
• How many distinct records in each field
• How many not null records
• Maximum length of data in each field

create table all_counts (id int identity , table_name  nvarchar(200), column_name  nvarchar(200), data_type nvarchar(200) , total_count int, distinct_count int , notnull_count  int, data_len int)

Declare 

@table_name varchar(50),

@column_name nvarchar(100),

@data_Type nvarchar(100),

@total_count integer,

@distinct_count integer,

@notnull_count integer,

@data_len integer,

@query_str nvarchar(4000),

@query_str2 nvarchar(4000) ,

@query_str3 nvarchar(4000) ,

@query_str4 nvarchar(4000) ;

declare cur_col Cursor for 

select  

t.TABLE_NAME

,column_name

,data_type 

from 

INFORMATION_SCHEMA.COLUMNS c 

join INFORMATION_SCHEMA.tables t on c.TABLE_NAME = t.TABLE_NAME 

where 

t.TABLE_TYPE ='BASE TABLE' 

open cur_col

fetch next from cur_col into @table_name,@column_name,@data_Type

while @@FETCH_STATUS=0

begin

Set @query_str='Select @total_count= count(@column_name1) from '+ @table_name;

exec sp_executesql @query_str, N'@column_name1 nvarchar(500),@total_count int output' ,@column_name1=@column_name,@total_count=@total_count output

Set @query_str2='Select  @distinct_count= count( distinct ' + @column_name + ') from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str2, N'@distinct_count int output' ,@distinct_count=@distinct_count output

Set @query_str3='Select  @notnull_count= count(  ' + @column_name + ') from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str3, N'@notnull_count int output' ,@notnull_count=@notnull_count output

Set @query_str4='Select  @data_len= len(max(  ' + @column_name + ')) from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str4, N'@data_len int output' ,@data_len=@data_len output

insert into all_counts select @table_name, @column_name, @data_Type, @total_count, @distinct_count, @notnull_count, @data_len

fetch next from cur_col into @table_name,@column_name,@data_Type 

end

close cur_col

deallocate cur_col

I hope this program is helping many engineers in different ways. Rest of usage is depend upon the thinking and capability of an engineer. He/she can modify this program to any extent since this is generic TSQL.

If any faced error while executing above TSQL cause of Table Name or Column Name have special character which was not supported by Dynamic SQL.

Error :

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'tablename'

Solution:

We need to update table_name and column name variables from Cursor as below

['+ @table_name +']';

Foreign Key Reference Tables

To know about the database it is good for one if he/she looking for all references for a object

• whats tables are dependent on MAIN table (Child Objects)
• On which tables MAIN table is dependent (Parent Objects)

Suppose i have 3 tables and in parenthesis show its fields

Dept (dept_id, Dept_name )

Emp (emp_id, Emp_first_name, emp_last_name , dept_id)

Sal(sal_id, year, emp_id)

Here EMP is Main table and Dept is Parent table (for Main table) and Sal is Child table (for Main table).

So one can easily understand the database if he has knowledge of both kind of references

Below is the script to identify the same.

SELECT 

f.name AS ForeignKey,

SCHEMA_NAME(f.SCHEMA_ID) SchemaName,

OBJECT_NAME(f.parent_object_id) AS TableName,

COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM 

sys.foreign_keys AS f

JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

where 

OBJECT_NAME(f.parent_object_id)='MAIN_TABLE'

or OBJECT_NAME (f.referenced_object_id) = 'MAIN_TABLE'

Convert Semicolon Separated String in Rows Along With Respective Data

I have a situation, have a data in table with Primary key and one data field (assume it is a company) and third field contain the information of Person working in it in semicolon/comma separated format.

CREATE TABLE SemicolonSep

(

ID INT,

Company Nvarchar(20) ,

person VARCHAR(100)

)

INSERT SemicolonSep SELECT 1, 'A', 'Person1;Person2;Person3'

INSERT SemicolonSep SELECT 2, 'B', 'Person1'

INSERT SemicolonSep SELECT 3, 'C', ''

INSERT SemicolonSep SELECT 4, 'D', 'Person1;Person2;Person5;Person7'

INSERT SemicolonSep SELECT 5, 'E', 'Person1;Person2;Person5;Person7;Person15;Person17'

select * from SemicolonSep

;WITH temp(id, Company, NewData, person) AS

(

SELECT

id,

Company,

cast(LEFT(person, CHARINDEX(';', person + ';') - 1) as nvarchar),

STUFF(person, 1, CHARINDEX(';', person + ';'), '')

FROM SemicolonSep

UNION all

SELECT

id,

Company,

cast(LEFT(Person, CHARINDEX(';', Person + ';') - 1) as nvarchar),

STUFF(Person, 1, CHARINDEX(';', Person + ';'), '')

FROM temp

where Person > ''

)

SELECT

id,

Company,

NewData

FROM temp

ORDER BY id

Simple enough, You can use this output any where in further logic breakdowns as well as store output in any temp table or permanent table to use this data further.

Keywords:

1. Convert semicolon separated string in rows
2. Convert comma separated string in rows (just replace semicolumn with comma)
3. Demoralize data
4. Column to rows