SQL Server one-to-many and index fragmentation I'm currently writing code to update an entities child collection (one-to-many relationship), and while thinking about how to write code to determine which entities have been added/removed/modified, I realized that not only is it easier to just  recreate the whole list, but probably better for SQL server performance?

Let's say I have a table Student with a one-to-many relationship to Course, and I have there 2 courses Math and Physics, with primary keys 1 and 2 respectively, and obviously a foreign key to Student which is 1.

If I one day decide to update the first course, remove the other, and add a new one, I would end up with the following entities


Math (upated), primary key is: 1
Physics (deleted), primary key was: 2
History (added), primary key is: 5000


Now the 2 courses are no longer next to each other, and this probably causes big performance problems in the long run since my keys are now fragmented, is this correct?

A related question is whether I should have a primary key on the Course table at all? I usually add it by default to all of my (except association) tables, but in this case I never query courses individually, but always in the context of a student. Does it make sense to keep the primary key, even if I know I'll never reference it from anywhere? Would the lack of a primary key hurt performance?

EDIT: I've decided to keep the primary key, in-case in the future I do need to query this child table without the parent.

My primary keys are integers and have a clustered index (default on SQL Server) and I'm using SQL Server 2016, and Entity Framework 6 as my ORM.

UPDATE:

Here's my CREATE TABLE from SSMS. I'm using EF Code-First so I didn't write any of this by hand.

CREATE TABLE [dbo].[Courses](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [StudentId] [uniqueidentifier] NOT NULL,
    [Fk1Id] [int] NOT NULL,
    [Fk2Id] [int] NOT NULL,
    [Fk3Id] [int] NULL,
 CONSTRAINT [PK_dbo.Courses] PRIMARY KEY CLUSTERED 
 (
     [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Courses]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Courses_dbo.Students_StudentId] FOREIGN KEY([StudentId])
REFERENCES [dbo].[Students] ([Id])
GO

ALTER TABLE [dbo].[Courses] CHECK CONSTRAINT [FK_dbo.Courses_dbo.Students_StudentId]
GO
--Omitted other alter tables for Fk1Id,Fk2Id, and Fk3Id


StudentId is the foreign key that I will always use to fetch these rows, they are not relevant in any other context (at least for now).

I have renamed the table and column names for privacy reasons, and removed 3 foreign key columns for simplicity! I hope they're not too relevant.
A related question is whether I should have a primary key on the
  Course table at all? I usually add it by default to all of my (except
  association) tables, but in this case I never query courses
  individually, but always in the context of a student. Does it make
  sense to keep the primary key, even if I know I'll never reference it
  from anywhere? Would the lack of a primary index hurt performance?


Every table should have Primary Key/Clustered Index.Since ID is seldom use in join condition.You can drop Clustered index from ID column.

Make Studentid +courseID in Course table as Clustered index.

In Future or Present, Studentid will be most frequently use in join condition and in all important query.On ther hand Course ID column will be very less use and not so important query.

I think [StudentId] [uniqueidentifier]  should have been INT or Bigint.


  Now the 2 courses are no longer next to each other, and this probably
  causes big performance problems in the long run since my keys are now
  fragmented, is this correct?


You can rebuild index.It is very normal in database.So when index is rebuild then id=1 and next id=5000 will be very much together.

First of all you can reconsider [Description] [nvarchar](max) NOT NULL 

If it is possible to make it VARchar (2000),(3000) etc.

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.

Is manually keeping the Id's together bad practice? 


Just because there is gap in ids that doesn't mean it is Index Fragmentation or it will hurt performance in any way.Gap in id is ok.Even if ids are continuous there will be index fragmentation.

Index Fragmentation