Can a Clustered Index based on a key field be more performant than a non-clustered covering index for a big table

Question

I am researching a DW reporting table that will grow very large. For simplicity, I will show the table as follows:

BigTable
--------
TableID INT IDENTITY NOT NULL,
CompanyName NVARCHAR(100) NOT NULL

Every query will use the company name to query within a partition of data (not a physical partition).

Since this table may contain over a billion rows and each company will have a pretty even distribution of data, querying by company should be as fast as possible. I am in the stages of setting up a few tests, but before doing so I thought I would ask and see if it would be a waste of time.

My idea was to determine that if each company's partition of data laid next to each other on disk via a clustered index, would data retrieval be any faster than just using a non-clustered index to cover CompanyName.

Example 1: Here is the variation where the IDENTITY column is the PK but not CLUSTERED. The CompanayName and TableID combine to make the Clustered Index so the data will be ordered by company on disk.

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY NONCLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE UNIQUE CLUSTERED INDEX [CLUSTERED_ByCompanyName_TableID] ON [dbo].[BigTable]
(
    [CompanyName] ASC,
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

And here is the traditional way of creating tables with covering indexes.

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY CLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ByCompanyName] ON [dbo].[BigTable]
(
    [CompanyName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Does anyone know right away if there would be any performance improvement to be had in using the first example over the second example?

EDIT: I am leaning towards using a clustered index with Company. The TableID is just autoincrement field to use as a PK if a row needs a unique reference. I feel that clustered Index Seeks/Scans are faster than Index Scan(s)/Seek(s).

I wish you could easily partition, or shard based on something like companyid.

A basic query would be in the form of

SELECT
    SUM(FieldA) OVER (PARTITION BY ...) a,
    COUNT(1) OVER (PARTITION BY...) b
    ...       
FROM
    BigTable 
WHERE
    CompanyName = 'NABISCO'
GROUP BY
   ....
ORDER BY
   ....

Answer 1

Here's an attempt to answer, based on the comments.

In summary, you say that you always filter on CompanyName for your queries.

Having a clustered index on CompanyName, TableID can indeed be beneficial, since SQL Server can navigate in the "data" to the right company and then only read the rows for this particular company.

Consider using data compression. And make sure you evaluate both Row and Page compression. People tend do forget about Row compression, but considering its almost non-existing overhead it can be a very attractive type of compression in some cases.

Having a columnstore index can be even more beneficial. Partly because of the even higher compression ratio compared to none, row or page compression. But also because you are more likely to see batch mode for your operators on the execution plan. You can get batch mode without columnstore indexes in 2019, but it requires 2019 database compatibility level and Enterprise Edition.

You want to cover the query with the columnstore index. Either a non-clustered which has all the columns that your queries need. Or probably more attractive in your case a clustered columnstore index - where you now realize also the storage savings for the columnstore index.

One aspect will be how the rows are laid out in the row groups (a row group is about 1 million rows, depending on how you load new data etc). You want to "cluster" this based on the company. Search for Company A, and if the rows for company A are limited to a small set of rowgroups you can now get a nice rowgroup elimination in run-time (aka segment elimination). SQL Server has meta-data for the lowest and highest value for each column and each rowgroup. When you create the index, you will make sure that SQL Server "happens" to read the rows in the desired order - by having a row-clustered index on that column and creating the columnstore index using CREATE INDEX ... WITH DROP EXISTING (basically converting the row clustered index to a column clustered index). Next version of SQL Server is planned to have support for an ORDER clause in the syntax for CREATE INDEX for this purpose.

There are limitations regarding row group elimination and data types. I believe that you don't get elimination for string data types yet. I.e., carefully consider if this will be a CompanyName or CompanyID column! The next version is planned to extend on the type support for rowgroup elimination.

And then there's the aspect when you add data. Add a bunch of rows, which probably are for many of your customers and they will be in the same rowgroup(s) - and this row group will now have to be read for your forthcoming queries. I.e., the index will "degrade" over time if you add data when it comes to rowgroup elimination, leaving you with the decision whether to rebuild the index (which again is tad tricky so far because we lack some ORDER clause) in order to re-cluster the rows based on the company.