Indexing Tables

Question

Indexing Tables

water490

Say Hey Kid

Points: 680
More actions
March 19, 2023 at 12:30 am

#4162391

Hi everyone
I need to construct indexes for two tables.
Table 1 - 30 Fields
There are about 8 fields that are used in various SP where these fields are used for sorting and joining tables
Table 2 - 7 Fields
There are 3 fields that are used in various SP where these fields are used for sorting and joining tables
I know the topic of indexing tables is quite complicated and is a huge topic on its own. I need some guidance from experts on how I can construct the indexes. Should I be using clustered, columnstore etc. There are many options.
The DB is being used for analysing stock market data and there is no ecommerce type of workload. It is largely creating data so it can be consumed by a data visualization tool I have. Table 1 has about 50 million records. Table 2 has about 3000 records.
Can someone please help me decide on the appropriate indexing strategy?
Thank you
March 21, 2023 at 11:34 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

water490 Say Hey Kid Points: 680 More actions · Answer 1

I have a script that was running fine but today it is taking way too long. I looked at the script and I see these errors:

"cannot specify included columns for a clustered index"

How do I fix this?

Thank you

pietlinden SSC Guru Points: 63319 More actions · Answer 2

Can't fix code we can't see. Can you post the CREATE TABLE and then CREATE INDEX scripts?

Ken McKelvey SSCoach Points: 18598 More actions · Answer 3

water490 wrote:

"cannot specify included columns for a clustered index"

As the clustered index contains all the columns why would you want to include them?

water490 wrote:

I have a script that was running fine but today.

Maybe the clustered index has been changed - see above.

homebrew01 SSC Guru Points: 55506 More actions · Answer 4

Are these tables already being used ?

If so, Identify the "trouble areas" and look at how to fix them, rather than just build indexes you think might be needed.

Emperor100 SSCommitted Points: 1892 More actions · Answer 5

From the error message, looks like you are trying to INCLUDE certain columns in CLUSTERED INDEX. Remember, when you create clustered index, all the columns of a table are automatically included by default.

If you want to exclude columns then you should use Non-Clustered indexes... that will have Key Columns(which supports JOINs, GROUP BY, PARTITION BY, ORDER BY) and Non-Key Columns(Other columns from the query plan that are shown as output columns).

Note: Order of columns in Key Columns is important while creating an Index.

---------------------------------------------------------------------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1002544 More actions · Answer 6

water490 wrote:

I have a script that was running fine but today it is taking way too long. I looked at the script and I see these errors:
"cannot specify included columns for a clustered index"
How do I fix this?
Thank you

Short and simple answer is... learning something about clustered indexes. 😉 Here's the link to their architecture. And, that entire article that one section is in should be studied. It tells you about the structure of indexes and a fair bit about how to design indexes.

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide#clustered-index-architecture

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)