Trabajar con Tipos de Datos y Funciones Integradas

Cuando definimos columnas en tablas, parámetros en procedimientos y funciones, y variables en lotes T-SQL, necesita elegirles un tipo de datos. El tipo de dato restringe los datos que son soportados, además de encapsular el comportamiento que opera sobre los datos, exponiéndolos a través de operadores y de otros medios. Debido a que los tipos de datos son un componente fundamental de sus datos, todo es construido por encima, sus opciones de tipos de datos tendrán implicaciones dramáticas para su aplicación en muchas capas diferentes. Por lo tanto, esta es un área que no debe tomarse a la ligera, sino que debe tratarse con mucho cuidado y atención. Su conocimiento de los tipos es fundamental tanto para la definición de datos, como para la manipulación de datos.

T-SQL soporta muchas funciones integradas que puede utilizar para manipular los datos. Porque las funciones operan sobre valores de entrada y retornan valores de salida, un entendimiento de las funciones integradas va de la mano con una comprensión de los tipos de datos.

Note que este post no pretende cubrir exhaustivamente todos los tipos y todas las funciones que T-SQL soporta. En lugar de ello, este post explica los factores que necesita considerar cuando elige un tipo de datos, y los aspectos clave del trabajo con funciones, por lo general en el contexto de ciertos tipos de datos, como los datos date y time o datos character.

 

Escoger el Tipo de Dato Apropiado

Escoger los tipos de datos apropiados para sus atributos es probablemente una de las decisiones más importantes que tomará con respecto a sus datos. SQL Server soporta muchos tipos de datos de diferentes categorías: numéricos exactos (INT, NUMERIC), cadena de caracteres (CHAR, VARCHAR), cadenas de caracteres Unicode (NCHAR, NVARCHAR), numéricos aproximados (FLOAT, REAL), cadenas binarias (BINARY, VARBINARY), fecha y tiempo (DATE, TIME, DATETIME2, SMALLDATETIME, DATETIMEOFFSET, DATETIME), y otros. Hay muchas opciones, por lo que podría parecer una tarea difícil, pero siempre y cuando siga ciertos principios, puede ser inteligente acerca de sus elecciones, lo que resulta en una base de datos robusta, coherente, y eficiente.

Una de las grandes fortalezas del modelo relacional es la importancia que le da a la aplicación de la integridad de datos como parte del modelo mismo, en múltiples niveles. Un aspecto importante al escoger el tipo apropiado para sus datos es recordar que un tipo es una restricción. Esto significa que tiene un cierto dominio de valores soportados y no permitirá valores fuera de ese dominio. Por ejemplo, el tipo DATE permite sólo fechas válidas. Un intento de introducir algo que no es una fecha, como 'abc' o '20120230', es rechazado. Si tiene un atributo que se supone representa una fecha, como por ejemplo la fecha de nacimiento, y se utiliza un tipo como INT o CHAR, no nos beneficiaremos de la validación integrada de fechas. Un tipo INT no impedirá un valor como 99999999 y un tipo CHAR no impedirá un valor como '20120230'.

Así como un tipo es una restricción, el NOT NULL es una restricción también. Si un atributo no se supone que permita NULLs, es importante hacer cumplir una restricción NOT NULL como parte de su definición. De lo contrario, los NULLs encontraran su camino en su atributo.

También, quiere asegurarse de que no confundirá el formato de un valor con su tipo. A veces, la gente utiliza las cadenas de caracteres para almacenar fechas porque piensan en almacenar una fecha en un formato determinado. El formato de un valor se supone que es responsabilidad de la aplicación cuando los datos son presentados. El tipo es una propiedad del valor almacenado en la base de datos, y el formato de almacenamiento interno no debería ser su asunto. Este aspecto tiene que ver con un principio importante en el modelo relacional llamado independencia de datos físicos.

Un tipo de datos encapsula el comportamiento. Si utiliza un tipo inadecuado, se olvida de todo el comportamiento encapsulado en el tipo, en la forma de operadores y funciones que lo soportan. Como un ejemplo sencillo, para los tipos que representan números, el operador más (+) representa adición, pero para las cadenas de caracteres, el mismo operador representa concatenación. Si elige un tipo inapropiado para su valor, a veces tiene que convertir el tipo (explícita o implícitamente), y a veces hacer malabares en el valor, con el fin de tratarlo como lo que se supone que es.

Otro principio importante en la elección del tipo apropiado para sus datos es el tamaño. A menudo uno de los principales aspectos que afectan el rendimiento de la consulta es la cantidad de E/S involucrada. Una consulta que lee menos, simplemente tiende a correr más rápido. Cuanto más grande es el tipo que utiliza, mayor es el almacenamiento que utiliza. Las tablas con muchos millones de filas, si no billones, son comunes hoy en día. Cuando inicia multiplicando el tamaño de un tipo por el número de filas en la tabla, los números pueden llegar a ser rápidamente significativos. A modo de ejemplo, suponga que tiene un atributo que representa resultados de pruebas, que son enteros en el rango de 0 al 100. El uso de un tipo de datos INT para este propósito es una exageración. Usaría 4 bytes por valor, mientras que una TINYINT usaría sólo 1 byte, y por lo tanto es el tipo más apropiado para este caso. Del mismo modo, para los datos que supuestamente representan fechas, la gente tiene una tendencia a utilizar DATETIME, que utiliza 8 bytes de almacenamiento. Si se supone que el valor representa una fecha sin un tiempo, debe usar DATE, que utiliza sólo 3 bytes de almacenamiento. Si se supone que el valor representa tanto fecha y tiempo, debería considerar DATETIME2 o SMALLDATETIME. El primero requiere almacenamiento entre 6 a 8 bytes (dependiendo de la precisión), y como valor añadido, ofrece una amplia gama de fechas y mejoras, de precisión controlable. El último sólo utiliza 4 bytes de almacenamiento, por lo tanto, si soporta un rango de fechas y precisión que cubra sus necesidades, debe utilizarlo. En resumen, debe utilizar el tipo más pequeño que sirva a sus necesidades. Aunque, por supuesto, esto no se aplica en ejecuciones cortas, pero si en ejecuciones largas. Por ejemplo, el uso de un tipo INT para una llave en una tabla que de un momento a otro crecerá a un grado de billones de filas, es una mala idea. Debería utilizar BIGINT. Pero el uso de INT para un atributo que representa resultados de pruebas o DATETIME para los valores de fecha y hora que requieren una precisión al minuto, son ambas malas opciones, más aún cuando piensa en ejecuciones largas.

