posted 8/15/2012 10:40:06 AM by Ahmad Osama - Views: [4647]
When posting my last blog on sequence I realized that sequences can also be used to generate dummy data for test tables. On further analysis I found that it’s faster than the conventional while loop method. This blog post gives a simple way to generate dummy data using sequence object and also compares it with the While Loop Method.
Method 1:
Method 2:
The Method 2, which uses Sequence object is 99% faster than the Method 1 which uses while loop for the same purpose. Another drawback of Method 1 is that it will generate lot of T-Logs where as Method 2 won’t as it’s a bulk insert operation.
If you like our blogs do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks
Thanks,
Ahmad(@ahmad_4u)
Ahmad Osama (Member since: 1/25/2012 1:03:07 PM) Ahmad Osama is a MCP Database Administrator/Developer, an avid gamer and a chicken lover. Ahmad started his career in the sales industry working as database executive; responsible for report writing, application development and basic database administration. In 2008 he joined World Fashion Exchange as Database Administrator. While in this role he focused on troubleshooting and performance tuning. In 2010 he joined The Perfect Future Technologies and has been awarded as best new comer and expert of the year working as database administrator on one of largest political database in the world. Ahmad has experience in database administration, SSIS, performance tuning, VLDBs and web development. When not working on SQL Server, he can be found glued to his Xbox.
View Ahmad Osama 's profile
Good article. We really have been missing Sequence in SQL Server. Nice addition to 2012
Thanks Abhishek - Yes they added lot of mysql-oracle things to SQL 2012
Dear Sir,
Really helpful in terms of time and performance.
Thanking You
Awesome Ahmad .A small comment i think the sole purpose of Sequence object is to generate database level unique values. You can use it for creating dummy data as said in the article ,but i think it would be more advisable to use SQL Server data generation template. Here's a small video http://youtu.be/8R3-xgmOTeE?hd=1 , thanks for your effort again.
Good Article..
Bulk Insert through Insert into / Selct into is cheaper than Cursor/LOOP...
Thanks Shivprasad and Suman for your valuable comments.
Interesting find. Though it's not a fair game comparing multiple INSERT statements versus one bulk insert. I believe it would be more interesting if you would compare the bulk insertion with sequence versus bulk insertion with ROW_NUMBER or something like that, or even IDENTITY in that matter.
Leave a comment