Self Service BI within Manufacturing #SQLSaturdayEdinburgh #SQLPASS Presentation (#SQLSat)

SQL Saturday

Edinburgh First Conference CentreDuring mid April I was approached by Microsoft (UK) and asked if I would do a presentation at the Microsoft “Accelerate Your Insights” one day conference on the 1st of May 2014. Though hesitant and somewhat nervous about the prospect, as I had never spoken in public… I agreed and prepared a presentation.

The presentation was related to the recent Case Study I had the pleasure of being involved in through my employer, Jabil (@JabilCircuitInc). It would focus on how, at Jabil, we have progressed though the various backend SQL Server infrastructures offered by Microsoft over recent years and how we are using new technologies and features to enable BI delivery to our employees via production systems.

As a direct result of the presentation at Microsoft’s UK headquarters (Reading, UK) I was also invited to speak at a SQL Saturday (SQL PASS community event) being held in Edinburgh on 14th June 2014 at Edinburgh University Conference Centre.

Though hitting a bit of a technical snag with my work laptop, with less than 2 minutes to my presentation: –

Image

I quickly switched to my personal Surface Pro, which by pure chance I had decided to grab as I was leaving in the morning… had only took it so I had something light to play with between session. Just as well I did – quick switch, download of presentation from cloud storage and was good to go minus my demos.

Overall was able to buffer out the presentation, taking about several other aspects and areas we are working with SQL 2014 and BI… had several questions so am taking from that that audience engagement was good. Hopefully all that attended my presentation took away something that they did not know or at least found it useful.

Presentation can be downloaded both the SQL Saturday website and from here: –

Image

Thanks to Jen Stirrup (@JenStirrup) for the invitation to speak and arranging the great free training event; hope to be invited back in the future.

#SQLPASS #SQLSat #SQLSatEdinburgh #SQLSat281

Full case study @ http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=710000004223

Global Firm Takes an Evolutionary Leap in Data Management with Self-Service BI (Case Study)

A case study I was involved in just got published on Microsoft.com.

Over the past few years a lot of the work I have been involved in has been subject to NDAs, including this work with Microsoft (via my employer). Hence been unable to blog about my work or any of the great features of SQL Server 2014 or the Power BI suite of products.

Over the past year as part of the case study we were given advance access to SQL Server 2014 builds, Power BI and enhanced features of SharePoint. We also had assistance and regular contact with the SQL development team and Power BI guys.

As direct result of my participation I was lucky enough to enjoy a few trips to the USA, including to Seattle, Charlotte (for SQL PASS 2013 conference) and Tampa; making 2013 a very enjoyable and educational year for me 🙂 

Business intelligence (BI) information is only valuable when the right users can discover, analyze, use and share it with others—and all in a timely manner. Current technologies produce data at overwhelming rates, often faster than business users can analyze it, and the bottleneck is frequently the time that it takes to generate useful and impactful reports. At US-based supply chain management giant Jabil, as in many enterprises, data analysis has long been a time-consuming and intensive collaboration between the business groups and IT, creating customized reports whose information, by the time it’s used, is already growing stale. With its new solution built on Microsoft SQL Server 2014 and SQL Server 2014 Power View, Jabil users can create their own reports in minutes from business critical data sources using Microsoft Excel, with IT providing training and guidance—freeing up time to work on strategic projects.

Full case study @ http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=710000004223

Download PDF of Case Study

Week of releases from Microsoft

 

Some free Microsoft Press eBooks…

Currently available books include – Introducing SQL Server 2012, Introducing Windows Server 2012, Programming Windows 8 Apps, Introducing Windows 8 for IT Professionals, Understanding Virtualization Solutions and more… Available in PDF, Mobi (Kindle) and epub, you can check them out at: –

http://blogs.msdn.com/b/microsoft_press/archive/2012/05/04/free-ebooks-great-content-from-microsoft-press-that-won-t-cost-you-a-penny.aspx

