title: Partitioned Sequence / Identity
body: <p>Given a table like this</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[_bh_test](
      [Id] [bigint] IDENTITY(1,1) NOT NULL,
      [Agency] [int] NOT NULL,
      [Year] [int] NULL,
      [Num] [int] NOT NULL
  ) ON [PRIMARY]
  
  GO
  SET IDENTITY_INSERT [dbo].[_bh_test] ON 
  
  GO
  INSERT [dbo].[_bh_test] ([Id], [Agency], [Year], [Num]) VALUES (1, 1, 2015, 1)
  GO
  INSERT [dbo].[_bh_test] ([Id], [Agency], [Year], [Num]) VALUES (2, 1, 2015, 2)
  GO
  INSERT [dbo].[_bh_test] ([Id], [Agency], [Year], [Num]) VALUES (3, 1, 2016, 1)
  GO
  INSERT [dbo].[_bh_test] ([Id], [Agency], [Year], [Num]) VALUES (4, 2, 2015, 1)
  GO
  INSERT [dbo].[_bh_test] ([Id], [Agency], [Year], [Num]) VALUES (5, 2, 2015, 2)
  GO
  INSERT [dbo].[_bh_test] ([Id], [Agency], [Year], [Num]) VALUES (6, 2, 2016, 1)
  GO
  INSERT [dbo].[_bh_test] ([Id], [Agency], [Year], [Num]) VALUES (7, 2, 2016, 2)
  GO
  INSERT [dbo].[_bh_test] ([Id], [Agency], [Year], [Num]) VALUES (8, 3, 2015, 1)
  GO
  INSERT [dbo].[_bh_test] ([Id], [Agency], [Year], [Num]) VALUES (9, 3, 2015, 2)
  GO
  INSERT [dbo].[_bh_test] ([Id], [Agency], [Year], [Num]) VALUES (10, 3, 2016, 2)
  GO
  SET IDENTITY_INSERT [dbo].[_bh_test] OFF
  GO
  /****** Object:  Index [UIX_Agency_Year_Num]    Script Date: 4/17/2017 10:42:15 PM ******/
  CREATE UNIQUE NONCLUSTERED INDEX [UIX_Agency_Year_Num] ON [dbo].[_bh_test]
  (
      [Agency] ASC,
      [Year] ASC,
      [Num] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  GO
  </code></pre>
  
  <p>I want the <code>Num</code> column to always have a value that is unique across that particular <code>Agency</code> when inserting. Additionally, I want it to be a number that has never been used before.</p>
  
  <p>So for example if I am inserting a value with agency 3, then a good candidate for <code>Num</code> would be 3.</p>
  
  <p>It would seem I could use a default/binding to a function that would take the MAX(Num) + 1 with a group by for Agency. However, I wasnt able to send in the Agency to the function. But futhermore if the MAX(Num) was 2 and I inserted in 3...Then deleted 3, and inserted again it would reuse a Num of 3, which is wrong because I only want it to use a Num that has never been used before.</p>
  
  <p>It sounds like an ideal solution for me would be a sequence that keeps track of a counter. Although it seems I cant used the PARTITION BY in the OVER in a SEQUENCE. Also the Num may be set manually so If the sequence was +1, and two values were inserted using the default seq. so now counter is at 2...it may be that 3 is manually inserted, and then next insert by default would be 3.  I would have to make sure to update the sequence on INSERT</p>
  
  <p>I am not sure the best approach. I think using an INSERT TRIGGER is an option but I am not sure how to get around the whole problem if the MAX() is deleted and an INSERT is done.</p>