Table partitioning on Azure SQL Database (not sharding), I'm after a rolling/sliding window solution based on DateTime column.

• Duran

  SSChampion

  Points: 13259

  More actions

  October 9, 2017 at 9:02 pm

  #339811

  Hello,

  I hope someone can offer me some insight. I am looking to partition a large table, using a datetime column, in an Azure SQL Database. We do not want to use Sharding.

  I have the first parts of creating a partition function and partition scheme, and only using the primary filegroup (because that is all you have in Azure SQL Database), I will be partitioning to the left.

  I am not sure of the correct terminology either, I am seeing 'sliding windows', and 'rolling windows', are both of these the same as switching? Are they the same, and the terminology is used interchangeably, or are they different?
  This article is the closest I can get to what I am after,...(might come in handy for someone else)

  https://stackify.com/how-to-partition-tables-in-azure-sql/

  ...I wonder how different it is to implement partitioning on an Azure SQL Database, compared to partitioning on an Azure SQL Data Warehouse? I wonder if its basically the same, and therefore I can use articles on Azure Data Warehouse partitioning to help me out?

  Any help or direction would be most appreciated. Thank you for reading.

  Regards,
  D.

• Johan Bijnens

  SSC Guru

  Points: 135506

  More actions

  October 10, 2017 at 12:39 am

  #1962942

  First of all: the concepts: https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning

  Did you have a look at : https://www.mssqltips.com/sqlservertip/3494/azure-sql-database--table-partitioning/ ?

  I haven't tested this on a Azure SQLDB, but this seems worth the try, since it also handles the trunctate of a partition ! (and is quiet recent ) https://stackify.com/how-to-partition-tables-in-azure-sql/
  
  Don't start it if you don't master it !

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me