Did you know? -- Altering the length of a fixed-length column

re: Did you know? -- Altering the length of a fixed-length column

Peter W. DeBetta — Fri, 13 Oct 2006 19:34:09 GMT

So then the question is: How do you "recover" that unused space?

I found one way to do it. First, you'll need a not null column, You can either add one, or preferably, use an existing column.

Using an existing column
1. Create and drop a Clustered Primary Key

ALTER TABLE bigchange ADD CONSTRAINT pk_bigchange PRIMARY KEY (col1);
GO
ALTER TABLE bigchange DROP CONSTRAINT pk_bigchange;
GO

2. Run the query that shows the leaf offset and see that the offsets are now "fixed". In this case, the leaf offsets of col1, col2, and col3 are respectively: 4, 6, 3006

Creating a new column
1. Create the new column. If the table has data the column must have some value. In this case, since there wasn't an identity defined, I just added the new column as an identity.

ALTER TABLE bigchange ADD col4 int not null identity(1, 1);
GO

2. Create and drop a Clustered Primary Key

ALTER TABLE bigchange ADD CONSTRAINT pk_bigchange PRIMARY KEY (col1);
GO
ALTER TABLE bigchange DROP CONSTRAINT pk_bigchange;
GO

3. Drop the column you added.

ALTER TABLE bigchange DROP COLUMN col4;

4. Run the query that shows the leaf offset and see that the offsets are now "fixed". In this case, the leaf offsets of col1, col2, and col3 are respectively: 8, 10, 3010. You'll notice that all the leaf offsets are 4 more than in the first example - a remnant of col4 being there.

I'm sure there are other techniques, and if you have them, do tell.

--Peter

re: Did you know? -- Altering the length of a fixed-length column

Peter W. DeBetta — Fri, 13 Oct 2006 19:40:18 GMT

OOps - I forgot to mention that in my first example (using an existing column), I changed col1 to be NOT NULL so that I could do the example. So, if you want to try this yourself, replace the original CREATE TABLE code with:

CREATE TABLE bigchange
(col1 smallint not null, col2 char(2000), col3 char(1000));

--Peter

re: Did you know? -- Altering the length of a fixed-length column

Manu — Mon, 16 Oct 2006 14:26:18 GMT

I remember having this problem once, and I reclaimed the space by recreating the clustered key

re: Did you know? -- Altering the length of a fixed-length column

Peter W. DeBetta — Tue, 17 Oct 2006 15:10:34 GMT

Manu,

I should have made the generalization that any clustered index would do the trick. Thanks for pointing it out.

So if you already have a clustered index or key, you can recreate it. Of course, if the clustered key is being referenced by any foreign keys, you will have to drop those and recreate those references as well.

Finally, another method would be (assuming there is no clustered index on the table)...

CREATE CLUSTERED INDEX ic_bigchange ON bigchange(col1);
GO
DROP INDEX bigchange.ic_bigchange ;
GO

--Peter

re: Did you know? -- Altering the length of a fixed-length column

Ewart — Thu, 30 Nov 2006 04:37:14 GMT

Hi there I've tried the clustered index trick and didn't work for me when changing data types in SQL 2000. If anyone has any ideas:

http://groups.google.co.nz/group/microsoft.public.sqlserver.server/browse_frm/thread/fabeb25ca4c15b89/6f47e93ff9d446b5?hl=en&

regards
ewart

re: Did you know? -- Altering the length of a fixed-length column

Shaun — Fri, 01 Jun 2007 21:48:33 GMT

Changing a type from fixed-decimal to vardecimal does not follow the principal of delayed change. The data is modifed at time of setting.

re: Did you know? -- Altering the length of a fixed-length column

david wei — Wed, 25 Jul 2007 23:25:06 GMT

Kalen,

I know it's an old thread but I am just reviewing all interested articles. (especially yours!)

After done my own research, I found the following statement is not correct:

"You may be well aware that if you alter the length of a column to a larger value, that SQL Server doesn’t go through all the rows at the time you issue the ALTER. The ALTER is a metadata only change, and the actual movement of data doesn’t happen until a row is updated."

When you alter a column (char or varchar), SQL server actually does move the data inside the page, you can noticed this by using DBCC page to check the this column is listed twice and one is marked as dropped. (the total number of columns is also increased by 1)

you mentioned this in your storage book.

