WITH ctePerson AS (
SELECT *
  , nextVisitDate = LEAD(p.VisitDate, 1, '2199-12-31') OVER (PARTITION BY PID ORDER BY p.VisitDate)
FROM #person AS p

Is there a reason why the CLUSTERED key is on the IDENTITY field?

Based purely on this query, you could look at changing the PRIMARY KEY to a NON-CLUSTERED...

You can try something like this.  NOTE: The lack of data or expected results means that this code is untested.
WITH cteDateCalc AS (
  SELECT
   trackid

Since this is a SQL 2008 Forum, I assume that you are using SQL 2008.  In this case a SEQUENCE object will not work.

You could use a watermark...

Your first problem is that you have tag names that include mathematical operators.  How do you know whether SC-1 is "SC" minus 1, or just the tag "SC-1".

Once...

I used the following code, and get 4 results from the sample XML that you posted.

DECLARE @CAQH_Return_XML TABLE (FileInfo XML);

INSERT INTO @CAQH_Return_XML(FileInfo)
VALUES...

Please post readily usable code.  A picture of the xml doesn't help. I have added the Provider root, as well as the NPI and LastName nodes to your sample data.

MSDN - DROP INDEX (Transact-SQL)

2 Possible options ...

DECLARE @SeedVal INT = ISNULL((SELECT LID_ID_NB from LastIDNumber WHERE LID_KET_NM = 'TableName'), 0) +1;
DBCC CHECKIDENT ( TableName, RESEED, @SeedVal ) WITH...


SELECT TOP(1) ID
FROM YourTable
ORDER BY StartDate DESC;