DICCIONARIO DE DATOS

PROBLEMA: Como desarollador de BD, se requiere de la creación del diccionario de datos, he visto que muchos lo realizan en archivos de WORD o EXCEL, pero creo que es algo tedioso, ustedes como realizan el diccionario de datos?

SOLUCION: SQL SERVER tiene una propiedad para los objetos en la cual se puede almacenar la descripción de cada uno de ellos, veamos donde se encuentra:

Es aquí donde se debe agregar la propiedad MS_Description y su valor o la descripción. Sin embargo es algo complejo y laborioso; encontré otra forma que es utilizando el consejo del MVP Ken Simmons, un tip que el publicó en MSSQLTIPS, es utilizando un script creado por el mismo, veamoslo:

USE [AdventureWorks2014]
GO

--Script to add an Extended Property to the Table
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'Lookup table containing the departments within the Adventure Works Cycles company.' ,
@level0type=N'SCHEMA', 
@level0name=N'HumanResources', --Schema Name
@level1type=N'TABLE', 
@level1name=N'Department' --Table Name
GO

--Script to add an Extended Property to a column
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'Name of the department.' ,
@level0type=N'SCHEMA', 
@level0name=N'HumanResources', --Schema Name
@level1type=N'TABLE', 
@level1name=N'Department', --Table Name
@level2type=N'COLUMN', 
@level2name=N'Name'--Column Name
GO

Después de hacer esto para cada columna y tabla, es necesario ejecutar el script que el proporciona para obtener las descripciones capturadas en un formato HTML que el mismo creo.

Con el resultado en forma de texto, lo copiamos a un archivo y lo guardamos como HTML o HTM y el resultado es el siguiente:

Una opción más es la aplicación SQL DATADICTIONARY y en lo particular se me hace demasiado práctica y muy fácil de usar. Solo tenemos que conectarnos a nuestra BD y comenzar a capturar las descripciones por cada uno de los objetos.

Después damos click en EDIT y podemos movernos entre la descripción de la BD, tablas, columnas, procedimientos almacenados, vistas, funciones, índices, etc.

Con esto ya podemos generar nuestro diccionario de datos

Y este es el resultado:

Aun cuando es una versión demo, creo que genera un PDF muy aceptable, ya si quieren pueden comprar la licencia y puede generar en formato HTML como lo muestra la página.

Espero que les sirva para realizar sus diccionarios de datos.

SALUDOS!

Continuar...

BUSCANDO PRIMARY KEYS Y FOREIGN KEYS?

PROBLEMA: Uno de los principales retos al que me tuve enfrentar era conocer las relaciones que existían entre las tablas y por ende, las columnas que componían cada una de las llaves primarias de las tablas, como lograr esto?

SOLUCION: Existen varias formas de poder conocerlos, en lo particular me adapté más a esta forma que es utilizando los procedimientos de catalogo, les explico:

Como ejemplo utilizaré algunas tablas de la BD AdventureWorks2014, las cuales son Person.Person , Person.BusinessEntityContact, Person.ContactType

Los procedimientos almacenados que utilizaremos son los siguientes: sp_pkeys , sp_fkeys

El script es el siguiente:

USE AdventureWorks2014;  
GO  
--nombre de la tabla
DECLARE @varTableName VARCHAR(100) = 'Person'  
DECLARE @varTableOwner VARCHAR(100) = 'Person'

EXEC sp_pkeys @table_name =  @varTableName   ,  @table_owner = @varTableOwner

EXEC sp_fkeys @pktable_name = @varTableName  , @pktable_owner = @varTableOwner

EXEC sp_fkeys @fktable_name = @varTableName , @fktable_owner = @varTableOwner

USE AdventureWorks2014;  
GO  
--nombre de la tabla
DECLARE @varTableName VARCHAR(100) = 'BusinessEntityContact'  
DECLARE @varTableOwner VARCHAR(100) = 'Person'

EXEC sp_pkeys @table_name =  @varTableName   ,  @table_owner = @varTableOwner

EXEC sp_fkeys @pktable_name = @varTableName  , @pktable_owner = @varTableOwner

EXEC sp_fkeys @fktable_name = @varTableName , @fktable_owner = @varTableOwner

