Can names have trailing spaces?

INSERT INTO TC60(Categories)
SELECT 'a/b' UNION ALL
SELECT 'a/b '

¿Does anyone know some trick to generate lots of data without duplicate records to test performance?

@Leszek,

If you find any trailing spaces, you can safely truncate the trailing spaces.

@Jacob

Thx

@diegoperdices

My data, result (first 10 and last 10) and stats:

INSERT INTO TC60(Categories)
select 'Computers/Apple/Macintosh' union all
select 'Food/Apple/Lobo' union all
select 
    'x' + cast((select cast('/' as varchar(max)) + replicate('a',1 + (tl.N/2 - abs(x.N - tl.N / 2)) * 4) + cast(tl.N + 1 as varchar(max)) + '_'+ cast(x.N + 1 as varchar(max))
    				from tsqlc_Tally x where x.N < tl.N + 2
    				for xml path('') ,type)
    			 as varchar(max))
from tsqlc_Tally tl where tl.N < 100


1   Computers	NULL
2   Food	NULL
3   x	NULL
4   Apple	1
5   Apple	2
6   a100_1	3
7   a10_1	3
8   a11_1	3
9   a12_1	3
10  a13_1	3


4998    aaaaa98_96	4896
4999    aaaaaaaaaaaaa99_96	4897
5000    aaaaaaaaa100_97	4898
5001    a97_97	4899
5002    a98_97	4900
5003    aaaaaaaaa99_97	4901
5004    aaaaa100_98	4902
5005    aaaaa99_98	4903
5006    a100_99	4904
5007    a99_99	4905


(5007 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 30452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TC60'. Scan count 1, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 192, lob physical reads 0, lob read-ahead reads 144.

 SQL Server Execution Times:
   CPU time = 4719 ms,  elapsed time = 5496 ms.

hmmm - Leszek - - could you or Jacob confirm that the results for these two lines should look as I have them below please ?? Just want to make sure I understand the problem correctly

Computers/Apple/Macintosh

Food/Apple/Lobo

1---Computers---Null

2---Food---Null

3---Apple---1

4---Apple---2

5---Macintosh---3

6---Lobo---4

Hello,

Is it possible to have a category which is parent and terminal node at the same time? In other words will there always be '/' in the path?

Thanks

@lof, Only the leaf level nodes can have items within them. So a category can either have other child categories or can be at the leaf level and can have items.

OK, there is no limit in the number of levels in the hierarchy, but are there limit in length of Categories field? Or to put it simple, is Categories varchar(max) or maybe varchar(8000)?

So: 'Computers/Apple/Macintosh' & 'Food/Apple/Lobo' are:

1 Computers Null

2 Food Null

3 Apple 1

4 Apple 2 ......

but 'food/baby/milk powder' & 'food/baby/icecream' are:

2 food Null

4 baby 2 ....

is this correct?

@Leszek thanks for test data, but are you sure your sorting is ok?

Lets take row 6 and 7:

6   a100_1  3 
7   a10_1   3 

In my opinion a101 should be before a1001. Below my results and stats:

1   Computers	NULL
2   Food	NULL
3   x	NULL
4   Apple	1
5   Apple	2
6   a1_1	3
7   a10_1	3
8   a100_1	3
9   a11_1	3
10  a12_1	3

5000    aaaaaaaaaaaaa100_96	4994
5001    aaaaaaaaaaaaa99_96	4995
5002    a97_97	4996
5003    a98_97	4997
5004    aaaaaaaaa100_97	4998
5005    aaaaaaaaa99_97	4999
5006    aaaaa100_98	5002
5007    aaaaa99_98	5003
5008    a100_99	5004
5009    a99_99	5005

(5009 row(s) affected)
Table 'Worktable'. Scan count 12, logical reads 179040, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TC60'. Scan count 6, logical reads 234, physical reads 0, read-ahead reads 0, lob logical reads 708, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 8375 ms,  elapsed time = 8552 ms.