Uncomment the query you want to try:
WITH Fullname
AS
(
SELECT * FROM (VALUES
('Erin','Keri','Moody','Sr.')
,('Megan','Laura','Morales','B.S.')
,('George','Lena',NULL,'')
,(NULL,'Ryan','Lucas','M.D.')
,('Sheryl','Marianne','Morton','IV')
) AS V([firstname],[middlename],[lastname],[suffix])
)
--SELECT COALESCE
--        (
--        firstname,...

The return type depends on "precedence" of types. In this case, the...

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)

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)

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...