July 16, 2008 at 1:05 am
I have tables with below schemas..
CREATE TABLE dbo.ParentTable (
ParentCol INT Primary Key,
Col2 INT,
Col3 VARCHAR(255)
)
CREATE TABLE dbo.ChildTable (
ChildCol INT Primary Key,
ParentCol INT,
Col3 VARCHAR(255)
)
Now want to build the indexing strategy on both tables. could anyone help me out in this?
Cases :
1. I could define Composite Key on ParentTable as well as on ChildTable
2. I could define Clustered index on primary Keys of both columns
3. I could define NonClustered index on Primary Taking Non key columns as Included.
please explain which index strategy will go best and how?
July 16, 2008 at 1:13 am
Abhijit (7/16/2008)
Now want to build the indexing strategy on both tables. could anyone help me out in this?
Not enough information.
Are there any foreign keys? How are you going to be querying those tables? What fields will be commonly searched by, what columns selected, what columns grouped by and what columns ordered by?
How much data is likely to go in those tables? Which columns are fairly selective?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2008 at 1:42 pm
As far as I know, SQL Server indexes cannot cross table boundaries. Each table has to have their own stand-alone indexes on their PKs and FKs. Usually the clustered index would go on the PK, but if the joins of your queries are downstream (from one-side to many-side because WHERE predicate is on the one-side) then I'd put the clustered index on the dominant (know the queries) FK instead.
You can create a fakie 2nd clustered index by making all the remaining columns in the table that are not part of the seek column set for the index include columns. I would limit includes to only what your queries actually need though, and limit more if the table is going to be written to alot.
July 16, 2008 at 2:47 pm
By default when you define a Primary Key in SQL Server you get a clustered index on the Primary Key. You can manually make the Primary Key Non-Clustered. Now this may not be the best strategy, but I usually put any foreign key columns as the first part of a non-clustered index. In you scenario, if the table ChildTable only has the 3 columns you mention, I would put a non-clustered index on ParentCol, Col3. This assumes that I am usually going to join on the parent table and likely have some kind of filter on Col3.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 16, 2008 at 10:14 pm
Hi Gail, here's the information you required..
Are there any foreign keys? Yes, On ChildTable for ParentCol with ParentTable
How are you going to be querying those tables? The table contains daily transactions Like in Sales System.. SaleSummary and SaleDetails
What fields will be commonly searched by, what columns selected, what columns grouped by and what columns ordered by?
All columns from Both the tables will be returned in resultset, data will be searched on the basis of Parent ID for which contains millions of records in child table( one-to-many )
How much data is likely to go in those tables? Millions / Billions
Which columns are fairly selective? Wide search can also be done on Col2, Col3 of ParentTable and Col3 of ChildTable.
July 17, 2008 at 12:19 am
Abhijit,
1st table Index hints:
CREATE TABLE dbo.ParentTable (
ParentCol INT Primary Key,
Col2 INT,
Col3 VARCHAR(255)
)
As you have not specified the index type on ParentCol then by default its clustered Index. And 2nd thing you said in the last post that you are going to search by col2 and col3 then you can create the nonclusterd index on this columns unless until you have less DML operation.
2nd table Index hints:
CREATE TABLE dbo.ChildTable (
ChildCol INT Primary Key,
ParentCol INT,
Col3 VARCHAR(255)
)
As I said for 1st table its also same with child table ChildCol is the Clustered Index. then come 2 next column (ParentCol) you have not specified this as foreign key but later you said it. then you may create a non clustered index on it. and 2nd thing you said in the last post that you are going to search by col3 then you may create the nonclusterd index as per the requirements.
Your query Answer:
Cases :
1. I could define Composite Key on ParentTable as well as on ChildTable
2. I could define Clustered index on primary Keys of both columns
3. I could define NonClustered index on Primary Taking Non key columns as Included.
Ans1: No need if you have this much requirements.
Ans2: when you create a primary key, its by default Clustered Index.
Ans3: Its not needed as per this.
I would rather suggest not to follow more complex design rather than required. Always go with simple design which helps you in future.
Cheers!
Sandy.
--
July 17, 2008 at 12:51 am
Looking at that, my first pass suggestion for indexs (without any testing done) would be:
ParentTable
- Clustered index on the primary key.
- Possible nonclustered index on Col2, Col3 (if they are searched on together)
ChildTable
- Make the PK nonclustered
- put the clustered index on ParentCol. Reason is, if the two tables are often joined, this index produces good options for the join, allowing SQL to do merge joins instwd of has joins on large rowcounts. Also, since its the foreign key to the parent table. Unless lots of searches are dne on the ChildCol, its a waste putting the clusterede index there
- Consider a NC index on Col3. But would depend on how often that's filtered on.
I would also suggest, based on the millions of expected rows, that you consider implementing partitioned tables. If you partition on the ParentCol in both tables you can vastly reduce the number of rows that will need to be considered for a query if SQL can easily tell which partition it needs to read, as opposed to the entire table.
As always, test, test, test. Make sure your queries are using the indexes as you expect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2008 at 4:28 am
Thanks alot to all of you!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply