Steve,

After plugging in one of my in house tally functions (dbo.tfn_Tally_10k) into both functions, your version is MUCH faster.

Using my function, on my DEV box, using a @variable dump... The...

The OP bailed out of the conversation long ago but not before establishing a constant pattern of moving the goal posts. The "after string" simply presented a more robust solution.

Your...

My test harness...

SET NOCOUNT ON;
GO
  DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
  PRINT(CONCAT(CHAR(13), CHAR(10), N'        wait a moment...', CHAR(13), CHAR(10)));
 ...

My previously posted code reformatted into an iTVF and replacing the tally function with an inline tally...

SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

CREATE FUNCTION dbo.split_bracket_values
/* ===================================================================
07/25/2020 JL, Created: Just...

“One good test is worth a thousand expert opinions.” - Wernher Von Braun

If anyone would like to do a 1M row test... Please feel free to use the following. It...

ctally_1E04.sqlpromptsnippet

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets>
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>ctally_1E04</Title>
      <Shortcut>ctally_1E04</Shortcut>
     ...

Thanks Jason,

I do use SQL Prompt (I have become quite embarrassingly dependent on it 😀 )

Thanks Scott also, I'm going to bookmark this whole thread, so I can steal...

• This reply was modified 4 years, 4 months ago by  Jason A. Long.
• This reply was modified 4 years, 4 months ago by  Jason A. Long.

Jason, Jeff I like that 🙂

I need to use tally tables more, ever since I found out about them, but have been dallying with my tallying, as we're getting...

• This reply was modified 4 years, 4 months ago by  Jason A. Long.

This is a solution that Jeff, myself and a few other worked out a few years ago. The idea of using a "date code" and a two seek operations rather...

• This reply was modified 4 years, 4 months ago by  Jason A. Long.

Nope... it is I that have misunderstood.  I got it backwards from what you were looking for.  Still, you might want to look at the DMV first because the...

Wouldn't it be easier to take a snapshot of sys.dm_db_index_usage_stats from one day till the next and do a comparison?  The added benefit here is that it includes indexes...

Yes sir, you have the idea.

Yes, I'm definitely looking for unused (and underused) indexes. I'm also looking at eliminating redundant indexes, indexes that have a first position key that matches...

Nothing in the system views will tell me what specific plans are using which index.

The goal is to get an idea of which stored procedures are likely to regress if/when...

• This reply was modified 4 years, 4 months ago by  Jason A. Long.

In addition to Jeff's good comments, another (and in my own opinion better) solution would be to normalize the table and split that column into a completely different table, with...

Working with the XML outside of SQL Server (or even using a CLR) is little out of scope... at least for my current project.

The fact is that I can use...