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 |
|
mahdi87_gh
Yak Posting Veteran
72 Posts |
 Posted - 2013-09-07 : 03:07:52
|
| HiI have a table which named "RawNews" with the following fields: [NewsID] [decimal](18, 0) IDENTITY(1,1) NOT NULL, [Title] [nvarchar](200) NULL, [Description] [nvarchar](500) NULL, [Text] [ntext] NULL, [RegDate] [nvarchar](50) NULL, [RegTime] [time](0) NULL, [Status] [nvarchar](300) NULL, [Tags] [nvarchar](50) NULL, [SecurityLevelID] [smallint] NULL, [IsDeleted] [bit] NULL, [DelDate] [nchar](10) NULL, [UserName] [nvarchar](50) NULL,and another table named "UsedNews" which has the same fields plussome other fields.and there are some other tables realted to these tables like uploads,images,newsGroups,NewsRooms,Users and etc.in the RawNews I have 100 records each day. and these 100 recors for UsedNews.these information should be kept for like eternity.I wanted to ask you to advice me for a good way too make archive of records, so the search and filtering performance remains good.thanks a lot |
|
|
mahdi87_gh
Yak Posting Veteran
72 Posts |
Posted - 2013-09-07 : 03:28:22
|
| and another question: I have log table which logs every event in the system, should i keep it in a seperate database or not? |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-07 : 08:42:30
|
| If you get only 100 records a day, that is only 36,500 records a year, which is very small. So you wouldn't need to archive them or do anything special to split the data into live vs archive at least for a few years. Are you running into any performance problems? If you are, appropriate maintenance on the database along with the correct indexes and efficient queries would help.Usually I keep the audit logs in the same database as the one where I have the data. But that is my choice for my particular situation. How you should design yours depends on the specifics of your problem. Unless there is any compelling reason to move them off to another database, I would keep it in the same database. |
|
|
|
mahdi87_gh
Yak Posting Veteran
72 Posts |
Posted - 2013-09-07 : 10:14:49
|
| thanks a lot.we have an old news website which I'm going to upgrade it to a new design. so I'm open handed here and i'll look into new ideas.so if we have 36500 X 2 = 73000 records per year, it would be 730000 record after 10 years. in my program there is date picker which user picks date and sees that date's news-es. so you think there would be no performance issues?thanks again. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-09-07 : 10:33:06
|
| Yep - no performance issues here with that small amount of data.You might want to review your "used news" to refer back to the original rather than a copy. Also store your date as a date not a string.Also read up on portioning and conditional indexes if you're worried (you need not be). |
|
|
|
mahdi87_gh
Yak Posting Veteran
72 Posts |
Posted - 2013-09-07 : 10:48:08
|
| Ok,thanks.I'm using Persian Calender, so i have to store my date as string. is there any other solutions? if i store it as date, i have to convert it in any query. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-09-07 : 19:24:12
|
| Ah ok. Unfortunately SQL Server is a bit parochial in this regard.There are a few options but it would depend on what you're likely to do with the dates as to which (if any) are best. As dates are stored as numbers, you should be able to use the basics <,> = etc. without a problem. It's when you want intervals, parts of date and stuff where it gets tricky but you're no better off with a string. Consider using a date and converting it to Persian in your client/front end. At the end of the day, you know best though. |
|
|
|
|
|
|
|
|
advice on archiving design