March 7, 2015 at 12:50 pm
I have three sprocs and three tables. I was told to use a clustered index in the first table and a unique clustered index on the second table. I never asked about the third table and the person I need to ask is on vacation. Most of the contents of the first table will be joined with all of the contents of the second table into the third table. Do I need to have a unique clustered index on the third table too?
The clustered index in the first sproc is on a unique key that I had created using by concatenating several columns together.
CREATE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.Margin_Optimization_Data (UNIQUE_KEY);
CREATE NONCLUSTERED INDEX IX_DATE ON MRP.Margin_Optimization_Data (PERIOD);
CREATE NONCLUSTERED INDEX IX_ODS_ID ON MRP.Margin_Optimization_Data
(GL_SEG1_COMPANY_ODS_ID, GL_SEG2_PROFIT_CTR_ODS_ID, GL_SEG3_LOB_ODS_ID, GL_SEG4_PRODUCT_DEPT_ODS_ID, GL_SEG5_ACCOUNT_ODS_ID);
The second sproc with the unique clustered index is on the unique key from the first table and a date attribute.
CREATE UNIQUE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.[MGN_OPT_KPI_SOURCE] (UNIQUE_KEY, PERIOD);
In the third sproc, I'll have a nonclusted index on the ODS_ID attributes, but I'm unsure of how to go about the clustered index situation.
CREATE NONCLUSTERED INDEX IX_ODS_ID ON MRP.MGN_OPT_KPI_VALUES
(GL_SEG1_COMPANY_ODS_ID, GL_SEG2_PROFIT_CTR_ODS_ID, GL_SEG3_LOB_ODS_ID, GL_SEG4_PRODUCT_DEPT_ODS_ID, GL_SEG5_ACCOUNT_ODS_ID);
"Nicholas"
March 9, 2015 at 5:32 am
Hi. I'm not sure what you mean by "sproc" - it normally means stored procedure, but you do not show any stored procedures.
You do not have to have indexes where there is a join - it depends on how many records there are. For very small tables, joins will work perfectly well without any index. You would usually use indexes for larger tables though.
If you could provide details of the table structures & the joins you want to make, someone will be able to help.
March 9, 2015 at 6:40 am
Most tables in most systems (and by most, 99% plus a few extra digits) should have a clustered index. It doesn't have to be unique as defined, although it's better generally if it is because the system will make it unique if it's not.
But, there's no way to tell you what you should be doing in this case because we can't see the queries you're writing or your structure. With those, we can make intelligent suggestions. Without those, we can make a few guesses and spout some best practices.
Keep your indexes as narrow as is practicable. Make sure your queries are written so that they'll use the indexes appropriately. For more, read my book on query tuning.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 9, 2015 at 10:51 am
If you want an index to be unique, you must explicitly specify that in SQL. You should also always explicitly specify the proper fillfactor and filegroup (even if it's just the default of PRIMARY).
Also, there is almost never a reason to have a nonclustered index on a table w/o a clustered index. Just make the nc index a clus one instead.
CREATE UNIQUE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.Margin_Optimization_Data ( UNIQUE_KEY ) WITH ( FILLFACTOR = 100 ) ON [PRIMARY];
CREATE CLUSTERED INDEX IX_ODS_ID ON MRP.MGN_OPT_KPI_VALUES ( GL_SEG1_COMPANY_ODS_ID, GL_SEG2_PROFIT_CTR_ODS_ID, GL_SEG3_LOB_ODS_ID, GL_SEG4_PRODUCT_DEPT_ODS_ID, GL_SEG5_ACCOUNT_ODS_ID ) WITH ( FILLFACTOR = 99 ) ON [PRIMARY];
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 9, 2015 at 11:49 am
CREATE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.Margin_Optimization_Data (UNIQUE_KEY);
If IX_UNIQUE_KEY is intended to be a unique key, then you probably want to add the UNIQUE keyword.
CREATE UNIQUE CLUSTERED INDEX IX_UNIQUE_KEY
ON MRP.Margin_Optimization_Data (UNIQUE_KEY);
Also, I don't see a primary key definition in your original post. If UNIQUE_KEY could also be the primary key, then consider making it such. By default, the primary key is also clustered. However, you don't need a seperate index in addition to a primary key; it should be a seconday unique index or the primary, but not both.
ALTER TABLE MRP.Margin_Optimization_Data
ADD CONSTRAINT PK_UNIQUE_KEY PRIMARY KEY (UNIQUE_KEY);
I'd reccomend waiting until everyone is back from vacation before making a decision about this.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply