26.4.13

Making your f****** life easier with TSQL SQLXML and XQUERY on SQLServer

As you probably know, SQL Server allows XML data storage and manipulation.

SQL Server offers a dedicated data type named XML that can be used to store xml documents or fragments of xml, also you can register XML Schemas with SQL Server, and store schema information within the database in order to be able to do an automatic validation of xml documents when a schema is present; and an automatic crushing of the xml data for and efficient querying and updating of the content.

To provide this querying and update facility over XML, SQL Server offers you an implementation of a subset of the W3C XQuery language and XML-DML.

These features makes your life easier. At least mine! xD

In this sample we will be using : EXIST VALUE QUERY
over



THE SAMPLE DATA:



/*******************************************
 * THE SAMPLE DATA, WITH AN ON MEMORY TABLE CALLED @SAMPLE_TABLE
 *******************************************/
DECLARE @SAMPLE1     XML,
        @SAMPLE2     XML

SET @SAMPLE1 =  '< list >  
                 < description id="1">description 1 in sample 1< /description>
                 < description id="2">description 2 in sample 1< /description>
                 < description id="3">description 3 in sample 1< /description>
                 < description id="5">description 5 in sample 5< /description>
               < /list >'

SET @SAMPLE2 = '< list >
                 < description id="5">description 5 in sample 2< /description>
                 < description id="4">description 4 in sample 2< /description>
                 < description id="1">description 1 in sample 2< /description>
               < /list >'
               
DECLARE @SAMPLE_TABLE TABLE([XML] XML)

INSERT INTO @SAMPLE_TABLE ([XML])VALUES (@SAMPLE1)
INSERT INTO @SAMPLE_TABLE ([XML])VALUES (@SAMPLE2)


Here is what you was looking for:

/*******************************************
 * STARTING HERE IS WHERE THE MAGIC HAPPENS
 *******************************************/
--filter this ID
DECLARE @ID INT
SET @ID = 5

SELECT (
           [xml].value(
               '(/list/description[@id= sql:variable("@ID")])[1]',
               'VARCHAR(MAX)'
           )
       ) AS ELEMENT_TEXT
FROM   @SAMPLE_TABLE
WHERE  [xml].exist('(/list/description[@id= sql:variable("@ID")])[1]') = 1

SELECT 
       ([xml].query('/list/description[@id=sql:variable("@ID")]')) AS 
       ELMENT_FRAGMENT
FROM   @SAMPLE_TABLE
WHERE  [xml].exist('(/list/description[@id= sql:variable("@ID")])[1]') = 1


Thats all. Of course, this is just the top of the iceberg xD, if you want more, remember that google is your friend :p

24.4.13

SQLSERVER, TSQL REBUILD, REORGANIZE ONE INDEX AT A TIME

No matter how good our database design is, we will always need to perform some maintenance tasks to existing indexes.

So, here is a simple script to rebuild, reorganize one index at a time, just one index will be processed on every excecution, starting with thouse indexes with higher fragmentation value.

Is this, just a table(MANTENIMIENTO_INDICES) for processed indexes, and a stored procedure for doing the job.

Once the stored procedure runs, will take the most fragmented indexed and depending on the current value will rebuild or reorganize the index. After that, will insert the processed index at MANTENIMIENTO_INDICES table, next time you run SP_INDEX_REBUILD_TOP, will take a new index, one that does not exist in MANTENIMIENTO_INDICES. When no new index available for defragmentation, MANTENIMIENTO_INDICES will be truncated, to start all over again. 

Remenber, just one index at a time, in my case, i run it every 8 minutes at nigth for about 4 hours.
8 minutes because, the biggest table (more than 60M rows) took that time to defrag.

