Curso SQL Server Implementing

Introducción

Esta página son los apuntes que he tomado durante el curso que seguro que están llenos de faltas de ortografía y con algunas otros errores de comprensión ya que los he tomado al vuelo.

Configuracion de red

Protocolos

TDS Tabular data stream, devuelve los datos tabulados SQL Native Access client SNAC (quitando muchas capas de configuracion)

TCP/IP, Named PIpes(utiliza el escribir a un archivo para mandar información a sql server) o Shared Memory (con la pega de que no se puede tener la aplicacion en otra máquina)

VIA es un protocolo para virtualización que está por extinguir.

Se pueden crear alias para conectarse a un servidor. Estos alias son de forma local. Lo bueno del alias es que especificas en el alias como te conectas al servidor (np, tcp, etc)

Seguridad

Como recuerdo del otro curso, hay logins (que son los usuarios de servidor) y después cada base de datos tiene otra lista de usuarios, que hacen mapping con logins de servidor. ASí solo hace falta logarse una vez. También se puede configurar logins con grupos de windows.

Se puede usar la db de usuarios de sql server o de directorio activo. Si usamos usuarios de sql server por defecto se heredarán las políticas de seguirdad (password) para estos usaurios de sqlserver, pero se puede salta esta política con un chckbox.

Grupos especiales de seguridad locales

Existen ciertos grupos de seguridad que se crean cuando instalas SQL Server, para dar permisos a los usaurios que arrancan los servicios. Podemos ver estos grupos en la gestión de usuarios de la máquina. De esta forma, cuando instalamos una instancia nueva de sql server se le da permisos a un grupo local de la máquina, por ejemplo, pare el directorio data donde están los ficheros de base de datos. Después, el usuario que arranca el servicio se mete en ese grupo y así no hay que tocar los permisos de las carpetas. Hay muchos grupos para diferentes privilegios.

Herramientas de trabajo con sql server

SQL Server management studio es la herramienta principal. Se puede integrar con Source Safe para tener historicos de scripting. Hay que tener instalado la parte cliente de source safe en el servidor.

También se instala BI Development Sutdio, que se usa para Analysis Services, Integration Services y Reporting Services.

NOTA: Aunque es sqlserver 2008R2 se usa el visual studio 2008. Esto genera algunos problemas como el que no valen las plantillas de reporting services de visual studio 2010.

Books Online: para buscar ayuda sobre los comandos.

Configuración de los servicios

Configuration manager

visto antes

SQL SErver services

La instancias por defecto se llama MSSQLSERVER El servicio de sql server agent existe por cada instanacia de sql server. El browser se encarga de gestionar la lista de instancias que hay en el servidor.

Profiler

Para ver inforamción de ejecución del servidor. Para monitorización de ejecución de servidor. Lo bueno de esta herramienta es que se puede integrar con el performance monitor de windows, dentor del propio profiler, para ver implicaciones de querys, rendimientos, no solo del sql server sino de la máquina en conjunto.

Tuning Advisor

Da recomendaciones sobre como configurar DB (creación de indices, hacer particionados, etc)

Master Data Servicoes Confiugration Manager

Componente web para administrar modelos de datos.

Reporting service conf manager

configuracion de reporting services

SQL Server Management Objects (SMO)

Clases para .net para dar soporte de administración de SQL Server. Antes eran clases com pero tenía el problema que si al usasar la clase moría el proceso moría todo (¿bases de datos tambien?).Con .net solo moriría la máquina virtual de .net (CLR).

Tipos de datos

Los tipos de datos se aplican a columnas de tablas, a variables, a expresiones y a parámetros. Por optimización es crítico elegir el tipo de dato correcto para cada caso.

Hay tres conjuntos de tipos de datos:

Sistemas Alias (los denominamos sinónimos) User-defined (personalizados): gerarquicos, espacial, etc que ahora van incorporados pero antes no.

Identity

Es una propiedad que da un valor que está dentro del rango de los números enteros, y que se genera de forma automática. Para la clave primaria. Se especifica por ejemplo con:

id int NOT NULL IDENTITY(1,1)

el primer 1 es por que numero empieza y el segundo es el incremento

Numeros aproximados

Son los float, para numeros muy grandes o muy pequeños. El típico 1.XXXXE20

Fechas

date datetime2 fecha con hora datetime es con un offset (...)

Identificadores únicos

Los GUID. para generar números únicos en nuestro entorno. Ocupan 128 bit. No se pueden usar con el IDENTITY. Suele

cometerse el error de guardarse como string.

NULL y NOT NULL

No se pueden usar en variables, pero si en columnas o variables. Se puede determinar si una columna admite null.

Datos de tipo carácter

Unicode

Es implementado en sql server con comillas dobles. En unicode los caracteres ocupan dos byte. Cuidado al usar el LEN

para devlver el numero de cararecteres... Se puede usar DATALENGHT.

Cuando definamos tablas, si queremos que sean unicode tendremos que usar nchar,nvarchar,ntext. Si queremos definier

un literal ANSI (heredero de ascii) se ponen con comillas simples 'ejemplo', y si queremos decirle que sea unicode

se definie con N delate n'ejemplo'.

Hay que tener en cuenta que nuestros datos no pueden pasar el dato de una página de datos de sql server que es de

