Wednesday, September 30, 2009
Exact Product Blog and on the Ready.Set.7 site of Microsoft.
On the Ready.Set.7 website you’ll find the Exact logo and behind it, a short piece describing the advantages.
A short film featuring Aad ‘t Hart can also be viewed, in which he gives his views on what Windows 7 can do for our customers. “Windows 7 increases the productivity of the Exact Globe end user,” Aad says.
With Exact Globe you are now able to run on the most recent available technology with Windows 7 and SQL Server 2008. Don't forget that you can buy SQL 2008 licenses for the use with Exact products with a big discount via Exact.
Tuesday, September 15, 2009
DELETE FROM MyTable WHERE COL1 = 1
it can take a while when you have for instance 1 million records to delete. It can results in a table lock which has a negative impact on the performance of your application.
As of SQL2005/2008 you can delete records in a table in batches with the DELETE TOP (BatchSize) statement. This method has 3 advantages
- It will not create one big transaction.
- It avoids a table lock.
- If the delete statement is canceled, only the last batch is rolled back. Records in previous batches are deleted.
CREATE TABLE DEMO (COL1 INT,COL2 INT)
DECLARE @COUNTER INT
SET @COUNTER = 1
INSERT INTO DEMO (COL1,COL2) Values (2,2)
WHILE @COUNTER < 50000
INSERT INTO DEMO (COL1,COL2) Values (1,@COUNTER)
SET @COUNTER = @COUNTER + 1
-- Show content of the table
SELECT COL1, COUNT(*) FROM DEMO GROUP BY COL1
-- Deleting records in batches of 1000 records
DECLARE @BatchSize INT
SET @BatchSize = 1000
WHILE @BatchSize <> 0
DELETE TOP (@BatchSize)
WHERE COL1 = 1
SET @BatchSize = @@rowcount
-- SELECT * FROM Demo -- Now we have only 1 record left