SQL Server Performance Forum – Threads Archive

using join

I have two tables splited on the bases of frequently searched fields and least used feilds. First table has fields like LoginID,AGE,Country, etc in this table I have choosen the AGE field to make CLUSTERED index as it is used in all search queries by range like 18 to 25.
I have made ID field as NONCLUSTERED PRIMARY KEY …. The second table use CLUSTERED PRIMARY KEY. Sometime it is required to search data from both tables using JOIN. For above 100,000 records is ok to use CLUSTERED INDEX on two different type of fields if we have to use join OR should make LoginID as CLUSTERED INDEX in both tables. About 75% users will search in first table only. There are 8 columns in first table and about 35 columns in second table. Earlier I had single table with 43 columns AND only LoginID column was indexed, it works very slow. Is my decision to split them is ok or should I re-combine them. Regards
Gurpreet Sachdeva

---

Well, this depends on what you have in your two tables and what the joining column is. From your statements, I gather that your joining key field is LoginID, which is a good idea because they are distinct. To answer the question of splitting the tables or combining them, we need to know the table layout and how the data is used. You may want to comine all fields and then renormalize your database. ———-
T Kelley
MS, MCDBA, OCA, CIW

---

Based on the information you have provided, I would keep your first table index like you have already described, as your current indexes make sense. In the second table, I would create a clustered primary key on the join column. The number of records in the table in unimportant. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

---

]]>