8k, excepto image y text. Asi que nuestros varchar y nvarchar no pueden pasar de 8000 caracteres.

También existe un tipo de datos xml que tampoco tiene límite.

collation

Representa el como se ordenan las cadenas y como se busca. Se establece a nivel de columna de tipo string (varchar,

nvarchar, etc), aunque sepuede especificar a nivel de base de datos de forma por defecto, o también por servidor. Es

importante seleccinar el idioma especifico si no la ñ estará detrás del n si cogo el EEUU estará en otro sitio.

Hay dos tipos de collation, windows y sql. los sql están despareciendo. Cuando definimos el collation le decimos

algunas reglas como por ejemplo si lo queremos Case sensitive y accent sensitive. Si definimos a nivel de servidor

que sea Case Sensitive los nombres de las tablas, por ejemplo, tambien los son.

Conversión de tipos

Importante saber las reglas implícitas. '1'+'2' puede ser '12' o 3. Por eso es mejor siempre funciones de

conversiones de datos cuando se mezclan tipos.

CAST

Para mostrar el valores a otro tipo. CAST(<columna> as VARCHAR(12))

en el books online hay una tabla de posibles conversiones buscando la palabra convert

CONVERT

Parecido al CAST pero de la forma: CONVERT(varchar(12),<columna>). Lo vueno del convert es que puede elgejir el

formato con el que se va a convertir.Por ejemplo para fechas. es el tercer parámetro de convert. el tipo de

conversión que le especificamos en el tercer parámetro depende del tipo de dato al que convertimos. En el books

online da más datos.

Echar un ojo a la tabla de coneversiones implícitas que biene en los books online

Problemas habituales cuando se usan las conversiones implíticitas son los Redondeos, truncamientos, valores fuera de

rango, que se pueden evitar usando las funciones de converisón.

timestamp y rowversion

timestamp, se utiliza para reprensentar cuando cambia un dato. Ahora se usa rowversion. Este último está relacionado

con la concurrencia optimista.

concurrencia optimista es la que presupone que no se va a usar un bloque al mismo tiempo. con optimista es leo,

calculo, bloqueo, escribo, quito bloqueo. En el pesismista se bloquea cuando se lee.

Ejemplo de update cuando es modelo optimista:

UPDATE <TABLA> SET <COLUMAN>=<VALOR> WHERE ID=<N> AND <COLUMNA>=<VALOR ANTERIOR>

Con rowversion esto se hace de forma automática. Solo guarda el rowversion y no todos los datos así notiene que

comparar todo, para saber si se ha modificado la fila mientra estaba calculando los cambios.

Alias data types

Alias de otros tipos. Podemos definier que ProductNumber sea un tipo varchar(20)

Normalizando

Eliminar datos repetidos en tablas separadas NO mezclar distintas entidades en la misma tabla (...)

Claves Primarias

Elegir la clave primaria hay que sopesar el tamaño de la clave y también el nivel de acceso que va a tener. Puede

haber muchas candidatas pero solo puede ser una que se llamará PK primary key y las otras serán AK alternative.

Puede ser con múltiples columnas pero no es aconsejable. La PK no debería cambiar nunca, aunque se puede...

Hay claves naturales, que son las que van en el dato (como el dni) pero hay algunas detipo subrrogado que se

calculan.

Foreign key

Referencias a otras tablas. Solo puede tener valores de la pk de la tabla referenciada o valor nulo. Se crean

restricciones de sistema.No sepueden borrar datos de la tabla padre si hay referencias a esa fila de la tabla padre.

Aunque sepueden programar acciones "en cascada" para borrar hijos de esa fila padre o para poner a null la fk de la

tabla hijo qu eestá relacionada con esa fila padre.

Tablas de sistema

Las tablas de sql server que dan soporte para el resto de tablas. Donde están referenciadas todas las tablas de

usuario. No son visibles, pero si hay vistas de estas tablas que si son visibles. Estas tablas y vistas están en

cada una de las bases de datos.

Esquemas

Un sistema de agrupacion de tablas que reemplaza la antigua agrupación de tablas por usuarios. se sigue usando un

esquema dbo, para compatibilidad con aplicaciones antiguas que siguen usando lo de añadir dbo.<tablename>, pero en

realidad es una estructuración de tablas, para dar permisos a nivel de esquemas.

lo de dbo nos aparece por defecto en nuestras db's porque está en la model, que cuando creamos una db nueva se crea 

como la model

Todos los objetos que hay dentro de una db menos los usuarios, y los grupos, pertenecen a un esquema.

Cada usuario tiene definido un esquema por defecto. Si con un usuario hago referencia a una tabla, vista, etc, sin

poner el esquema delante del nombre, por defecto usara el que le he definido para ese usuario. Si un objeto al que

hacemos referencia sin el esquema no aparece en nuestro esquema por defecto lo irá a buscar como segunda oportunidad

al esquema dbo.

Para crear esquemas

CREATE SCHEMA <NOMBRE>

AUTHORIZATION <user>;

Para mover un objeto a otro esquema se haría con:

ALTER <OBJETO> <NOMBRE> TRANSFER <nuevo_esquema>;

Tablas

Crear tablas

