SQL-Compass: SQL Server

Showing posts with label SQL Server. Show all posts

SQL Server: Trigger to track or Log DML queries executed on a specific table

There is a master table in my production server. This table is getting updated from various users from various sources. As this table is very huge and the changes/updates/inserts are in bulk, I cannot maintain old data and updated data track. So I needed a trigger to track DML queries executed on the table by particular user in particular date.

(1) First create a table with the schema. You can customise this table as per your needs.

CREATE TABLE [test]
(
    [ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [DeviceName] [varchar](100) NULL,
    [UserName] [varchar](100) NULL,
    [objectName] [varchar](100) NULL,
    [EventType] [varchar](400) NULL,
    [EventTime] [datetime] NULL,
    [CommandText] [varchar](max) NULL,
)

(2) Create the below Trigger on the table which you want to track query logs.

CREATE TRIGGER [dbo].[TriItemsDMLlog]
ON [dbo].[MasterTable]
AFTER UPDATE,DELETE,INSERT
AS
BEGIN
SET NOCOUNT ON

DECLARE @TABLENAME VARCHAR(20)
SET @TABLENAME='MasterTable'

DECLARE @inputbuffer TABLE
(EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000))

INSERT INTO @inputbuffer EXEC('DBCC inputbuffer('+@@Spid+') with no_infomsgs')

DECLARE @INS INT
DECLARE @DEL INT

SELECT @INS=COUNT(*) FROM INSERTED
SELECT @DEL=COUNT(*) FROM DELETED

INSERT INTO MasterTableDMLQueryLog
(
UserName,
DeviceName,
objectname,
EventType,
EventTime,
CommandText
) SELECT SUSER_NAME(),HOST_NAME(),@TABLENAME,
CASE WHEN ISNULL(@INS,0)>0 AND ISNULL(@DEL,0)>0 THEN 'UPDATE'
when ISNULL(@INS,0)>0 AND ISNULL(@DEL,0)=0 THEN 'INSERT'
ELSE 'DELETE'END,GETDATE(),EventInfo FROM @inputbuffer;

SET NOCOUNT OFF

END

SQL Server: How to create a linked server

I was trying to import data from a table of production SQL server to my local SQL server with below query. I wanted to deploy this in one of my SP. I encountered with an error.

SELECT * INTO LocalTable FROM [192.168.0.111].ProdDatabase.dbo.ProdTestTable

Msg 7202, Level 11, State 2, Line 3
Could not find server '192.168.0.111' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Solution:
As we are trying to get the data from another server, an authentication is required. A link table has to be created in local SQL Server to access data from another server.

Enter remote Server IP.

Click on Security available at left pane. Enter your local and remote SQL Server credentials as in below screenshot.
Note: You can provide your local system name when your SQL Server login is Windows authentication.

If you are calling stored procedure in remote server from local SQL server or vice-versa, set RPC and RPCOut options to True whcih are available under Server Options.

MS Access: Execute SSIS dtsx package from Access vba

I needed to execute dtsx package from Access VBA. After trying few attempts I found that this cannot be done directly. Instead call a bat file which again calls dtsx package or we can say SSIS package. Below is the script to run bat file through Access VBA.

RunFile "D:\test\callpackage1.bat", vbNormalFocus

To execute above script, below function has to be added as well in VBA.

