Collections:
Creating a Large Table with Random Data for Indexes in SQL Server
How To Create a Large Table with Random Data for Index Testing in SQL Server?
✍: FYIcenter.com
If you want to see how index can be used to improve data search performance, you have to build some large tables, which requires large amount of random data. This tutorial exercise helps you to build a large table with pure random data:
USE FyiCenterData; GO -- Drop the old table, if needed DROP TABLE fyi_random; GO -- Create a table with primary key CREATE TABLE fyi_random ( id INT, rand_integer INT, rand_number numeric(18,9), rand_datetime DATETIME, rand_string VARCHAR(80) ); GO -- Insert rows with random values DECLARE @row INT; DECLARE @string VARCHAR(80), @length INT, @code INT; SET @row = 0; WHILE @row < 100000 BEGIN SET @row = @row + 1; -- Build the random string SET @length = ROUND(80*RAND(),0); SET @string = ''; WHILE @length > 0 BEGIN SET @length = @length - 1; SET @code = ROUND(32*RAND(),0) - 6; IF @code BETWEEN 1 AND 26 SET @string = @string + CHAR(ASCII('a')+@code-1); ELSE SET @string = @string + ' '; END -- Ready for the record SET NOCOUNT ON; INSERT INTO fyi_random VALUES ( @row, ROUND(2000000*RAND()-1000000,0), ROUND(2000000*RAND()-1000000,9), CONVERT(DATETIME, ROUND(60000*RAND()-30000,9)), @string ) END PRINT 'Rows inserted: '+CONVERT(VARCHAR(20),@row); GO Rows inserted: 100000 SELECT TOP 10 * FROM fyi_random; GO id integer number date string 1 609596 896530.556031080 1891-04-22 rbxddlaayxq x... 2 401483 375107.107911612 1941-10-15 ou pdep rjixd... 3 -350570 103724.712787200 1921-11-01 uwylic paxtlp... 4 -125377 204467.582749559 1931-11-11 xoikcdv gxixb... 5 -222733 -967023.477922098 1934-10-13 vj pqkyj ujbn... 6 377398 263410.255290027 1960-04-02 btstgqqx dy... 7 -422529 43452.274555387 1911-04-19 qyoxqqo zuvxh... 8 -900104 357566.072355041 1833-10-19 d xyqfotmvbnd... 9 -724738 -396821.456353329 1939-09-27 rx ... 10 398355 -817318.223094167 1874-03-25 g on oekx bc ...
⇒ Measuring Performance of INSERT Statements in SQL Server
⇐ Creating an Index for Multiple Columns in SQL Server
2016-11-13, 2077🔥, 0💬
Popular Posts:
Why I Can Not Enter 0.001 Second in DATETIME values in SQL Server Transact-SQL? If you enter millise...
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
How To Use DATEADD() Function in SQL Server Transact-SQL? DATEADD() is a very useful function for ma...
How To List All User Names in a Database in SQL Server? If you want to see a list of all user names ...
What Is a Dynamic Performance View in Oracle? Oracle contains a set of underlying views that are mai...