Tenga mucho cuidado con los tipos imprecisos FLOAT y REAL. Las dos primeras oraciones en la documentación que describe estos tipos, debería darle un buen sentido de su naturaleza: "tipo de datos numéricos aproximados, para usarlo con datos numéricos de punto flotante. Los datos de punto flotante son aproximados; por lo tanto, no todos los valores en el rango del tipo de datos pueden ser representados con exactitud". El beneficio en estos tipos es que pueden representar números muy grandes y muy pequeños, más allá de cualquier otro tipo numérico que pueda representar y soportar el SQL Server. Así, por ejemplo, si necesita representar números muy grandes o muy pequeños, para fines científicos y no necesita una completa precisión, puede encontrar estos tipos de utilidad. También son bastante económicos (4 bytes para REAL y 8 bytes para FLOAT). Pero no los utilice para cosas que se supone que deban ser precisas.

El Problema Float

Recordamos un caso donde un cliente utilizaba FLOAT para representar los números de código de barras de productos, y estaba entonces sorprendido por no conseguir el producto correcto al escanear los códigos de barras de los productos. También, recientemente, se obtuvo una consulta acerca de la conversión de un valor FLOAT a NUMERIC, resultando en un valor diferente del que se ingresó. Aquí está el caso.

DECLARE @f AS FLOAT = '29545428.022495';

SELECT CAST(@f AS NUMERIC(28, 14)) AS Valor;

¿Puede adivinar el resultado de este código? Aquí está.

Valor

---------------------------------------

29545428.02249500200000

Como se ha mencionado, algunos valores no pueden ser representados con precisión.

En resumen, asegúrese de usar los tipos numéricos exactos cuando necesite representar valores precisos, y reservar el uso de tipos numéricos aproximados solo en los casos en que tenga la certeza de que sea aceptable para la aplicación.

Otro aspecto importante en la elección de un tipo, tiene que ver con la elección de los tipos fijos (CHAR, NCHAR, BINARY) versus los dinámicos (VARCHAR, NVARCHAR, VARBINARY). Los tipos fijos utilizan el almacenamiento para el tamaño indicado; por ejemplo, CHAR (30) utiliza el almacenamiento para 30 caracteres, ya sea que en realidad especifique 30 caracteres o menos. Esto significa que al actualizar no requerirá que la fila se expanda físicamente, y por lo tanto el desplazamiento de datos no es requerido. Así que para los atributos que son actualizados frecuentemente, donde el rendimiento de la actualización es una prioridad, debe considerar los tipos fijos. Note que cuando la compresión es utilizada, específicamente la compresión de filas, SQL Server almacena los tipos fijos como si fueran variables, pero con menos sobrecarga.

Los tipos variables utilizan el almacenamiento para el que ingresa, además de un par de bytes para información offset (o 4 bits con la compresión de fila). Así que para amplias variaciones de tamaños de cadena, si utiliza los tipos variables puede ahorrar mucho de almacenamiento. Como ya se mencionó, a menos almacenamiento utilizado, menos tiene una consulta para leer, y más rápido se puede realizar la consulta. Así los tipos de longitud variable son usualmente preferibles en tales casos, cuando el rendimiento de lectura es una prioridad.

Con las cadenas de caracteres, también existe la cuestión de utilizar los tipos de caracteres regulares (CHAR, VARCHAR) versus los tipos Unicode (NCHAR, NVARCHAR). Los primeros usan 1 byte de almacenamiento por carácter y soporta sólo un idioma (basado en las propiedades de intercalación) además del inglés. Los últimos usan 2 bytes de almacenamiento por carácter (a menos que este comprimido) y soportan múltiples idiomas. Si un par sustituto es necesario, un carácter requerirá 4 bytes de almacenamiento. Así que si los datos están en varios idiomas y necesita representar solo un solo idioma, además del inglés, en sus datos, puede beneficiarse del uso de tipos de caracteres regulares, con los requisitos de almacenamiento más bajos. Cuando los datos son internacionales, o su aplicación de forma nativa trabaja con datos Unicode, debe utilizar tipos de datos Unicode para que no pierda la información. Los requerimientos de almacenamiento más grandes de Datos Unicode son mitigados a partir de SQL Server 2008 R2 con la compresión Unicode.

Cuando utiliza tipos que pueden tener una longitud asociada, tales como CHAR y VARCHAR, T-SQL soporta omitir la longitud y entonces utilizar una longitud por defecto. Sin embargo, en diferentes contextos, los valores por defecto pueden ser diferentes. Es considerada una buena práctica siempre ser explícito acerca de la longitud, como en CHAR(1) o VARCHAR(30).

Cuando se definen atributos que representan la misma cosa a través de diferentes tablas, especialmente las que serán usadas después como columnas join (como la llave primaria en una tabla y la llave foránea en otra); es muy importante ser consistente con los tipos. De lo contrario, cuando compare un atributo con otro, SQL Server tiene que aplicar la conversión implícita de un tipo de atributo al otro, y esto podría tener implicaciones de rendimiento negativos, como evitar el uso eficiente de los índices.

También querrá asegurarse de que cuando se indica un literal de un tipo, utiliza la forma correcta. Por ejemplo, los literales de cadenas de carácter regulares son delimitados con comillas simples, como en 'abc', mientras que los literales de cadenas de caracteres Unicode son delimitadas con una N mayúscula y luego con comillas simples, como en N'abc'. Cuando una expresión involucra elementos de diferentes tipos, SQL Server necesita aplicar la conversión implícita cuando sea posible, y esto puede resultar en una penalidad del rendimiento. Note que en algunos casos la interpretación de un literal no puede ser lo que piensa intuitivamente. Con el fin de forzar un literal a ser de un cierto tipo, puede que necesite aplicar una conversión explícita con funciones como CAST, CONVERT, PARSE, o TRY_CAST, TRY_CONVERT y TRY_PARSE. A modo de ejemplo, el literal 1 es considerado un INT por SQL Server en cualquier contexto. Si necesita que el literal 1 sea considerado, por ejemplo, como un BIT, es necesario convertir el tipo del literal explícitamente, como en CAST(1 AS BIT). Del mismo modo, el literal 4000000000 es considerado NUMERIC y no BIGINT. Si necesita que el literal sea como en el último, utilice CAST(4000000000 AS BIGINT). La diferencia entre las funciones sin TRY y sus contrapartes con TRY es que aquellos sin TRY fallan si el valor no es convertible, mientras que aquellos con TRY retornan un NULL en tales casos. Por ejemplo, el código siguiente produce un error.

