Script Inside

Entradas

Mostrando las entradas etiquetadas como queries

SQL Server, columnas calculadas

febrero 12, 2020

Aquí os dejo algunos apuntes básicos sobre las columnas calculadas en SQL Server. ¿Cuando se calculan? Depende de cómo defina la columna calculada. Una columna calculada PERSISTIDA se calculará y luego se almacenará como datos dentro de la tabla al ser creada. Si no define la columna como PERSISTIDA, se calculará cada vez que se ejecute una consulta que la incluya. Mas info aquí . Tablas temporales o versionadas La modificación del esquema de una tabla versionada por el sistema (tablas temporales) falla porque no se admite agregar una columna calculada mientras el versionado del sistema está activado. Para solucionarlo: Primero desactivar el versionado Luego agregar la columna calculada a la tabla base Verificar el tipo de la columna calculada resultante Agregar la columna con el tipo estático apropiado a la tabla de historial (tabla temporal o tabla versionada) Volver a activar el versionado Otra cosa a tener en cuenta es que una columna calculada que utiliza una...

Alta disponibilidad con un espejo en SQL Server

noviembre 18, 2015

SQL Server tiene varias formas para que dispongamos de alta disponibilidad en nuestras bases de datos, en este post veremos una de ellas: la creación de un espejo. Para mi la creación de un espejo vs un clúster es una preferencia personal, creo que un clúster es más complejo de mantener y gestionar, es más caro porque ambas máquinas comparten el almacenamiento y por lo menos mi experiencia con SQL Server 2008 R2 no fue muy buena y sin un soporte premiun por parte de Microsoft los fallos son difícilmente corregibles. En cambio un mirror o espejo es sencillo de configurar y pueden ser dos máquinas totalmente independientes. ¿Por qué crear un espejo en SQL Server? Porque este método permite hacer una réplica exacta de una base de datos de un servidor a otro. La gran ventaja de este método es que permite el failover automático sin intervención humana (siempre que se instale un tercer servidor witness). La desventaja del mirror es que sólo podemos tener una máquina secundaria o mirro...

CentOS 7 reset root password MySQL 5.7

noviembre 06, 2015

Si habéis llegado aquí estáis en el lugar correcto dónde podréis restaurar la contraseña de root en MySQL 5.7 . Internet está plagado de tutoriales dónde te indican cómo hacer esto pero ninguno está actualizado a las últimas versiones de CentOS y MySQL, además por todos es conocido que el soporte a MySQL por parte de la comunidad Open Source ha menguado desde su compra por Oracle. ¿Que cambia en CentOS 7 y MySQL 5.7? CentOS 7 incorpora systemd (Sistema y administrador de servicios para Linux, compatible con scripts de inicio SysV) y su herramienta de administración systemctl . ¿en que afecta esto? Pues que el script mysql_safe no está disponible (no se instala) en estos sistemas y hay que utilizar otras alternativas y gestionar mysql server community desde systemd . La tabla de usuarios en MySQL 5.7 ya no tiene campo password ahora es authentication_string. Restaurar contraseña root en MySQL 5.7 y CentOS 7 systemctl set-environment MYSQLD_OPTS="--skip-grant-...

Consultas útiles SQL Server

Muchas veces necesitamos conocer las tablas, campos, claves, etc. de una base de datos en concreto, todos estos datos están disponibles en tablas de sistema de SQL Server, veamos algunos ejemplos. Devolver las tablas de la base de datos actual con sus claves primarias SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' Devolver los campos de una tabla concreta que son claves foráneas y la tabla a la que hace referencia SELECT CU.COLUMN_NAME, PK.TABLE_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME WHERE FK.TABLE_NAME = ? Devo...

Trabajar con Excel en SQL Server

agosto 14, 2014

