SQL Server – Delete the duplicate record (data) form table

 It is very easy to delete duplicate record from table in sql server. SQL Server always stores each tupple (Row) as unique into the table.

To see duplicate record, we can use the count function with group by clause with having in the condition.

 To delete the record to Max function with NOT IN keyword.

Just execute and see how it work to delete duplicate record  into table.

USE tempdb

GO

CREATE TABLE Jainendra_TestTable (My_ID INT, Rank_Col VARCHAR(50))

Go

INSERT INTO Jainendra_TestTable (My_ID, Rank_Col)

SELECT 1, 'First'

UNION ALL

SELECT 2, 'No Rank'

UNION ALL

SELECT 3, 'Second'

UNION ALL

SELECT 4, 'Second'

UNION ALL

SELECT 5, 'Second'

UNION ALL

SELECT 6, 'Third'

UNION ALL

SELECT 7, 'Five'

UNION ALL

SELECT 8, 'Second'

UNION ALL

SELECT 9, 'Five'

UNION ALL

SELECT 10, 'Nine'

UNION ALL

SELECT 11, 'Third'

GO

-- See the inserted data in create table

SELECT *

FROM Jainendra_TestTable

GO

-- Now below query is detecting duplicate records into table

SELECT Rank_Col, COUNT(*) TotalCount

FROM Jainendra_TestTable GROUP BY Rank_Col

HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

GO

-- Now below query is deleting the duplicate record into table

DELETE FROM Jainendra_TestTable

WHERE My_ID NOT IN

( SELECT MAX(My_ID) FROM Jainendra_TestTable GROUP BY Rank_Col)

GO

-- Selecting Data

SELECT *

FROM Jainendra_TestTable

GO

DROP TABLE Jainendra_TestTable

GO

If it is useful than please like and share it to other SQL Server learners

How to attached user database MDF and LDF file in SQL Server

How to attached user database .MDF and .LDF file.

USE [master]

GO

CREATE DATABASE [Application_Registr] ON

( FILENAME = N'E:\Database\MSSQL$Sharepoint\Data\Application_Registr.mdf' ),

( FILENAME = N'L:\Database\MSSQL$Sharepoint\Log\Application_Registr_log.LDF' )

FOR ATTACH

GO

User is unable to connect to SQL Server in 2016. What may be scenarios and how to troubleshoot it?

Possible Scenarios and Resolution to login issue with Fixes-

• •  Error: 26: SQL Browser Firewall No connectivity between client and server.- Check SQL Server browser service using Services.msc 
  
  
  •  Error: 28: Instance TCP/IP was disabled- Go to SQL Server configuaration manager and enable the TCP/IP 
  
  
  •  Error: 40: Instance service is not running - Open the services and RUN the MSSQLSERVICE to permanent FIX run type make it as automatically. 
  
  
  •  Error: 18456: Login failed. (invalid login or password ) - Check your user name and pasowrd, Also server which you are trying to connect.
  
  
  •  Expired Timeout: Network issue, Server is busy, In server max sessions are open ,No available session memory - Try to ping the server, if network issue contact to Network team and if Memory issue contact to DBA support team or increase the memory. 
  
  
  •  Connection Forcibly Closed: Update the client computer to the server version of the SQL Server Native Client.
  
  
  •  In single user mode: if any other service is connected with the db Engine, it doesn't allow connections. change the mode as multiuser
  
  

What are the recommendations to design a backup and recovery solution?

I hope the below recommendations will help you to design a backup and recovery solution.

1. Determine what is needed
2. Recovery Model
3. Select Backup Types
4. Backup Schedule
5. Backup Process
6. Document
7. Backup to Disk
8. Archive to Tape
9. Backup to Different Drives
10. Secure Backup Files
11. Encrypt or Password Protect Backup Files
12. Compress Backup Files
13. How Much to Keep on Disk
14. Online Backups
15. Run Restore Verifyonly
16. Offsite Storage
17. Additional information:
18. SQL Server Backup Checklist

Please comment if you have more recommendations to design a backup and recovery solution.  Jainendra Verma

How to identify longest running queries in SQL Server?

There are two ways to identify slow running queries

1)      Profiler (By using duration of the query)

2)      sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_requests DMVs etc

3)      DBCC OPENTRAN

Example 1-

SELECT  creation_time

        ,last_execution_time

        ,total_physical_reads

        ,total_logical_reads

        ,total_logical_writes

        , execution_count

        , total_worker_time

        , total_elapsed_time

        , total_elapsed_time / execution_count avg_elapsed_time

        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

         ((CASE statement_end_offset

          WHEN -1 THEN DATALENGTH(st.text)

          ELSE qs.statement_end_offset END

            - qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY total_elapsed_time / execution_count DESC;

Example 2-

                        select *, sql_handle from sys.dm_exec_requests

                 select  * from sys.dm_exec_sql_text (0x020000004D4F6005A3E8119F3DD3297095832ABE63E312F2)

Example 3

SELECT

    [ds_tst].[session_id],

    [ds_es].[login_name] AS [Login Name],

    DB_NAME (ds_tdt.database_id) AS [Database],

    [ds_tdt].[database_transaction_begin_time] AS [Begin Time],

    [ds_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],

    [ds_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],

    [ds_est].text AS [Last T-SQL Text],

    [ds_eqp].[query_plan] AS [Last Plan]

FROM

    sys.dm_tran_database_transactions [ds_tdt]

JOIN

    sys.dm_tran_session_transactions [ds_tst]

ON

    [ds_tst].[transaction_id] = [ds_tdt].[transaction_id]

JOIN

    sys.[dm_exec_sessions] [ds_es]

ON

    [ds_es].[session_id] = [ds_tst].[session_id]

JOIN

    sys.dm_exec_connections [ds_ec]

ON

    [ds_ec].[session_id] = [ds_tst].[session_id]

LEFT OUTER JOIN

    sys.dm_exec_requests [ds_er]

ON

    [ds_er].[session_id] = [ds_tst].[session_id]

CROSS APPLY

    sys.dm_exec_sql_text ([ds_ec].[most_recent_sql_handle]) AS [ds_est]

OUTER APPLY

    sys.dm_exec_query_plan ([ds_er].[plan_handle]) AS [ds_eqp]

ORDER BY     [Begin Time] ASC;

4)      DBCC OPENTRAN

Use this to clean buffer data for testing purpose

Dbcc Dropcleanbuffers

BEGIN TRAN
UPDATE Person.Address SET AddressLine2 = '' WHERE AddressID = 112

Now open another connection in SQL and execute following script.

DBCC OPENTRAN

Result: - Using SPID can get Query-

Transaction information for database 'AdventureWorks2012'.

Oldest active transaction:

 SPID (server process ID): 57

 UID (user ID) : -1

 Name : user_transaction

 LSN : (163:324:1)

 Start time : Jan 16 2014 4:34:58:813PM

 SID : 0x0105000000000005150000001650720d0cca921474e68766e8030000

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please commnet if you know any other way to get or  identify longest running queries in SQL Server.- Jainendra Verma