I don't have a SQL Server 2005 instance to test against, but this works under SQL Server 2008. It uses a couple of Common Table Expressions and a Windowing Function
DECLARE @TestData TABLE (
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-02','T');
insert into @...
If your goal is to log sp_Blitz's output to table, use the @OutputDatabaseName, @OutputSchemaName, and @OutputTableName parameters as described in the documentation. For example, this will write the sp_BlitzOutput to a table named DBAtools.dbo.BlitzResults:
sp_Blitz @OutputDatabaseName = 'DBAtools',
@OutputSchemaName = 'dbo',
@OutputTableName = '...
One way you could go about this if you want to influence data types or column names is create an intermediate table to store the result of the stored procedure and then selecting from it.
Something along the lines of
CREATE TABLE #temp (< column definitions like the stored procedure returns >);
INSERT INTO #temp EXEC sp_Blitz;
SELECT < CAST ...
For that kind of need, I like to build a SSIS package that will copy the content of the view to a different SQL DB (on a different instance if I want to make sure their reporting won't hurt the prod DB).
You can then schedule the data refresh when you want and you can also create index that would make their reports run faster.
Thinking about the requirements:
"quickly get access to the data without overloading the DB"
"pull them what was made static [at] the time the view was created"
Especially the part about the data being static captured at a moment in time, I doubt a view is what you want. I think you may want something more like CDC, but that wasn't ...
You should be able to get it by using an OUTER APPLY join plus TOP 1
(r.race=1 and r.program = 'gold')
r.race is null
case when r.race is null then 1 else 0 end
commission | program | race
---------: | :------ | ---:
14 | gold | 1
23 | gold | null
Significant gains? No, I shouldn't think so.
SQL Server's indexes are B-Trees. These have a hierarchical nature. To read a row the server starts at the root node of the index then steps through one or more intermediate nodes before reading the row's values via the leaf node. (Details vary between clustered & non-clustered indexes, and heaps.) The speed-...
The answer by Remus was spot on. But since I needed to dig a bit more, here's more details. This is tested on SQL Server 2017, but the principle should be the same.
Check the following view definitions. I was mostly looking for user mappings, but if you need more info about servers, you find those tables from the first view.