다음을 통해 공유

Demo Transcript: Table Partitioning in SQL Server 2012

  • 아티클

SQL11UPD02-TSCRIPT-04

This wiki is a transcript of a previously recorded video.

Related content assets:

SQL Server 2012 Partitioning Enhancements

Hey everybody – this is Joe Sack and I’m a Principal Consultant with SQLskills, and this is the partitioning for SQL Server 2012 demonstration – so hopefully you got a chance to look at the full presentation – here’s the demo.

What I’m going to do is open up SQL Server Management Studio and we’re going to use AdventureWorksDWDenali and specifically I want to just show two small demonstrations. The first is that we can create more than 1000 partitions natively. So that option was there if you added… prior to 2012 you could create more than 1000 partitions if you installed service pack, however with SQL Server 2012 RTM, it’s natively supported out of the box – you don’t have to worry about interoperability with other SQL Server instances that might not be at the same version. So if you’re dealing with other SQL Server 2012 instances in the topology then you’ll know that you have native support of greater than 1000 partitions – up to 15 thousand.

So first thing I’m going to do here is I’m going to create 1804 sample rows, just so that we have something to demonstrate the 1000-plus. Alright – and now I’m going to create this partition function pfFactCallCenter – and I’m going to page and page and page as we’ve got lots of partitions to add. And beyond that I’m going to create a partition scheme and I’m going to map everything to the primary filegroup, but there are scenarios – I suppose you could have 1800 filegroups that you put it to, but in terms of manageability I don’t know how ideal that would be? But just for this demonstration we’re putting everything to primary.

Alright – and then I’m going to recreate the clustered index and the nonclustered index to use that partition function/partition scheme and leverage that so we end up having 1835 partitions per index. So I’m just highlighting everything down to this – I’m ALTER TABLE ADD CONSTRAINT, I’m dropping the constraint, I’m recreating the constraint – in this case the two constraints are associated with indexes so one is for the primary key index and one is for the unique. Ok so I’m going to just recreate that…

Alright – and so when that’s done I’m going to actually query sys.dm_db_partition_stats to show that yes indeed, we’re over 1000 partitions per index. And so index ID 1 – that’s our clustered index – and if I look down here, sure enough we have 1800-plus, and then index ID2 – that’s our nonclustered index – that number of partitions as well.

So this gets to the second part of the demonstration, which is I want to create a staging table and then I want to switch out to the staging table and I want to use the wizard for it, I don’t want to have to hand-code that.

So in Object Explorer I’ll expand the database, tables, and then I’ll right click the table FactCallCenter and go to Storage and Manage Partition – and it knows it’s partition – had we used the Storage option before I partitioned it just now, you’d have seen the Create Partition so we could have also partitioned it using that method. I already took care of that so we can just go to Manage Partition and I’ll go Next. And in terms of what’s changed, before SQL Server 2012, these two options – so if you compared it side-by-side, for example, 2008 R2 to 2012, you’d see these two new options were created so I could do granular switch out operation or granular switch in operation, and I can also create a staging table.

So I’m going to actually break this out – I’ll create a staging table and then I’ll show you switch out. So let’s create that staging table first and I’ll give it a creative name – there you go – StagingTable. And then in terms of the switch partition, I’m going to let it know what the switch partition is so it can create the supporting objects accordingly. And in this case I’m going one partition down so I know there’s a row in this partition where it’s 20000103. And I’ll go Next and I’ll create the script as I’m not going to run it immediately, I want to show you, I want to walk through it.

Ok – so that’s done being created and it creates all these supporting objects. You even see here there’s a check add constraint and it gives me the range that would constrain to that specific partition that I chose. So this saved you quite a bit of hand coding – it’s creating a staging table that I can use very quickly. I’m going to execute this right away and also notice that this is within a transaction so if there were any issues it would roll back – actually it would all be encapsulated in one transaction.

Alright, so my staging table’s created so the second part of this demonstration, well really the third part is I’m going to go back and go to Storage and Manage Partition again and I’m going to say Switch Out. And this time I’m going to pick that second partition again – that 20000103 and there’s one row in it. Now, you notice right here it says – it basically says, hey, you can create a new table so it would have created on-the-fly what I just created manually – but I wanted to show you exactly what the objects were ahead of time rather than just switching it out and ruining all the fun.

But in this case I have an existing table so I’m going to say ok, switch that partition out to the existing table and Next, and again I’ll script this out – we’re going to have much less code this time. Alright, so I have my ALTER TABLE and I’m switching partition 2 out to the staging table. Alright – I just switched it – it was very quick, granted it was only one row, but still very quick. Let’s just make sure that it got over there – alright, yep, it got there.

So I can do the same thing – so I can right-click here again and I can say Manage Partition and I can use the same methodology to switch things back in. So in this case I could switch it back in and say, ok I want you to switch from that partition into the following table – and actually I’m saying FactCallCenter here. Let’s go ahead and just do that quick switch. Alright, so now I’m on the staging table and I’m going to… going to go back here, Manage Partition, and I’m going to Switch In, and I’m going to switch in  to this 20000103 with the right boundary of that value, and then there’s my staging table right there. And… create script and then it’s just a reverse pattern – so now I’m switching in from the staging table I just exported to, and I’m going to switch that partition back in.

So there we go – it’s a very quick and straightforward demonstration on a couple of the enhancements to the wizard and also just to demonstrate the native port of greater than 1000 partitions.

So I hope you enjoyed watching this video and thanks for watching!

Return to [[SQL Server 2012 Developer Training Kit BOM (en-US)]]