Another problem I’ve seen frequently is the overuse of user-defined functions (UDFs), especially nesting scalar UDFs.

scalar UDFs, nested or not, tend to burn the house down.  When used in a...

Glen - I love this article. Amazing stuff. I read some of it and skimmed the rest but can't wait until I have time to play with your code a...

Thanks for the replies and sorry for being a Ghost OP.

To me, the above makes sense based on the wording of those metrics and is useful information.  Does the above...

Adi did it like I would. His excellent solution could be simplified as

SELECT t.FamilyID, t.PortionKey, t.GroupID, GeneratorNumber = 
        ...

I would write it using EXISTS instead of INs. It's easier if you use table aliases.

I always use EXISTS instead of IN. Best case - it performs better, worse case...

Note that the "./" in your code is not required based on the XML Context. Also note that specifying that you need the elements' text, via text(), will improve performance.

For...

To better understand the problem and solution have a look at Well Formed XML:

This is my first time messing with JSON in SQL. The BOL entry on this helped me.

First, JSON is cases sensitive so, $.Data is not the same as $.data. Next,...

As Jeff mentioned, CONVERT + 101 gets you the formatting you need:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101);

For the last day of the previous month:

SELECT EOMONTH(DATEADD(MONTH,-1,GETDATE()));

For the...

You can create your own INSTR function leveraging ngrams8k.

CREATE OR ALTER FUNCTION dbo.Instr8K
(
  @string   VARCHAR(8000),
  @search   VARCHAR(100),
  @instance INT
)
/*
Created by...

For cleaner code you can leverage fnTally. Since fnTally leverages TOP, you can exploit row goals as Steve Collins mentioned above.

CREATE OR ALTER FUNCTION dbo.getYears(@year...

I don't have a server handy at the moment but what you are looking for is in the REPORTSERVER.DBO.CATALOG

This has the RDL XML (Report Definitions) which you can query. To...

@ Alan,

I'm honestly a little bit surprised that you're justifying a sort because it has a low row count.  We know how that usually works out.

I was a little rushed...

• This reply was modified 3 years, 3 months ago by  Alan Burstein.

apart from the performance that is likely to be quite bad has errors on its construct

The Construct is a problem but I suspect the performance should be fine, STRING_SPLIT is...

there is currently no way to incrementally populate the table.

...  a [Huge] fact table that gets regenerated nightly

This is a design/process issue more than a technical one. I'll put my...