SELECT CAST('abc' AS INT);

Por el contrario, el código siguiente retorna un NULL.

SELECT TRY_CAST('abc' AS INT);

En cuanto a la diferencia entre CAST, CONVERT y PARSE; con CAST, se indica la expresión y el tipo de destino; con CONVERT, hay un tercer argumento que representa el estilo para la conversión, que es soportada por algunas conversiones, como entre cadenas de carácter y valores de fecha y tiempo. Por ejemplo, CONVERT(DATE, '1/2/2012', 101) convierte la cadena de caracteres literal a DATE usando el estilo 101 representando el estándar de Estados Unidos. Con PARSE, puede indicar la cultura utilizando cualquier cultura soportada por Microsoft .NET Framework. Por ejemplo, PARSE('1/2/2012' AS DATE USING ‘en-US’) analiza la entrada literal como DATE utilizando una cultura de inglés de Estados Unidos.

Cuando utiliza expresiones que implican operandos de diferentes tipos, SQL Server normalmente convierte el que tiene la precedencia menor de tipo de datos a la que tiene la mayor. Considerar la expresión 1+’1’ como ejemplo. Un operando es INT y el otro es VARCHAR. La INT precede a VARCHAR; por lo tanto, SQL Server implícitamente convierte el valor VARCHAR '1' al valor INT 1, y por lo tanto el resultado de la expresión es 2 y no la cadena '11'. Por supuesto, siempre puede controlarlo utilizando una conversión explícita.

Si todos los operandos de la expresión son del mismo tipo, este también será el tipo del resultado, y es posible que no quiera que sea el caso. Por ejemplo, el resultado de la expresión 5/2 en T-SQL es el valor INT de 2 y no el valor NUMERIC de 2.5, debido a que ambos operandos son números enteros, por lo tanto el resultado es un número entero. Si estaba trabajando con dos columnas enteras, como col1 / col2, y quería que la división sea NUMERIC, podría necesitar convertir las columnas explícitamente, como en CAST(col1 AS NUMERIC(12, 2)) / CAST(col2 AS NUMERIC(12, 2)).

Escogiendo un Tipo de Datos para Llaves

Cuando define llaves inteligentes en sus tablas, llámese llaves basadas en atributos ya existentes derivadas de la aplicación, no hay preguntas acerca de los tipos debido a que ya las escogió para sus atributos. Pero cuando necesita crear llaves sustitutas, que han sido agregadas con el único fin de ser utilizadas como llaves, necesita determinar un tipo apropiado para el atributo, además de un mecanismo para generar los valores de llave. La realidad es que escuchará muchas opiniones diferentes sobre cuál es la mejor solución, algunas basadas ​​en la teoría, y algunas respaldadas por evidencia empírica. Pero los diferentes sistemas y las diferentes cargas de trabajo podrían terminar con diferentes soluciones óptimas. Es más, en algunos sistemas, el rendimiento de escritura puede ser la prioridad, mientras que en otros, puede ser el rendimiento de lectura. Una solución puede hacer las inserciones más rápidas, pero las lecturas más lentas, y otra solución podría funcionar al revés. Al final del día, para tomar decisiones inteligentes, es importante aprender de la teoría, aprender acerca de las experiencias de otros, pero eventualmente asegurarse de ejecutar benchmarks en el sistema de destino.

Note que esta post se refiere a elementos como objetos sequence, la propiedad de columna identity, y los índices.

Las opciones típicas que las personas usan para generar llaves sustitutas son:

·         La Propiedad de Columna Identity. Una propiedad que automáticamente genera las llaves en un atributo de tipo numérico con una escala de 0; es decir, cualquier tipo entero (TINYINT, SMALLINT, INT, BIGINT) o NUMERIC/DECIMAL con escala de 0.

·         El Objeto Sequence. Un objeto independiente en la base de datos de la cual puede obtener nuevos valores de secuencia. Como identity, soporta cualquier tipo numérico con una escala de 0. A diferencia de identity, no está atada a una columna en particular; en su lugar, como se ha mencionado, se trata de un objeto independiente en la base de datos. También puede solicitar un nuevo valor de un objeto sequence antes de usarlo.

·     GUIDs No Secuenciales. Puede generar identificadores únicos globales no secuenciales para ser almacenados en un atributo de un tipo UNIQUEIDENTIFIER. Puede utilizar la función NEWID de T-SQL para generar un nuevo GUID, posiblemente invocándola con una expresión predeterminada adjunta a la columna. También puede generar uno desde cualquier lugar, por ejemplo, el cliente, utilizando una interface de programación de aplicaciones (API) que genera un nuevo GUID. Las GUIDs están garantizadas que serán únicas a través del espacio y del tiempo.

·         GUIDs Secuenciales. Puede generar GUIDs secuenciales en la máquina utilizando la función NEWSEQUENTIALID de T-SQL.

·         Soluciones Personalizadas. Si no desea utilizar las herramientas integradas que proporciona SQL Server para generar llaves, necesita desarrollar su propia solución personalizada. El tipo de datos para la llave entonces depende de su solución.

Una cosa a considerar con respecto a la elección del generador de llave sustituta y el tipo de datos implicado, es el tamaño del tipo de datos. Cuanto más grande sea el tipo, mayor almacenamiento es requerido, y por lo tanto hace más lenta la lectura. Una solución usando un tipo de datos INT requiere 4 bytes por valor, BIGINT requiere 8 bytes, UNIQUEIDENTIFIER requiere 16 bytes, y así sucesivamente. Los requerimientos de almacenamiento para su llave sustituta pueden tener un efecto en cascada si su índice clustered es definido sobre las mismas columnas llave (el valor predeterminado para una restricción de llave primaria). Las columnas llave de índice clustered son utilizadas por todos los índices nonclustered internamente, como los medios para localizar filas en la tabla. Así que si se define un índice clustered en una columna x, e índices nonclustered, uno en la columna a, uno en b, y uno en la c; sus índices nonclustered son internamente creados en la columna (a, x), (b, x), y (c, x), respectivamente. En otras palabras, el efecto es multiplicativo.

