Voice of the DBA

Tag: T-SQL

  • Adding Data to Filestream with T-SQL

    I’ve written on how to enable Filestream and how to add a filegroup, but I haven’t touched the Filestream impact on your tables. This post will look at the table side of Filestream and how you get your binary data into the database.

    Let’s assume you have a filestream enabled database. If you look at my File and Filegroups post, you’ll see how to do this. Given that, we now need to create a table and add data to it.

    Creating a Table with Filestream

    Filestream doesn’t have a separate entity like Filetables. Instead, the Filestream attribute is added to a column in a table, much like the identity property.

    I’ll create a simple table that holds Filestream data.

    CREATE TABLE FSDemo
(
    id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
  , name VARCHAR(20)  
  , jpg varbinary(MAX) FILESTREAM
);
go

    Here we are creating a basic table that has an ID and name, which are essentially meta data for our filestream data. You could have any amount of information in this table, but the Filestream data is stored in the jpg field, where we have a varbinary(max) datatype, and the FILESTREAM attribute on the table.

    I could have a more complex schema, such as the Production.Document table in Adventureworks2008 (shown below)

    CREATE TABLE [Production].[Document](
    [DocumentNode] [hierarchyid] NOT NULL,
    [DocumentLevel]  AS ([DocumentNode].[GetLevel]()),
    [Title] [nvarchar](50) NOT NULL,
    [Owner] [int] NOT NULL,
    [FolderFlag] [bit] NOT NULL,
    [FileName] [nvarchar](400) NOT NULL,
    [FileExtension] [nvarchar](8) NOT NULL,
    [Revision] [nchar](5) NOT NULL,
    [ChangeNumber] [int] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [DocumentSummary] [nvarchar](max) NULL,
    [Document] [varbinary](max) FILESTREAM  NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
    );

     

    Adding Data

    To add data to this table, we have a variety of choices using T-SQL. It’s possible to use an application, and pass in parameters that are streamed to the table, but for the simplicity of this example, let’s show two ways in T-SQL.

    First, let’s look at a direct insert. I can CAST my data to varbinary, and then insert it into the table. I have a small image that looks like this:

    circle

    It’s a simple circle, very small. The actual data in this image is this:

    0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4 D002A000000080004011A0005000000010000003E011B00050000000100000046 01280003000000010002000001310002000000120000004E00000000000000600 000000100000060000000015061696E742E4E45542076332E352E313000FFDB00 43000201010201010202020202020202030503030303030604040305070607070 706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C 0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0 C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C 0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F000001050 1010101010100000000000000000102030405060708090A0BFFC400B510000201 0303020403050504040000017D010203000411051221314106135161072271143 28191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435 363738393A434445464748494A535455565758595A636465666768696A7374757 67778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3 B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E 8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F01000301010101010101010100000000 00000102030405060708090A0BFFC400B51100020102040403040705040400010 277000102031104052131061241510761711322328108144291A1B1C109233352 F0156272D10A162434E125F11718191A262728292A35363738393A43444546474 8494A535455565758595A636465666768696A737475767778797A828384858687 88898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C 4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9 FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03 FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C1 8940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C 59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87 BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007 FFFD9

    I can insert this data directly into my table:

    INSERT INTO FSDemo(ID, name, jpg) 
Values (NEWID()
      , 'circle.jpg'
      , 0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4D002A000000080004011A0005000000010000003E011B0005000000010000004601280003000000010002000001310002000000120000004E00000000000000600000000100000060000000015061696E742E4E45542076332E352E313000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C18940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007FFFD9
      );
go

    The other method I can show is a direct insert, streaming from the OPENROWSET command. Here I’ll pic an image on my system and insert it.

    INSERT INTO FSDemo(ID, name, jpg) 
Values (NEWID()
      , 'uma.jpg'
      , (SELECT * FROM OPENROWSET(BULK N'C:\Users\Steve\Documents\SampleData\Uma.jpg', SINGLE_BLOB) AS CategoryImage)
      );
go

    Now if I query the table

    SELECT
  id, name, jpg
 from FSDemo;
