Transpose Single delimited rows into multiple rows

 

DECLARE @data NVARCHAR(MAX)  
 select  @data= 'Ahamedabad,Bengaluru,Chennai,Delhi'
 DECLARE @sql_xml XML = Cast('<root><U>'+ Replace(@data, ',', '</U><U>')+ '</U></root>' AS XML)
SELECT f.x.value('.', 'VARCHAR(max)') AS Transpose_Values
--INTO #Temp
FROM @sql_xml.nodes('/root/U') f(x)

Select non-ascii characters more than 255 ascii code in SQL Server

;With cteNumbers as

(

    Select ROW_NUMBER() Over(Order By c1.Unique_ID_Column) as N

    From Your_Table_Name c1

)

Select Distinct Unique_ID_Column,Your_Column

From Your_Table_Name t

Join cteNumbers n ON n <= Len(CAST(Your_Column As NVarchar(MAX)))

Where UNICODE(Substring(Your_Column, n.N, 1)) > 255

OR UNICODE(Substring(Your_Column, n.N, 1)) <> ASCII(Substring(Your_Column, n.N, 1))

 order by 1

Select hidden Spaces which are not supporting by ASCII character in SQL Server.

;WITH cte AS

(

   SELECT 0 AS CharCode

   UNION ALL

   SELECT CharCode + 1 FROM cte WHERE CharCode <31

)

SELECT * FROM

   Your_Table_Name T

     cross join cte

WHERE

   EXISTS (SELECT Unique_ID_Column,Your_Column

        FROM Your_Table_Name Tx

        WHERE Tx.Unique_ID_Column = T.Unique_ID_Column

AND Tx.Your_Column LIKE '%' + CHAR(cte.CharCode) + '%'

and cte.CharCode>0

)

Select records where having minimum costs.

create table #temp
(
id int,
Name varchar(10),
Cost int
)

insert into #temp 
values
(1,'x',5),
(1,'z',4),
(2,'y',6),
(3,'x',2),
(3,'y',5),
(3,'z',8)
select * from #temp

Select tbl.* From #Temp tbl
Inner Join
(
  Select Id,MIN(cost) MinCost From #Temp Group By Id
)tbl1
On tbl1.id=tbl.id
Where tbl1.MinCost=tbl.Cost

Split Rows into Multiples Rows If they have more than One Counts.

I have a Input table with a Name and Counts Columns.
I want to Split my Name with multiple times based on Counts column values.
Please , Check an above Screen Short that i have pasted.

create table #SPLITROW 
(Name varchar(10)
,Counts int 
)
INSERT INTO #SPLITROW
VALUES ('AA',5)
INSERT INTO #SPLITROW
VALUES ('BB',4)
INSERT INTO #SPLITROW
VALUES ('CC',3)
INSERT INTO #SPLITROW
VALUES ('DD',2)
INSERT INTO #SPLITROW
VALUES ('EE',1)

declare @Rc as int
declare @inital as int=1
select @rc=max(Counts) from #SPLITROW
declare @rowTab as table
(Countss int)
while (@inital<=@Rc)
begin
insert into @rowTab values(@inital)
set @inital=@inital+1
end
SELECT Name,Counts
FROM #SPLITROW  sr
JOIN (select Countss as RN from @rowTab) AS  oft
ON oft.RN <= Counts
order by Name

Alternate Option
SELECT Name , Counts
FROM @SPLITROW  sr
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
      FROM sys.columns) AS  oft
ON oft.RN <= Counts

Output Table

How to use CDC (Change Data Capture) in SQL Server

Step 1 -

Check for all Databases that are already Enabled with CDC tracking.

USE master 
GO 
SELECT [name], database_id, is_cdc_enabled  
FROM sys.databases       
GO  

Step 2 -

On above example we can is_cdc_enables all are 0. it means still we are not using.

USE Your_DB
GO 
EXEC sys.sp_cdc_enable_db 
GO 

Database MSO is now 1. Means CDC is enabled for MSO DB.

Step 3 -

Let me check all tables where CDC mode are enabled.

USE MSO 
GO 
SELECT [name], is_tracked_by_cdc  
FROM sys.tables 
GO  

Step 4 -

Enable CDC on MSO Database for Items table

USE MSO 
GO 
EXEC sys.sp_cdc_enable_table 
@source_schema = N'dbo', 
@source_name   = N'items', 
@role_name     = NULL 
GO

Once you will execute above query it will create Two jobs in SQL  Server Agent

Step - 5 

Go to Your Database =>Table=>System Table. Some CDC table you will get.

Step 6 - Update Your table

update Items set ITEMID=451350 where ITEMID=12345

USE MSO 
GO 
SELECT * 
FROM [cdc].[dbo_items_CT]
GO  --this table contains in System Table in Your Particular DB

All changes are recorded into a tracker CDC table.

SQL SERVER – FIX : ERROR : Cannot find template file for new query (C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\ SQLFile.sql)

To fixed this error follow the below steps.
Step 1- Open a New Query (Right click on Database)
Step 2- Save it under the showing above location till SQL Folder.
Step 3- Rename it as SQLFile.SQL
Notes:
There might be a chance to get a permission from administrator. 