En cuanto al uso de llaves secuenciales (como ocurre con identity, sequence y NEWSEQUENTIALID) versus los no secuenciales (como con NEWID o un generador de llave aleatoria personalizada), tiene varios aspectos a considerar.

Empezando con llaves secuenciales, todas las filas van al extremo derecho del índice. Cuando una página está completa, SQL Server asigna una nueva página y la llena. Esto resulta en una menor fragmentación en el índice, el cual es beneficioso para el rendimiento de lectura. Además, las inserciones pueden ser más rápidas cuando una sola sesión está cargando los datos, y los datos residen en una sola unidad o un pequeño número de unidades. Sin embargo, con los subsistemas de almacenamiento de gama alta que tienen muchos discos, la situación puede ser diferente. Cuando carga los datos de varias sesiones, terminará con una contención latch de página (latches son objetos utilizados para sincronizar accesos a las páginas de la base de datos) sobre las páginas más a la derecha de la lista enlazada a nivel de hoja del índice. Este cuello de botella impide usar todo el rendimiento del subsistema de almacenamiento.

Note que si decide usar las llaves secuenciales, y está usando las numéricas, puede siempre comenzar con el valor más bajo en el tipo para utilizar el rango completo. Por ejemplo, en lugar de empezar con 1 en un tipo INT, puede comenzar con -147483648.

Considerar llaves no secuenciales, como las aleatorias generadas con NEWID o con una solución personalizada. Cuando trata de forzar una fila en una página ya llena, SQL Server realiza una división de página clásica, que asigna una nueva página y mueve la mitad de las filas de la página original a la nueva. Dividir una página tiene un costo, además de que da lugar a la fragmentación del índice. La fragmentación del índice puede tener un impacto negativo en el rendimiento de las lecturas. Sin embargo, en términos de rendimiento de inserción, si el subsistema de almacenamiento contiene muchos discos y va a cargar los datos de varias sesiones, el orden aleatorio en realidad puede ser mejor que el secuencial a pesar de las divisiones. Eso es porque no hay un punto caliente en el extremo derecho del índice, y se utiliza el almacenamiento del subsistema disponible de mejor rendimiento.

Note que la división y la fragmentación del índice pueden ser mitigadas por reconstruir el índice periódicamente como parte de las actividades de mantenimiento habituales, suponiendo que tiene una ventana disponible para esto.

Si por las razones antes mencionadas decida confiar en llaves generadas de forma aleatoria, aun necesitará decidir entre GUIDs y una solución de generador de llave aleatoria personalizada. Como ya se ha mencionado, los GUIDs son almacenados en un tipo UNIQUEIDENTIFIER que es de 16 bytes de tamaño; eso es grande. Pero uno de los principales beneficios de los GUIDs es el hecho de que pueden ser generados en cualquier lugar y no generan conflictos a través del tiempo y del espacio. Puede generar GUIDs no sólo en SQL Server utilizando la función NEWID, sino en cualquier lugar, usando los APIs. De lo contrario, podría quedarse con una solución personalizada que genera llaves aleatorias más pequeñas. La solución puede incluso ser una combinación de una herramienta integrada y de algunos ajustes encima.

Para concluir acerca de las llaves y tipos de llaves, recuerde que hay múltiples opciones. La más pequeña es generalmente la mejor, pero luego está la cuestión del hardware que utiliza y dónde están sus prioridades de rendimiento. Asimismo, recuerde que si bien es muy importante hacer conjeturas, también es importante un benchmark de las soluciones en el entorno de destino.

Funciones Date y Time

T-SQL soporta un número de funciones date y time que le permiten manipular sus datos de fecha y de hora. El soporte para las funciones date y time sigue mejorando, con las dos últimas de versiones de SQL Server que agregan un número de nuevas funciones.

Esta sección cubre algunas de las funciones importantes soportadas por T-SQL y proporciona algunos ejemplos.

Fecha y Hora actual

Una categoría importante de las funciones es la categoría que retorna la fecha y la hora actual. Las funciones en esta categoría son GETDATE, CURRENT_TIMESTAMP, GETUTCDATE, SYSDATETIME, SYSUTCDATETIME y SYSDATETIMEOFFSET.

GETDATE es específica de T-SQL, retornando la fecha y la hora actual en la instancia de SQL Server al que está conectado, como un tipo de datos DATETIME. CURRENT_TIMESTAMP es lo mismo, sólo que es estándar, y por lo tanto la recomendada para usar. SYSDATETIME y SYSDATETIMEOFFSET son similares, sólo que retornan los valores como los tipos más precisos DATETIME2 y DATETIMEOFFSET (incluyendo offset), respectivamente. Note que no hay funciones integradas para retornar la fecha actual o la hora actual; para obtener dicha información, simplemente convierta la función SYSDATETIME a DATE o TIME, respectivamente. Por ejemplo, para obtener la fecha actual, use CAST(SYSDATETIME() AS DATE). La función GETUTCDATE retorna la fecha y la hora actual en términos UTC como un tipo DATETIME, y SYSUTCDATE hace lo mismo, sólo retornando el resultado como el tipo DATETIME2 más preciso.

Partes de Fecha y de Hora

Esta sección cubre las funciones de fecha y de hora que o bien extraen una parte de un valor de fecha y de hora (como DATEPART) o construyen un valor de fecha y de hora de las partes (como DATEFROMPARTS).

Usando la función DATEPART, puede extraer de un valor de fecha y de hora de entrada  una parte deseada, tal como un año, los minutos, o nanosegundos, y retornar las partes extraídas como un entero. Por ejemplo, la expresión DATEPART(month, '20120212') retorna 2. T-SQL proporciona las funciones YEAR, MONTH y DAY como abreviaciones a DATEPART, y que no requiere especificarla. La función DATENAME es similar a DATEPART, sólo retorna el nombre de la parte como una cadena de caracteres, en contraposición a un valor entero. Note que la función es dependiente del idioma. Es decir, que si el idioma efectivo en su sesión es us_english, la expresión DATENAME(month, '20120212') retorna 'February', pero para el italiano, devuelve 'febbraio'.