CREATE TABLE <nombre> (<col> ...

Borrar Tablas

DROP TABLE <nombre>

Modificar tablas

ALTER TABLE <NOMBRE> ... (ADD, MODIFY, ETC)

Tablas temporales

Se crean en la DB temp, que se recrea cada vez que se inicial el sistema. Para crear una tabla de sesión se crean

añadiendo # al prinicipoi del nombre. Si la creo con ## es visible para todos los usuarios.

Es muy facil crearlas con instrucción select into.

Columnas calculadas

Pensadas para no ocupar espacio que se calcula cuando se hace la select.

CREATE TABLE <NOMBRE>

(...
...
 Yearofbirth AS datepart(year,DateofBirth) [PERSISTED]
  );

no se les puede hacer insercción de dato y además penaliza a la hora de hacer queryes. Si le añadimos en la creación

de la tabla la clausula PERSISTED entonces reservará espacio para almacenar el resultado del caulculo, para no

calcularlo en cada query. Para poder hacer esto hay que desactivar un flag, runabort off, para que si se produce un

desbordamiento continue con los siguentes elementos de la query.

VISTAS

Definicion de una query con nombre. Se utiliza para simplificar el modelo.

Los tipos son:

• standard views
• systems views.
• Indexed views (vista que ha sido procesada y almacenada)
• Partitioned views. (vista que está en varios servidores)

Ventajas Se enfoca más en las necesidad de usuarios y además simplifica permisos, y estructura datos.

Sobre las vistas del sistema:

• vistas de catalogo (informacion que usa el motor de DB)
• Vistas de compatibildad ¿?
• Vistas de información de esquema.

Vistas dinámicas

No consultan datos de tablas si no recursos externos, como bloqueos, el sistema, etc. Se caracterizan porque si

squema es sys y empiezan por dm_. Por ejemplo sys.dm_db_file_space_usage. NO son funciones determinstas (mismo

resultado con los mismos parametros en diferentes momentos del tiempo)

Creación de vistas

CREATE VIEW <nombre> AS SELECT ...

no se permite hacer order by de las vistas.

Solo se puede llegar a 32 niveles de provindidad en selects anidadas, y esto incluye a las select que hay dentro de

las vistas de vistas de vistas... etc..

Cadena de propietarios

Si el usuario A tiene una tabla a la que no tiene permiso el usuario B, pero el usauri A crear una vista de esa

tabla y le da permiso al usaurio B para ver esa vista... vera los datos de la tabla A. :-)

La vista por ser del mismo propietario que la tabla siempre tiene premiso de consulta.

Si la vista no es del mismo propietario, para que el usuario B pueda consultar los datos tiene que tener permisos

sobre la tabla a la que hace referencia la vista.

listado de vistas

Para ver la lista de vistas se usa la vista sys.views. con la fuccion OBJECT_DEFINITION() podemos ver la definiicion

de la vista con con sys.comments.

Vistas actualizables

Es pisbile actualizar la tabla sobre la que está la vista haciendo update a la vista. Solo puede afectar a una

tabla a la vez en caso de que la vista sea un join. No se puede actualizar columnas calculadas y tampoco en las qu

se basan los group by , distinct, having, etc.

Vistas ofuscadas

Se puede añadir WITH ENCRYPTION a la creación de la vista para que no se pueda ver el origen de la vista. Si se encripta y después de hace un alter de la vista hay que volver a ponerle with encryption porque si no la vista

que sin encriptar de nuevo.

Vistas particionadas

La consulta tiene que devolver las mismas columnas en ambos origienes. Cuando hacemos una consulta a otro servidor el orden del nombre de la tabla es <db>.<db>.<esquema>.<tabla/vista>

Indices

Para evitar los escaneos de tabla(leer toda la tabla), cuando usamos el WHERE para coger ciertos elementos y no

todos.Algunas restricciones se hacen con índices, como el UNIQ.

Un inidice es una "tabla" con el valor del indice y enlace al elemento de la tabla. en realidad no es una tabla,

sinó un árbol.

estructuras de índices

<la historia de las páginas. lapágina principal se llama root y va haciendo enlaces a otras pagínas en arbol>

Selectvidad, densidad y profundidad de índice

Selectividad

Cantidad de elementos que devuelve cuando hacemos consulta a un valor de ese índice

Densidad

Elementos únicos que tiene un índice

Profundidad

Numero de niveles dentro del índice

Estos datos son útiles para analizar el uso de índices. En algunas ocasiones puede ser mejor hacer consultas con

table scan en vez de con un índice, ya qu el índice nos va a dar muchos datos y tiene que cargar tanto el índice en

en memoria como la tabla de los datos.

fragmentacion de índices

Hay varios tipos de fragmentacion

• Interno (las páginas no están llenas)
• Externo (paginas estan fuera de la secuencia lógica)

Se produce cuando se hacen muchas modficiaciones sobre los datos de una tabla que está indexada.

Para detectar la fragmentación de los índices se puede consultar en la página de propiedades de un índice (seguro

que también hay un comando). Se soluciona reconstruyendo el índice u optimizando.

Los tipos de datos impactan en el uso de los índices. Los aproximadso son menos eficientes. Los de string son menos

