Author: way0utwest

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.

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

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

These are actually the files I see in the share.

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:

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

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.

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

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.

September 11, 2013

The Future of Knowledge Measurement

This is part 3 of a 3 part series of thoughts on certification and Microsoft technologies.

We’ll never be able to completely and accurately measure a person’s skills in technology. At least not in any cost- and time-effective way. Ultimately we want to come up with some way to weed through candidates and ensure they have a minimum aptitude for technology and some level of skill in the areas that are important to us. We want a way, with some level of confidence, to say that a person who has xx certification knows yy skills.

In the Microsoft world we can be sure that our platforms and technologies will change at least every 2-3 years, with major or minor revisions to all parts of the product we use. We might see minor tool changes, but fundamental feature enhancements or vice versa. However even when there are major changes, the revisions to the effective way we accomplish tasks doesn’t change much. It evolves, and I think that a core set of skills can be measured, and more importantly, scored.

How we do that, I’m not sure. As Brent Ozar said, however, the experiment must go on. We, as an industry and group, should be finding ways to assess our community, and drive forward our profession. I’d like to think that we could build an open source framework that allows for the presentation of a situation, and the evaluation of a result. It could be a framework like tsqlt, which allows us to write tests that can be evaluated by a scoring system. By taking a script of some sort, and comparing it to a “question”, some automated measurement would be able to determine if the question was answered (or partially answered).

Our community could easily build a bank of hundreds, if not thousands, of questions. Want to evaluate someone? Download 50 questions, drop someone in a room for an hour and see how much they get done. They might not finish, which would be a good test in and of itself. Run their answers through a scoring engine and get a report back. With tags, we could easily separate questions into a variety of packs that employers could use to test certain areas. Testing core skills, without too much worry about version specific items would allow questions to live for years. Heck, with the age of some SQL Server instances out here, I bet some companies still need SQL Server 2000 based tests.

Ultimately I don’t think Microsoft will properly build and maintain a framework to evaluate candidates. They have too much incentive to cheat. They can fool lots of employers with easy to pass, paper diplomas and turn a profit with lots of easy certifications that sound good, but don’t really test skills. The future of measurement in technology will be like it is in many other fields, with independent bodies that provide a minimal level of educational skill for most individuals. It will consist of granular tests that measure skills, in real situations, not question and answer trivia. Some people will slip through, some will cheat, but it will work well enough when it falls out of the hands of vendors.

Until that time, all you can do is prove your own skills, in person, through your publications, or with lots of good, valuable answers given to others.

Steve Jones

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.

Windows Media Video ( 25.9MB)

MP4 iPod Video ( 29.5MB)

MP3 Audio ( 6.0MB)

Feeds are available at iTunes and Mevio

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

September 11, 2013

T-SQL Tuesday #46–The Rube Goldberg Machine

It’s T-SQL Tuesday time again. This is the monthly blog party where we all write on a topic. This month the topic is the Rube Goldberg Machine, as it might relate to SQL Server. You can read the invitation linked above from Rick Krueger and write your own post if you choose.

Just be sure you post it on Tuesday, Sept 6, 2013.

Catching the Cheaters

A long, long time ago, in a county just to the west of here, I worked for a small company as a DBA. I had to support our production operations, as well as a team of 10-12 developers that were building our main company application. Since we were an ecommerce type company, this was an important application for the company.

We were working in an high incremental way, probably similar to an Agile methodology, and deploying changes every week. However when I started the deployments weren’t smooth. Our developers were handling them, often bumbling them a bit, but able to fix things in an hour since they had done the development.

Not a good separation of duties and it was becoming more of an issue as our boss wanted minutes of downtime, preferably single digits. I tracked down the main cause as a haphazard development style where changes were made to databases, instances, and IIS without much tracking going on. The IIS changes were easy: the developers had their rights removed from production and even test machines for configuration changes. However the database required a bit more work.

As this was SQL Server 2000, we had limited ability to audit and manage rights. We also required our developers to have rights to the development machines in order to create test databases and work on importing data. We also wanted to ensure that developers moved quickly, but I needed a way to keep control of the instance.

The first contraption I used was a simple one. I loaded the output of sp_configure into a table on the instance. I had created an administrative database that I used to track backups, and I added this information in there. I then created a job that would run sp_configure every day, compare the values to my table, and then alert me to changes. It would also update the table with the new, current values.

This allowed me to catch various changes that developers were making when they tinkered with the server to “make it run faster”. I didn’t prevent changes, and if their changes worked, we’d deploy them to test (and eventually production), but this allowed us to document them and be aware.

This worked well enough that I build a few more mousetraps. Catching schema changes is hard, as there isn’t good auditing in SQL Server 2000. However there is a “version” number for each object that is incremented when it’s changed. I built a similar audit/job system for our sysobjects table, but ran this every hour, catching changes. When I was alerted, which was more days than not, I’d email the team, track down the person changing things, and make a note for our current branch of work.

This worked great in that development wasn’t slowed, but I was able to account for all development changes and slot them into the current, or future, development deployments. In a few months we reduced our deployment time from about an hour every Wed night to less than 5 minutes.

Sometimes the mousetraps actually help the mice work better.

September 10, 2013

What does certification achieve?

This is part 2 of a 3 part series of thoughts on certification and Microsoft technologies.

The idea of certifying someone as having a skill is almost as old as the idea of teaching someone a skill. I’m sure as soon as a person had the idea of teaching someone for compensation, others wanted some assurance that the person had learned the skill. That was probably the first time that a test was developed, graded, and scores passed out.

We all know that a certificate or a test score doesn’t imply any level of competence at a skill. If I had to weld two pieces of metal together as a test, successfully completeing this wouldn’t imply that I could weld any two other pieces of the same metal together (size, scale, etc. matter). It wouldn’t even imply that I’d do as good a job welding the 99th and 100th pieces together in a week as I’d done during the test. The same would hold true in almost any endeavor, but we still have tests, grades, certifications, diplomas, and awards in many different fields.

Is IT that different? In one sense it is. The technology field seems to change so often, and the bars for entry (and thus hiring) are so low that it’s difficult to set up standardized tests, often because it’s not cost effective. Medicine and law change constantly, but their tests slowly evolve, and they certainly don’t change at the rate of SQL Server versions and tests. It’s also more cost effective to create new revisions of tests in these other fields when the candidates have made a large investment in their education and regulatory agencies require licenses to practice in these other fields.

However I might argue that technology doesn’t change that much. The idea of backing up a SQL Server database, rebuilding a clustered index, adding a login, querying for duplicates, and more haven’t changed much in the two decades I’ve worked with the product. The actual syntax might be different, but are we hiring people that remember syntax or accomplish tasks?

I think that’s the point of certification. It should be a method that gives others confidence that an individual can accomplish a task, or has some basic skill. I’d argue the current set of tests, questions, and even structure of the MCSE/MCITPro/MCSA whatever doesn’t remotely do that. It doesn’t test skills, tests knowledge at the base level of memorization, and fails to provide a basic bar that we can be sure everyone has met.

Perhaps those aren’t Microsoft’s goals. Perhaps they value their profits higher than the certification that individuals hold skills, perhaps they view these designations as a part of their marketing effort to sell software. Perhaps they have other goals. All I know is that as long as employers ask for these certifications in job postings, as long as employers pay for tests and candidates take them, why should Microsoft change?

If we all believe the emperor has clothes, does it matter if he really wears any?

Steve Jones

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.

Windows Media Video ( 22.8MB)

MP4 iPod Video ( 26.2MB)

MP3 Audio ( 5.3MB)

Feeds are available at iTunes and Mevio

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

September 10, 2013