Nonclustered vs Clustered PK indexes - Performance

Also pull the DBCC out of the tight loop:

truncate table company.employee

BEGIN TRANSACTION;
WHILE(@temp < 100000)
BEGIN
    DECLARE @ssn int;
    SELECT @ssn = round(RAND()*987654321, 0);
    insert INTO company.employee values ('Maria', 'G', 'Sousa', @ssn, '2001-01-01', 'Rua XPTO', 'M', 1200, NULL, NULL);
 
    SET @temp +=1;
END;

commit transaction;

And you can just leave it out as there's no reason to flush the page cache when inserting into an empty table.

If that doesn't do it, post your complete test script and the results you are getting.

David

---

David http://blogs.msdn.com/b/dbrowne/

These are the results I get:

btw, I forgot to mention that my professor made the tests in an virtual machine running on a mac, with an SSD.

/*
 9.1 a)
        --i.   PK CLUSTERED with FILLFACTOR=100 and PAD_INDEX=ON
        --ii.  PK CLUSTERED with FILLFACTOR=80 and PAD_INDEX=ON
        --iii. PK NONCLUSTERED with FILLFACTOR=100 and PAD_INDEX=ON
        --iv.  PK NONCLUSTERED with FILLFACTOR=80 and PAD_INDEX=ON

 9.1 b)
        i.   index fragmentation %:
                 a)i.   99,49 %
                 a)ii.  99,09 %
                 a)iii. 99,01 %
                 a)iv.  98,62 %

        ii.  index pages fill %
                 a)i.   69,88 %
                 a)ii.  68,61 %
                 a)iii. 73,37 %
                 a)iv.  76,65 %

        iii. Elapsed times: (values in ms for 100000 inserts)
                                w/DBCC DROPCLEANBUFFERS         wo/DBCC DROPCLEANBUFFERS
                a) i.           180430                                        4360  (with 128MB memory: 23096)
                a) ii.          196030                                        4083  (with 128MB memory: 20940)
                a) iii.         198266                                        5133  (with 128MB memory: 10043)
                a) iv.          205726                                        5326  (with 128MB memory: 6366)


                Given by professor: (server and client running in a virtual machine on a mac with SSD)
             Elapsed times: (values in ms for 100000 inserts)
                                w/DBCC DROPCLEANBUFFERS?
                a) i.           235640
                a) ii.          129360
                a) iii.         118673
                a) iv.          117373
*/

Are these values expected?



Thanks!