efectivos que los numéricos y ocupan más espacio. Se puede usar un collation binary para hacer indices más rápido

(se indexan los textos como numeros pero se pierde la ordenación)

Si indexamos GUID's tendremos una eficiencia moderada ya que son valores grandes de 16 bytes 128 bits

Los campos bit son efectivos si los combinamos con otros índices

indices deuna columna vs compuestos

Suelen ser útiles. El primer cámpo ha de hacer más criba que el segundo.

estadisticas de los índices

Ayudan a hacer los planes de ejecución. Se puede hacer en automático o defines las queyres para generar esas

estadísticas. Tambíen se puede generar un job que las haga cada cierto tiempo.

HEAP

Estructura de datos asociada a una tabla. SQL escribe en bloques de pa´ginas llamadas extents. La página IAM es la página prinicipal. Una fila de una tabla

no puede estar en dos páginas diferentes. Cada página de un fichero está devidida en bloques y cada uno asignado a una tabla.

Operaciones en heaps

INSSERT UPDATE DELETE Se marca un bloque como que puede usarse SELECT Si hacemos un select en el heap, sin condicion, se hace un tablescan

punteros de redireccioin

Cuando un dato se ha movido de página se usan punteros de redirección que pueden

Indice cluster

Asocia el índice con los datos de la tabla. Tiene el id 1.

Un btree es un arbol balanceado, que controla que las ramas estén bien repartidas para cada nodo.

Las filas se almacenan en la tabla en el orden especificada.

Pueden ser únicos o no unicos. si es no único en este caso le añadie 4 bytes de diferenciacion a los elementos del indice ya que tiene que ser único.

Trabajando con indices cluster

Se crea como un índice normal pero añadiendo la clausula cluster. O también, cuando definimos un campo como primary key se crea de forma automática un índice

cluster.

También se puede hacer drop del indice.

Otra cosa que podemos hacer es reconstruir el índice, con alter index <indice>|all on <tabla> rebuild;. También se pueden reorganizar o deshabilitar.

No se pueden cambiar los criteros de indexación de un indice, pero se puede crear un nuevo índice con el mismo nombre poniendo la clausula with drop

existing???

Definiendo espacio libre en las páginas de los índices

Cuando tenemos tablas en las que se va a estar escribiendo mucho y modificando, es mejor tener las páginas menos llenas siempre, ya que si las tenemos muy

llenas y estamos escribiendo nuevos datos o modificando, vamos a estar creando nuevas páginas y cambiando los punteros.

Este nivel de rellenado se hace con FILL_FACTOR, especificamos el porcentaje de llenado de una página. Además con PAD_INDEX le decimos que también este

porcentaje sea aplicado a los nodos intermedios del índice.

Un ejemplo de pedir estadít sis.dm_db_index_physical_stats

Consejos de uso de indices cluster

Que sea una clave corta que sea estática que se vaya incrementando que sea única

Estla limitado a 16 columnas en indices compuestos La suma de loscampos de las columnas no pueden superar los 900 bytes

=== tipos de datos apropiados para indices cluster Numericos varchar al ser tamaño variable son más pesados de usar en índices fechas son buenos candidatos, por tamaño fijo smalldatetime es bueno porque es pequeño y fijo pero pueden repetirse datos.

Vistas Materializadas

indices sobre las vistas. Pueden mejorar las consulas pero puede tener coste a la hora de hacer actualizacinoes y aque hab´ria que modificar indices de la

tabla y de las vistas que tuvieran índices sobre esa tabla.

En la versión Enterprise y Datacenter se crean estas vistas de forma automática cuando determina que es útil.

Cuando se crea una vista de este tipo hay que decirle with schemabinding.

Planes de ejecución

Estrategias para hacer consultas.

fases de ejecución

• Recibe el comando y lo parsea.
• Resuelve las referencias a objetos (tablas, columnas, etc)
• busca un plan lo suficientemente bueno para ejecutar la query. (no se aplica a ddl) Tiene algunos patrones para construir el plan
• Se ejecuta el plan por le motor de almacenamiento y ejecución.
• El plan se almacena en la cache de planes

No siempre se ejecuta el plan estimado, debido a cambios en el tiempo, como las estadisticas o el grado actual de paralelismo de una query.

Las estimaciones se basan en las estadísticas y el plan real se basa en los datos.

Contexto de ejecucion

Es el conjunto de datos asociados a una ejecución, por ejemplo, los valores que hay dentro de la clausula where o en un insert los valores que vamos a

insertar.

Formatos del paln de ejeucción

Se pueden almacenar en texto, que está deprecado, usando set showplan_text on También se pueden almacenar en xml.

Después se puede ejeuctar una instrucción con un plan de ejecución que nosotros le especifiquemos. USE PLAN.

ver los hints de querys en los books online para ver más modificadores

El management studio ssms lo graba como .sqlplan

Elementos del plan de ejecución

Table scan: va a leer los datos del heap (los datos en si, es decir, todas las páginas de la tabla) Cluster index scan: lo mismo de antes pero usando el índice ordenado Index seek: busco un determinado elemento en el índice Nested loops: para los join, los inner. PUede ser usado para los left inner join, left semi o¡join and left anti semi join