Introducing SQL Server 2012   Programming Windows 8 Apps   Introducing Windows Server 2012

T-SQL Foreign Key ON DELETE CASCADE and ON DELETE SET NULL – Creating an alternative INSTEAD OF DELETE TRIGGER that allows multiple cascading paths…

… to avoid “Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths”

In most cases if you need to support linking of more than one ID from one table into another table, you would use an intermediary table, this allows any number of links to be established and details of each link can be supplemented with additional details.

However when maintaining and upgrading an existing database that has been around for many years, or even designing a new one, were there will only ever be a need to link in a specific number of IDs, it may be the case that the IDs are placed within the other table directly. 

Doing this prevents the ability for SQL to use the ON DELETE CASCADE and ON DELETE SET NULL from working.

Indeed even if there is only the one ID linked in, but there is more than one round table link you cannot have multiple ON DELETE CASCADE foreign keys established between the tables because it will also fail. Both these examples give the error “Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths”.

I recently embarked on upgrading an existing database to include foreign keys relationships (previously controlled by software only), so that we could introduce full support for merge replication.

The database includes over 360 tables with around 860 relationships to be establish between tables, it is used by over 4500 users in a large number of organisations, with around 70 modules (or applications) accessing the data.

As the applications have been designed and written over the past 10 years by several developers there is no way to guarantee that all the relationships between tables are being enforced correctly… Leaving aside the creation of the FKs and the verification of existing data for another blog, I needed a way to maintain compatibility with all the applications currently using the database. The only way to do this while properly enforcing the foreign keys would be to delete or set to NULL the appropriate linked data on deletion of a row, rather than rely on the application to perform the action.

Having searched for quite a while I found that there was many examples of people recommending using an INSTEAD OF DELETE TRIGGER, to accomplish it, though I could not find any examples. After searching for some time the only examples I found contain hard coded references to linked tables. This is no way ideal and would be a nightmare to maintain with over 860 foreign key links between over 360 tables, which are added to and modified on a regular basis as the applications continue to grow.

Anyway to cut a long story short and get to the code I wrote a TRIGGER that would satisfy my needs, based on a few assumptions.

  1. If a Foreign Key column is nullable I am assuming it is not dependant on the Primary Key data and should be set to NULL rather than deleted.
  2. If the Foreign Key does not support NULLs then the data must be dependant on a valid link and will be deleted… obviously in this case I am assuming that all of the FKs that do not support NULLs are not to be set to a default value from the PK table.
  3. Within the TRIGGER I am will be checking if the FK constraint has a referential action, is disable, is trusted (enforced) and also isn’t a link back to the PK table itself.
  4. The Trigger is designed to fail if fired against a table that has a multi-column Primary Key.

It is worth noting when using a trigger it can be run on multiple rows and is not run for each row deleted, so must handle this

Now I am a bit short of time tonight so I am just going to just show the source of what I have done and assume you will be able to interpret it….

The trigger will cycle through all Foreign keys based on the tables name and the primary key column name. It then deletes or updates the dependant data as appropriate, as it does so (assuming the same trigger is on the dependant tables) if the dependant tables have an update or delete trigger on them it will in turn trigger that causing a cascade effect… As a result of this it could potentially result in a long loop so needs to be used with caution and the database’s foreign keys need to be established carefully.

You should not use this trigger if you are unsure, and I bear no responsibility for any data that is lost as a result of it’s usage! The trigger is based on T-SQL for MS SQL Server 2005, INSTEAD OF DELETE does not work in MS SQL 2000 and I have not tested it on MS SQL 2008.

The trigger looks something like the following; it is based on a table called [Table_1] with a Primary Key column that is either an integer or varchar called [ID], I will be adding it to all tables I’ll be writing a script that just runs through all the tables adding it: –

Code revised 31st Jan. 2009

 

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRIG_PK_Table_1_Delete_FKs]’))

