1

Here's the code I have. The table actually has 20 more columns but I am just showing the first few:

CREATE TABLE [dbo].[Phrase]
(
    [PhraseId] [uniqueidentifier] NOT NULL,
    [PhraseNum] [int] NULL
    [English] [nvarchar](250) NOT NULL,

    PRIMARY KEY CLUSTERED ([PhraseId] ASC)
) ON [PRIMARY]
GO

From what I remember I read

Fragmentation and GUID clustered key

that it was good to have a GUID for the primary key but now it's been suggested it's not a good idea as data has to be re-ordered for each insert -- causing fragmentation.

Can anyone comment on this. Now my table has already been created is there a way to unfragment it? Also how can I stop this problem getting worse. Can I modify an existing table add NEWSEQUENTIALID?

Alan2
  • 19,668
  • 67
  • 204
  • 365
  • To defragment, execute [`ATLER INDEX...REORGANZIE`](https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql), A `NEWSEQUENTIALID` default constraint will help avoid fragmentation and help avoid the random IO penalty (an issue on spinning media). – Dan Guzman Dec 26 '17 at 00:42

2 Answers2

1

Looks like this is dup to: INT vs Unique-Identifier for ID field in database

But here's a rehash for your issue:

Rather than a guid and depending on your table depth, int or big int would be better choices, both from storage and optimization vantages. You might also consider defining the field as "int identity not null" to further help population.

GUIDs have a considerable storage impact, due to their length.

CREATE TABLE [dbo].[Phrase]
(
    [PhraseId] [int] identity NOT NULL 
          CONSTRAINT [PK_Phrase_PhraseId] PRIMARY KEY,
    [PhraseNum] [int] NULL
    [English] [nvarchar](250) NOT NULL,
....
) ON [PRIMARY]
GO
marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Mike Fabry
  • 51
  • 5
1

Thats true ,NEWSEQUENTIALID helps to completely fill the data and index pages.

But NEWSEQUENTIALID datasize is 4 times than int.So 4 times more page will be require than int.

declare @t table(col int 
,col2  uniqueidentifier DEFAULT NEWSEQUENTIALID())
insert into @t (col) values(1),(2)

select DATALENGTH(col2),DATALENGTH(col) from @t

Suppose x data page is require in case of int to hold 100 rows In case of NEWSEQUENTIALID 4x data page will be require to hold 100 rows. Therefore query will read more page to fetch same number of records.

So ,if you can alter table then you can add int identity column and make it PK+CI.You can drop or not [uniqueidentifier] as per your requirement or need.

KumarHarsh
  • 4,644
  • 1
  • 14
  • 19