SQL SERVER 2014: ALTER INDEX

-- =========================================================================================

-- AUTOR: MANUEL OMAR OLGUÍN HERNÁNDEZ

-- FECHA 15-JUL-2013

-- DESCRIPCIÓN REORGANIZA O RECONSTRUYE LOS ÍNDICES DE LA BASES DE DATOS QUE CONTENGAN FRAGMENTACIÓN SOBRE SUS TABLAS ENTRE

-- 5 - 30 % REORGANIZE

-- 31 - 100% REBUILD

-- NOTA: EN LA BASE SELECCIONADA SEGÚN EL CONTEXTO

-- =========================================================================================

USE DATABASENAME;

SELECT I.NAME AS INDICE , T.NAME AS TABLENAME, S.NAME AS SCHEMANAME, AVG_FRAGMENTATION_IN_PERCENT

,IND.INDEX_TYPE_DESC

,IND.DATABASE_ID

,IND.INDEX_ID

,DB.NAME AS DATABASE_NAME

,ID = ROW_NUMBER () OVER (ORDER BY I.NAME, IND.INDEX_ID)

INTO #INDICES

FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (NULL,null,NULL,NULL,NULL)AS IND

INNER JOIN SYS.INDEXES AS I ON IND.OBJECT_ID = I.OBJECT_ID

INNER JOIN SYS.TABLES AS T ON IND.OBJECT_ID = T.OBJECT_ID

INNER JOIN SYS.SCHEMAS AS S ON T.SCHEMA_ID = S.SCHEMA_ID

INNER JOIN SYS.DATABASES AS DB ON IND.DATABASE_ID = DB.DATABASE_ID

WHERE IND.INDEX_ID > 0 AND I.NAME IS NOT NULL

AND IND.AVG_FRAGMENTATION_IN_PERCENT BETWEEN 5 AND 100

-- SELECT * FROM #INDICES order by ID

DECLARE @ID INT

DECLARE @INDICE NVARCHAR(500)

DECLARE @INDEX_ID INT

DECLARE @SCHEMANAME VARCHAR(200);

DECLARE @TABLENAME VARCHAR(200);

DECLARE @STRSQL NVARCHAR(500);

DECLARE @DESCRIPTION NVARCHAR (500);

DECLARE @INDEX_TYPE NVARCHAR (50);

DECLARE @INSTRUCCION NVARCHAR(150);

DECLARE @FRAGMENTATION DECIMAL (8,2)

SET @ID =0

WHILE (@ID IS NOT NULL)

BEGIN