RID Loockup: busca en el heap usando el row ID Key Loockup: Merge Join: Recoger info y la junta en una Hash Match: Para hacer join pero con un hash. En este caso esa clave de cada elmento es parte de los datos y apunta a un dato numerico que apunta al dato de

la tabla. En dos tablas que hay recorrer de forma secuencial por cada elemento de la primera... como en la segunda tabla vamos a recorrer muchas veces se

hace este hash y así la segunda pasada ya vamos a los elementos que nos interesa funcionas agregadas: filter: bajo coste operacion sort:

Instrucciones de modificacion de datos

Insert: Update: Delete: t-sql merge puede ser usado en combinaciones de insert update y deletes.

trabajando con planes de ejecucion

Se pueden capturar con el management studio o bien con el profiler.

Otra forma es consultar las vistas dmv's, con el activity monitor o con el sql server data collection.

Vistas y funciones relativas a los planes

sys.dm_exec_...

Se puede hacer limpieza del cache de planes usando dbcc freeproccache

Indices non-cluster

Mantiene orden de las filas de datos pero sin integrarse con las páginas de datos, empieza a partir del index_id 2 y puede haber tantos como se quiera. Son

árboles balanceados. Mejoran el tiempo de respuesta en consultas. Al final del arbol hay un puntero que apunta a la página del dato.

Si la tabla tiene un índice cluster el puntero no apunta al row id sinó al índice del cluster.

como obtener información de los índices

Con el management studio Con los informes. Con los catálogos de vistas (sys.index) Procedmimentos almacenados

Como crear indices

Create index (se puede poner de forma opcional create noncluster index)

INCLUDE

Con esta clausula almacenamos datos de una columna (que se va a usar a menudo) con el propio índice.Requiere tener bien identificado cual es el uso del

índice.

Los indices se pueden deshabilitar

Indices filtrados

Indices de una tabla pero no de todos sus valores.

usando el tuning advisor

con el profiler podemos capturar datos y con el tuning advisor ver si nos hacen falta indices o si nos sobran.

Procedimientos almacenados

Son procesos con parámetros o no de entrada. Los parámetros pueden ser pasandoselo a través de variables. Puede

devolver o no un valor. Para recuperar ese valor se ejecuta con

exec @variable=<proc>

Si devuelve más valores podemos usar tambien parametros de tipo output

xec @variable=<proc> @v2 output,@v3 output, ...

puede devolver tambien un conjunto de filas.

Si en el procedimiento alamacenado hay muchas select devuelve un conjutno de datos equivalente a todas las selects.

Beneficios

• puede mejorar la seguirdad de una aplicación
• permite la programación modular ¿?
• permite enlazar con databases que todaviá no existen y evita la necesidad de ordenar en creación de objetos
• Mejora del rendimiento, ya que con una sola sentencia se pueden ejeuctar muchas y se puede usar como reutilización

de código (ahorro de trabajo)

procedimientos de sistema

los que vienen con sql server. También hay los procedmiemtnos almacenados de sistema extendidos, que no tienen

porqué estar codificados en transactsql pueden estar enlazados con ddl. Estos últimos son los que empiezan por xp_.

Los normales empiezan por sp_. Se recomienda que los de usuario empiecen por usp_

Hay instrucciones que no están permitias en los procedimientos, sobre todo las de ddl (definicion de datos)

Los de sistema están definidos en la db de master, aunque los veamos en todos las db.

Creacion de procedimientos

CREATE PROCEDURES <schema>.<nombre> AS <CODIGO>

Ese es forma antigua, en la nueva hay un bloque BEGIN / END

Para modificar se cambia el CREATE por el ALTER y se ha de cambiar entero.

Dependencias

Hay alguna vista que nos dice las dependencias de los procedmimentos almacenados

sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities

Recomendaciones

• en el código poner siempre las referencias a server.db.esquema.objeto
• mantener un set options consistentes, es decir, que si hay que usar un set cuando termine el procedimiento poner

loque hemos modificado como estaba.

• nomenglatura correcta usp_
• se puede anidar procedimientos hasta un nivel de 32. Dificilmente se puede usar recursividad...
• Usar un procedimiento por tarea...

ofuscacion

with encryption

no está recomendado.

Si hago alter tengo que volver a poner with encryption

parametrización

Hay parametros de entrada y de salida y después la respuesta del procedimiento

Cuando se define un parámetro es obligatorio decir de que tipo de valor es ese parámetro, tanto en la defniicion

como cuando indicamos en el exec la varaible en la que recuperamos el dato

Planes de ejecución de los procedimientos

...

Controlando ejecución de contexto

Execute as clause

Para que un usuario pueda ejecutar un proc tendrá que tener permisos sobre el proc y sobre las tablas de las que

haga uso.

Pero se puede poner la calusula with execute as 'usuario', para que cuando un proc vaya a coger los datos de una

tabla cambie el contexto del usaurio que hace la consulta.

de esta forma permite impersonalización,

Se puede poner con CALLER, SELF, OWNER o un usuario en concreto

Usando el Merge

Se implmenta con el tipo de dato tabla, y se puede usar el tipo de dato talba para pasar como parámetro a un proc.

Combina varias operaciones dentro de un mismo comando.

