 |
|
| SAS/ACCESS Interface to Microsoft SQL Server |
Data Types for Microsoft SQL Server
| Overview |
This section includes information about Microsoft SQL Server null and default values and data conversions.
Every column in a table has a name and a data type. The data type tells the Microsoft SQL Server how much physical storage to set aside for the column and the form in which the data is stored.
| Microsoft SQL Server Null Values |
Microsoft SQL Server has a special value called NULL. A Microsoft SQL Server NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a Microsoft SQL Server NULL value, it interprets it as a SAS missing value.
Microsoft SQL Server columns can be defined as NOT NULL so that they require data--they cannot contain NULL values. When a column is defined as NOT NULL, the DBMS does not add a row to the table unless the row has a value for that column. When creating a DBMS table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.
For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data in SAS/ACCESS for Relational Databases: Reference.
To control how SAS missing character values are handled by Microsoft SQL Server, use the NULLCHAR= and NULLCHARVAL= data set options.
| LIBNAME Statement Data Conversions |
The following table shows all data types and default SAS formats that SAS/ACCESS Interface to Microsoft SQL Server supports.
| Microsoft SQL Server Data Type | Default SAS Format |
|---|---|
| SQL_CHAR | $n |
| SQL_VARCHAR | $n |
| SQL_LONGVARCHAR | $n |
| SQL_BINARY | $n.* |
| SQL_VARBINARY | $n.* |
| SQL_LONGVARBINARY | $n.* |
| SQL_DECIMAL | m or m.n or none if m and n are not specified |
| SQL_NUMERIC | m or m.n or none if m and n are not specified |
| SQL_INTEGER | 11. |
| SQL_SMALLINT | 6. |
| SQL_TINYINT | 4. |
| SQL_BIT | 1. |
| SQL_REAL | none |
| SQL_FLOAT | none |
| SQL_DOUBLE | none |
| SQL_BIGINT | 20. |
| SQL_DATE | DATE9. |
| SQL_TIME |
TIME8.
Microsoft SQL Server cannot support fractions of seconds for time values |
| SQL_TIMESTAMP | DATETIMEm.n where m and n depend on precision |
| * Because the Microsoft SQL Server driver does the conversion, this field displays as if the $HEXn. format were applied. | |
The following table shows the default data types that the Microsoft SQL Server interface uses when creating tables.
| SAS Variable Format | Default Microsoft SQL Server Data Type |
|---|---|
| m.n | SQL_DOUBLE or SQL_NUMERIC using m.n if the DBMS allows it |
| $n. | SQL_VARCHAR using n |
| datetime formats | SQL_TIMESTAMP |
| date formats | SQL_DATE |
| time formats | SQL_TIME |
The Microsoft SQL Server interface allows non-default data types to be specified with the DBTYPE= data set option.
|
|
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.