In this case save this file it into an another location and paste again on the same path.
Hope : Your error will be fixed.

Track Your Execution Query through Trigger

I am creating a Tracker table where I can store my DML operation execution Query for INSERT, UPDATE, and DELETE operation.

Create table Query_Tracker
(
ID int unique identity(1,1),
Host varchar(15),
[Date] datetime,
Context varchar(max)
)

Creating trigger with name of Query_tracker_Trig

Create trigger [dbo].[Query_tracker_Trig]

ON [dbo].[Items_Copy]
after UPDATE,INSERT,DELETE
AS
BEGIN
DECLARE @sql nvarchar(max)
SET @sql = 'DBCC INPUTBUFFER(' + CAST(@@SPID AS nvarchar(100)) + ')'
CREATE TABLE #SQL
(
    EventType varchar(100),
    Parameters int,
    EventInfo nvarchar(max)
)
INSERT INTO #SQL
EXEC sp_executesql @sql
SELECT @sql = EventInfo FROM #SQL
INSERT INTO Query_Tracker(Host,[Date],Context)VALUES(Host_name(),getDate(),@sql)
DROP TABLE #SQL
END

select * from Query_Tracker

Bulk Insert from multiple text file into a SQL Server Table in Dynamic way:

I have five different text files in my Local Drive(D). Lets try to Insert this all data from text file to below created structure table in SQL Server.

Creating table structure

create table Foo
(
ID varchar(10),
Name varchar(30),
Mobile varchar(10)
)

Creating a Store Procedure to insert all data from different text file to a SQL table

Create procedure [dbo].[Foo_Insert]
as
declare @file varchar(30)
declare @c int=1
declare @n int =5
begin
while (@c<=@n)
begin
EXEC
('
BULK
INSERT Foo
FROM ''D:\SSIS_Work\Foo'+@c+'.txt''
WITH
(
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''\n'')'
)
set @c=@c+1
end
end

Now execute the SP
Exec [Foo_Insert]

Access Deny from one Database to another Database

Step 1 : Use master database and create a new DB with any name
use master
create database p_test
use p_test
Step 2 : Go to Security -> Logins , can see all users that have created earlier.

Step 3 : Will create a new user with a given password
create login Test_Login with password='test', check_policy = off

User created successfully.

Step 4 : If we execute select statement to access another DB still we can.

Step 5 : Follow the below Query(changing ownership).

use p_test

go

sp_changedbowner 'Test_Login'

Step 5 : Use master DB and execute the below query to access deny another DB

use master

go

deny VIEW any DATABASE to Test_Login

use master

go

execute as login ='Test_Login'

go

Step 6 : select count(*) from mso.dbo.Items_Copy

Following error will get from output:

Msg 916, Level 14, State 1, Line 5

The server principal "Test_Login" is not able to access the database "MSO" under the current security context.

Notes : After creating the user permission you can access only three databases.
select * from sys.databases

Change created DB owner to distinct owner

use p_test 

GO

sp_changedbowner 'sa'

Drop Login and DB

drop login Test_Login
drop database p_test

Select what are the new values has been updated in a table on a particular column :

Step 1 : create table
create table Auto_track(ID int unique identity(1,1),
Name varchar(50) default 'Reza',Value int)
Step 2 : Inserting Records with Default values
insert into Auto_track values(default,null)
go 10
Select * from Auto_track

Notes : Must be a primary key in your table
alter table Auto_track add primary key(ID)

Step 3 : Enable database tracking mode for a specific periods

ALTER DATABASE [YOUR_DB]
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  
Step 4 :Enable Table tracking mode
ALTER TABLE [dbo].[Auto_track]  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON)  
Step 5 :Update value column with some values
update Auto_track set Value=ID+100
Select * from Auto_track

Step 6 :Execute the below code will show the modified records
SELECT ISNUll(pn.Value,0) as Value 
from changetable(changes Auto_track, 1) as ct
INNER JOIN Auto_track pn on pn.ID = CT.ID
WHERE SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'

Dynamically Transposing values into multiple columns if it contains with Delimiter :

I have a table named is #temp with following values

create table #Temp(ID int,barcode_type varchar(50))
insert into #Temp values(12345,'AAAA,BBBB,CCCC,DDDD,EEEE,FFFF,GGGG')
insert into #Temp values(12346,'GGGG,FFFF,EEEE,DDDD,CCCC,BBBB,AAAA')
insert into #Temp values(12347,'MMMM,NNNN,OOOO,PPPP,QQQQ,RRRR,SSSS')
select * from #Temp
Step1: will create multiple columns after counting the maximum words from #temp 
table (Barcode_Type column) into a separate table named #len_of_words.
Step2: Execute the following code and let see the results what will come.

Select ID,barcode_type,len(barcode_type) - len(replace(barcode_type, ',', '')) + 1 No_of_Count

into #len_of_words from #Temp

declare @c as int=1

declare @i as int

select @i=max(No_of_Count) from #len_of_words

while (@c<=@i)

