Lee Everest created a post named MongoDB vs. SQL Server – INSERT comparison where he compared inserting 50001 rows with MongoDB vs. SQL Server. So he claims that MongoDB inserts 50001 rows in 30 seconds while the SQL Server one takes 1.5 minutes. Okay so I looked at this SQL Script and can make 2 improvements
First create this table
CREATE TABLE MongoCompare (guid uniqueidentifier ,value int ) GO
Here is the script he used.
DECLARE @id int = 1 WHILE (@id < 500001) BEGIN INSERT INTO MongoCompare VALUES (newid(), @id) SET @id+=1 END GO
Now if I was to write that code I would write it with an explicit transaction and I would also use nocount on. So If we do this
SET NOCOUNT ON BEGIN TRAN DECLARE @id int = 1 WHILE (@id < 500001) BEGIN INSERT INTO MongoCompare VALUES (newid(), @id) SET @id+=1 END COMMIT
SQL Server now only takes 6 seconds and you have one atomic block of code, either all succeeds or nothing succeeds.