| Author |
Topic |
|
cognos79
Posting Yak Master
241 Posts |
 Posted - 2007-01-02 : 12:52:37
|
| I do have a dump table "table1" Table1:UniqueId varchar(4)scandate datetimecourseno varchar(5).This table is not related to any other tables in the database. This is used to track the student attendance for a particular course. There can 60000 distinct uniqueid's in the table and 300 disticnt courses. I query this table most of the time on uniqueid and coursecrn combination. This table can grow up to 5 million records.can anybody tell on what fields do I have to create indexes and also of what type(clustered or non-clustered). This table doesnt have a primary key. Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-02 : 12:55:55
|
| Well the first index you should add is the primary key. Any indexes added after that will be dependent on your queries. Please post your most common queries.Tara Kizer |
 |
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-01-02 : 13:02:19
|
| This is the scenario. when the student goes to a class he swipes his id then a record will be created in this table. e.g. 700223333 1/2/2007 10:00:00 AM cs101so if he attends class for 45 days in a semester he has 45 records for a particular course. so i dont think i can create a primary key on this table...makes sense???I query this table to get the attendance of students registered for a particular course...likei am jus giving u a gist not exact queryselect count(scandate), uniqueidfrom table1where courseno = @coursenogroup by uniqueidi all of my queries i use either courseno or uniqueid or both... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-02 : 13:07:32
|
| No it doesn't make sense to not have a primary key. That is a huge no no in database design. What you have described doesn't mean you shouldn't add a primary key. Your database design is probably flawed if you think that.Tara Kizer |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-02 : 13:50:26
|
| primary key should be accross uniqueid, scandate, courseno. i'll most likely cluster on uniqueid, scandate (means pk is NON-clustered), put non-clustered on courseno. all depends. should try indexing then examine execution plans for most common queries. may try various combinations. important note: if scandate contains time info as well as date info, then this pk is useless (in which case i'll add a column, so one for date only, one for time only or datetime). i am assuming that a student can only attend each course one time per day of course...finally, why not have course# and uniqueid as int? either way, both should be foreign keyed to tables enforcing referential integrity. |
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-01-02 : 13:52:00
|
| where do u think the design is flawed. the database is so simple with 3 tables.students table contains students personal information (uniqueid is primary key).courses table contains course details(courseno is primary key). and the third one is this dump table...??? |
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-01-02 : 13:58:27
|
| russell...scandate contains timestamp too..coursenumber can contain alpabets. sorry uniqueid is int (my bad).logically the student can attend a course once a day. but if he swipes his card three times there will be three records... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-02 : 14:01:30
|
quote:
Originally posted by cognos79
where do u think the design is flawed. the database is so simple with 3 tables.students table contains students personal information (uniqueid is primary key).courses table contains course details(courseno is primary key). and the third one is this dump table...???
It's hard to say if the design is correct or not without seeing the actual DDL for the tables.Tara Kizer |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-02 : 14:01:46
|
| i'll add column for just date part of datetime value, add non-clustered pk on composite of that new column, uniqueid, courseno.cluster as said above |
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-01-02 : 15:22:15
|
| russell...you say to create a new column with time value and create non-clustered index on new column, uniqueid, courseno??? or should it be non-clustered pk??? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-02 : 19:59:54
|
| i'll make pk non-clustered (at least first), to enforce uniqueness over (uniqueid, scandate, courseno). then i'll create clustered index on (uniqueid, scandate) for optimal query performance.of course, i'm adding a column :) so scandate is really my new column with time info truncated.definitely try it and examine execution plans, execute some queries for testing.clear buffers (DBCC DROPCLEANBUFFERS) between tests to make sure u r comparing apples to apples. |
|
|
|
|
creating indexes