Voice of the DBA

Category: Blog

Slipping in a Break

I know some people like long holidays, but I tend to prefer short ones. Today is one of those breaks for me. I’m coming back from Pensacola and SQL Saturday #132, taking the day off as I spent Sunday and the morning with my daughter.

This was a combination of work on Fri night/Sat for the event, and then a mini-vacation on Fri morning, Sunday, and Monday morning for the two of us. A little Daddy/daughter time that we both enjoy and look forward to.

Remember to get those breaks in when you can, and take advantage of the chance to spend time away from work if you are traveling and can fit it in.

June 11, 2012
The Exceptional DBA Awards – 2012

Who will be the Exceptional DBA of 2012? I’ll be judging.

It’s hard to believe that it’s time for the Exceptional DBA awards once again. It was just a few months ago that I watched the presentation of the award to Jeff Moden in Seattle. However time flies, especially as I get older, and the nominations are once again open for the 2012 award. You can enter yourself or someone else between now and June 29th, so take a chance and enter if you think you’re doing a great job as a DBA for your company.

The previous winners have been ordinary people, just like most of you out there, that go to work, do their job, and are relatively unknown. They are exceptional, however, and their applications have shown that. These are people that go above and beyond what others do. They under-promise, over-deliver, and they help others. Those are the types of traits and characteristics that I get from their nomination forms. It’s interesting to see that many of our past finalists not only entered the contest, but were entered by a co-worker. That in and of itself says something about their work when a friend is willing to nominate them.

I’m judging again this year, along with Brad McGehee, Grant Fritchey, and Rodney Landrum. We’ve all down this before, and we all have lots of experience with people that stand out, and do not stand out, at work and in the community. We each have a slightly different take on the award, but we all get together and debate and decide who the finalists will be.

From my perspective, I want to see someone that does two main things. First, they’re effective. I have always been very successful at different jobs because I get things done. I ensure the systems work, and I solve problems, without getting bogged down in arguments, religious wars, or personal conflicts. My job first and foremost is to ensure that things work as people expect. I’m looking for those traits in an Exceptional DBA. Second, I want to see someone that makes others better, that helps the community. It doesn’t have to be by speaking, or blogging, or any one particular thing, but there are lots of smart people out there that get things done. I think the exceptional ones also make others better, either inside their company, in their area, or throughout the world. They share what they know and help lift up everyone else’s skills.

If you think you qualify, take some time and put together a nomination. If you think there’s someone that’s helped you and does a great job, nominate them. Make sure you spend a little time drafting, and proofing what you write. Good communication is essential here, and we all are writers for a living, so make sure you send us your best effort.

Enter today, and you may be the Exceptional DBA of 2012.

June 11, 2012
Contained Databases – Preventing Collation Conflicts
One of the demos from my Contained Databases talk looks at the issues you can have when your database collation does not match your server collation. I’ll walk through the issue here. I’ll show the issue, and then the fix with contained databases.

First, let’s create a database and a table:
```
-- create db without containment
CREATE DATABASE ucdb2
 COLLATE Japanese_CS_AS
;
go
USE ucdb2
;
go


-- Create Unicode Table, add a row
CREATE TABLE MyTable
( mychar NVARCHAR(200)
);
go
INSERT MyTable SELECT 'This is a Japanese Row'
go
```
My server collation is shown here (SQL_Latin1_General_CP1_CI_AS:

Now let’s create the exact same table in tempdb.
```
-- create temp unicode table
CREATE TABLE #MyTable
( mychar NVARCHAR(200)
);
go
INSERT #MyTable SELECT 'This is a Japanese Row'
go
```
If I try to work with these two tables together, I will have problems. As an example, let’s just union query these two items together.
```
SELECT 
  'udcb2'
,  mychar
 FROM MyTable
UNION ALL
SELECT 
  'tempdb'
, mychar
 FROM #MyTable
;
go
```
I get an error, as shown here:

The collation error occurs because the query optimizer can’t decide which collation to use. You can easily fix this, as I’ve blogged about before with a collation clause.

However contained databases mean you don’t have to change code. Let’s do the same thing, this time with a contained database.
```
-- create db with containment
CREATE DATABASE cdb2
 CONTAINMENT = PARTIAL
 COLLATE Japanese_CS_AS
;
go
USE cdb2
;
go

-- Create Unicode Table, add a row
CREATE TABLE MyTable
( mychar NVARCHAR(200)
);
go
INSERT MyTable SELECT 'This is a Japanese Row'
go

-- create temp unicode table
CREATE TABLE #MyTable
( mychar NVARCHAR(200)
);
go
INSERT #MyTable SELECT 'This is a Japanese Row'
go
```
Now if I run the same statement, I see:

The contained database has correctly resolved the collation issues.

You can check the collations with sp_help with the two table names.
June 7, 2012
Creating a Filetable
How do you create a filetable? I assume you’ve enabled Filestream and created a filegroup for your filestream and filetable data. Then you just do this:
```
-- Create a filetable
CREATE TABLE AuthorDrafts 
  AS FileTable
GO
```
The only optional part of this statement is the table name. No other options, no columns, no schema needed. The FileTable has a fixed schema, which is mostly metadata about the files that you put in it.

If I were to select from this table, I’d use this statement. I’m not showing all the columns in the results since there are a lot, but they are in the select.
```
-- check the table.
select 
   stream_id ,
          file_stream ,
          name ,
          path_locator ,
          parent_path_locator ,
          file_type ,
          cached_file_size ,
          creation_time ,
          last_write_time ,
          last_access_time ,
          is_directory ,
          is_offline ,
          is_hidden ,
          is_readonly ,
          is_archive ,
          is_system ,
          is_temporary
 from AuthorDrafts;
go
```
Most of these are really meta data about the file. If I were to drop a table in the share, I’d see results like this:

Putting files inside the table is really a drag and drop from Windows. I can get the share name for my filetable from :
```
-- check the share
select  FileTableRootPath('dbo.AuthorDrafts');
go
```
If I paste this in Explorer, I see my file:

I can drag and drop, or use any scripting commands (Powershell, VBScript, etc) to move files in and out of this share, and they will appear in my table.

It’s that easy to start working with FileTables. How you use them in your application? That’s a whole other series of posts. I’ll work on a few examples you can use over time.
June 5, 2012