June 26, 2013 at 12:40 am
Hi All,
I have one table with 10 lacks records. I partitioned that table on CreatedDate column with non clustered index
( i am not removing clustered index on ID column, It is as part of primary key).
It is inserting to data into relevant partition only. But i am verifying is that table partitioned or not by using below steps, in object Explorer-Database-->TestDB-->tables-->select partitioned table and Right click on table select properties --Storage
File Group= Primary
Table Partitioned = False
If create Partitioned with Clustered index , it is showing correctly Table Partitioned = True But i am creating with non clustered.
Can any one explain
Is it table partitioned or not? and how to know data is coming from which partition (with out using ($partition)
below are example table partition script which i followed steps for original table.
CREATE TABLE tblPartition(ID int primary key identity(1,1),Name varchar(30),CreatedDate Datetime)
insert into tblPartition(Name,CreatedDate)
SELECT 'Name1','2013-05-26 13:53:47.650'
union all
select 'Name2','2013-05-26 13:53:47.650'
union all
SELECT 'Name1','2013-06-26 13:53:47.650'
union all
select 'Name2','2013-06-26 13:53:47.650'
union all
SELECT 'Name1','2013-07-26 13:53:47.650'
union all
select 'Name2','2013-07-26 13:53:47.650'
go
CREATE PARTITION FUNCTION [PartitionFunction](datetime) AS RANGE RIGHT FOR VALUES (N'2013-05-31 23:59:59', N'2013-06-30 23:59:59', N'2013-07-31 23:59:59')
CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunction] TO ([FGNdf10], [FGNdf11], [FGNdf12], [PRIMARY])
CREATE NONCLUSTERED INDEX [IX_PartitionScheme_CreatedDate] ON [dbo].[tblPartition]
(
[CreatedDate]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartitionScheme]([CreatedDate])
go
select * from tblPartition
where $partition.PartitionFunction(CreatedDate)=1
go
Thanks,
PRR
June 26, 2013 at 4:55 am
Hi All,
Can any one explain on it?
June 28, 2013 at 10:55 am
hi,
it's a little bit hard to understand you're question but I think I got it.
Let's have a look at your CREATE TABLE statement. You did not add the information on which filegroup you create the table. Have a look at books online, and you will find that, if not defined, the table is created on the filegroup PRIMARY.
So I guess your table is NOT partitioned. You will have to rebuild the table or the clustered index respectively.
Independent of the table itself, you can create a nonclustered index. As for the table you have to define the filegroup for the index. If not defined it is PRIMARY, or even the filegroup of the clustered index I think (look at books online).
As you DID define the filegroup, in this case the partition scheme, it means that the index is actually partitioned.
The following select might help you:
select
ObjectName = object_schema_name( i.object_id ) + '.' + object_name( i.object_id )
, i.index_id
, i.name
, part.partition_number
, DataSpaceType = spaces.type_desc
, DestinatonDataSpaceName = spaces.name
, PartitionKey = ranges.value
, FileGroupName = fgroup.name
, [FileName] = files.name
, files.physical_name
from
sys.indexes i
inner join
sys.objects obj on
obj.object_id = i.object_id and
obj.type = 'U'
left outer join
sys.partition_schemes schemes on
schemes.data_space_id = i.data_space_id
left outer join
sys.partitions part on
part.object_id = i.object_id and
part.index_id = i.index_id
left outer join
sys.partition_functions func on
func.function_id = schemes.function_id
left outer join
sys.partition_range_values ranges on
ranges.function_id = func.function_id and
ranges.boundary_id = part.partition_number - func.boundary_value_on_right
left outer join
sys.destination_data_spaces dest on
dest.partition_scheme_id = schemes.data_space_id and
dest.destination_id = part.partition_number
left outer join
sys.data_spaces spaces on
spaces.data_space_id = i.data_space_id
left outer join
sys.filegroups fgroup on
fgroup.data_space_id = dest.data_space_id or
fgroup.data_space_id = i.data_space_id
left outer join
sys.database_files files on
files.data_space_id = fgroup.data_space_id
order by
object_schema_name( i.object_id ) + '.' + object_name( i.object_id ),
i.index_id,
part.partition_number
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply