Export (0) Print
Expand All

File Storage Type

SQL Server 2000

File Storage Type

The file storage type describes how data is stored in the data file. Data can be copied to a data file as its database table type (native format), as a character string in ASCII format (character format), or as any data type where implicit conversion is supported (for example, copying a smallint as an int). User-defined data types are copied as their base types.

To bulk copy data from an instance of Microsoft® SQL Server™ to a data file in the most compact storage possible (native data format), accept the default file storage types provided by bcp.

To bulk copy data from an instance of SQL Server to a data file as ASCII text, specify char as the file storage type for all columns in the table.

To bulk copy data to an instance of SQL Server from a data file, specify the file storage type as char for ASCII-only files, and the following appropriate file storage type for data stored in native data type format.

File storage type Enter at command prompt
char c[har]
varchar c[har]
nchar w
nvarchar w
text T[ext]
ntext W
binary x
varbinary x
image I[mage]
datetime d[ate]
smalldatetime D
decimal n
numeric n
float f[loat]
real r
Int i[nt]
bigint B[igint]
smallint s[mallint]
tinyint t[inyint]
money m[oney]
smallmoney M
Bit b[it]
uniqueidentifier u
sql_variant V[ariant]
timestamp x

Entering a file storage type that represents an invalid implicit conversion causes bcp to fail. For example, specifying smallint for int data causes overflow errors, but specifying int for smallint data is valid. Specifying char as the file storage type when bulk copying any data type from an instance of SQL Server to a data file is always valid.

When noncharacter data types (for example, float, money, datetime, or int) are stored as their database types, the data is written to the data file in the SQL Server internal binary data format.

A format file can also be generated to save the responses of the file storage type for each field. This format file can be used to provide the default information used to bulk copy the data in the data file back into an instance of SQL Server, or to bulk copy data out from the table another time, without needing to respecify the format.

Each native file storage type is recorded in the format file as a corresponding host file data type.

File storage type Host file data type
char SQLCHAR
varchar SQLCHAR
nchar SQLNCHAR
nvarchar SQLNCHAR
text SQLCHAR
ntext SQLNCHAR
binary SQLBINARY
varbinary SQLBINARY
image SQLBINARY
datetime SQLDATETIME
smalldatetime SQLDATETIM4
decimal SQLDECIMAL
numeric SQLNUMERIC
float SQLFLT8
real SQLFLT4
int SQLINT
bigint SQLBIGINT
smallint SQLSMALLINT
tinyint SQLTINYINT
money SQLMONEY
smallmoney SQLMONEY4
bit SQLBIT
uniqueidentifier SQLUNIQUEID
sql_variant SQLVARIANT
timestamp SQLBINARY

Because data files stored as ASCII text use char as the file storage type, only SQLCHAR appears in the format file in those instances.

See Also

Using Format Files

Show:
© 2015 Microsoft