|
Delete and Truncate performs Identical tasks. but there are differences...
Delete is a DML command used to delete a particular record or some set of Records based on the WHERE-Clause. And It can also Delete all the Records when there is no WHERE-Clause specified. The Delete operation performed using Delete Command can be Rolled Back.
Truncate is a DDL Command used to delete all the Reocrds from the Table and also it Reinitializes the Table. Primary Key Identity values will be regenerated from the initial value for the subsequent entries after deleting all reocrds. WHERE-Clause can't be used in Truncate Command. The Delete operation performed using Truncate Command can't be Rolled Back.
Truncate is much faster than Delete
Note : Truncate = Delete + Commit
Regards
J O H N ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
Good answer. Get's a 5 for its clarity and completeness.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks Pete.
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
You're welcome. It's always a pleasure to read a detailed and accurate answer.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I think one other difference is that Delete is logged and truncate is not. Thus the ability to roll back etc. That is also part of the reason that truncate is faster.
Ben
|
|
|
|
|
Ok so I guess part of it is logged. I had to look it up.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
Ben
|
|
|
|
|
|
One other difference is that the user doing the TRUNCATE must have greater permissions than doing a DELETE. DELETE permissions can be granted to a user or role on a per-table basis; on SQL Server 2000, TRUNCATE permissions are only granted to the owner of the table, members of the sysadmin server role, and members of the db_owner or db_ddladmin fixed server roles.
SQL Server 2005's documentation is a little unclear, it says that the user must have ALTER permissions on the table but then repeats the sentence from SQL Server 2000 Books Online.
Obviously if you're connecting to SQL Server using sa or a trusted connection (Windows authentication) using an administrator account, you have those permissions. However, this is not a good practice as those accounts have full control over the entire database server, and as such, a mistake in your application logic could lead to accidental, or even deliberate, damage. In my view, you should firewall what an application can do by setting up less-privileged server logins and database users.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Delete - delete deletes the records from table it can be rollbacked also you can give the where condiition to it.
Truncate - delete all records from table There is no rollback it always commit without givening the commit.
SSK.
Anyone who says sunshine brings happiness has never danced in the rain.
|
|
|
|
|
Hi,
In Our Organisation we have a CRm which is used by 100 pcs at a time, we are regularly facing the problem of connection timeout bcoz of SQL. Now we plan to increase the seats to 200...
Is there any configuration setting that we need to look in to SQL Server 2000.
Plz suggest....
Regards,
Bala
|
|
|
|
|
This problem is due to database connections that are left open and are not closed properly. check your application to make sure that the connections are closed when it is not needed. Even though the concept the garbage collection work fine in ASP.NET to claim the unused resources, sometimes in a busy website the conneection pool will run out of connections before the Garbage collection kicks in. One solution to this problem - you can increase the Pool size to 200 in the connection string.
Max Pool Size=200
Regards
J O H N ![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
oledbcommand cmd=new oledbcommand(@"select F1,F2,F3 from [sheet1$]",con);
F2 datatype string when execute command get F2 values Null
please i need why get F2 null
but if values integer can get data
|
|
|
|
|
You posted the same question in the C# forum. Pick only one forum, please.
It might have something to do with you having a string in F2...
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
i read excel sheet all columns can getting but column contain string can not getting
can change any setting in connection string or command to solving problems
|
|
|
|
|
Hi,
I have an application A which runs a stored procedure. I want that stored procedure to get and send data to another application B. What can be the efficient ways, currently we are using temporary table and using pooling on that table by application B.
Regards
Shajeel
|
|
|
|
|
SQL Server doesn't offer any support for that. If you want to pass messages between applications, and have them persist in the case of software or hardware failure, Microsoft's solution is Microsoft Message Queueing[^].
The temporary table is one solution, but do remember that true temporary tables (named with a # or ## prefix) are stored in tempdb, which is not persisted across SQL Server runs. It is recreated from scratch every time SQL Server starts up. I believe it is also recreated whenever a SQL Server cluster fails over to a different node, though I'm not sure about that.
SQL Server 2005 and .NET Framework 2.0 offer the SqlDependency class, which can be used to send a query to the database and the database to then call the application back when the results of that query would change. For more information, look up "query notifications" in the index in Books Online.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thanks for the reply. We are actually using Oracle and VC 6 services and table is not actually temporary, it is simple table but data in it is only stored for message passing and then removed. I just wanted to know whether there is any better solution but it seems like our solution is the best we can get as we cannot modify first application.
Regards
Shajeel
|
|
|
|
|
in my application i'm using sql login to allow the users to log in .in this process i have to send the password and the user name which is risky, through the network , i want to encrypt the password and username,how can i do this,if i use encrypt =true , Is this enough....
|
|
|
|
|
can i protect my sql server2000 data bases (ldf - mdf files)
from attach or open by
password or any thing
ahmed eldeghedy
|
|
|
|
|
What sort of attack are you envisioning?
SQL Server 2000, and it successors are designed to run in a server environment. The access to the machines they are running on should be restricted. The servers will normally be in a secured server room.
|
|
|
|
|
Colin Angus Mackay wrote: What sort of attack are you envisioning?
My guess is that he's concerned that some one might make a copy of the database files and run with them.
Colin Angus Mackay wrote: access to the machines they are running on should be restricted
That would solve his problem...
Colin Angus Mackay wrote: The servers will normally be in a secured server room.
Yes, with machine gun totting security guys and vicious guard dogs :->
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Please keep your replies on the forum.
ahmed eldeghedy wrote: thank you
i am not thanking of that
that is good but that a small thanking some Project like windows base- application (accounting for small company) small to run @ server that make some developer set up sqlserver2000 to the local computer to the company
so that open the data base for any developer
A developer usually has rights to the database anyway. They are supposed to be sufficiently trusted for such access.
|
|
|
|
|
Hi,
I need a help about deletion of records.
I am using MS acces, and using recordset.Addnew() to add records into a table.if iam going on adding records it is adding at end only and the order of records is fine..but if i delete some existing records bu using recordset.Delete(), and then add some records the order is shuffling, and the new records are not added at end. Can anyone explain why it is happening and a solution for the records in order.
Thanks for suggestions
Regards
vijay
|
|
|
|
|
Not sure, it sounds like Access is placing the data where ever it feels like it. Shouldn't really matter from a developer's stand point, as long as the logical correctness of your application is in place.
"Try asking what you want to know, rather than asking a question whose answer you know." - Christian Graus
|
|
|
|
|
I wish to generate a sequence number in vb.net 1.1 based on the record count of a table.If more than one user want to create sequence number as abobe by accesing the same database and table concurrently, the table should be locked for the second user till the sequence number for the first user is created .
Please help me.
J Rasal Doss Solomon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
