Microsoft SQL Server : existing table new PK & index issue

Question

I have an old database that unfortunately a choice was made when it was designed that one of the largest tables, which contains photos (table called Photos) and has a PK of type uniqueidentifier. This table has 195k rows and obviously nearly 100% fragmentation. I would like to build a new PK on this table, but have some questions and considerations

Data Background

The row size of the table is not expected to grow beyond the bounds of (int). Additionally there are no FK relationships with the PK of the Photos table. As stated, there are 195k rows in the Photos table. There is a potential consideration: there is a non-unique column in Photos called Job_ID which has FK relationship with the PK of another table called DeliveryTicket. The nature of that key is such that it's a an incrementing counter (int). There is a one->many relationship between the PK Job_ID in table DeliveryTicket and the FK Job_ID in table Photos.

My concerns stem from a perspective of fragmentation, and indexing, and my questions are:

1. How to add the new column New_ID to Photos with a PK constraint as non-null AND populate existing rows with a new incrementing index of type (int)?

2. Should I, and how do I, create a new clustered index on the FK value on the Photos table? Note that it is expected by way of the program logic that most queries to the Photos table will be on the FK value Job_ID, but never on either the existing or any new PK of the Photos table. The fastest index for the table Photos would be on the FK relationship column Job_ID.

3. Is there a better approach to say somehow create a new PK that counts up from the lowest (non-unique on Photos) value of Job_ID, such that the default index (the PK) would be the fastest index?

Here is the CREATE TABLE statement for Photos:

CREATE TABLE [dbo].[Photos]
(
    [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
    [Photo_Serial_Number] [int] IDENTITY(1,1) NOT NULL,
    [Job_ID] [int] NOT NULL,
    [Photo_Upload_Disposition_Type] [int] NOT NULL,
    [NET_Rating] [real] NULL,
    [Rating_ID] [int] NULL,
    [Image_Data] [varbinary](max) FILESTREAM  NOT NULL,
    [File_Name] [nvarchar](50) NOT NULL,
    [File_Extension] [varchar](5) NOT NULL,
    [Image_Comments] [nvarchar](1000) NULL,

     CONSTRAINT [PK_Photos] PRIMARY KEY CLUSTERED (ID)
);

I am in the exploratory phase. This is a production DB, and while it will be backed up and changes tested, as it has to happen during a maintenance windows, no such changes have yet been made.

Answer 1

This table has 195k rows and obviously nearly 100% fragmentation.

So what? Do you have a measurable performance issue? You could simply add a default of NEWSEQUENTIALID to generate sequential GUIDs to reduce fragmentation going forward.