Diferencia entre revisiones de «Curso SQL Server Implementing»

De Ardemans Wiki
Saltar a: navegación, buscar
(Página creada con '==Configuracion de red== ===Protocolos=== TDS Tabular data stream, devuelve los datos tabulados SQL Native Access client SNAC (quitando muchas capas de configuracion) TCP/IP,...')
 
Línea 282: Línea 282:
 
ALTER <OBJETO> <NOMBRE> TRANSFER <nuevo_esquema>;
 
ALTER <OBJETO> <NOMBRE> TRANSFER <nuevo_esquema>;
  
−
== Creando tablas ==
+
== Tablas ==
 +
 
 +
=== Crear tablas===
 
CREATE TABLE <nombre>
 
CREATE TABLE <nombre>
 
(<col> ...
 
(<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.
 +
 +
=== asc o desc===
 +
 +
 +
=== 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.

Revisión de 14:10 20 mar 2012

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 Error and usage reporting

Powershell provider

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.

asc o desc

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.

Obtenido de «https://wiki.ardemans.com/index.php?title=Curso_SQL_Server_Implementing&oldid=138»