T-SQL proporciona un conjunto de funciones que construyen un valor de fecha y de hora deseado de sus partes numéricas. Tiene una función para cada uno de los seis tipos de fecha y de hora disponibles: DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, TIMEFROMPARTS y SMALLDATETIMEFROMPARTS. Por ejemplo, para construir un valor DATE de sus partes, podría usar una expresión así como DATEFROMPARTS(2012, 02, 12).

Por último, la función EOMONTH calcula la respectiva fecha de fin de mes para el valor de fecha y de hora de entrada. Por ejemplo, suponga que hoy es 12 de febrero del 2012. La expresión EOMONTH(SYSDATETIME()) debería entonces retornar la fecha '2012-02-29'. Esta función soporta una segunda entrada opcional indicando cuantos meses agregar al resultado.

Add y Diff

T-SQL soporta las funciones de adición y de diferencia de fecha y de hora llamado DATEADD y DATEDIFF.

DATEADD es una función muy comúnmente usada. Con la que puede agregar un número solicitado de unidades de una parte especificada a un valor de fecha y de hora especificado. Por ejemplo, la expresión DATEADD(year, 1, '20120212 ') agrega un año a la fecha de entrada 12 de febrero del 2012.

DATEDIFF es otra función comúnmente usada; retorna la diferencia en términos de una parte solicitada entre dos valores de fecha y de hora. Por ejemplo, la expresión DATEDIFF(day, '20110212', '20120212') calcula la diferencia en días entre el 12 de febrero del 2011 y el 12 de febrero del 2012, retornando el valor 365. Note que esta función mira sólo las partes solicitadas y por arriba en la jerarquía de fecha y de hora, no por debajo. Por ejemplo, la expresión DATEDIFF(year, '20111231', '20120101') mira sólo la parte del año, y por lo tanto retorna 1. No mira las partes mes y día de los valores.

Offset

T-SQL soporta dos funciones relativas a valores de fecha y de hora con un offset: SWITCHOFFSET y TODATETIMEOFFSET.

Con la función SWITCHOFFSET, puede retornar un valor DATETIMEOFFSET de entrada en un término offset solicitado. Por ejemplo, considere la expresión SWITCHOFFSET(SYSDATETIMEOFFSET(), ‘-08:00’). Independientemente del offset de la instancia a la que está conectado, solicita presentar el valor de fecha y de hora actual en términos del offset ‘-08:00’. Si el offset del sistema es, por ejemplo, '-05:00', la función compensará ésta por restar tres horas del valor de entrada.

La función TODATETIMEOFFSET es utilizada para un propósito diferente. Se utiliza para construir un valor DATETIMEOFFSET de dos entradas: el primero es un valor de fecha y de hora que no considera offset, y el segundo es el offset. Puede utilizar esta función cuando migra de datos que no consideran offset, donde se mantiene el valor de fecha y de hora local, en un atributo, y el offset en otro, para datos que consideran offset. Digamos que tiene la fecha y la hora local, en un atributo llamado dt, y el offset en un atributo llamado theoffset. Agrega un atributo llamado dto de un tipo DATETIMEOFFSET a la tabla. Entonces, actualiza el nuevo atributo a la expresión TODATETIMEOFFSET(dt, theoffset), y luego elimina los atributos originales dt y theoffset de la tabla.

El código siguiente demuestra cómo utilizar ambas funciones.

SELECT

  SWITCHOFFSET('20130212 14:00:00.0000000 -08:00', '-05:00') AS [SWITCHOFFSET],

  TODATETIMEOFFSET('20130212 14:00:00.0000000', '-08:00') AS [TODATETIMEOFFSET];

Aquí está el resultado de este código.

SWITCHOFFSET                  TODATETIMEOFFSET

---------------------------------- ----------------------------------

2013-02-12 17:00:00.0000000 -05:00 2013-02-12 14:00:00.0000000 -08:00

Funciones Carácter

T-SQL no fue realmente diseñada para soportar funciones de manipulación de cadenas de caracteres muy sofisticadas, por lo que no encontrará un conjunto muy grande de tales funciones. Esta sección describe las funciones de cadenas de caracteres que T-SQL soporta, organizadas en categorías.

Concatenación

La concatenación de cadenas de caracteres es una necesidad muy común. T-SQL soporta dos formas de concatenación de cadenas, una con el operador de suma (+), y otro con la función CONCAT.

Aquí se muestra un ejemplo de concatenación de cadenas en una consulta utilizando el operador +.

SELECT empid, country, region, city,

country + N',' + region + N',' + city AS localizacion

FROM HR.Employees;

Aquí está el resultado de esta consulta.

empid  country  region  city      localizacion

------ -------- ------- --------- ----------------

1      USA       WA      Seattle   USA,WA,Seattle

2      USA       WA      Tacoma    USA,WA,Tacoma

3      USA       WA      Kirkland  USA,WA,Kirkland

4      USA       WA      Redmond   USA,WA,Redmond

5      UK        NULL    London    NULL

6      UK        NULL    London    NULL

7      UK        NULL    London    NULL

8      USA       WA      Seattle   USA,WA,Seattle

9      UK        NULL    London    NULL

Observe que cuando cualquiera de las entradas es NULL, el operador + retorna un NULL. Es un comportamiento estándar que puede ser cambiado por desactivar una opción de sesión llamada CONCAT_NULL_YIELDS_NULL, aunque no es recomendable confiar en un comportamiento no estándar. Si quiere sustituir un NULL con una cadena vacía, hay un número de formas para hacer esto programáticamente. Una opción es usar COALESCE(<expresión>,' '). Por ejemplo, en estos datos, solo la región puede ser NULL, así que puede utilizar la siguiente consulta para reemplazar una coma más la región con una cadena vacía cuando la región es NULL.

SELECT empid, country, region, city,

country + COALESCE( N',' + region, N'') + N',' + city AS localizacion

FROM HR.Employees;

Otra opción es utilizar la función CONCAT que, a diferencia del operador +, sustituye una entrada NULL con una cadena vacía. Así es como se ve la consulta.

