October 4, 2021 at 6:44 pm
I have a blog.
To log in, I request a User Name. Should I have a non-clustered index on that column?
Also, in another table, I query on the email address, should I have a non-clustered index on that column?
In both tables, I have an integer field as the primary key.
October 4, 2021 at 6:59 pm
My recommendation is ... TRY IT! See what happens. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 4, 2021 at 7:15 pm
I agree I can try it...but I was looking for best practice when doing lookups on a table that already has a primary index and hence a clustered index.
If I am doing queries against a table on a non-primary key, is it wise to add in a non-clustered index for speed?
October 4, 2021 at 8:19 pm
I
[1] To log in, I request a User Name. Should I have a non-clustered index on that column?
[2] Also, in another table, I query on the email address, should I have a non-clustered index on that column?
[3] In both tables, I have an integer field as the primary key.
[1] Typically yes. You should also review the missing index stats on that table. But, if you're don't want to do that, and/or you're unsure, add the nonclus index.
[2] Typically yes. You should also review the missing index stats on that table. But, if you're don't want to do that, and/or you're unsure, add the nonclus index.
[3] That may or may not be correct. Don't believe the myth that "(almost) every table should automatically be clustered by identity" (or some other int/numeric column). That's the single most damaging myth for overall db performance. The clustered index is the single most critical index for table performance, so it should be carefully chosen, not just an identity slapped on the table as a key. Of course sometimes an identity will be the correct, but for far less than half of the tables.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 4, 2021 at 9:14 pm
Scott, fyi...I do have a clustered index defined for the column that is the primary key - an IDENTITY column.
[UserId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
October 5, 2021 at 1:34 am
Scott, fyi...I do have a clustered index defined for the column that is the primary key - an IDENTITY column.
[UserId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [UserId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Yes, and that's usually the WRONG thing to cluster on, no matter that some "experts" tell you to "always default" to using identity as the clus key.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 5, 2021 at 3:44 am
But, in this case, having the Clustered Index on the UserID is probably perfect.
You also won't need a non-clustered index on UserID then, either.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 5, 2021 at 12:29 pm
But, in this case, having the Clustered Index on the UserID is probably perfect.
You also won't need a non-clustered index on UserID then, either.
Jeff's not wrong (I say that about everything really, except Extended Events). Picking the clustering key is vital. Yes, the default behavior is to put it on the identity column. That doesn't mean it's right. It's just a default.
I recommend that you identify the primary path to your data. For a lot of queries, that will be the identity column. But for others it might be a date, a foreign key, or a unique user name. Since the clustered index also stores the data, making the cluster key your primary path to the data makes the vast majority of your queries run fast, right out of the gate.
Then, as you identify places where you also need a secondary non-clustered index, you add them. Just test them well to ensure they don't negatively impact other processes (especially data modification).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 5, 2021 at 1:26 pm
Grant,
I think that is what I am doing. The primary path AFTER sign in is by UserId - the identify column. However, at sign in it looks for the User Name - a secondary path. So I was concerned that if my website grows to be many rows on the table, then should I create a non-clustered index on the User name? Also, the same for email address as it is a secondary path as well.
So, if I feel that I need the 2 non-clustered indexes after table creation, can I alter the table to create them?
October 5, 2021 at 1:35 pm
Grant,
I think that is what I am doing. The primary path AFTER sign in is by UserId - the identify column. However, at sign in it looks for the User Name - a secondary path. So I was concerned that if my website grows to be many rows on the table, then should I create a non-clustered index on the User name? Also, the same for email address as it is a secondary path as well.
So, if I feel that I need the 2 non-clustered indexes after table creation, can I alter the table to create them?
Let me phrase this carefully. Can you? Yes. Should you? Well, like has been said a couple of times in this thread, testing is your friend. They absolutely can help performance. I just don't automatically add indexes though. The query behavior matters because maybe an INCLUDE column or two would really help. It's just hard to say for certain without more knowledge of the code & structures. So, test it well.
At the root, there is absolutely nothing wrong with adding a nonclustered index to your rowstore or columnstore tables. There's nothing wrong with adding two, three, or even eight, as long as you've tested and know the effects those indexes are going to have on the other queries in the system. Never be afraid of nonclustered indexes. They are a vital and valuable tool.
Used judiciously.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 5, 2021 at 1:58 pm
Grant,
Got it. Good advice about testing from all.
And it looks like I can us ALTER to create a non-clustered index after the table was created.
October 5, 2021 at 2:56 pm
Grant,
Got it. Good advice about testing from all.
And it looks like I can us ALTER to create a non-clustered index after the table was created.
Oh yeah. Adding them is really simple.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 6, 2021 at 3:10 am
Grant,
Got it. Good advice about testing from all.
And it looks like I can us ALTER to create a non-clustered index after the table was created.
No. You don''t use "ALTER" to create an index. You use CREATE INDEX.
If "alter" a table to have a constraint such as a UNIQUE constraint or Primary Key, it will also create an index to enforce the constraint but neither of those are the normal way to create a non-clustered index.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 6, 2021 at 1:38 pm
Jeff...got it.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply