If you want the back story on what this topic is all about - http://sqlblog.com/blogs/argenis_fernandez/archive/2012/03/05/t-sql-tuesday-028-jack-of-all-trades-master-of-none.aspx <-- ha webforms

Argenis went on some blabfest about how life got better for him after he started to really focus on SQL Server. He's a SQL Server certified Master, is incredibly helpful and one of the reasons I don't over commit myself to specific mastery. "But Rob, how can you say that? MCM's are reveled by everyone!!!" It's true. I take nothing away from a MCM and it is one of the few certs that are actually respected. But it's also not for me. Most of my shining moments as a DBA have nothing to do with a mastery of say quirky transaction scope of full text indexes or being the Chuck Norris of international merge replication scenarios and everything to do with knowing how things work on the whole. The more I know on the whole, especially with concepts, the better a DBA I am. When I'm getting Win-Auth errors, I don't need to be a Kerberos God ("When someone asks if you're a Kerberos God, you say YES!") to start to troubleshoot, but I need to know enough to ask the right questions. Being a jack of all trades typically lets me know when I'm in over my head and need to call in an expert - like my good friend Google or Argenis... which brings me to my next point:

As someone with an insatiable appetite for pizza...err learning, technology simply moves too fast and brings me too much joy to try to really master a facet of a single product. That is not a global rule, that is my personal choice and more importantly a personal defense mechanism from burnout. That is another thing that is fun about technology. There are usually many correct paths to success and interweaving those paths can build some really incredible software... which brings me to my final point:

As someone with no shortage of ego (I am a DBA afterall!) if you want to go down the path of being a jack of all trades, you better be friends with Masters and put your ego to the side and ask for help. There are few things more dangerous than not asking for help when you need it. We all write code that doesn't work the first time, we all have asked silly questions before, and we (as individuals) don't know everything.

Ok, well I'm not leaving SQL Server professionally (or financially!) but another relational database platform has clearly captured my heart. That platform is Postgres! Through a combination of relentless potshots at MySQL and emotional bribes where I feign interest in Rails, I was able to trick Rob Conery and the Tekpub team into not only moving Tekpub to Postgres but also let me record a Postgres series with them. In this series, we share the data love on a platform that is open, fast and incredibly flexible. The ease at which Postgres addresses many of the day to day issues I deal with as a SQL Server DBA is mouthwatering to say the least. Luckily for you, it isn't just about me or DBAs which is why the head App Dev(il) himself Rob C came along for the ride! He is quick to ask questions and to put in his tidbits and raise issues that would relate to you, the developer. That's right friends, it isn't just a series that shakes the hand of Postgres, we walk up and give it a big sloppy kiss. So check it out and have fun watching "The Robs" bring you a double felony of database knowledge.

Hello Postgres

Show notes and scripts will be put up on GitHub when I get back in town.

An issue I run into quite a bit is unnecessary sorting in the database. I'm not talking about the sort of 'Get last 5' type of sorting where you need to sort to get a valid result set. I'm talking about the 'Hey Database! I want some data...and I'll probably throw some business logic in you... and while I'm here, how about we throw the presentation layer in as well and you sort the results for our UI!'

Nut Kicking:
In the same way that AppDevs outnumber DBAs, infrastructure wise there are typically way more web/caching servers than there are database servers. This is mostly due to the fact that like a decent DBA, a decent database server is expensive. AppDevsWebservers in general are cheaper, have less memory and don't need to be as awesome as a DBAdatabase server.

Code Please:
Lets take a look at some execution plans/cost so you can view 'ORDER BY' the same way I do.
Below is a simple example of selecting some badges by userid, and then display them alphabetically for the user to view :
SELECT Name
FROM dbo.Badges
WHERE userid = 91254;

SELECT Name
FROM dbo.Badges
WHERE userid = 91254
ORDER BY name DESC;
And this is the execution plan it creates:

On the bottom, do you see the glyph that is "Sort Cost: 15%"? As well as a difference of almost 10% in general between the two queries? Removing those "sort"(har har har) of thing adds up...like a lot.

Just Fix It
In C#, you have these things you can use called Ordered Enumerables and they are really easy to use... take a look:

