Borg, please clarify:
This post got longer than I first thought but might serve as a useful list of how an old database now differs from a new one...
1) In my database (upgraded from 7 to 8 then 8 to 9) the following fields are 'bit' whereas in msSQL_server_setup.asp they are defined in a new database as 'smallint'. Is the upgrade correct or the msSQL_server_setup.asp?
tblConfiguration
A_code
NewsPad
2) The following field is ntext whereas in msSQL_server_setup.asp it is nvarchar(255):
tblAuthor
Signature
3) The following field is nvarchar(50) whereas in msSQL_server_setup.asp it is nvarchar(100):
tblConfiguration
NewsPad_URL
4) The following field does not allow NULL whereas in msSQL_server_setup.asp it does:
tblConfiguration
PM_Flood
5) The following field is nvarchar(70) whereas in msSQL_server_setup.asp it is nvarchar(90):
tblPoll
Poll_question
6) The following field is nvarchar(70) whereas in msSQL_server_setup.asp it is nvarchar(80):
tblPollChoice
Choice
7) In msSQL_server_setup.asp, replace the comment 'Create the Thread Table' with 'Create the Session Table'.
8) In msSQL_server_setup.asp, shouldn't all PRIMARY indexes be created as UNIQUE (they probably are anyway). The reason I am checking indexes is that there seem to be duplicates, the Primary indexes you created on the ID and auto-named indexes probably created by SQL Server.
9) Are both of these indexes necessary (also CAT_ID). I'm not sure the relationships need the key defined twice:
strSQL = "CREATE INDEX [Group_ID] ON [" & strDBO & "].[" & strDbTable & "Author]([Group_ID]) ON [PRIMARY]"
strSQL = "CREATE INDEX [" & strDbTable & "Group" & strDbTable & "Author] ON [" & strDBO & "].[" & strDbTable & "Author]([Group_ID]) ON [PRIMARY]"
10) The following indexes were missing (not added in upgrades?):
strSQL = "CREATE INDEX [Sub_ID] ON [" & strDBO & "].[" & strDbTable & "Forum]([Sub_ID]) ON [PRIMARY]"
strSQL = "CREATE INDEX [Moved_ID] ON [" & strDBO & "].[" & strDbTable & "Topic]([Moved_ID]) ON [PRIMARY]"
11) Are the names of these two indexes reversed deliberately:
strSQL = "CREATE INDEX [Author_ID] ON [" & strDBO & "].[" & strDbTable & "BuddyList]([Buddy_ID]) ON [PRIMARY]"
strSQL = "CREATE INDEX [Buddy_ID] ON [" & strDBO & "].[" & strDbTable & "BuddyList]([Author_ID]) ON [PRIMARY]"
12) tblPermissions does not appear to have a primary key, neither does tblPollVote - is this OK?
13) Some Primary Keys are not right. I'm not an SQL expert but should "ON [PRIMARY]" be included against all key definitions in msSQL_server_setup.asp?
The primary key of Configuration is website_name - should it be ID.
The primary key of DateTimeFormat is date_format - should it be ID.
14) The following field is indexed whereas in msSQL_server_setup.asp it is not:
tblForum
Forum_code
15) The following field has a default of (null) whereas in msSQL_server_setup.asp it does not:
tblAuthor
Author_email
16) tblConfiguration and tblDateTimeFormat have constraints (defaults) where the same tables in the V9 database have none.
17) In addition to 16), the following fields in the old database have constraints (defaults) where the same fields in the V9 database have none:
tblForum
Last_post_date
tblPermissions
Calendar_event
Display_post
View_Forum
18) In addition to 16) and 17), the following fields in the V9 database have constraints (defaults) where the same fields in the old database have none:
tblTopic
Poll_ID
19) tblPollVote has no indexes (in V9 and old database) - does this degrade performance when checking if a member has voted?
20) tblSmut is only indexed on ID and not keyword (in V9 and old database). Perhaps this table is held in memory so it doesn't matter.
21) I read a recent post that the following field should have a default of 1 instead of 0 as defined in msSQL_server_setup.asp:
Edited by Nick-V - 31 July 2007 at 10:57am
Topic Options
Post Options
Thanks(0)
