2

I created a table importing an Excel sheet with 600,000 rows. Most of the queries I will run will be using C_CustomerID, they will be like the example below:

select * from testtable where C_CustomerID = 12345678

The table definition is:

CREATE TABLE [dbo].[testtable](
    [id_card] [int] IDENTITY(1,1) NOT NULL,
    [C_CustomerID] [int] NOT NULL,
    [C_AccountID] [nvarchar](255) NULL,
    [C_ProductID] [varchar](20) NULL,
    .......... more columns here ...     
) ON [PRIMARY]

I created an index:

CREATE NONCLUSTERED INDEX [inx_profitability] ON [dbo].[testtable]
(
    [C_CustomerID] ASC
)

I will only import more records later on from an Excel sheet. Will export about 500,000 new records every 6 months.

Is there anything I can do to increase performance in the future once table gets bigger?

I am running SQL Server 2012 and Microsoft SQL Server Management Studio 11.0.3128.0.

  • 1
    How many rows per a C_CustomerID? Without this we can only speculate... Apart from that, you should probably add a PK and a clustered index to the table regardless. – dean Oct 19 '17 at 8:38
  • 2
    Bigger as in "10 billion rows or more"? You seem to think you have a lot of data. 20 years ago desktop databases handled 1 mllion rows without issues - since then we have come a great deal. SQL Server does not require anything special to deal with billions of rows. Tens of billions of rows. – TomTom Oct 20 '17 at 12:25
6

If all queries are of the form:

select * from testtable where C_CustomerID = 12345678

then what you have already is not entirely unreasonable. There is a cost to retrieving columns not covered by the nonclustered index from the base table (currently a heap), but unless there are many rows per C_CustomerID, this may not be a practical concern.

That said, the current arrangement is not ideal. There is a chance that SQL Server will decide to scan the whole heap for matches, rather than seek into the nonclustered b-tree index and then perform multiple single-row lookups.

In the absence of any other considerations, I would probably replace the nonclustered index with a (non-unique) clustered index:

DROP INDEX inx_profitability ON dbo.testtable;
GO
CREATE CLUSTERED INDEX [CX dbo.testtable C_CustomerID]
ON dbo.testtable (C_CustomerID);

The clustered index defines the logical ordering of the rows in the table, and provides direct access to the whole row (so no lookups needed). To a large extent, a clustered index is largely 'free', since it is not a separate structure with a copy of (some of) the data.

For rows with the same C_CustomerID, SQL Server will add a hidden integer 'uniquifier' to ensure each row is uniquely identifiable internally. You cannot reference this value directly, but it does add a small overhead.

Making this a clustered table (by adding the clustered index) will also likely improve unused future space management for the table, in the event you delete rows (the question mentions an export).

You might choose to specify a fill factor on the clustered index build above (and when performing maintenance) e.g. by adding WITH (FILLFACTOR = 70). This will reserve a proportion of free space at the leaf levels for future inserts (remember the rows and pages are logically ordered by C_CustomerID). The choice of 70 is entirely arbitrary. The value you choose (if any) is entirely situation-dependent. If all queries are as shown above, there is probably little need to specify a fill factor.

In principle, every table should have a key (primary or otherwise) that enforces whatever real-world combination of columns uniquely identifies each row. This could be a nonclustered primary key or unique constraint in your case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.