Looks like up until SQL Server 2000 there was an undocumented command DBCC CHECKDBTS that did exactly what you want but it is no more. It looks like it is stored on the database boot page with no (safe, see below) way of manually setting it.
I'm not sure why you want to do this and I suggest that you don't bother but if you must then you will likely find set based inserts considerably quicker.
(NB: The below might need tweaking to avoid overshooting the target as I found that @@DBTS incremented by 1003921 for each million row insert but should be an improvement...)
CREATE TABLE __DUMMY (I TINYINT, TS TIMESTAMP)
DECLARE @Target INT, @Batch INT
SET @Target = 666666666
WHILE @Target > CONVERT(INT, @@DBTS)
BEGIN
RAISERROR ('@@DBTS = %d',0,1,@@DBTS) WITH NOWAIT;
SET @Batch = @Target - CONVERT(INT, @@DBTS);
IF @Batch > 1000000
SET @Batch = 1000000;
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
INSERT INTO __DUMMY (I)
SELECT 1
FROM cteTally
WHERE N <= @Batch;
TRUNCATE TABLE __DUMMY
END
DROP TABLE __DUMMY
Or alternatively if this is only a development database, you have backups and you aren't going to come complaining to me if it all goes wrong you could do some experimenting with the undocumented and highly warned against DBCC WRITEPAGE command.
USE tempdb;
IF DB_ID('TestDBTS') IS NOT NULL
DROP DATABASE TestDBTS;
CREATE DATABASE TestDBTS;
USE TestDBTS;
DBCC WRITEPAGE(TestDBTS, 1, 9, 412, 8, 0xAA86BC2700000000);
ALTER DATABASE TestDBTS SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE TestDBTS SET ONLINE WITH ROLLBACK IMMEDIATE;
USE TestDBTS;
SELECT @@DBTS;
DBCC TRACEON (3604);
DBCC DBINFO ('TestDBTS');
DBCC TRACEOFF (3604);
What do you expect your real life volume of data to be?
For 10 million rows, I wouldn't bother with partitioning. The overhead far outweighs the benefits: partitioning isn't a silver bullet to cure performance issues.
To answer,
Point 1: on the first run, data needs loaded into memory ("buffer pool") and will stay cached until evicted based on memory pressure and usage. Personally, I'd test with the cache filled because you'd expect your app to require that data very often, especially if you think partitioning is the solution to some problem
For point 2, what queries do you expect to run in production? The queries should be representative of this production load. However they should test different realistic filter combinations with and without partition key at least.
Edit, some reading, after comments below:
Best Answer
I would enable Read-Committed Snapshot Isolation, which does row versioning under the covers by making use of tempdb. Readers will not block writers, which is what you're looking for in this scenario.
This is a per-database level setting, which doesn't require a reboot. However, you must have tempdb configured correctly (appropriate fixed-size file growth, sufficient data files, etc.).
(Read more here: Choosing Row Versioning-based Isolation Levels.)