October 24, 2007 at 4:41 am
Hi,
I have a table with 100 mil rows and I intend to change the datatype of one of the columns from nvarchar(4000) to nvarchar(max).
Is it wise to just do it in one step "alter table .. alter column .. " or maybe I should use some other tricks (like creating a new column etc ..)
Thank for help !
R
October 24, 2007 at 4:59 am
nvarchar :-
=========
Each column in a table in a Microsoft SQL Server 2005 Mobile Edition (SQL Server Mobile) database supports a set of data types that specify the type of data that the column can hold.
Note:
There might be minor differences between Microsoft SQL Server 2005 and SQL Server Mobile in the way data types are promoted when the execution of a function results in an overflow or underflow.
SQL Server Mobile supports the following data types.
Data Type Description
bigint
Integer (whole number) data from –2^63 (–9,223,372,036,854,775,808) through 2^63–1 (9,223,372,036,854,775,807). Storage size is 8 bytes.
integer
Integer (whole number) data from –2^31 (–2,147,483,648) through 2^31–1 (2,147,483,647).
Storage size is 4 bytes.
smallint
Integer data from –32,768 to 32,767. Storage size is 2 bytes.
tinyint
Integer data from 0 to 255. Storage size is 1 byte.
bit
Integer data with a value of either 1 or 0.
Storage size is 1 bit.
numeric (p, s)
Fixed-precision and scale-numeric data from –10^38+1 through 10^38–1. The p variable specifies precision and can vary between 1 and 38. The s variable specifies scale and can vary between 0 and p.
Storage size is 19 bytes.
money
Monetary data values from (–2^63/10000) (–922,337,203,685,477.5808) through 2^63–1 (922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.
float
Floating point number data from –1.79E +308 through 1.79E+308
Storage size is 8 bytes.
real
Floating precision number data from –3.40E+38 through 3.40E+38.
Storage size is 4 bytes.
datetime
Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds.
Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.
national character(n)
Synonym:nchar(n)
Fixed-length Unicode data with a maximum length of 4000 characters. Default length = 1. Storage size, in bytes, is two times the number of characters entered.
national character varying(n)
Synonym:nvarchar(n)
Variable-length Unicode data with a length of 1 to 4000 characters. Default length = 1. Storage size, in bytes, is two times the number of characters entered.
ntext
Variable-length Unicode data with a maximum length of (2^30–2)/2 (536,870,911) characters. Storage size, in bytes, is two times the number of characters entered.
Note:
ntext is no longer supported in string functions.
binary(n)
Fixed-length binary data with a maximum length of 8000 bytes. Default length = 1.
Storage size is fixed, which is the length in bytes declared in the type.
varbinary(n)
Variable-length binary data with a maximum length of 8000 bytes. Default length = 1.
Storage size varies. It is the length of the value in bytes.
image
Variable-length binary data with a maximum length of 2^30–1 (1,073,741,823) bytes.
Storage is the length of the value in bytes.
uniqueidentifier
A globally unique identifier (GUID). Storage size is 16 bytes.
IDENTITY [(s, i)]
This is a property of a data column, not a distinct data type.
Only data columns of the integer data types can be used for identity columns. A table can have only one identity column. A seed and increment can be specified and the column cannot be updated.
s (seed) = starting value
i (increment) = increment value
ROWGUIDCOL
This is a property of a data column, not a distinct data type. It is a column in a table that is defined by using the uniqueidentifier data type. A table can have only one ROWGUIDCOL column.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
nvarchar(max) :
============
nvarchar [ ( n | max ) ]
Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying.
October 24, 2007 at 5:03 am
If your concern is the wasted space that is common when you change or drop a fixed length column, then this is is a problem when you move from varchar to varchar(max).
What will happen is that a few bytes will be wasted in your new table because the old varchar(n) column meta information will stay in the system tables (sys.syshobtcolumns) (you need admin connection to see this table). The original variable length column will be hidden in the sys.columns.
If this disturbs you, the best thing is to rebuild the table. (create a new one with the correct schema, and then copy the data across, change the name ...)
Regards,
Andras
October 24, 2007 at 5:04 am
Thank yo mate but I am more concerned about locks on the table and so on ... I was just doing some tests and it seems the table is locked while I am doing the alter command(alter column ...) such that I am not even able to select a row.
thank,
R
October 24, 2007 at 5:05 am
Thank Andras, I will take it in consideration.
R
October 24, 2007 at 5:07 am
if you are serious about doing some tests with the logics that you were trying to implement with the respective table of a databases., then better create multiple temp tables and work on the test logics.
If you are satisfied with the same, drop the existing table from the schema/object re-name your test table with the valid name and make them available online to continue with the process
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply