Sql-server – ny significant difference between Month(date) and DatePart(month, date)

I would want to attempt to insert a row for every hit and use SQL partitioning to store the table in managable chronological slices (by week or by month). This way, by keeping the original timestamp for each hit, you'll be able to aggregate the data using different methods as needed (by year one day, by hour the next, etc). For doing the aggregation, I would build a SSAS cube or Excel PivotTable, or T-SQL grouping functions. By using paritioning, you can store data for different time periods into individual filegroups spread across different disks to maintain scalability. When it comes to archival, you can perform what's called a sliding window to detach and later drop old unneeded time periods of data without expensive DELETE operations.

The answer is - you have to test it to find out.
I did a test of my own on a table which has ~8,000,000 rows

DECLARE @date DATETIME
SET @date = GETDATE()
;
SELECT T.DateCol, DATEADD(dd,-100,@date)
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,@date)
;
SELECT T.DateCol, DATEADD(dd,-100,GETDATE())
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,GETDATE())
;

In my case the use of the variable caused SQL Server to estimate an expensive plan:

the reason is that SQL Server builds the query plan during compilation,
at that time the value of the variable is not yet known and SQL Server can not use the statistics.
I suggest you read about parameterization

There are other cases where the use of the variable will cost less then calling a function many times,
so here it is again - TEST TEST, and TEST :)