Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
techguy817
Starting Member
1 Post |
 Posted - 2012-07-09 : 12:47:27
|
| All,I recently implemented IIS ODBC logging for our Windows Server 2008 R2 web server. I've referred to http://support.microsoft.com/kb/245243 which shows the required table structure. I have about 40,000 records in the table now (this is after I run a SP after hours each day to clean up records deemed irrelevant) and there is no primary key. I don't believe I can make the logtime field a primary key because it is not unique for each record (there are several entries with the exact same timestamp. I run queries against this table and the performance is less than ideal. I'm thinking that a primary key autonum column would be good but I'm not sure exactly how to implement it and not break the IIS ODBC logging. I can't find anything on this online (figured it should be common).I know that I can do something like:alter table InternetLog add ID INT IDENTITYbut I'm not sure if this is the way to go and it doesn't create the IDs aligned with the logtime ascending or descending (which I think would be an ideal way to store it). Any suggestions are greatly appreciated! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-09 : 14:01:29
|
| When I had to something similar, I did the following:a) created a non-null integer column and populated it with the numbers in the order that I wanted.b) used the SSMS table designer to change the identity property of the new column to true.When you use SSMS to do this, behind the scenes SQL server is doing a lot of stuff, so this may be an intensive operation, but if you have only 40,000 records, it shouldn't be too horrible.Not necessarily recommending this approach - if there is a better way, I don't know what it is. Even if you do this, PLEASE test the theory in a development environment. Also, if you need to do this on a live database, that might cause a problem if new data needs to be logged when the update to the table schema is going on. |
 |
|
|
|
|
|
|
|
Adding a primary key to the IIS ODBC logging table