SQL SERVER – Introduction to Heap Structure – What is Heap?

Sometime simple questions are very interesting. A day ago, jr. developer asked me question : What is Heap?

In SQL Server 2005 data is stored within tables. Data within a table is grouped together into allocation unites based on their column data types, what it means is one kind of data types are stored together in allocation unites. Data within this allocation unit is stored in pages. Each pages are of size 8KB. Group of 8 pages is stored together and they are referred as Extent. Pages within a table store the data rows with structure which helps to search/locate data faster. If the data of table is not logically sorted, in other word there is no order of data specified in table it is called as Heap Structure.

If index is created on table, the data stored in table is sorted logically and it is called as clustered index. If index is created as separate structure pointing location of the data it is called non clustered index.

If you interested in learning more about Indexes read SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

14 thoughts on “SQL SERVER – Introduction to Heap Structure – What is Heap?

  1. Hi Pinal,

    In the above explanation, U have said
    “If index is created on table, the data stored in table is sorted logically and it is called as clustered index “.

    Is it not that Clustered Index would sort the data physically rather than logically. Please correct me if I am wrong.

    Thanks
    Shown

    • Shown it is right that you put a right question if u think logically. Just tell me my one question answer that Will you see that how indexing is work in server.
      As indexing work logically into the server same the answer in question said that cluster index sorting the physical data into logical manner.
      and also here sortin means not that sort ascending and descending it means that how indexing fetch data into their logical manner it has linked with data.

  2. Hi katko0ota
    A table that does not have a clustered index is referred to as a HEAP and a table that has a clustered index is referred to as a clustered table. A clustered table provides a few benefits over a heap such as physically storing the data based on the clustered index, the ability to use the index to find the rows quickly and the ability to reorganize the data by rebuilding the clustered index.

  3. CREATE TABLE [dbo].[tblWorks_RM_OPS_Comments](
    [RM_OPS_WorkCommentID] [int] IDENTITY(1,1) NOT NULL,
    [RM_OPS_WorkID] [int] NOT NULL,
    [CommentNo] [int] NULL,
    [Comments] [varchar](max) NULL,
    [CreatedBy] [varchar](8) NULL,
    [CreatedOn] [datetime] NULL,
    [EditedBy] [varchar](8) NULL,
    [EditedOn] [datetime] NULL,
    CONSTRAINT [PK_tblWorks_RM_OPS_Comments] PRIMARY KEY CLUSTERED
    (
    [RM_OPS_WorkCommentID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY]
    )

    AND

    USE [VisCore_Testing]
    GO

    /****** Object: Table [dbo].[tblWorks_RM_OPS] Script Date: 11/08/2011 16:55:43 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[tblWorks_RM_OPS](
    [RM_OPS_WorkID] [int] IDENTITY(1,1) NOT NULL,
    [ProjectCode] [varchar](4) NULL,
    [PatchCode] [varchar](6) NULL,
    [JobCheckListStatus] [varchar](10) NULL,
    [CreatedBy] [varchar](8) NULL,
    [CreatedOn] [datetime] NULL,
    [EditedBy] [varchar](8) NULL,
    [EditedOn] [datetime] NULL,
    CONSTRAINT [PK_tblWorks_RM_OPS] PRIMARY KEY CLUSTERED
    (
    [RM_OPS_WorkID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
    )

    GO

    SET ANSI_PADDING OFF
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_tblExchanges] FOREIGN KEY([ExchangeCode])
    REFERENCES [dbo].[tblExchanges] ([ExchangeCode])
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_tblExchanges]
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_tblMilestones] FOREIGN KEY([CurrentMilestoneID])
    REFERENCES [dbo].[tblMilestones] ([MilestoneID])
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_tblMilestones]
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_tblPatches] FOREIGN KEY([PatchCode])
    REFERENCES [dbo].[tblPatches] ([PatchCode])
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_tblPatches]
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_tblProjectCodes] FOREIGN KEY([ProjectCode])
    REFERENCES [dbo].[tblProjectCodes] ([ProjectCode])
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_tblProjectCodes]
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_tblRMOPSRepeatPriority] FOREIGN KEY([RepeatPriorityID])
    REFERENCES [dbo].[tblRMOPSRepeatPriority] ([RepeatPriorityID])
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_tblRMOPSRepeatPriority]
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] ADD CONSTRAINT [DF_tblWorks_RM_OPS_ExcludeFromClaimYN] DEFAULT ((0)) FOR [ExcludeFromClaimYN]
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] ADD CONSTRAINT [DF_tblWorks_RM_OPS_SubmittedForClaimYN] DEFAULT ((0)) FOR [SubmittedForClaimYN]
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] ADD CONSTRAINT [DF_tblWorks_RM_OPS_IsFREParentJob] DEFAULT ((0)) FOR [IsFREParentJob]
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] ADD CONSTRAINT [DF_tblWorks_RM_OPS_InvalidClaimYN] DEFAULT ((0)) FOR [InvalidClaimYN]
    GO

    ALTER TABLE [dbo].[tblWorks_RM_OPS] ADD CONSTRAINT [DF_tblWorks_RM_OPS_PSJobYN] DEFAULT ((0)) FOR [PSJobYN]
    GO

    PARTION
    ————–

    USE [VisCore_Testing]
    GO
    BEGIN TRANSACTION
    CREATE PARTITION FUNCTION [TBLWORKS_RM_OPS_PFN](int) AS RANGE RIGHT FOR VALUES (N’200′, N’400′)

    CREATE PARTITION SCHEME [TBLWORKS_RM_OPS_PS] AS PARTITION [TBLWORKS_RM_OPS_PFN] TO ([PRIMARY], [VisCore_RM_OPS_200TO400], [VisCore_RM_OPS_400TO600])

    ALTER TABLE [dbo].[tblWorks_RM_OPS_CheckListStatus] DROP CONSTRAINT [FK_tblWorks_RM_OPS_CheckListStatus_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_ClaimStatus] DROP CONSTRAINT [FK_tblWorks_RM_OPS_ClaimStatus_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_Comments] DROP CONSTRAINT [FK_tblWorks_RM_OPS_Comments_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_ContactDetails] DROP CONSTRAINT [FK_tblWorks_RM_OPS_ContactDetails_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_FaultHistoryDetails] DROP CONSTRAINT [FK_tblWorks_RM_OPS_FaultHistoryDetails_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_PlaceNotes] DROP CONSTRAINT [FK_tblWorks_RM_OPS_PlaceNotes_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_RequestText] DROP CONSTRAINT [FK_tblWorks_RM_OPS_RequestText_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_RFSChanges] DROP CONSTRAINT [FK_tblWorks_RM_OPS_RFSChanges_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_SandEDetails] DROP CONSTRAINT [FK_tblWorks_RM_OPS_SandEDetails_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_SignOffActionCodes] DROP CONSTRAINT [FK_tblWorks_RM_OPS_SignOffActionCodes_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_SummaryCodes] DROP CONSTRAINT [FK_tblWorks_RM_OPS_SummaryCodes_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_Tasks] DROP CONSTRAINT [FK_tblWorks_RM_OPS_Tasks_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_TrunkDetails] DROP CONSTRAINT [FK_tblWorks_RM_OPS_TrunkDetails_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_XConnectDetails] DROP CONSTRAINT [FK_tblWorks_RM_OPS_XConnectDetails_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS] DROP CONSTRAINT [PK_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS] ADD CONSTRAINT [PK_tblWorks_RM_OPS] PRIMARY KEY CLUSTERED
    (
    [RM_OPS_WorkID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [TBLWORKS_RM_OPS_PS]([RM_OPS_WorkID])

    ALTER TABLE [dbo].[tblWorks_RM_OPS_CheckListStatus] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_CheckListStatus_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_CheckListStatus] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_CheckListStatus_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_ClaimStatus] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_ClaimStatus_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_ClaimStatus] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_ClaimStatus_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_Comments] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_Comments_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_Comments] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_Comments_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_ContactDetails] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_ContactDetails_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_ContactDetails] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_ContactDetails_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_FaultHistoryDetails] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_FaultHistoryDetails_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_FaultHistoryDetails] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_FaultHistoryDetails_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_PlaceNotes] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_PlaceNotes_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_PlaceNotes] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_PlaceNotes_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_RequestText] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_RequestText_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_RequestText] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_RequestText_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_RFSChanges] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_RFSChanges_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_RFSChanges] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_RFSChanges_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_SandEDetails] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_SandEDetails_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_SandEDetails] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_SandEDetails_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_SignOffActionCodes] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_SignOffActionCodes_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_SignOffActionCodes] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_SignOffActionCodes_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_SummaryCodes] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_SummaryCodes_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_SummaryCodes] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_SummaryCodes_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_Tasks] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_Tasks_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_Tasks] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_Tasks_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_TrunkDetails] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_TrunkDetails_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_TrunkDetails] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_TrunkDetails_tblWorks_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_XConnectDetails] WITH CHECK ADD CONSTRAINT [FK_tblWorks_RM_OPS_XConnectDetails_tblWorks_RM_OPS] FOREIGN KEY([RM_OPS_WorkID])
    REFERENCES [dbo].[tblWorks_RM_OPS] ([RM_OPS_WorkID])
    ALTER TABLE [dbo].[tblWorks_RM_OPS_XConnectDetails] CHECK CONSTRAINT [FK_tblWorks_RM_OPS_XConnectDetails_tblWorks_RM_OPS]

    CREATE NONCLUSTERED INDEX [IX_tblWorks_RM_OPS_ClaimStatus] ON [dbo].[tblWorks_RM_OPS]
    (
    [ClaimStatus] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [TBLWORKS_RM_OPS_PS]([RM_OPS_WorkID])
    CREATE NONCLUSTERED INDEX [IX_tblWorks_RM_OPS_ClientRefNo] ON [dbo].[tblWorks_RM_OPS]
    (
    [ClientRefNo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [TBLWORKS_RM_OPS_PS]([RM_OPS_WorkID])
    CREATE NONCLUSTERED INDEX [IX_tblWorks_RM_OPS_CurrentMileID] ON [dbo].[tblWorks_RM_OPS]
    (
    [CurrentMilestoneID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [TBLWORKS_RM_OPS_PS]([RM_OPS_WorkID])
    CREATE NONCLUSTERED INDEX [IX_tblWorks_RM_OPS_Exchange] ON [dbo].[tblWorks_RM_OPS]
    (
    [ExchangeCode] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [TBLWORKS_RM_OPS_PS]([RM_OPS_WorkID])
    CREATE NONCLUSTERED INDEX [IX_tblWorks_RM_OPS_JobCheckListStatus] ON [dbo].[tblWorks_RM_OPS]
    (
    [JobCheckListStatus] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [TBLWORKS_RM_OPS_PS]([RM_OPS_WorkID])

    COMMIT TRANSACTION

    PARTITIONII
    ——————

    USE [VisCore_Testing]
    GO
    BEGIN TRANSACTION
    ALTER TABLE [dbo].[tblWorks_RM_OPS_Comments] DROP CONSTRAINT [PK_tblWorkComments_RM_OPS]

    ALTER TABLE [dbo].[tblWorks_RM_OPS_Comments] ADD CONSTRAINT [PK_tblWorkComments_RM_OPS] PRIMARY KEY NONCLUSTERED
    (
    [RM_OPS_WorkCommentID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)ON [PRIMARY]

    CREATE CLUSTERED INDEX [ClusteredIndex_on_TBLWORKS_RM_OPS_PS_634563642643124346] ON [dbo].[tblWorks_RM_OPS_Comments]
    (
    [RM_OPS_WorkID]
    )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [TBLWORKS_RM_OPS_PS]([RM_OPS_WorkID])

    DROP INDEX [ClusteredIndex_on_TBLWORKS_RM_OPS_PS_634563642643124346] ON [dbo].[tblWorks_RM_OPS_Comments] WITH ( ONLINE = OFF )

    CREATE NONCLUSTERED INDEX [IX_tblWorks_RM_OPS_Comments_CNo] ON [dbo].[tblWorks_RM_OPS_Comments]
    (
    [CommentNo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
    DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
    ON [TBLWORKS_RM_OPS_PS]([RM_OPS_WorkID])

    CREATE NONCLUSTERED INDEX [IX_tblWorks_RM_OPS_Comments_WorkID] ON [dbo].[tblWorks_RM_OPS_Comments]
    (
    [RM_OPS_WorkID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
    DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
    ON [TBLWORKS_RM_OPS_PS]([RM_OPS_WorkID])

    COMMIT TRANSACTION

    ———

  4. Let me ask you a question too. Why do you call yourself “SQL Authority” when you know so little about heap? And if you know more, why don’t you tell people more about it?

    An “authority” is something else than someone on the internet trying to teach people.

  5. This site has provided solutions for many readers including this one, and is one among others I check for opinion. No site is perfect, we should just be thankful that sites like this exist and people like Pinal who devotes time to be helpful to others.

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #022 | SQL Server Journey with SQL Authority

  7. Some other SQL dude
    Let me ask you a question too. Why do you call yourself “SQL Authority” when you know so little about heap? And if you know more, why don’t you tell people more about it?

    An “authority” is something else than someone on the internet trying to teach people.

    You probably googled “what is a heap?” and landed here. I have found this blog very useful and value the input from Pinal. His articles are written with the end user in mind and have been ranked very in the search engines. Ask yourself why?

    Pinal – Please do not let ungrateful people like above put you off. I have learned a lot from your blogs.

    Thanks you.

    A

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s