February 15, 2009 at 12:23 am
Hello,
I am learning indexes and I think I know differences between them (what is clustered and non-clustered, what is unique...). However - I have trouble setting up test environment for testing all different indexes. Is there any good tutorial which also specifies actual data and performance (like... create table with this data, now try to select/insert data into this table once with clustered index on this column and once with non-clustered index on this column...).
Thanks
February 15, 2009 at 10:30 am
there are few articles on this web-site on how to insert test records.
eg..
http://www.sqlservercentral.com/articles/scripting/64884/
February 16, 2009 at 6:46 am
I wouldn't call it a tutorial, but I did write up my experiences doing this sort of thing. It will give you an idea of how you could approach it. It's over at Simple-Talk[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 16, 2009 at 7:55 am
Here's script that I use to create test tables of any size:
ALTER proc [dbo].[TestTable_Create](@name as varchar(55), @reccount as int) as
--sproc to make as TestTableXX
BEGIN TRY
exec('Drop table TestTable'+@name)
END TRY
BEGIN CATCH
Print ' '
END CATCH
EXEC('Select TOP('+@reccount+') Identity(int) as RowID
, cast(-1 as bigint)*((select count_big(*) from master..sysobjects)*o1.id + o2.id) as NegPkID
, o1.xtype+o2.name + o2.xtype+o1.name + cast(o1.uid as varchar(9)) + cast(o2.uid as varchar(9)) as StrPK
, cast(o1.id as bigint)-o2.id as DiffID, cast(o1.id as bigint)+o2.id as AddID
, o2.*
Into TestTable'+@name+'
From master..sysobjects o1
join master..sysobjects o2 ON 1=1
Order By o1.id')
This has a couple of nice features: first, it has columns of many different types and with many different distributions of data. Secondly, it produces reasonably "wide" rows, instead of the unrealistically "skinny" rows of so many examples.
I know that Jeff Moden has an even more sophisticated data generator that he uses.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply