Capítulo II.  Los tipos de datos del lenguaje SQL del motor Microsoft Jet

Publicado el 30/03/2004 por Enrique Martínez Montejo. Enlace original

Contenido

• Tipos de datos del motor Microsoft Jet
• Datos carácter
• Datos numéricos
  • Datos enteros
  • El tipo de dato Autonumérico
  • Datos decimales
  • Datos aproximados
• Datos monetarios
• Datos de fecha y hora
• Datos binarios
• Datos para objetos OLE
• Datos BOOLEAN
• Datos GUID

Tipos de datos del motor Microsoft Jet

A la hora de crear una tabla, es importante definir el tipo de dato que utilizarán las columnas o campos para almacenar los valores de la tabla, a fin de asegurarse que usan la mínima cantidad posible de espacio de almacenamiento.

Los tipos de datos del SQL del motor de base de datos Microsoft Jet se compone de una serie de datos primarios definidos por el propio motor, y de varios tipos de datos sinónimos reconocidos por aquéllos.

La siguiente tabla enumera los tipos de datos del SQL del motor Microsoft Jet, sus sinónimos y los tipos de datos equivalentes en Microsoft SQL Server y en el estándar SQL de ANSI:

SQL Jet	Sinónimos	SQL Server	SQL ANSI
BINARY	VARBINARY, BINARY VARYING	BINARY, VARBINARY	BIT, BIT VARYING
BIT	LOGICAL, LOGICAL1, YESNO	BIT	No admitido
TINYINT	INTEGER1, BYTE	TINYINT	No admitido
COUNTER	AUTOINCREMENT, IDENTITY		No admitido
MONEY	CURRENCY	MONEY	No admitido
DATETIME	DATE, TIME, TIMESTAMP	DATETIME	DATE, TIME, TIMESTAMP
UNIQUEIDENTIFIER	GUID	UNIQUEIDENTIFIER	No admitido
DECIMAL	NUMERIC, DEC	DECIMAL	DECIMAL
REAL	SINGLE, FLOAT4, IEEESINGLE	REAL	REAL
FLOAT	DOUBLE, FLOAT8, IEEEDOUBLE, NUMBER	FLOAT	DOUBLE PRECISION, FLOAT
SMALLINT	SHORT, INTEGER2	SMALLINT	SMALLINT
INTEGER	LONG, INT, INTEGER4	INTEGER	INTEGER
IMAGE	LONGBINARY, GENERAL, OLEOBJECT	IMAGE	No admitido
TEXT	LONGTEXT, LONGCHAR, MEMO, NOTE, NTEXT	TEXT	No admitido
CHAR	TEXT (n), ALPHANUMERIC, CHARACTER, STRING, VARCHAR, NCHAR, CHARACTER VARYING, NATIONAL CHAR, NATIONAL CHARACTER,  NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING	CHAR, VARCHAR, NCHAR, NVARCHAR	CHARACTER, CHARACTER VARYING, NATIONAL CHARACTER, NATIONAL CHARACTER VARYING
No admitido		No admitido	INTERVAL

Datos carácter

Los datos carácter consisten en secuencias de caracteres alfanuméricos utilizados para representar cualquier combinación de números y letras, o para almacenar números que no se utilicen para efectuar cálculos. La siguiente tabla resume los tipos de datos carácter.

