search

erikdarlingdata / DarlingData

Open source SQL Server nonsense: sp_PressureDetector, sp_QuickieStore, sp_HumanEvents, etc.
https://www.erikdarling.com/
MIT License
487 stars 137 forks source link

(sp_IndexCleanup) Feature Suggestion: Normalize filtered index bodies #490

Open chadbaldwin opened 1 week ago

chadbaldwin commented 1 week ago

Saw your LinkedIn post. I will likely have more suggestions in the future as I've been working on this exact same script. No one know who I am, so maybe I'll create some suggestions to add features I've added to mine since yours will likely get more people using it 😄

I'm curious to see what you think about this feature/capability.

Here's how I've implemented it in my script: https://github.com/chadbaldwin/SQL/blob/2f6d41fd8dc121fa340c17e51cf2b48815889647/Scripts/Find%20Overlapping%20Indexes.sql#L85-L110

Is your feature request related to a problem? Please describe. I regularly run into filtered indexes where the filter is similar but not the same. For example:

IndexA: WHERE ([ColA] = 1 AND [ColB] > 2)
IndexB: WHERE ([ColB] > 2 AND [ColA] = 1)

Those are the "same" but would not be seen as a duplicate.

Describe the solution you'd like Normalize the definition so that they are comparable even when ordered differently.

Are you ready to build the code for the feature? I'm happy to see if I can work the feature in...but I'm more interested in your insight of the capability itself. After that I'm happy to submit the PR.

chadbaldwin commented 1 week ago

Obviously there is a bit of a gotcha here...if you have a column that contains [sp]AND[sp], it will incorrectly split it...And I would not at all be surprised if someone has a column named...[foo and bar].

erikdarlingdata commented 1 week ago

@chadbaldwin perhaps it would work better to normalize the column names and expressions first. I can't think of an awesome way to do that off the top of my head.

My larger concern is using the build in string split function. I think I have references to it in here where I was just a bit lazy, but it only being available in compat level 130+ is a show stopped for me to release generally.

Anyway, I'd be happy for any input, feedback, or contributions you might have. You do some neat stuff.

chadbaldwin commented 1 week ago

Yeah, that's fair. The STRING_SPLIT stuff could easily be changed to use the old school XML method. I'll play around with the idea testing some edge cases like columns with weird names and see if I can come up with a better backward compatible version with less gotchas.