Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Is it safe to use "update syscolumns set colstat" Expand / Collapse
Author
Message
Posted Monday, December 29, 2008 12:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:46 PM
Points: 182, Visits: 737
Hello guys,
I have a very large table(100,000,000 records), and I have an identity field ,which is primary key.I want to take out this identity property from my primary key.Is it safe to work with system tables and fallowing TSQL:
sp_configure 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it's an identity column */
where id = object_id('Table1')
and name = 'Id'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go
Post #626777
Posted Monday, December 29, 2008 12:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 10,381, Visits: 13,441
I don't believe you can update the system tables in SQL Server 2005. I wouldn't do it even if you can.

The usual suggestion for this type of issue is to do a select into new_table and then drop the old table and do an sp_rename on the new table.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #626788
Posted Monday, December 29, 2008 12:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
I agree - really bad idea.

Use an ALTER TABLE script or copy the data into another table. Don't try to do this manually in the system tables.
Post #626789
Posted Monday, December 29, 2008 1:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:46 PM
Points: 182, Visits: 737
but the problem is that,the speed is very important for my operation.if I use a new table and then transfer the data,it takes a long time cause of large amount of data.but I want to do in less than 1 minutes.
Post #626799
Posted Monday, December 29, 2008 1:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
Can ALTER TABLE affect the IDENTITY property? I'm looking at help online for ALTER TABLE and can't see it. If you search "identity columns/modifying properties", it just points you to Object Explorer.

This is probably why the OP is asking the question in the first place.

Why on earth does this have to be done in one minute? Are you going to turn it right back on again? After 100 million rows, you just decided identity wasn't the right approach? Or are you just wanting to slip a couple of new rows in where previous ones have been deleted and you are trying to maintain the numbering scheme? Something is amiss here.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #626806
Posted Monday, December 29, 2008 1:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
You don't have access to system tables in 2005. Those are views that you're seeing.

I wouldn't do something like that on a bet, especially on a production system that I was paid to care for.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #626815
Posted Monday, December 29, 2008 1:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:55 PM
Points: 7,179, Visits: 15,780
What's the goal? Is it important to drop the column, or are you only looking to change the PRIMARY KEY constraint? If the latter, you don't have to drop the column, you could simply delete the PRIMARY KEY constraint and rebuild it. If you don't force a change to the clustered index, it shouldn't be slow at all.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #626824
Posted Monday, December 29, 2008 1:48 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
Matt, that was my first thought, but there's a problem if they just defined the identity property for the column without expressly creating a constraint.

Try this:

Create table dbo.dummy (ID int identity(1,1) primary key, filler char(100))

select * from information_schema.constraint_column_usage
where table_name = 'dummy'


There's a constraint on the ID column for the primary key, but not one for identity. You can drop that constraint and ID is no longer the primary key, but it is still an identity column.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #626831
Posted Monday, December 29, 2008 1:51 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
Aspet Golestanian Namagerdi (12/29/2008)
Is it safe to work with system tables and fallowing TSQL:


If your goal is to corrupt your database and cause downtime and loss of data, sure.

It is never safe to fiddle with the system tables, on any version. If you're not really careful and know exactly what you;re doing, you can cause major problems, and that's in SQL 2000.
In 2005 the system tables are hidden and aren't even readable. syscolumns is a view, and is only there for backward compatibility with SQL 2000. The actual table is syscolpars.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #626834
Posted Monday, December 29, 2008 2:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:55 PM
Points: 7,179, Visits: 15,780
Bob Hovious (12/29/2008)
Matt, that was my first thought, but there's a problem if they just defined the identity property for the column without expressly creating a constraint.

Try this:

Create table dbo.dummy (ID int identity(1,1) primary key, filler char(100))

select * from information_schema.constraint_column_usage
where table_name = 'dummy'


There's a constraint on the ID column for the primary key, but not one for identity. You can drop that constraint and ID is no longer the primary key, but it is still an identity column.


Ack - read that backwards.... He wants to KEEP the PK, just not the Identity property.

Agreed - no way to do that without what will end up being a "drop and rebuild table" process. Even doing it from SSMS will drop and recreate the whole table.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #626839
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse