截断和删除之间的区别 [英] Difference between truncate and delete

问题描述

我刚刚阅读了一篇关于截断和删除之间差异的文章。但我不清楚地理解下面的答案。请用更好的例子解释一下

差异介于这两个进程之间 相当简单。 截断表示 只是清空表 。  另一方面， delete 命令可让您 delete 整行来自表中的，但 not not  所有   表<中的数据。

我最近向朋友提出这个问题。我的朋友在说



truncate无法回滚。



删除可以滚动bak ..



我们如果删除一些记录就可以回滚...





现在这是对的吗？如果在删除时可以回滚那么怎么办？我们需要使用trnasaction吗？

解决方案

10-difference-between-truncate-delete-statement-in-sql-server / - [ ^ ]



查看此链接..希望它会有所帮助...

尝试以下脚本来了解其中的区别;）

  CREATE   TABLE  #employees（ID  INT   IDENTITY （ 1 ， 1 ），FirstName  VARCHAR （ 30 ））
 
 < span class =code-comment>  -    insert示例数据 
  INSERT   INTO  #employees（FirstName）
 < span class =code-keyword> SELECT  '  Maciej' 
 < span class =code-keyword> UNION   ALL   SELECT  '  Sergey' 
  UNION   ALL   SELECT  '  Anurag ' 
  UNION   ALL   SELECT  '  Raja' 
  UNION   ALL   SELECT  '  Richard' 
  UNION   ALL   SELECT  '  Bernard' 
 
   -   显示初始值 
  SELECT  * 
  FROM  #employees 
 
   -  删除初始值 
  DELETE  
  FROM  #employees 
   -   添加新值;它们在此示例中相同 
  INSERT   INTO  #employees（FirstName）
  SELECT  '  Maciej' 
  UNION   ALL   SELECT  '  Sergey' 
  UNION   ALL   SELECT  '  Anurag' 
  UNION   ALL   SELECT  '  Raja' 
  UNION  所有  SELECT  '  Richard' 
  UNION   ALL   SELECT  '  Bernard' 
 
   -   显示新值;看看ID字段！ 
  SELECT  * 
  FROM  #employees 
 
  TRUNCATE   TABLE  #employees 
 
   -   添加新值;它们在此示例中相同 
  INSERT   INTO  #employees（FirstName）
  SELECT  '  Maciej' 
  UNION   ALL   SELECT  '  Sergey' 
  UNION   ALL   SELECT  '  Anurag' 
  UNION   ALL   SELECT  '  Raja' 
  UNION  所有  SELECT  '  Richard' 
  UNION   ALL   SELECT  '  Bernard' 
 
   -   显示新值;看看ID字段和你看到的内容？ 
  SELECT  * 
  FROM  #employees 
   -   最后，删除表格 
  DROP   TABLE  #employees 

第二版OP问题完全解决了这个问题！



是的， truncate 命令不允许恢复以前的数据（除非你有数据库的副本）并且是 - delete 命令允许只要我们使用交易 [ ^ ]。请点击链接。

[/ EDIT]

Maciel已经以非常好的方式解释了它。

但是如果你还需要进一步澄清这个..

请访问。



http://codebetter.com/raymondlewallen/2005/05/09/the-difference-in- truncate-and-delete-in-sql-server / [ ^ ]



，简而言之，我会说.. < br $>


DELETE

1. DELETE是DML命令。

2.使用行锁执行DELETE语句，表格中的每一行都被锁定以便删除。

3.我们可以在where子句中指定过滤器

4.如果条件存在，它会删除指定的数据。

5.删除激活触发器b因为操作是单独记录的。

6.比截断慢，因为它保留了日志。

7.可以回滚。



TRUNCATE

1. TRUNCATE是一个DDL命令。

2. TRUNCATE TABLE总是锁定表和页面但不是每一行。

3.不能使用Where条件。

4.它删除所有数据。

5. TRUNCATE TABLE无法激活触发因为操作不记录个别行删除。

6.性能方面更快，因为它不保留任何日志。

7.无法回滚。



和你的第二个查询 -

引用：

DELETE和TRUNCATE都可以在与TRANSACTION一起使用时回滚。



如果完成了Transaction，意味着COMMITED，那么我们就不能回滚TRUNCATE命令了，但是我们可以仍然从LOG文件回滚DELETE命令，因为DELETE写入记录em在日志文件中以防将来需要从LOG文件回滚。

Quote：

I was just going through a article regarding the difference between truncate and delete. But i am not understanding the below answer clearly. Kindly explain it with better example

The difference between these two processes is fairly simple. Truncate means to simply empty out a table. On the other hand, the delete command lets you delete entire rows from within a table, but not all of the data within that table.

I recently asked this question to my friend. My friend is saying

truncate cannot be rolled back.

Delete can be roll bak..

how we can rollback if we delete some record...


Now is this correct? If rollback is possible in delete then how ? do we need to use trnasaction?

解决方案

10-difference-between-truncate-delete-statement-in-sql-server/-[^]

Check this link..hope it will help...

Try below script to understand the difference ;)

CREATE TABLE #employees(ID INT IDENTITY(1,1), FirstName VARCHAR(30))

--insert example data
INSERT INTO #employees (FirstName)
SELECT 'Maciej'
UNION ALL SELECT 'Sergey'
UNION ALL SELECT 'Anurag'
UNION ALL SELECT 'Raja'
UNION ALL SELECT 'Richard'
UNION ALL SELECT 'Bernard'

--display initial values
SELECT *
FROM #employees

--delete initial values
DELETE 
FROM #employees
--add new values; they are same in this example
INSERT INTO #employees (FirstName)
SELECT 'Maciej'
UNION ALL SELECT 'Sergey'
UNION ALL SELECT 'Anurag'
UNION ALL SELECT 'Raja'
UNION ALL SELECT 'Richard'
UNION ALL SELECT 'Bernard'

--display new values; have a look at ID field!
SELECT *
FROM #employees

TRUNCATE TABLE #employees

--add new values; they are same in this example
INSERT INTO #employees (FirstName)
SELECT 'Maciej'
UNION ALL SELECT 'Sergey'
UNION ALL SELECT 'Anurag'
UNION ALL SELECT 'Raja'
UNION ALL SELECT 'Richard'
UNION ALL SELECT 'Bernard'

--display new values; have a look at ID field and what you see?
SELECT *
FROM #employees
--finally, remove table
DROP TABLE #employees

[EDIT]
Second edition of OP question totally chaged the question!

Yes, truncate command does not allow restore previous data (unless you have a copy of database) and yes - delete command allows to reject changes as long as we use transaction[^]. Please, follow the link.
[/EDIT]

Well Maciel has already explained it in a very nice way.
but if you still need further clarification on this ..
Do visit.

http://codebetter.com/raymondlewallen/2005/05/09/the-difference-in-truncate-and-delete-in-sql-server/[^]

and in brief I would say..

DELETE
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.

TRUNCATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.

and about your second query -

Quote:

DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.

If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.

Quote:

这篇关于截断和删除之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！