{"id":10270,"date":"2024-03-14T11:48:59","date_gmt":"2024-03-14T11:48:59","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/"},"modified":"2025-08-03T21:24:21","modified_gmt":"2025-08-03T21:24:21","slug":"how-do-you-remove-duplicate-data-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/","title":{"rendered":"Remove Duplicate Data in SQL Server: Complete Guide"},"content":{"rendered":"<p>In SQL Server, you can use the DISTINCT keyword to remove duplicate data. For example, if you have a table named table1 with a column named column1, and you want to query the unique values of column1, you can use the following SQL query statement:<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">DISTINCT<\/span> column1\r\n<span class=\"hljs-keyword\">FROM<\/span> table1;\r\n<\/code><\/pre>\n<p>This will return unique values of column1. If you want to delete duplicate data in the table, you can use the ROW_NUMBER() function and CTE (Common Table Expression) to achieve this. Here is an example:<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">WITH<\/span> CTE <span class=\"hljs-keyword\">AS<\/span> (\r\n  <span class=\"hljs-keyword\">SELECT<\/span> column1,\r\n         <span class=\"hljs-built_in\">ROW_NUMBER<\/span>() <span class=\"hljs-keyword\">OVER<\/span> (<span class=\"hljs-keyword\">PARTITION<\/span> <span class=\"hljs-keyword\">BY<\/span> column1 <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> (<span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-number\">0<\/span>)) <span class=\"hljs-keyword\">AS<\/span> rn\r\n  <span class=\"hljs-keyword\">FROM<\/span> table1\r\n)\r\n<span class=\"hljs-keyword\">DELETE<\/span> <span class=\"hljs-keyword\">FROM<\/span> CTE\r\n<span class=\"hljs-keyword\">WHERE<\/span> rn <span class=\"hljs-operator\">&gt;<\/span> <span class=\"hljs-number\">1<\/span>;\r\n<\/code><\/pre>\n<p>This query will delete duplicate data in the table, while keeping the first record in each group of duplicates. Please note to make sure you back up the data before executing the deletion operation in case any important data is accidentally deleted.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In SQL Server, you can use the DISTINCT keyword to remove duplicate data. For example, if you have a table named table1 with a column named column1, and you want to query the unique values of column1, you can use the following SQL query statement: SELECT DISTINCT column1 FROM table1; This will return unique values [&hellip;]<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[12742,12741,774,9486,572],"class_list":["post-10270","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-cte-sql-server","tag-distinct-keyword","tag-remove-duplicates","tag-row_number","tag-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Remove Duplicate Data in SQL Server: Complete Guide - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Learn effective methods to remove duplicate data in SQL Server using DISTINCT, ROW_NUMBER(), and CTE. Clean your database with these SQL techniques.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Remove Duplicate Data in SQL Server: Complete Guide\" \/>\n<meta property=\"og:description\" content=\"Learn effective methods to remove duplicate data in SQL Server using DISTINCT, ROW_NUMBER(), and CTE. Clean your database with these SQL techniques.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-14T11:48:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-03T21:24:21+00:00\" \/>\n<meta name=\"author\" content=\"Jackson Davis\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jackson Davis\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/\"},\"author\":{\"name\":\"Jackson Davis\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/55a10b8b0457c35884c25677889ad350\"},\"headline\":\"Remove Duplicate Data in SQL Server: Complete Guide\",\"datePublished\":\"2024-03-14T11:48:59+00:00\",\"dateModified\":\"2025-08-03T21:24:21+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/\"},\"wordCount\":131,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"keywords\":[\"CTE SQL Server\",\"DISTINCT keyword\",\"remove duplicates\",\"ROW_NUMBER\",\"sql server\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/\",\"name\":\"Remove Duplicate Data in SQL Server: Complete Guide - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-14T11:48:59+00:00\",\"dateModified\":\"2025-08-03T21:24:21+00:00\",\"description\":\"Learn effective methods to remove duplicate data in SQL Server using DISTINCT, ROW_NUMBER(), and CTE. Clean your database with these SQL techniques.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Remove Duplicate Data in SQL Server: Complete Guide\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/55a10b8b0457c35884c25677889ad350\",\"name\":\"Jackson Davis\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/2fdb47d6df1226e92380d96973782572a97b0675d098bb914410dec348eb5d29?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/2fdb47d6df1226e92380d96973782572a97b0675d098bb914410dec348eb5d29?s=96&d=mm&r=g\",\"caption\":\"Jackson Davis\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/jacksondavis\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Remove Duplicate Data in SQL Server: Complete Guide - Blog - Silicon Cloud","description":"Learn effective methods to remove duplicate data in SQL Server using DISTINCT, ROW_NUMBER(), and CTE. Clean your database with these SQL techniques.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Remove Duplicate Data in SQL Server: Complete Guide","og_description":"Learn effective methods to remove duplicate data in SQL Server using DISTINCT, ROW_NUMBER(), and CTE. Clean your database with these SQL techniques.","og_url":"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-14T11:48:59+00:00","article_modified_time":"2025-08-03T21:24:21+00:00","author":"Jackson Davis","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Jackson Davis","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/"},"author":{"name":"Jackson Davis","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/55a10b8b0457c35884c25677889ad350"},"headline":"Remove Duplicate Data in SQL Server: Complete Guide","datePublished":"2024-03-14T11:48:59+00:00","dateModified":"2025-08-03T21:24:21+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/"},"wordCount":131,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"keywords":["CTE SQL Server","DISTINCT keyword","remove duplicates","ROW_NUMBER","sql server"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/","url":"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/","name":"Remove Duplicate Data in SQL Server: Complete Guide - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-14T11:48:59+00:00","dateModified":"2025-08-03T21:24:21+00:00","description":"Learn effective methods to remove duplicate data in SQL Server using DISTINCT, ROW_NUMBER(), and CTE. Clean your database with these SQL techniques.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-remove-duplicate-data-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Remove Duplicate Data in SQL Server: Complete Guide"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/55a10b8b0457c35884c25677889ad350","name":"Jackson Davis","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/2fdb47d6df1226e92380d96973782572a97b0675d098bb914410dec348eb5d29?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/2fdb47d6df1226e92380d96973782572a97b0675d098bb914410dec348eb5d29?s=96&d=mm&r=g","caption":"Jackson Davis"},"url":"https:\/\/www.silicloud.com\/blog\/author\/jacksondavis\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/10270","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=10270"}],"version-history":[{"count":2,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/10270\/revisions"}],"predecessor-version":[{"id":154021,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/10270\/revisions\/154021"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=10270"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=10270"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=10270"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}