USE AdventureWorks2014;  
GO  
--nombre de la tabla
DECLARE @varTableName VARCHAR(100) = 'ContactType'  
DECLARE @varTableOwner VARCHAR(100) = 'Person'

EXEC sp_pkeys @table_name =  @varTableName   ,  @table_owner = @varTableOwner

EXEC sp_fkeys @pktable_name = @varTableName  , @pktable_owner = @varTableOwner

EXEC sp_fkeys @fktable_name = @varTableName , @fktable_owner = @varTableOwner

Es un script muy sencillo pero que me ha ayudado bastante para conocer la información sobre una tabla, más adelante les mostraré como pueden buscar una columna o una tabla en toda la BD en caso que solo tenga una pequeña noción del nombre.

SALUDOS

Continuar...

IIF, CHOOSE: FUNCIONES NUEVAS POSIBLEMENTE DESCONOCIDAS

PROBLEMA: Desde la versión 2012 tenemos nuevas funciones en muchos aspectos del motor de BD que nos ayudan a mejorar el rendimiento de nuestras o a optimizar las mismas, sabías sobre la inclusión de las funciones IIF y CHOOSE? Sabes para que sirven?

SOLUCION: Les explicaré de manera general el funcionamiento y algunos ejemplos de los mismos.

IIF

Muchas personas que hemos venido trabajando versiones anteriores a SQL SERVER 2012 hemos estado pidiendo a gritos una función similar; devuelve VERDADERO o FALSO para expresión de tipo booleana analizada dentro de la sintaxis.

En anteriores versiones usábamos la sintaxis CASE WHEN para tener un resultado similar a la función IIF, sin embargo la gran diferencia es que la primera función puede evaluar más de una expresión, la segunda solamente puede evaluar una.

Formato sencillo
CASE expresionBooleana
WHEN valorExpresionBooleana1 THEN valorDevueltoParaResultadoVerdadero1
WHEN valorExpresionBooleana2 THEN valorDevueltoParaResultadoVerdadero2
…
WHEN valorExpresionBooleanaN THEN valorDevueltoParaResultadoVerdaderoN
ELSE valorDevueltoParaELSE
END

USE AdventureWorks2014;  
GO  
SELECT   ProductNumber, ProductLine,  Category =  
      CASE ProductLine  
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END,  
   Name  
FROM Production.Product  
ORDER BY ProductNumber;  
GO

Formato complejo

CASE

WHEN expresionBooleana1 THEN valorDevueltoParaResultadoVerdadero1

WHEN expresionBooleana2 THEN valorDevueltoParaResultadoVerdadero2

…

WHEN expresionBooleanaN THEN valorDevueltoParaResultadoVerdaderoN

ELSE valorDevueltoParaELSE

END

USE AdventureWorks2014;  
GO  
SELECT   ProductNumber, Name, [Price Range] =   
      CASE   
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'  
         WHEN ListPrice < 50 THEN 'Under $50'  
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'  
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'  
         ELSE 'Over $1000'  
      END  
FROM Production.Product  
ORDER BY ProductNumber ;  
GO  

Un ejemplo sencillo para IIF es el siguiente:

DECLARE @a int = 45, @b int = 40;  
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;  

Ahora bien, tratemos de aplicar la función IIF a los ejemplos utilizados para CASE

USE AdventureWorks2014;  
GO  
SELECT   ProductNumber, ProductLine,  Category =  
      CASE ProductLine  
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END, 
   ResultadoIIF = 
   IIF( ProductLine = 'R' , 'Road' , 
  IIF( ProductLine = 'M' , 'Mountain' , 
   IIF( ProductLine = 'T' , 'Touring' ,
    IIF( ProductLine = 'S' , 'Other sale items' , 
     'Not for sale' ) ) ) )
   , Name  
FROM Production.Product  
ORDER BY ProductNumber;  
GO