Muchas veces necesitamos obtener datos desde un Excel e importarlos en una tabla de nuestra base de datos o sencillamente utilizarlos con alguna consulta, si este es tu caso este tutorial te puede ayudar a trabajar con archivos Excel como si fueran una base de datos y cada una de sus hojas como una tabla. Lo primero que tienes que tener instado en el servidor donde está SQL Server (no vale en la máquina remota donde haces las consultas) es el Office o en su defecto 2007 Office System Driver Data Connectivity Components (solo 32 bits) o Microsoft Access Database Engine 2010 Redistributable (versiones de 32 y 64 bits). Es muy muy importante tener esto instalado para evitar el siguiente error The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered . Más importante aún es tener la versión correcta, si tu SQL Server es de 64 bits solo puedes instalar de 64 bits, si es de 32 bits lo mismo, en caso contrario tendrías el siguiente error OLE DB provider 'Micr...

Convertir UTF8 a LATIN1 en MySQL

agosto 13, 2014

Imaginemos que tenemos una base de datos con una tabla codificada en UTF8 y otra base de datos codificada en LATIN1, ambas tablas las queremos unir en una consulta y la latin1 es la principal ¿cómo sacar los datos codificados en UTF8 en formato legible con LATIN1? CONVERT(CAST(CONVERT(campo_tabla USING latin1) AS binary) USING utf8) Espero que les valga.

Optimizando consultas en MySQL

Hay un artículo muy bueno con 20 consejos para mejorar MySQL que recomiendo a todos leer antes de seguir con este post porque básicamente lo que haré será ampliar o rebatir alguno de los puntos del mismo. El primer punto que me gustaría modificar es el 5, indexar los campos para utilizarlos con consultas que utilicen JOIN. Básicamente lo que dice este punto es que los campos de unión entre tablas deben ser del mismo tipo y estar indexados, yo a eso añadiría que ambas tablas deben ser del tipo Innodb y ambos campos tener una relación entre ellos con una Foreign Key , así no solo garantizamos las premisas anteriores sino que se asegura la integridad de los datos en ambas tablas. Lo ideal para hacer uniones entre tablas es que uno de los campos sea una clave primaria y el otro una clave foránea que apunte a ella. En el punto 16, particionamiento vertical, no estoy del todo de acuerdo, partir una tabla por partirla y creer que esto mejorará el rendimiento porque tiene menos datos o men...

SQL Server vs MySQL

Esto no es una comparativa a nivel profundo ni lo pretende ser, mi único objetivo es remarcar desde el punto de vista de un usuario básico las ventajas de uno u otro sistema a la hora de trabajar con ellos. No a la hora de instalarlos, configurarlos ni mantenerlos, solo a la hora de pedir datos almacenados en estos gestores de bases de datos. Por mi trabajo tengo que manejar ambos sistemas a partes iguales y muchas veces extraño cosas de uno en el otro y viceversa, aquí os dejo una pequeña comparativa desde mi punto de vista y basada en mi experiencia profesional, si alguien descubre algún error o una mejor forma de hacer las cosas estaría encantado de recibir un feedback. Cosas que agradezco de MySQL Me encantan las sintaxis ON DUPLICATE KEY e INSERT IGNORE, me parecen muy útiles a la hora de ejecutar consultas y gestionar errores. El uso de los índices de texto completo me parecen mucho más sencillo e intuitivo sobre todo con la integración de estos en tablas InnoDB desde las...

Almacenar IPs en las bases de datos

La mayoría de los usuarios cuando tienen que almacenar IPs en sus bases de datos los hacen con tipos de campos VARCHAR cuando es mucho más óptimo almacenarlos con un entero sin signo (en caso de MySQL) o BIG INT (en caso de SQL Server). Convertir IPs en MySQL Existen 2 funciones para ello inet_aton e inet_ntoa , la primera convierte la IP en un número y la segunda es el proceso contrario. Convertir IPs en SQL Server En SQL Server no hay funciones predefinidas para ello pero las podemos crear muy fácilmente, en el siguiente enlace hay un par de ejemplos muy buenos para convertir IPs a BIG INT en SQL Server y viceversa . Esta transformación también la podemos hacer al insertar los datos en el lenguaje de programación, en el caso de PHP sería ip2long y long2ip .