SET @SCHEMANAME = (SELECT TOP 1 SCHEMANAME FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

SET @TABLENAME = (SELECT TOP 1 TABLENAME FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

SET @INDICE = (SELECT TOP 1 INDICE FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

SET @DESCRIPTION = (SELECT TOP 1 AVG_FRAGMENTATION_IN_PERCENT FROM #INDICES WHERE INDICE > @INDICE ORDER BY INDICE )

IF @ID IS NOT NULL

BEGIN

SELECT TOP 1

@SCHEMANAME = SCHEMANAME,

@TABLENAME = TABLENAME,

@INDICE = INDICE,

@FRAGMENTATION = AVG_FRAGMENTATION_IN_PERCENT,

@INDEX_TYPE = INDEX_TYPE_DESC,

@INDEX_ID = INDEX_ID

FROM #INDICES

WHERE ID > @ID

ORDER BY ID

SET @ID = (SELECT TOP 1 ID FROM #INDICES WHERE ID > @ID ORDER BY ID )

IF @FRAGMENTATION <= 30 SET @INSTRUCCION = 'REORGANIZE';

--IF @FRAGMENTATION > 30 SET @INSTRUCCION = 'REBUILD';

IF @FRAGMENTATION > 30 SET @INSTRUCCION = 'REBUILD PARTITION = ALL WITH (FILLFACTOR = 85)';

SET @STRSQL = ('ALTER INDEX ' + @INDICE + ' ON ' + @SCHEMANAME + '.' + @TABLENAME + ' ' + @INSTRUCCION)

SET @DESCRIPTION = 'ID:' + CONVERT(VARCHAR, @ID) + ' INDEX:' + CONVERT(VARCHAR,@INDEX_ID) + ' ' + @STRSQL + ' - ' + @INDEX_TYPE + ' AVG FRAGMENTATION:' + CONVERT(VARCHAR,@FRAGMENTATION);

RAISERROR (@DESCRIPTION,0,1) WITH NOWAIT

EXECUTE SP_EXECUTESQL @STRSQL

END

SELECT I.NAME AS INDICE , T.NAME AS TABLENAME, S.NAME AS SCHEMANAME, AVG_FRAGMENTATION_IN_PERCENT

,IND.INDEX_TYPE_DESC

,IND.DATABASE_ID

,IND.INDEX_ID

,DB.NAME AS DATABASE_NAME

,ID = ROW_NUMBER () OVER (ORDER BY I.NAME, IND.INDEX_ID)

INTO #INDICES_REBUILD

FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (NULL,null,NULL,NULL,NULL)AS IND

INNER JOIN SYS.INDEXES AS I ON IND.OBJECT_ID = I.OBJECT_ID

INNER JOIN SYS.TABLES AS T ON IND.OBJECT_ID = T.OBJECT_ID

INNER JOIN SYS.SCHEMAS AS S ON T.SCHEMA_ID = S.SCHEMA_ID

INNER JOIN SYS.DATABASES AS DB ON IND.DATABASE_ID = DB.DATABASE_ID

WHERE IND.INDEX_ID > 0 AND I.NAME IS NOT NULL

AND IND.AVG_FRAGMENTATION_IN_PERCENT BETWEEN 0 AND 100

SELECT A.Indice, A.SchemaName, A.TableName

,OLD_FRAGMENTATION = CONVERT(DECIMAL(5,2),(A.AVG_FRAGMENTATION_IN_PERCENT))

,NEW_FRAGMENTATION = CONVERT(DECIMAL(5,2),(B.AVG_FRAGMENTATION_IN_PERCENT))

,Fragmentation = CONVERT(VARCHAR(10), CONVERT(DECIMAL(5,2), (A.AVG_FRAGMENTATION_IN_PERCENT) - (B.AVG_FRAGMENTATION_IN_PERCENT))) + '%'

,A.INDEX_ID

,A.INDEX_TYPE_DESC AS INDEX_TYPE

,A.DATABASE_NAME

FROM #INDICES AS A

LEFT JOIN #INDICES_REBUILD AS B ON A.TABLENAME = B.TABLENAME AND A.SCHEMANAME = B.SCHEMANAME AND A.DATABASE_ID = B.DATABASE_ID AND A.INDEX_ID = B.INDEX_ID AND A.INDICE = B.INDICE

ORDER BY A.SCHEMANAME, A.TABLENAME, A.INDICE, A.INDEX_ID

DROP TABLE #INDICES;

DROP TABLE #INDICES_REBUILD

/* To rebuild all indexes in a table changing the fillfactor (cuando se hace el rebuild conviene ajustar el fill factor de nuevo, por si ya está lleno)*/

--ALTER INDEX ALL ON Avispa.Brokers

--REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);

XML to TABLE

A continuación te mostraré como leer una cadena XMLdesde SQL Server para convertirlo en un formato tipo tabla.
Iniciamos con un XML sencillo:

</cliente>

Para leer el xml que muestro en la parte superior se hace lo siguiente:

SELECT

t.c.value('text()[1]','int') as IdCliente

from @xmlCliente.nodes('//id') as t(c)

@xmlCliente es la variable que contiene la cadena del XML.

Observa que 'text()[1]' es utilizado para leer el valor de los elementos de este XML

En este ejemplo se está accediendo directamente a los nodos <id> => '//id'

Ejemplo completo:

El xml en cadena se asigna a una variable de tipo XML:

DECLARE @xmlCliente as xml

set @xmlcliente =

'<cliente>

</cliente>';

SELECT t.c.value('text()[1]','int') as IdCliente

from @xmlCliente.nodes('//id') as t(c)

= >

EJEMPLO 2

Ahora bien, si dentro de tu XML tienes atributos y deseas leerlos,

se hace de la siguiente manera:

1.- Cuando el Atributo esta en el elemento padre, entonces debemos subir al nodo un nivel,

ya que el ejemplo anterior se colocó directamente en el nodo <Id>.

</cliente>

Se selecciona el nodo del elemento principal <cliente>

con un alias nombre tabla y nombre columna

y de ese mismo nodo principal, ahora en lugar de seleccionar el contenido

del elemento (con 'text()') ahora seleccionamos el valor del atributo 'name'

colocando una @ para atributos.

SELECT

t.c.value('@name[1]','varchar(20)') as Nombre

,t2.c2.value('text()[1]','int') as Id

from @xmlCliente.nodes('//cliente') as t(c)

cross apply t.c.nodes('id') as t2(c2)

Para acceder al valor de los elementos, se debe obtener del nodo principal

t.c.nodes, el cual realizamos un cross apply...

EJEMPLO COMPLETO:

DECLARE @xmlCliente as xml

SET @xmlcliente =

</cliente>

SELECT

t.c.value('@name[1]','varchar(20)') as Nombre

,t2.c2.value('text()[1]','int') as Id

from @xmlCliente.nodes('//cliente') as t(c)

cross apply t.c.nodes('id') as t2(c2)

2.- Cuando los elementos secundarios contiene atributos:

</cliente>

Solo resta agregar la columna como atributo '@' ya que esos

elementos se tienen en t2(c2)

,t2.c2.value('@value[1]','varchar(1)') as value

EJEMPLO COMPLETO:

DECLARE @xmlCliente as xml

set @xmlcliente =

</cliente>

SELECT

t.c.value('@name[1]','varchar(20)') as Nombre

,t2.c2.value('text()[1]','int') as Id

,t2.c2.value('@value[1]','varchar(1)') as value

from @xmlCliente.nodes('//cliente') as t(c)

cross apply t.c.nodes('id') as t2(c2)

Entonces concluimos que el contenido

de un elemento se obtiene con 'text()[1]'

y el valor de un atributo con el prefijo '@'

XML con namespaces

DECLARE @xmlCliente as xml

SET @xmlcliente =

<ns:cliente name="Juanito Perez" xmlns:ns="http://google.com" >

<ns:id>57</ns:id>

<ns:id>58</ns:id>

<ns:id>59</ns:id>

</ns:cliente>

WITH XMLNAMESPACES ( 'http://google.com' as "ns" )

SELECT

t.c.value('@name[1]','varchar(20)') as Nombre

,t2.c2.value('text()[1]','int') as Id

from @xmlCliente.nodes('//ns:cliente') as t(c)

cross apply t.c.nodes('ns:id') as t2(c2)

;WITH XMLNAMESPACES
  (
'http://tempuri.org/basketDayArchives.xsd' AS ns
)
SELECT
y.value (N'@val [1]', N'varchar(100)')     AS Valor
,y.value (N'@data [1]', N'varchar(100)')     AS Fecha
,y.value (N'text() [1]', N'varchar(100)')     AS valorElemento

FROM @XML.nodes(N'//ns:Basket') t(c)
OUTER APPLY c.nodes ('ns:BasketList') as r(y)

select @XML

SQL SERVER 2014

martes, 29 de marzo de 2016

REORGANIZE OR REBUILD INDEX

jueves, 1 de octubre de 2015

XML A TABLA

XML to TABLE

EJEMPLO 2