SQL Indexing on uniqueidenfier column is not working

Question

SQL Indexing on uniqueidenfier column is not working

33 Views Asked by Xylan Daniel At 05 July 2022 at 06:31

I am trying to put the index to a uniqueidentifier column.

Here is the table schema and data:

CREATE TABLE [dbo].[TestTable]
(
    [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ParentId] [uniqueidentifier] NULL,
    [Description] [varchar](50) NULL,

    CONSTRAINT [PK_TestTable] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'4a8782d7-1cfc-ec11-939e-089204240d4d', NULL, N'A')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'4b8782d7-1cfc-ec11-939e-089204240d4d', NULL, N'B')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'4c8782d7-1cfc-ec11-939e-089204240d4d', NULL, N'C')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'4d8782d7-1cfc-ec11-939e-089204240d4d', NULL, N'D')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'4e8782d7-1cfc-ec11-939e-089204240d4d', NULL, N'E')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'39a36c39-1dfc-ec11-939e-089204240d4d', N'4a8782d7-1cfc-ec11-939e-089204240d4d', N'AA')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'3aa36c39-1dfc-ec11-939e-089204240d4d', N'4a8782d7-1cfc-ec11-939e-089204240d4d', N'AB')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'3ba36c39-1dfc-ec11-939e-089204240d4d', N'4a8782d7-1cfc-ec11-939e-089204240d4d', N'AC')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'3ca36c39-1dfc-ec11-939e-089204240d4d', N'4a8782d7-1cfc-ec11-939e-089204240d4d', N'AD')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'3da36c39-1dfc-ec11-939e-089204240d4d', N'4a8782d7-1cfc-ec11-939e-089204240d4d', N'AE')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'3ea36c39-1dfc-ec11-939e-089204240d4d', N'4a8782d7-1cfc-ec11-939e-089204240d4d', N'AF')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'e5bda860-1dfc-ec11-939e-089204240d4d', N'4a8782d7-1cfc-ec11-939e-089204240d4d', N'AG')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'e6bda860-1dfc-ec11-939e-089204240d4d', N'4a8782d7-1cfc-ec11-939e-089204240d4d', N'AH')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'e7bda860-1dfc-ec11-939e-089204240d4d', N'4a8782d7-1cfc-ec11-939e-089204240d4d', N'AI')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'e8bda860-1dfc-ec11-939e-089204240d4d', N'4a8782d7-1cfc-ec11-939e-089204240d4d', N'AJ')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'64e3497a-1dfc-ec11-939e-089204240d4d', N'4b8782d7-1cfc-ec11-939e-089204240d4d', N'BA')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'65e3497a-1dfc-ec11-939e-089204240d4d', N'4b8782d7-1cfc-ec11-939e-089204240d4d', N'BB')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'66e3497a-1dfc-ec11-939e-089204240d4d', N'4b8782d7-1cfc-ec11-939e-089204240d4d', N'BC')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'67e3497a-1dfc-ec11-939e-089204240d4d', N'4b8782d7-1cfc-ec11-939e-089204240d4d', N'BD')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'68e3497a-1dfc-ec11-939e-089204240d4d', N'4b8782d7-1cfc-ec11-939e-089204240d4d', N'BE')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'69e3497a-1dfc-ec11-939e-089204240d4d', N'4b8782d7-1cfc-ec11-939e-089204240d4d', N'BF')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'6ae3497a-1dfc-ec11-939e-089204240d4d', N'4b8782d7-1cfc-ec11-939e-089204240d4d', N'BG')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'6be3497a-1dfc-ec11-939e-089204240d4d', N'4b8782d7-1cfc-ec11-939e-089204240d4d', N'BH')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'6ce3497a-1dfc-ec11-939e-089204240d4d', N'4b8782d7-1cfc-ec11-939e-089204240d4d', N'BI')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'6de3497a-1dfc-ec11-939e-089204240d4d', N'4b8782d7-1cfc-ec11-939e-089204240d4d', N'BJ')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'42f09aa5-1dfc-ec11-939e-089204240d4d', N'4c8782d7-1cfc-ec11-939e-089204240d4d', N'CA')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'43f09aa5-1dfc-ec11-939e-089204240d4d', N'4c8782d7-1cfc-ec11-939e-089204240d4d', N'CB')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'44f09aa5-1dfc-ec11-939e-089204240d4d', N'4c8782d7-1cfc-ec11-939e-089204240d4d', N'CC')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'45f09aa5-1dfc-ec11-939e-089204240d4d', N'4c8782d7-1cfc-ec11-939e-089204240d4d', N'CD')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'46f09aa5-1dfc-ec11-939e-089204240d4d', N'4c8782d7-1cfc-ec11-939e-089204240d4d', N'CE')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'47f09aa5-1dfc-ec11-939e-089204240d4d', N'4c8782d7-1cfc-ec11-939e-089204240d4d', N'CF')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'48f09aa5-1dfc-ec11-939e-089204240d4d', N'4c8782d7-1cfc-ec11-939e-089204240d4d', N'CG')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'49f09aa5-1dfc-ec11-939e-089204240d4d', N'4c8782d7-1cfc-ec11-939e-089204240d4d', N'CH')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'4af09aa5-1dfc-ec11-939e-089204240d4d', N'4c8782d7-1cfc-ec11-939e-089204240d4d', N'CI')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'4bf09aa5-1dfc-ec11-939e-089204240d4d', N'4c8782d7-1cfc-ec11-939e-089204240d4d', N'CJ')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'4cf09aa5-1dfc-ec11-939e-089204240d4d', N'4d8782d7-1cfc-ec11-939e-089204240d4d', N'DA')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'4df09aa5-1dfc-ec11-939e-089204240d4d', N'4d8782d7-1cfc-ec11-939e-089204240d4d', N'DB')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'4ef09aa5-1dfc-ec11-939e-089204240d4d', N'4d8782d7-1cfc-ec11-939e-089204240d4d', N'DC')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'4ff09aa5-1dfc-ec11-939e-089204240d4d', N'4d8782d7-1cfc-ec11-939e-089204240d4d', N'DD')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'50f09aa5-1dfc-ec11-939e-089204240d4d', N'4d8782d7-1cfc-ec11-939e-089204240d4d', N'DE')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'51f09aa5-1dfc-ec11-939e-089204240d4d', N'4d8782d7-1cfc-ec11-939e-089204240d4d', N'DF')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'52f09aa5-1dfc-ec11-939e-089204240d4d', N'4d8782d7-1cfc-ec11-939e-089204240d4d', N'DG')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'53f09aa5-1dfc-ec11-939e-089204240d4d', N'4d8782d7-1cfc-ec11-939e-089204240d4d', N'DH')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'54f09aa5-1dfc-ec11-939e-089204240d4d', N'4d8782d7-1cfc-ec11-939e-089204240d4d', N'DI')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'55f09aa5-1dfc-ec11-939e-089204240d4d', N'4d8782d7-1cfc-ec11-939e-089204240d4d', N'DJ')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'56f09aa5-1dfc-ec11-939e-089204240d4d', N'4e8782d7-1cfc-ec11-939e-089204240d4d', N'EA')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'57f09aa5-1dfc-ec11-939e-089204240d4d', N'4e8782d7-1cfc-ec11-939e-089204240d4d', N'EB')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'58f09aa5-1dfc-ec11-939e-089204240d4d', N'4e8782d7-1cfc-ec11-939e-089204240d4d', N'EC')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'59f09aa5-1dfc-ec11-939e-089204240d4d', N'4e8782d7-1cfc-ec11-939e-089204240d4d', N'ED')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'5af09aa5-1dfc-ec11-939e-089204240d4d', N'4e8782d7-1cfc-ec11-939e-089204240d4d', N'EE')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'5bf09aa5-1dfc-ec11-939e-089204240d4d', N'4e8782d7-1cfc-ec11-939e-089204240d4d', N'EF')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'5cf09aa5-1dfc-ec11-939e-089204240d4d', N'4e8782d7-1cfc-ec11-939e-089204240d4d', N'EG')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'5df09aa5-1dfc-ec11-939e-089204240d4d', N'4e8782d7-1cfc-ec11-939e-089204240d4d', N'EH')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'5ef09aa5-1dfc-ec11-939e-089204240d4d', N'4e8782d7-1cfc-ec11-939e-089204240d4d', N'EI')
INSERT [dbo].[TestTable] ([Id], [ParentId], [Description]) VALUES (N'5ff09aa5-1dfc-ec11-939e-089204240d4d', N'4e8782d7-1cfc-ec11-939e-089204240d4d', N'EJ')

ALTER TABLE [dbo].[TestTable] 
    ADD CONSTRAINT [DF_TestTable_Id] DEFAULT (newsequentialid()) FOR [Id]

I am trying to add a non-clustered index into this table

CREATE NONCLUSTERED INDEX IDX_TestTable_ParentId
ON dbo.TestTable (ParentId asc);

Before Add index

The number of rows read = 55 (all records in table)

After added the index, it is remain reading 55 records:

After Add index to ParentId

What is the proper way to create the index for this?

I need to speed up the query search for example

SELECT * 
FROM TestTable 
WHERE ParentId = '4E8782D7-1CFC-EC11-939E-089204240D4D'

Original Q&A

SQL Indexing on uniqueidenfier column is not working

There are 0 best solutions below

Related Questions in SQL

Related Questions in INDEXING

Related Questions in CLUSTERED-INDEX

Related Questions in NON-CLUSTERED-INDEX

Trending Questions

Popular # Hahtags

Popular Questions