javijerome
Programmer
so i have two tables in my database.and the other table name symbol_table should have all its elements deleted when the form closes, how do i do that?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
delete all element of a table
- Thread starter javijerome
- Start date
- Status
- Thread starter
- #3
javijerome
Programmer
how about the code? what code should i write to delete the elements of that certain table?
the delete statement is a sql string issued against the database. to the ado.net is how you communicate between the application and the database.
what code do you have already? we can point you in the right direction if we can see where you currently are.
Jason Meckley
Programmer
faq855-7190
faq732-7259
what code do you have already? we can point you in the right direction if we can see where you currently are.
Jason Meckley
Programmer
faq855-7190
faq732-7259
You might also want to look at the sql command truncate. It's supposed to be faster than the delete command. Check the syntax here:
I would build, in sql server, a stored procedure, then call that procedure from your code. Do a google search for how to do this if you need to. There are lots of resources available.
I would build, in sql server, a stored procedure, then call that procedure from your code. Do a google search for how to do this if you need to. There are lots of resources available.
truncate has a difference context then delete. truncate is not meant for "every day" use. you can google or ask in forum183 for the details on the differences.
stored procs is just one option to definging sql statements. they are not a cure all. other options include
1. raw sql scripts referenced in code
2. ORMs
it's a balance of performance, ease of deployment and the ability to maintain the system.
Jason Meckley
Programmer
faq855-7190
faq732-7259
stored procs is just one option to definging sql statements. they are not a cure all. other options include
1. raw sql scripts referenced in code
2. ORMs
it's a balance of performance, ease of deployment and the ability to maintain the system.
Jason Meckley
Programmer
faq855-7190
faq732-7259
From BOL:
Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
Remarks
Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:
Less transaction log space is used.
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 data and records only the page deallocations in the transaction log.
Fewer locks are typically used.
When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
Without exception, zero pages are left in the table.
After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.
If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.
Restrictions
You cannot use TRUNCATE TABLE on tables that:
Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
Participate in an indexed view.
Are published by using transactional replication or merge replication.
For tables with one or more of these characteristics, use the DELETE statement instead.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).
Truncating Large Tables
Microsoft SQL Server has the ability to drop or truncate tables that have more than 128 extents without holding simultaneous locks on all the extents required for the drop. For more information, see Dropping and Rebuilding Large Objects.
Permissions
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.
I really don't see much of a difference jmeckley. Truncate can be used for every day use (esp. on large tables, as it is faster). Unless of course, you NEED to log transactions. Otherwise just to empty a table, truncate works well.
Stored proc's offer better performance, generally, vs raw sql statements in code. Why? Because for the most part, a stored proc is already compiled and ready for use. While sql statements in code are not. Improves performance. Others have also argued it is more secure to use a stored proc. I worked for a large company that said any sql that is in production MUST be run from a stored proc.
Yes, I understand there may be times when raw sql statements may work out well.
Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
Remarks
Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:
Less transaction log space is used.
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 data and records only the page deallocations in the transaction log.
Fewer locks are typically used.
When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
Without exception, zero pages are left in the table.
After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.
If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.
Restrictions
You cannot use TRUNCATE TABLE on tables that:
Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
Participate in an indexed view.
Are published by using transactional replication or merge replication.
For tables with one or more of these characteristics, use the DELETE statement instead.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).
Truncating Large Tables
Microsoft SQL Server has the ability to drop or truncate tables that have more than 128 extents without holding simultaneous locks on all the extents required for the drop. For more information, see Dropping and Rebuilding Large Objects.
Permissions
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.
I really don't see much of a difference jmeckley. Truncate can be used for every day use (esp. on large tables, as it is faster). Unless of course, you NEED to log transactions. Otherwise just to empty a table, truncate works well.
Stored proc's offer better performance, generally, vs raw sql statements in code. Why? Because for the most part, a stored proc is already compiled and ready for use. While sql statements in code are not. Improves performance. Others have also argued it is more secure to use a stored proc. I worked for a large company that said any sql that is in production MUST be run from a stored proc.
Yes, I understand there may be times when raw sql statements may work out well.
- Thread starter
- #9
javijerome
Programmer
here's the download link for the program that i made. thanks.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