Tipo de dato	Descripción
char, character, nchar, national char, national character	Para datos carácter de longitud fija de hasta 255 caracteres. Si no se especifica el número de caracteres, el tamaño del campo será de 255 caracteres. El tamaño de almacenamiento en bytes siempre será el doble del tamaño definido. Si el tamaño del campo es de 25 caracteres, el tamaño de almacenamiento siempre será de 50 bytes, aunque los datos de la columna tengan 5 caracteres. Este tipo de dato es apropiado cuando todos los valores del campo tengan la misma longitud o se aproximen lo suficiente.
alphanumeric, character varying, national char varying, national character varying, string, text (n), varchar	Para datos carácter de longitud variable de hasta 255 caracteres. Si no se especifica el número de caracteres, el tamaño del campo será de 255 caracteres. El tamaño de almacenamiento en bytes será el doble del tamaño real. Si el tamaño del campo es de 25 caracteres, y los datos de la columna ocupan 5 caracteres, el tamaño de almacenamiento será de 10 bytes. Este tipo de dato es apropiado cuando exista una sustancial diferencia de longitud entre los distintos valores del campo.
longtext, longchar, memo, note, ntext, text	Para datos carácter de longitud variable de hasta un máximo de 65.536 caracteres, salvo que el campo no contenga datos binarios, en cuyo caso el límite se encuentra en el tamaño de la base de datos (aproximadamente 2.14 gigabytes ó 1.070,000.000 de caracteres Unicode). El tamaño de almacenamiento en bytes será el doble del tamaño real de los datos almacenados en el campo. Este tipo de dato se utiliza para almacenar texto de gran longitud, como bien puede ser notas o descripciones.

Todos los sinónimos anteriores de tipos de datos sólo se pueden especificar cuando se utilice ADO junto con el proveedor Jet OLE DB 4.0. Si utilizamos la biblioteca de DAO 3.6, o la interfaz de usuario de Microsoft Access, solamente podremos utilizar los sinónimos siguientes:

• Caracteres de longitud fija: CHAR
• Caracteres de longitud variable: ALPHANUMERIC, STRING, TEXT, TEXT (n), VARCHAR
• Cadenas de caracteres largos: LONGTEXT, LONGCHAR, MEMO, NOTE

Si abrimos la tabla en modo de diseño utilizando la interfaz de usuario de Microsoft Access, todos los campos de longitud fija y variable aparecerán con el tipo de dato Texto, por lo que no tendremos posibilidad de diferenciar el tipo de longitud de unos y otros campos. Para diferenciar la longitud tendremos que abrir la tabla, donde observaremos que aparecerá señalada toda la celda de la cuadrícula correspondiente a los campos de longitud fija, estando solamente señalados los datos de las celdas en los campos de texto de longitud variable, tal y como se puede apreciar en las siguientes imágenes.

Referente a los tipos de datos de caracteres largos, cualquier sinónimo que utilicemos para crear o modificar el tipo de dato del campo, en la vista de diseño de la interfaz de usuario de Microsoft Access aparecerán con el tipo de dato Memo.

Si desea comprobar ciertas propiedades de los campos (tamaño definido, de almacenamiento, etc.), puede ejecutar el siguiente ejemplo, siempre y cuando utilice la biblioteca de ADO:

Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

' Creamos un nuevo objeto Recordset
'
Set rst = New ADODB.Recordset
With rst
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open "Tabla1", oConexion, , , adCmdTable
End With

' Leemos, campo a campo, todos los registros de la tabla
'
Do
    For Each fld In rst.Fields
        MsgBox "Tamaño definido: " & fld.DefinedSize & " bytes." & vbCrLf & _
                    "Número de caracteres: " & Len(fld.Value) & vbCrLf & _
                    "Tamaño de almacenamiento: " & fld.ActualSize & " bytes." & vbCrLf & _
                    "Tipo de dato: " & fld.Type, , fld.Name & ": " & fld.Value
    Next
    rst.MoveNext
Loop Until rst.EOF

Datos numéricos

Los datos numéricos sólo consisten en números que pueden ser utilizados para realizar cálculos aritméticos. Al definir un tipo de dato numérico establecemos el número de bytes que se usarán para guardar los datos, lo que afectará a la precisión del número guardado en el campo.

Al igual que los restantes tipos de datos, los numéricos también disponen de un número de sinónimos que podemos utilizar a la hora de definir el tipo de dato, el cual deberá de ser elegido dependiendo de si la tabla permanecerá en una base de datos Jet (Microsoft Access) o será escalada a un servidor de bases de datos, como bien puede ser Microsoft SQL Server. En este último caso, deberá de utilizar un nombre compatible en la declaración del tipo de dato para hacer más fácil el cambio.

