title: How to find largest sequence of consecutive null values in a column
body: <p><a href="https://i.stack.imgur.com/hKPCz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hKPCz.png" alt="Sample"></a></p>
  
  <p>I want to get the maximum consecutive occurrence of null in a column as shown in the screenshot. As there is ID column also there in this table, ordering could be done based on it. 
  Like in this case, the output would be 4 as maximum 4 nulls are together.</p>
  
  <pre><code>SELECT * INTO #tmp FROM (Select 1 AS ID, 1 as lvl union all
  Select 2, 1 union all
  Select 3, Null union all
  Select 4, Null union all
  Select 5, 1 union all
  Select 6, 1 union all
  Select 7, Null union all
  Select 8, Null union all
  Select 9, Null union all
  Select 10, Null union all
  Select 11, 1 union all
  Select 12, 1 union all
  Select 13, 1 union all
  Select 14, 1)x
  SELECT * FROM #tmp
  DROP TABLE #tmp
  </code></pre>
  
  <p>I just dont know from where to start, so any help would be appreciable.</p>