USE AdventureWorks2014;  
GO  
SELECT   ProductNumber, Name, [Price Range] =   
      CASE   
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'  
         WHEN ListPrice < 50 THEN 'Under $50'  
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'  
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'  
         ELSE 'Over $1000'  
      END  
   , ResultadoIIF = 
   IIF( ListPrice = 0 , 'Mfg item - not for resale'  ,
  IIF( ListPrice < 50 , 'Under $50' ,
   IIF( ListPrice >= 50 and ListPrice < 250 , 'Under $250' , 
    IIF( ListPrice >= 250 and ListPrice < 1000 , 'Under $1000' ,
     'Over $1000'   ) ) ) ) 
FROM Production.Product  
ORDER BY ProductNumber ;  
GO 

CHOOSE

Esta función devuelve el valor contenido en una lista de valores dado una posición específica.

CHOOSE ( posición , valor1, valor2 , valor3[,  valorN ] ) 

SELECT CHOOSE ( 3, '1. UNO', '2. DOS', '3. TRES', '4. CUATRO' ) AS Resultado;  

USE AdventureWorks2014;  
GO  
SELECT ProductCategoryID, CHOOSE (ProductCategoryID, 'A','B','C','D','E') AS Expression1  
FROM Production.ProductCategory; 

USE AdventureWorks2014;  
GO  
SELECT JobTitle, HireDate
, CHOOSE(MONTH(HireDate),'Invierno','Invierno', 'Primavera','Primavera','Primavera','Verano','Verano', 'Verano','Otoño','Otoño','Otoño','Invierno') AS Estacion 
FROM HumanResources.Employee  

Espero que estos ejemplos les sirvan para comprender mejor el funcionamiento y además puedan aplicar dichas funciones si es que se requiere.

SALUDOS!

Continuar...

SP, VERSIONES, HERRAMIENTAS DE SQL SERVER

Con la salida del SERVICE PACK 2 para SQL SERVER 2014, se me ocurrió hacer una recopilación de las versiones, herramientas y SP para las versiones que vengo utilizando, sé que no soy el primero que hace esto, sin embargo en una ocasión un MVP me dijo que hiciera un blog para escribir sobre las nuevas cosas que vaya aprendiendo, esto con la finalidad de cuando necesite recordar algo, ya sabré donde buscar en primera instancia, espero que también les sirva a ustedes.

SERVICE PACKS

SQL SERVER 2016
SQL SERVER 2014	SP1	SP2
SQL SERVER 2012	SP1	SP2	SP3

DATA TOOLS

SQL SERVER DATA TOOLS INVISUAL STUDIO 2015

SQLSERVER DATA TOOLS BUSINESS INTELLIGENCE FOR VISUAL STUDIO 2013

SQLSERVER DATA TOOLS BUSINESS INTELLIGENCE FOR VISUAL STUDIO 2012

EXPRESS EDITIONS

SQLSERVER 2016

SQLSERVER 2014

SQLSERVER 2012

EVALUATION EDITIONS

SQLSERVER 2016

SQLSERVER 2014

SQLSERVER 2012

EXTERNAL TOOLS

SQL DATA DICTIONARY Herramienta sencilla para realizar el diccionario de nuestra base de datos, con la versión demo creo que es suficiente, sin embargo el costo no es tan alto, ustedes deciden.

Con el tiempo iré actualizando este artículo con herramientas que yo en lo particular utilizo, espero que les sirva, porque seguramente a mi también me servirá.

SALUDOS!

Continuar...

VERSIONES DE EVALUACION SQL SERVER 2016

Como muchos han de saber, hace algunas semanas se anunció que la versión SQL SERVER 2016 DEVELOPER sería gratuita, bien pues esta descarga la podemos hacer desde la siguiente liga:

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

Después nos pedirá que iniciemos sesión o en su caso registrarnos:

Nos pide cierta información para el registro

Después de dar click en continuar, se iniciará automáticamente la descarga, elegimos la opción deseada.

En mi caso elegí descargar la instalación, porque quiero conservar el ISO para futuras instalaciones:

Con esto ya tendremos el ISO para poder realizar la instalación:

Afortunadamente ahora en esta nueva versión ya trae las ligas para poder descargar e instalar las herramientas como SQL SERVER DATA TOOLS

