-2

I want to convert a column from varchar(50) to bigint or int.

What are the disadvantages when tables has 2 billion data in SQL Server 2012?

Length of data is 11 and data has only numbers. They were stored as varchar. Now I have to create index to it. So I think I have to convert to int to create index. There are no constraints and no index on that table. No one use that table for now. For example:

data
-----
12345678911
12345678915
12345678911
12345678911
12345678914
12345678913
12345678912

closed as unclear what you're asking by ypercubeᵀᴹ, John aka hot2use, mustaccio, Erik Darling, LowlyDBA Feb 12 '18 at 15:57

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    It would also help to add the CREATE TABLE statement, including all indexes. And tell us if there are any foreign keys that reference this table//column. – ypercubeᵀᴹ Feb 12 '18 at 12:11
  • Is it 2 billion bytes of data or 2 billion rows of data? The latter means a much larger volume of data than the former. Of course, the table is too big for conversion in one pass either way, but it would still be better to describe the size less ambiguously. – Andriy M Feb 12 '18 at 14:55
2

Community wiki answer:

You can create an index on a varchar column, it just won't be as efficient as if you had used a more appropriate data type. But searching on the index will be better than a full table scan.

It would probably be better to create a new table rather than changing the existing column type.

You could do that with

SELECT CAST(YourVarcharColumn AS bigint) AS YourBigintColumn 
INTO dbo.NewTable 
FROM OriginalTable 

or with INSERT...SELECT.

Regarding the idea of creating a new table and populating in batches, see Restructure 100 Million Row (or more) Tables in Seconds. SRSLY! on SQL Server Central.

8

Even if the real length of a column is 11, you are not passing from the column of the same type to the column of the same type but with smaller size. So it will be not "metadata-only" operation. Another column will be created instead of your varchar, and this time the column will be fixed-length column.

This means that every row will be touched. If there is not enough space on every page to accomodate new column, new page will be allocated. This means you'll get a lot of forwarding records in case of a heap and a lot of page splits in case of clustered table.

In every case your table size will grow until the next table rebuild, and it will be made in one transaction that means huge log writing and much time engaged.

If you absolutely need to change this column type, it may be more convenient new column of correct type creation an its updating in a small batches, then dropping old column, or maybe new table creation will be less resource intensive because you can split loading data into batches (but it will require additional space equal to your table size).

You can also consider adding computed column as a cast of existing column and creating index on it.

-5

You will need to go bigint as 11 digits is too big for int.

You will save space (once complete).

Downside is that it will take time and space to convert.

If you have leading 0 or not all exactly 11 digits then sort will be different.

  • Until rebuilt, there will be no space saving, moreover, the table size will grow. And rebuild will require additional space equal to the size of existing table. – sepupic Feb 12 '18 at 13:12
  • @sepupic That is what the answer states. – paparazzo Feb 12 '18 at 13:17
  • @Paparazzi >>>Once complete<<< Once complete ALTER TABLE ALTER COLUMN there will be only increase in size, if my comment is not clear – sepupic Feb 12 '18 at 14:09
  • @sepupic Not going to argue symantics with you. OP does not ask how to complete. – paparazzo Feb 12 '18 at 14:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.