SELECT empid, country, region, city,

CONCAT(country, N',' + region, N',' + city) AS localizacion

FROM HR.Employees;

Aquí está la salida de esta consulta.

empid  country  region  city      localizacion

------ -------- ------- --------- ----------------

1       USA      WA      Seattle   USA,WA,Seattle

2       USA      WA      Tacoma    USA,WA,Tacoma

3       USA      WA      Kirkland  USA,WA,Kirkland

4       USA      WA      Redmond   USA,WA,Redmond

5       UK       NULL    London    UK,London

6       UK       NULL    London    UK,London

7       UK       NULL    London    UK,London

8       USA      WA      Seattle   USA,WA,Seattle

9       UK       NULL    London    UK,London

Observe que esta vez, cuando la región fue NULL, fue sustituido por una cadena vacía.

Extracción y Posición de Subcadenas

Esta sección cubre las funciones que puede utilizar para extraer una subcadena de una cadena, e identificar la posición de una subcadena dentro de una cadena.

Con la función SUBSTRING, puede extraer una subcadena de una cadena dada como el primer argumento, partiendo de la posición dada como el segundo argumento, y una longitud dada como el tercer argumento. Por ejemplo, la expresión SUBSTRING('abcde', 1, 3) retorna 'abc'. Si el tercer argumento es mayor que el que podría obtener al final de la cadena, la función no falla; en su lugar, simplemente extrae la subcadena hasta el final de la cadena.

Las funciones LEFT Y RIGHT extraen un número de caracteres solicitados desde la izquierda, y del final derecho de la cadena de entrada, respectivamente. Por ejemplo, LEFT('abcde', 3) retorna 'abc' y RIGHT('abcde', 3) retorna 'cde'.

La función CHARINDEX retorna la posición de la primera ocurrencia de la cadena proporcionada como el primer argumento en la cadena proporcionada como el segundo argumento. Por ejemplo, la expresión CHARINDEX(' ', 'Pedro Rondon') busca la primera ocurrencia de un espacio en la segunda de entrada, retornando 6 en este ejemplo. Note que puede proporcionar un tercer argumento indicando a la función dónde empezar la búsqueda.

Puede combinar, o anidar, funciones en la misma expresión. Por ejemplo, suponga que consulta una tabla con un atributo llamado nombrecompleto formateado como '<first> <last>', y necesita escribir una expresión que extrae la primera parte del nombre. Puede utilizar la siguiente expresión.

LEFT(nombrecompleto, CHARINDEX(' ', nombrecompleto) - 1)

T-SQL también soporta una función llamada PATINDEX que, como en CHARINDEX, puede utilizarla para localizar la primera posición de una cadena dentro de otra cadena. Pero mientras que con CHARINDEX está buscando una cadena constante, con PATINDEX está buscando un patrón. El patrón es formado muy similar al patrón LIKE con el que probablemente estará familiarizado, en el que utiliza comodines como % para cualquier cadena, _ para un solo carácter, y entre corchetes ([]), representando un carácter individual de una lista determinada o rango. Como un ejemplo, la expresión PATINDEX('%[0-9]%', 'abcd123efgh') mira la primera ocurrencia de un dígito (un carácter en el rango de 0-9) en la segunda entrada, retornando la posición 5 en este caso.

Longitud de Cadena

T-SQL proporciona dos funciones que puede utilizar para medir la longitud de un valor de entrada, LEN y DATALENGTH.

La función LEN retorna la longitud de una cadena de entrada en términos del número de caracteres. Note que retorna el número de caracteres, no bytes, si la entrada es una cadena de caracteres regular o de caracteres Unicode. Por ejemplo, la expresión LEN(N'xyz') retorna 3. Si hay espacios finales, LEN los elimina.

La función DATALENGTH retorna la longitud de la entrada en términos del número de bytes. Esto significa, por ejemplo, que si la entrada es una cadena de caracteres Unicode, contará 2 bytes por carácter. Por ejemplo, la expresión DATALENGTH(N'xyz') retorna 6. Note también que, a diferencia de LEN, la función DATALENGTH no quita los espacios finales.

Alteración de Cadenas

T-SQL soporta un número de funciones que puede utilizar para aplicar cambios a una cadena de entrada. Estos son REPLACE, REPLICATE y STUFF.

Con la función REPLACE, puede reemplazar en una cadena de entrada proporcionada como el primer argumento, todas las ocurrencias de la cadena proporcionada como el segundo argumento, con la cadena proporcionada como el tercer argumento. Por ejemplo, la expresión REPLACE('.1.2.3.', '.', '/') sustituye todas las ocurrencias de un punto (.) con una barra (/), retornando la cadena '/1/2/3/'.

La función REPLICATE permite replicar una cadena de entrada un número solicitado de veces. Por ejemplo, la expresión REPLICATE ('0', 10) replica la cadena '0' diez veces, retornando '0000000000'.

La función STUFF opera sobre una cadena de entrada proporcionada como el primer argumento; entonces, a partir de la posición del carácter indicado como segundo argumento, elimina el número de caracteres indicado por el tercer argumento. Luego inserta en esa posición la cadena especificada como el cuarto argumento. Por ejemplo, la expresión STUFF(',x,y,z', 1, 1, '') elimina el primer carácter de la cadena de entrada, retornando la cadena 'x,y,z'.

Formato de Cadenas

Esta sección cubre las funciones que puede utilizar para aplicar las opciones de formato a una cadena de entrada. Estas son las funciones UPPER, LOWER, LTRIM, RTRIM y FORMAT.

Las cuatro primeras funciones son fáciles de entender (mayúsculas de la entrada, minúsculas de la entrada, entrada después de eliminar espacios iniciales, y entrada después de eliminar espacios finales). Note que no hay alguna función TRIM que elimine tanto los espacios iniciales y los finales; para lograr esto, necesita anidar una llamada a una función dentro de otra, como en RTRIM(LTRIM(<input>)).

Con la función FORMAT, puede formatear un valor de entrada basado en una cadena de formato, y opcionalmente especificar la cultura como una tercera entrada, cuando sea relevante. Puede utilizar cualquier cadena de formato soportado por el .NET Framework. Como un ejemplo, la expresión FORMAT(1759, '000000000') formatea el número de entrada como una cadena de caracteres con un tamaño fijo de 10 caracteres con ceros a la izquierda, retornando '0000001759'.

