One T-SQL query output to multiple record sets

Question

Don't ask for what, but i need two tables from one SQL query.

Like this...

Select Abc, Dgf from A

and result are two tables

abc
1
1
1

dgf
2
2
2

More details? Ok lets try.

Now i have sp like this:

    SELECT a.* from ActivityView as a with (nolock) 
where a.WorkplaceGuid = @WorkplaceGuid

    SELECT b.* from ActivityView as a with (nolock) 
left join PersonView as b with (nolock) on a.PersonGuid=b.PersonGuid  where a.WorkplaceGuid = @WorkplaceGuid

It's cool. But execution time about 22 seconds. I do this because in my programm i have classes that automaticly get data from records set. Class Activity and class Person. That why i can't make it in one recordset. Program didn't parse it.

Answer 1

You can write a stored procedure that has two SELECTs.

SELECT Abc FROM A AS AbcTable;

SELECT Dgf FROM A AS DfgTable;

Depending on your specific requirements, I would consider just submitting two separate queries. I don't see any advantage to combining them.

Answer 2

SQL Server supports legacy COMPUTE BY clause which acts almost like GROUP BY but returns multiple resultsets (the resultsets constituting each group followed by the resultsets with the aggregates):

WITH    q AS
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2 AS id
        )
SELECT  *
FROM    q
ORDER BY
        id
COMPUTE COUNT(id)
BY      id

This, however, is obsolete and is to be removed in the future releases.

Answer 3

Those don't seem to be excessively complicated queries (although select * should in general not be used in production and never when you are doing a join as it needlessly wastes resources sending the value of the joined field twice). Therefore if it is taking 22 seconds, then either you are returning a huge amount of data or you don't have proper indexing.

Have you looked at the execution plans to see what is causing the slowness?