Skip to content

Prepare test data

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATE TABLE [dbo].[TestData](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [tuuid] [uniqueidentifier] NULL,
    [tint] [int] NULL,
    [tvarchar] [varchar](150) NULL,
    [tdecimal] [decimal](18, 2) NULL,
    [xsmalldatetime] [smalldatetime] NULL,
  [xdatetime] [datetime] NULL,
  [xdatetime2] [datetime2] NULL,
  [xdate] [date] NULL,
  [xtime] [time] NULL,
    [tbit] [bit] NULL
 CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestData] ADD  CONSTRAINT [DF_TestData_tuuid]  DEFAULT (newid()) FOR [tuuid]

-- Generate numbers from 1 to 1,000,000 using a recursive CTE
;WITH CTE_Numbers AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM CTE_Numbers
    WHERE Number < 10
)
INSERT INTO TestData (tint,tvarchar,tdecimal,xsmalldatetime,xdatetime,xdatetime2,xdate,xtime,tbit)
SELECT 
  Number,
  CONVERT(varchar(255), NEWID()),
  ROUND(RAND(CHECKSUM(NEWID())) * (1000000), 2),
  DATEADD(MINUTE,  (ABS(CHECKSUM(NEWID())) % 65530), getdate() ),
  DATEADD(MINUTE,  (ABS(CHECKSUM(NEWID())) % 65530), getdate() ),
 DATEADD(MINUTE,  (ABS(CHECKSUM(NEWID())) % 65530), getdate() ),
  DATEADD(MINUTE,  (ABS(CHECKSUM(NEWID())) % 65530), getdate() ),
  DATEADD(MINUTE,  (ABS(CHECKSUM(NEWID())) % 65530), getdate() ),
  CAST(ROUND(RAND(),0) AS BIT)
FROM CTE_Numbers
OPTION (MAXRECURSION 0) -- Enable unlimited recursion

-- Verify the data
SELECT COUNT(*) AS NumRows
FROM TestData
select top 100 * from TestData order by tdecimal asc
-- Cleanup
--DROP TABLE TestData
XML
1
2
3
4
5
6
7
<srs label="Spent Time">
  <def>
    <itm model="command"   name="data" opts="server"><![CDATA[select * from TestData ]]></itm>
    <itm name="view" link="self"><![CDATA[select * from data]]></itm>
    <itm name="totals" link="self"><![CDATA[select sum(tdecimal) total, strftime('%Y', tsmalldate) year from data group by strftime('%Y', tsmalldate )]]></itm>
  </def>
</srs>