141 reputation
7
bio website DeveloperTipOfTheDay.com
location United Kingdom
age 30
visits member for 3 years, 1 month
seen Jun 3 at 8:02

A versatile full stack web developer based in the UK with 9 years of experience.

Want to learn more, contact me on Stackoverflow careers


Jun
27
awarded  Popular Question
Jan
29
comment Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
Hi @AlexKuznetsov I looked at the ALTER TABLE ... SWITCH which I believe is using partitions (I think)?
Jan
29
comment Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
Interesting, thanks for the link @Remus - I've not come across that page before. Did you see Aaron's edited post, what do you think?
Jan
29
awarded  Commentator
Jan
29
comment Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
Thanks @Remus a handy shunt back to reality. It's good to know the possible side effects. Would it be possible to use table hints on the reads to help mitigate this - maybe allowing dirty reads (dirty reads are ok for the table's use)? We certainly have to fix the overall problem, but looking for workarounds to by time to a proper refactor.
Jan
24
comment Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
Wowzer, that's a different realm of size of DB compared to ours (tiny in comparison)! But good to know the transaction logs is ok, thanks for the additional detail.
Jan
24
comment Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
Thanks Simon, good idea. How did you find that effected your transaction log? Was it ok? We may have to do this an number of times a day. How many rows were you deleting?
Jan
24
comment Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
Ah ok, so a double switch. Good idea. How does this effect reads? Will the reads still go to the original table whilst this is happening?
Jan
24
comment Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
Thanks Martin, that's an interesting idea. I've not heard of that before. Looking at MSDN the receiving table needs to be empty first, but perhaps used with Simon's answer it could be good.
Jan
24
asked Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
Jan
11
awarded  Citizen Patrol
Jan
11
comment Reset / force SQL Agent Next run date
Thanks for the idea, shame next_run_date isn't datetime.
Jan
11
comment Reset / force SQL Agent Next run date
Hi @AaronBertrand I must admit I wasn't counting. But looking at my Last Run date (from when I last ran it manually) it's been about 40 minutes, so looks like it didn't fix itself.
Jan
11
asked Reset / force SQL Agent Next run date
Dec
5
comment Generate insert statements for table AND related data
Thanks Kevin, good point on the order of the inserts. I suppose the order doesn't matter, I suppose it's the discover-ability of the related data and writing all the INSERT statements out.
Dec
5
comment Generate insert statements for table AND related data
Hi @Kevin Feasel I've added a little example to my question.
Dec
5
revised Generate insert statements for table AND related data
added example to copying fk / pk
Dec
5
awarded  Editor
Dec
5
revised Generate insert statements for table AND related data
added 127 characters in body
Dec
5
comment Generate insert statements for table AND related data
Thanks @Kevin Feasel +1 from me. What I'm really hoping for is the ability to generate the inserts to tie up the FK / PK when doing the insert. I'll update the question text.