Podemos definir los tipos de datos numéricos en varias categorías dependiendo de la forma de representar  los números.

Datos enteros

Estos tipos de datos representan exclusivamente valores numéricos completos, sin decimal alguno, teniendo la mayoría de ellos la capacidad de representar tanto valores positivos como negativos. Los tipos de datos se diferencian principalmente en la magnitud del número que pueden representar, lo que afectará al tamaño de almacenamiento que ocupará el valor de la columna. Por regla general se debería de utilizar el tipo de dato más pequeño posible, poniendo especial cuidado en especificar un tamaño suficiente a fin de que los posibles valores del campo no queden fuera del intervalo del rango permitido por el tipo de dato declarado. La siguiente tabla lista los tipos de datos numéricos enteros.

Tipo de dato	Descripción
tinyint, integer1, byte	Para valores enteros positivos en el intervalo de 0 a 255. Cada valor de la columna ocupa un byte.
smallint, short, integer2	Para valores enteros cortos en el intervalo de -32.768 a 32.767. Cada valor ocupa dos bytes.
integer, long, int, integer4	Para valores enteros largos en el rango de -2.147.483.648 a 2.147.483.647. Cada columna ocupa cuatro bytes.
counter, autoincrement, identity	Para valores en el mismo rango que los tipos de datos enteros largos, y sólo para campos definidos como Autonuméricos. Cada columna ocupa cuatro bytes. Sólo se puede definir en la tabla un campo Autonumérico.

Todos los tipos de datos indicados podemos declararlos para ser ejecutados con la biblioteca de DAO 3.6, así como desde la interfaz de usuario de Microsoft Access, salvo los tipos de datos TINYINT e IDENTITY, que sólo se podrán utilizar con ADO y el proveedor Jet OLE DB 4.0.

El tipo de dato Autonumérico

El tipo de dato COUNTER se utiliza para definir un valor numérico entero largo en una columna que se autoincrementará en cada nuevo registro, teniendo la posibilidad de especificar el valor de inicio y el de incremento a la hora de crear el campo, tal y como se muestra en el siguiente ejemplo, donde se creará un campo Autonumérico de valor inicial 10, incrementándose en 5 unidades por cada nuevo registro que se añada a la tabla:

CREATE TABLE TablaAutoincrementada (
            CampoIncremento IDENTITY (10,5),
            Campo2 CHAR
)

Si no se especifica el valor de inicio y de incremento, ambos valores tendrán por defecto el valor 1. Sólo podrá tener un campo COUNTER en la tabla.

Si utiliza el proveedor Jet OLE DB, también puede utilizar el sinónimo IDENTITY para declarar un tipo de dato Autonumérico, en cuyo caso será compatible con el tipo de dato IDENTITY de SQL Server.

En versiones previas de bases de datos Access, el valor de inicio del campo Autonumérico se reinicializaba al valor máximo disponible después de compactar la base de datos. Esto ya no continúa siendo cierto con la versión 4.0 del motor Microsoft Jet, porque para reinicializar el contador, habría que eliminar primero los registros de la tabla y compactar la base de datos después. Una solución viable sería ejecutar una consulta de modificación de campo mediante el proveedor Jet OLE DB, para indicar el nuevo valor de inicio y de incremento, tal y como se muestra en el siguiente ejemplo:

ALTER TABLE Clientes ALTER COLUMN IdCliente IDENTITY (10,5)

El siguiente registro que se añada a la tabla Clientes, el campo IdCliente tendrá el valor de 10, incrementándose en cinco unidades los valores de los siguientes registros que posteriormente se agregen a la tabla. 

La variable @@IDENTITY es una variable SQL genérica que nos puede ser útil para conocer el último valor introducido en un campo del tipo Autonumérico, con las siguientes peculiaridades:

• Se debe de utilizar junto con la instrucción SELECT sin especificar en ningún caso el nombre de la tabla;
• Para obtener el valor, la consulta siempre se deberá de ejecutar inmediatamente después de haber añadido un nuevo registro a la tabla mediante código;
• Para añadir un valor al resultado de la variable @@IDENTITY, encierre la variable entre corchetes;
• Por último, dicha variable únicamente se puede utilizar con el proveedor Jet OLE DB y la biblioteca de ADO.

El siguiente ejemplo muestra la utilización de la variable global @@IDENTITY:

' Añadimos un nuevo registro a nuestra tabla de Clientes, donde el campo
' IdCliente es del tipo Autonumérico
'
INSERT INTO Clientes (Nombre, Domicilio) VALUES ('Federico López', 'C:\Nueva, 25')

' Leemos el valor añadido al campo IdCliente, por lo que será necesario
' abrir un objeto ADODB.Recordset
'
Dim oRst As New ADODB.Recordset

oRst.Open "SELECT @@IDENTITY As Ultimo_Cliente", _
                oCnn, , , adCmdText

' Leemos el valor
'
MsgBox oRst.Fields!Ultimo_Cliente

Si por ejemplo, deseamos guardar en una variable el siguiente valor del campo, le añadiríamos el valor del incremento que dispone nuestro campo Autonumérico. Suponiendo que el incremento es de cinco unidades, ejecutaríamos la siguiente consulta SQL:

Dim lngSiguienteCliente As Long

oRst.Open "SELECT [@@IDENTITY] + 5 As Proximo_Cliente", oCnn, , , adCmdText

lngSiguienteCliente = oRst.Fields!Proximo_Cliente 

Datos decimales                    SÓLO ADO

A esta categoría de datos pertenecen los tipos de datos DECIMAL y sus sinónimos NUMERIC y DEC, permitiéndose únicamente su declaración para ser ejecutada con la biblioteca de ADO y el proveedor Jet OLE DB 4.0, ya que desde la interfaz de usuario de Microsoft Access o la biblioteca de DAO 3.6, el único nombre de tipo de dato permitido (NUMERIC) crea un campo numérico del tipo DOUBLE.

Al igual que en los tipos de datos enteros, el tipo de datos decimal representa valores de forma exacta, con las siguientes diferencias respecto de los valores enteros:

• Los tipos de datos decimales permiten especificar valores decimales, cosa que no permiten los valores enteros.
• El tipo de dato decimal permite una precisión variable, lo que significa que el número total de dígitos (tanto a la derecha como a la izquierda del punto decimal), puede variar entre uno y 28. Por defecto, la precisión es de 18.
• Igualmente permite designar una escala variable, que vendrá a representar los dígitos que aparecerán a la derecha del separador decimal. Por defecto, el valor de la escala es cero, y nunca puede ser inferior a cero o superior al valor definido para la precisión.

Siendo la precisión p y la escala e, el rango de valores permitidos estaría representado por la siguiente expresión:

  0 ≤ e ≤ p ≤ 28 

A la hora de declarar el tipo de dato decimal podemos especificar la precisión y escala, y en este mismo orden, encerrando sus valores entre paréntesis y separados ambos por una coma, tras el nombre del tipo de dato:

CREATE TABLE TablaDecimal (
            CampoDecimal DECIMAL (20,2)
)

Por último indicar que, según la documentación oficial existente al respecto, el tamaño de almacenamiento de los tipos de datos decimal es de 17 bytes, si bien, al ejecutar el ejemplo anteriormente mencionado, el tipo de dato del campo arroja un tamaño de almacenamiento de 19 bytes.

Datos aproximados

Los tipos de datos numéricos vistos con anterioridad representan los datos de una manera precisa, lo que en gran medida evita los errores de redondeo. Aunque la precisión de los tipos de datos aproximados es muy cercana al valor exacto, dicha disminución se realiza a costa de ofrecer un mayor rango de intervalo de datos posible que los ofrecidos por los tipos de datos enteros y decimales. La siguiente tabla lista los tipos de datos numéricos aproximados, cuyos nombres pueden ser todos especificados para ser ejecutados tanto por  la biblioteca de ADO como por la de DAO, así como desde la interfaz de usuario de Microsoft Access.

