I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.
Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?
Here is the DDL of a table that I am trying to add an identity column and create a clustered index. I am using SQL 2012 SE
CREATE TABLE [dbo].[IPF]( [IPFId] [uniqueidentifier] NOT NULL, [IPId] [uniqueidentifier] NOT NULL, [FId] [uniqueidentifier] NULL, [FName] [nvarchar](50) NULL, [FItemId] [uniqueidentifier] NULL,
[code]....
The table currently has 220million rows. I am trying to add a new identity new column and create a clustered index on it. I ran the script and its been more than 26hrs and its still running. I ensured there is no blocking.
I am trying to add the hours between each time block stored in a database.
In this database a user enters the begin time and the end time. For example the course MATH0001 would start at 8am and end at 10am. Therefore the user would enter 0810 in the start field and 1000 in the end field. The course MATH0001 doesn't run the entire semester it may only run from 8th Jan - 15th March and the course is scheduled in a room called GR4. Now because a course can be scheduled modularly, one room could have several courses scheduled in this manner.
The problem: I need to find out how many hours GR4 is used but it contains the following courses
A day only has 13 hours. Therefore the total hours spent in GR4 should be 12 hours. This is calculated by adding the hours between 8am and 5pm = 9 hours and 5pm an 8pm = 3 hours. I would not include 9am to 1pm because it is a subset of the 8am to 5pm slot.
Now, how to accomplish this but below is the code that i have thus far:
I forgot to mention that this code was to just test my 'final code' results and it outputs the table shown above. Anyway for testing purposes I have limited the search to the room GR4 and the day Tuesdays.
Code: select DISTINCT ssrmeet_room_code, ssrmeet_start_date, ssrmeet_end_date, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 As HoursPerClass,
i'm trying to capture data for every hour and would like to display the hours of the day in a column.
select case when (datepart(hh,calldate) between 0 and 7AM then 1 else 0 end as [12-7AM], case when (datepart(hh,calldate) = 8 then 1 else 0 end as [8AM] from table1 where cast(calldate as date) = cast(calldate as date)
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
I simply need the ability using SQL to add columns in an existing table before (or after) columns that already exist.
The MS SQL implementation of ALTER TABLE doesn't seem to provide the before or after placement criteria I require. How is this done in MS SQL using SQL or is there a stored procedure I can use?
I have two columns in a table:StartDate DateTime and StartTime DateTime.The StartDate column holds a value such as 07/16/2004The StartTime column holds a value such as 3:00:00 PMI want to be able to add them in a stored procedure.When I use StartDate + StartTime I get a date two days earlier than expected.For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns7/14/2004 3:00:00 PM.Can anyone point out wht I'm doing wrong with this one?Thanks,lq
Hi,I have an application up and running. I need to add a new column to one of the tables which is currently being used - would adding a new column change or cause errors in the current application? e.g. if the table is being accessed by selecting * from table, will adding a new column cause an error? If there is any circumstance where an error would be caused by adding a new column, I will have to create an entirely new table. If I have to do this - how do I get a column in the new table to have the same values as a column in the old table? Can I create a computed column where column=oldcolumn? Thanks,Jon
hi guys! I have a table with 3 columns but i realized that i need to add 1 column between column 2 and 3. Can anybody please help me on how to do that? Thanks in advance!
Suppose I have a table with the following columns: Year, SalesInEurope,SalesInAmerica, TotalSales. I want to add a new column calledSalesInAsia, say, but I want it to appear before TotalSales. How canthis be achieved?Thanks,Bruno
I have a sp: mysp_getstuff it contains the following:SELECT Adress,City FROM tblUserData WHERE UserName='john'as you can see it returns 2 columns.I also have another SP: mysp_GetNr. This sp returns an integer.I want to call mysp_getnr from mysp_getstuff and add the result to the 2 columns in a column named 'Number'So the resultset from mysp_getstuff should be:Adress, City, Number (in which the number column contains the result from mysp_GetNr)How can I do that?
I posed this problem a few days ago, but havent been able to generate the results i need. Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:Lastname totalsales orderID-----------------------doe 1403 510doe 500 680 doe 200 701using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a newfield?netsports
I have been working on a website in asp.net1.1 in vb.net2003. I am using a sql2000 server. I am attempting to add a column to my datagrid that will add the total number of wins and output the number in that colum. With some help, I have been able to write the code. However, I am not sure where to put it. Is it a sql function I need to call from my code to add to the win column? Thanks for your help.
I have a table size 2078mb, number of row +530,900. Is it normal for sql to lock users out of the db when I add a column to the end of the table? I'm running SQL 7.0. The table has 4 col regular indexes. No primary keys. It locked the user out for about 10 min. I thought with SQL 7.0 this problem went away?
We have a SQL Server 6.5 with SP 5a . I want to add an additional column in one of the tables of a database . Since i don't have much experience on SQL 6.5 , i need your precious help in resolving this case . Can somebody help me in this regard in a detail step wise manner ? Enterprise manager does not have facility to add new columns to an existing table in SQL 6.5 . I want to add a Column called 'DM ' with datatype as 'bit' with size as 1 and as non nullable . Any kind of help will be very precious to me .
This is a very simple select but I would like to add a final column that adds the QOH, QOB, QOO and Quantity. How can I accomplish this? Thanks SELECT DISTINCT zcus_MM_Medsurg_Used.stock_no, zcus_MM_Medsurg_QOH_Only.qty_on_hand AS QOH, zcus_MM_Medsurg_Used.QOB, zcus_MM_Medsurg_Used.QOO, zcus_MM_Medsurg_Used.QUANTITY
FROM zcus_MM_Medsurg_QOH_Only INNER JOIN zcus_MM_Medsurg_Used ON zcus_MM_Medsurg_QOH_Only.stock_no = zcus_MM_Medsurg_Used.stock_no
Can I add a column to a database table without dropping and recreating the table?
The problem is that everytime a user creates an action that requires a new table - at the moment I drop the table and recreate the table with the new column.
This requires lots of resources as I have to populate the table again.
Hi, I would like to alter a table in a sql server database then update the column with data in the same script. However, the database does not recognize the database column if I create it within the script. Is there a way to refresh this within the script so that I can run this in one procedure? If I create the table in one script then update in a second script it will work. Thanks.
I have a pretty simple SQL query that has two columns that contains only numbers. Is there a way i can add an extra column that simply subtracts the number in one column from number in the other column.I query the data tables rather than have a live link so the .csv file gets overwritten every time i run the query.
I have two queries I would like to combine the output on. they are as follows:
Select substring(WrkSta.[Name],1,2) 'Location' ,count (aexe.ReturnCode) as '# Patched' from WrkSta, AeXEvt_AeX_SWD_Execution aexe where WrkSta.WrkStaId=aexe.WrkStaId and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%' or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%' or WrkSta.[Name] like 'ET%') and (aexe.returncode='0' or aexe.returncode ='3010') and aexe.AdvertisementName like 'MS05-035-043%' group by substring (WrkSta.[Name], 1,2)
--- Returns : Location # Patched EP 102 ES 1986 ET 19 AB 174 SU 6 EM 506
and the second one:
Select substring(WrkSta.[Name],1,2) 'Location' ,count (coll.WrkStaId ) as '# Workstation' from WrkSta join AeXNSCollectionMembership coll on WrkSta.WrkStaId=Coll.WrkStaId where coll.CollectionGuid = '38F5DAFC-E09D-49A5-A0FD-370983CA7596' and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%' or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%' or WrkSta.[Name] like 'ET%') group by substring (WrkSta.[Name], 1,2)
--- returns:
Location # Workstations EP 178 ES 2299 ET 24 AB 215 SU 13 EM 582
What I need is :
Location # Workstations # Patched EP 178 102 EI 2299 1986 ET 24 19 AB 215 174 SU 13 6 EM 582 582
No mater how I try to do a join to do this in a single query I end up with what looks like a cross-join and # workstations and # Patched jump to huge numbers. I obviously am having a problem understanding how to set up the select statements so that I can do this in one query or am I following the wrong direction and should be trying something else? Once again I appreciate your help in advance....
