Question: Hello we have an SQL server application running over a low bandwith connection.
We use ADO.NET.

I was wondering if anybody has any tips on minimizing traffic over the channel.
Im looking for answers on ADO.NET/SQL-Server specific tips that make it more efficient.
Not obvious answers like "dont fetch many records". 

En example for MySql would be "enable comptression=true" in the connection string.
I'm cannot find anything on transport layer compression in SQL server.

Do any of you people have experience with this ?
Are there important do's and dont's that we must know ? 

Thanks in advance..

Answer: 
I would use the OUTPUT clause present in SQL SERVER 2008 onwards...

[**OUTPUT Clause (Transact-SQL)**](http://msdn.microsoft.com/en-us/library/ms177564.aspx)

Something like...

```
BEGIN TRANSACTION

DELETE [table] OUTPUT deleted.* WHERE [woof] 

ROLLBACK TRANSACTION

```

INSERTs and UPDATEs can use the 'inserted' table too. The MSDN article covers it all.

**EDIT:**

This is just like other suggestions of SELECT then DELETE inside a transaction, except that it actually does both together. So you open a transaction, delete/insert/update with an OUTPUT clause, and the changes are made while ALSO outputting what was done. Then you can choose to rollback or commit.
When you are in the context of the transaction, you can rollback changes any time before the transaction is committed. (Either by calling commit tran explicitly or if a condition arises that will cause the server to implicitly commit the transaction)

```
create table x (id int, val varchar(10))

insert into x values (1,'xxx')
begin tran

delete from x
select * from x

rollback tran
select * from x

```