Primary Key

1

Hi,

I have a table;

TableA

QuestionnaireID               PatID              Name
3                             12                  A
1                             24                  B
2                             8000                C

PatID is the Primary Key and QuestionnaireID is an identity with auto increment by 1.

Now is there a way I can have it presented as;

QuestionnaireID               PatID              Name
1                             24                  B
2                             8000                C
3                             12                  A

By retaining PatId as the Primary Key?

Thanks

2

Do you mean this?

SELECT QuestionnaireID,PatID, Name
FROM TableA
ORDER BY QuestionnaireID

If so, and you currently have records listed in PatID order that suggests to me that you do not have an ORDER BY clause in your query, and by chance you are getting Primary Key (Clustered Index) order. If so please note that whilst this may happen most (in fact "nearly all") of the time, it is not guaranteed. Therefore you should always have an ORDER BY clause in your query so that results are repeatable

3

Thanks Kristen..

What I meant is when I do the insert in the table it is done based on the PatID - I want it to be in terms of QuestionaireID so that I have a follow up 1, 2, 3, 4 etc

Presently, records are inserted based on the PatID - this resulted in the TableA #1 and not TableA #2.

Can I do anything to force records added based on the QuestionnaireID.

I hope am clearer..

Thanks

4

Looks like you want the table to return the data in the order of the second table?

I'm assuming that you've added a clustered primary key to this table on PatID, you could try changing this to be a non-clustered primary key and then add another clustered index on QuestionnaireID

As Kristen mentioned though, to guarantee an ordering you must use ORDER BY

5

My immediate reaction is "why would you care?" :slight_smile: 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
(
    PatID, Name
)
SELECT *
FROM
(
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
FROM TableA
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.