public IOrderedEnumerable<dynamic> GetBadgeByUserId(int badgeid)
{
	var table = new Badges();
	var badges = table.query("SELECT  Name FROM dbo.Badges WHERE userid = @0", args: badgeid);
	return badges.OrderBy(x=>x.Name);
	//return badges.OrderByDescending(x => x.Name);
}

That wasn't too hard was it? In the above example, it is making a call to the DB (using Massive) and sucking the results into 'badges'. That is where it breaks off its relationship with the database, and sorts the results in 'badges' and returns them to whatever was calling it.

Does this make us happy?
I'm happy because you're not putting extra load on the DB. You're happy because you have some sorted data and can close a help ticket... everyone wins right?

But But But, our servers are busy too!
Ahhh, but perhaps you're one of those clever AppDevs who says that if the DB is getting over worked, then the webservers are too! Since I don't have the webserver metrics, I can't really object to that. But what I can say is: "Yo, that's cool. Since you are a programmer.... program up some javascript to sort the results in the UI. Then, the webservers and the db can both go listen to dub step or whatever servers like to do in their spare time."

There are a ton of ways to do this:
http://lmgtfy.com/?q=sort+a+table+with+jquery

Doing it on the front end also works great if you are stuck using some particular DALs or ORMs that take a bulimic approach to data retrieval IE - Eating everything in site and then barfing it out to the app.

I am a huge fan of Red Gate SQL Source Control because it makes source controlling/deploying your database easier. I am aware of the MS Database Projects abomination application and the short version on that is that I have deemed them unworthy for my use. There are lots of tutorials and examples of using RG SSC with centralized version control systems like Subversion or TFS and it does work wonderfully with them. Recently, my workplace decided to ditch TFS and move to a more modern distributed version control named Git. Like most disruptions to your workflow, there are a few growing pains. This post is going to cover installing Git on Widows, hooking it up to github.com and showing some basic actions as we change our database.

Install Git:
Download the full installer for Git and install it.
While you are mindlessly clicking 'next' make sure you integrate with CMD. This is critical for RG SSC and just a convenient thing to do in general. The important part will look like this:

Aside: I'm a converted fan of posh-git. Using things like the Git GUI or Tortoise GIT will be incredibly tempting, and perhaps for initial familiarization, it might be a good idea to use those to visualize some concepts. That being said, it is really in your best interest to move towards the CLI and posh-git is very helpful in that regard. It isn't nearly as bad as you think once you get going.

Repo Hosting
Once you have Git installed, you will want to hook up to some hosting. An incredibly popular and easy to use one is Github.com but there are plenty of others.

Repo Man
In Github, you will want to create your repository and assign your SSH keys. This process can be a bit more than you are used to at first, but GitHub as great at giving you some hand holding instructions: http://help.github.com/
Once the repo is initialized, I like to create a folder for the App and a folder for the database like so:

You don't have to do it like that, but again, that is the way I currently like to do it.

Lets hook up the DB!
Why use the Adventure Works database? Since it is a bit of a marketing database used to show both neat and inane features, we're hooking it up just to make sure all types of various objects work just fine with RG SQL Source Control.
From there, go to SSMS and link up the DB to your local repository:

Command Line Interface ... or whatever
Back in powershell (or whatever client you are using) create a new branch for updating our proc. git branch dbobjects
Note: We don't have to create a new branch. We could very easily just keep doing this in the 'master' branch. I find that creating of the branches to be a good habit and a handy organizational tool. Branching is one of those features that really shines in Git so lets put it to use.
Go into that branch: git checkout dbobjects

Now, we go back to SSMS and commit our changes:

Cool! Our "dbobjects" branch has been committed locally, now we just need to merge it into our master branch!

From there, go back to master: git checkout master
and then merge into master our dbobjects branch: git merge master dbobjects
After we have merged, we can delete the branch we were working in by running:
git branch -d dbobjects
and the process will end up looking like this:

If you look on Github, you will see that nothing has changed there. Why is that? That is because all the work we have done has been locally. This is the essence and the speed of a distributed control system. We can commit, fix, break all we want without getting everyone else mad. However, at some point, we will need to send our changes off so that others on the team can use (laugh) at them. We do this by "push"ing to the origin: git push -u origin master

Now, if we look at Github, we see our changes in the main repository, and if we go to check it out again:

We can see them there for people pulling down changes. You've done it. Now you too can participate in the source control fun with the AppDevs and make nerdworthy comments about rebasing, merging and pushing master.

I had the pleasure of being able to write up a delightfully ephemeral piece on SQL Test for Simple-Talk. This is a fun little tool and I'm anxious to see just how much David and his team can improve it.

http://www.simple-talk.com/sql/sql-tools/sql-test-seeing-red-change-to-green/

In other news, being published on Simple-Talk is also another thing Troy Hunt won't be able to 1-Up me with.

"Postgres? WTF man, you're a SQL Server DBA!" It's true, I am a SQL Server DBA but even I have to admit that the Postgres team is doing some wildy exciting stuff. With that in mind, lets take a quick look at just how quickly we can replace SQL Server with a free/open full bore Enterprise database system like Postgres.

One of the first things you will notice is Postgres is way easier to install than SQL Server. The SQL Server install is born from a truck stop romance between TFS and Sharepoint that someone found in a garbage bag in a dumpster and burned to a DVD. Postgres takes a different angle and opts for a 'less is more' type of approach. One of the big things you will want to pay attention to is the password you give the default 'postgres' account. Think of him as the SA account in SQL Server as he too wields a mighty big stick.

Once we finish the installer, lets open up pgAdmin III (henceforth known as pgAdmin). If you are accustomed to SSMS with SQL Server and always wished it went on a diet... then you might like pgAdmin. Be warned though, pgAdmin isn't SSMS on a diet... it is like SSMS with Anorexia. Once pgAdmin is opened and you are connected, then drill down to 'databases' and do a 'new database' and use the GUI to make your fancy new demo database, or just run the raw sql:

 
CREATE DATABASE "Demo"
  WITH ENCODING='UTF8'
       OWNER=postgres
       CONNECTION LIMIT=-1;
 

Next, lets hop in that database and throw up some tables/data:

 
CREATE TABLE "Colors"
(
	colorid serial NOT NULL,
	colorname character varying(50) NOT NULL,
	constraint pk_colorid PRIMARY KEY (colorid)
)
WITH (OIDS=false);
 
INSERT INTO "Colors" (colorname)
VALUES ('red');
INSERT INTO "Colors" (colorname)
VALUES ('blue');
SELECT * FROM "Colors";
 

So why the quotes around the table names you might be asking... well, SQL Server has a bit of Honey Badger approach to what case you assign names and Postgres treats it like a pretty big deal. Luckily, overcoming it isn't a big deal, we just double quote it. Now, if you don't have years of tsql or camelcasing drilled into your brain, you can easy just do everything in lower case and not fight the double quote dragon.
What else sticks out there... oh Data Types! You will have to adjust to some variance in data types, but in a good way. Postgres has a lavish assortment of data types for you to fall in love with and one of which is using 'serial' instead of the whole IDENTITY(1,1) you might be used to in SQL Server. There are also some really cool things called Sequences in Postgres which are blazing fast and give you the value of the ID before you insert it. DBAs like them because it can get you AppDevs away from fatty GUID primary keys.

Fire up a new MVC3 app and hop into the Nuget console. From there, we're going to add our Postgre provider with "Install-Package Npgsql". Now that we have our vital provider assemblies, lets go mess around in the web.config.

 
<configuration>
	<system.data>
		<DbProviderFactories>
			<add name="Npgsql Data Provider"
        invariant="Npgsql"
        support="FF"
        description=".Net Framework Data Provider for Postgresql Server"
        type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
		</DbProviderFactories>
	</system.data>
 
	<connectionStrings>
		<add name="pg" connectionString="Server=127.0.0.1;Port=5432;Database=Demo;User Id=postgres;Password=dontusethisaccount;CommandTimeout=20;" providerName="Npgsql" />
	</connectionStrings>
 

Yeah! We have our provider factory rocking and our connection string. Yes, I am using the postgres account in this demo and yes that is wrong/stupid, but I'm having so much fun playing with the Postgres engine that I decided to take a shortcut there for this entry.