Expresión CASE y Funciones Relacionadas

T-SQL soporta una expresión llamada CASE y un número de funciones relacionadas que puede utilizar para aplicar lógica condicional para determinar el valor retornado. Mucha gente se refiere incorrectamente a CASE como un sentencia. Una sentencia realiza algún tipo de acción o controla el flujo del código, y eso no es lo que CASE hace; CASE retorna un valor, y por lo tanto es una expresión.

La expresión CASE tiene dos formas, la forma simple y la forma buscada. He aquí un ejemplo de la forma CASE simple, emitida sobre la base de datos de muestra TSQL2012.

SELECT productid, productname, unitprice, discontinued,

 CASE discontinued

  WHEN 0 THEN 'No'

  WHEN 1 THEN 'Si'

  ELSE 'Desconocido'

 END AS discontinued_desc

FROM Production.Products;

La forma simple compara una expresión de entrada (en este caso el atributo discontinued) a múltiples expresiones when de escalares posibles (en este caso 0 y 1)​​, y retornan la expresión de resultado (en este caso, 'No' y 'Si', respectivamente) asociado con la primera coincidencia. Si no hay coincidencias y una cláusula ELSE es especificada, la expresión else (en este caso, 'Desconocido') es retornada. Si no hay cláusula ELSE, el valor predeterminado es ELSE NULL. Aquí se muestra una forma abreviada de la salida de esta consulta.

productid  productname    unitprice  discontinued discontinued_desc

---------- -------------- ---------- ------------ -----------------

1           Product HHYDP  18.00 0            No

2           Product RECZE  19.00 0            No

3           Product IMEHJ  10.00 0            No

4           Product KSBRM  22.00 0            No

5           Product EPEIM  21.35 1            Si

...

La forma buscada de la expresión CASE es más flexible. En lugar de comparar una expresión de entrada a múltiples expresiones posibles, utiliza predicados en las cláusulas WHEN, y el primer predicado que se evalúa a Verdadero determina cual expresión when es retornada. Si ninguno es Verdadero, la expresión CASE retorna la expresión else. La siguiente consulta es un ejemplo.

SELECT productid, productname, unitprice,

 CASE

  WHEN unitprice < 20.00 THEN 'Bajo'

  WHEN unitprice < 40.00 THEN 'Medio'

  WHEN unitprice >= 40.00 THEN 'Alto'

  ELSE 'Desconocido'

 END AS pricerange

FROM Production.Products;

En este ejemplo, la expresión CASE retorna una descripción del rango del precio unitario del producto. Cuando el precio unitario es inferior a $20.00, retorna ‘Bajo’, cuando es $20.00 o más y por debajo de $40.00, retorna ‘Medio’, y cuando es $40.00 o más, retorna 'Alto'. Hay una cláusula ELSE por seguridad; si la entrada es NULL, la expresión else retornada es "Desconocido". Note que el segundo predicado when no necesita comprobar si el valor es $20.00 o más, explícitamente. Eso es porque el predicado when es evaluado en orden y el primer predicado when no se evalua a Verdadero. He aquí una forma abreviada de la salida de esta consulta.

productid  productname    unitprice   pricerange

---------- -------------- ---------- ----------

1           Product HHYDP  18.00 Bajo

2           Product RECZE  19.00 Bajo

3           Product IMEHJ  10.00 Bajo

4           Product KSBRM  22.00 Medio

5           Product EPEIM  21.35 Medio

...

T-SQL soporta un número de funciones que pueden ser consideradas como abreviaciones de la expresión CASE. Estas son las funciones estándar COALESCE y NULLIF y las no estándar ISNULL, IIF y CHOOSE.

La función COALESCE acepta una lista de expresiones como entrada y retorna la primera que no es NULL, o NULL si todas son NULLs. Por ejemplo, la expresión COALESCE(NULL, 'x', 'y') retorna 'x'. Más generalmente, la expresión:

COALESCE(<exp1>, <exp2>, …, <expn>)

Es similar a la siguiente.

CASE

  WHEN <exp1> IS NOT NULL THEN <exp1>

  WHEN <exp2> IS NOT NULL THEN <exp2>

  …

  WHEN <expn> IS NOT NULL THEN <expn>

  ELSE NULL

END

Un uso típico de COALESCE es sustituir un NULL con otra cosa. Por ejemplo, la expresión COALESCE (region,'') retorna region si no es NULL y retorna una cadena vacía si es NULL.

T-SQL soporta una función no estándar llamada ISNULL que es similar a la estándar COALESCE, pero es un poco más limitada en el sentido de que sólo soporta dos entradas. Como COALESCE, retorna la primera entrada que es no NULL. Así, en lugar de COALESCE(region,''), podría usar ISNULL(región,''). Generalmente, se recomienda atenerse a las características estándar a menos que exista alguna ventaja en flexibilidad o en rendimiento en la característica no estándar que sea de mayor prioridad. ISNULL es en realidad más limitada que COALESCE, por lo general, se recomienda adherirse a COALESCE.

Hay un par de diferencias sutiles entre COALESCE e ISNULL que puede ser interesante. Una diferencia está, en cual entrada determina el tipo de salida. Considere el siguiente código.

DECLARE

 @x AS VARCHAR(3) = NULL,

 @y AS VARCHAR(10) = '1234567890';

SELECT COALESCE(@x, @y) AS [COALESCE], ISNULL(@x, @y) AS [ISNULL];

Aquí está el resultado de este código.

COALESCE   ISNULL

---------- ------

1234567890 123

Observe que el tipo de la expresión COALESCE es determinada por el elemento retornado, mientras que el tipo de la expresión ISNULL es determinada por la primera entrada.

La otra diferencia entre COALESCE e ISNULL es cuando se utiliza SELECT INTO. Supongamos que la lista SELECT de una sentencia SELECT INTO contiene las expresiones COALESCE(col1, 0) AS newcol1 versus ISNULL(col1, 0) AS newcol1. Si el atributo de origen col1 es definido como NOT NULL, ambas expresiones producirán un atributo en la tabla de resultado definido como NOT NULL. Sin embargo, si el atributo de origen col1 es definido para permitir NULLs, COALESCE creará un atributo de resultado permitiendo NULLs, mientras que ISNULL creará uno que no permite NULLs.

