I found some code someone else has written to...

Yes, it can retrieve partially updated records and if a page-split is happening it can return duplicate rows.

Best to only be used on data that is not being updated and...

It makes no difference.

That's pretty interesting. Looks somewhat similar to Eirikur's but a few differences. The one thing I am not a fan of is that it has the same major miss...

You can rewrite the SQL as this:

IF EXISTS(SELECT *
            FROM #GRatings
     ...

Just break up the statement so you are adding to @SQLSTRING several times.

SET @SQLSTRING = N';WITH CTE AS ' +
       ...

You could try this STRING_SPLIT function I'm in the process of writing and give me some feedback.

It's like SQL Server's STRING_SPLIT function but will work with SQL 2012 and...

I wouldn't increase the amount of memory you give to SQL Server, 29GB out of 32GB is already quite high. You need to leave enough memory for the operating system...

Your query would be more efficient if you used a cursor to select distinct ref_nr_vertrag from table tbl_111_aed2_umhaengen_Verträge. Like this:

DECLARE @Vertrag AS int= 1
DECLARE @myCursor cursor
SET @myCursor...

If you have an index on the table:

DECLARE @Planning_Proposal_Temp TABLE (object varchar(10), dateFrom datetime2, dateTo datetime2
           ...

• This reply was modified 5 years, 10 months ago by Jonathan AC Roberts.

I think it can be done with a recursive CTE:

;WITH rCTE AS
(
    SELECT a.object,
          ...

I'm not sure what you are trying to do, but COUNT(MyDateField) will count the not null MyDateField values.

Or are you trying to do do something else like COUNT(DISTINCT CONVERT(varchar, MyDateField, 112))...

Another method:

;WITH CTE AS 
(
    SELECT t.VisitID, 
           MAX(t.ActivityDateTime) MaxActivityDateTime
    ...

Here's a simple method to do that:

SELECT year1a.get_customeVal_1-year1b.get_customeVal_2 AS get_customeVal_3,
       year2a.get_customeVal_1-year2b.get_customeVal_2 AS get_customeVal_4
  FROM (SELECT 1 X) X
 CROSS APPLY(SELECT...

Have you tried setting the fetchsize to the number of rows you are expecting from the query?