So our color app is pretty quick and dirty. In keeping with that spirit, I'm going to use Massive as my ORM of choice. It already has a Postgres version ready to go. PetaPoco has some very nice Postgres options in it, but Massive is very quick/easy and the Expandos makes all the strong-typers uncomfortable and I love a good code-trolling.

In the Model folder of our app, I create a new class file called Massive and I copy/paste the Postgres version into it, and I also created a new class file called "Colors" that I pasted the following in:

 
	public class Color : DynamicModel
	{
		public Color() : base("pg", "Colors", "colorid") { }
 
	}
 

All wired up! In the home controller, we'll throw some code to play with our table in:

 
var table = new Color();
var colors = table.All();
ViewBag.colors = colors;
 

And then in our view:

 
@foreach (var color in ViewBag.colors)
{
	 @: @color.colorid @color.colorname
}
 

F5 and what do we get? BOOM!!! "Error 42P01: relation "colors" does not exist"
So remember when I started our Colors table with a capital C? This is where it can come back to bite you... and also one of the reasons I absolutely love Micro-ORMs. Fast, malleable, and so easy a DBA can use it. Let's hop into Massive and throw some quotes around our table access:

 
string sql = limit > 0 ? "SELECT TOP " + limit + " {0} FROM \"{1}\" " : "SELECT {0} FROM \"{1}\" ";
 

and Viola! It works! In pretty much the time it takes to install SQL Server, we're already rocking our stunning and highly valuable colors app!

What a great link bait title right? The purpose of this post is to give my reasoning for joining the Friends of Red Gate program. So without further ado:

Education - I simply love software development with all my heart. Much of this love is channeled towards learning. In the SQL Server arena there are a couple resources that just stand out above the others... I'm talking about SQL Server Central, Simple-Talk, the wealth of information that Brad McGehee(blog | twitter) puts out, Grant Fritchney(blog | twitter), and the various books. It became quite apparent that a lot of what has made me such a successful DBA is because Red Gate shares that same passion for building great software.

Community - Another manifestation of this love shows itself in community events. Whenever I put on an event or attend one, there is one company I know of that consistently steps up to the plate. Red Gate. Even during slower economic times, when some sponsors required coaxing and justification, when it came to community, Red Gate was always on board. While they are a company and these events are advertising or promotional opportunities, that aspect of it never comes on. No matter how big or how small the event, I always feel like they genuinely want all of us to learn and be better at our craft. I have a tremendous amount of respect for that company on this alone.

Tools - When I first downloaded and installed the beta of SQL Source Control, my knees became weak. In the chaotic world of database change management and DDL triggers, this tool absolutely blew my mind. When I started playing with this tool, it was like I had just won a lifetime supply of pizza and cupcakes. Everyone has schema/data compare tools or backup tools, but a native source control tool? Way to step up to the plate Red Gate. I wanted to email customer support and ask for SQL Source Controls ring size because I was hooked. We use TFS at work, but on the side I use a combination of Subversion and GIT... SQL Source control doesn't even care - it just does its job. The quality, ease of use, and the need that tool fills made me think that Red Gate loves software development just as much as I do.

I am not a salesman and I don't try to be. I'm not trying to sell you Red Gate tools. All I can do is tell you how they have worked for me and let you make the best decision for your situation. One message I do hope I convey is that the next time you are at an event that Red Gate sponsors or take advantage of any of their various learning resources, take a moment to thank them. They do a lot of things in front of and behind the scenes to help all of us reach success without any of us being forced to buy their tools and I find that amount of passion in a company both admirable and rare.

The question emails are starting to pile in (which is a good thing!) so this post is basically a way to centralize links/answers. In the show, I say a few times "we'll see this later" (like with SQL Server Profiler) and we never see it... that was mostly my fault. For some reason I kept thinking I would be able to squeeze 5 hours of content into 1 hour and was wrong. If there are aspects of SQL Server that you would like to see more of, as well as real world problems that you may have and would like to see solutions to, then let Tekpub know. We'll see if we can hack out real world solutions wrapped in a warm blanket of statistics and opinion.

Questions:
How do you get the stats to appear in the "messages" window?
Those stats come from the following commands:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
If you don't want to turn it on for every query/window, you can do what I do and set them to always be on in Management studio, you can do this by :
Tools -> Options -> Query Execution -> SQL Server -> Advanced
then check:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;