Need to add a new column to an existing table with 37M rows

I have been trying a couple of methods to add a column to a table. Well adding the column hasnt been that difficult. The difficult part is when I need to update this newly added column with a value returned from a function. Even 1000 rows takes for ever to update in a transaction. Is there any one who has come across this stituation, please help.

Thanks in Advance.

Quote:

Originally Posted by codezilla

I have been trying a couple of methods to add a column to a table. Well adding the column hasnt been that difficult. The difficult part is when I need to update this newly added column with a value returned from a function. Even 1000 rows takes for ever to update in a transaction. Is there any one who has come across this stituation, please help.

Thanks in Advance.

am not sure there are other ways...maybe you could benchmark your UPDATE vs SELECT ...newfield = udf(para) into ... from...

depending on table that your updating (may have triggers, constraint). the cons of SELECT...INTO is also space on your db.

also, try if you can just use a CALCULATED FIELD. another one is to just use a function outside of db, that is if you don't need to keep this field and will be used primarily for display purposes.

Need SQL Help

I have following query which is joining couple of tables. i have a field "Status" in MeetingAttendees table. I have to add one more check (probably one more case statement) that if MA.Status=4 then Count(A.AttendeeID) as NoofAttendees. So NoofRSVPs (doesn't matter what is the status in MeetingAttendees table) will return total RSVPs and NoofAttendee will return only # of Attendees. how can i add do that? please help...

SELECT

M.State AS MeetingState,

CASE
WHEN MA.AttendeeType = 1 THEN 'Participant'
WHEN MA.AttendeeType = 2 THEN 'Speaker/Faculty'
WHEN MA.AttendeeType = 3 THEN 'Client'
WHEN MA.AttendeeType = 4 THEN 'Staff'
END AS AttendeeType,

Count(A.AttendeeID) as NoofRSVPs

FROM
Programs P
INNER
JOIN eCDReservations M
ON P.SubCompanyCode = M.SubCompanyCode
AND P.ProgramCode = M.ProgramCode
left outer
JOIN MeetingAttendees MA
ON M.ReservationID = MA.MeetingID
left outer
JOIN Attendees A
ON MA.AttendeeID = A.AttendeeID
left outer
JOIN Regions R
ON MA.RegionCode = R.RegionCode
WHERE
P.SubCompanyCode = @.SubCompanyCode AND
P.ProgramCode = @.ProgramCode




GROUP BY

M.State,

MA.AttendeeType

ORDER BY

MA.AttendeeTypeCOUNT(CASE WHEN MA.Status=4 THEN 'present' END) as NoofAttendees

Need script to kill all processes on a database

I restore a backup every week. It serves a couple of purposes. One, to verify the backup and secondly, to give us a current testing/training environment. As things evolve, it would be nice to script this and then schedule it as a job.

I can write the restore statement, but it fails if any sessions are open on the db. I thought about using sp_who and the the kill statement. But I was having trouble using the recordset returned by sp_who.

Now I'm thinking the sysprocess table is a better place to look. Does anybody have some experience in this area and can point me in the right direction?

While I'm going down this path, does anybody have a script to look at the master database and dynamically manage their backup strategy? That would be super cool?!?!

Thanks,

Alex8675I restore a backup every week. It serves a couple of purposes. One, to verify the backup and secondly, to give us a current testing/training environment. As things evolve, it would be nice to script this and then schedule it as a job.

I can write the restore statement, but it fails if any sessions are open on the db. I thought about using sp_who and the the kill statement. But I was having trouble using the recordset returned by sp_who.

Now I'm thinking the sysprocess table is a better place to look. Does anybody have some experience in this area and can point me in the right direction?

Go to google type "kill all processes IN A DATABASE SQL SERVER" i got atleast 10 different SP's which will do your job.....choose the one which suits you|||this will work if you are not logged connected to the database and no other sysadmin is doing anything...

ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE|||Very elegant solution and exactly what I was looking for...but I couldn't find and was going a long way around to find what was readily available.|||this will work if you are not logged connected to the database and no other sysadmin is doing anything...

ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
This one's not as elegant, but worked when I had to disconnect / move the log files tonight.
DECLARE @.sql nVARCHAR(4000)
SET @.sql = ''

-- To disconnect, first kill all processes.
SELECT @.sql = @.sql + ' KILL ' + CAST(spid AS VARCHAR(10)) + ' '
FROM master.dbo.sysprocesses
WHERE DB_NAME(dbid) = @.MYDB
AND spid > 50 AND spid <> @.@.SPID
EXEC(@.sql)

Thrasymachus; Do I have to do something to undo the SET?

Need Optimal Schedule for Snapshot and Merge Replications

I need to schedule a snapshot replication of some tables and a merge
replication of a couple other tables. I would like to know the best
way to schedule the various replication agents.
My questions are:
- Should I arrange the schedule in this order:
Step 1. Run Snapshot Agent for snapshot replication
Step 2. Run Distribution Agents: one for each branch office
Step 3. Run Snapshot Agent for merge replication
Step 4. Run Merge Agents: one for each branch office
The reason I ask is that I am under the impression that
Distribution Agent has everything to do with snapshot
replication and has nothing to do with merge replication.
Therefore, I may want to run Distribution Agent right
after Snapshot Agent for snapshot replication -- just to
group related tasks together and out of the way.
Is my understanding correct?
What is the correct order anyway?
- I have one Distribution Agent for each branch office.
Likewise, I have one Merge Agent for each branch office.
I have two branch offices; this means I have two
Distribution Agents and two Merge Agents.
Should I start the two Distribution Agents at the same time?
Should I start the two Merge Agents at the same time?
Of course, I could have separate those two Distribution
Agents in two different time slots. But I don't want to do
this in order to avoid keeping track with two different sets
of schedules.
I have a feeling that I should be able to run those two agents
at the same time because the bottleneck is the T1 line between
the central office and each branch office. Is my understanding
correct?
Thanks in advance for any info.
Jay Chan
In general you can run all agents simultaneously if you have a small number of subscribers.
You can leave the distribution and merge agents running continoulsy and they will detect the new snapshot is available for distribution and will distribute it.
Many DBAs like to
1)run their distribution and merge agents in continuous loops
2) or schedule them to restart every 10 minutes or so and have them run continoss
This makes them more resilitent to failure.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html