Created a new SQL Partition but my select statement wont work - why?

  • 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

  • What exactly are you trying to do? Count the number of rows contained in a specific partition?

    Jared
    CE - Microsoft

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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!

  • 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

  • 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:

    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)

    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

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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, DocContent

    FROM 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

  • 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, DocContent

    FROM 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

  • 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!

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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