Tema Clave

COALESCE e ISNULL pueden afectar al rendimiento cuando está combinando conjuntos; por ejemplo, con joins o cuando está filtrando datos. Considere un ejemplo donde tiene dos tablas T1 y T2 y necesita unirlas basadas en una coincidencia entre T1.col1 y T2.col1. Los atributos permiten NULLs. Normalmente, una comparación entre dos NULLs nos da desconocido, y esto causa que la fila sea descartada. Quiere tratar dos NULLs como iguales. Lo que se hace en tal caso es usar COALESCE o ISNULL para sustituir un NULL con un valor que sabe que no puede aparecer en los datos. Por ejemplo, si los atributos son enteros, y sabe que tiene sólo enteros positivos en sus datos (puede incluso tener restricciones que garanticen esto), puede tratar de utilizar el predicado COALESCE(T1.col1,-1) = COALESCE(T2.col1,-1), o ISNULL(T1.col1,-1) = ISNULL(T2.col1, -1). El problema con esta forma es que, debido a que aplica manipulación a los atributos que está comparando, SQL Server no confiará en el ordenamiento del índice. Esto puede resultar en no utilizar los índices disponibles eficientemente. En lugar de ello, se recomienda utilizar la forma más larga: T1.col1 = T2.col1 OR (T1.col1 IS NULL AND T2.col1 IS NULL), el cual SQL Server entiende como una comparación que considera NULLs como iguales. Con esta forma, SQL Server puede utilizar eficientemente los índices.

T-SQL también soporta la función estándar NULLIF. Esta función acepta dos expresiones de entrada, retorna NULL si son iguales, y retorna la primera entrada si no lo son. Por ejemplo, considere la expresión NULLIF(col1, col2). Si col1 es igual a col2, la función retorna un NULL; de lo contrario, retorna el valor col1.

En cuanto a IIF y CHOOSE, éstas son funciones T-SQL no estándar que fueron añadidas para simplificar las migraciones de plataformas de Microsoft Access. Debido a que estas funciones no son estándar y hay alternativas estándar sencillas con expresiones CASE, no es recomendable utilizarlas. Sin embargo, cuando está migrando de Access a SQL Server, estas funciones pueden ayudar a agilizar la migración, y luego poco a poco se puede reconstruir el código para utilizar las funciones estándar disponibles. Con la función IIF, puede retornar un valor si un predicado de entrada es verdadero y otro valor en caso contrario. La función tiene la siguiente forma.

IIF(<predicate>, <true_result>, <false_or_unknown_result>)

Esta expresión es equivalente a la siguiente.

CASE WHEN <predicate> THEN <true_result> ELSE <false_or_unknown_result> END

Por ejemplo, la expresión IIF(orderyear = 2012, qty, 0) retorna el valor en el atributo qty cuando el atributo orderyear es igual a 2012, y cero en caso contrario.

La función CHOOSE le permite proporcionar una posición y una lista de expresiones y retorna la expresión en la posición indicada. La función tiene la siguiente forma.

CHOOSE(<pos>, <exp1>, <exp2>, …, <expn>)

Por ejemplo, la expresión CHOOSE(2, 'x', 'y', 'z') retorna 'y'. De nuevo, es fácil reemplazar una expresión CHOOSE con una expresión CASE lógicamente equivalente; pero el punto en soportar CHOOSE, así como de IIF, es simplificar las migraciones de Access a SQL Server como un solución temporal.

Ejercicio 1: Aplicar Concatenación de Cadenas y Usar una Función Date y Time

En este ejercicio, practica la concatenación de cadenas y el uso de una función fecha y hora.

1.    Abrir el SSMS y conéctese a la base de datos de muestra TSQL2012.

2.    Escribir una consulta sobre la tabla HR.Employees que retorna el empid, el nombre completo del empleado (concatenando los atributos firstname, espacio, y lastname), y el año de nacimiento (aplicar una función al atributo birthdate). He aquí una posible consulta que logra esta tarea.

SELECT empid,

  firstname + N' ' + lastname AS nombrecompleto,

  YEAR(birthdate) AS annonacimiento

FROM HR.Employees;

Ejercicio 2: Usar Funciones Date y Time Adicionales

En este ejercicio, practica el uso de funciones de fecha y de hora adicionales.

Escribe una expresión que calcula la fecha del último día del mes en curso. También escribe una expresión que calcula el último día del año en curso. Por supuesto, hay un número de maneras de lograr tales tareas. He aquí una manera de calcular el fin del mes en curso.

SELECT EOMONTH(SYSDATETIME()) AS fin_de_mes_actual;

Y aquí está una manera de calcular el fin del año en curso.

SELECT DATEFROMPARTS(YEAR(SYSDATETIME()), 12, 31) AS ultimo_dia_de_anno_actual;

Usando la función YEAR, extrae el año actual. Luego, proporciona el año en curso junto con el mes 12 y el día 31 a la función DATEFROMPARTS para construir el último día del año en curso.

Ejercicio 3: Usar Funciones de Cadena y de Conversión

En este ejercicio, practica el uso de las funciones de cadena y de conversión.

1.    Escriba una consulta sobre la tabla Production.Products que retorna el productid numérico existente, además el productid formateado como una cadena de tamaño fijo con 10 dígitos con ceros a la izquierda. Por ejemplo, para productid de 42, necesita retornar la cadena '0000000042'. Una manera de enfrentar esta necesidad es usando el siguiente código.

SELECT productid,

  RIGHT(REPLICATE('0', 10) + CAST(productid AS VARCHAR(10)), 10) AS str_productid

FROM Production.Products;

2.    Usando la función REPLICATE, genera una cadena hecha de 10 ceros. Entonces, concatena el formato carácter del productid. Luego extrae los 10 caracteres más a la derecha de la cadena de resultado.

¿Puede pensar en una manera más sencilla de lograr la misma tarea utilizando las nuevas funciones que fueron introducidas en SQL Server 2012? Una manera mucho más simple para lograr lo mismo es utilizando la función FORMAT, como en la siguiente consulta.

SELECT productid,

  FORMAT(productid, 'd10') AS str_productid

FROM Production.Products;