DROP TRIGGER [dbo].[TRIG_PK_Table_1_Delete_FKs]

GO

 

/**

CODED BY: Paul Bunting (http://www.paulbunting.com/)

Created: 23/01/2009

Modified: 31/01/2009

**/
CREATE TRIGGER TRIG_PK_Table_1_Delete_FKs ON dbo.Table_1

INSTEAD OF DELETE

AS

BEGIN

    SET NOCOUNT ON

      PRINT ‘***************************************************’

      PRINT ‘RUNNING – TRIG_PK_Table_1_Delete_FKs’
 
      — before doing anything lets check we
      — actually have something to delete!
      DECLARE @Counter_DELETED_ROWS INT
      SELECT @Counter_DELETED_ROWS = COUNT([EQUIPMENTID]) FROM DELETED

      IF (@Counter_DELETED_ROWS <= 0)
      BEGIN
          PRINT ”
          PRINT ‘0 records deleted’
          PRINT ”
      END
      ELSE
      BEGIN   

    — 1) we only wish to deal with tables that have one Primary Key…

    —    so lets check that this table has only one Primary Key

    DECLARE @Counter_PKs INT

    SELECT @Counter_PKs = COUNT(Col.Column_Name) FROM
            INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col

            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab

            ON  Col.Constraint_Name = Tab.Constraint_Name AND
            Col.Table_Name = Tab.Table_Name

      WHERE Tab.Constraint_Type = ‘PRIMARY KEY’ AND
            Col.Table_Name = ‘Table_1’

     

      — 2) Verify only 1 found

      PRINT ‘  Found ‘ + CONVERT(VARCHAR(255), @Counter_PKs) +
            ‘ primary keys in table’

      IF (NOT (@Counter_PKs = 1))

      BEGIN

            — Lets throw an error if this trigger has been established 

            — against a table with more than one Primary Key, could

            — alter this later to handle them but no requirement to

            — do so at the moment

            DECLARE @tn varchar(4000)

            SELECT @tn = object_name(parent_obj)
            FROM sysobjects WHERE id = @@procid

            SET @tn = ‘Casscade deletes not allowed for this ‘ +
                   ‘table due to multiple primary key columns: ‘ + @tn

            PRINT @tn

            IF EXISTS(SELECT TOP(1) * from deleted) 

                  RAISERROR (@tn, 16, 1)   

      END

      ELSE

      BEGIN

            — 3) Lets get all the Foreign Keys assosiated with this table

            DECLARE @ForeignKeys TABLE (TableTempID bigint
               IDENTITY(1,1) PRIMARY KEY CLUSTERED,

               [Name] varchar(2000),[OBJECT_ID] int, [is_disabled] bit,
               [is_not_for_replication] bit,
               [delete_referential_action] int,
               [update_referential_action] int,
               [Fk_table_name] varchar(2000),
               [Fk_table_schema] varchar(2000),
               [Pk_table_name] varchar(2000),
               [Pk_table_schema] varchar(2000),
               [Fk_col_name] varchar(2000),
               [Pk_col_name] varchar(2000),
               [constraint_column_id] int,
               [is_not_trusted] bit)

           

            INSERT INTO @ForeignKeys
               ( [Name], [OBJECT_ID], [is_disabled],
                 [is_not_for_replication], [delete_referential_action],

                 [update_referential_action], [Fk_table_name],
                 [Fk_table_schema], [Pk_table_name], [Pk_table_schema],

                 [Fk_col_name], [Pk_col_name], [constraint_column_id],
                 [is_not_trusted] )

               ( select Fk.name, Fk.object_id, Fk.is_disabled, 
                  Fk.is_not_for_replication, Fk.delete_referential_action,

                  Fk.update_referential_action,
                  object_name(Fk.parent_object_id) as Fk_table_name,

                  schema_name(Fk.schema_id) as Fk_table_schema,
                  TbR.name as Pk_table_name,
                  schema_name(TbR.schema_id) Pk_table_schema,

                  col_name(Fk.parent_object_id,
                  Fk_Cl.parent_column_id) as Fk_col_name,

                  col_name(Fk.referenced_object_id,
                  Fk_Cl.referenced_column_id) as Pk_col_name,
                  Fk_Cl.constraint_column_id,

                  Fk.is_not_trusted

                 from sys.foreign_keys Fk
                  left outer join sys.tables TbR

                 on TbR.object_id = Fk.referenced_object_id
                  inner join sys.foreign_key_columns Fk_Cl

                 on Fk_Cl.constraint_object_id = Fk.object_id

                 where

                  ( ([TbR].[name] = ‘Table_1’) AND
                    (col_name(Fk.referenced_object_id, 
                        Fk_Cl.referenced_column_id) = ‘ID’) AND
                    ([Fk].[delete_referential_action] = 0) AND

                    ([Fk].[is_disabled] = 0) AND
                    ([Fk].[is_not_trusted] = 0) ) )

 

            — 4) Lets see how many Foreign Keys we are dealing with

            DECLARE @Counter bigint

            DECLARE @MaxTableCount BIGINT

           

            SELECT @MaxTableCount = max(TableTempID) from @ForeignKeys

            SET @Counter = 1

            PRINT ‘  Found ‘ + CONVERT(VARCHAR(255), @MaxTableCount) +
                 ‘ foreign keys links for this table’

 

           

            DECLARE @Fk_table_name VARCHAR(2000)

            DECLARE @Pk_table_name VARCHAR(2000)

            DECLARE @Fk_col_name VARCHAR(2000)

            DECLARE @Pk_col_name VARCHAR(2000)

            DECLARE @is_not_trusted BIT

            DECLARE @is_disabled BIT

            DECLARE @delete_referential_action INT

            DECLARE @Fk_col_IsNullable BIT

           

            DECLARE @sqlQuery nVarchar(4000)

           

            — 5) Lets cycle through all the Foreign Keys

            WHILE (@Counter <= @MaxTableCount)

            BEGIN

                 

                 SELECT @Fk_table_name = ltrim(rtrim(Fk_table_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @Pk_table_name = ltrim(rtrim(Pk_table_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @Fk_col_name = ltrim(rtrim(Fk_col_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @Pk_col_name = ltrim(rtrim(Pk_col_name))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @is_not_trusted = ltrim(rtrim(is_not_trusted))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @is_disabled = ltrim(rtrim(is_disabled))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 SELECT @delete_referential_action =
                   ltrim(rtrim(delete_referential_action))
                  FROM @ForeignKeys WHERE TableTempID = @Counter

                 

                 SELECT @Fk_col_IsNullable = c.IsNullable
                  FROM syscolumns c WHERE c.id =
                   OBJECT_ID(@Fk_table_name) AND
                   c.NAME = @Fk_col_name

                 

                  PRINT ‘—————————————‘

                  PRINT ‘  Processing key ‘ +
                    CONVERT(VARCHAR(255), @Counter) + ‘ of ‘ +
                    CONVERT(VARCHAR(255), @MaxTableCount)

                  PRINT ‘  @Fk_table_name "’ + 
                    CONVERT(VARCHAR(255), @Fk_table_name) +
                    ‘" ~ @Fk_col_name – "’ +
                    CONVERT(VARCHAR(255), @Fk_col_name) + ‘"’

                  PRINT ”

 

                  — 6) Lets verify that there is no CASCADE or
                  —    UPDATE already on the Key link and that
                  —    it is not a system table… also

                  —    need to verify that the Foreign Key Table
                  —    is not the same as the current table and
                  —    that Key should be enforced.

                  IF (

                              (@delete_referential_action = 0) AND

                              (@is_not_trusted = 0) AND

                              (@is_disabled = 0) AND

                              (@Fk_table_name <> ‘dtproperties’) AND

                              (@Fk_table_name <> ‘sysdiagrams’) AND

                              (NOT (@Fk_table_name = @Pk_table_name))

                        )

                  BEGIN

                 

                        — 7) Because we need to execute deletes with
                        —    a "EXECUTE sp_executesql…" statement the

                        —    DELETED table is not available so we must
                        —    cycle through it in case it is a batch
                        —    delete.

                        —

                        —    We convert the values to Varchar here to
                        —   account for PKs that are text and integer,
                        —   plus we need it as text later

                        —

                        —    This is because we do not know what all
                        —    the FK column names will be so we need

                        —    to build the query

                        DECLARE @DELETED_ROWS TABLE
                          (TableTempID bigint IDENTITY(1,1)
                            PRIMARY KEY CLUSTERED,

                           [ValueINNER] varchar(4000))

                       

                        INSERT INTO @DELETED_ROWS

                          ( [ValueINNER] )

                          ( SELECT CONVERT(VARCHAR(4000), [ID])
                            from DELETED )

                        DECLARE @CounterINNER bigint

                        DECLARE @MaxTableCountINNER BIGINT

                        DECLARE @ValueINNER VARCHAR(4000)

           

                        SELECT @MaxTableCountINNER = max(TableTempID)
                          from @DELETED_ROWS

                        SET @CounterINNER = 1

           

                        — 8) Lets cycle through all the ROWS to be deleted

                        WHILE (@CounterINNER <= @MaxTableCountINNER)

                        BEGIN

                              SELECT @ValueINNER =
                                 ltrim(rtrim([ValueINNER])) 
                              FROM @DELETED_ROWS WHERE
                                TableTempID = @CounterINNER

                              — 9) Lets see if the Foreign Key
                              —    column allows nulls… we will
                              —    use this to guess if the linked
                              —    table is dependant on the
                              —    link (i.e. a link between

                              —    an email and it’s attachments)
                              —    or if it is only a lookup link
                              —    (i.e. a link between a staff 
                              —    member and the email they edited…
                              —    may want to delete the staff
                              —    member but don’t want their
                              —    emails deleted)

                              PRINT ‘====’

                              IF (@Fk_col_IsNullable = 1)

                              BEGIN

                                    — 10a) Set LINKED values to NULL
                                    —      (Using the like statement here
                                    —      should account for INT and
                                    —      VARCHAR PKs)

                                    PRINT ‘  UPDATE [‘ + @Fk_table_name
                                     + ‘] SET [‘ + @Fk_col_name 
                                     + ‘] = NULL WHERE [‘ + @Fk_col_name
                                     + ‘] LIKE ”’ + @ValueINNER + ””

                                    SET @sqlQuery = ‘

SET NOCOUNT OFF

UPDATE [‘ + @Fk_table_name + ‘] SET [‘ + @Fk_col_name + ‘] = NULL WHERE [‘ + @Fk_col_name + ‘] LIKE ”’ + @ValueINNER + ”’

SET NOCOUNT ON’

                                    –PRINT @sqlQuery

                                    EXECUTE sp_executesql @sqlQuery

                              END

                              ELSE

                              BEGIN

                                    — 10b) Delete DEPENDANT values
                                    —      (Using the like statement
                                    —      here should account for INT
                                    —      and VARCHAR PKs)

                                    PRINT ‘  DELETE FROM [‘
                                     + @Fk_table_name + ‘] WHERE [‘
                                     + @Fk_col_name + ‘] LIKE ”’
                                     + @ValueINNER + ””

                                    SET @sqlQuery = ‘

