Can some one give an example of a non-aligned partitioned index

Question

Post reply

Can some one give an example of a non-aligned partitioned index

mssqlnoob

Ten Centuries

Points: 1245
More actions
December 21, 2013 at 9:40 am

#280617

Hi,
Can some one give an example of a non-aligned partitioned index?
I have the following examples
USE AdventureWorksDW2012
SELECT MAX(OrderDate) FROM FactInternetSales --200807-31
SELECT MIN(OrderDate) FROM FactInternetSales --2005-07-01
--Create date partition function with increment by month.
DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION PfInternetSalesOrderMonth (datetime) AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime = '20050701';
WHILE @i < '20080801'
BEGIN
SET @DatePartitionFunction += '''' + CONVERT(nvarchar(10), @i,112) + '''' + N', ';
SET @i = DATEADD(MM, 1, @i);
END
SET @DatePartitionFunction += '''' + CONVERT(nvarchar(10), @i,112)+ '''' + N');';
PRINT N'@DatePartitionFunction ' + @DatePartitionFunction
EXEC sp_executesql @DatePartitionFunction;
GO
CREATE PARTITION SCHEME PsInternetSalesOrderMonth
AS PARTITION PfInternetSalesOrderMonth
ALL TO ([PRIMARY])
GO
CREATE TABLE [dbo].[FactInternetSales_Part](
[ProductKey] [int] NOT NULL,
[OrderDateKey] [int] NOT NULL,
[DueDateKey] [int] NOT NULL,
[ShipDateKey] [int] NOT NULL,
[CustomerKey] [int] NOT NULL,
[PromotionKey] [int] NOT NULL,
[CurrencyKey] [int] NOT NULL,
[SalesTerritoryKey] [int] NOT NULL,
[SalesOrderNumber] [nvarchar](20) NOT NULL,
[SalesOrderLineNumber] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NOT NULL,
[OrderQuantity] [smallint] NOT NULL,
[UnitPrice] [money] NOT NULL,
[ExtendedAmount] [money] NOT NULL,
[UnitPriceDiscountPct] [float] NOT NULL,
[DiscountAmount] [float] NOT NULL,
[ProductStandardCost] [money] NOT NULL,
[TotalProductCost] [money] NOT NULL,
[SalesAmount] [money] NOT NULL,
[TaxAmt] [money] NOT NULL,
[Freight] [money] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[CustomerPONumber] [nvarchar](25) NULL,
[OrderDate] [datetime] NULL,
[DueDate] [datetime] NULL,
[ShipDate] [datetime] NULL,
CONSTRAINT [PK_FactInternetSales_Part_SalesOrderNumber_SalesOrderLineNumber] PRIMARY KEY CLUSTERED
(
[SalesOrderNumber] ASC,
[SalesOrderLineNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON PsInternetSalesOrderMonth(OrderDate)
GO
INSERT INTO dbo.FactInternetSales_Part SELECT * FROM dbo.FactInternetSales
GO
CREATE INDEX FactInternetSales_Part_ProductKey ON dbo.FactInternetSales_Part(ProductKey) ON PsInternetSalesOrderMonth(OrderDate)
CREATE INDEX FactInternetSales_Part_OrderDateKey ON dbo.FactInternetSales_Part(OrderDate) ON PsInternetSalesOrderMonth(OrderDate)
CREATE INDEX FactInternetSales_Part_DueDateKey ON dbo.FactInternetSales_Part(DueDateKey) ON PsInternetSalesOrderMonth(OrderDate)
CREATE INDEX FactInternetSales_Part_ShipDateKey ON dbo.FactInternetSales_Part(ShipDateKey) ON PsInternetSalesOrderMonth(OrderDate)
CREATE INDEX FactInternetSales_Part_CustomerKey ON dbo.FactInternetSales_Part(CustomerKey) ON PsInternetSalesOrderMonth(OrderDate)
CREATE INDEX FactInternetSales_Part_PromotionKey ON dbo.FactInternetSales_Part(PromotionKey) ON PsInternetSalesOrderMonth(OrderDate)
CREATE INDEX FactInternetSales_Part_CurrencyKey ON dbo.FactInternetSales_Part(CurrencyKey) ON PsInternetSalesOrderMonth(OrderDate)
GO
how do we know whether table is partitioned nor not? what views should we use? I've tried
http://technet.microsoft.com/en-us/library/ms190324.aspx
http://technet.microsoft.com/en-us/library/ms187406.aspx
http://technet.microsoft.com/en-us/library/ms173760.aspx
http://technet.microsoft.com/en-us/library/ms175012.aspx
http://technet.microsoft.com/en-us/library/ms187780.aspx
http://technet.microsoft.com/en-us/library/ms175054.aspx
http://technet.microsoft.com/en-us/library/ms187381.aspx
but seems that non of catalog views answers any of my questions
thanks a lot!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Use sys.partitions

As for a non-aligned index, this

CREATE INDEX FactInternetSales_Part_CustomerKeyNonaligned ON dbo.FactInternetSales_Part(CustomerKey) ON [PRIMARY]

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

mssqlnoob Ten Centuries Points: 1245 More actions · Answer 2

SELECT Prod.EnglishProductName, DATEPART(yyyy, FIS_Part.OrderDate), DATEPART(month, FIS_Part.OrderDate), SUM(UnitPrice*OrderQuantity)

FROM FactInternetSales_Part FIS_Part JOIN DimProduct Prod

ON Prod.ProductKey = FIS_Part.ProductKey

WHERE DATEPART(yyyy, FIS_Part.DueDate)=2005 AND DATEPART(month, FIS_Part.DueDate)=7 AND DATEPART(dd, FIS_Part.DueDate)=13

GROUP BY Prod.EnglishProductName, DATEPART(yyyy, FIS_Part.OrderDate), DATEPART(month, FIS_Part.OrderDate)

ORDER BY Prod.EnglishProductName, DATEPART(yyyy, FIS_Part.OrderDate), DATEPART(month, FIS_Part.OrderDate)

GO

Will this query be logically be using partitioning pruning if I have partioned the table FactInternetSales on OrderDate?

from http://technet.microsoft.com/en-us/library/ms191158.aspx

If that being the case how do you know from execution plan that partition pruning is happening? Should I look out for Distribute Streams?

Also how should I force partition pruning?