merge into <tabla> as e using <tabla> as eu on <columna_t1>=<columna_t2> WHEN matched... when not matched

Usuando el output

es como un pipe para sacar por consola. En el output se puede usar la variable $action que devolverá el valor de la

acción que ha sido ejecutada (inserccion, borrado o actualizacion). Para mostrar mensajes se utilizan tablas

virtuales inserted y deleted, que se genera durnate el merge. Si hay una actualización de una fila de la tabla sobre

la que se está hacienod merge los datos anteriores irán a deleted y los nuevos a inserted

ejemplo

OUTPUT $action,inserted.<columna>,deleted.<columna>

determinismo y rendimiento

Lo bueno de merge es que ejecutando este comando puedes hacer inserts, updates, y deletes en un solo comando y un

solo plan de ejecución, en vez de hacer varios planes para cada instrucción.

COALESCE: funcion para mezclar strings por si uno es null

Implementeando tipos de dato tabla

Es como declarar una tabla pero que no va a estar de forma persistente, estará en la tempdb.

Para declarar una variable usamos:

DECLARE @<nombre> <tipo>

En el caso de tipo tabla sería del estilo:

DECLARE @ejemplo TABLE(id int, nombre varchar, etc)

Podemos crear un tipo de dato nuevo que sea de tipo table:

CREATE TYPE dbo.ejemplo AS TABLE (col1 int, col2 varchar, etc)

Y luego podemos definir variables de ese tipo

DECLARO @var dbo.ejemplo;

Para asignarle valores se hace como si fuera una tabla pero usando la variable:

INSERT INTO @var VALUES (val1, "val2", ...);

Usar tipo de datos table como parámetro

Se puede usar para pasar datos a procedimientos, pero como solo lectura, no se puede usar como parametro de salida,

y además tenemos que ponerle READONLY en la definición de la variable del procedimiento.

Todas las variables de un procedmimento se pasan como copia, pero en el caso de variables de tipo tabla se pasa como

referencia, y el procedmimento no puedoe modificar los datos de esa tabla que le pasamos por referencia.

Transacciones

Transacciones auto-commit

es un parametro de inicio de sesion. si una operación sucede bien se hace el commmit automáticamente, y si hace

rollback.

con set xact_abort ON;

convierte las terminación de instruciones error en batch...?¿?¿?¿

transacciones explicitas

cuando se termina una transacción siempre empieza otra, eso sería una transaccion implícita, pero nosotros podemos

decir cuando empieza una transacción nueva

BEGIN TRASACTION

A las transacciones se le puede asgnar un nombre para referirnos a ellas en un futuro, como por ejemplo restaurar un

backup hasta esa transacción usando WITH MARK

Se pueden guadar también las transacciones ?¿?¿

Transaccines implicitas

Empieza cuando uno de las siguientes comandos es ejecutado y el comando no es parte de una transaccion

ALTER TABLE CREATE DELETE (muchas más)

Recuperación de transaccines

(ver gráfico de vida de las transaccoines)

RECOMENDACIÓN
Los ficheros de log de transacción deberían estar en otros discos que no fueran los de datos y más rápidos

Las transacciones se van guardando en memoria en y en log de transacciones (por eso es importantisimo tener disco

rápìdo).

En el momento de checkpoint es cuando se guarda el dato en mdf o ndf

recomendaciones

Una transacción debe ser breve. y no debe tener interactividad con usuarios y tiempos de espera Intentar acceder a recursos en el mismo orden, para evitar interbloqueos. La transacción es única... no hay transacciones dentro de transaaciones... cuando haces commit se cierran todas las

transaccion que se hubieran abierto en la misma sessión. Igual con el rollback

Se puede usar @@trancount.¿?

metodos de concurrencia

Pesismista y optmista

locks

Son objetos para controlar acceso a recurso. Antes de acceder al recurso se mira si existe un objet de bloqueo para

ese recurso.

Sirven para garantizar la integridad de la base de datos

Hay de dos tipos... lectura (permite leer pero no escribir) y escritura (no permito leer y ni escribir)

Locking: se impide acceder al dato blocking: bloquear el acceso a un dato

Estos bloqueos evitan:

• el que escribe el último sobreescribe lo que haya hecho alguien en medio
• leer datos que todavía no están validados (están siendo modificados pero todavía no ha sido cambiado)
• perdias de actuailzacones, analisis incosistentes. entre que leo un valór y lo modifico ha podido cambiar.

(isloations levels, avisa de si ha sido modificado mientras lo modificamos nosotros)

• Lecturas fantasmas. Cuando lei estaban pero después ya no están.

REcursos que se pueden bloquear

filas paginas extent heap completo hoBT tabla (con todos sus indices) fichero (de base dde datos) aplicación metadata allocation_unit (buffers) database

tipos de bloqueos

compartidos (permite leer pero no escrbiri) update (cerrojo... impide hacer actualizaciones) Exclusive (impide que no se haga nada) Intent (bloqueos en la jeraquia) Schema (estructura de objetos) Bulk update (para hacer insercciones masivas... bloquea la tabla para hacer este tipo de inserts) Key-range (bloqueo de conjuntos de filas)

compatibilidad

shared incompabitle con exclusive update incompatible con todos menos con share exclusive incompatible con todos los demás