Tips para campos datetime en SQL Server

octubre 30, 2013

Para mi es mucho más sencillo e intuitivo trabajar con campos datetime en MySQL pero SQL Server ofrece las mismas (sino alguna más) posibilidades de convertir estos campos en formatos concretos de hora y fecha. Para ello tenemos las funciones CONVERT y DATEPART . Aquí os dejo algunos ejemplos de su uso. Convertir un campo datetime a fecha en castellano (el año con 4 dígitos) SELECT CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 103); Convertir un campo datetime a fecha en castellano (el año con 2 dígitos) SELECT CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 3); Extraer la fecha de un campo datetime SELECT CONVERT(DATE, CURRENT_TIMESTAMP); Extraer el tiempo de un campo datetime con milisegundos SELECT CONVERT(TIME, CURRENT_TIMESTAMP); Extraer el tiempo de un campo datetime sin milisegundos SELECT CONVERT(VARCHAR(9), CURRENT_TIMESTAMP, 108); ¿Se os ocurre algún otro formato más?

SQL Joins (intersecciones entre tablas en SQL)

octubre 17, 2012

Mucho de lo que voy a hablar hoy es lo típico que se te olvida de cuando lo has estudiado, te quedas con lo más frecuente, lo más utilizado, pero SQL trae consigo multitud de herramientas, todas y cada una imprescindibles, para extraer datos con la intersección de 2 o más tablas. No confundir este post que trata sobre los JOIN (intersecciones) con la sintaxis UNION. Veamos de las más conocidas a las menos Inner join SELECT * FROM A INNER JOIN B ON A.KEY = B.KEY Esta sencilla consulta nos devolvería los datos que están presente solamente en ambas tablas y su notación sería la siguiente AnB donde n significa intersección. left outer join Los que trabajamos con SQL Server y que provenimos de trabajar con MySQL esta sintaxis nos parece extraña y tal vez excluyente porque left join hace lo mismo, y es verdad, es realmente lo mismo, pero MySQL al abreviarlo nos elimina la posibilidad de saber el por qué del OUTER al no incluirlo en sus sintaxis, mientras SQL Server es más pragmático ...

Introducción a las fechas en SQL Server