The interesting is if you change the length to a SMALLER (not larger) value, SQL server does not move the data.

David Wei

re: Did you know? -- Altering the length of a fixed-length column

Salim Fayad — Mon, 22 Mar 2010 15:08:44 GMT

Hi Kallen,

I wanna congratulate you on the very nice book "Microsoft SQL Server 2008 Internals". But I have 2 questions:

1. If I have 2 columns of type "bit" in one table, how are they being stored? I noticed that they have the same offset and that they are saved in the same byte. Is this correct?

2. I ran your query above and it produced some duplicate column. I noticed that the "sys.partitions" returned 2 records for the same table. Is there some specific criteria that I should put to make it generic to return back all the columns without duplication?

Thank you very much.

Regards,

Salim Fayad

salimfayad@hotmail.com

re: Did you know? -- Altering the length of a fixed-length column

Kalen Delaney — Mon, 22 Mar 2010 15:26:33 GMT

Hello Salim

Up to 8 bit columns will be combined in a single byte, so it will show they have the same offset.

I'm confused about your second question. My output shows one row for every column. Are you saying you get something different, running the same script?

Or are you using a different table? You may need to read more about partitions and sys.partitions view. If you have indexes on the table, each index has its own partitions.

re: Did you know? -- Altering the length of a fixed-length column

Salim Fayad — Tue, 23 Mar 2010 06:24:00 GMT

Hi Kalen,

You are right. There are 2 partitions on this table that I am doing the query. My question was: Is there some criteria to get the default partition that generates all the columns?

Thank you.

re: Did you know? -- Altering the length of a fixed-length column

Salim Fayad — Tue, 23 Mar 2010 09:23:54 GMT

Hi Kalen again,

Can you please help me in how the Transaction Log stores the LOB? (varbinary(MAX), text, ntext, image, ...)?

Thank you in advance

re: Did you know? -- Altering the length of a fixed-length column

Kalen Delaney — Wed, 24 Mar 2010 23:48:47 GMT

Salim

There is no such thing as a 'default' partition. All the partitions contain rows from the table and all the rows have all the columns. However, if you are seeing multiple rows for each column, you can filter the output for only one partition_number...something like: AND partition_number = 1

As for you Transaction log question, MS does not make public exactly how LOB data is stored in log records.

~Kalen

re: Did you know? -- Altering the length of a fixed-length column

Mark Patrick — Tue, 13 Jul 2010 15:14:27 GMT

I used Peter's technique to fix a large custom SQL table, in Sage CRM, that required many field revisions/deletions, due to poor design and haphazard maintenance. About half way through, I was getting row size errors and couldn't finish my clean-up job. Thanks Peter...and Kalen, for pointing out this somewhat obscure fact about SQL. I was down to considering having to delete the table, and everything related to it, and starting over. You both saved me at least 3-4 solid days of work.

re: Did you know? -- Altering the length of a fixed-length column

Martin — Wed, 15 Jun 2011 10:32:41 GMT

Hi Kalen,

I was trying to write a query that I could run against each database to detect tables and indexes with this issue.

In doing so it came to my attention that the JOIN ON column_id = partition_column_id is not necessarily valid.

An example where this is the case is below...

DROP TABLE Foo

CREATE TABLE Foo (A CHAR(65), B CHAR(66), C CHAR(67), D CHAR(68), E CHAR(69));

CREATE CLUSTERED INDEX ixA ON Foo(A);

CREATE NONCLUSTERED INDEX ixB ON Foo(B) INCLUDE (E);

SELECT CASE max_inrow_length

WHEN 4 THEN 'uniquifier'

ELSE CHAR(max_inrow_length)

END AS actual_column_name,

column_id,

name AS column_name_according_to_join,

max_inrow_length,

leaf_offset

FROM sys.system_internals_partition_columns pc

JOIN sys.partitions p

ON p.partition_id = pc.partition_id

LEFT JOIN sys.columns c

ON column_id = partition_column_id

AND c.object_id = p.object_id

WHERE p.object_id = OBJECT_ID('Foo') AND index_id > 1

Which returns

actual_column_name column_id column_name_according_to_join max_inrow_length leaf_offset

------------------ ----------- ------------------------------ ---------------- -----------

B 1 A 66 1

A 3 C 65 67

uniquifier 4 D 4 -1

E 2 B 69 132