I have a question about Surrogate keys, say i have two tables(in sql server):

Table A: Student_ID, Name, School, Section
Table B: Book_ID,Student_ID, IssueDate, BookName

If Student_ID is a clustered surrogate key and Name is unclustered index. How would one manually enter data into Table B without knowing the Student_ID. I know i might have to do a lookup in table A to find the IDs but that would not be efficient. Is there any way to make this efficient? I am trying to think how surrogate keys essentially work in terms of efficiency as a foreign key when considering inserts.

  • Why the emphasis on manual operations? If you wrote a simple app to intermediate, you could have the user enter the name,let the app ask for disambiguation if needed, and lookup the student ID in Table A before doing the insert into Table B. – Walter Mitty Jan 20 '17 at 17:41

Assuming Student_ID is an identity

INSERT INTO [student] ([name]) VALUES ('penny');
declare @studentID int =  (select @@IDENTITY);
insert into studentBood (studentID, bookID) values (@studentID, 1);

insert into [studentBook] ([studentID], [bookID]) 
select [student].[iden], 13
from [dbo].[student] 
where name = 'penny';

select * from studentBook

select * from student
| improve this answer | |

You could just add the name of the student in Table B instead of Student_ID but that would not be normalized and you would be duplicating data, also if you don´t have constraints it may be possible for you to add students that are not registered in Table A. Name column for sure will use more bytes than Student_ID. Lookup will be efficient since you´re using Student_ID as clustered index in Table A. If you make queries filtering by that column your search would be fast, SQL Engine will use an index seek operation in the execution plan and even if the table is small SQL will put the entire table in cache (RAM) so it won´t have to go and read from disk. For a transactional app your design is fine, for a data-warehouse you may use a denormalized design where duplicating data is allowed.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.