Sql-server – Get minute part from time data type

Sounds like what you need is sparse columns and filtered indexes and go with option 1. These are fully supported and documented features for exactly this scenario.

The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row in the table, the value requires no storage.

I can't imagine an XML solution performing well in this scenario, it will have a huge overhead of redundant metadata and will be slow to query.

You can't do what Microsoft does with built-ins, sorry. You will need to write your function to accept a string, for example, and evaluate it inline, e.g.

CREATE FUNCTION dbo.CustomDateDiff
(
  @datepart VARCHAR(32), -- does not need to be NVARCHAR
  @start    DATETIME,
  @end      DATETIME
)
RETURNS INT
AS
BEGIN
    RETURN (SELECT CASE @datepart 
      WHEN 'HOUR'   THEN DATEDIFF(HOUR,   @start, @end) 
      WHEN 'MINUTE' THEN DATEDIFF(MINUTE, @start, @end)
      WHEN 'WEEK'   THEN DATEDIFF(WEEK,   @start, @end)
      ELSE               DATEDIFF(DAY,    @start, @end)
    END);
END
GO

Not that you can't use a conditional inside DATEDIFF - a lot of people think you could say:

DATEDIFF(@datepart, 

Or

DATEDIFF(CASE WHEN @datepart = 'HOUR' THEN HOUR END,

But neither of these will work - both yield:

Msg 1023, Level 15, State 1, Line 2
Invalid parameter 1 specified for datediff.