asked on

advice

This is MS SQL question

I have a table with a million record, I want to partition it. I use the enterprise version to create partition script.

USE [database]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [WFPartitionFunction](datetime) AS RANGE LEFT FOR VALUES (N'2013-01-01T00:00:00', N'2013-02-01T00:00:00', N'2013-03-01T00:00:00', N'2013-04-01T00:00:00')

CREATE PARTITION SCHEME [WFPartitionScheme] AS PARTITION [WFPartitionFunction] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

CREATE CLUSTERED INDEX [ClusteredIndex_on_WFPartitionScheme_634965441549611755] ON [dbo].[WORKERS]
(
[CREATED_DATE]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [WFPartitionScheme]([CREATED_DATE])

DROP INDEX [ClusteredIndex_on_WFPartitionScheme_634965441549611755] ON [dbo].[WORKERS] WITH ( ONLINE = OFF )

COMMIT TRANSACTION

In this script, I have few questions

1. Can I automatically set the range month by month, no need to specify like N'2013-01-01T00:00:00', N'2013-02-01T00:00:00', N'2013-03-01T00:00:00', N'2013-04-01T00:00:00'...etc?

2. I run this script directly in management studio. After that, does it mean the table partition is created? how to verify it?

3. I see CREATE CLUSTERED INDEX and then DROP INDEX [ClusteredIndex_on_WFPartitionScheme_634965441549611755]
Why to do that? drop the index, does it mean you drop the table partition?

4. If I apply this script to no Enterprise version MS SQL 2008 server, the table partition will work?

SOLUTION

EugeneZ

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Chris Luttrell

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

wasabi3689

ASKER

Yes, I am meaning Standard Edition SQL Server or developer version. If the hosting server is STD version, the table partition will not work even I apply the partition scrip in STD versiont generated from Enterprise version, correct?

ASKER CERTIFIED SOLUTION

Chris Luttrell

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

wasabi3689

ASKER

I want to return all rows from one partition of a partitioned table or index from above my example,

how to do that?

This is example from link
http://msdn.microsoft.com/en-us/library/ms188071%28v=sql.105%29.aspx

SELECT * FROM Production.TransactionHistory
WHERE $PARTITION.TransactionRangePF1(TransactionDate) = 5 ;

In my case, how to modify it into like above?

wasabi3689

ASKER

how to query each partition range after I make the partition?

SOLUTION

Chris Luttrell

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer