June 2009
Mon Tue Wed Thu Fri Sat Sun
« May   Jul »
1234567
891011121314
15161718192021
22232425262728
2930  

Month June 2009

Entries 10 Total

Columbus Give Camp

Jun 15, ’09 6:41 AM

The Columbus Give Camp site has gone live.

What’s a give camp? Basically, to paraphrase the about page, a give camp is an opportunity for developers to give back to the local community by contributing time and effort to create custom software for non-profits.

How can you help?

Why am I plugging this?

  1. It’s a cool idea
  2. I designed and built the database
  3. I plan on being there

What are you waiting for? Head on over to Columbus Give Camp and volunteer!

Comments (0)

Links for the Week of 2009.06.12

Jun 12, ’09 7:14 AM

SQL Server

Denis Gobo provides a double dose of dynamic SQL hotness via Changing exec to sp_executesql doesn’t provide any benefit if you are not using parameters correctly and Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec. Following his tips in here will net you some considerable benefits in how your dynamic SQL behaves and performs in your production applications. The best part is that he provides a huge amount of example code to back up everything he’s saying.

Free SQL Server tools that might make your life a little easier Mladen Prajdic maintains an amazing list of free tools to help data folks get their job done. SSMS Tools Pack (which Mladen created and maintains) is incredibly helpful. I suspect that if he finds these other tools useful, they’ll probably save you many many hours over the years to come.

SQL Server Troubleshooting Tips and Tricks This isn’t a blog post so much as it is really important to keep around. It’s just a list of tips and tricks to keep things easy and painless with SSMS. If you didn’t check out the pain of the week webcast with Michelle Ufford and Brent Ozar on SSMS, you should do so when you get the chance, it’s well worth it!

More SQL Server Training Videos Training. Free. Videos. I’m in there, that’s really all the reason you need to visit that link.

BONUSTen SQL Server Functions That You Have Ignored Until Now Bonus link from Denis Gobo, once again, covering some great, little known, SQL Server functions.

Development

Unit Testing is Not a “Figure It Out Later” Read this. Read this now. Do it. We’ll still be here. Stop what you’re doing and read this. I cannot stress how important it is for you to read this, even us data folks. If I could put a blink tag around this paragraph, I would. (Thanks to Stephen Wright for finding this link.)

Defensive Programming – Assumptions Must be Guaranteed or Tested Aaron Alton blogs about the importance of defensive programming. While this is specifically from a SQL Server standpoint, it’s important no matter which language you’re using. In fact, Aaron proves an important point – you can put any development practice to use in any language.

Stuff & Things

Improving your intranet – keep it sustainable using kaizen – Patrick Walsh talks about using kaizen to produce evolutionary improvements to an intranet. You could do this with anything, really, but it’s a good read either way you look at it.

I cheated and stole the last two links from Lifehacker, but they’re just too good to not share.

CopyPasteCharacter.com Makes Special Characters Easy to Paste Yup, easy. Nice. Easy mode: engage.

LiberKey Installs 200+ Portable Applications I have too many thumbdrives laying around the house. Now I have a use for them.

Comments (1)

Splitting Strings in SQL

Jun 11, ’09 9:10 AM

Let’s say that you have a column that contains some goofy data and it looks like this:

USA/Ohio
USA/Indiana
USA/Iowa
UK/Scotland
UK/England

So, how would you typically split such a string to only get the part after the ‘/’?

Well, here’s how I did it:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
  DROP TABLE #tmp;
GO

CREATE TABLE #tmp
(
  s VARCHAR(255)
);
GO

INSERT INTO #tmp (s) VALUES
('USA/Ohio'),
('USA/Indiana'),
('USA/Iowa'),
('UK/Scotland'),
('UK/England');

SELECT SUBSTRING(s, CHARINDEX('/', s) + 1, LEN(s)) -- this is what does all the work
FROM #tmp;

But, apparently, there’s a simpler way:

SELECT PARSENAME(REPLACE(a, '/', '.'),1)
FROM #tmp;

It’s a bit hacky, but it works.

Basically, it takes advantage of the PARSENAME function to grab specific sections of a period delimited string, as it it were a SQL Server object.

Now you know. Paypal me some money if you find this useful and pass it off as your own work of genius.

Comments (3)

Columbus PASS/SQL Server User Group Meeting Tonight!

Jun 11, ’09 7:28 AM

This is just a quick reminder to everyone that CBusPASS (the Columbus, OH PASS Chapter) will be meeting tonight.

Details here, however the general gist is that Brent Ozar and I will be talking about using SQL Server Express edition in a presentation titled “Developing Something for Nothing”.

Comments (0)

What I’ve Learned at PASS

Jun 9, ’09 5:16 PM

So, there’s this contest going on. So, what’s the contest? Write the best thing you’ve learned at a previous PASS conference and you could win a free registration or free hotel stay at PASS Summit 2009!

I went to my first PASS Summit many many months ago in November of 2008. I didn’t know many people in the SQL Server community, but I knew a few people. Throughout the PASS Summit, I spent time getting to know people. I met a lot of great people at the Summit and I’ve stayed in touch and become friends with many of them.

But, the best thing I learned at PASS was that there’s always something to learn from everyone. We’re all part of a community and we get better by learning from each other.

See? It’s just that easy! You could win a free registration to PASS. I could win a free registration. Maybe we both could win a free registration and meet at PASS and hang out!

Comments (0)

Building a new SQL 2008 box

Jun 9, ’09 5:44 AM

I have a lucky, and rare, chance coming my way in about a week: I will be given the opportunity to design and install the production SQL servers at work.

Here’s the fun part – I’ve never done this before, at least not on a production machine.

What best practices, secret tricks, or hare-brained schemes do you, my fellow DBAs, follow when you put together a new SQL Server installation?

This could be anything from drive configuration, something about SANs, trace flags, whatever. You name it, I’ll evaluate it and confer with other smartypants types and once the install is done, I’ll let you know how everything goes.

Oh, the hardware is going to be a dual CPU quad Xeon machine from Dell with 16GB of RAM. And we do have a SAN of some nature, but that’s a mystery box.

Edit: So, what will these machines actually do? They will serve reports off of an OLTP schema that is updated every 15 minutes through replication from our master database server. There will be two instances on each machine – one that is live and one that just sits there, doing nothing. They mirror each other, in addition to the geographic fail over that somebody else is configuring. We do some heavy batch processing in the early morning hours, but during the day the machine is largely doing reads and whatever writes that the replication brings about.

The database that holds the majority of the reporting data is about 45GB and there is approximately 25GB of metadata and summarized reports (the batch processing, naturally) in a second database.

Comments (1)

Links for the Week 2009.06.05

Jun 6, ’09 6:33 AM

Important Note: These are yesterday’s links. Not today’s. That’s why the date is wrong.

SQL Server

Really Search Cache for Execution Plans Jason Strate revists his code that finds the last plan that was used for a procedure.

Reasons for NOT using Varchar(MAX) Pat Wright brings back the argument of why you should or shouldn’t use varchar(max). You can clearly tell from the title how he feels. I’m quoted in this one, so you probably know how I feel. Read it. Learn it. Love it. SQL! (Yes, I know I said ‘field’ instead of ‘column’. Bite me.)

Development

Fighting technical deb with the wall of pain Technical debt is an insidious foe. Technical debt is that extra effort you need for future development because you made a quick and dirty design choice at some point in the past. This crap is poison.

Coding like it’s 1999 Cameron Moll put together a great overview of why he’s moved back to using the HTML 4.01 DOCTYPE. Just don’t use tables, okay folks?

Poor man’s guide to database optimization – by the Marquis de Sade Ever wonder if your code is being really crappy and executing N+1 selects for everything in a collection? WONDER NO MORE! With Ayende’s newest bit of code wizardry you can have a configurable wait time between each query executed. All of a sudden, you too will be dropped into your DBA’s nightmares as you realize that some programmer’s horrible, horrible code is slowly destroying the database!

Stuff & Things

Calvinball Projects Calvinball is the greatest game ever. Running a project like it’s a game of Calvinball has to be fun, right?

Feng Shui On Steroids: Design Your Space to Achieve Your Goals Jonathan Mead writes about how to use your environment to influence your ability to meet your goals. This looks like a great way to go about hacking my workspace and make it easier to get myself into a working mindset.

Lifehacker Pack 2009: Our List of Essential Free Windows Downloads Free software. Good stuff in here. Get it.

Links (6/4/2009) Steve PIetrek’s list o’ links from earlier this week has some gems in it, especially for software developers.

How to Lose Weight by Eating More Everything in here is true. “Diets” suck. They don’t work forever. There are good ideas in here on how to change the way you eat to be healthier.

Comments (1)

This is MY Island

Jun 5, ’09 10:48 AM

Jason Massie (twitter) has tagged me in the most recent DBA chain letter. Tim Ford tagged him, and here we are.

So, let’s recap, shall we?

So You’re On A Deserted Island With WiFi and you’re still on the clock at work. Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?

There are a few things that have been on my plate recently.

Development

I’m not going to lie – I want to do a little bit of programming now and then. The CLR contains some phenomenal features that can be leveraged both inside and outside of the database. As a consultant and database developer, it’s incredibly important that I stay up to speed on techniques to access the database. Why? Different technologies have different paradigms and idiosyncrasies. It’s important to understand how developers are interacting with data so you can steer them towards the appropriate data access methodologies for their particular technology stack/platform. Different data access methods also have different performance profiles. It’s important to understand how LINQ to SQL performs in relation to NHibernate and why you might want to use it.