Tipo de dato	Descripción
real, single, float4, ieeesingle	Para valores de datos aproximados de coma flotante de precisión simple con un intervalo comprendido entre -3,402823E38 y -1,401298E-45 para valores negativos, y entre 1,401298E-45 y 3,402823E38 para valores positivos. El tamaño de almacenamiento es de 4 bytes.
float, double, float8, ieeedouble, number	Para valores de datos aproximados de coma flotante de precisión doble con un intervalo comprendido entre -1,79769313486231E308 y -4,94065645841247E-324 para valores negativos, y entre 4,94065645841247E-324 y 1,79769313486231E308 para valores positivos. El tamaño de almacenamiento es de 8 bytes.

Datos monetarios

A esta categoría de datos pertenece el tipo de dato MONEY y su sinónimo CURRENCY, los cuales pueden ser especificados para ser ejecutados tanto por  la biblioteca de ADO como por la de DAO, así como desde la interfaz de usuario de Microsoft Access.

Los tipos de datos monetarios comprenden valores de moneda y datos numéricos utilizados en cálculos matemáticos en los que estén implicados datos que contengan entre uno y cuatro decimales, y se encuentren comprendidos entre 922.337.203.685.477,5807 y – 922.337.203.685.477,5808. La precisión es de hasta 15 dígitos a la izquierda del separador decimal y hasta 4 dígitos a la derecha del mismo. Su tamaño de almacenamiento es de 8 bytes.

Cuando se introducen valores en el tipo de dato monetario, se puede utilizar un símbolo de moneda y un punto decimal, pero procure no introducir una coma como valor decimal: utilice 1234.5678 € en lugar de 1.234,5678 €.

Datos de fecha y hora

A esta categoría de datos pertenecen los tipos de datos DATETIME y sus sinónimos DATE, TIME y TIMESTAMP, los cuales pueden ser utilizados con las bibliotecas de ADO y DAO, así como desde la interfaz de usuario de Microsoft Access.

Comprenden valores de fecha y hora dentro del intervalo comprendido entre el 01/01/100 y el 31/12/9999, siendo su tamaño de almacenamiento de 8 bytes.

Datos binarios

Los datos binarios se utilizan para representar datos en su formato binario nativo. La siguiente tabla lista los tipos de datos binarios y sus sinónimos, teniendo en cuenta que el tipo BINARY VARYING sólo se puede especificar si se utiliza la biblioteca de ADO:

Tipo de dato	Descripción
binary	Para datos binarios de longitud fija de hasta 510 bytes de longitud. Se utiliza BINARY(n) para definir el tamaño, donde n es el número de bytes. Su tamaño de almacenamiento es de n bytes. Apropiado cuando todos los campos tienen la misma longitud.
varbinary, binary varying	Para datos binarios de longitud variable de hasta 510 bytes de longitud. Se utiliza VARBINARY(n) para definir el tamaño, donde n es el número máximo de bytes. El tamaño de almacenamiento es de n bytes. Apropiado cuando todos los valores de los campos tengan diferente longitud.

El tipo de dato binario no está disponible en la interfaz de usuario de Microsoft Access, por lo que tendrá que ejecutar una consulta SQL de creación o modificación de tabla, si desea tener un campo con dicho tipo de dato.

Se puede almacenar cualquier tipo de datos en un campo binario, teniendo en cuenta que los datos almacenados en el mismo no se puede traducir a otro tipo de dato como bien podría ser a un tipo de dato de texto. La forma en que se introducen los datos en un campo binario indica cómo aparecerán al mostrarlos.

Si exportamos los datos de la tabla a un archivo de texto, podremos observar la diferencia entre utilizar el tipo de dato BINARY (datos de longitud fija) y VARBINARY (datos de longitud variable):

En el primer ejemplo, se ha ejecutado las siguientes consultas SQL, produciendo el resultado indicado:

CREATE TABLE Tabla1 (Campo1 BINARY (20), Campo2 VARCHAR (18))
INSERT INTO Tabla1 VALUES ('Binario1', 'Primer registro')
INSERT INTO Tabla1 VALUES ('Binario2', 'Segundo registro')

