Download Objetos de la Base de Datos

Objetos de la Base de Datos
Todos los datos de una base de datos de Microsoft® SQL Server™ 2005/8
están contenidos en objetos llamados tablas. Cada tabla representa algún tipo
de objeto con significado para los usuarios. Por ejemplo, en una base de datos
de una escuela podríamos encontrar una tabla de clases, una tabla de
profesores y una tabla de alumnos.
Las tablas de SQL Server tienen dos componentes principales:
• Columnas
Cada columna representa algún atributo del objeto representado por la tabla,
por ejemplo, una tabla de piezas tendría columnas para Id., color y peso.
• Rows
Cada fila representa una única aparición del objeto representado por la tabla.
Por ejemplo, la tabla de piezas tendría una fila por cada una de las piezas
comercializadas por la empresa.
1
Objetos de la Base de Datos
Como cada columna representa un atributo de un objeto, los datos de cada
aparición de la columna son similares.
Una de las propiedades de las columnas es su tipo de datos, que define el tipo
de datos que la columna puede alojar.
SQL Server tiene varios tipos de datos base que se pueden especificar para las
columnas:
Binary
Bigint
bit
Char
datetime
decimal
Float
image
Ent
Money
Nchar
Ntext
nvarchar
Numérico
Real
smalldatetime
smallint
smallmoney
sql_variant
sysname
Text
timestamp
tinyint
varbinary
varchar
uniqueidentifier
2
Objetos de la Base de Datos
Además, los usuarios pueden crear sus propios tipos de datos definidos
por el usuario
ejemplo:
-- Crea un tipo de datos “Cumpleaños”que permite nulos
EXEC sp_addtype cumpleaños, datetime,'NULL'
GO
-- Crea una tabla que utiliza el mismo tipo de datos
CREATE TABLE empleado
(
emp_id
char(5),
emp_first_nameemp_first_name
char(30),
emp_last_nameemp_last_name
char(40),
emp_birthdayemp_birthday
cumpleaños
)
3
Objetos de la Base de Datos
Aquí se puede observar los valores del nuevo tipo generado, a continuación se
insertan datos en esta nueva tabla:
insert into empleado values ('1','apellido1','apellido2','1776-7-4');
4
Objetos de la Base de Datos
Leemos los resultados:
SELECT TOP 1000 [emp_id]
,[emp_first_nameemp_first_name]
,[emp_last_nameemp_last_name]
,[emp_birthdayemp_birthday]
FROM [AdventureWorks].[dbo].[empleado]
5
Ejemplo:
Objetos de la Base de Datos
A la misma base de datos, se agregará otro tipo de datos, llamado imss, el cual
debe ser tipo varchar y tener una longitud de 11 dígitos, la columna no puede ser
NULL. Este tipo de dato después se agregará a la tabla empleado.
EXEC sp_addtype IMSS, 'varchar(11)', 'NOT NULL';
GO
Posteriormente agregaremos una columna llamada IMSS, la cual utilizará el
mismo tipo de dato “IMSS”
ALTER TABLE empleado ADD IMSS IMSS
GO
Finalmente se introducirán datos en la tabla “empleado” usando la columna IMSS:
insert into empleado values('2','apellido1','apellido2','2-21-1923','12345678901')
go
6
Objetos de la Base de Datos
Revisemos los valores insertados:
Select * from empleado;
GO
7
Objetos de la Base de Datos
Ejercicio:
1.- Crear 2 tipos de datos nuevos:
•telefono: el cual debe ser de tipo INT y no puede ser NULL
•descripcion: Este tipo de dato debe ser de tipo varchar y con longitud de 54
caracteres y puede ser NULL.
2.- Agregar a la tabla “empleado” dos columnas nuevas llamadas TELEFONO y
descripción, cada una de su tipo correspondiente:
Columna
Tipo_de_dato
TELEFONO
telefono
Descripcion
descripcion
3.- Finalmente introducir 2 registros y mostrar la salida de la query:
Select * from empleado;
8
Objetos de la Base de Datos
Los tipos de datos definidos por el usuario hacen que la estructura de las tablas
tenga mayor significado para los programadores y contribuyen a asegurar que
las columnas que almacenan clases de datos similares tengan el mismo tipo de
datos base
Cuando se ha asignado un tipo de datos a una columna, todos los valores que
se coloquen en la columna deben ser de ese tipo de datos.
Las instrucciones SQL pueden especificar que se utilicen valores de distintos
tipos de datos como valor de origen sólo si SQL Server puede convertir
implícitamente el tipo de datos del valor de origen al tipo de datos de la
columna.
Por ejemplo, SQL Server admite la conversión implícita de valores int a
decimal; por tanto, las instrucciones SQL pueden especificar valores int como
el valor que se va a asignar a una columna decimal.
9
Entendiendo las Estadísticas en SQL SERVER
• Las estadísticas de SQL Server son información
sobre la distribución de los datos existentes en las
columnas de las tablas de nuestra base de datos.
• A través de las estadísticas, el servidor conoce
como es la información de una columna, como por
ejemplo, si varía mucho, si todos los datos son
iguales y los niveles de variación que hay.
10
Entendiendo las Estadísticas en SQL SERVER
• Esta información la utiliza cuando nosotros le
pedimos datos de ciertas tablas que cumplan
con ciertas condiciones (select.. from .. where).
• Las condiciones que especificamos en el
where de una consulta son analizadas por el
optimizador de consultas para determinar cual
es la forma más rápida de obtener la
información requerida.
11
Entendiendo las Estadísticas en SQL SERVER
• Suponiendo que tenemos una consulta con varias condiciones en
el where, el servidor examina las estadísticas asociadas a las
columnas referenciadas en el where, como también los índices
existentes en la tablas, y donde participan esas columnas.
• Para el caso de los índices, SQL Server maneja un conjunto de
estadísticas de forma similar que para una columna de una tabla,
independiente del tipo de índice (agrupado o no agrupado).
• Si hay un índice sobre la columna en que estamos buscando, en la
mayoría de los casos lo usará (dependerá de las estadísticas y de
otros factores) y en caso contrario, realizará un SCAN sobre la
tabla o el índice agrupado (en caso de tener).
12
¿Cómo se consultan los catálogos internos?
• Algunas de las tablas a las que hacemos referencia en las consultas no
necesariamente existen físicamente y muchas de ellas, o son vistas solamente o
se construyen al momento de ejecutar
• Los objetos (tablas, procedimientos, etc.) rara vez están almacenados por el
nombre que nosotros le damos, sino que por un identificador interno.
• Para obtener el identificador interno de un objeto, existe una función llamada
object_id('objeto') que lo retorna, pudiendo usarse en una consulta o una
instrucción set.
• El catálogo donde se almacena la información de los índices y estadísticas de
una tabla se llama sysindexes.
• Para consultarlo se debe entonces filtrar la información por el id de la tabla
HumanResources.Employee, como se muestra en la siguiente consulta:
use AdventureWorks
select * from sysindexes where id = object_id('HumanResources.Employee')
go
13
¿Cómo se consultan los catálogos internos?
Como resultado de esta consulta se obtiene lo siguiente:
El resultado indica que para esta consulta, se han creado 7 índices con diferentes
tamaños (dpages)
14
Estadísticas en SQL SERVER
EJEMPLO 1
Se creará la siguiente tabla dentro de la base de datos AdventureWorks:
use AdventureWorks
CREATE TABLE [dbo].[Test] (
[identificador] [int] IDENTITY (1, 1) NOT NULL ,
[nombre] [varchar] (50) NOT NULL ,
[apellido] [varchar] (50) NOT NULL ,
[direccion] [varchar] (50) NOT NULL ,
[fechanacimiento] [datetime] NOT NULL ,
[login] [varchar] (20) NOT NULL
) ON [PRIMARY]
GO
15
Estadísticas en SQL SERVER
Insertemos algunos registros en la base tabla de prueba,
copiándolos de la tabla HumanResources.Employee en la
base de la misma base de datos.
use AdventureWorks
--SET IDENTITY_INSERT Test ON
--GO
insert into Test (identificador, nombre, apellido, direccion,
fechanacimiento)
(select EmployeeID,NationalIDNumber,LoginID, Title,
BirthDate
from AdventureWorks.HumanResources.Employee)
GO
16
Estadísticas en SQL SERVER
Verificando que la tabla se encuentre con datos:
17
Estadísticas en SQL SERVER
• Ahora tenemos nuestra tabla, con un índice agrupado
(cluster) en la columna de identidad, y con un cantidad de
registros pequeña.
• Para el objetivo de nuestro ejemplo no es necesario tener
gran cantidad de registros.
• Existen dos formas tradicionales de ver las estadísticas.
Una de ellas es consultando el catálogo interno de SQL
Server o la otra es a través de la interfaz gráfica asociada
al plan de ejecución de una consulta.
18
Estadísticas en SQL SERVER
• Si realizamos ahora una consulta simple sobre la tabla buscando
por la columna apellido, que sabemos no está considerada en
ningún índice, se realizarán cambios sobre sysindexes.
• Por ejemplo, si la siguiente consulta retorna o no registros, las
estadísticas serán creadas (por que nuestras base de datos está
configurada para que las cree automáticamente).
select * from test where apellido = 'gonzález'
19
Estadísticas en SQL SERVER
• Haciendo nuevamente la consulta sobre sysindexes, el
resultado cambia, obteniéndose un nuevo registro. Se sabe
que es una estadística ya que la cantidad de paginas
(dpages = 0) y el grupo (groupid=0) son cero.
20
Estadísticas en SQL SERVER
• Ahora, si se quiere conocer que hay en la estadística,
existe una instrucción de administración llamada dbcc
showstatistics (tabla, indice|estadística) para hacerlo.
• El resultado de la ejecución
de dbcc show_statistics (test,
Nombre_de_la_estadistica)
se despliega ahora. El
nombre de la estadística se
muestra a continuación
21
Estadísticas en SQL SERVER
22
Estadísticas en SQL SERVER
El resultado se divide en tres grupos:
El primer grupo nos entrega información general de la
estadística.
Acá encontramos el nombre, fecha de actualización, la
cantidad de filas de la tabla (Rows = 290), la cantidad
de filas que se consideraron para obtener la muestra
(Rows Sampled = 290), la cantidad de pasos (steps =
188) (explicado más adelante), la densidad y el largo
promedio de los datos de la columna en el caso de una
estadística o el largo promedio de los datos del índice
en caso de ser un índice.
23
Estadísticas en SQL SERVER
• El segundo grupo muestra datos específicos asociados a
la columna.
– En este caso, la densidad (0.003448276), el largo
promedio y la columna. Para el caso de un índice,
presenta varias líneas con información y densidades
para las columnas del índice, mostrando desde la
primera columna hasta todas las columnas juntas.
– La densidad se obtiene de calcular la siguiente
ecuación:
Densidad = 1 / (cardinalidad de la llave del índice)
24
Estadísticas en SQL SERVER
La cardinalidad de la llave corresponde a la cantidad de datos
únicos de la columna o columnas.
Lo importante es que la densidad sea el valor más chico
posible. Mientras más pequeño, SQL Server obtendrá mejores
resultados en las búsquedas.
Por ejemplo, si la densidad de un índice es 0,3, significa que
sólo se puede filtrar hasta un 30% de los datos con ese índice,
resultado que puede considerarse muy malo.
Un buen valor debe estar por debajo del 5%. En nuestro
ejemplo, un densidad de 0.003448276 (3%) significa que en
la tabla hay 1/0.0034 valores diferentes, o bien 294.11. La
consulta select count(distinct(apellido)) from test confirma el
resultado:
25
Estadísticas en SQL SERVER
Recordemos que Distinct
devuelve el número de
valores diferentes dentro
de una tabla
• El tercer bloque corresponde a la distribución de los datos de la
columna en la tabla.
• Para un índice de varias columnas se considera sólo al valor de
la primera columna.
• La información se segmenta por rangos (los pasos = 188),
donde en cada línea se encuentran los valores
correspondientes a los datos que están entre el
RANGE_HI_KEY de esa línea y son menores a
RANGE_HI_KEY de la línea de más abajo.
26
Estadísticas en SQL SERVER
En el resultado desplegado antes, se obtiene que entre
adventure-works\alan0 y adventure-works\alex0, no hay
más valores (RANGE_ROWS = 0), hay 1 valor igual
(EQ_ROWS = 1), no hay valores diferentes en el rango sin
considerar el mismo valor administrador
(DISTINCT_RANGE_ROWS = 0), y para finalizar, el
promedio de filas (cantidad) por cada valor distinto en el
rango es uno (AVG_RANGE_ROWS = 1).
Corresponde notar que DISTINCT_RANGE_ROWS no
incluye las filas que son iguales a RANGE_HI_KEY ya que
estas están incluidas en EQ_ROWS.
27
Estadísticas en SQL SERVER
• Toda esta información le permite saber al optimizador
de consultas como es la información de la columna o
índice, sin necesidad de "tocar" los datos.
• Y por el mismo motivo, si queremos que el analizador
siempre encuentre la mejor opción y el servidor
responda al máximo, debemos proveer a éste de las
estadísticas actualizadas.
28
Estadísticas en SQL SERVER
• Por lo tanto, podemos decir que las estadísticas pueden
actualizarse o eliminarse manualmente a través de la
interfaz gráfica o de consultas sql (drop statistics).
• Para borrar las estadísticas se utiliza el siguiente
comando:
use AdventureWorks
DROP STATISTICS dbo.Test._WA_Sys_00000002_7ABC33CD
• Además, conviene saber que SQL Server se encarga de
actualizarlas y eliminarlas cuando estima que es
necesario, pero también puede agregarse una tarea de
mantención que las actualice cada cierto tiempo.
29
Estadísticas en SQL SERVER
Observemos el comportamiento de las estadísticas gráficamente
Se realiza la siguiente consulta:
select * from Test where nombre='136%'
Al final de la consulta se puede
Observar la creación de nuevas
estadísticas
30
Estadísticas en SQL SERVER
De la misma manera se pueden
generar estadísticas a la
medida:
create statistics Stats_apellido
on dbo.Test(apellido)
with FULLSCAN
31
Estadísticas en SQL SERVER
Se muestran los resultados:
DBCC SHOW_statistics('dbo.Test','Stats_apellido') with histogram
La configuración de las
estadísticas
automáticas se
realizan de la siguiente
manera:
32
Estadísticas en SQL SERVER
Set Statistics IO, con esta opción podemos observar, cuantos scans
fueron realizados, cuantas lecturas lógicas y físicas fueron realizadas
y cuantas páginas fueron colocadas en cache anticipadamente:
use AdventureWorks
SET STATISTICS IO ON
GO
select COUNT(*) From dbo.Test
GO
SET STATISTICS IO OFF
Resultados:
Table 'Test'. Scan count 1, logical reads 4, physical reads
0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
33
Estadísticas en SQL SERVER
Set Statistics Time: Regresa tiempos concernientes a la query ejecutada:
use AdventureWorks
SET STATISTICS TIME ON
GO
select COUNT(*) From dbo.Test
GO
SET STATISTICS TIME OFF
Resultados
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 11 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
34