Voice of the DBA

Tag: T-SQL

  • Test Coverage

    I’ve never had to work full time in a QA group, but I have had to perform software testing of an application and it wasn’t fun. Even as I worked through the various features, looking for edge cases, common mistakes, etc., I realized that running tests was something that really required more discipline and process than I was giving it at the time. Too often I ‘d realize that my regressions weren’t 100% duplicates of previous executions because I’d allowed too much “human effort” into my process.
    These days most testing of software is automated. I know QA groups still exist, and they need to handle some of the manual checks that are very hard to automate. However more and more testing is being pushed back onto developers to handle, with frameworks like NUnit and JUnit. In the database world, we haven’t done a great job of including testing into the code we write, but there’s a great testing framework we can use.
    TSQLT is a framework written by Sebastian Meine and Dennis Lloyd and it’s free. It’s been developed to help you write tests that can exercise your T-SQL code to determine if it’s doing what you expect. I’ve used it a little, and while I see the potential, I also realize that this will take some practice to learn how to more easily write tests that can cover the various potential places where bugs can be introduced.
    However, if you use version control, and you should, then you can easily spread the load of writing tests to all of your developers. Since the tests are stored procedures, they can be included in your VCS project and shared by all your developers. With a little effort from each member of your team, you might be surprised at the code coverage you can achieve with this framework.
    Testing is important, as we see over and over again as software is released and doesn’t work as expected. I think much of that is our fault, as software developers, for not improving our testing skills and discipline.
    Steve Jones
    If you want more automated ways to easily share tests between developers and execute them, take a look at SQL Source Control and SQL Test from Red Gate Software.

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 20.9MB) feed

    MP4 iPod Video ( 24.8MB) feed

    MP3 Audio ( 4.9MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Encrypting T-SQL Code

    This editorial was originally published on April 10, 2009. It is being re-run as Steve is on vacation.

    I recently engaged in a discussion with someone that was building an application on SQL Server. This person had a bunch of SQL code that was being put in stored procedures and then being sent to client sites. The developers were worried about clients modifying their code and wanted to send “secure updates” to the client by encrypting the stored procedures and giving the clients the encrypted text.

    Apart from the hassles of getting this to work, I asked by would they bother. There are decryption routines available and this isn’t meant to be a secure way to hide your code. Heck, even application code can be decompiled, and if they’re likely to mess with the code, they likely have the skills to get the source.

    So for this Friday’s poll, I’m wondering about how you feel about encrypting code in SQL Server. I want to know what you think.

    Is there a point?

    Is there a reason to encrypt stored procedure code? After all, there are many, many vendors that sell applications built on SQL Server, with stored procedures.  Most of that code isn’t encrypted and it’s usually not a problem. Most customers don’t mess with the code and there are usually prohibitions written into support agreements.

    Personally I don’t think there are many great ideas, and likely very, very few in the database space, that are worth securing. Someone doesn’t buy a software package so the can learn how you wrote it. Most of them buy software because it solves a problem and saves them time. If you can deliver a well performing, and good looking application, no one cares about the code.

    But I’m curious what the rest of you think, both end users and software developers. Is there really a good reason to worry about encrypting your code?

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • The T-SQL Paradigm

    This editorial was originally published on Mar 21, 2009. It is being re-run as Steve is on vacation.

    I ran across this post ranting about T-SQL. It’s got going pretty quickly with some less that professional responses. It’s cleaned itself up a bit, but it got me thinking for this Friday.

    My first programming was done in BASIC. From there I played with a little assembler before going on to Pascal and C in high school. The came LISP, APL, Fortran, more assembler, C++, and a little ADA in college. My professional career had me programming in Lotus 1-2-3, dBase, FoxPro, C, VB, and finally SQL with minor dabbling in HTML, XML, ASP, and some .NET.

    I think I’ve had a decent amount of programming experience in my life. In that time, I’ve found that most programming languages to work fine, and while I’ve enjoyed some more than others, I haven’t thought that any “sucked.” I wouldn’t want to write most modern apps in APL, but I don’t much think there’s any thing wrong with picking VB.NET v Java v C#. So for this Friday’s poll:

    Is T-SQL really a strange paradigm?

    Or SQL in general? Is it really messed up? Would the structures used by LINQ make more sense to anyone? Hardcore developers seem to like a different structure, but is it because it seems more natural to them? Or is SQL really such a strange beast.

    Many people seem to have trouble with SQL. Just read the forums here at SQLServerCentral to see all the issues that come when people try to query the database. But is it just hard to learn or is there a better way to build a query language?

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

  • Managing Data in a FileTable with T-SQL

    I wrote a post about creating a Filetable, which just covered the basics of how to build one. How do you work with the data in this table? In this post I’ll look at a few things you can do from the T-SQL side.

    From the last post, I had my author drafts Filetable. I can see this in the Object Explorer.

    filetable_c

    I can use the same “select data” feature from Object Explorer on a Filetable, just like any other table.

    filetable_e

    I get the results, and as you see, I have a few rows in the table.

    filetable_f

    These are actually the files I see in the share.

    filetable_d

    Inserting Data

    One of the advantages of Filetable is that you can use Explorer (and any tools that use the same Explorer APIs) to move data in and out of a table. However that doesn’t preclude you from using T-SQL.

    I can use a script to insert data into the table, just as I might with Filestream.

    INSERT INTO AuthorDrafts(name, file_stream) 
Values (
        'circle.jpg'
      , 0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4D002A000000080004011A0005000000010000003E011B0005000000010000004601280003000000010002000001310002000000120000004E00000000000000600000000100000060000000015061696E742E4E45542076332E352E313000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C18940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007FFFD9
      );
go

     

    As you can see, this command works fine:

    filetable_g

    If I then look at the share, I see my file:

    filetable_h

    Retrieving Data

    As shown above, I can use SELECT queries to return data from a Filetable in T-SQL. However, I have a share as well, and I can cut, copy, paste, and open files from the share just as I would any other file in the file system.

    filetable_j

     

    If I open the file in Paint, I see my image:

    filetable_i

    Summary

    Working with files in a file table is easy, and while many people will use Explorer functions, you can use T-SQL as well to insert, or retrieve the data as you choose.