Wednesday, March 16, 2016

Identify all SQL Server tables that have XML columns

Managing the XML data type, and manipulating XML data in SQL Server is a tremendous topic.  Or a 'whole new can of worms', per se.  But, it can be done.  This post only skims the surface, but it's very simple method of listing all of the tables and columns in your database that are the XML data type.  Super easy, but very helpful as well.  I used it myself just today.

   USE yourDBname;
   SELECT
         TABLE_CATALOG [Database],
         TABLE_NAME [Table],
         COLUMN_NAME [Column]
   FROM
         INFORMATION_SCHEMA.COLUMNS
   WHERE
         DATA_TYPE = 'XML'


Your results:













As I said, it only skims the surface, but it's super quick, super easy, and it works.  I'll try to get back here soon with some more involved details on the XML datatypes and manipulations with SQL Server.  Until then....

More details regarding the SQL Server XML data type:

Parse a delimited list into multiple records with tSQL

This is great!  I have always used a table-valued function to go in and split a delimited string into multiple values.  Not a bad method, but I learned something today that is much easier!! Check it out:

   -- table variable for the delimited strings
   DECLARE @source TABLE (ConstituentID INT, ParishByConCodes VARCHAR(255))
   INSERT @source (ConstituentID,ParishByConCodes)
   VALUES (123,Null),
          (234,'0101'),
          (234,'0201;0301'),
          (567,'0401;0501;0691'),;

Now take a look at what we just wrote: 

   SELECT * FROM @source











Good, good... Now let's split each of those Parish codes out into separate values. Comment out the SELECT from @source, and add this in there, directly after the insert into the table variable:

   SELECT
      ConstituentID,
      LTRIM(RTRIM(a.b.value('.[1]','VARCHAR(255)'))) Parish
   FROM
     (
      SELECT ConstituentID,CAST('<XMLRoot><RowData>' +   
      REPLACE(ParishByConCodes,';','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) x
      FROM  @source)t CROSS APPLY x.nodes('/XMLRoot/RowData')a(b)

Run the whole statement again, and look at our results:













Isn't that the best?!  I certainly think so.  Kudos to Pinal Dave at    http://blog.sqlauthority.com/.

Sunday, March 6, 2016

Recover lost SQL Server SSMS scripts

This is a good one.  I'm working a problem earlier today in SSMS, and I didn't close a WHILE LOOP properly.  Rather than telling me this, SQL just runs, and runs, and runs... I couldn't kill it. I couldn't cut/paste/copy my code. I couldn't do anything. So I take a screenshot and kill it in Task Mgr. But - it's not just my bad code.  I lose my entire SSMS session.  Crap.  

I remember seeing something like this before, and I check this directory:



Nadda.  Then I check this one:



Again, nothing. I put a lot of time into this; even w/the bad WHILE LOOP, I still wanted my code! Then it hit me --- the cache! I've worked with sys.dm_exec_query_stats recently, which contains the cached plans. Wouldn't it be in there?  Yep!!  It was, and I was able to scrape my code back together. SSMS crashes, or you close it too quickly without saving your code.  You can get it back!

     USE DBA; --- change this to your database
     SELECT
        execquery.last_execution_time [Date],
        execsql.text [Script]
     FROM
        sys.dm_exec_query_stats AS execquery
           CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) execsql
     ORDER BY
        execquery.last_execution_time DESC  

Your results will return any statements that have been cached, like this:

Not bad at all.  This DMV is very useful for query plan analysis and performance troubleshooting.  Definitely worth a look.

How to dis-aggregate summarized data in SQL Server ?

Now that is just weird to me.  Why would you want to 'dis-aggregate' data? Who wants to look at the details?  If you're not an accountant, I mean... For example, instead of this:







You want to see this:











Turns out it's fairly common to need to see the details behind a summarized report. This is a quick example for dis-aggregating data. 

-- load your summarized data
CREATE TABLE #aggregated (
  [filename] VARCHAR(25),downloads INT,downloaded date,data1 VARCHAR(15),
  data2 VARCHAR(15),data3 VARCHAR(15),processed bit
)
INSERT #aggregated ([filename],downloads,downloaded,data1,data2,data3,processed)
VALUES
       ('filename1.txt',5,'11/15/2015','md1','md2','md3',0),
       ('filename1.txt',79,'12/31/2015','md1','md2','md3',0),
       ('filename1.txt',24,'1/15/2016','md1','md2','md3',0),
       ('filename2.xlsx',49,'10/15/2015','md5','string','string2',0),
       ('filename2.xlsx',25,'2/15/2016','md6','string','string3',0),
       ('filename2.xlsx',39,'12/15/2015','md7','string','string4',0),
       ('filename2.xlsx',8,'1/15/2016','md','string','string5',0);

-- table for the disaggregated details
CREATE TABLE #disaggregated (
  [filename] VARCHAR(25),[action] CHAR(8),downloaded date,data1 VARCHAR(15),
  data2 VARCHAR(15), data3 VARCHAR(15)
)

-- set based approach (rather than cursor)
WHILE EXISTS(SELECT 1 FROM #aggregated WHERE processed = 0)
BEGIN
   DECLARE
      @next INT = (SELECT TOP 1 downloads FROM #aggregated WHERE processed = 0),
      @count INT = 0
      
      WHILE @count < @next
      BEGIN
         INSERT #disaggregated ([filename],[action],downloaded,data1,data2,data3)
         SELECT [filename],'download',downloaded,data1,data2,data3
         FROM #aggregated
         WHERE downloads = @next

         SELECT @count = @count + 1
     END

     UPDATE #aggregated SET processed = 1 WHERE downloads = @next
END

Take a look at the details in your your #disaggregated table:
















Or just run this to see that the count of what we just disaggregated corresponds to the numeric 'downloads' value in the aggregated data set.

--  query to show the counts of what was inserted
SELECT a.[filename],a.downloads,count(b.[action]) [details]
FROM #aggregated a INNER JOIN #disaggregated b
  ON a.[filename] = b.[filename]
  AND a.downloaded = b.downloaded
  AND a.data1 = b.data1
  AND a.data2 = b.data2
  AND a.data3 = b.data3
GROUP BY
  a.[filename],
  a.downloads

Should return this:











Hope it helps!  Let me know what you think.