Here is another option

DECLARE @val1 nvarchar(40) = 'Hello';
DECLARE @val2 nvarchar(40) = 'Treacle';
DECLARE @val3 nvarchar(40) = NULL;
DECLARE @val4 nvarchar(40) = 'Rhubarb';

DECLARE @nth int = 2;

WITH cteNonNullData AS (
...

This sounds very much like a homework or interview question.

I would suggest researching the TOP key word

Your problem is the "," before the "ORDER BY" in your "OVER" clauses

WITH T1 (ID1, ID2)
AS
(
SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,2
)
SELECT *,
RANK()...

You could also do it without the CTE

SELECT Q.Quater as 'Quarter', Q.Year as 'Year'
FROM Quaters as Q
WHERE Q.Year   = DATEPART(YEAR, GETDATE())
  AND Q.Quater =...

Depending on your indexes, this may help

; WITH CTE AS
(
SELECT DATEPART(QUARTER, GETDATE()) as 'Quarter', DATEPART(YEAR, GETDATE()) as 'Year'
)
SELECT CTE.Quarter, CTE.Year
FROM CTE
INNER JOIN Quaters as Q
  ...

I suppose that if you don't want the LOB data in the temporal table, you could move all LOB data to an extension table with the same PK as the...

The Above sample one works fine as it is. When I include it in my SP. It throws the below error.

The conversion of a nvarchar data type to a...

In most cases, performance issues are in the actual code.  If you post the ACTUAL execution plan, we might be able to see where the pain points are.

I'm not a DBA, but there are a few alarm bells right off the bat.

• C drive has 4.99GB free out of 49.99 GB - Windows tends to get rather...

Hi Jeff,

Hi took a four column data set containing waiting times measurements with 1,006,934 rows and then used the median function to calculate the median wait.  The query execution...

You haven't give us the table structure as requested.  Also your examples are not readily consumable sample data.

So, maybe this guess about your data will help

CREATE TABLE...

You cannot pass a #Table as a param, but what you can do is declare a #Table in proc 1 and then call proc2 from proc 1, and it will...

If you are stuck with the delimited strings, I would start bu using one of the locally produced high-speed string splitters

/****************************************************************************************************************
*** SOURCE:  http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/
***   ...

You can turn the code into a function

CREATE FUNCTION dbo.itvfGetDays(@TheDate date)
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN SELECT TheDay  = CASE DATEDIFF(dd, 0, @TheDate) %7
  ...

Something like this

DECLARE @TheDate date = '2019-12-17';

SELECT TheDay = CASE DATEDIFF(dd, 0, @TheDate) %7
             ...

• This reply was modified 6 years ago by DesNorton. Reason: Fixed the offset for Day1