Function RunFile(strFile As String, strWndStyle As String)
On Error GoTo Error_Handler

   Shell "cmd /k """ & strFile & """", strWndStyle

Error_Handler_Exit:
   On Error Resume Next
   Exit Function

Error_Handler:
   MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
   Err.Number & vbCrLf & "Error Source: RunFile" & vbCrLf & "Error Description: " & _
   Err.Description, vbCritical, "An Error has Occured!"
   Resume Error_Handler_Exit
End Function

Suppossing your package name is package1.dtsx, below script has to be mentioned inside bat file.

-@ECHO OFF
"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /F "D:\test\package1.dtsx"
echo msgbox "FILE UPLOADED SUCESSFULLY!"

SQL Server: Pass Multiple values as parameter dynamically

Here is an example to show how multiple values can be passed as parameter which can be used in stored procedure.

CREATE PROC [dbo].[DynamicSQL_PassMultiVal](@EmpName varchar(500))
AS
BEGIN

DECLARE @SQLStatement varchar(max)
SET @EmpName = Replace(@EmpName,',',''',''')
SET @SQLStatement='select * from Employee where EmployeeName in (' + QuoteName(@EmpName) + ')'

EXEC (@SQLStatement)

END

Without using QUOTENAME() we can write the query as

ALTER PROC [dbo].[DynamicSQL_PassMultiVal](@EmpName varchar(500))
AS
BEGIN

DECLARE @SQLStatement varchar(max)
SET @EmpName = Replace(@EmpName,',',''',''')
SET @SQLStatement='select * from Employee where EmployeeName in (' + '''' + @EmpName + ''')'

EXEC (@SQLStatement)

END

Usage

[DynamicSQL_PassMultiVal] 'Bheem,Krishna'

SQL Server: Pass single value parameter dynamically

This is just an example to pass a single parameter dynamically.

CREATE PROC [dbo].[DynamicSQL_PassSingleVal](@EmpName varchar(500))
AS
BEGIN

DECLARE @SQLStatement varchar(max)
SET @SQLStatement ='select * from Employee where EmployeeName = ' + '''' + @EmpName + ''''

EXEC (@SQLStatement)

END

Usage

[DynamicSQL_PassSingleVal] 'Bheem'

Click here to see how to Pass Multiple values as parameter dynamically

SQL Server: Open Recordset in SQL Server from MS Access

We can open tables from MA Access, Excel and other MS Office applications. Here we shall see how records can be retrieved from MS Access
Supposing we have test.mdb file on your desktop(C:\Users\mine\Desktop\test.mdb) and has a table by name Table1 containing 4 records in it.

SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Users\mine\Desktop\test.mdb"')...Table1;

You may obtain this error if you are running first time.

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

You need to execute below two queries and execute above query. It works without any issue.

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

If you need to open recordset from access 2007 and above versions you have to use this query

SELECT * FROM OPENDATASOURCE(
'Microsoft.ACE.OLEDB.12.0',
'Data Source="C:\Users\mine\Desktop\test.accdb"')...Table1;

SQL Server: Find Unicode/Non-ASCII characters in a column

I have a table having a column by name Description with NVARCHAR datatype. It may contain Unicode characters. I needed to find in which row it exists. I used this query which returns the row containing Unicode characters.

SELECT * FROM Mytable WHERE [Description] <> CAST([Description] as VARCHAR(1000))

This query works as well

SELECT * FROM [ITEM]
WHERE [DESC] LIKE N'%[^ -~]%' collate Latin1_General_BIN

MS Access

To find Unicode characters in MS Access, I could not found a better way. so the best way is to import the access data into SQL Server and follow above method.

SQL Server: SELECT syntax

SELECT clause specifies the columns to be returned by the SELECT statement.
SELECT statement returns data from a database.

Now confusion arises on the difference between SELECT clause and SELECT statement. To know the difference between SQL Clause and Statement click on the above link.

SQL Server: Difference between SQL Clause and Statement

This might be a silly discussions on my blog. But some learners like me who are not familiar with English language may get confused between the two. Thus I have posted this article.
As the name implies, SQL Statement is made up of one or more SQL Clauses. To say with an example

SELECT Items FROM inventory WHERE ItemName = 'glasscups' HAVING COUNT(*)>1

The whole content inside the above block is a SQL Statement. Where as SELECT, FROM, WHERE, HAVING are SQL Clauses.

SQL Server: Insert/Update/Delete in VIEW table

When I was conducting interviews for SQL candidates having 1-2 years of experience, most of them had misunderstanding about SQL VIEW tables and answered me as "VIEW table is created from BASE tables and thus VIEW is a virtual table where we cant do any edits on VIEW tables". When I say we can do UPDATE/INSERT/DELETE operations on VIEW tables, they wonder and not ready to accept it. This is incorrect.

"we can perform DML operations like UPDATE/INSERT/DELETE on VIEW tables."

Note

UPDATE, DELETE, INSERTS cannot be performed when the VIEW involves constraints in it

SQL Server: Transpose rows to columns without PIVOT

In this section we will see how to transpose rows to columns without PIVOT concept. Rather than explanation I will show with an example.
Execute below queries to create a sample table

CREATE TABLE package
(
itemnumber INT,
qty INT,
uom VARCHAR(10)
)

INSERT INTO package (itemnumber,qty,uom) VALUES (1111,1,'EA')
INSERT INTO package (itemnumber,qty,uom) VALUES (1111,10,'BX')
INSERT INTO package (itemnumber,qty,uom) VALUES (1111,100,'CA')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,1,'EA')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,15,'BX')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,30,'CA')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,60,'CARTON')
INSERT INTO package (itemnumber,qty,uom) VALUES (3333,1,'EA')
INSERT INTO package (itemnumber,qty,uom) VALUES (3333,12,'DZ')

SELECT * FROM package

itemnumber	qty	uom
1111	1	EA
1111	10	BX
1111	100	CA
2222	1	EA
2222	15	BX
2222	30	CA
2222	60	CARTON
3333	1	EA
3333	12	DZ

The below query will transpose rows to columns.

SELECT itemnumber,
MAX(CASE WHEN serial = 1 THEN qty END) qty1,
MAX(CASE WHEN serial = 1 THEN uom END) uom1,
MAX(CASE WHEN serial = 2 THEN qty END) qty2,
MAX(CASE WHEN serial = 2 THEN uom END) uom2,
MAX(CASE WHEN serial = 3 THEN qty END) qty3,
MAX(CASE WHEN serial = 3 THEN uom END) uom3,
MAX(CASE WHEN serial = 4 THEN qty END) qty4,
MAX(CASE WHEN serial = 4 THEN uom END) uom4,
MAX(CASE WHEN serial = 5 THEN qty END) qty5,
MAX(CASE WHEN serial = 5 THEN uom END) uom5
FROM
(
SELECT itemnumber,qty,uom,
ROW_NUMBER() OVER(PARTITION BY itemnumber
ORDER BY itemnumber,qty) serial
FROM package
) d
GROUP BY itemnumber

itemnumber	qty1	uom1	qty2	uom2	qty3	uom3	qty4	uom4	qty5	uom5
1111	1	EA	10	BX	100	CA	NULL	NULL	NULL	NULL
2222	1	EA	15	BX	30	CA	60	CARTON	NULL	NULL
3333	1	EA	12	DZ	NULL	NULL	NULL	NULL	NULL	NULL

SQL Server: Import data from excel to SQL Server is BAD IDEA!?!

YES IT IS... According to me, it is not a good idea when you have alphanumeric characters in any of the columns.

Several times I have imported data from excel sheets to SQL Server. No errors popped. But when data is thoroughly checked there will be data loss if you have alphanumeric data in any of the columns.

The following behaviors of the Jet provider with the Excel driver can lead to unexpected results when reading data from an Excel data source.

Data sources: The source of data in an Excel workbook can be a worksheet, to which the $ sign must be appended (for example, Sheet1$), or a named range (for example, MyRange). In a SQL statement, the name of a worksheet must be delimited (for example, [Sheet1$]) to avoid a syntax error caused by the $ sign. The Query Builder automatically adds these delimiters. When you specify a worksheet or range, the driver reads the contiguous block of cells starting with the first non-empty cell in the upper-left corner of the worksheet or range. Therefore you cannot have empty rows in the source data, or an empty row between title or header rows and the data rows.
Missing values: The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window. For more information, see PRB: Excel Values Returned as NULL Using DAO OpenRecordset.
Truncated text: When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. For more information, see PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error.

The above details obtained from Microsoft Library. Follow the below link
http://msdn.microsoft.com/en-us/library/ms141683.aspx

Difference between Stored Procedures and Functions

Stored Procedure (SP)	Function (User Defined Function - UDF)
SPs are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called	Functions are compiled and executed every time when it is called
SP can return zero, single or multiple values	Function should return atleast one value
We can use Transactions in SP	Cannot use transactions in UDF
SPs can use SELECT, UPDATE, INSERT, DELETE, DROP statements	Functions are computed values and only SELECT statement is valid
SPs cannot be utilised in SELECT statements	Functions can be embedded in SELECT statement
SP can have input and output parameter	UDFs can have only input parameters
SPs can can execute with or without parameters	UDFs needs at least one input parameter. Without parameter UDFs can be created but it would be a insignificant one
We call function from SP	We cannot call SP from an UDF
Exception handlers like TRY-CATCH blocks can be used in SP	Cannot use Exception handlers can be used in UDF

SQL Server: Trim all columns of a table at a time

I had a situation of trimming all columns of a table. My table had 80 columns. So I had to specify each column in the query to trim which was very hectic and irritating.

UPDATE mytable SET col1 = LTRIM(RTRIM(col1)), col2 = LTRIM(RTRIM(col1))... till col80

I found below script as the solution to avoid this.

USE MyDatabase

DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'mytable'

SELECT @SQL = COALESCE(@SQL + ',[', '[') +
COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL
EXEC (@SQL)

When you need to pass table name whcih has to perform LTRIM and RTRIM for all columns just create a stored procedure

CREATE PROCEDURE TrimAllColumnsOfTable @TableName Varchar(100)
AS
BEGIN

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName
AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL

EXEC (@SQL)

END

If you have different schema names apart from default dbo schema like [abc].[TableName] and [xyz.pqr].TableName etc. then you must use below SP

CREATE PROCEDURE [dbo].[TrimAllColumnsOfTable] @SchemaName Varchar(100),@TableName Varchar(100)
AS
BEGIN

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName
AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @SchemaName + '].[' + @TableName + '] SET ' + @SQL

EXEC (@SQL)

END

We can use this script to trim all char,nchar, varchar and nvarchar columns of all tables across all databases in a server

SELECT 'UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ' + 'SET [' + COLUMN_NAME + '] = LTRIM(RTRIM([' + COLUMN_NAME + '])) ' + 'WHERE [' + COLUMN_NAME + '] <> LTRIM(RTRIM([' + COLUMN_NAME + ']))' + CHAR(13) + CHAR(10) + 'GO' FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('varchar', 'nvarchar') ORDER BY TABLE_NAME, COLUMN_NAME

SQL Server: Difference between <> and != operator

Two Different Not equal to operators - Difference
Once I was using <> operator in a query.

select * from Mytable where col1 is not null and col1 <> ''

Where as one of my colleagues used != operator.

select * from Mytable where col1 is not null and col1 != ''

Both the queries resulted the same. I just googled to know the answer and I got this

http://msdn.microsoft.com/en-us/library/ms188074.aspx

There is no difference between both the operators technically or performance wise.
!= operator is not an ANSI standard. So better to use <> operator.

SQL Server: Remove special characters from a string

I needed a function to remove special characters from a column. So I created below function.

CREATE FUNCTION [dbo].[fnRemoveSpecialchars]
(
    @String varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
    DECLARE @Clearstring varchar(255)
    Set @Clearstring = LTrim(RTrim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
    (Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
    (Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
    (Replace(Replace(Replace(@String,'~',''),'`',''),'?','')
    ,'>',''),'<',''),',',''),':',''),';',''),']',''),'[',''),'}',''),'{',''),'|',''),'+','')
    ,'=',''),'_',''),')',''),'(',''),'&',''),'^',''),'%',''),'$',''),'@',''),'!',''),Char(39),'')
    ,'#',''),'*',''),'"',''),'-',''),'.',''),'\',''),'/',''),' ','')))
    RETURN @Clearstring
END

Usage: SELECT 'String_with$special&chars',[dbo].[fnRemoveSpecialchars]('String_with$special&chars')

Another way to remove special characters and parse only alphanumeric characters is

CREATE FUNCTION [dbo].[UDF_ParseAlphaChars]
    (
        @string VARCHAR(8000)
    )
RETURNS VARCHAR(8000)
            AS
    BEGIN
    DECLARE    @IncorrectCharLoc SMALLINT
        SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
    WHILE @IncorrectCharLoc > 0
        BEGIN
        SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
        SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
        END
    SET @string = @string
    RETURN @string
    END

SQL Server: Parse alphanumeric characters

I needed a function to remove all special characters with existing spaces to be removed in any given string. Here is the function to accomplish it.

Check table lock status

To check which table has been locked in SQL Server the script that can be used is

SELECT L.request_session_id AS SPID,
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName,
        P.object_id AS LockedObjectId,
        L.resource_type AS LockedResource,
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

SQL Server: Find Nth Highest Salary value

Execute below scripts to create Employee table

CREATE TABLE Employee (ID INT,emp_name VARCHAR(50),Salary INT)

INSERT INTO Employee VALUES(1,'Dolu',15000)
INSERT INTO Employee VALUES(2,'Bolu',15000)
INSERT INTO Employee VALUES(3,'Kalia',10000)
INSERT INTO Employee VALUES(4,'Bheem',50000)
INSERT INTO Employee VALUES(5,'Krishna',40000)
INSERT INTO Employee VALUES(6,'Chutki',30000)

SELECT * FROM Employee

ID	emp_name	salary
1	Dolu	15000
2	Bolu	15000
3	Kalia	10000
4	Bheem	50000
5	Krishna	40000
6	Chutki	30000

Execute any one of the queries to obtain high salaried employee

SELECT TOP 1 * FROM Employee WHERE salary IN (SELECT TOP 1 salary FROM Employee ORDER BY salary DESC)(or)
SELECT TOP 1 * FROM (SELECT TOP 1 * FROM Employee ORDER BY salary DESC )AS B ORDER BY B.salary

To obtain 2nd highest salaried employee we can to execute

SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Employee ORDER BY salary DESC )AS B ORDER BY B.salary

To obtain 3rd highest salaried employee we can execute

SELECT TOP 1 * FROM (SELECT TOP 3 * FROM Employee ORDER BY salary DESC )AS B ORDER BY B.salary

And to obtain nth highest salaried employee just exucute

SELECT TOP 1 * FROM (SELECT TOP n * FROM Employee ORDER BY salary DESC )AS B ORDER BY B.salary

To obtain multiple employees who has same highest salaries then execute

SELECT Top 1 WITH TIES emp_name, salary from Employee order by salary desc

Note

Do not use the query SELECT TOP 1 MAX(salary),emp_name FROM Employee GROUP BY emp_name
This will end up with wrong results.

Nth Lowest Salary value

To obtain Lowest salaried employee we can to execute

SELECT TOP 1 * FROM (SELECT TOP 1 * FROM Employee ORDER BY salary ASC)AS B ORDER BY B.salary DESC

To obtain 2nd lowest salaried employee

SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Employee ORDER BY salary ASC)AS B ORDER BY B.salary DESC

SQL Server: Remove Duplicate records using CTE

Execute below scripts to create a new table by name Sampletable.

CREATE TABLE Sampletable(Emp_Name VARCHAR(50),Emp_ShortName VARCHAR(3),Emp_City VARCHAR(50))

Insert few records

INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Shivakumar', 'SKR','Bangalore')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('KumarKrishnan', 'KKN','Chennai')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Sunil', 'SNL','Shimoga')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Job John', 'JJN','Trivendrum')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Lokesh', 'LKS','Bangalore')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Vinod Raj', 'VRJ','Belgam')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Shivakumar', 'SKR','Bangalore')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Vinod Raj', 'VRJ','Belgam')

Select to check the records of your Sampletable

select * from Sampletable

Your results will be as shown as below.

Execute the below CTE script to check what exactly the query does.

With CTE
As
(
SELECT Emp_Name, Emp_ShortName, Emp_City,ROW_NUMBER() OVER(PARTITION BY Emp_Name, Emp_ShortName, Emp_City ORDER BY Emp_Name) AS DuplicateCount
FROM Sampletable
)
SELECT * FROM CTE WHERE DuplicateCount>1

Results will be like

Once it is confirmed those are the duplicated records, go ahead and run below script to delete duplicated records.