Una vez seleccionada la opción de instalación de una nueva instancia, podremos darnos cuenta que nos da la opción de seleccionar alguna de las versiones de las evaluación, estas son  EVALUATION, DEVELOPER, EXPRESS. La primera nos da la opción de instalar todas las características de una versión completa de SQL SERVER sin embargo solo es por 180 días, DEVELOPER también nos permite instalar todas las características, sin embargo solo es para desarrollo, no está permitido su uso a nivel producción, y la EXPRESS es una versión más ligera.

Una gran opción para desarrollar y probar las nuevas características que nos trae esta versión.

SALUDOS!

Continuar...

OBJETO SECUENCIA

PROBLEMA: Muchos o todos conocemos lo que es una columna con la propiedad autoincrementable, sin embargo, a veces requerimos de un variable que se pueda utilizar por toda la base de datos y que tenga la misma propiedad de ser autoincrementable al igual que una columna IDENTITY, sabemos que objeto utilizar?

SOLUCION: A partir de la versión 2012, SQL SERVER introdujo los objetos llamados SECUENCIA, dichos objetos pueden ser creados como cualquier otro, llámese una vista, una tabla, una función entre otros y con la particularidad que puede ser utilizado en cualquier lado de nuestra base de datos, veamos como crear uno y como utilizarlo.

Primero veamos la sintaxis que nos ofrece la MSDN

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

No detallaré todas las opciones, solo lo que creo considerable. Para empezar, es necesario señalar que dicho objeto se crea utilizando tipos de datos numéricos (INT, SMALLINT, BIGINT, TINYINT, FLOAT, NUMERIC, entre otros ). Veamos algunos ejemplos ahora:

USE pruebas; 

CREATE SEQUENCE dbo.objSecuencia AS INT
 START WITH 50
 INCREMENT BY 3
 MINVALUE 50 MAXVALUE 60
 CYCLE; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

USE pruebas; 

CREATE SEQUENCE dbo.objSecuencia AS INT
 START WITH 50
 INCREMENT BY 3
 MINVALUE 50 MAXVALUE 60
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

Secuencia hacia atrás:

USE pruebas;

CREATE SEQUENCE dbo.objSecuencia AS INT
 START WITH 50
 INCREMENT BY -3
 MINVALUE 50 MAXVALUE 60
 CYCLE
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

USE pruebas;

CREATE SEQUENCE dbo.objSecuencia AS INT
 START WITH 50
 INCREMENT BY -3
 MINVALUE 50 MAXVALUE 60
 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

SELECT NEXT VALUE FOR dbo.objSecuencia; 
GO

Si al objeto le especificamos el tipo de dato, es necesario respetar los rangos que dicho tipo de dato puede almacenar, un ejemplo el siguiente:

CREATE SEQUENCE dbo.objSecuencia AS TINYINT
 START WITH 0
 INCREMENT BY 50
 MINVALUE 0 MAXVALUE  255
GO

USE pruebas;

CREATE SEQUENCE dbo.objSecuencia AS TINYINT
 START WITH 0
 INCREMENT BY 100
 MINVALUE 0 MAXVALUE  255
 CYCLE
GO

DECLARE @tab TABLE ( columna1 TINYINT, descripcion VARCHAR(100) ); 

INSERT INTO @tab
SELECT NEXT VALUE FOR dbo.objSecuencia , 'uno'; 

INSERT INTO @tab
SELECT NEXT VALUE FOR dbo.objSecuencia , 'dos'; 

INSERT INTO @tab
SELECT NEXT VALUE FOR dbo.objSecuencia , 'tres'; 

INSERT INTO @tab
SELECT NEXT VALUE FOR dbo.objSecuencia , 'cuatro';

INSERT INTO @tab
SELECT NEXT VALUE FOR dbo.objSecuencia , 'cinco';

INSERT INTO @tab
SELECT NEXT VALUE FOR dbo.objSecuencia , 'seis';

SELECT * FROM @tab; 

Un último ejemplo, como les comentaba podemos utilizarlos en muchas partes de nuestra base de datos, aquí lo utilizaremos en una consulta de la base de datos AdventureWorks2014 junto con la clausula OVER

SELECT 
NEXT VALUE FOR dbo.objSecuencia OVER ( ORDER BY BusinessEntityID ) AS col, 
*
FROM HumanResources.Employee

Espero que con estos ejemplos puedan implementar objetos secuencia con lo requieran.

