June 4, 2012 at 9:54 am
partition_idobject_idindex_idpartition_numberhobt_idrowsfilestream_filegroup_iddata_compressiondata_compression_desc
720575940391731202455759131172057594039173120100000000NONE
72057594039238656 2455759131272057594039238656341793100NONE
select count(*) from TestTable partition - this works fine
select count(*) from TestTable partition (TestTable_primary_TestDB_Part1) - but this does not - why?
X:\partition\primary\TestDB_Part1 - this is one of my partitions.
I found an article that says this works: "select count(*) from SALES partition (SALES_JAN2000)"
so i tried:
select count(*) from TestTable partition (TestTable_TestDB_Part1)
Thanks
June 4, 2012 at 10:11 am
What exactly are you trying to do? Count the number of rows contained in a specific partition?
Jared
CE - Microsoft
June 4, 2012 at 10:14 am
That's not valid T-SQL syntax, it's not going to work.
The article that you found was for Oracle.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2012 at 10:18 am
Here is a script that I use, though I can't find the reference to it so I cannot give credit. However, I did not write it:
select
obj.name as table_name,
--ps.name as ps_name,
--'$partition.'+pf.name+'('+c.name+')' as pafu,
--pf.type as pf_type,
--c.name as pa_col_name,
--case
-- when prng_down.value is null then ''
-- else
-- case
-- when pf.type = 'R' then c.name+' >= '''+ convert( varchar(50),prng_down.value , 20)+''' '
-- when pf.type = 'L' then c.name+' > '''+ convert( varchar(50),prng_down.value , 20)+''' '
-- end
--end as lower_check,
case
when prng_down.value is null then ''
else
case
when pf.type = 'R' then convert( varchar(50),prng_down.value , 20)
when pf.type = 'L' then convert( varchar(50),prng_down.value , 20)
end
end as lower_date,
--case
-- when prng_up.value is null then ''
-- else
-- case
-- when pf.type = 'R' then c.name+' < '''+ convert( varchar(50),prng_up.value , 20)+''' '
-- when pf.type = 'L' then c.name+' <= '''+ convert( varchar(50),prng_up.value , 20)+''' '
-- end
--end as upper_check,
case
when prng_up.value is null then ''
else
case
when pf.type = 'R' then convert( varchar(50),prng_up.value , 20)
when pf.type = 'L' then convert( varchar(50),prng_up.value , 20)
end
end as upper_date,
pa.partition_number,
pa.rows,
ds.name as fg_name,
part_size_Gb,
reserved_size_Mb,
used_size_Mb,
i.index_id
from sys.indexes i
left join sys.index_columns ic
ON (ic.partition_ordinal >= 0) -- >0 shows only partitioned tables
AND (ic.index_id = i.index_id AND ic.object_id = CAST(i.object_id AS INT))
left JOIN sys.columns c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
inner join sys.objects obj
on i.object_id = obj.object_id
inner join sys.partitions pa
on pa.object_id = i.object_id
and pa.index_id = i.index_id
left JOIN sys.partition_schemes ps
ON i.data_space_id=ps.data_space_id
left join sys.partition_functions pf
on ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values prng_up
ON prng_up.function_id=pf.function_id
and prng_up.boundary_id = pa.partition_number
left join
(select OBJECT_ID, index_id, partition_number, CAST(reserved_page_count * (0.0078125/1024.00) AS NUMERIC(18,2)) AS part_size_Gb,
CAST(reserved_page_count * (0.0078125) AS NUMERIC(18,2)) AS reserved_size_Mb ,
CAST(used_page_count * (0.0078125) AS NUMERIC(18,2)) AS used_size_Mb
from sys.dm_db_partition_stats) phsta
on pa.object_id = phsta.object_id
and pa.index_id = phsta.index_id
and pa.partition_number = phsta.partition_number
left join sys.partition_range_values prng_down
ON prng_down.function_id=pf.function_id
AND prng_down.boundary_id = pa.partition_number-1
left join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
and dds.destination_id=pa.partition_number
left join sys.data_spaces ds
on isnull(dds.data_space_id,i.data_space_id) = ds.data_space_id
where 1=1
and obj.type = 'U'
and
(ps.name is null
and (i.index_id = 0 and ic.partition_ordinal is null or i.index_id > 0 and ic.index_column_id = 1)
or ps.name is not null and ic.partition_ordinal = 1)
Jared
CE - Microsoft
June 4, 2012 at 10:41 am
SQLKnowItAll (6/4/2012)
What exactly are you trying to do? Count the number of rows contained in a specific partition?
yes.
I have created two partitions, one with 1000000 records and one with 3000000+ records, so i am trying to start with a count , then select specific columns based on where criteria.
I cant get even get the select count to work!
June 4, 2012 at 10:45 am
The best way is to query the known partition by restricting it. The database engine will take care of knowing where to look. So If mine is partitioned on month, I will query like this:
SELECT COUNT(*)
FROM partitionedtable
WHERE partitioncolumn >= '2012-05-01' AND partitioncolumn < '2012-06-01'
Jared
CE - Microsoft
June 4, 2012 at 10:45 am
SQLKnowItAll (6/4/2012)
Here is a script that I use, though I can't find the reference to it so I cannot give credit. However, I did not write it:
selectobj.name as table_name,
--ps.name as ps_name,
--'$partition.'+pf.name+'('+c.name+')' as pafu,
--pf.type as pf_type,
--c.name as pa_col_name,
--case
-- when prng_down.value is null then ''
-- else
-- case
-- when pf.type = 'R' then c.name+' >= '''+ convert( varchar(50),prng_down.value , 20)+''' '
-- when pf.type = 'L' then c.name+' > '''+ convert( varchar(50),prng_down.value , 20)+''' '
-- end
--end as lower_check,
case
when prng_down.value is null then ''
else
case
when pf.type = 'R' then convert( varchar(50),prng_down.value , 20)
when pf.type = 'L' then convert( varchar(50),prng_down.value , 20)
end
end as lower_date,
--case
-- when prng_up.value is null then ''
-- else
-- case
-- when pf.type = 'R' then c.name+' < '''+ convert( varchar(50),prng_up.value , 20)+''' '
-- when pf.type = 'L' then c.name+' <= '''+ convert( varchar(50),prng_up.value , 20)+''' '
-- end
--end as upper_check,
case
when prng_up.value is null then ''
else
case
when pf.type = 'R' then convert( varchar(50),prng_up.value , 20)
when pf.type = 'L' then convert( varchar(50),prng_up.value , 20)
end
end as upper_date,
pa.partition_number,
pa.rows,
ds.name as fg_name,
part_size_Gb,
reserved_size_Mb,
used_size_Mb,
i.index_id
from sys.indexes i
left join sys.index_columns ic
ON (ic.partition_ordinal >= 0) -- >0 shows only partitioned tables
AND (ic.index_id = i.index_id AND ic.object_id = CAST(i.object_id AS INT))
left JOIN sys.columns c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
inner join sys.objects obj
on i.object_id = obj.object_id
inner join sys.partitions pa
on pa.object_id = i.object_id
and pa.index_id = i.index_id
left JOIN sys.partition_schemes ps
ON i.data_space_id=ps.data_space_id
left join sys.partition_functions pf
on ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values prng_up
ON prng_up.function_id=pf.function_id
and prng_up.boundary_id = pa.partition_number
left join
(select OBJECT_ID, index_id, partition_number, CAST(reserved_page_count * (0.0078125/1024.00) AS NUMERIC(18,2)) AS part_size_Gb,
CAST(reserved_page_count * (0.0078125) AS NUMERIC(18,2)) AS reserved_size_Mb ,
CAST(used_page_count * (0.0078125) AS NUMERIC(18,2)) AS used_size_Mb
from sys.dm_db_partition_stats) phsta
on pa.object_id = phsta.object_id
and pa.index_id = phsta.index_id
and pa.partition_number = phsta.partition_number
left join sys.partition_range_values prng_down
ON prng_down.function_id=pf.function_id
AND prng_down.boundary_id = pa.partition_number-1
left join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
and dds.destination_id=pa.partition_number
left join sys.data_spaces ds
on isnull(dds.data_space_id,i.data_space_id) = ds.data_space_id
where 1=1
and obj.type = 'U'
and
(ps.name is null
and (i.index_id = 0 and ic.partition_ordinal is null or i.index_id > 0 and ic.index_column_id = 1)
or ps.name is not null and ic.partition_ordinal = 1)
Thanks for the script, not how do i access the columns?
I have two columns, ID and DocContent.
How would i structure my Select statement to display records in the first partition(up to 1000000 records) with an ID < 100?
Thanks
June 4, 2012 at 10:47 am
I have two columns, ID and DocContent.
How would i structure my Select statement to display records in the first partition(up to 1000000 records) with an ID < 100?
SELECT ID, DocContent
FROM table
WHERE ID < 100
Jared
CE - Microsoft
June 4, 2012 at 10:48 am
isuckatsql (6/4/2012)
I cant get even get the select count to work!
The one in the initial post? It won't work, it's Oracle syntax not SQL Server
Do a count and filter on the partition boundaries or group by (or filter by) $Partition.<partition function name>(<partition column name>)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2012 at 10:58 am
SQLKnowItAll (6/4/2012)
I have two columns, ID and DocContent.
How would i structure my Select statement to display records in the first partition(up to 1000000 records) with an ID < 100?
SELECT ID, DocContentFROM table
WHERE ID < 100
Wow, that was easy!
I thought you had to reference the partitioned part of the table in the query.
I guess not!
Thanks
June 4, 2012 at 11:00 am
isuckatsql (6/4/2012)
SQLKnowItAll (6/4/2012)
I have two columns, ID and DocContent.
How would i structure my Select statement to display records in the first partition(up to 1000000 records) with an ID < 100?
SELECT ID, DocContentFROM table
WHERE ID < 100
Wow, that was easy!
I thought you had to reference the partitioned part of the table in the query.
I guess not!
Thanks
I assumed that your table is partitioned on ID. If it is not, then this will not work. See Gail's previous post about specifically referencing partitions in SQL Server. You can also simply query by filtering on the partitioned column.
Jared
CE - Microsoft
June 4, 2012 at 11:00 am
GilaMonster (6/4/2012)
isuckatsql (6/4/2012)
I cant get even get the select count to work!The one in the initial post? It won't work, it's Oracle syntax not SQL Server
Do a count and filter on the partition boundaries or group by (or filter by) $Partition.<partition function name>(<partition column name>)
I never knew that the query i was looking at was Oracle syntax!
Thanks for your help!
June 4, 2012 at 11:05 am
isuckatsql (6/4/2012)
Wow, that was easy!I thought you had to reference the partitioned part of the table in the query.
I guess not!
Thanks
The whole point of partitioning is that it is transparent to queries...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2012 at 11:07 am
I have four years of data that i would like to partition.
Would it be best to do four partitions on one year, or eight partitions on six months, and allow the overflow to the next partition, if a user wanted to search on a twelve month period?
Thanks
June 4, 2012 at 11:16 am
isuckatsql (6/4/2012)
I have four years of data that i would like to partition.Would it be best to do four partitions on one year, or eight partitions on six months, and allow the overflow to the next partition, if a user wanted to search on a twelve month period?
Thanks
My first question would be "Why do you want to partition it?" There may be good reasons, but I don't know your setup. Those reasons would also help to dictate how you will set up the partition ranges.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply