Execution Plan on Table Partitioning

Question

Hello guys I've got a table with the code below

CREATE TABLE [dbo].[PTableMediumPart](
    [PK] [int] NOT NULL,
    [Col1] [int] NOT NULL,
    [Col2] [int] NULL,
    [Col3] [money] NOT NULL,
    [Col4] [money] NULL,
    [Col5] [nvarchar](60) NOT NULL,
    [Col6] [nvarchar](60) NULL,
    [Col7] [varchar](255) NOT NULL,
    [Col8] [varchar](255) NULL,
    [Col9] [smallint] NOT NULL,
    [Col10] [smallint] NULL,
    [Col11] [decimal](20, 3) NOT NULL,
    [Col12] [decimal](20, 3) NULL,
    [Col13] [char](8) NOT NULL,
    [Col14] [datetime2](7) NULL,
    [PartitionKey] [tinyint] NOT NULL
)
GO

the partitionkey column is populated with the CAST((PK%3) AS TINYINT) and I've define partition function and scheme as below

CREATE PARTITION FUNCTION [PFTest](tinyint) AS RANGE LEFT FOR VALUES (0, 1)
GO

CREATE PARTITION SCHEME [PSTEST] AS PARTITION [PFTest] TO ([FGTest1], [FGTest2], [FGTest3])
GO

and then I created the clustered index like

CREATE UNIQUE CLUSTERED INDEX [CI_PArt] ON [dbo].[PTableMediumPart] 
(
    [PK] ASC,
    [PartitionKey] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PSTEST]([PartitionKey])
GO

but when I run the query with the same unpartitioned table I get the better performance or equal performance on queries, for example when I run the below queries I get the 50-50% exution plan, and in other scenarios the unpartitioned table get better execution plan, Why this happens? I am wrong about anything here?

SELECT * 
FROM dbo.PTableMedium
WHERE PK = 789 

SELECT * 
FROM dbo.PTableMediumPart 
WHERE PK = 789 AND  PartitionKey = CAST((789 % 3) AS TINYINT)

Answer 1

In my tests (SQL Server 2008), both execution plans have different costs: 75% for the first query and 25% for the second query:

Also, in my tests

1. First query will access all partitions (1..3) and
2. Second query will access only one partition ( 789 % 3 = 0 => is accesed partition [0..1) ) because of that supplementary predicate (AND PartitionKey = expression) defined on partitioning key/column (CREATE TABLE ... ON [PSTEST]([PartitionKey])). This predicate / condition allows partition elimination. Thus SQL Server will seek for rows having PK = 789 within only one partition.

Also, see this blog: http://blog.kejser.org/2014/01/15/curious-partition-function-behaviour/