SALUDOS!

Continuar...

MODIFICANDO LA NUMERACION DE UN IDENTITY

PROBLEMA: En los foros de MSDN, muchas personas se preguntan como modificar el siguiente número que utilizará la propiedad IDENTITY al momento de realizar una inserción.

SOLUCION: Utilizando la sentencia DBCC CHECKIDENT podemos resolver esto de la siguiente manera.

DBCC CHECKIDENT

Primero crearemos una tabla con algunos datos de prueba:

IF OBJECT_ID( 'dbo.tablaPruebasIdentity' ) IS NOT NULL
 DROP TABLE dbo.tablaPruebasIdentity; 

CREATE TABLE dbo.tablaPruebasIdentity( 
 columnaID SMALLINT IDENTITY( 1,1 ) 
 , descripcion VARCHAR(100) 
); 

INSERT INTO dbo.tablaPruebasIdentity( descripcion ) 
VALUES( 'uno' ) , ('dos'), ('tres'), ('cuatro') ;  

Ahora bien, podemos revisar cual es el último valor para nuestra columna con la propiedad IDENTITY de la siguiente manera:

DBCC CHECKIDENT('dbo.tablaPruebasIdentity', NORESEED )

Ejecutemos la siguiente instrucción para modificar el siguiente valor para nuestra columna IDENTITY

DBCC CHECKIDENT( 'dbo.tablaPruebasIdentity' , RESEED, 10 ); 

De esta manera podemos observar que el valor actual para la propiedad IDENTITY ( no es el ultimo valor que se encuentra en la columna) es de 10, lo que quiere decir que el siguiente valor para la columna será 11, ejecutemos nuevamente: 

DBCC CHECKIDENT('dbo.tablaPruebasIdentity', NORESEED )

El valor para la propiedad IDENTITY ya cambió, sin embargo no quiere decir que sea el ultimo IDENTITY ingresado en la columna que es de 4, ahora insertemos nuevamente algunos datos y revisemos el contenido de la tabla:

INSERT INTO dbo.tablaPruebasIdentity( descripcion ) 
VALUES( 'once' ) , ('doce'),('trece') ; 

SELECT * FROM dbo.tablaPruebasIdentity; 

Ejecutemos nuevamente la sentencia de verificación de IDENTITY: 

DBCC CHECKIDENT('dbo.tablaPruebasIdentity', NORESEED );

Los valores han cambiado nuevamente, ahora regresemos el valor de la columna IDENTITY para que inserte el número 5 y consultamos la propiedad nuevamente: 

DBCC CHECKIDENT( 'dbo.tablaPruebasIdentity' , RESEED, 4 ); 
DBCC CHECKIDENT('dbo.tablaPruebasIdentity', NORESEED ); 

Y una vez más insertemos algunos datos para verificar el contenido de la tabla: 

INSERT INTO dbo.tablaPruebasIdentity( descripcion ) 
VALUES( 'cinco' ) , ('seis'); 

SELECT * FROM dbo.tablaPruebasIdentity; 

Revisamos la propiedad IDENTITY

DBCC CHECKIDENT('dbo.tablaPruebasIdentity', NORESEED );

El primer valor subrayado con rojo recordemos que es el ultimo valor ingresado en cualquier operación por lo que el siguiente número que fuera ingresado ocupará el valor de 7, y el segundo valor subrayado es el valor más alto ingresado. Pero que pasaría si nosotros seguimos insertando valores hasta alcanzar el número 11, que pasará? Lo saltará automáticamente? La respuesta es NO, veamos:

INSERT INTO dbo.tablaPruebasIdentity( descripcion ) 
VALUES( 'siete' ) , ('ocho'), ( 'nueve' ) ,('diez'), ( 'once' ), ('doce');

SELECT * FROM dbo.tablaPruebasIdentity; 

Por última vez revisemos cual será el siguiente valor que se insertará:

DBCC CHECKIDENT('dbo.tablaPruebasIdentity', NORESEED );

Con esta información, ya podrán reiniciar o establecer el siguiente número en la columna que contenga dicha propiedad IDENTITY, podrán repetir los números siempre y cuando su columna no sea parte de la llave primaria.

SALUDOS!

Continuar...