You’ll sometimes want to create a table quickly in SQL Server to test some concept or feature. If you’re anything like me you’ll quite often want to do so with a reasonable amount of data.
The brute force approach to loading a table in this scenario might be to use simple while loop. When you’re starting out with SQL Server you might be tempted to do something like this:
-- Disable notifications of row inserts for a small performance boost SET NOCOUNT ON GO DECLARE @counter AS INT = 0 WHILE @counter < 1000000 BEGIN INSERT INTO tblData (Data, MoreData) VALUES ( 'Data', 'MoreData') SET @counter = @counter + 1 END
Can’t argue with that for simplicity. This approach is effective, but won’t get you finished in a hurry. On my laptop, a while loop insert of 1 million rows takes around 4 minutes 50 seconds.
Now I like reasonable test volumes – but I don’t like waiting the best part of 5 minutes to get them.
There are a few more adventurous measures you can take to improve this dramatically, but in terms of keeping things simple and effective you can’t beat simply wrapping the WHILE loop in an explicit transaction:
DECLARE @counter AS INT = 0 BEGIN TRANSACTION WHILE @counter < 1000000 BEGIN INSERT INTO tblData (Data, MoreData) VALUES ( 'Data', @counter + 1) SET @counter = @counter + 1 END COMMIT
With that tiny change, you have turned a 1 million transaction operation into a single transaction.
In my environment that drops the operation from approaching 5 minutes down to 18 seconds
I know it seems obvious but I’ve seen numerous developers forget about the advantages of an explicit vs an implicit transaction at the right moment
Not bad for adding 3 words!