July 7, 2009 at 12:21 am
I have observed that in AdventureWorks some tables are with multiple partitions and some other are on single partition. And in some cases tables on multiple partitions have less rows than that of on single partition. Row number in all partitions is shown same.
I want to know that on what basis partitions are automatically being created in some cases?
Regards
DBDigger Microsoft Data Platform Consultancy.
July 8, 2009 at 4:59 am
A table only get partitioned when a partitioning scheme and partitioning function has been defined for it. The number of rows in a table has no impact on if it is partitioned, this is due entirely to the partition scheme.
Books Online (BOL) has a good description of what these items are. If you still have questions after reading BOL please ask them.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 8, 2009 at 5:15 am
Every table has at least one partitioned defined. I have noticed that following script provides number of partitions for a table equal to number of indexes defined in that particular table.
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='tablename'
GO
May be that every index in table has also its own separate partition defined.
Regards
DBDigger Microsoft Data Platform Consultancy.
July 8, 2009 at 5:41 am
In SQL Server 2005 and above, all tables have the capability of supporting multiple partitions. By default all data is kept in a single partition.
If you want to use multiple partitions, then you need a partitioning scheme and a partition function.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 10, 2009 at 5:37 am
A useful strategy in large databases is to partition a single logical set of data into multiple physical storage locations for manageability or performance reasons.
SQL Server 2005 supports partitioned tables and indexes. Partitioned tables and indexes are single objects stored in multiple physical partitions, each partition holding a specific subset of the data, which will improve physical I/O and performance.
A partitioned table is a table in which the data is separated horizontally into multiple physical locations based on a range of values for a specific column. The physical locations for partitions are filegroups. For example, you could use a partitioned table to store sales orders and then separate the order records into different filegroups based on the order date so that orders placed in the current financial year are stored in one partition, orders placed in the previous financial year are stored in a second partition, and all orders older than two years are stored in a third partition. This technique makes it possible to control the physical storage of different kinds of orders while still maintaining them in a single table.
We first create partition function as below
CREATE PARTITION FUNCTION pf_OrderDate (datetime)
AS RANGE RIGHT
FOR VALUES ('01/01/2003', '01/01/2004', '01/01/2005')
then we create a partition scheme
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO (fg1, fg2, fg3, fg4, fg5)
After this use the ON clause to place the table on the ps_OrderDate partition scheme. You can keep all partitions in one physical file (i.e. Primary) or many physical files
AQ KHAN
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply