My immediate reaction is "why would you care?" because SQL will happily Order then for you when you SELECT them....
You could pre-sort the data you are inserting, you can do this during the INSERT:
INSERT INTO TableA
SELECT 24 AS PatID, 'B' AS Name UNION ALL
SELECT 8000, 'C' UNION ALL
SELECT 12, 'A'
) AS T
ORDER BY PatID, Name
But I suspect I am answering the wrong question ... ??
Even if you, initially, insert the rows "in order", by PatID, then when you insert another row with a PatID value amongst the existing values then surely the rows will be out-or-order byQuestionnaireID??
If you want them physically ordered, on disk, in QuestionnaireID order then create the Primary Key on PatID NONCLUSTERED and create a CLUSTERED index on QuestionnaireID. That will make retrieval by QuestionnaireID more efficient (particularly if you select based on a RANGE)
If you want a Row Number (1, 2, 3, ...) sequenced by PatID perhaps?? then use this instead of QuestionnaireID
SELECT Row_Number OVER (ORDER BY PatID) as RowNo, PatID, Name
ORDER BY RowNo
If you want to insert into TableA and then insert related data into TableB (linking on the allocated IDENTITY value assigned to QuestionnaireID in TableA) then you can use scope_identity() after insert to TableA to retrieve the allocated IDENTITY value.
Sorry, I'm answering all sorts of might-be-this questions because I haven't fully understood your requirement.