Sql-server – Are there any performance benefits to using a hash table with no clustered index

database-designheapindexsql serversql-server-2008

I have this table:

CREATE TABLE [dbo].[relatea] (
   [mid] [varchar](16) NOT NULL,
   [sid] [varchar](16) NOT NULL
)

It stores hash matches. Is there any benefit to having this as a heap? It has around 7 million rows, and the values are not unique in either column. I know heaps are generally never good for any large tables. This table currently has no indexes.

I was thinking of altering the table to:

CREATE TABLE [dbo].[relatea] (
   [mid] [varchar](16) NOT NULL,
   [sid] [varchar](16) NOT NULL,

   CONSTRAINT [pk_relatea] PRIMARY KEY CLUSTERED (
      [mid] ASC,
      [sid] ASC
   )
)

Table queries are generally but not always on sid, and the combination of mid and sid should always be unique.

Is it better to create a clustered index like this, as opposed to leaving the table as a heap?

Best Answer

I would put a clustered index (not a primary key) on the sid column. If you do lookups against the mid column sometimes as well then add an index to the mid column with the sid column as an included index.

If you don't want to create a clustered index, then two non-clustered indexes one on each column with the other column as an included column should give you the same basic read performance.

Related Solutions

Sql-server – Best way to index a table with two datetime columns that are equally queried

There is no substitute for benchmarking. To answer the question, I would create and populate several possible tables. Then I would expose these tables to your typical workload, and benchmark.

Including additional columns in you NCIs will slow down modifications and speed up selects. Based on frequency of both, we can choose which approach uses less resources. If a row is on average read twice per year, your conclusions might be different as compared to the case when every row is on average read twice per minute.

Besides, not all queries are born equal. If some queries must complete in certain time no matter what, then you must make sure these requirements are met. Obviously such requirement trump the common good approach described above. Only you can know the actual requirements.

Sql-server – Moving a heap between filegroups

You're going to have to physically move the data - there is no getting around that. But I don't know who came up with the idea that doing so requires creating a clustered index - this just exchanges certain steps for others, and isn't going to require any different amounts of temporary space to accomplish than the method I outline below.

Why not create a second, empty heap on the new filegroup:

CREATE TABLE dbo.whatever_copy
(
  ... cols ...
)
ON [second filegroup];

Then script the indexes, constraints etc. from the original table, copy the data over, remove any constraints that need to be removed (e.g. foreign keys pointing to the original table), then drop the original table, and rename the new one. Finally, add the indexes and constraints to the new table.

 -- script out indexes, constraints etc.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

INSERT dbo.whatever_copy(cols) WITH (TABLOCK)
  SELECT cols FROM dbo.whatever;

 -- remove constraints etc.

DROP TABLE dbo.whatever;

EXEC sp_rename N'dbo.whatever_copy', N'whatever', N'OBJECT';

 -- add constraints etc.

COMMIT TRANSACTION;

Best Answer

Related Solutions

Sql-server – Best way to index a table with two datetime columns that are equally queried

Sql-server – Moving a heap between filegroups

Related Question