Plus, I really want to get my brain wrapped around NHibernate because it looks like an easy and powerful way to model data.

TSQL and the Storage Engine

I’m currently reading Itzik Ben-Gan’s amazing book: Inside Microsoft® SQL Server® 2008: T-SQL Querying. I would love to have the time to dive into this and read it a few times and experiment with the knowledge and techniques contained within its covers. Why? Part of what I do is tune SQL Server to run very fast. Part of making SQL Server run very fast is knowing how to write very fast queries.

The next book on my reading list is Kalen Delaney’s book: Microsoft® SQL Server® 2008 Internals (Pro – Developer). Like Itzik’s book, this contains a lot of information that I’m itching to get a hold of. I know a bit about the query engine and it fascinates me. I would love to sit down and carefully devote my time to studying how it works and how I can take advantage of its inner workings to make my queries run fast.

Data Mining

Like Brent Ozar, I really want to wrap my brain around data mining and dig into it. There is phenomenal knowledge and power hidden inside of data.

Let’s say you run a restaurant. You don’t know where your customers come from, but you know on any given Friday night you have the restaurant about 80% full, on average, all night. You advertise in the local paper that’s handed out in the area. You’re successful. This is good.

What if I could tell you the following about your average customer:

  • 35-44 years old
  • male
  • spends an average of $48
  • tips an average of 18%
  • lives 12 miles away in zip code 43004
  • likes pie

Of course, if I could cross reference this with an even larger body of data, I could probably determine where this average customer is most likely to shop, how much he spends, and what kinds of mass media he’s most likely to pay attention to, etc. Suddenly you know where to spend your money, you know who to target, and you have a better idea of how to appeal to your best customers. This kind of information, as Brent mentioned, is money. Giant filthy piles of money.

Who is next in this horrible glorious chain letter?

  • Jonathan Kehayias – SQL CLR maste rand builder of clusters
  • Tim Beinninghoff – bug enthusiast, PowerShell student, fighter of good fights, and all around good guy.
  • Rick Kierner – a filthy developer, coworker, and good friend.
Comments (0)

Changing SSMS and Visual Studio Default Project Location

Jun 5, ’09 6:32 AM

Changing the location of the default projects folder in Visual Studio is super easy to do. Just click the Tools menu, choose Options, and then select the Projects and Solutions tab to change the default projects folder location:

Changing default projects is fun!

Changing default projects is fun!

Sadly, this doesn’t change anything for SSMS. Why? I don’t know.

However, I do know how to change this in SSMS. Well, google told me.

  1. Open up your favorite text editor.
  2. Navigate to My Documents\SQL Server Management Studio\Settings
  3. Open up the .vssettings file in there. On my computer it was CurrentSettings-2009-05-24
  4. Search for the text “ProjectsLocation”
  5. You want to find a section that looks like <PropertyValue name="ProjectsLocation">%vsspv_visualstudio_dir%\Projects</PropertyValue> and go ahead and change the text value of that XML element to whatever you want it to be. (I chose C:\Projects!)
  6. ???
  7. Code
Comments (1)

CBusPASS Meeting – June 11th

Jun 4, ’09 9:39 PM

Is it nearing the second Thursday of the month already? That must mean it’s time to announce the meeting topic for CBusPASS!

This time around Brent Ozar and I will be talking about SQL Server Express Edition in a presentation titled “Developing Something for Nothing”

The abstract looks something like this:

Join SQL Server expert Brent Ozar and SQL Server developer Jeremiah Peschka as they give an overview of the free tools you can’t afford to ignore in tough economic times! In this presentation, you’ll see how to improve your productivity with SQL Server Express Edition.

  • SQL Server Express Edition – the free version of SQL Server
  • SQL Server Management Studio Express – the free way to manage your Express Edition instances

The meeting will take place at 6:30 PM on June 11th, Eastern at Battelle For Kids – 1160 Dublin Rd Suite 500, Columbus, OH 43215

LiveMeeting will be available for this event – click here to attend.

Add it to your calendar!

Audio Information
Computer Audio
To use computer audio, you need speakers and microphone, or a headset.
First Time Users:
To save time before the meeting, check your system to make sure it is ready to use Microsoft Office Live Meeting.
Troubleshooting
Unable to join the meeting? Follow these steps:

1. Copy this address and paste it into your web browser:
https://www.livemeeting.com/cc/usergroups/join
2. Copy and paste the required information:
Meeting ID: Q754Q2
Entry Code: XQX<6\s\G
Location: https://www.livemeeting.com/cc/usergroups

If you still cannot enter the meeting, contact support

Comments (0)

This site is protected with Urban Giraffe's plugin 'HTML Purified' and Edward Z. Yang's Powered by HTML Purifier. 226 items have been purified.