Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Quick and dirty way to make a very large table


Quick and dirty way to make a very large table

Author
Message
GabyYYZ
GabyYYZ
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 2332
Comments posted to this topic are about the item Quick and dirty way to make a very large table

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
Gaby,

Ok... I realize that you said "quick and dirty", but I keep looking at this article and wondering... why? Blink Why would someone need to generate a giga-byte sized test table with virtually no useful test data in it? The only thing I can think of (maybe) is to use it for testing backups.

And, that's a lot of RBAR in that code... it can be done without any RBAR using the following...

--===== If the test table already exists, drop it so we can rebuild it
IF OBJECT_ID('dbo.BigTable','U') IS NOT NULL
DROP TABLE dbo.BigTable
GO
--===== Define a handful of dynamic SQL variables
DECLARE @SQL1 VARCHAR(8000),
@SQL2 VARCHAR(8000),
@SQL3 VARCHAR(8000)

--===== Define the "fixed" portion of the SELECT list
SELECT @SQL1 = '
SELECT TOP 265000
IDENTITY(INT,1,1) AS RowID,
GETDATE() AS EntryDate,
' + CHAR(9)

--===== Define the 220 GUID columns we want to build.
-- This uses the variable overlay technique of concatenation.
SELECT @SQL2 = COALESCE(@SQL2+','+CHAR(10)+ CHAR(9),'') + 'NEWID() AS Val' + CAST(Number AS VARCHAR(10))
FROM Master.dbo.spt_Values --Could use a Tally or Numbers table here
WHERE Type = 'P'
AND Number BETWEEN 1 AND 220

--===== Define the "Into" and the cross join that will spawn the rows.
-- This uses the same cross-join technique usefull in building Tally and Numbers tables
SELECT @SQL3 = '
INTO dbo.BigTable
FROM Master.dbo.SysColumns sc1 WITH (NOLOCK)
CROSS JOIN Master.dbo.SysColumns sc2 WITH (NOLOCK)'

--===== Show what the final code looks like, just for fun...
-- PRINT @SQL1+@SQL2+@SQL3

--===== ... and then run it. (Takes about a minute on my ancient but useful box)
EXEC (@SQL1+@SQL2+@SQL3)




--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GabyYYZ
GabyYYZ
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 2332
Hmmm...true. Smile Ran it very quickly on my box as well.

I discovered one more way to create a big table a day or so after submitting my script. Create your table with one dummy_column as default null (or default getdate()) and the rest default newid() columns.

Insert 1 row into the table with default values then loop it as so:


insert bigtable default values
set @ctr = 0
set @num_iterations = 18 -- this is a DOUBLING number, so anything past 18 and you're getting into slow territory.
while @ctr < @num_iterations
begin
insert into bigtable(dummy_column)
select dummy_column from bigtable
set @ctr = @ctr + 1
end



Dummy_column can be anything, including null, as long as the other columns have default newid() set. This is also pretty quick way to create a large table.

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
You still haven't answered the question though... why would anyone create such a table of 1 gig of almost nothing but NEWID()'s?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GabyYYZ
GabyYYZ
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 2332
Jeff Moden (9/5/2008)
You still haven't answered the question though... why would anyone create such a table of 1 gig of almost nothing but NEWID()'s?


I guess it was to generate a large enough backup for a third-party backup utility (i.e. Litespeed) to see how it performed. Didn't want to toy with any production servers.

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
Thanks, Gaby... Smile

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search