CODEWITHSUNDEEP
sqlsql-serverpartitioningdatabase-partitioningdatetime2
Zapnologica
Zapnologica

Reputation: 22556

Searching for a date In a partitioned table in SQL Server

When doing queries on a partitioned table in SQL Server, does one have to do anything special?

The reason I am asking is because we have a fairly large SQL Server table that is partitioned on a `datetime2(2)' column by day.

Each day is mapped to its own file group with a file in that file group named appropriately such as Logs_2014-09-15.ndf.

If I do a query on this table that say, only spans 2 days. I see that in ResourceMonitor that SQL Server is accessing more than 2 of the daily .ndf files. (edit, in fact I have noticed that it goes and searched through every single one. even if i Select from a day that falls in partition1 )

From my understanding with partitioned tables, it should only search amongst the appropriate data /partitions that it needs to?

So my questions:

  1. Is this the case?

  2. does how I compare the DateTime2 column effect the query?

    For example, I could query like so:

    select * from LogsTable
where [date] like '2014-09-15'

    or I could do:

    select * from LogsTable
where [date] = CAST('2014-09-15'AS DATETIME2)

  3. Does the partition function automatically look at the [time] element if it is in the query and then send sql to the correct partition?

Upvotes: 3

Views: 1014

Answers (1)

Mihir Shah
Mihir Shah

Reputation: 988

Have you tried with this:

select * from LogsTable
where Dateadd(D, 0, Datediff(D, 0, [date])) = CAST('2014-09-15'AS DATETIME2)

Upvotes: 1

Related Questions