begin

EXEC ('ALTER TABLE #len_of_words ADD barcode_type'+@c+' VARCHAR(100);')

exec('update t1 set barcode_type'+@c+'= NewXML.value(''/Product[1]/Attribute['+@c+']'',''varchar(50)'')

FROM #len_of_words t1

CROSS APPLY (SELECT XMLEncoded=(SELECT barcode_type AS [*] FROM #Temp t2 WHERE t1.ID = t2.ID FOR XML PATH(''''))) EncodeXML

CROSS APPLY (SELECT NewXML=CAST(''<Product><Attribute>''+REPLACE(XMLEncoded,'','',''</Attribute><Attribute>'')+''</Attribute></Product>'' AS XML)) CastXML')

set @c=@c+1

end

select * from #len_of_words

Permutation and Combination in SQL Server :

declare @String varchar(10) = 'TIGER'
;with s(t,n)
as 
(
select substring(@String,1,1),1
union all
select substring(@String,n+1,1),n+1
from s where n<len(@String)
)
,j(t) 
as 
(
select cast(t as varchar(10)) from s
union all
select cast(j.t+s.t as varchar(10))
from j,s where patindex('%'+s.t+'%',j.t)=0
)
select t from j where len(t)=len(@String)

Check database Recovery model status :

Execute the below query , we can see type of recovery model consisting by the database.
SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel 
FROM master..sysdatabases ORDER BY name

Update Third column if First Column has Duplicate values and Second column are Distinct with Comma delimiter:

create table #temp(ID int, ID2 Varchar(10),Results varchar(50))

insert into #temp values(1,'2',null)

insert into #temp values(1,'3',null)

insert into #temp values(1,'4',null)

insert into #temp values(2,'2',null)

insert into #temp values(2,'3',null)

insert into #temp values(2,'4',null)

insert into #temp values(2,'4',null)

insert into #temp values(3,'1',null)

insert into #temp values(4,'5',null)

insert into #temp values(5,'6',null)

insert into #temp values(5,'6',null)

select * from #temp

Execute below code:

;with cte

as

(

 select distinct t.ID,t.ID2,Results,

  STUFF((SELECT distinct ', ' + t1.ID2

         from #temp t1

         where t.[id] = t1.[id]

            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)')

        ,1,2,'') department

from #temp t

)

Update t set t.Results=c.department from #temp t

inner join cte c

on t.ID=c.ID

Some Tricky Ideas:

Swap values from one column to another with some Criteria's :
Most of interviewers will ask this question.Please check below example
declare @a varchar(10)
declare @b varchar(10)
set @a='Male'
set @b='Female'
select @a, case when @a='Male' then 'Female' end
select @b, case when @b='Female' then 'Male' end
Output:
@a if Male then print Female or
@b if Female then print Male

Update column A with column B values if A is Male and B is Female.

create table #tmp (A varchar(20),B varchar(20))

insert into #tmp values('Male','Female')

insert into #tmp values('Male','AA')

insert into #tmp values('Female','VV')

insert into #tmp values('Male','Female')

update #tmp set A=B , B=A 

where A='Male' and B='Female'

Trigger for text or Ntext or image data type in SQL Server:

--Main table--
CREATE TABLE [dbo].[Employee_Test]
(
[Emp_ID] [int] IDENTITY(1,1) NOT NULL,
[Emp_name] [text] NULL,
[Emp_Sal] [int] NULL,
[Address1] [varchar](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--Trigger table--
CREATE TABLE [dbo].[Emp_trigg]
(
[Name] [varchar](max) NULL,
[Old_name] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--Trigger--

ALTER TRIGGER [dbo].[FieldUpdated]

ON [dbo].[Employee_Test]

INSTEAD OF UPDATE

AS

  UPDATE [Employee_Test] SET Emp_name = (SELECT Emp_name FROM Inserted)                     

  WHERE Emp_ID = (SELECT Emp_ID FROM Inserted)                                     

   IF (UPDATE (Emp_name))

  BEGIN

    DECLARE @oldValue nvarchar(max)

    DECLARE @newValue nvarchar(max)

    SET @newValue = (SELECT CONVERT(nvarchar(max), Emp_name) FROM Inserted)

    SET @oldValue = (SELECT CONVERT(nvarchar(max), Emp_name) FROM Deleted)

    IF (@oldValue != @newValue)

    BEGIN

    insert into Emp_trigg(Name,Old_name) values(@newValue,@oldValue)

    END

  END

Select mid string between two string :

DECLARE @c varchar(100)
SET     @c = '0116522656517989898'
SELECT SUBSTRING(STUFF(@c, 1, CHARINDEX('01',@c), ''), 0, CHARINDEX('17', STUFF(@c, 1, CHARINDEX('01',@c), '')))
Output:
1165226565

Transfer table into different Schema :

Select table name with Schema
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM   sys.tables
WHERE  name = 'orders'
GO

Create new Schema with some name:
create schema new_schema
Now transfer table into new schema:
alter schema new_schema
transfer dbo.Orders
go
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM   sys.tables
WHERE  name = 'orders'
GO