latch estructura para acceder a los bloqueos.

gestión de bloqueos

Ante un bloqueo se espera. se puede definir el tiempo de bloqueo

set lock_timeout x;

escalado de locks

si hay muchos bloqueos para muchas filas de una tabla el sql server puede decir bloquear a nivel de páginas. Sube de

nivel según los niveles definidos antes. Si hay muchas páginas intentaría bloquar extents, etc

DEadlocks

interbloqueos. Acceden a dos recursos en orden inverso y uno bloquea a otro.

hints para las insercciones para provocar bloqueos

HOLDLOCK mantiene bloqueos compartidos hasta que termina la transaccion NOLOCK sin bloqueo PAGLOCK ROWLOCK TABLOCK TABLOCKX exclusivo para tabla UPDLOCK bloquea para hacer actualizacion y se mantiene bloqueado hasta final de transaccion XLOCK los bloqueos que hagan falta se harán de forma exclusiva

consulta de bloqueos

vistas dinámicas profiler

niveles de aislamiento

isolation levels

(ver tabla en docu)

alter database <nombre> set allow_snapshot_isolation on;

Para que, sin cambiar la aplicacion, funcione el tema de snapshot isloation level se puede usar

alter database <nombre> set read_committed_snapshto On;

hints para estas cosas CONSTULAR!!!

Gestion de errores de t-sql

implemmentando gestion de errores

lanzar error

RAISERROR se puede poner un string de error o añadir un mensaje de error ¿?¿?

revisando si ha habido error)

con @@ERROR nos dice el código de error de la última instrucción. cero si ha ido bien

IF @@ERROR <> 0 PRINT 'ERROR=... ...

Errores y transacciones

para poder controlar los errores usamos set xact_abort off solo la instruccion hace rollback y no la transacción

completa.

@@TRANCOUNT se puede usar para testear el nivel de transacción.

Errores personalizados

sp_addmessage codigo (a partir del 50000) , severidad , "mensaje"

para lanzar error

con esto se puede crear alertas personalizadas tambien. Se puede hacer que se dispare un evento cuando salte tal

código de error. Para poder hacer esto hay uque hacer el raiserror with log y generar una alerta.

try catch

BEGIN TRY END TRY BEGIN CATCH END CATCH

pueden anidarse

hay funciones que dan información del error dentro del catch

Hay errores que no pueden ser controldos por try catch, como los errores de compilación o recompilación o los

errores que están fuera del begin try end try

codigo administrado .net

try/catch/finally

En el finally se pone código que es común para el try y para el catch, al que que hay que ejeuctar tanto si hay

error como si no. Esto es de .net

Los errores de .net siempre van con el numero 6522. PUeden llevar excepciones anidadas (como java)

funciones

tipos

• del sistema, para calculos de fechas, conversiones, etc...
• escalares (devuelven un único valor, deterministas o no deterministas (get day))
• funciones que devuelven tablas (tienen un conjunto de instrucciones y al final calculan los datos y los devuelven

en forma de tabla)

• inline vs multi statament functions

por normalizar se suele añadir udf_ delante del nombre de la función

inline va a hacer una select y va a devolver una serie de campos que no hace falta definier en la funcion. las multi-statament define la tabla que va a devolver

Algo pareceo al join es el cross apply que es como el join pero busca de forma automatica los campos de cruce

impactos de rendimiento en funciones escalares

Si se pone una funcion para un campo se ejecutará tantas veces como filas tenga esa select si se pone en la clausula where ocurrirá lo mismo... por cada fila se ejecutará la funcion en el from solo se ejecuta una vez

Recomendación de calcular la funcion a una varaible si es posible antes de poner en where.

con el cross apply intentar limitar el numero de resultados

control de ejecuccion

Es igual que en procedures

consejos de uso de funciones

los mismos que para procedures

no admiten try-catch

alternativas a las funciones

REVISAR EN DOCUMENTACION

Integridad mediante constraints

tipos de integridad

Dominio: por columna (tipo de dato) Entidad: por fila, controla que los valores dentro de una fila sea coherente Referencial: Cohencia entre diferentes tablas

Se puede forzar la intigridad con: tipos de datos si se puede hacer null que siempre tiene que haber un valor (dafault values) constraints (forzar a cumplir ciertas reglas) triggers (codigo que se ejeucta de forma automática con ciertos eventos)

Triggers

(se fue la luz, así que no nengo apuntes) ver docu

...

triggers instead of

Se ejecuta en lugar de la operación, no se ejecuta la operación.

Un ejemplo, que cuando se haga un delete en realidad se ejecute un update que marque una columna de fecha de baja o

bit de baja.

triggers anidados

Cuando un trigger modifica una tabla puede disparar otro trigger de otra tabla. No se permiten más de 32 niveles.

Para los triggers de tipo update existe una funcion update() que pasando el nombre de un campo nos dice si ese campo 

ha sido modificado. Devuelve true o false

Orden de ejeucción de los triggers

sp_settriggerorder es una funcion para esto

Alternativas a los triggers

Para lógica de negocio mejor usar check constraint Usar datos por defecto en vez de triggers para esto Usar integridad referencial para controlar Usar columnas calculadas y persistentes en vez de triggers que van modificando datos.

Managed code

sql server 2005 en adelante incorpora un módulo de ejeucción de .net. Este código se ejecuta en una "máquina

virtual" (run-time environment) CLR de .net. Antes de podían ejeuctar código nativo en .dll que son más rápidos,

pero más inseguros, ya que un error en ese código podía tirar el sql server.

CLS es el common language especification (se puede programar en varios lenguajes)

Para que el código administrado

Mayor nivle de abstracción para solución de problemas. Sobre todo para problemas que no implican datos.

Se pueden crear:

• funciones definidas por el usuario
• procedmimientos almacenados
• triggers

También permite crear nuevos tipos de objetos

(en docu hay tabla de cosas que es mejor en t-sql o en managed code)

Assembly

El resultado de la compilación de un programa .net. Pueden ser exe o dll. Esto se integra en sql server y se le

especifica a ese assambly que tipo de operaciones puede realizar.

(ver tabla de assambly permission sets)

SAFE, EXTERNAL_ACCESS Y UNSAFE

Normalmente el safe, pero si hay que acceder porejemplo a ficheros external_access

Para añadir nuestro assambly se usa:

CREATE ASSEMBLY <nombre FROM "C:\....dll" WITH PERMISSION_SET = SAFE;

Cuando se crea un objeto, por defecto, está deshabiltado (ya que puede ser un agujero de seguridad). En el menú del

botón secundario del raton en el servidor, hay la opicíon facets y tenemos que entrar en el surface configuration.

Otra forma es con el sp_configure "clr enabled",1

funciones y procedimientos externas

Una vez que tenemos el assambly podemos crear la funcion referenciando a ese assambly

CREATE FUNCTION <nombre> (parametros) RETURNS variable AS EXTERNAL NAME <nombre de la clase>

Con procedimientos almacenados es muy parecido.

Creacion de funciones agregadas

CREATE AGREGATE <nombre> (@variables tipo) RETURNS <tipo de datos> EXTERNAL NAME <nombre assembly>

creación de tipos

Muy parecido a lo anterior no son indexables no se pueden comparar datos de tipo definidos por usuario... habría que implementar una función

XML

(explicacion del formato xml, mejor buscar información por ahí)

En sql server hay un tipo de dato xml, donde almacenaremos una estructura completa de xml. Al ser texto plano es fa

´cil hacer conversión de este tipo de dato a string.

Hay una colección de esquemas.

Cuando insertamos un documento xml en un campo de tipo xml no hace falta que ese xml se valide con un xsd. A no ser

que cuando definamos el campo de tipo xml le asociemos a un esquema.

Esto sería que cuando definimos un campo de tipo xml entre paréntesis le decimos el schema

create table <nombre> (id int,

datos xml(<esquema db>.<esquema en la colección de xsd>)

);

Además, si añadimos ehí las palabras reservadas DOCUMENT o CONTENT para decir que en ese campo va un xml completo o

parte de un xml, respectivamente.

datos xml(DOCUMENT <esquema db>.<esquema xml>)

indices para xml

Indices especiales para campos xml. Se basa en un indice primario y en un máximo de tres indices secundarios que

basan la indexación en etiquetas xml o en xpath (path dentro del xml) o en valores

Transformando querys en xml

Podemos convertir resultados de querys directamente a xml añadiendo a nuestra query FOR XML ... esto nos devuelve un

xml directamente.

Si ponemos FOR XML RAW nos aparecerán cada fila con una etiqueta y todos los campos como propiedades si ponemos FOR XML RAW, ELEMENTS nos aparecerá en formato de elementos en jerarquia

Se puede mezclar con una operación compleja usando tag en las columnas de la query. Esto se puede simplificar usando

FOR XML PATH

También hay otro tipo TYPE

Otra opción es usar una hoja de transformación, usando hojas de estilo.

como modificar documentos xml dentor de un campo de la db

xquery es un lenguaje para identificar nodos en xml y puero hacer consultas estructurass o semi extructuradas en los

xml. Sus expresiones se llaman FLWOR

ejemplo /InvoceList/Invoice[@InvoiceNo=1000]

Un ejemplo de como haríamos una query en sql

SELECT XmlEvent.query( '<EventSPIDs> { for $e in /EVENT_INSTANCE return <SPID> {number($e/SPID[1])} </SPID> } </EventSPID>') FROM dbo.DatabaseLog;

XmlEvent es el campo de la tabla DatabaseLog y le lanzamos el metodo query.

Hay ejemplos de modficiacones de xml, añadiendo nuevas etiquetas

shredding XML data

La operación de meter los campos de un xml y meterlos en una estructura de tabla

Hay que seguir varios pasos:

recibir el xml del cliente crear una representación en árbol interna para usar xp_xml_preparedocument Abrir docuemtno con OPENXML para recoger el rowset (...) Borro el documento de entrada.

El Openxml lo podemos usar con un select

select * from openxml(@idoc, '/Customer/Order/OrderDetail',1) WITH ( CustomerId int (...) ver libro

Campos de tipo espacial (GPS)

TIPOS DE DATOS ESAPACIALES

vector (series de segementos lineales) raster (pequeñas areas)

Planar system = flat earth Geodetic systems (gps) = con curvatura