{"id":31316,"date":"2024-02-26T17:59:52","date_gmt":"2024-02-26T16:59:52","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=31316"},"modified":"2024-02-26T17:59:55","modified_gmt":"2024-02-26T16:59:55","slug":"sql-server-manage-large-data-ranges-using-partitioning","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/","title":{"rendered":"SQL Server: Manage large data ranges using partitioning"},"content":{"rendered":"\n

Introduction:\u00a0<\/strong><\/h2>\n\n\n\n

When it comes to moving ranges of data with many rows across different tables in SQL-Server, the partitioning functionality of SQL-Server can provide a good solution for manageability and performance optimizing. In this blog we will look at the different advantages and the concept of partitioning in SQL-Server.<\/p>\n\n\n\n

Concept overview:\u00a0<\/strong><\/h2>\n\n\n\n

In SQL-Server, partitioning can divide the data of an index or table into smaller units. These units are called partitions. For that purpose, every row is assigned to a range and every range in turn is assigned to a specific partition. Practically there are two main components: The partition function and the partition scheme.  <\/p>\n\n\n\n

The partition function defines the range borders through boundary values and thus the number of partitions, in consideration with the data values, as well. You can define a partition function either as \u201crange right\u201d or \u201crange left\u201d. The main difference is how the boundary value gets treated. In a range right partition function, the boundary value is the first<\/strong> value of the next<\/strong> partition while in a range left partition function the boundary value is the last<\/strong> value of the previous<\/strong> partition. For example: <\/p>\n\n\n\n

We want to partition a table by year and the datatype of the column where we want to apply the partition function has the datatype \u201cdate\u201d. Totally we have entries for the year 2023 and 2024 which means, we want 2x partitions. In a range right function, the boundary value must be the first day of the year 2024 whereas in a range left function the boundary value must be the last day of the year 2023.\u00a0\u00a0<\/p>\n\n\n\n

See example below:\u00a0<\/p>\n\n\n\n

\"Image:<\/figure>\n\n\n\n
\"Image:<\/figure>\n\n\n\n

The partition scheme is used to map the different partitions, which are defined through the partition function, to multiple or one filegroup.<\/p>\n\n\n\n

Main benefits of partitioning<\/strong>:<\/h2>\n\n\n\n

There are multiple scenarios where performance or manageability of a data model can be increased through partitioning. The main advantage of partitioning is that it reduces the contention on the whole table as a database object and restricts it to the partition level when performing operations on the corresponding data range. Partitioning also facilitates data transfer with the \u201cswitch partition\u201d statement, this statement performs a switch-in or switch-out of o whole partition. Through that, a large amount of data can be transferred very quickly.<\/p>\n\n\n\n

Demo Lab<\/strong>:<\/h2>\n\n\n\n

For demo purposes I created the following script, which will create three tables with 5 million rows of historical data from 11 years in the past until today:<\/p>\n\n\n

\nUSE [master] \nGO \n \n--Create Test Database \nCREATE DATABASE [TestPartition] \nGO \n \n--Change Recovery Model \nALTER DATABASE [TestPartition] SET RECOVERY SIMPLE WITH NO_WAIT \nGO \n \n--Create Tables \nUse [TestPartition] \nGO \n \nCREATE TABLE [dbo].[Table01_HEAP]( \n[Entry_Datetime] [datetime] NOT NULL, \n[Entry_Text] [nvarchar](50) NULL \n) \nGO \n \nCREATE TABLE [dbo].[Table01_CLUSTEREDINDEX]( \n[Entry_Datetime] [datetime] NOT NULL, \n[Entry_Text] [nvarchar](50) NULL \n) \nGO \n \nCREATE TABLE [dbo].[Table01_PARTITIONED]( \n[Entry_Datetime] [datetime] NOT NULL, \n[Entry_Text] [nvarchar](50) NULL \n) \nGO \n \n--GENERATE DATA \n \ndeclare @date as datetime \n \ndeclare @YearSubtract int \ndeclare @DaySubtract int \ndeclare @HourSubtract int \ndeclare @MinuteSubtract int  \ndeclare @SecondSubtract int  \ndeclare @MilliSubtract int \n \n--Specifiy how many Years backwards data should be generated \ndeclare @YearsBackward int \nset @YearsBackward = 11 \n \n--Specifiy how many rows of data should be generated \ndeclare @rows2generate int \nset @rows2generate = 5000000 \n \n \ndeclare @counter int \nset @counter = 1 \n \n--generate data entries \nwhile @counter <= @rows2generate  \nbegin \n \n--Year \nSet @YearSubtract = floor(rand() * (@YearsBackward - 0 + 1)) + 0 \n--Day \nSet @DaySubtract = floor(rand() * (365 - 0 + 1)) + 0 \n--Hour \nSet @HourSubtract = floor(rand() * (24 - 0 + 1)) + 0 \n--Minute \nSet @MinuteSubtract = floor(rand() * (60 - 0 + 1)) + 0 \n--Second \nSet @SecondSubtract = floor(rand() * (60 - 0 + 1)) + 0 \n--Milisecond \nSet @MilliSubtract = floor(rand() * (1000 - 0 + 1)) + 0 \n \n \nset @date = Dateadd(YEAR, -@YearSubtract , Getdate()) \nset @date = Dateadd(DAY, -@DaySubtract , @date) \nset @date = Dateadd(HOUR, -@HourSubtract , @date) \nset @date = Dateadd(MINUTE, -@MinuteSubtract , @date) \nset @date = Dateadd(SECOND, -@SecondSubtract , @date) \nset @date = Dateadd(MILLISECOND, @MilliSubtract , @date) \n \ninsert into Table01_HEAP (Entry_Datetime, Entry_Text) \nValues (@date, 'This is a entry from ' + convert(nvarchar, @date, 29)) \n \nset @counter = @counter + 1 \n \nend \n \n--COPY DATA TO OTHER TABLES \n \nINSERT INTO dbo.Table01_CLUSTEREDINDEX \n  (Entry_Datetime, Entry_Text) \nSELECT Entry_Datetime, Entry_Text \n  FROM Table01_HEAP \n \nINSERT INTO dbo.Table01_PARTITIONED \n  (Entry_Datetime, Entry_Text) \nSELECT Entry_Datetime, Entry_Text \n  FROM Table01_HEAP \n \n--Create Clustered Indexes for dbo.Table01_CLUSTEREDINDEX and dbo.Table01_PARTITIONED \n \nCREATE CLUSTERED INDEX [ClusteredIndex_Table01_CLUSTEREDINDEX] ON [dbo].[Table01_CLUSTEREDINDEX] \n( \n[Entry_Datetime] ASC \n \n) on [PRIMARY] \nGO \n \nCREATE CLUSTERED INDEX [ClusteredIndex_Table01_PARTITIONED] ON [dbo].[Table01_PARTITIONED] \n( \n[Entry_Datetime] ASC \n \n) on [PRIMARY] \nGO \n\n<\/pre><\/div>\n\n\n
The tables have the same data in it. The difference between the tables is, that one is a heap, one has clustered index and one has a clustered Index which will be partitioned in the next step:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
After the tables are created with the corresponding data and indexes, the partition function and scheme must be created. This was done by the following script:<\/p>\n\n\n
\n-- Create Partition Function as range right for every Year -10 Years \nCreate Partition Function [myPF01_datetime] (datetime) \nAS Range Right for Values ( \nDATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 0, 0), DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0), DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0), \nDATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0), DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0), DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0),  \nDATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 6, 0), DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 7, 0), DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 8, 0),  \nDATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 9, 0), DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 10, 0) \n); \nGO \n \n-- Create Partition Scheme for Partition Function myPF01_datetime \nCREATE PARTITION SCHEME [myPS01_datetime] \nAS PARTITION myPF01_datetime ALL TO ([PRIMARY]) \nGO \n\n<\/pre><\/div>\n\n\n
I have used the DATEADD() function in combination with the DATEDIFF() function to retrieve the first millisecond of the year as datetime data type and that for the last 10 years and used this as range right boundary values. For sure it is also possible to hard code the boundary values like \u20182014-01-01 00:00:00.000\u2019 but I prefer to keep it as dynamically as possible. At the end it is the same result:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
After creating the partition function, I have created the partition scheme. The partition scheme is mapped to the partition function. In my case I assign every partition to the primary filegroup. It is also possible to split the partitions across multiple filegroups.<\/p>\n\n\n\n
As far as the partition function and scheme are created successfully it can be applied to the existing table: Table01_PARTITIONED. For achieving that, the clustered index of the table must be recreated on the partition scheme instead of the primary filegroup:\u00a0<\/p>\n\n\n
\n-- Apply partitiononing on Table: Table01_PARTITIONED through recreating the Tables Clustered Index ClusteredIndex_Table01_PARTITIONED on Partition Scheme myPS01_datetime \nCREATE CLUSTERED INDEX [ClusteredIndex_Table01_PARTITIONED] ON [dbo].[Table01_PARTITIONED] \n( \n[Entry_Datetime] ASC \n \n) with (DROP_EXISTING = ON) on myPS01_datetime(Entry_Datetime);  \nGO \n\n<\/pre><\/div>\n\n\n
After doing that, the Table Table01_PARTITIONED has multiple partitions while the other tables have still only one partition:\u00a0<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
There are at all 12 partitions for every year between 2014 and 2024 as well as one for every entry which has an earlier datetime than 2014-01-01 00:00:00.000 and one for every entry that has a later datetime value than 2024-01-01 00:00:00.000 while partition nr. 1 has the earliest data and partition nr. 12 has the latest data in it. See below:\u00a0<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
DEMO Tests:<\/h2>\n\n\n\n
First, I want to compare the performance when moving outdated data, which is older than 2014-01-01 00:00:00.000, from the table itself to a history table. For that purpose, I created a history table with the same data structure as the table Table01_CLUSTEREDINDEX:<\/p>\n\n\n
\nUse [TestPartition] \nGO \n \n--Create History Table \nCREATE TABLE [dbo].[Table01_HISTORY01]( \n[Entry_Datetime] [datetime] NOT NULL, \n[Entry_Text] [nvarchar](50) NULL \n) \nGO \n \n--Create Clustered Indexes for dbo.Table01_HISTORY01 \nCREATE CLUSTERED INDEX [ClusteredIndex_Table01_HISTORY01] ON [dbo].[Table01_HISTORY01] \n( \n[Entry_Datetime] ASC \n \n) on [PRIMARY] \nGO \n\n<\/pre><\/div>\n\n\n
I am starting first with the table with the clustered index with a classic \u201cinsert into select\u201d statement:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
We can see that we have 10932 reads in total and a total query run time of 761 milliseconds.<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
In the execution plan, we can see that a classical Index seek operation occurred. Which means, the database engine seeked for every row which has a datetime value previous to 2014-01-01 00:00:00.000 and wrote it into the history table.<\/p>\n\n\n\n
For the delete operation we can see similar results:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
Totally 785099 rows where moved and we have in the table Table01_CLUSTEREDINDEX no older entries than 2014-01-01 00:00:00.000 anymore:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
Next let us compare the data movement when using a \u201cswitch partition\u201d statement. For switching a partition from a partitioned source table to a nonpartitioned destination table, we need to use the partition number of the source table. For that I run the following query:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
We can see that the partition number 1 was moved within 2 milliseconds<\/strong>. Compared to the previous query where it took 761 milliseconds for inserting the data and an additional 596 milliseconds for deleting the data, the switch partition operation is obviously much faster. But why is this the case? \u2013 that\u2019s because switching partitions is a metadata operation. It does not seeking through an index (or even worse \u2013 scanning a table) and write every row one by one, instead it changes the metadata of the partition and remaps the partition to the target table.\u00a0<\/p>\n\n\n\n
And as we can see, we have the same result:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
Another big advantage is when it comes to deleting a whole data range. For example: Let us delete the entries of the year 2017 \u2013 we do not need them anymore.<\/p>\n\n\n\n
For the table with the clustered Index, we must use a statement like this:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
We can see that we have here a query runtime of 355 milliseconds and 68351 page reads in total for the delete operation with the clustered index.\u00a0<\/p>\n\n\n\n
For the partitioned table instead, we can use a truncate operation on the specific partition. That\u2019s because the partition is treated as a own physical unit and can for that be truncated.<\/p>\n\n\n\n
And as we should know: Truncating is much faster, because this operation is deallocating the pages and writes only one transaction log entry for the page deallocation while a delete operation is going row by row and writes every row deletion in the transaction log.<\/p>\n\n\n\n
So, let us try: The year 2017 is 7 years back so let us verify, that the right data range will be deleted:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
We can see with the short query above: 7 Years back, that would be the partition nr. 5 and the data range seems to be right.\u00a0 So, let us truncate:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
And we can see to truncate all the entries from the year 2017, the database engine took 1 millisecond compared to the 355 seconds for the delete operation again much faster.<\/p>\n\n\n\n
Next: let\u2019s see, how we can change the lock behavior of SQL-Server through partitioning.\u00a0For that I ran the following update query for updating every entry text for dates which are younger than May 2018:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
While the update operation above was running, I queried the DMV sys.dm_tran_locks in another session for checking the locks my update operation above is holding:\u00a0<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
And we can see that we have a lot of page locks and also an exclusive lock on the object itself (in this case the Table01_HEAP).\u00a0\u00a0That is because of SQL-Servers lock escalation behavior.<\/p>\n\n\n\n
I ran the same update operation on the partitioned table but before I changed the lock escalation setting of the table from default value \u201ctable\u201d to \u201cauto\u201d. This is necessary for enable locking on partition level:\u00a0<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
And when I\u2019m querying the dmv again while the update operation above is running, I get the following result:<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
We can see that we have no exclusive look on abject level anymore, we have an intended exclusive look, which will not prevent other transactions from accessing the data (as far as it has no other look on a more granular level). Instead, we have multiple exclusive looks on multiple resources called HOBT. And when we take a look at the \u201cresource_associated_entity_id\u201d and using them for querying the sys.partitions table, we can see the following information’s:\u00a0<\/p>\n\n\n\n
\"Image:<\/figure>\n\n\n\n
These resources locked through the update operation on the partitioned table are the partitions associated with the table. So, SQL-Server locked the partitions instead of locking the whole table. This has the advantage that locking happens in a more granular context which prevents lock contention on the table itself.\u00a0<\/p>\n\n\n\n
Conclusion:<\/strong><\/h2>\n\n\n\n
Partitioning can be a very powerful and useful functionality in SQL-Server when used in an appropriate situation. Especially when it comes to regular operations on whole data ranges, partitioning can be used for enhancing performance and manageability. With partitioning, it\u2019s also possible to distribute the data of a table over multiple files groups. Additionally with splitting and merging partitions it\u2019s possible to maintain partitions for growing or shrinking data.<\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction:\u00a0 When it comes to moving ranges of data with many rows across different tables in SQL-Server, the partitioning functionality of SQL-Server can provide a good solution for manageability and performance optimizing. In this blog we will look at the different advantages and the concept of partitioning in SQL-Server. Concept overview:\u00a0 In SQL-Server, partitioning can […]<\/p>\n","protected":false},"author":145,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,368,99],"tags":[51],"type_dbi":[],"class_list":["post-31316","post","type-post","status-publish","format-standard","hentry","category-database-management","category-development-performance","category-sql-server","tag-sql-server"],"acf":[],"yoast_head":"\nSQL Server: Manage large data ranges using partitioning - dbi Blog<\/title>\n<meta name=\"description\" content=\"Blog about how to use SQL-Server partitioning for manage large data ranges and enhancing performance and manageability.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server: Manage large data ranges using partitioning\" \/>\n<meta property=\"og:description\" content=\"Blog about how to use SQL-Server partitioning for manage large data ranges and enhancing performance and manageability.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-02-26T16:59:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-02-26T16:59:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/02\/image-43.png\" \/>\n<meta name=\"author\" content=\"Hocine Mechara\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Hocine Mechara\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/\"},\"author\":{\"name\":\"Hocine Mechara\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/29415d02bc1b50884796a01cf649951f\"},\"headline\":\"SQL Server: Manage large data ranges using partitioning\",\"datePublished\":\"2024-02-26T16:59:52+00:00\",\"dateModified\":\"2024-02-26T16:59:55+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/\"},\"wordCount\":1580,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/02\\\/image-43.png\",\"keywords\":[\"SQL Server\"],\"articleSection\":[\"Database management\",\"Development & Performance\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/\",\"name\":\"SQL Server: Manage large data ranges using partitioning - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/02\\\/image-43.png\",\"datePublished\":\"2024-02-26T16:59:52+00:00\",\"dateModified\":\"2024-02-26T16:59:55+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/29415d02bc1b50884796a01cf649951f\"},\"description\":\"Blog about how to use SQL-Server partitioning for manage large data ranges and enhancing performance and manageability.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/02\\\/image-43.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/02\\\/image-43.png\",\"width\":331,\"height\":66},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-manage-large-data-ranges-using-partitioning\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server: Manage large data ranges using partitioning\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/29415d02bc1b50884796a01cf649951f\",\"name\":\"Hocine Mechara\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g\",\"caption\":\"Hocine Mechara\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/hocinemechara\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server: Manage large data ranges using partitioning - dbi Blog","description":"Blog about how to use SQL-Server partitioning for manage large data ranges and enhancing performance and manageability.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server: Manage large data ranges using partitioning","og_description":"Blog about how to use SQL-Server partitioning for manage large data ranges and enhancing performance and manageability.","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/","og_site_name":"dbi Blog","article_published_time":"2024-02-26T16:59:52+00:00","article_modified_time":"2024-02-26T16:59:55+00:00","og_image":[{"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/02\/image-43.png","type":"","width":"","height":""}],"author":"Hocine Mechara","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Hocine Mechara","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/"},"author":{"name":"Hocine Mechara","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/29415d02bc1b50884796a01cf649951f"},"headline":"SQL Server: Manage large data ranges using partitioning","datePublished":"2024-02-26T16:59:52+00:00","dateModified":"2024-02-26T16:59:55+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/"},"wordCount":1580,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/02\/image-43.png","keywords":["SQL Server"],"articleSection":["Database management","Development & Performance","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/","name":"SQL Server: Manage large data ranges using partitioning - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/02\/image-43.png","datePublished":"2024-02-26T16:59:52+00:00","dateModified":"2024-02-26T16:59:55+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/29415d02bc1b50884796a01cf649951f"},"description":"Blog about how to use SQL-Server partitioning for manage large data ranges and enhancing performance and manageability.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/02\/image-43.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/02\/image-43.png","width":331,"height":66},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-manage-large-data-ranges-using-partitioning\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server: Manage large data ranges using partitioning"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/29415d02bc1b50884796a01cf649951f","name":"Hocine Mechara","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g","caption":"Hocine Mechara"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/hocinemechara\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/31316","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/145"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=31316"}],"version-history":[{"count":25,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/31316\/revisions"}],"predecessor-version":[{"id":31378,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/31316\/revisions\/31378"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=31316"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=31316"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=31316"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=31316"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}