title: Wrong order of SQL Server's partitions
body: <p>I created database</p>
  
  <pre><code>USE [master]
  GO
  
  CREATE DATABASE [testdb]
   CONTAINMENT = NONE
   ON  PRIMARY 
  ( NAME = N'testdb', FILENAME = N'D:\MSSQL\Data\testdb.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
   FILEGROUP [2016] 
  ( NAME = N'2016', FILENAME = N'D:\MSSQL\Data\2016.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
   FILEGROUP [2017] 
  ( NAME = N'2017', FILENAME = N'D:\MSSQL\Data\2017.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
   FILEGROUP [2018] 
  ( NAME = N'2018', FILENAME = N'D:\MSSQL\Data\2018.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
   FILEGROUP [2019] 
  ( NAME = N'2019', FILENAME = N'D:\MSSQL\Data\2019.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
   FILEGROUP [2020] 
  ( NAME = N'2020', FILENAME = N'D:\MSSQL\Data\2020.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
   LOG ON 
  ( NAME = N'testdb_log', FILENAME = N'D:\MSSQL\Data\testdb_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
  GO
  ALTER DATABASE [testdb] SET COMPATIBILITY_LEVEL = 130
  GO
  </code></pre>
  
  <p>I have a table</p>
  
  <pre><code>CREATE TABLE [dbo].[ticket2](
      [id] [int] NULL,
      [datesell] [datetime] NULL,
      [status] [int] NULL
  ) ON [PRIMARY]
  GO
  </code></pre>
  
  <p>Next, I've added partitions for this table</p>
  
  <pre><code>USE [testdb]
  GO
  
  BEGIN TRANSACTION
  CREATE PARTITION FUNCTION [byYear](datetime) AS RANGE LEFT FOR VALUES (N'2015-12-31T23:59:59', N'2017-01-01T00:00:00', N'2018-01-01T00:00:00', N'2019-01-01T00:00:00', N'2020-01-01T00:00:00')
  
  CREATE PARTITION SCHEME [years] AS PARTITION [byYear] TO ([PRIMARY], [2016], [2017], [2018], [2019], [2020])
  
  CREATE CLUSTERED INDEX [ClusteredIndex_on_years_636227525068063988] ON [dbo].[ticket2]
  (
      [datesell]
  )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [years]([datesell])
  
  DROP INDEX [ClusteredIndex_on_years_636227525068063988] ON [dbo].[ticket2]
  
  COMMIT TRANSACTION
  GO
  </code></pre>
  
  <p>When, I add some data into my table, I see next 
  <a href="https://i.stack.imgur.com/v6h8x.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/v6h8x.png" alt="Data in partitions"></a> </p>
  
  <p>Next, I've added one more partition for the next year</p>
  
  <pre><code>ALTER DATABASE testdb
  ADD FILEGROUP [2021]
  
  ALTER DATABASE testdb
  ADD FILE
  ( NAME = N'2021', FILENAME = N'D:\MSSQL\Data\2021.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ) TO FILEGROUP [2021] 
  
  ALTER PARTITION SCHEME [years] NEXT USED [2021];
  ALTER PARTITION FUNCTION byYear() split RANGE (N'2022-01-01T00:00:00' )
  
  BEGIN TRANSACTION
  CREATE CLUSTERED INDEX [ClusteredIndex_on_years_636227647501414735] ON [dbo].[ticket2]
  (
      [datesell]
  )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [years]([datesell])
  DROP INDEX [ClusteredIndex_on_years_636227647501414735] ON [dbo].[ticket2]
  COMMIT TRANSACTION
  </code></pre>
  
  <p>And I see wrong partitions order. 
  <a href="https://i.stack.imgur.com/LTD4a.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LTD4a.png" alt="Wrong partitions order"></a></p>
  
  <p>Partition 2020 is still the last, but the last partition must be 2021. And all data with sell date more than 01/01/2022 will be in partition 2020, but I expected to see them in partition 2021.</p>
  
  <p>Why it is so? </p>