SET NOCOUNT OFF

DELETE FROM [‘ + @Fk_table_name + ‘] WHERE [‘ + @Fk_col_name + ‘] LIKE ”’ + @ValueINNER + ”’

SET NOCOUNT ON’

                                    –PRINT @sqlQuery

                                    EXECUTE sp_executesql @sqlQuery

                              END

                              PRINT ‘====’

                              SET @CounterINNER = @CounterINNER + 1

                        END 

                  END

 

                  SET @Counter = @Counter + 1

            END

            PRINT ‘—————————————‘

           

            — 11) Proceed with the delete…

            SET NOCOUNT OFF

            DELETE FROM [Table_1]

                  WHERE [ID] in (SELECT [ID] FROM DELETED)

            SET NOCOUNT ON

            –PRINT ‘DELETE FROM [Table_1]

            —    WHERE [ID] in (SELECT [ID] FROM DELETED)’

      END

 

      END 

     

      PRINT ‘COMPLETED – TRIG_PK_Table_1_Delete_FKs’

      PRINT ‘***************************************************’

      SET NOCOUNT OFF

END

GO

How to… Showing a sub-query containing multiple rows within one column of a SELECT statement (using CROSS APPLY and SQL’s XML abilities)

It is often necessary to associate multiple rows within one table to a single row within another table, for example several contacts associated with one company, or several categories mapped to one company or contact.

