I can't give you an answer to question 1).
But to answer question 2):
What you are requesting is known as the "sliding window scenario".
If you want to eliminate a partition containing data that is no longer needed you have to perform following steps:
- Create a table on the same filegroup as the partition you want to delete (typically, this table is referred to as a staging table).
This table has to have exact the same structure as the partitioned table, including indexes.
- Switch the partition to the staging table
- You have to remove the reference to the filegroup from the partition function vie
MERGE RANGE ...
- Now you can truncate the staging table and drop it. What is left is an empty filegroup you can remove, too.
Some more details on this at http://technet.microsoft.com/en-us/library/aa964122.aspx and the white paper on partitioning, available under http://msdn.microsoft.com/en-us/library/dd578580%28v=sql.100%29.aspx.
In order for the table to be partitioned, your clustered index should be created as:-
ALTER TABLE [dbo].[Logs] ADD CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED
(
[logId] ASC,
[time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PS_dbo_Date_ByDay](Time)
You can then view the different partitions by running:-
SELECT
t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id,
i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue, p.rows
FROM
sys.tables AS t
INNER JOIN
sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN
sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
INNER JOIN
sys.partition_functions AS f ON s.function_id = f.function_id
LEFT OUTER JOIN
sys.partition_range_values AS r ON f.function_id = r.function_id AND r.boundary_id = p.partition_number
WHERE
t.name = 'Logs'
AND
i.type <= 1
ORDER BY p.partition_number;
The table is essentially carved up into different sections this will allow queries to reference the different sections without blocking each other (try inserting records into two different partitions at the same time, then try it on a non-partitioned table).
This will be the same for an ALIGNED index. Create it on the paritition scheme (the same as the clustered index) and it will also be carved up (the SELECT above will show you).
However you can create a NON-ALIGNED index, just don't add the ON PS_dbo_Date_ByDay, use PRIMARY or another filegroup.
Hope this helps.
EDIT:- Your primary key contains a column (LogID) that is not part of the partition. The table will be partitioned but there could be a performance impact. I asked a similar question on here a while ago, here's the link:-
Partitioning Query
Best Answer
Partitioning a table with all of the partitions on the same filegroup gives you the following benefits:
These benefits are also present when putting partitions on different filegroups, except when noted otherwise.
One reason to put partitions on different filegroups is when you want to put older data on a filegroup that's mapped to a slower tier of storage.