Q&A for professional and enthusiast programmers
CREATE TABLE #MyLogs (dtUtcDateTime datetime, HourPart int); CREATE CLUSTERED INDEX cx ON #MyLogs (HourPart, dtUtcDateTime); INSERT INTO #MyLogs (dtUtcDateTime, HourPart) VALUES ('2015-07-31 19:00:06', 19), ('2015-07-31 19:00:07', 19), ('2015-07-31 19:01:27', 19), ('2015-07-31 19:01:29', 19), ('2015-07-31 19:01:50', 19), ('2015-07-31 19:04:17', 19), ('2015-07-31 19:04:42', 19), ('2015-07-31 19:27:48', 19), ('2015-07-31 19:48:17', 19), ('2015-07-31 20:04:43', 20), ('2015-08-01 01:55:11', 1), ('2015-08-01 01:55:13', 1), ('2015-08-01 01:55:21', 1), ('2015-08-01 01:55:23', 1), ('2015-08-01 01:55:25', 1), ('2015-08-01 01:55:29', 1), ('2015-08-01 01:55:42', 1), ('2015-08-01 01:55:44', 1), ('2015-08-01 01:55:45', 1), ('2015-08-01 01:56:26', 1), ('2015-08-01 01:56:27', 1), ('2015-08-01 01:56:31', 1), ('2015-08-01 01:56:32', 1), ('2015-08-01 02:06:32', 2), ('2015-08-01 02:10:49', 2), ('2015-08-01 02:14:36', 2), ('2015-08-01 02:15:01', 2), ('2015-08-01 02:15:10', 2), ('2015-08-01 02:15:11', 2), ('2015-08-01 02:15:16', 2), ('2015-08-01 02:15:20', 2), ('2015-08-01 03:11:40', 3), ('2015-08-01 03:11:38', 3), ('2015-08-01 03:14:07', 3), ('2015-08-01 03:14:20', 3), ('2015-08-01 03:14:12', 3), ('2015-08-01 03:14:17', 3), ('2015-08-01 03:14:51', 3), ('2015-08-01 03:15:04', 3), ('2015-08-01 03:15:06', 3), ('2015-08-01 03:15:10', 3), ('2015-08-01 03:15:11', 3), ('2015-08-01 03:15:16', 3), ('2015-08-01 03:15:24', 3), ('2015-08-01 03:15:25', 3), ('2015-08-01 03:15:29', 3), ('2015-08-01 03:15:41', 3), ('2015-08-01 03:15:43', 3), ('2015-08-01 03:17:24', 3), ('2015-08-01 03:17:25', 3), ('2015-08-01 03:17:28', 3), ('2015-08-01 03:17:49', 3), ('2015-08-01 03:17:45', 3), ('2015-08-01 03:19:30', 3), ('2015-08-01 03:20:03', 3), ('2015-08-01 03:22:12', 3), ('2015-08-01 03:23:13', 3), ('2015-08-01 03:23:16', 3), ('2015-08-01 03:24:46', 3), ('2015-08-01 03:26:36', 3), ('2015-08-01 03:26:35', 3), ('2015-08-01 03:26:49', 3), ('2015-08-01 03:26:52', 3), ('2015-08-01 03:26:57', 3), ('2015-08-01 03:27:14', 3), ('2015-08-01 03:27:15', 3), ('2015-08-01 03:27:31', 3), ('2015-08-01 03:27:30', 3), ('2015-08-01 03:28:04', 3), ('2015-08-01 03:29:16', 3), ('2015-08-01 03:29:32', 3), ('2015-08-01 03:29:30', 3), ('2015-08-01 03:29:43', 3), ('2015-08-01 03:29:56', 3), ('2015-08-01 03:30:06', 3), ('2015-08-01 03:30:25', 3), ('2015-08-01 03:30:26', 3), ('2015-08-01 03:30:27', 3), ('2015-08-01 03:32:21', 3), ('2015-08-01 03:34:07', 3), ('2015-08-01 03:35:01', 3), ('2015-08-01 03:35:02', 3), ('2015-08-01 03:35:04', 3), ('2015-08-01 03:35:41', 3), ('2015-08-01 03:35:48', 3), ('2015-08-01 03:36:03', 3), ('2015-08-01 03:36:02', 3), ('2015-08-01 03:36:08', 3), ('2015-08-01 03:36:09', 3), ('2015-08-01 03:36:24', 3), ('2015-08-01 03:36:33', 3), ('2015-08-01 04:30:06', 4), ('2015-08-01 04:36:33', 4), ('2015-08-01 06:03:12', 6), ('2015-08-01 06:03:24', 6), ('2015-08-01 06:03:26', 6), ('2015-08-01 06:03:29', 6), ('2015-08-01 06:03:30', 6), ('2015-08-01 06:04:16', 6), ('2015-08-01 06:25:25', 6), ('2015-08-01 06:25:33', 6), ('2015-08-01 06:26:31', 6), ('2015-08-01 06:26:42', 6), ('2015-08-01 06:26:47', 6), ('2015-08-01 06:26:49', 6), ('2015-08-01 06:26:53', 6), ('2015-08-01 06:28:10', 6), ('2015-08-01 06:28:11', 6), ('2015-08-01 06:28:12', 6), ('2015-08-01 06:31:26', 6), ('2015-08-01 06:32:02', 6), ('2015-08-01 06:32:03', 6), ('2015-08-01 06:32:08', 6), ('2015-08-01 06:32:39', 6), ('2015-08-01 06:32:42', 6), ('2015-08-01 06:32:47', 6), ('2015-08-01 06:33:55', 6), ('2015-08-01 06:37:11', 6), ('2015-08-01 06:38:11', 6), ('2015-08-01 06:38:21', 6), ('2015-08-01 06:38:32', 6), ('2015-08-01 06:38:31', 6), ('2015-08-01 06:38:38', 6), ('2015-08-01 06:38:39', 6), ('2015-08-01 06:38:40', 6), ('2015-08-01 06:38:43', 6), ('2015-08-01 06:38:59', 6), ('2015-08-01 06:39:40', 6), ('2015-08-01 06:41:19', 6), ('2015-08-01 06:41:23', 6), ('2015-08-01 06:41:33', 6), ('2015-08-01 06:41:35', 6), ('2015-08-01 06:41:38', 6), ('2015-08-01 06:42:53', 6), ('2015-08-01 06:44:15', 6), ('2015-08-01 06:44:47', 6), ('2015-08-01 06:45:18', 6), ('2015-08-01 06:45:22', 6), ('2015-08-01 06:46:15', 6), ('2015-08-01 06:46:16', 6), ('2015-08-01 06:49:28', 6), ('2015-08-01 06:50:26', 6), ('2015-08-01 06:51:08', 6), ('2015-08-01 06:55:17', 6), ('2015-08-01 06:58:38', 6), ('2015-08-01 06:58:46', 6), ('2015-08-01 06:59:18', 6), ('2015-08-01 07:01:16', 7), ('2015-08-01 07:01:20', 7), ('2015-08-01 07:01:24', 7), ('2015-08-01 07:01:59', 7), ('2015-08-01 07:02:02', 7), ('2015-08-01 07:02:11', 7), ('2015-08-01 07:02:13', 7), ('2015-08-01 07:03:55', 7), ('2015-08-01 07:04:16', 7), ('2015-08-01 07:04:26', 7), ('2015-08-01 07:04:27', 7), ('2015-08-01 07:04:28', 7), ('2015-08-01 07:05:11', 7), ('2015-08-01 07:05:13', 7), ('2015-08-01 07:06:31', 7), ('2015-08-01 07:06:32', 7), ('2015-08-01 07:07:21', 7), ('2015-08-01 07:07:39', 7), ('2015-08-01 07:07:32', 7), ('2015-08-01 07:07:33', 7), ('2015-08-01 07:07:36', 7), ('2015-08-01 07:08:51', 7), ('2015-08-01 07:08:52', 7), ('2015-08-01 07:10:03', 7), ('2015-08-01 07:11:04', 7), ('2015-08-01 07:11:05', 7), ('2015-08-01 07:11:11', 7), ('2015-08-01 07:11:17', 7), ('2015-08-01 07:11:35', 7), ('2015-08-01 07:12:15', 7), ('2015-08-01 07:14:31', 7), ('2015-08-01 07:14:43', 7), ('2015-08-01 07:15:29', 7), ('2015-08-01 07:16:05', 7), ('2015-08-01 07:16:07', 7), ('2015-08-01 07:17:25', 7), ('2015-08-01 07:17:39', 7), ('2015-08-01 07:19:13', 7), ('2015-08-01 07:27:46', 7), ('2015-08-01 07:28:04', 7), ('2015-08-01 07:28:10', 7), ('2015-08-01 07:28:12', 7), ('2015-08-01 07:29:17', 7), ('2015-08-01 07:31:36', 7), ('2015-08-01 07:31:45', 7), ('2015-08-01 07:31:48', 7), ('2015-08-01 07:31:52', 7), ('2015-08-01 07:31:54', 7), ('2015-08-01 07:31:55', 7), ('2015-08-01 07:32:01', 7), ('2015-08-01 07:32:08', 7), ('2015-08-01 07:32:12', 7), ('2015-08-01 07:33:00', 7); -- Drop the clustered index (HourPart dependency) DROP INDEX cx ON #MyLogs; -- Drop the column ALTER TABLE #MyLogs DROP COLUMN HourPart; -- Replace with a computed column ALTER TABLE #MyLogs ADD HourPart AS DATEPART(HOUR, dtUtcDateTime); -- Nonclustered index, for variety CREATE NONCLUSTERED INDEX nc1 ON #MyLogs (HourPart) INCLUDE (dtUtcDateTime); DECLARE @StartHour integer = 1, @EndHour integer = 5; SELECT * FROM #MyLogs AS ML WHERE @StartHour < @EndHour AND ML.HourPart >= @StartHour AND ML.HourPart <= @EndHour UNION ALL SELECT * FROM #MyLogs AS ML WHERE @StartHour > @EndHour AND ML.HourPart >= @StartHour UNION ALL SELECT * FROM #MyLogs AS ML WHERE @StartHour > @EndHour AND ML.HourPart <= @EndHour;