This raises the problem of how to allow your end users to search on multiple items rather than just one, and only display the main route item once.

Let me explain a scenario that you might encounter: –

You have a contacts table, which is linked to a companies table, which is again liked to a keywords table. Any one company can have multiple contacts and multiple keywords associated with it.

Now say you want to allow a multi keyword search against the keywords table, but the search must contain both keywords for the same company and you only want one row per contact to be returned. You may also simple want to display all the keywords associated with a company when viewing a list of contacts.

Traditionally this would have been quite complex to achieve. However with Microsoft SQL 2005 and above’s support for the CROSS APPLY syntax, this becomes a whole lot easier to achieve.


CROSS APPLY

Put simply CROSS APPLY is an INNER or OUTER JOIN between a table and a table-valued function.

A simple example is one commonly shown for the Microsoft AdventureWorks smaple database: –

USE

AdventureWorks
GO

CREATE

FUNCTION
   
Sales
.fnTopNOrders(
        @CustomerID
AS int, @n AS INT) RETURNS TABLE AS RETURN

    SELECT TOP(@n) SalesOrderID, ShipDate = convert(char(10), ShipDate,112),
       
TotalDue=convert(varchar,TotalDue,1) 
   
FROM AdventureWorks.Sales.SalesOrderHeader
   
WHERE CustomerID = @CustomerID ORDER BY TotalDue DESC
GO

