Both filtered index and statistics suffer of parameterization in the where clause.

In the following query, the optimizer can't predict which filtered statistic should be used (think to stored procs).

declare @lan...

The correct answer is "depend" on numbers of records in the table:

With one or few records the correct answer is "09:00:10", but with many records (in my case 6800) the...

Old "C" language trick.

Dohsan (12/22/2016)

---

Could possibly remove the case

CREATE FUNCTION [dbo].[ShiftCalc]

(

@ShiftTime DATETIME,

@ShiftStart DATETIME,

@NumOfShifts TINYINT

)

RETURNS TABLE WITH SCHEMABINDING

AS

RETURN

SELECT

OnShift = CHAR(ABS(DATEDIFF(DAY,@ShiftStart,DATEADD(HOUR,-DATEPART(HOUR,@ShiftStart), @ShiftTime))) % @NumOfShifts + 65);

GO

DECLARE @ShiftAStart DATETIME = '2006-01-01T07:00:00';

SELECTD.Incident,

CA1.OnShift

FROM(

VALUES(CAST('2006-01-01T07:00:00' AS DATETIME)),

('2006-01-01T17:00:00'),

('2006-01-02T06:00:00'),

('2006-01-02T07:00:00'),

('2006-01-02T23:00:00'),

('2006-01-03T07:00:00'),

('2006-01-03T09:00:00'),

('2006-01-04T07:00:00')

) AS D(Incident)

CROSS

APPLYdbo.ShiftCalc(D.Incident,@ShiftAStart,3) AS...

SET @compareDate = DATEADD(HOUR, -7, @ShiftDate)

SELECT CASE ABS(DATEDIFF(DD, @compareDate, @baseDate)) % 3

WHEN 0 .....

Thanks! I learned a real new and interesting feature!

Any select could be surrounded by parenthesis:

(select * from sys.objects WHERE name like 'sys%')

(select * from sys.indexes WHERE name like 'c%')

I love a lot the inline TVF.

😛

Stewart "Arturius" Campbell (11/14/2016)

---

Interesting question, thanks Junior.

However, as I am sure you are aware, the sequence is important:

if the query is changed toSELECT 0

UNION

SELECT 1

UNION

SELECT NULL

UNION ALL

SELECT NULL

UNION

SELECT 2;, the...

Steve Jones - SSC Editor (11/3/2016)

---

Clearly I edited an answer somewhere, causing issues. It's certainly my fault. Our interface makes checking these items hard, so I have to be extra...

Despite of the confusion, it's a good question.

You're wrong!

CONCATENATE concatenates only two string. CONCATENATE(<text1>,<text2>)

CONCATENATEX concatenates ALL values of a table. See the...

Very interesting!

Thanks!

Very interesting!

Thanks!

🙂

Rune Bivrin (9/23/2016)

---

Good question, but a small nit-pick:

"The syntax of the batch is correct" is not really an output of the batch, but the conclusion of the parser. That message...