Sql-server – Query To Show All Users Who Have Multiple Managers

Those XML results you keep mentioning. I do not think you are seeing what you think you are seeing. ;-)

Is FOR XML magic?

When you have the XML result in the grid, click on the left margin that contains the row number of the result set (there should be just 1 row). That should highlight the XML result. Now hit Control-C to copy the XML and then in the query editor above, put the cursor on the beginning of an empty line and hit Control-V to paste the XML. That should leave the cursor on the far right, at the end of the XML. Look down at the status bar that has the Line #, Column #, and Character #. What does it shown for Column # and Character #? Mine shows 2033, yet the XML got clipped and there should be more.

Now click on the XML itself in the result set. That takes you to another tab with the full XML result, unclipped. That is more than likely well over 2033 characters of text.

The "instantaneous" result you are seeing with the FOR XML is merely the fact that SSMS is only displaying the first 2033 characters of however many there are, where it is 5000 or 5,000,000 characters more in the actual result value. And in fact, if your query takes long enough, you can pay close attention to the query timer in the lower, right-hand corner and if you re-run the query and are returning 10+ rows, that counter can still be going after the result grid finishes displaying its truncated XML snippet.

Do the number of rows and columns greatly affect the time to display?

I put together some code (shown below), which returns 2300 columns, to test this behavior. It starts out with the FOR XML being uncommented. After the initial run, it returns immediately each time, no matter how you adjust the @Rows input param. Set it to 10, then 20, then 40. The XML always comes back immediately. But also watch the query timer as it will keep going, especially as you increase the value of @Rows.

Then comment-out the SET statement that adds the FOR XML so you get the 2300 individual fields in the grid. It might take 30 takes to run the first time. Running again it took me only 2 - 3 seconds each time, even as I increased the @Rows from 4 to 10, 20, 40, 50, even 80. Hence, the time it takes to display the results for 2000+ columns is not a function of how many rows are being returned, at least not at this small scale.

The Code

DECLARE @Select NVARCHAR(MAX) = N'SELECT TOP (@Rows)
         OBJECT_NAME(sac1.[object_id]) AS [ObjectName], *';
DECLARE @From NVARCHAR(MAX) = N'FROM   [sys].[all_columns] sac1';
DECLARE @JoinTemplate NVARCHAR(MAX) = N'
INNER JOIN [sys].[all_columns] sac{{counter}}
        ON sac{{counter}}.[object_id] = sac1.[object_id]
       AND sac{{counter}}.[column_id] = sac1.[column_id]';

DECLARE @CRLF NCHAR(2) = NCHAR(0x0D) + NCHAR(0x0A);
DECLARE @Counter INT = 2;

WHILE (@Counter < 93)
BEGIN
  SET @From += REPLACE(@JoinTemplate, N'{{counter}}', CONVERT(NVARCHAR(5), @Counter));

  SET @Counter += 1;
END;


SET @Select += @CRLF + @From;
--SET @Select += @CRLF + N'ORDER BY OBJECT_NAME(sac1.[object_id])';
SET @Select += @CRLF + N' FOR XML RAW, ELEMENTS';

EXEC sp_executesql
  @Select,
  N'@Rows INT',
  @Rows = 4;

Uncomment the SET line that has the ORDER BY to see that it takes the full 30 - 40 seconds every time you run the query (due to the sort).

You have transposed your desired result so it is rather straightforward to achieve what you want:

select storenum
     , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end as x
     , count(1)
from monster.dbo.storesales
group by storenum
    , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end

Now you can map each storenum / notes combination to a text, something like:

select case when storenum = '1463' and x = 0 then 'Passed w/ notes'
            when storenum = '1463' and x = 1 then ...
            when ...
     , cnt
from (
    select storenum
         , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end as x
     , count(1) as cnt
    from monster.dbo.storesales
    group by storenum
           , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end
) as T