/*************************************************************
* This table will store those indexed, used because some index will not be indexed 
* and the process will try the same everytime, so, once processed will be included in this table, 
* the process will proces those that are not present in this table
 ************************************************************/

 CREATE TABLE [dbo].MANTENIMIENTO_INDICES(
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [TABLE] VARCHAR(MAX), 
 [NAME] VARCHAR(MAX),
 [AVG] FLOAT
 CONSTRAINT [PK_MANTENIMIENTO_INDICES] PRIMARY KEY CLUSTERED 
 (
 [ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
 ) ON [PRIMARY]
 GO





/*************************************************************
* Just create a scheduled task than run EXEC SP_INDEX_REBUILD_TOP every X minutes at night ?
************************************************************/

CREATE PROCEDURE SP_INDEX_REBUILD_TOP
AS
BEGIN
 SET NOCOUNT ON
 
 DECLARE @TEMP_INDEX TABLE(
             database_id SMALLINT,
             [OBJECT_ID] INT,
             index_id INT,
             NAME VARCHAR(MAX),
             avg_fragmentation_in_percent FLOAT
         )
 
 DECLARE @db_id SMALLINT;
 SET @db_id = DB_ID(N'DATABASE_NAME_GOES_HERE');
 
 INSERT INTO @TEMP_INDEX
   (
     database_id,
     [OBJECT_ID],
     index_id,
     NAME,
     avg_fragmentation_in_percent
   )
 SELECT ps.database_id,
        ps.OBJECT_ID,
        ps.index_id,
        b.name,
        ps.avg_fragmentation_in_percent
 FROM   sys.dm_db_index_physical_stats (@db_id, NULL, NULL, NULL, NULL) AS ps
        INNER JOIN sys.indexes AS b
             ON  ps.OBJECT_ID = b.OBJECT_ID
             AND ps.index_id = b.index_id
 WHERE  ps.database_id = @db_id
        AND PS.avg_fragmentation_in_percent > 5
 ORDER BY
        ps.avg_fragmentation_in_percent DESC
 
 DECLARE @cmd NVARCHAR(MAX) 
 DECLARE @TABLE VARCHAR(MAX) 
 DECLARE @NAME VARCHAR(MAX)
 DECLARE @AVG FLOAT
 DECLARE @COUNT INT
 
 SELECT @COUNT = COUNT(1)
 FROM   sys.objects AS o
        JOIN @TEMP_INDEX TE
             ON  TE.OBJECT_ID = o.object_id
 WHERE  TE.avg_fragmentation_in_percent > 5
        AND TE.NAME IS NOT NULL
        AND TE.NAME NOT IN (SELECT [NAME]
                            FROM   MANTENIMIENTO_INDICES(NOLOCK))
 
 
 IF @COUNT = 0
 BEGIN
     TRUNCATE TABLE MANTENIMIENTO_INDICES
 END       
 
 SELECT TOP(1) @TABLE = QUOTENAME(o.name),
        @NAME     = TE.NAME,
        @AVG      = te.avg_fragmentation_in_percent
 FROM   sys.objects AS o
        JOIN @TEMP_INDEX TE
             ON  TE.OBJECT_ID = o.object_id
 WHERE  TE.avg_fragmentation_in_percent > 5
        AND TE.NAME IS NOT NULL
        AND TE.NAME NOT IN (SELECT [NAME]
                            FROM   MANTENIMIENTO_INDICES(NOLOCK))
 ORDER BY
        te.avg_fragmentation_in_percent DESC 
 
 SELECT @TABLE,
        @NAME,
        @AVG
 
 IF @AVG > 30
 BEGIN
     SET @cmd = 'ALTER INDEX [' + @NAME + '] ON ' + @TABLE + ' REBUILD'
 END
 ELSE
 BEGIN
     SET @cmd = 'ALTER INDEX [' + @NAME + '] ON ' + @TABLE + ' REORGANIZE'
 END
 
 EXEC (@cmd) 
 
 INSERT INTO MANTENIMIENTO_INDICES
   (
     [TABLE],
     NAME,
     [AVG]
   )
 VALUES
   (
     @TABLE,
     @NAME,
     @AVG
   )
END

17.4.13

Create a HTML Table with TSQLXML - SQLServer

Just a simple query to return a HTML Table from a recordset using SQLXML in SQLServer.

Lets take this table as example:


  Issue  | Status  | 

----------------------
  one    | Active  | 
  two    | Active  |  

And this query:


SELECT (SELECT 'Issue' AS 'TH' FOR XML PATH(''),TYPE),
       (SELECT 'Status' AS 'TH' FOR XML PATH(''),TYPE)
UNION ALL         
SELECT (SELECT IS.ISSUE AS 'TD' FOR XML PATH(''),TYPE),
       (SELECT IS.STATUS AS 'TD' FOR XML PATH(''),TYPE)
FROM   ISSUES IS
       FOR XML PATH('TR'),ROOT('TABLE'),TYPE
GO


Will return something like:

<table>
<tr><th>ISSUE</th><th>STATUS</th></tr>
<tr><td>one</td><td>Active</td></tr>
<tr><td>two</td><td>Active</td></tr>
</table>

And that's all.

Creative Commons License This page is powered by Blogger. Isn't yours?