En este post quiero tratar de explicar cómo usar las fechas en SQL Server y que cosas debemos tomar en consideración pero antes voy a tratar de explicar que son para SQL Server, el cual, tiene básicamente dos tipos de datos donde se pueden almacenar las mismas: Datetime y SmallDateTime, en este cuadro veremos las diferencias entre estos dos tipos de datos. DateTime Valores de Fecha y Hora que están comprendidos entre 1/1/1763 y 31/12/9999. La hora se expresa con una exactitud de hasta 1/300 de segundo SamallDateTime Valores de Fecha y Hora que están comprendidos entre 1/1/1900 y 6/6/2079. El grado de precisión de la hora llega hasta el minuto ¿Cómo guarda internamente SQL Server las fechas ? El motor de SQL Server siempre guarda las fechas de una sola forma y no esta referida a ningún formato concreto como podría ser Americano, Español, Japonés, etc.; SQL Server guarda las fechas DateTime como enteros de 4 bytes (los primeros 4 bytes almacenan la fecha y los otros 4 la...

Claves únicas para valores nulos en MySQL

diciembre 07, 2011

Todo el que esté familiarizado con las bases de datos MySQL sabe que un campo que admita valores nulos no puede ser una clave única porque al insertar 2 veces el valor null esto daría error, sin embargo no es descabellado pensar que necesitemos un campo que admita valores nulos pero que en caso de no ser nulo sea único. Existen varias formas de hacer esto, una del lado del código y otra con las herramientas propias de la base de datos. La más compleja pero más eficiente es la segunda de estas opciones y es sobre la que trata este artículo. En esto post voy a hablar de la creación de 2 triggers (disparadores) para MySQL que validan que al insertar u actualizar una fila un determinado campo sea nulo o único. La sintaxis de los triggers en MySQL puede ser muy sencilla e intuitiva una vez la conoces pero como todos los lenguajes tiene su particularidad, lo importante es saber que no se pueden tener 2 trigger para un mismo evento en una tabla por tanto si queremos validar 2 campos de est...

SQL Server: acceso exclusivo a la base de datos

noviembre 11, 2011

Muchas de las operaciones que se realizan sobre las bases de datos de SQL Server requieren el uso exclusivo de esta base de datos por parte del usuario que está ejecutando el proceso o la tarea, una de ella puede ser la restauración de la misma desde un backup. Por ser un requerimiento común es muy normal recibir este mensaje de error: Exclusive access could not be obtained because the database is in use Corregirlo no suele ser muy sencillo, más bien todo lo contrario y depende de los accesos que tenga esa base de datos, aquí os dejaré algunos pasos para identificar e intentar garantizar ese acceso exclusivo . Lo primero que debemos hacer es terminar todas las conexiones existentes a la base de datos y ello lo haremos con la siguiente instrucción: USE database GO ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE database SET MULTI_USER GO También podemos ejecutar esta opción utilizando el procedimiento almacenado sp_dboption: Use master go sp_db...

SQL Server: reorganizar y volver a generar índices

noviembre 07, 2011

Según la propia Microsoft : El Motor de base de datos de SQL Server mantiene índices automáticamente cada vez que inserta, actualiza o elimina operaciones realizadas en los datos subyacentes. Con el tiempo, estas modificaciones pueden hacer que la información del índice se disperse por la base de datos (se fragmente). La fragmentación ocurre cuando los índices tienen páginas en las que la ordenación lógica, basada en el valor de clave, no coincide con la ordenación física dentro del archivo de datos. Los índices muy fragmentados pueden reducir el rendimiento de la consulta y ralentizar la respuesta de la aplicación. Se puede solucionar la fragmentación del índice reorganizándolo o volviéndolo a generar pero el primer paso es identificar los índices fragmentados, para ello podemos ejecutar la siguiente consulta SQL que nos sacará un listado de los índices, las tablas a las que pertenecen y el nivel de fragmentación de los mismos: SELECT a.object_id as table_id, OBJECT_NAME(a.object...

Rendimiento SQL Server: grado de paralelismo

octubre 17, 2011

El tema de los grados de paralelismo en SQL Server es bastante extenso y tiene una bibliografía bastante amplia sobre todo en inglés (como casi todo en informática), en este post intentaré dar una visión global de qué es y para qué sirven los grados de paralelismo en SQL Server y sobre todo cuando y cómo utilizarlos. En primer lugar y lo más importante es disipar cualquier idea que puedas tener acerca de esta opción ( Max Degree of Parallelism ) y su vinculación a la cantidad de procesadores que SQL Server puede utilizar cuando trata de procesar más de una conexión (o usuario) - no es así. Si SQL Server tiene acceso a cuatro procesadores ociosos y tiene cuatro procesos para ejecutar, utilizará los cuatro procesadores - independientemente del grado máximo de paralelismo. Entonces, ¿qué esta opción no? Max Degree of Parallelism establece el número máximo de procesadores que SQL Server puede utilizar para una consulta. Si SQL Server tiene que devolver una gran cantidad de datos...

Read Committed Snapshot Isolation (RCSI)

octubre 14, 2011

Con el lanzamiento de SQL Server 2005 se introdujo un nuevo nivel de aislamiento llamado Read Committed Snapshot ( aislamiento de instantáneas ). ¿Qué son los niveles de aislamiento? El nivel de aislamiento de una transacción (transaction isolation level) define el grado en que se aísla una transacción de las modificaciones de recursos o datos realizadas por otras transacciones . El nivel de aislamiento de una transacción es una característica de vital importancia en el desarrollo de aplicaciones de base de datos, ya que afecta a los tipos y duración de bloqueos que se producen en nuestra infraestructura de base de datos, lo cual, tiene efectos directos en el rendimiento y tiempo de respuesta de nuestras consultas y transacciones. Además, el nivel de aislamiento nos permite indicar cómo deseamos que se comporte nuestro motor de base de datos frente a situaciones (…) que pueden impactar consistentemente en la eficacia de nuestros desarrollos en entornos de base de datos con concu...

Optimizar las estadísticas en SQL Server

octubre 13, 2011

Las estadísticas en SQL Server se utilizan para que el servidor ante una petición ejecute un plan de ejecución adecuado y optimizado al máximo para la misma. Existen varias opciones en las propiedades de la base de datos para gestionar las estadísticas, veremos cada una de ellas para garantizar la mejor configuración de la base de datos. Para ver y editar la configuración de las estadísticas de una base de datos en SQL Server: Abrir el SQL Server Managment Studio Conectarse a la instancia donde está alojada nuestra base de datos. En el panel izquierdo clic derecho en la base de datos que queremos consultar y del menú desplegable elegir Propiedades. En la ventana de propiedades de la base de datos elegir a la izquierda la página de Opciones Aparecerán las configuraciones de las estadísticas en el apartado automático Crear estadísticas automáticamente (Auto Create Statistics) Se recomienda fuertemente activar Auto Create Statistics pues es una de las formas que utili...

Agregar un valor por defecto en SQL Server

octubre 11, 2011

Supongamos que queremos modificar una columna existente en una tabla que tenemos creada en nuestra base de datos SQL Server 2008 R2, queremos modificarla para hacerla NO NULL y agregarle un valor por defecto, la solución que me ha funcionado en un poco rara y compleja pues consta de 3 consultas, pero a fin de cuentas me ha funcionado. Como modificar una columna de una tabla y agregarle un valor por defecto (default) USE databaseName GO UPDATE tableName set columnName = defaultValue where columnName IS NULL; GO ALTER TABLE tableName ALTER COLUMN columnName dataType NOT NULL; GO ALTER TABLE tableName ADD CONSTRAINT constraintName DEFAULT defaultValue FOR columnName; GO Lo que hago para modificar una columna de una tabla y agregarle un valor por defecto son tres sencillos pasos: rellenar el campo de la tabla para los valores que son nulos con el valor por defecto modificar esta columna y decirle que va a ser no nula agregar la constante que se encarga de supervisar y de agreg...

Obtener las claves foráneas de una tabla en SQL Server

septiembre 26, 2011

La siguiente consulta SQL permite obtener el nombre de todos los campos de una tabla que hacen referencia a índices de tablas externas (lo que conocemos como foreign keys o claves foráneas). SELECT SYSCOLUMNS.name as field FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.ID = SYSCOLUMNS.ID WHERE SYSOBJECTS.name = '$vTable' and SYSCOLUMNS.xtype = '56' and SYSCOLUMNS.name <> '$vIndex’; En SQL Server el valor que corresponde a las claves foráneas en la tabla SYSCOLUMNS en el campo xtype es el 56. Pero este valor también corresponde a la clave primaria es por ello que en la consulta anterior agrego SYSCOLUMNS.name <> '$vIndex’ donde vIndex es el nombre del campo que es clave primaria. Adicionalmente podemos agregar en esta consulta que nos muestre o no los valores que pueden ser o no nulos, para ello solo debemos agregar en el WHERE la siguiente comprobación and SYSCOLUMNS.isnullable = ‘’ (1 para los que puedan ser nulos y 0 para los que no)....