go

    I see my data.

    Capture_027

    When I look at this in Management Studio, I see the actual hex data, which isn’t easy to understand. I need another way to extract this data and render it. However I can show one more quick demo here. Let me take an XML document and insert it:

    declare @list XML
select @list = '
<Lists>
  <Groceries Store=''Safeway''>
    <Item>Milk</Item>
    <Item>Eggs</Item>
    <Item>Bread</Item>
  </Groceries>
  <Drinks Store=''Tipsys''>
    <Drink>Fat Tire</Drink>
    <Drink>Klinker Brick Cabernet</Drink>
    <Drink>Patron</Drink>
  </Drinks>
  <Ranch>
    <Item>electric fence ribbon</Item>
  </Ranch>
</Lists>'
;
INSERT INTO FSDemo(ID, name, jpg) 
Values (NEWID()
      , 'sample.xml'
      , CAST( @list AS VARBINARY(MAX))
      );
go

    Once this is done, the data is in binary, as I see from the selection from the table.

    Capture_029

    However if I cast this result back, I get the data as expected.

    Capture_028

    Hopefully this helps you to understand how to insert data into a Filestream table. In another post, I’ll look at how you can extract this data for rendering.

  • Inserting Binary Data

    There are a number of articles on dealing with the insertion of binary data into SQL Server using BULK INSERT, OPENROWSET, and even Java. However I saw someone asking recently how to insert data dynamically and thought that was an interesting question.

    If you are working with some front end language, this is easy with the various ODBC/OLEDB/ADO/etc interfaces into SQL Server. These languages allow you to specify parameters as a binary stream.

    However if you need to insert this data in T-SQL, what can you do? I know that there is a CAST function and that should work with varbinary. Let’s test something:

    CREATE TABLE Binarytest
( id INT
, note VARBINARY(500)
)
;
INSERT BinaryTest SELECT 1, CAST( 'A' AS VARBINARY)

SELECT note, CAST( note AS VARCHAR) FROM BinaryTest

    This returns:

    binary1

    I also know that I can directly take binary data in SQL Server and work with it, as long as I specify it’s binary. So I can do this:

    INSERT BinaryTest SELECT 1, 0x41

    If I now query my table, I get:

    binary2

    Not the easiest format to work with, but if I construct binary data somehow, I can work with it in T-SQL if I need it.

  • Full-Text Search – Word Breakers and Stemmers

    There are numerous components to the Full-Text Search (FTS) subsystem in SQL Server that help provide efficient, relative answers to queries. Full-text Search is a little complex, and as I’ve been working with the system in an effort to learn more about it, I decided to document how a few things work.

    Word Breakers and stemmers are two interesting parts of the FTS system. They deal with certain language specific operations that help searches work better. They are related as the two items are loaded together if you use third party word breakers and stemmers.

    I haven’t seen third party word breakers, but some work from other products. As an example, here’s a post to load the Greek FTS search word breaker and stemmer from Sharepoint server if you are on SQL Server 2008. It’s included in SQL Server 2012.

    Word Breakers

    Let’s start with word breakers, which do just what the term implies: they break words. It would seem to be obvious that spaces are the word boundaries, and they are in English, but not necessarily in all languages. There are also the issues of characters in Asian languages like Japanese and Chinese. You can’t count spaces as the word boundaries on those languages.

    Word breakers use the lexical rules of the language to determine word boundaries. Essentially they find what the words are, and then further action can be taken in building the FTS index or processing the query.

    The “words” that the word breaker spits out are seen a “tokens” to the FTS index, and each can then be processed by stemmers, stoplists, thesaurus, etc.

    Stemmers

    Stemmers are an interesting part of the full-text search system. They remind me of my high school Latin classes, where we had to conjugate words. A stemmer takes a word and generates inflectional forms, or conjugations. The example in Books Online, and an easy one to understand is “run”. There are various forms of "run” that we would want to consider as equivalent when performing a search. For example, you would want to consider:

    • ran
    • running
    • runs
    • runner (perhaps)

    The same could be said for “lay”. That would generate

    • lie
    • laying
    • lain
    • lays

    This is one of the big advantages over the LIKE predicate in that stemmers can match these forms of the word being searched for. The index would relate all of these to the core, base word.

    More

    The Books Online page for Word Breakers and Stemmers has technical information on checking what’s installed, some troubleshooting, language settings, and some drier documentation on what you can do with word breakers, but not a lot of explanatory detail.

    I used a little of the information in Books Online, and some from Pro Full-Text Search in SQL Server 2008. You can read more, but unfortunately I haven’t found a lot more documentation on the details of how things work.

    You would probably learn more if you write your own Word Breaker and Stemmer, and there is a sample in the Windows SDK to get you started, but that’s beyond what I want to do.

  • T-SQL Tuesday #37 – A Month of Joins

    tsqltuesdayIt’s time once again for T-SQL Tuesday, and this month is hosted by SQLity.net, Sebastian Meine.

    If you want to know more or participate, read the invitation and write your own blog post.

    The topic this month is joins, in honor of Sebastian’s a-join-a-day series. He’s writing about various aspects of joins, and invites us all to do the same thing on this Tuesday.

    Writing Better Joins

    I’m not a T-SQL expert. I can write code, and understand many type of queries, but I’m not one to dazzle others with their code, like Jeff Moden can. Instead, I want to talk about how I’ve learned to ensure that my code makes sense, is understandable, and most importantly, easy to find the mistakes inside.

    I mainly do this by paying attention to the formatting of the code. I would say that once I started to get away from writing code like this, I found bugs easier, and understood the code better:

    WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Get the initial list of Employees for Manager n
FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @BusinessEntityID
UNION ALL SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [HumanResources].[Employee] e INNER JOIN [EMP_cte] ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName',
[EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
FROM [EMP_cte] INNER JOIN [HumanResources].[Employee] e ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25)

    I often find code in forums, or sent to me and I need to reformat it so that it looks better. I prefer something like this:

    WITH    [EMP_cte] ( [BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel] )
          -- CTE name and columns
          AS (
               SELECT
                e.[BusinessEntityID]
               ,e.[OrganizationNode]
               ,p.[FirstName]
               ,p.[LastName]
               ,0 -- Get the initial list of Employees for Manager n
               FROM
                [HumanResources].[Employee] e
                INNER JOIN [Person].[Person] p
                    ON p.[BusinessEntityID] = e.[BusinessEntityID]
               WHERE
                e.[BusinessEntityID] = @BusinessEntityID
               UNION ALL
               SELECT
                e.[BusinessEntityID]
               ,e.[OrganizationNode]
               ,p.[FirstName]
               ,p.[LastName]
               ,[RecursionLevel] + 1 -- Join recursive member to anchor
               FROM
                [HumanResources].[Employee] e
                INNER JOIN [EMP_cte]
                    ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
                INNER JOIN [Person].[Person] p
                    ON p.[BusinessEntityID] = e.[BusinessEntityID]
             )
    SELECT
        [EMP_cte].[RecursionLevel]
    ,   [EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode]
    ,   p.[FirstName] AS 'ManagerFirstName'
    ,   p.[LastName] AS 'ManagerLastName'
    ,   [EMP_cte].[BusinessEntityID]
    ,   [EMP_cte].[FirstName]
    ,   [EMP_cte].[LastName] -- Outer select from the CTE
    FROM
        [EMP_cte]
        INNER JOIN [HumanResources].[Employee] e
            ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
        INNER JOIN [Person].[Person] p
            ON p.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY
        [RecursionLevel]
    ,   [EMP_cte].[OrganizationNode].ToString()
OPTION
        ( MAXRECURSION 25 )

    That actually came from reformatting the code using SQL Prompt, a product from my employer, Red Gate Software. I’m lucky in that SQL Prompt formats things as I’d prefer them, indenting and getting the JOIN and ON clauses onto separate lines.

    Having code with structure, where you can clearly see the tables being joined, the clauses in use, and not miss any of the columns being selected at a glance is important. When you’re under stress and trying to debug or develop something, it’s easy to miss something that’s happening in the code if it’s not formatted correctly.

    Whether you like commas before or after columns, or you want things indented so that the names of objects line up doesn’t really matter. What’s important is that you and your team agree on a set of formatting, or have tools that reformat things for each developer in a consistent way. You’ll spend less time trying to understand the code and more time building or fixing it, if it has a consistent layout.