SELECT
   

StoreName=s.Name, [Top].ShipDate, [Top].SalesOrderID, 
    TotalDue
=‘$’+[Top].TotalDue
FROM AdventureWorks.Sales.Store AS s
JOIN AdventureWorks.Sales.Customer AS c
ON s.CustomerID = c.CustomerID
CROSS APPLY
    AdventureWorks.Sales.fnTopNOrders(c.CustomerID, 5) AS [Top]
WHERE CustomerType=‘S’
ORDER BY StoreName, convert(money,TotalDue) DESC
GO

In this sample the table valued function returns the top ‘n’ (largest) orders for a store. The SELECT statement would normally be limited to the stores, but with the CROSS APPLY join in place the store information can be combined (joined) with the results for the top orders (in this case the top 5 orders).

The same result could have been achieved previously by using a temporary table and joining with that, but that can be tedious to code and maintain. Using CROSS APPLY to join the table to the function’s results is much neater and can be quite powerful.


Back to the problem…

So how does CROSS APPLY help us list all our keywords in one column for our company?

Well the answer comes from SQL’s XML features… which can return results as a table-value, with just one row and one column… and we can manipulate the format of the output so we have something nice and neat to work with.


FOR XML Clause

The FOR XML Clause has several modes that can run, which will determine the shape of that resulting XML. These modes are RAW, AUTO, EXPLICIT or PATH.

Without going into too much detail the mode we are interested in to help us with our problem is the PATH mode. So using the FOR XML PATH clause in our query.

The PATH mode provides a simple way to mix elements and attributes, in order to output some XML based on our data (for more in-depth details take a look at http://msdn2.microsoft.com/en-us/library/ms189885.aspx and http://msdn2.microsoft.com/en-us/library/ms190922.aspx)

Now in our problem we are looking to retrieve all the keywords for a company and display them in one column. So lets see what we can get from SQL using the FOR XML Clause

If we simply used the following: –

SELECT Keyword FROM cont_keyword WHERE CompanyID = 19
FOR XML PATH

We would be returned one table with one row that had one column. The contents of which would be something like: –

<

row>
    <
Keyword>Reseller</Keyword>
</
row>
<
row>
    <
Keyword>Ireland</Keyword>
</
row>

This is not quite what we are looking for so we shall modify the PATH mode clause to exclude the row element: –

SELECT

Keyword FROM cont_keyword WHERE CompanyID = 19
FOR XML PATH() 
 

Now we get an output something like: –

<

Keyword>Reseller</Keyword>
<
Keyword>Ireland</Keyword>

Which is getting much closer to something we can use. Now when outputting something as XML in SQL we can tell it to output any column as plain text without any element wrappers. This would be done as follows: –

SELECT

Keyword AS [text()] FROM cont_keyword
WHERE CompanyID = 19 FOR XML PATH()

But this isn’t much use as it would just return one string with every keywords joined together.

ResellerIreland

Modifying our query a little more will allow us to separate our keywords: –

SELECT

‘{’ + Keyword + ‘},’ AS [text()]
FROM cont_keyword WHERE CompanyID = 19
FOR XML PATH()
 

I have chosen to surround my keywords with "{" and "}", this is to allow me to easily search for specific categories in a LIKE statement. If I simply separated them with commas then if there are categories that are similar (for example "Architect", "Structural Architect", etc), I would run into problems when constructing my LIKE clause. The query above would give a result looking something like: –

{Reseller},{Ireland},

Now this is something that we can use… well almost, you’ll notice if you run it, the table column is given a random XML name, as is the table the result is presented in.

SIDE NOTE: – You may have been tempted to use square brackets instead ("[" and "]") but we must remember that these are reserved characters in LIKE statements and would have to be escaped in any subsequent queries.

We could use something like: –

SELECT (SELECT ‘{’ + Keyword + ‘},’ AS [text()]
FROM cont_keyword WHERE CompanyID = 19
FOR XML PATH()) AS keywordlist
 

If we just need the column to have a legible name, but for our purposes we also need it defined as a table, which is where part of the CROSS APPLY syntax comes in…


Back to the problem

Now we shall use a simple CROSS APPLY join to join our companies table and keywords table-value together.

SELECT
    cont_companies
.*, 
    LEFT(kw.keywordlist, LEN(kw.keywordlist) 1) AS keywords
FROM 
    cont_companies
CROSS APPLY
    (SELECT ‘{’ + Keyword + ‘},’ AS [text()]
        FROM cont_keyword
        WHERE COMPANYID = cont_companies.Company_ID
        FOR XML PATH()) kw(keywordlist)

Now in the code above you will notice a few things, first of, we only select all the columns from the companies table, then we actually take the output from our XML table and trim of the last comma character. The CROSS APPLY join is done based on the CompanyID filed, so we will get a list of keywords for each company.

In this sample the keyword output is put into a table called ‘kw’, with the column ‘keywordlist’. ‘keywordlist’ contains the full output from the XML query we built above, which includes an additional comma on the end. In our select statement we disregard this because it is not needed, and should not be displayed at anypoint to end users.

We only select all the columns from the companies table, plus the trimmed keywords column, because we are not interested in any of the other data that will be in the ‘kw’ temporary table, so why return it.

For my purposes I’d actually define this query as a view… you need to watch however, because SQL 2005 Management Studio will tell you that it cannot parse the query if you edit or create this through the designed… Along with a couple of other features (like ‘newsequentialid()’) the SQL 2005 Management Studio isn’t able to parse this type of query and will tell you so.

Because the FOR XML clause will only ever return one row an alternative to using the CROSS APPLY join would be to call the FOR XML clause SELECT twice, for example: –

SELECT

cont_companies.*,
    LEFT((
        SELECT ‘{’ + Keyword + ‘},’ AS [text()]
        FROM cont_keyword
        WHERE CompanyID = cont_companies.Company_ID
        FOR XML PATH())
        ,
        LEN((
            SELECT ‘{’ + Keyword + ‘},’ AS [text()]
            FROM cont_keyword
            WHERE CompanyID = cont_companies.Company_ID
            FOR XML PATH())
        ) 1 ) AS keywords
FROM cont_companies

Now this will give the same result but would run the FOR XML clause query twice, and if we have several thousand companies with several keywords associated with each, then the overhead of running the FOR XML twice as many times as is needed will soon add up.

Using the CROSS APPLY join should therefore be more efficient and easier to manage.

Update: – 22nd March 2008
======================

On further usage I found that the resulting "keywords" column from both the queries shown above would, on occasion, return the data as binary data. As such they should have a CONVERT statement applied to them in order to correctly return the required data as a TEXT data-type, i.e.: –

SELECT
    cont_companies
.*, 
    CONVERT(TEXT,
    LEFT(
kw.keywordlist, LEN(kw.keywordlist) 1)
   
)
AS keywords
FROM 
    cont_companies
CROSS APPLY
    (SELECT ‘{’ + Keyword + ‘},’ AS [text()]
        FROM cont_keyword
        WHERE COMPANYID = cont_companies.Company_ID
        FOR XML PATH()) kw(keywordlist)

or

SELECT

cont_companies.*,
    CONVERT(TEXT, LEFT((
        SELECT ‘{’ + Keyword + ‘},’ AS [text()]
        FROM cont_keyword
        WHERE CompanyID = cont_companies.Company_ID
        FOR XML PATH())
        ,
        LEN((
            SELECT ‘{’ + Keyword + ‘},’ AS [text()]
            FROM cont_keyword
            WHERE CompanyID = cont_companies.Company_ID
            FOR XML PATH())
        ) 1 )) AS keywords
FROM cont_companies

How To… Adding a standard field to all tables in a Large SQL Database

On occasion you may need to add a standard field to a group of tables within your database or to them all.
 
The following script sample shows how to add a standard field to all tables, simply filtering the table list will allow this to be aplied to a selection of tables… In this sample I show adding a Microsoft TimeStamp field to all tables within a database: –

 

USE

[MyDB]
GO

DECLARE

@TableNames TABLE (TableTempID bigint IDENTITY(1,1) PRIMARY KEY CLUSTERED, TableName varchar(2000))

INSERT INTO @TableNames (TableName) (select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N‘IsUserTable’) = 1 AND OBJECTPROPERTY(id,‘ismsshipped’) = 0)

DECLARE @Counter bigint
DECLARE
@MaxTableCount bigint

SELECT @MaxTableCount = max(TableTempID) from @TableNames
SET @Counter = 1

DECLARE @TableName Varchar(2000)
DECLARE @sqlQuery nVarchar(4000)

WHILE (@Counter <= @MaxTableCount)
BEGIN

    SELECT @TableName = ltrim(rtrim(TableName)) FROM @TableNames WHERE TableTempID = @Counter
    SET @sqlQuery = ‘ALTER TABLE [‘ + @TableName +
    ‘] ADD MS_TSTAMP TIMESTAMP’
    EXECUTE sp_executesql @sqlQuery
    SET @Counter = @Counter + 1

END