Difference between select from a whole partition or from table with small dataset?

I am new to partitioning.

Would be there a difference in performance between

select * from my_partitionedData where date = '201105开发者_如何学JAVA23' 

and

select * from my_Data where date = '20110523' 

where my_partitionedData is a table partitioned by date by 1 day and my_Data is a table which has only data for '20110523' and both tables have same structure?

The other question - would be there a difference in performance in running these selects if all the partitions of the my_partitionedData are in the same file group? (note - the select is always for 1 day)

Like everything else in SQL, you will need to test to be sure.

That being said, I think you should get identical performance.

Behind the scenes, a partitioned table is basically a lot of smaller tables logically unioned together. If you are partitioning by day in you partitioned table, and your non-part table has only one day of data, the execution plan and performance should be pretty much identical.

If one returns the same data set a partitioned and non-partitioned table will return the data with the same IO. If the partitioned table has less fragmentation there would be a reduction in the IO delay from a random seek of the disk heads to retrieve the pages but all in all 100k of data is 100k of data.

You did not mention if you were considering partitioning the index. Partitioning index is an excellent way to reduce the number of levels which must be traversed to find the location of the data row. Partitioning indexes and tables with the same function is the optiomal solution.

where my_partitionedData is a table partitioned by date by 1 day and my_Data is a table which has only data for '20110523' and both tables have same structure?

The later one will less access time.

The other question - whould be there a difference in performance in running these selects if all the partitions of the my_partitionedData are in the same file group? (note - the select is always for 1 day)

The access time will be more in this case despite of 1 day data.

Partitioning is required to improve the scalability and manageability of large tables and tables that have varying access patterns.

You created two tables to store information about each day records and on the other hand a single table for each day data is the easiest to design and understand, but these tables are not necessarily optimized for performance, scalability, and manageability, particularly as the table grows larger.