{"id":416,"date":"2016-11-01T06:10:59","date_gmt":"2016-11-01T06:10:59","guid":{"rendered":"http:\/\/waynenguyen.org\/dev\/?p=416"},"modified":"2016-11-01T06:10:59","modified_gmt":"2016-11-01T06:10:59","slug":"tips-to-generate-a-large-sql-server-table-for-testing","status":"publish","type":"post","link":"https:\/\/bruceng.com\/dev\/?p=416","title":{"rendered":"Tips to generate a large SQL Server table for testing"},"content":{"rendered":"

To generate a large table with millions of records, it’s possible to use the following script:<\/p>\n

declare<\/span> @RecordsNum <\/span>int<\/span> =<\/span> 10000000<\/span>-(<\/span>select<\/span> count<\/span>(*)<\/span> from<\/span> Person<\/span>)<\/span><\/p>\n

declare<\/span> @Count <\/span>int<\/span> =<\/span> 0<\/span><\/p>\n

set<\/span> nocount<\/span> on<\/span><\/p>\n

Begin<\/span> transaction<\/span><\/p>\n

while<\/span>(<\/span>@Count <\/span><<\/span> @RecordsNum<\/span>)<\/span><\/p>\n

begin<\/p>\n

insert<\/span> into<\/span> MyTable<\/span>(<\/span>Name<\/span>,<\/span> Address<\/span>,<\/span> Age<\/span>)<\/span><\/p>\n

values<\/span>(<\/span>substring<\/span>(<\/span>Convert<\/span>(<\/span>varchar<\/span>(<\/span>255<\/span>),<\/span> NewID<\/span>()),<\/span> 0<\/span>,<\/span> 30<\/span>),<\/span><\/p>\n

substring<\/span>(<\/span>Convert<\/span>(<\/span>varchar<\/span>(<\/span>255<\/span>),<\/span> NewID<\/span>()),<\/span>0 <\/span>,<\/span>30<\/span>),<\/span> RAND<\/span>(<\/span>100<\/span>)<\/span> *<\/span> 100<\/span>)<\/span><\/p>\n

set<\/span> @Count <\/span>=<\/span> @Count <\/span>+<\/span> 1<\/span><\/p>\n

End<\/p>\n

commit<\/p>\n

select<\/span> count<\/span>(*)<\/span> from<\/span> MyTable<\/span><\/p>\n

Some things to do to improve performance of the script:<\/p>\n