July 5, 2013 at 2:40 am
Hi All,
In a database (partitionDB) I have created partition then I have crated table on that DB. In another database (Partitiondb_New) I have the same table but here is no partition. In both the datbases there is a table 'Orders'.
I have used the following query to partition the database (partitionDB)
Create PARTITION FUNCTION PFORDERDATERANGE (DATETIME)
AS
RANGE RIGHT FOR VALUES ('2011-05-04','2011-08-04','2011/11/04','2013/01/04','2013/04/04','2013/07/04')
Create Partition Scheme PSOrderDateChange
as
Partition PFORDERDATERANGE to (FG1, FG2, FG3, FG4, FG5, FG6, [Primary])
Create table dbo.orders
(
Orderid int identity (1,1) not null,
[Orderdate] [datetime] not null,
[Name] nvarchar (255),
[Productid] int null,
constraint [pk_orders] primary key clustered ( Orderid ASC, Orderdate ASC )
ON PSOrderDateChange (OrderDate)
)
on PSOrderDateChange (OrderDate)
Lastly I have checked to see which filegroups contain how may records
select $partition.PFORDERDATERANGE (o.orderdate) as [Prtition Number],
Min(o.orderdate) as [Min Value],
max (o.orderdate) as [Max Value],
Count(*) [records in Partition]
from Orders o
Group by $partition.PFORDERDATERANGE (o.orderdate)
order by [Prtition Number]
and I am getting result. to see the result and performance issue I ran a query in both the databases
select * from dbo.Orders
where Orderdate between '2013-01-04 00:00:00.000' and '2013-04-03 00:00:00.000'
but in both the scenario I am getting output in 8 secs....
then How will I use partition in my database or table?
I am sure someone will show the right direction....
Thanks in advance
July 5, 2013 at 8:37 am
Hi
Maybe following link https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/ will enclear some things to you.
Igor Micev,My blog: www.igormicev.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply