Drop Clustered Indexes? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Drop Clustered Indexes?

Author

Topic

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-28 : 07:19:29

We have a database that is rife with inappropriate data types that we'd like to correct. I know that all indexes that the target column participates in have to be dropped before altering the data type, but should I also drop the clustered index? One of our tables is 250,000,000 + records, and I'm about to change a field from nvarchar(30) to int.

Jim

Everyday I learn something that somebody else already knew

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-28 : 07:52:28

Is the column you're trying to change the clustered index key?

--
Gail Shaw
SQL Server MVP

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-28 : 08:05:58

No. It's just an nvarchar(30) that has nothing but ints in it (it's even called iValue!). After all the data type changes are done -- this table also has ntext fields -- and a;; the other similar changes are done to the othere tables, we're going shrink the database and recreate/rebuild the indexes

Jim

Everyday I learn something that somebody else already knew

Kristen
Test

22859 Posts

Posted - 2011-10-28 : 08:14:54

Won't you have to Drop/Recreate the table to change data type? (which would take care of Drop/Recreate the Clustered Index, and all other indexes) or are you going to add a new column, copy over the data, and then drop the original column and rename NewColumn to OldColumnName?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-28 : 08:22:01

I was planning on just doing an ALTER TABLE ALTER COLUMN statement.

Jim

Everyday I learn something that somebody else already knew

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-28 : 08:41:45

If it's not the clustered key, you don't have to drop the clustered index to change it.

Alter table ... alter column works fine for changing data types.

--
Gail Shaw
SQL Server MVP

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-28 : 08:46:00

Changing the data type won't cause a huge reshuffling of all the pages as that field goes from 60 bytes to 4? That's what I'm ost concerned about.

Jim

Everyday I learn something that somebody else already knew

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 10:03:20

Create a new table with the correct datatype

Sanitize the data to make sure it will meet the new definitions

bcp out the data

then bcp into the new table

then use sp_rename

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-10-28 : 10:45:10

"bcp out the data"

What format? I presume (but don't know for sure) that you cannot use NATIVE if you exporting VARCHAR and importing into INT?

Thus, for any other format, isn't there risk that some character in the data may disrupt the import of the file? (embedded comma, quote, TAB or Linebreak, for example)

Kristen
Test

22859 Posts

Posted - 2011-10-28 : 10:47:21

quote:
Originally posted by GilaMonster

Alter table ... alter column works fine for changing data types.

I really ought to stop getting SSMS to write my scripts for me!

Thanks Gail.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-28 : 11:13:22

quote:
Originally posted by jimf

Changing the data type won't cause a huge reshuffling of all the pages as that field goes from 60 bytes to 4? That's what I'm ost concerned about.

No. Shouldn't in fact do anything to the pages. You'll have to rebuild the clustered index before any of the space freed by this is available.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 11:35:14

quote:
Originally posted by Kristen

"bcp out the data"

What format? I presume (but don't know for sure) that you cannot use NATIVE if you exporting VARCHAR and importing into INT?

Thus, for any other format, isn't there risk that some character in the data may disrupt the import of the file? (embedded comma, quote, TAB or Linebreak, for example)

I was thinking -c -t"|~|"

And I did mention sanitizing the data BEFORE you bcp out

Otherwise, YES, it will f up

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 11:38:08

and if someone put's pipe tilda pipe in their data...screw them

REPLACE(col,'|~|','xxx')

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-28 : 11:40:27

quote:
No. Shouldn't in fact do anything to the pages. You'll have to rebuild the clustered index before any of the space freed by this is available.

Thanks Gail. That's what I was looking for!

Jim

Everyday I learn something that somebody else already knew

Kristen
Test

22859 Posts

Posted - 2011-10-28 : 11:49:02

"pipe tilda pipe"

Ah, I remember you mentioning that before. Its a good choice.

Any issue with embedded Linebreak in the fields in BCP? Will BCP keep absorbing fields until it has enough for the row, and THEN expect a Linebreak? or will the RowTerminator be acted on even if the row appears to be "short"?

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 11:55:50

No..I've run into that problem as well

REPLACE(Col, CHAR(13)+CHAR(10),'^')

and then put them back

with some dynamic SQL...

+ 'WHERE LEN([' + COLUMN_NAME + ']) <> LEN(REPLACE(['+COLUMN_NAME+'],CHAR(13)+CHAR(10),'+''''+''''+'))'

painful stuff..I think I've hit every problem

Even whne the 3rd part developers use reserved words as co9lumns..bcp will spit it out, but throws up on the way back in (using format files)..so I then need to use sp_rename..

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-10-28 : 12:26:38

OK, so (where space available)


CREATE TABLE TempTable(...)

INSERT INTO TempTable (...) SELECT Col1, COl2, CONVERT(int, Col3), ... FROM OriginalTable

DROP TABLE OriginalTable
sp_rename TempTable, OriginalTable

would be a possible cure for that (including needing to handle all constraints, FKeys, Indexes etc etc

But if implicit conversion will handle it then just changing the datatype, in situ, seems like a god-send.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources