Sql-server – Which columns to place the clustered index

You may believe primary key is unnecessary, but in my experience it is critical. You may never use it. But the database itself uses the primary key to determine PHYSICAL organization of the data. Correction: I have been corrected here - in SQL Server, the physical organization is based on the clustering index. By default a primary key IS the clustering index, so by adding a primary key, the clustered index is added and the rest of my point is still correct. But if you don't want to use an autonumber or some other primary key as the clustered index, you CAN still use some other column to cluster. You should definitely pick one, though. Check out this link: http://technet.microsoft.com/en-us/library/ms186342.aspx

Without a primary key clustered index you risk corruption of your data. And in some cases, when it seems like totally unnecessary, adding a primary key that you don't even search on will dramatically improve query performance.

here is an excellent description of the basic indexes in SQL Server. https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

Based on the basics you have given us, I would say you need something like a primary key (auto number works) that will be your clustered index, then several nonclustered indexes (on all three of your search fields), which are the typical index when you want to search on various columns that are not necessarily the primary key.

If data will be edited a lot (like when a software application is used to manage data), a lot of indexes (i.e., nonclustered indexes) will slow down insert/update/delete activity, because the indexes are updated when the table is. But if your focus is on selecting data, indexes are critical.

If this is updated in batch daily and is not part of a software system, you could have a routine which drops the indexes, does a batch insert, then recreates the indexes.

But regardless - what you want for your search fields are nonclustered indexes. Clustered indexes "cluster" an entire record around the primary key (or whatever field is chosen as the cluster basis). Nonclustered indexes are what are created when you do indexes on any other field.

Primary Key and Clustered Index are really separate concepts and, although many tables put both of these attributes on the same constraint or index, this is not a requirement.

I do not see how changing the Clustered Index to the make it also the Primary Key will particularly help your performance.

It sounds like you have a Primary Key (BIGINT) already and that it is physically implemented through a unique non-clustered index. This should be good for fairly quick lookups of the Primary Key since it would be a relatively narrow index.

As you have it defined now, the skewed Clustered Index is mostly the same special non-NULL value, with about 17.5K rows.

Since you identify the Clustered Index as a likely pain point, you might look into whether a Filtered Index or an INDEXED VIEW would provide the needed performance improvement.

Sample of a Filtered View based on MSDN, like this:

CREATE NONCLUSTERED INDEX YourNonClusteredFilteredIndex
    ON dbo.YourTable (ClusterKeyCol)
    WHERE ClusterKeyCol IS NOT NULL; -- Or <> {special value}
GO

Sample of an Index View based on MSDN, something like this:

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE VIEW dbo.YourClusteredView
WITH SCHEMABINDING
AS
    SELECT ClusterKeyCol, PrimaryKeyCol
    FROM dbo.YourTable
    WHERE PrimaryKeyCol <> {special value};
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX YourClusteredIndex 
    ON dbo.YourClusteredView (ClusterKeyCol);
GO

If you have a more detailed problem description, as John M asked, then perhaps some more targeted help can be provided.