{"id":95023,"date":"2022-11-07T15:46:31","date_gmt":"2022-11-07T15:46:31","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95023"},"modified":"2022-11-08T21:06:29","modified_gmt":"2022-11-08T21:06:29","slug":"the-basics-of-deleting-data-from-a-sql-server-table","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/the-basics-of-deleting-data-from-a-sql-server-table\/","title":{"rendered":"The Basics of Deleting Data from a SQL Server Table"},"content":{"rendered":"<p style=\"margin: 0in; font-family: Calibri; font-size: 11.0pt;\"><p><strong>This article is part of Greg Larsen's continuing series on Learning T-SQL. To see all the items in the series, <a href=\"https:\/\/www.red-gate.com\/simple-talk\/t-sql-coding-basics\/\">click here<\/a>.<\/strong><\/p>\n<\/p>\n<p>Over time data in SQL Server tables needs to be modified. There are two major different aspects of modifying data: updating and deleting. In my last article \u201c<a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/the-basics-of-updating-data-in-a-sql-server-table\/\">Updating SQL Server Data<\/a>\u201d I discussed using the <code>UPDATE<\/code> statement to change data in existing rows of a SQL Server table. In this article I will be demonstrating how to use the <code>DELETE<\/code> statement to remove rows from a SQL Server Table.<\/p>\n<p><strong>Syntax of the Basic DELETE statement<\/strong><\/p>\n<p>Deleting data seems like a simple concept so you would think the syntax for a <code>DELETE<\/code> statement would be super simple. In some respects that is true, but there are many ways and aspects of how the DELETE statement can be used. In this article I will be discussing only the basic DELETE statement.<\/p>\n<p>The syntax for basic <code>DELETE<\/code> statement can be found in Figure 1:<\/p>\n<table>\n<tbody>\n<tr>\n<td>\n<p><em>DELETE <\/em><\/p>\n<p><em> [ TOP (&lt;expression&gt;) [ PERCENT ] ] <\/em><\/p>\n<p><em> [ FROM ] &lt;object&gt; <\/em><\/p>\n<p><em> [ WHERE &lt;search_condition&gt;]<\/em><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"caption\">Figure 1: Basic syntax of the <code>DELETE<\/code> statement<\/p>\n<p>Where:<\/p>\n<ul>\n<li><code>expression<\/code> &#8211; Identifies the number or percentage of rows to be delete. When only the <code>TOP<\/code> keyword is identified the expression identifies the number of rows to be deleted. It the keyword <code>PERCENT<\/code> is also included then the expression identifies the percentage of rows to be delete.<\/li>\n<li><code>object<\/code> &#8211; Identifies the table or view from which rows will be deleted.<\/li>\n<li><code>search_condition<\/code> \u2013 Identifies the criteria for which a row must meet in order to be deleted. The <code>search_condition<\/code> is optional. When it is excluded from a <code>DELETE<\/code> statement, then all the rows in the object will be deleted.<\/li>\n<\/ul>\n<p>For the complete syntax of the <code>DELETE<\/code> statement refer to the Microsoft Documentation which can be found <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/delete-transact-sql?view=sql-server-ver16\">here<\/a>.<\/p>\n<p>To better understand the syntax of the simple <code>DELETE<\/code> statement and how to use it, several examples are provided below. But before these examples can be run a couple sample tables need to be created.<\/p>\n<p><strong>Sample Data<\/strong><\/p>\n<p>Listing 1 contains a script to create two sample tables in the <code>tempdb<\/code> database.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\r\nGO\r\n\r\nCREATE TABLE dbo.LoginTracking (\r\nID INT IDENTITY(1,1),\r\nLoginName varchar(100),\r\nLoginDataTime datetime,\r\nLogoffDateTime datetime);\r\nGO\r\n\r\nINSERT INTO dbo.LoginTracking VALUES\r\n('Scott','2022-07-11 08:41:31','2022-07-11 11:45:50'),\r\n('Sally','2022-07-11 08:55:27','2022-07-11 11:59:59'),\r\n('Dick','2022-07-11 09:05:17','2022-07-11 16:15:37'),\r\n('Dick','2022-07-12 08:05:11','2022-07-12 15:50:31'),\r\n('Scott','2022-07-12 08:12:27','2022-07-12 16:11:22'),\r\n('Sally','2022-07-12 09:20:06','2022-07-12 16:45:11'),\r\n('Dick','2022-07-13 08:10:13','2022-07-13 15:59:45'),\r\n('Scott','2022-07-13 08:12:37','2022-07-13 16:21:38'),\r\n('Sally','2022-07-13 09:07:05','2022-07-13 16:55:17');\r\nGO\r\n\r\nCREATE TABLE dbo.LoginsToRemove(\r\nLoginName varchar(100));\r\nGO\r\n\r\nINSERT INTO dbo.LoginsToRemove VALUES ('Sally');\r\nGO<\/pre>\n<p class=\"caption\">Listing 1: Script to create sample tables.<\/p>\n<p>The first table created in Listing 1 is the <code>dbo.LoginTracking<\/code> table. This table is the table from which rows will be deleted. The second table created is <code>dbo.LoginsToRemoved<\/code><em>. <\/em>This table is a staging table that contains a single column named <code>LoginName<\/code>. This table will be used to show how to delete rows using rows in another table.<\/p>\n<p>If you would like to follow along and run the code in this article you can use Listing 1 to create the two sample tables in the <code>tempdb<\/code> database on your instance of SQL Server.<\/p>\n<h2>Deleting a Single Row<\/h2>\n<p>To delete a single row, the <code>DELETE<\/code> command needs to identify the specific row that needs to be deleted. This is done by including a <code>WHERE<\/code> constraint. The <code>WHERE<\/code> constraint needs to uniquely identify the specific row to delete. The code in Listing 2 will delete the <code>LoginTracking<\/code> record that have a <code>LoginName<\/code> \u201cScott\u201d and an <em>ID <\/em>value of 1.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempDB;\r\nGO\r\n\r\nDELETE FROM dbo.LoginTracking\r\nWHERE LoginName = 'Scott' and ID = 1;\r\nGO<\/pre>\n<p class=\"caption\">Listing 2: Deleting a single row<\/p>\n<p>In the <code>LoginTracking<\/code> table there are multiple rows that have a <code>LoginName<\/code> of \u201cScott\u201d. Therefore the <code>ID<\/code> column value of \u201c<code>1<\/code>\u201d was also included in the <code>search_condition<\/code> to uniquely identify which single row was to be deleted.<\/p>\n<p>Only the <em>ID <\/em>column alone could have been used to identify the single record to be deleted. However, in many cases having extra filter conditions can be useful. For example, if the row where <code>ID = 1<\/code> has a different <code>LoginName<\/code>, it would not be deleted.<\/p>\n<p><strong>Note<\/strong>: When you are expecting a certain number of rows to be deleted, it is a good idea to check the value in <code>@@ROWCOUNT<\/code> to make sure.<\/p>\n<h2>Deleting Multiple Rows<\/h2>\n<p>A single <code>DELETE<\/code> statement can also be used to delete multiple rows. To delete multiple rows the <code>WHERE<\/code> clause needs to identify each row in the table or view that needs to be deleted. The code in Listing 3 identifies two <code>LoginTracking<\/code> rows to delete by identifying the <code>ID<\/code> column values for the rows to be deleted.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempDB;\r\nGO\r\n\r\nDELETE FROM dbo.LoginTracking\r\nWHERE ID = 2 or ID=3;\r\nGO<\/pre>\n<p class=\"caption\">Listing 3: Deleting multiple rows with a single <code>DELETE<\/code> statement<\/p>\n<p>By specify a <code>WHERE<\/code> clause, that identifies multiple rows the code in Listing 2 will delete multiple rows in the <code>LoginTracking<\/code> table when this code is executed.<\/p>\n<h2>Using the <code>TOP<\/code> Clause to Delete Rows of Data<\/h2>\n<p>When the <code>TOP<\/code> clause is included with a <code>DELETE<\/code> statement, it identifies the number of random rows that will be deleted from the table or view being referenced. There are two different formats for using the <code>TOP<\/code> clause. <code>(1)<\/code> identifies the number of rows to be delete.<\/p>\n<p>The code in Listing 4 shows how to use the <code>TOP<\/code> clause to delete one random row from the <code>LoginTracking<\/code> table.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\r\nGO\r\n\r\nDELETE TOP(1) FROM dbo.LoginTracking; \r\nGO <\/pre>\n<p class=\"caption\">Listing 4: Deleting one row using the <code>TOP<\/code> clause<\/p>\n<p>The reason the top clause deletes random row is because SQL Server does not guarantee the order rows will be returned, without an <code>ORDER<\/code> clause.<\/p>\n<p>If you need to delete rows in a specific order, it is best to use a subquery that uses TOP and an order by, as shown in Listing 5.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\r\nGO\r\n\r\nDELETE TOP (2) FROM dbo.LoginTracking\r\nWHERE ID IN \r\n   (SELECT TOP(2) ID FROM dbo.LoginTracking ORDER BY ID DESC);\r\nGO<\/pre>\n<p class=\"caption\">Listing 5: Deleting the last 2 rows based in <code>ID<\/code><\/p>\n<p>Executing the code in Listing 5 deletes the last two rows in the <code>LoginTracking<\/code> table, based on the descending order of the <em>ID <\/em>column. The code accomplished this by using a subquery in the <code>WHERE<\/code> constraint. The subquery uses the <code>TOP<\/code> clause to identify the two <code>ID<\/code> values that will be deleted based on descending sort order. This list of <code>ID<\/code> values is then used as the <code>WHERE<\/code> filter condition to identify the two rows to be delete.<\/p>\n<p><strong>Note<\/strong>: in this case, the <code>TOP (2)<\/code> in the <code>DELETE<\/code> is technically redundant. But if the <code>ID<\/code> column didn\u2019t contain unique values, and the <code>TOP<\/code> clause would need to be included to make sure only two rows would have been deleted,.<\/p>\n<p>The <code>TOP<\/code> clause in the prior two examples identified a specific number of rows to be deleted. The <code>TOP<\/code> clause also supports identifying a percentage of rows to delete. Before showing an example of deleting a percentage of rows using the <code>TOP<\/code> clause let\u2019s first review the details of the rows still in the <code>LoginTracking<\/code> table, by running the code in the Listing 6.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\r\nGO\r\n\r\nSELECT * FROM dbo.LoginTracking;<\/pre>\n<p class=\"caption\">Listing 6: Displaying the rows currently in the <code>dbo.LoginTracking<\/code> table<\/p>\n<p>When the code in Listing 6 is run the results in Report 1 are displayed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"521\" height=\"144\" class=\"wp-image-95024\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/text-description-automatically-generated.png\" alt=\"Text\n\nDescription automatically generated\" \/><\/p>\n<p class=\"caption\">Report 1: Current rows in the <code>LoginTracking<\/code> table.<\/p>\n<p>Currently there are 5 rows in the <code>LoginTracking<\/code> table.<\/p>\n<p>To delete a percentage of the rows in a table the <code>TOP<\/code> clause is use along with the <code>PERCENT<\/code> keyword. The expression provided provide with the <code>TOP<\/code> clause identifies the percentage of rows to delete. The code in Listing 7 specifies 41 percent of the rows in the <code>LoginTracking<\/code> table should be deleted.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\r\nGO\r\nDELETE TOP(41) PERCENT FROM dbo.LoginTracking;\r\nGO\r\nSELECT * FROM dbo.LoginTracking;\r\nGO<\/pre>\n<p class=\"caption\">Listing 7: Deleting 41 percent of the rows using the <code>TOP<\/code> clause.<\/p>\n<p>When Listing 4 is executed the output in Report 2 is displayed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"517\" height=\"74\" class=\"wp-image-95025\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/11\/graphical-user-interface-text-description-automa-2.png\" alt=\"Graphical user interface, text\n\nDescription automatically generated\" \/><\/p>\n<p class=\"caption\">Report 2: Number left in <code>LoginTracking<\/code> after the code in Listing 7 was run<\/p>\n<p>By reviewing Report 2 and comparing it with Report 1 results, you can see 3 rows were deleted, which means 60 percent of the rows were deleted. Why 60 percent? The reason 60 percent of the rows were deleted is because SQL Server needs to delete a percentage of rows that equates to a whole number. Since 41 percent of 5 rows would have been 2.05 of the rows in the <code>LoginTracking<\/code> tracking table, SQL server rounded the row count up to 3. 3 equates to 60 percent of the rows when it performed the <code>DELETE<\/code> statement.<\/p>\n<h2>Using a Table to Identify the Row to Delete<\/h2>\n<p>There are times when you might need to identify rows to delete based on a table. To show how to delete rows based on rows in a table, the <code>LoginsToRemove<\/code> table was created in Listing 1. The <code>LoginsToRemove<\/code> table could be considered a staging table, which identifies the logins that need to be deleted. The code in Listing 8 uses this table to delete rows from the <code>LoginTracking<\/code> table.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\r\nGO\r\n\r\nDELETE FROM dbo.LoginTracking\r\nFROM dbo.LoginTracking JOIN dbo.LoginsToRemove\r\nON LoginTracking.LoginName = LoginsToRemove.LoginName\r\nGO<\/pre>\n<p class=\"caption\">Listing 8: Using a table to identify rows to be deleted from a table<\/p>\n<p>The code in Listing 8 joined the <code>LoginsToRemove<\/code> table with the <code>LoginTracking<\/code> table on the <code>LoginName<\/code> column from each table. This join operation only finds those rows in the <code>LoginTracking<\/code> table which have a matching <code>LoginName<\/code> in the <code>LoginsToRemove<\/code> table. In this example all the records that had \u201c<code>Sally<\/code>\u201d as a <code>LoginName<\/code> got deleted from the <code>LoginTracking<\/code> table.<\/p>\n<h2>Deleting all Rows from a Table<\/h2>\n<p>The <code>DELETE<\/code> statement can be used to remove all the rows in a table. To accomplish this a <code>DELETE<\/code> Statement without a <code>WHERE<\/code> constraint can be executed, as in Listing 9.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\r\nGO\r\n\r\nDELETE FROM dbo.LoginTracking;\r\nGO<\/pre>\n<p class=\"caption\">Listing 9: Deleting all the rows in a table.<\/p>\n<p>Even though the <code>DELETE<\/code> statement can delete all the rows in a table, as done in Listing 9, this is not the most efficient way to delete all the rows in a table. The <code>DELETE<\/code> statement performs a row-by-row operation to delete all the rows. Every time a row is deleted information needs to be written to the transaction log file, based on the database recovery model option. If there are a lot of rows in a table, it could take a long time and use a lot of resources to delete all the rows using a <code>DELETE<\/code> operation.<\/p>\n<h3>An alternative method to remove all rows in a table<\/h3>\n<p>A more efficient, though more limited method to delete all rows is to use the <code>TRUNCATE TABLE<\/code> statement as shown in Listing 10.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempDB;\r\nGO\r\n\r\nTRUNCATE TABLE dbo.LoginTracking;\r\nGO<\/pre>\n<p class=\"caption\">Listing 10: Deleting all the rows in a table using a <code>TRUNCATE TABLE<\/code> statement<\/p>\n<p>When a <code>TRUNCATE TABLE<\/code> statement is used, less information is logged to the transaction log file, as well as has some other significate differences. One of those differences is when a <code>DELETE<\/code> statement is used, if all the rows are deleted from a physical <code>DATA<\/code> page the page is left empty in the database, which wastes valuable disk space. Additionally, a <code>TRUNCATE TABLE<\/code> option requires less locks to remove all the rows.<\/p>\n<p>Another important difference is how these two different delete methods affect the identity column\u2019s seed value. When a <code>TRUNCATE TABLE<\/code> statement is executed the seed value for the identity column is set back to the seed value of the table. Whereas the <code>DELETE<\/code> statement retains the last identity column value inserted. This means when a new row is inserted, after all the rows were deleted, using a <code>DELETE<\/code> statement, that the next identify value will not start at the seed value for the table. Instead, the new row\u2019s identity value will be the determined based off the last identity value inserted, and the increment value for the identity column. These differences need to be kept in mind when deleting rows using the <code>DELETE<\/code> statement during testing cycles.<\/p>\n<p>There are two major limitations to consider. First, the table being truncated cannot be referenced in a <code>FOREIGN KEY<\/code> constraint. The second is security. To be able to execute a <code>DELETE<\/code> statement that references a table, you need <code>DELETE<\/code> permissions. To execute <code>TRUNCATE TABLE<\/code> you need <code>ALTER TABLE<\/code> rights, which gives the user the ability to change the table\u2019s structure. For more details on <code>TRUNCATE TABLE<\/code>, check the Microsoft documentation <a href=\"https:\/\/learn.microsoft.com\/sql\/t-sql\/statements\/truncate-table-transact-sql\">here<\/a>.<\/p>\n<h2>Deleting data using a view<\/h2>\n<p>Deleting from a view is the same as deleting from a table, with one caveat. The delete can only affect one table. This essentially means that the <code>FROM<\/code> clause of the view can only reference one table to be the target of a <code>DELETE<\/code> statement. You could have conditions (like subqueries) that reference other objects, but no joins. For example, in listing 11, consider the two view objects.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">CREATE VIEW dbo.v_LoginTracking\r\nAS\r\nSELECT ID,\r\n       LoginName,\r\n       LoginDataTime,\r\n       LogoffDateTime\r\nFROM   dbo.LoginTracking;\r\nGO\r\n\r\nCREATE VIEW dbo.v_LoginTracking2\r\nAS\r\nSELECT LoginTracking.ID,\r\n       LoginTracking.LoginName,\r\n       LoginTracking.LoginDataTime,\r\n       LoginTracking.LogoffDateTime\r\nFROM dbo.LoginTracking JOIN dbo.LoginsToRemove\r\nON LoginTracking.LoginName = LoginsToRemove.LoginName\r\nGO<\/pre>\n<p class=\"caption\">Listing 11: View objects to demonstrate how deleting from a view works<\/p>\n<p><code>Executing<\/code> a DELETE statement in the first view: <code>dbo.v_LoginTracking<\/code>, will work. But attempt to delete rows using the second view: <code>dbo.v_LoginTracking<\/code>, and you will get the following error.<\/p>\n<p><code>View or function 'dbo.v_LoginTracking2' is not updatable because the modification affects multiple base tables.<\/code><\/p>\n<h2>Deleting Rows from a SQL Server table<\/h2>\n<p>The <code>DELETE<\/code> statement is used to delete rows in a SQL Server table or view. By using the <code>DELETE<\/code> statement, you can delete one or more rows from an object. If all the rows in a table need to be deleted, a <code>DELETE<\/code> statement is less efficient then using the <code>TRUNCATE TABLE<\/code> statement, but has fewer limitations and doesn\u2019t reset the identity value information.<\/p>\n<p>Knowing how to use the basic <code>DELETE<\/code> statement, and when not to use it is important concept for every TSQL programmer to understand.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Over time data in SQL Server tables needs to be modified. There are two major different aspects of modifying data: updating and deleting. In my last article \u201cUpdating SQL Server Data\u201d I discussed using the UPDATE statement to change data in existing rows of a SQL Server table. In this article I will be demonstrating&#8230;&hellip;<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143525,143531],"tags":[],"coauthors":[11330],"class_list":["post-95023","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95023","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=95023"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95023\/revisions"}],"predecessor-version":[{"id":95034,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95023\/revisions\/95034"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=95023"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=95023"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=95023"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=95023"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}