THE ROBERT O. ANDERSON SCHOOL AND GRADUATE SCHOOL OF MANAGEMENT

1. THE ROBERT O. ANDERSON SCHOOL AND GRADUATE SCHOOL OF MANAGEMENT The University of New Mexico MGT 329/637 – Spring 2009 SQL Server 2005 Data Types Last Revised: 11/18/2014 10:06 PM

2. Data Types Overview • Data comes in many different kinds such as: • Numbers • Characters • Pictures • Sound • Money • Dates and Times • A computer stores all data values as sequences of binary digits (ones and zeros) • The way in which data are represented and stored as ones and zeros varies depending on the kind of data • A data type is one particular method of storing one particular kind of data as a sequence of ones and zeros

3. Data Type Characteristics • Data types also vary in length and standardization: • Length: How many bits or bytes store each data value? • Bit – a single binary digit (zero or one) • Byte – a sequence of 8 bits • Standardization: Some data types follow a widely-adopted standard, for example: • IEEE floating point – used for real numbers (numeric values that have, or can have, values on both sides of the decimal point) • ASCII – used for characters of English and some European languages • Unicode – used for characters of all the World’s languages

4. Data Type Conversion Issues • When moving data from one place to another data types must often be converted: • A data type used by at the source may not exist in the destination or vice versa • The destination may require a specific data type that differs from how similar data is stored at the source • Data type conversion can be source of error, for example: • Storing a real number in a data type intended for integers truncates the fractional part of the value (loss of precision) • Storing a long sequence of characters in a shorter target location loses some of the trailing characters (truncation) • An incorrect conversion can yield “garbage” such as when the ASCII character ‘A’ is converted to the integer value 33

5. Databases and Data Types • Every column in a database schema is defined as a specific data type • When data is added to the database it must either already be in the defined data type or must be converted • Conversion can be: • Implicit – For example, An SQL Insert statement represents all data values as ASCII or Unicode characters – the DBMS implicitly (automatically) converts each value to the appropriate data type • Explicit – A user, programmer, or a predefined software component defines what data values are converted to what types and how errors are handled

6. SQL Server 2005 Data Types • A subset of the data types follows: • Bit/Boolean [DT_BOOL] –stores values that are either true or false • Currency [DT_CY] – stores monetary values in a form that simplifies currency conversion • Database timestamp [DT_DBTIMESTAMP] – stores data and time in a single value that simplifies date/time comparisons and calendar conversions • Double-precision float [DT_R8] – stores real values that can contain many digits on either or both sides of the decimal point • Four-byte signed integer [DT_I4] – stores integer (whole number) values that can be zero, positive, or negative with absolute value up to approximately 2 billion • String [DT_STR] – stores variable-length sequences of ASCII characters • Unicode string [DT_WSTR] – stores variable-length sequences of Unicode characters