I would create an iTVF to search a string for the starting positions of a keyword

CREATE FUNCTION dbo.GetAllStartingPositions (
    @String  varchar(8000)
  ,...

@jeff Moden

I have a fiber outage at the moment, so was unable to catch your Group By session on GUIDS V.S. FRAGMENTATION last night.

Is there perhaps a recording...

@jeff Moden

I have a fiber outage at the moment, so was unable to catch your Group By session on GUIDS V.S. FRAGMENTATION last night.

Is there perhaps a recording of your...

The queries would look something like this

SELECT      TOP ( 10 )
           ...

Try taking a look at theses 2 management views.  Change the ORDER BY in your queries to get highest use per I/O, CPU, Memry sys.dm_exec_query_stats

Try taking a look at theses 2 management views.  Change the ORDER BY in your queries to get highest use per I/O, CPU, Memry

sys.dm_exec_query_stats

sys.dm_exec_procedure_stats

You cannot index all those nvarchar fields.  So the sql engine has to do a full table scan to look for the matches.

Based on your terrible table designs, I cant think of anything that will help you.

Things that immediately stand out as what will hurt

• The field sizes are way too big...

If you convert your @DB_Users table variable to a temp table, the following code will dynamically create the cross-tab in SQL

DECLARE @Query nvarchar(MAX);

SET @Query = 'WITH cteData...

You can take a look at the following articles by Jeff Moden on dynamically creating PIVOTs and CROSS_TABs

ttps://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

The following query will satisfy your requirements

WITH cteData AS (
  SELECT  t.*
        , rn = ROW_NUMBER() OVER (...

This should give you the same result as your complicated calculation

SELECT @naam = 'I' + CONVERT(char(4), GETDATE(), 12) + '-' + RIGHT('00000' + @MAX_naam, 5)

Can you please show us a partial query so that we can see what you mean.  No need to add all 60 date fields.

If you have something like this

How to post data/code on a forum to get the best help.