CREATE PROC #CompareStarVsConstant
@NumberOfColumnsInTable INT = 1024,
@BatchIterations INT = 10,
@BatchTimeoutInSeconds INT  = 60,
@NumberOfStarQueries INT = 0 OUTPUT,
@NumberOfConstantQueries INT = 0 OUTPUT,
@NumberOfPKQueries INT = 0 OUTPUT,
@NumberOfColQueries INT = 0 OUTPUT
AS
IF(@NumberOfColumnsInTable NOT BETWEEN  2 AND 1024) OR
   (@BatchIterations < 1) OR
   (@BatchTimeoutInSeconds < 1)
BEGIN
RAISERROR('Invalid Params',16,1)
RETURN
END

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..##T') IS NOT NULL
    DROP TABLE ##T

SELECT @NumberOfStarQueries = 0, @NumberOfConstantQueries=0, @NumberOfPKQueries=0, @NumberOfColQueries = 0

DECLARE @table_create_sql NVARCHAR(MAX)

SELECT @table_create_sql = isnull(@table_create_sql,'') + ',C' + LEFT(number,4) + ' INT'
FROM master..spt_values
WHERE type='P' AND
     number BETWEEN 1 AND (@NumberOfColumnsInTable -2)

SET @table_create_sql = 'CREATE TABLE ##T (col1 int not null primary key, col2 int not null ' + ISNULL(@table_create_sql,'') + ')'

EXEC(@table_create_sql)


DECLARE @BatchCounter INT = 1,
        @CurrentBatchStarted DATETIME2,
        @BatchTimeoutInMicroSeconds INT = @BatchTimeoutInSeconds * 1000000


DECLARE @V int /*Holds results of execution to remove effect of results being sent back*/

WHILE @BatchCounter <= @BatchIterations
BEGIN

SET @CurrentBatchStarted = SYSDATETIME()
WHILE  DATEDIFF(MICROSECOND,@CurrentBatchStarted,SYSDATETIME()) < @BatchTimeoutInMicroSeconds
BEGIN
SELECT @V=1 WHERE EXISTS (SELECT * FROM ##T) --OPTION(RECOMPILE)
SET @NumberOfStarQueries +=1
END

SET @CurrentBatchStarted = SYSDATETIME()
WHILE  DATEDIFF(MICROSECOND,@CurrentBatchStarted,SYSDATETIME()) < @BatchTimeoutInMicroSeconds
BEGIN
SELECT @V=1 WHERE EXISTS (SELECT 1 FROM ##T) --OPTION(RECOMPILE)
SET @NumberOfConstantQueries +=1
END
SET @CurrentBatchStarted = SYSDATETIME()
WHILE  DATEDIFF(MICROSECOND,@CurrentBatchStarted,SYSDATETIME()) < @BatchTimeoutInMicroSeconds
BEGIN
SELECT @V=1 WHERE EXISTS (SELECT col1 FROM ##T) --OPTION(RECOMPILE)
SET @NumberOfPKQueries +=1
END


SET @CurrentBatchStarted = SYSDATETIME()
WHILE  DATEDIFF(MICROSECOND,@CurrentBatchStarted,SYSDATETIME()) < @BatchTimeoutInMicroSeconds
BEGIN
SELECT @V=1 WHERE EXISTS (SELECT col2 FROM ##T) --OPTION(RECOMPILE)
SET @NumberOfColQueries +=1
END


SET @BatchCounter +=1;
END

DROP TABLE ##T

GO




DECLARE @NumberOfStarQueries INT = 0,
        @NumberOfConstantQueries INT = 0,
        @NumberOfPKQueries INT = 0,
        @NumberOfColQueries INT = 0,
        @BatchIterations INT  = 2,
        @BatchTimeoutInSeconds INT  = 2

DECLARE @TestSeconds int = 40*@BatchIterations*@BatchTimeoutInSeconds
DECLARE @SecondsPerBatch float = @BatchIterations*@BatchTimeoutInSeconds


RAISERROR('Beginning Test, Expected Completion in a little over %d seconds.',0,1,@TestSeconds) WITH NOWAIT

DECLARE @NumberOfColumnsInTable INT = 1024

--Do 10 iterations checking effect of column counts from 2 to 1024
WHILE (@NumberOfColumnsInTable >= 2)
    BEGIN
        EXEC #CompareStarVsConstant
            @NumberOfColumnsInTable,
            @BatchIterations,
            @BatchTimeoutInSeconds,
            @NumberOfStarQueries OUTPUT,
            @NumberOfConstantQueries OUTPUT,
            @NumberOfPKQueries OUTPUT,
            @NumberOfColQueries OUTPUT

        SELECT @NumberOfColumnsInTable AS [@NumberOfColumnsInTable],
               @NumberOfStarQueries/@SecondsPerBatch    AS [@NumberOfStarQueries per sec],
               @NumberOfConstantQueries/@SecondsPerBatch AS [@NumberOfConstantQueries per sec],
               @NumberOfPKQueries/@SecondsPerBatch    AS [@NumberOfPKQueries per sec],
               @NumberOfColQueries/@SecondsPerBatch AS [@NumberOfColQueries per sec]
        RAISERROR('',0,1) WITH NOWAIT; /*Flush Buffer so see results sooner in SSMS*/
        SET @NumberOfColumnsInTable = @NumberOfColumnsInTable/2;
    END



DROP PROC #CompareStarVsConstant