+-----------+--------------------------------------+--------------+
| DesignKey |            DesignTypeGuid            | DesignNumber |
+-----------+--------------------------------------+--------------+
|      2312 | 4FB560B0-E867-46B2-B116-338AD48C97AC |            3 |
|      2313 | 4FB560B0-E867-46B2-B116-338AD48C97AC |            7 |
|      2314 | 4FB560B0-E867-46B2-B116-338AD48C97AC |            8 |
+-----------+--------------------------------------+--------------+

DECLARE @CurrentChangeOrderDesignNumber INT =
              (SELECT [DesignKey], [DesignNumber]
                FROM [Design]
                WHERE ParentDesignKey = @DesignKey
                AND DesignTypeGuid = @COTypeGuid ORDER BY DesignKey);

+-----------+--------------------------------------+--------------+
| DesignKey |            DesignTypeGuid            | DesignNumber |
+-----------+--------------------------------------+--------------+
|      2312 | 4FB560B0-E867-46B2-B116-338AD48C97AC |            1 |
|      2313 | 4FB560B0-E867-46B2-B116-338AD48C97AC |            2 |
|      2314 | 4FB560B0-E867-46B2-B116-338AD48C97AC |            3 |
+-----------+--------------------------------------+--------------+

SELECT [DesignKey], [DesignNumber],
       ROW_NUMBER() OVER (PARTITION BY ParentDesignKey, DesignTypeGuid ORDER BY DesignNumber) as new_DesignNumber
FROM [Design]
WHERE ParentDesignKey = @DesignKey AND
      DesignTypeGuid = @COTypeGuid
ORDER BY DesignKey);