Campo1;Campo2
42 00 69 00 6E 00 61 00 72 00 69 00 6F 00 31 00 00 00 00 00 ;Primer registro
42 00 69 00 6E 00 61 00 72 00 69 00 6F 00 32 00 00 00 00 00 ;Segundo registro

En el segundo, las siguientes consultas:

CREATE TABLE Tabla2 (Campo1 VARBINARY (20), Campo2 VARCHAR (18))
INSERT INTO Tabla2 VALUES ('Binario1', 'Primer registro')
INSERT INTO Tabla2 VALUES ('Binario2', 'Segundo registro')

Campo1;Campo2
42 00 69 00 6E 00 61 00 72 00 69 00 6F 00 31 00 ;Primer registro
42 00 69 00 6E 00 61 00 72 00 69 00 6F 00 32 00 ;Segundo registro

Como podrá observar, a pesar que los dos campos binarios tienen la misma longitud (20), al utilizar BINARY el campo se rellenará de pares de ceros hasta completar el tamaño definido, lo que no sucede si especificamos VARBINARY.

Datos para objetos OLE

A esta categoría de datos pertenecen los tipos de datos IMAGE y sus sinónimos LONGBINARY, GENERAL y OLEOBJECT, los cuales pueden ser utilizados con las bibliotecas de ADO y DAO, así como desde la interfaz de usuario de Microsoft Access.

Los tipos de dato Objeto OLE se utilizan para guardar objetos binarios largos, como documentos de Word u hojas de cálculo Microsoft Excel, así como archivos gráficos. El tamaño máximo puede llegar hasta 2.14 gigabytes.

Datos BOOLEAN

El tipo de dato BIT se utiliza para representar datos Verdadero/Falso o Sí/No, siendo sus sinónimos BIT, LOGICAL y LOGICAL1, nombres estos que pueden ser utilizados con las bibliotecas de ADO y DAO o desde la interfaz de usuario de Microsoft Access. Un valor Verdadero es equivalente a -1 mientras que un valor Falso es igual a 0, o dicho de otra manera: todo valor numérico distinto de 0 es Verdadero. El tamaño de almacenamiento es de 1 byte.

Datos GUID

El tipo de dato UNIQUEIDENTIFIER y su sinónimo GUID (este último sólo se puede utilizar con Microsoft Access o con la biblioteca de DAO), indica un número de identificación global único de 16 bytes, utilizado en una base de datos de Access para establecer un identificador exclusivo para la replicación. Los GUID se utilizan para identificar réplicas, conjuntos de réplicas, tablas, registros y otros objetos. En una base de datos de Access, los GUID se denominan Id. de réplica.

Se puede especificar un valor UNIQUEIDENTIFIER como una cadena de 32 caracteres hexadecimales, según el siguiente formato: {12345678-90AB-CDEF-1234-567890ABCDEF}.

Para generar un valor GUID puede utilizar la función API CoCreateGuid, donde puede encontrar un ejemplo en el siguiente artículo de la Base del Conocimiento: Cómo Usar la API CoCreateGUID para Generar un GUID con VB.

Si lo desea, también le puede servir el valor devuelto por la siguiente función:

Private Function GenerarGUID(ByVal iLongitud As Integer) As String

    Dim x As Integer, strGUID As String
    Const strValidar = "0123456789ABCDEF"

    Randomize Timer

    For x = 1 To iLongitud
        strGUID = strGUID & Mid(strValidar, Int(Rnd(1) * Len(strValidar)) + 1, 1)
    Next

    GenerarGUID = strGUID

End Function

Para llamar a la función utilizaríamos la siguiente sintaxis:

Dim strTemp As String

' Generamos el GUID
strTemp = GenerarGUID (8) & "-" & _
                GenerarGUID (4) & "-" & _
                GenerarGUID (4) & "-" & _
                GenerarGUID (4) & "-" & _
                GenerarGUID (12)

' Encerramos la cadena entre llaves
strTemp = "{" & strTemp & "}"

' Mostramos el resultado
MsgBox strTemp