December 21, 2013 at 9:40 am
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!
December 21, 2013 at 2:03 pm
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
December 23, 2013 at 9:02 am
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?
SELECT Prod.EnglishProductName, DATEPART(yyyy, FIS_Part.OrderDate), DATEPART(month, FIS_Part.OrderDate), SUM(UnitPrice*OrderQuantity)
FROM FactInternetSales_Part FIS_Part WITH (FORCESEEK(FactInternetSales_Part_ProductKey(ProductKey)))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
thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply