SQL中TRUNCATE和DELETE的区别是什么?
如果你的答案是针对特定平台的,请注明。
SQL中TRUNCATE和DELETE的区别是什么?
如果你的答案是针对特定平台的,请注明。
当前回答
对于SQL Server或MySQL,如果有一个带有自动递增的PK, truncate将重置计数器。
其他回答
Truncate and Delete in SQL are two commands which is used to remove or delete data from table. Though quite basic in nature both Sql commands can create lot of trouble until you are familiar with details before using it. An Incorrect choice of command can result is either very slow process or can even blew up log segment, if too much data needs to be removed and log segment is not enough. That's why it's critical to know when to use truncate and delete command in SQL but before using these you should be aware of the Differences between Truncate and Delete, and based upon them, we should be able to find out when DELETE is better option for removing data or TRUNCATE should be used to purge tables.
参考资料请点击这里
DELETE语句可以有一个WHERE子句来删除特定的记录,而TRUNCATE语句不需要任何子句并擦除整个表。 重要的是,DELETE语句记录删除日期,而TRUNCATE语句不记录删除日期。
它方便的一个重要原因是,当您需要刷新数百万行表中的数据,但又不想重新构建它时。“Delete *”会花费很长时间,而Truncate对性能的影响可以忽略不计。
通过发出TRUNCATE TABLE语句,您将指示SQL Server删除表中的每条记录,而不进行任何日志记录或事务处理。
微软sql server的另一个不同之处在于,使用delete,你可以使用output语句来跟踪哪些记录被删除了,例如:
delete from [SomeTable]
output deleted.Id, deleted.Name
你不能用truncate这样做。