Transact SQL :: Altering A Table Without Impacting The Performance?
Sep 15, 2015
Altering a table which is having more than 100 million rows. Would like to know the best possible way to add a new column to this table without impacting the performance much.
View 7 Replies
ADVERTISEMENT
Aug 11, 2000
SQL Server 7.0 (SP1)
Error:
------
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
A column was deleted from the a table on the linked server and now this message appears when using the linked server definition to access the table. Deleting/Recreating the Linked Server has no effect. I found an earlier note on this...but it just kind of ended with no resolution. Anyone have any thoughts on this now.
Thanks for any input.
View 1 Replies
View Related
Dec 2, 2003
Hai All.
I want to know ,is there any way to modify a table's field like adding of new field to a table.
If any one have idea plz enlighten me.
Bye
Regards,
Karthik.A
View 1 Replies
View Related
Aug 27, 2007
This is a SQL 2005 production server.
I have to delete around 51 million rows from a table which has 149 million rows.
Can't use truncate option as the other rows in the table are still needed.
How can I delete the rows without filling up the tempdb ?
If the tempdb fills up I can't bounce the server.
View 11 Replies
View Related
Aug 19, 2004
Hi, How to alter a table with default value?
I am using the below statement, But, it is not working..Any pointers?
Thx..
-------------------------------
alter table action_item ALTER COLUMN STATUS default 0
View 1 Replies
View Related
Feb 12, 2004
Hi! I've added a column to a published table in the Publisher using SP_REPLADDCOLUMN. After doing this the replication triggers for that table(i.e. del_%,upd_%,ins_%) are doubled both at the Publisher and at the Subscriber. If i update anyone of the column in the table at the Subscriber; i get the following error:
Server: Msg 208, Level 16, State 1, Procedure upd_3E6DE124B82D42A5AEB169557C0D757C, Line 60
Invalid object name 'ctsv_3E6DE124B82D42A5AEB169557C0D757C'.
Any ideas, what has gone wrong????
I have the following SQL setup.
Publisher: Enterprise Edition, SP3.
Subscriber: Standard-Edition, SP3.
The error is at the Subscriber.
View 5 Replies
View Related
Feb 2, 2007
How can i change my Table Structure that is replicated?
I need to add a new field.
View 1 Replies
View Related
Jul 23, 2005
Hi,How can I modify table with publication (change of one column length)without completely breaking replication.Thanks in advance
View 1 Replies
View Related
Sep 10, 2007
Hi Guys,
I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.
Let's say I have a Dictionary table, 2 columns:
Word | Definition
And I have a string - "The cat sat on the dog"
If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"
At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"
The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...
I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )
Any ideas?
Thanks in advance
-Craig
View 4 Replies
View Related
Jul 20, 2005
I'm trying to do a simple alteration to the table design of one of ourSQL 2k tables, simply changing an identity row so that its not 'notfor replication', and its taking absolutely ages to do so, and stopsthe sql server from working.Whilst it's attempting the update, no one can access the database, thesqlservr.exe memory usage shoots up and enterprise manager reports anot responding status. Eventually after about 10 minutes, it bombs outreporting,Unable to modify tableCould not allocate space for object 'Tmp_TableName' in database'DBNAME' because the 'PRIMARY' filegroup is full.The table i'm attempting to change has only about 4000 records sothere's not a huge amount of data.Any ideas what's causing this and how i can get around it?A similar thing happens when i attempt to change the length of avarchar too.Thanks in advance for any suggestionsDan Williams.
View 3 Replies
View Related
Apr 18, 2014
I have a db1.tPersonJobHist where I have two columns
[WorkflowCoordinatorFlag] [dbo].[shrsFlag] NOT NULL,
[HRBusinessPartnerCode] [dbo].[shrsCode] NULL,
Our developer mistakenly made HRbusinesspartnercode field as nullable. He now wants to change it to Not Nullable.
So there were 4 rows where the values were not null(not sure how). We edited those rows and changed those values to NUll. Now we have no Nulls in that column.
So we brought up the table designer and made the change but got the following error
'tPersonJobHist' table
- Unable to modify table.
Cannot insert the value NULL into column 'HRBusinessPartnerCode', table 'BD6578.dbo.Tmp_tPersonJobHist'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Question is why wouldn't it let me alter the table design. and what is tmp_tpersonjobhist.?
How can I make this column Not-nullable.
View 2 Replies
View Related
Jun 28, 2004
I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. Thanks
View 7 Replies
View Related
Oct 27, 2015
We are currently developing an OLTP application, which will need to purge data when it becomes older than 1 hour.Rather than having a process which deletes rows periodically (and risks locking the tables), I am considering using partitioning on a rolling 15 minute window.The idea is to have 5 active partitions, with the 5<sup>th</sup> one being swapped out, merged and a new one split in. This will allow data to live to a max of 1 hour 15 mins,which is acceptable.
Actually, I will have 8 partitions; there will be 4 partitions set in the future, just to ensure when the last partition is split, there isn’t any data movement, as the newest partition will be empty.I am wondering if there will be any performance issues due to partition swapping, merging and splitting every 15 minutes? The application will have a high volume of users when live. I think this should be a better option that continually deleting from the tables.
View 6 Replies
View Related
Aug 28, 2006
Hi all,
I am looking for some study meaterial focussed on performance based transact SQL development. I am a fairly well seasoned (3+ years self taught), and I am getting into situations where different query constructs yield the same results, but the performance (execution time and disk I/O) varies.
I have found that sometimes nested select statements execute faster than joins, but usually the opposite is true, for example, and I would like to learn why.
Info would be appreciated.
View 4 Replies
View Related
Oct 12, 2015
I have table A and B. A has column ID,A1,A2,A3,A4,A5 columns. B has ID,B1,B2, A1 columns. A table has a trigger. I defined the below trigger.
Solution 1
ALTER TRIGGER [dbo].[Tri_A]
ON [dbo].[A]
for UPDATE
AS
BEGIN
UPDATE B
SET B.A1= i.A1
FROM inserted i
INNER JOIN B
ON B.ID = i.ID
END;
GO
If I change the above solution 1 trigger to solution 2.Can I improve the trigger performance dramatically? I mean only A.A1 is changed then update B.A1. So when the other columns is changed, the update will not be required.
Solution 2
ALTER TRIGGER [dbo].[Tri_A]
ON [dbo].[A]
for UPDATE
AS
BEGIN
IF ( UPDATE (A1) )
UPDATE B
SET B.A1= i.A1
FROM inserted i
INNER JOIN B
ON B.ID = i.ID
END;
GO
View 2 Replies
View Related
Jun 22, 2015
I have encountered a problem with a specific set of tables. The same select yields slightly differing execution plans in two different environments (instances). But the slight variation seems to contain a huge differences in stats. I don't know the significance of these stats. The two tables have the exact same indices.
This is the selcet statement:
SELECT 'xx' FROM DUKS.dbo.Profiler
WHERE DNA_Løbenummer IN
(SELECT DNA_Løbenummer FROM DUKS.dbo.Effektregister
WHERE Sagsnummer = '2015-00002')
View 17 Replies
View Related
Jul 20, 2005
Hello!I have a problem that I really could use some help to solve. I have a tablewhich looks like this:id1, id2, id3, rate, ratenrExamples of a select * from this table would be:1047336399 21000 1 617 11047336399 21000 1 624B 11047336399 21000 1 621D 11047336399 21000 2 624B 11047336399 21000 2 612A 11047336399 21000 2 621D 11047336399 21000 3 617 11047336399 21000 3 624B 11047336399 21000 3 621D 1I would like to transform this table into something like this:1047336399 21000 1 617 1 624B 1 621D 11047336399 21000 2 624B 1 612A 1 621D 11047336399 21000 3 617 1 624B 1 621D 1the three first columns should be the primary key.Any help is appreciatedGunnar
View 1 Replies
View Related
Jan 23, 2008
Hi!
What is the difference in performance if I use a Temp-table or a local-table variable in a storedprocedure?
Why?
//Daniel
View 5 Replies
View Related
Oct 18, 2001
I have an environment where I would like to grant the ability to perform certain functions to specific users for all databases on one of my SQL Servers. Specifically, I want to allow someone to alter table structures on the server, without having the ability to modify data within any databases on the server and without having the ability to create new databases on the server. Is this possible, and if so, how?
View 2 Replies
View Related
Oct 24, 2001
Pros,
I would like to change the logical filenames for a database on my test server. I know this is done with ALTER DATABASE, but I'm not sure of the exact syntax. Can anyone help?
rb
View 3 Replies
View Related
May 4, 2000
I was able to create a scheduled job in Enterprise Manager 8.0 on a server running 7.0, but I couldn't find a way to disable it. I've used the sp_delete_job stored procedure to delete my job and re-entered it with new parameters.
Is using the sp_delete_job sp only way to modify scheduled jobs?
If yes, I have a problem since my 7.0 Enterprise Manager is gone.
Any ideas?
Pete
View 1 Replies
View Related
Feb 27, 2008
somehow I am not able to figure this out.
How do I change a computed column using the ALTER TABLE ALTER COLUMN... command?
View 1 Replies
View Related
Jan 25, 2004
I have been looking in the SQL Server Help Files, but I cant seem to find the syntax to change the name of a column name..?
Can it be done? if so, what is the syntax?
View 4 Replies
View Related
Sep 19, 2014
I have created a login with some restrictions ,By default the Login has DB_Owner Role,Now i want to change that Role to DB_datareader.
How can i alter the Login role from db_owner to db_datareader.
View 6 Replies
View Related
May 22, 2008
Hi Everyone,
I have a database which once every week gets backup/restored and then renamed for training purpose. The problem is that I got trigger on some of the tables and once the database gets renamed the trigger failed to work as it referring to the old database name. I have idenify all the trigger and done a ALTER TRIGGER. However I can't seem to get it to run as a JOB. Tried to create a store procedure but get a error as the ALTER TIGGER is after CREATE PROCEDURE I could do it as a SQLstring but theirs alot of trigger, want to know if there any other ways of doing it. Please help
View 2 Replies
View Related
Jul 21, 2006
The DATEDIFF(s, time1, time2) function output is of the 'datetime' datatype.
How can I alter the 'datetime' to 'int' (or 'bigint') datatype to make it compatible with other variables in my calculations?
View 7 Replies
View Related
Jul 20, 2005
I have a table (in Access) that is linked to the SQL server, and Ineed to add a column to it. I wrote the following:ALTER TABLE CensusADD COLUMN 'ActiveFacility' BIT;and I get a syntax error that I do not know how to solve. The columnneeds to contain yes/no data for each record.Any help for a struggling newbie?Thanks,Christine
View 1 Replies
View Related
May 19, 2008
I need help in SQL in 2 things:
How do i modify a column to change it to a primary key
How do i add a column that is of type bit, with no nulls, and a default value of ((0))
Thanks
Kevin
View 5 Replies
View Related
Feb 26, 2008
Question: I have Log Shipping setup and i am wanting to verify it will do what we need. That is when a table is changed in our production enviroment that change is replicated into our other server. IE I rename a field in a table or create a new table... You know change the structure... How do I replicate that stuff to my other servers?
View 6 Replies
View Related
Aug 8, 2007
I have a column going into a merge DT_WSTR length 50 on both(left/right) sides, yet when viewing the resulting output metadata, the length is 16. (I am viewing the metadata by clicking on the arrows)
This is obviously wrong.
NB originally the length was not set to this. As an attempt to try and fix the problem, I have added a placeholder column before all of my conditional splits of str50 type.
Why is it doing this?
This is causing my 0xC02020C1 error, I am convinced of it.
One of the errors has now gone away but the other two merges still have the error.
SSIS seems to assume that you will make no mistakes and will never want to go back and change anything, woe betide you if you have to go back and alter it later.
I have also noticed that certain (other) errors go away merely by opening and closing the tasks. Shocking.
View 1 Replies
View Related
Aug 18, 1999
How do I alter a column check constraint?
I have the table:
CREATE TABLE Mytable(
mykey integer,
mycol integer
CHECK(mycol BETWEEN 1 AND 2)
PRIMARY KEY(mykey))
How do I change the constraint to
CHECK(mycol BETWEEN 0 AND 2)
...without losing any data?
Thanks!
Jim
View 1 Replies
View Related
Jul 22, 1998
Folks,
Is there any way I can dynamically alter the structure of a table? In other words if my Transact SQL
statement returns 5 rows I want to alter an existing table and put in 5 columns. Strange?!
Something like
alter table add
column+@i
The alter table statement is within a Cursor and ideally @i should increment each time and alter
the table to put in a new column.
If I went 3 times thru the loop, I should have
column1
column2
column3
added to the new table.
Any pointers would be greatly appreciated. Thanks much in advance.
View 1 Replies
View Related
Jul 23, 2005
Let's say I create a multi-statement function like this:CREATE FUNCTION dbo.Test ()RETURNS @res TABLE (N int NOT NULL CHECK (N >= 0))ASBEGININSERT INTO @resSELECT 1RETURNENDThat works fine. Then I make a change in the function's body, replace theCREATE FUNCTION with ALTER FUNCTION, and execute the batch. I get an error:Server: Msg 3729, Level 16, State 3, Procedure Test, Line 9Cannot ALTER 'dbo.Test' because it is being referenced by object'CK__Test__N__5D2E32EB'.Indeed, if I look at the list of dependencies for the function in QA'sobject tree, I can see the check constraint referenced in the errormessage.ALTER FUNCTION works fine if I don't specify the CHECK constraint in thedefinition of the @res table.So it seems that the only way to modify such a function is to drop andrecreate. Is that a known behavior? Is there any particular reason for it?Thanks.--(remove a 9 to reply by email)
View 1 Replies
View Related