How to improve performance for datetime filtering in SQL Server?

by

Just a suggestion when it comes to indexes on datetime in msql is the index footprint impacts search times (Yes this seems obvious…but please read onward).

The importances to this when indexing on the datetime say for instance ‘2015-06-05 22:47:20.102’ the index has to account for every place within the datetime. This becomes very large spatially and bulky. A successful approach that I’ve leveraged is create a new datetime column and populate the data by rounding the time to the hour and then building the index upon this new column. Example ‘2015-06-05 22:47:20.102’ translates to ‘2015-06-05 22:00:00.000’. By taking this approach we leave the detailed data alone and can display it or use it by search on this new column which gives us approximately a 10x (at minimum) return on how fast results are returned. This is due to the fact that the index doesn’t have to account for the minutes, seconds and millisecond fields.

More Related Contents:

  1. Solutions for INSERT OR UPDATE on SQL Server
  2. What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?
  3. Conditional WHERE clause in SQL Server
  4. How to Join to first row
  5. Concatenate row values T-SQL
  6. GROUP BY to combine/concat a column [duplicate]
  7. How to return multiple values in one column (T-SQL)?
  8. Difference between “read commited” and “repeatable read”
  9. Reference alias (calculated in SELECT) in WHERE clause
  10. Implement paging (skip / take) functionality with this query
  11. Copy data into another table
  12. When to use Common Table Expression (CTE)
  13. How to replace a string in a SQL Server Table Column
  14. How to send email from SQL Server?
  15. SQL server ignore case in a where expression
  16. How To Create Table with Identity Column
  17. Find closest date in SQL Server
  18. SQL Server LIKE containing bracket characters
  19. Can we pass parameters to a view in SQL?
  20. How to split string using delimiter char using T-SQL?
  21. Alternate of lead lag function in SQL Server 2008
  22. “Order By” using a parameter for the column name
  23. DISTINCT for only one column
  24. How do I create and query linked database servers in SQL Server?
  25. Add emoji / emoticon to SQL Server table
  26. Perform regex (replace) in an SQL query
  27. What is the internal representation of datetime in sql server?
  28. Eliminate and reduce overlapping date ranges
  29. SQL SERVER DATETIME FORMAT
  30. Insert all values of a table into another table in SQL

Leave a Comment