Indexing Tables
-
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
-
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
-
Can't fix code we can't see. Can you post the CREATE TABLE and then CREATE INDEX scripts?
-
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.
-
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.
=======================================================================
-
This was removed by the editor as SPAM
- 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.
--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) -
Hi everyone
I had a bit of time to work on my side project. I am getting "Cannot specify included columns for a clustered index" error.
Here is my script:
CREATE CLUSTERED COLUMNSTORE INDEX [VIXNearTermExpirationIndex] ON [dbo].[VIXNearTermExpiration] WITH (DROP_EXISTING = ON, COMPRESSION_DELAY = 0) ON [PRIMARY]
Here is the table:
CREATE TABLE [dbo].[VIXNearTermExpiration](
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
[EXPIRATION] [date] NOT NULL,
[ROOT_SYMBOL] [nvarchar](10) NOT NULL
) ON [PRIMARY]
GOSS is underlying in red and says there is an error yet when the SP is run there is no error:
What am I doing wrong?
Thank you
-
You cannot do INCLUDEs on Clustered Indexes, period. The reason why is that the Clustered Index IS the table and it already includes all the data.
I posted a link further above. If you're going to work on indexes, you should at least know what a Clustered Index is. Please go read the article at the link. It'll help you a whole lot.
As for how to do the indexing, I suggest you gather up the procs and compare what they need and make as few indexes as possible to support the collection.
--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) - Jeff Moden wrote:
You cannot do INCLUDEs on Clustered Indexes, period. The reason why is that the Clustered Index IS the table and it already includes all the data.
I posted a link further above. If you're going to work on indexes, you should at least know what a Clustered Index is. Please go read the article at the link. It'll help you a whole lot.
As for how to do the indexing, I suggest you gather up the procs and compare what they need and make as few indexes as possible to support the collection.
I did not include anything. The script is attached and nothing is included. If a column was included then wouldn't my script have that info there to let me know what is included or not?
-
Intellisense telling you it thinks there is an error isn't the same as having an error.
so do you GET an error when you execute the code?
does the index not get created (or changed from whatever it was as you are using a "drop_existing = on")?
and have you done enough investigation to determine if a columnstore index is indeed what you need for those tables and for the supporting queries?
- water490 wrote:Jeff Moden wrote:
You cannot do INCLUDEs on Clustered Indexes, period. The reason why is that the Clustered Index IS the table and it already includes all the data.
I posted a link further above. If you're going to work on indexes, you should at least know what a Clustered Index is. Please go read the article at the link. It'll help you a whole lot.
As for how to do the indexing, I suggest you gather up the procs and compare what they need and make as few indexes as possible to support the collection.
I did not include anything. The script is attached and nothing is included. If a column was included then wouldn't my script have that info there to let me know what is included or not?
My apologies. I really missed things there.
I agree with Frederico... the Intellisense feature isn't always correct even if you hit {ctrl}{shift}{R} to refresh it.
--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) -
If the index doesn't already exist you need to remove
DROP_EXISTING = ONfrom the index creation or set it toOFF. - frederico_fonseca wrote:
Intellisense telling you it thinks there is an error isn't the same as having an error.
so do you GET an error when you execute the code?
does the index not get created (or changed from whatever it was as you are using a "drop_existing = on")?
and have you done enough investigation to determine if a columnstore index is indeed what you need for those tables and for the supporting queries?
That is the puzzling part. The query runs with no errors yet I still get the ones I mentioned in my post earlier. Is it possible that the index never gets created? Is there a way to confirm if an index is created?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply