April 2009
Mon Tue Wed Thu Fri Sat Sun
« Mar   May »
 12345
6789101112
13141516171819
20212223242526
27282930  

Month April 2009

Check for the Existence of a Temp Table

Do you suffer from the pain of temporary tables hanging around long after you need them? Have you ever found yourself frantically googling, trying to remember the syntax to check for the existence of a temporary table before deleting it?

I, too, used to suffer alongside you, dear readers. I, too, know your pain of re-running a script only to find that a temporary table was skulking around in memory somewhere, waiting to ruin my carefully crafted three-value logic.

Well, my pain is gone thanks to my new found understanding of naming things in tempdb! You too can remove the terrible pain and suffering of searching for temp tables in your code and instead can use this one, that’s right only one, simple step to make sure that your temp tables are cleaned up by the time you need them again!

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

You’re welcome. Please make all checks payable to ‘cash’.

Dynamic T-SQL Resources

As a follow up to my Dynamic T-SQL presentation, a list of Dynamic T-SQL resources have been posted on the AppDev SIG page of PASS site. Hit up the link for more info.

When I first started writing dynamic SQL, I had a hard time finding any resources on the topic. If you have any that you’d like to share, hit me up in the comments or shoot me an email at jeremiah.peschka@gmail.com and I’ll be sure to review it and put the link up.

CBusPASS 2009.04.09 Meeting Recap

We had the fourth meeting of CBusPASS on April 9th. There were seven people in attendance, including myself. In hindsight, we probably should have delayed the meeting for a week with the holiday weekend. That being said, we had a great meeting.

The topic was general tips, tricks, and techniques that the community uses to make their jobs easier. Three members of the community talked about various techniques including configurable keyboard shortcuts in SSMS, using templates, PowerShell, and the benefits of clustered indexes (thanks to K. Brian Kelley for that suggestion). The meeting was a great success and turning into a group discussion on a variety of other topics and best practices.

Being able to sit down with other SQL Server professionals and have a discussion about various techniques and technologies that we use on a daily basis is great. What’s even better is being able to share knowledge and learn something at the same time. We all took advantage of the setting and contributed to the presentation and were able to share our knowledge with each other. This is, honestly, the reason why I rekindled the Columbus PASS chapter and is the reason why I’m so excited to attend the PASS Summit again in November.

Links for the Week of 2009.04.10

SQL Server

A Web Based Search for Books Online Buck Woody provides links to macros for MSDN search that will give the search bar in your browser the ability to search online inside Books Online. This has become my go to method for searching BOL. It’s much faster than opening the desktop BOL client and the results are typically exactly what I need.

The Third Pillar – Fundamentally Sound Louis Davidson discusses the third (of seven) pillars of database design.

PASS Update SQL Batman infiltrated the lair of PASS in the last round of elections and is now providing insider reports on what’s happening in our world of databases. Or something like that.

Development

Meet and Code Recap My friend, co-worker, and co-conspirator in development mischief has provided a recap on the Meet and Code event that we put together a few weeks ago. It’s well worth checking out to get his thoughts on the thing. Especially since I wrote my thoughts on a combined total of 9 hours of sleep for the weekend.

Stuff & Things

Narrative Planetarium Ideas for exploring narratives provided by the night time view of a city’s skyline.

A Day in the Life of the Boss (NSFW) Normally I (probably) wouldn’t link to something NSFW, but in this case this was too funny. Really it’s just bad language in a flow chart, but some people could be offended. You have been warned, this link contains offensive language (and no, it’s not the words ‘CREATE CURSOR’).

CBusPASS Meeting Thursday, 2009.04.09

This Thursday, April 9th, the Columbus chapter of PASS will be meeting at Battelle For Kids(1160 Dublin Rd, Suite 100, Columbus, OH 43215.)

There will be several mini-presentations given by local community members covering tips, tricks, and techniques that we use to make our jobs easier on a day to day basis.

Everything starts at 6:30PM EST. Pizza and soft drinks will be provided, so stop on by. If you’d like to present, send an email to jeremiah.peschka@gmail.com.

From Data to Information

Originally, a long time ago, I became interested in programming primarily as a means of information visualization. (Well, truthfully, back in 2000/2001 I wanted to have my own blog but I got distracted and eventually learned how to do other things.) Anyway, once I discovered some of the ideas in the field of information architecture and, later, through the IxDA I was hooked. One of the principle ideas that stuck with me is that information must have a structure and must be searchable in order to be useful to a consumer. This seems like a really fundamental, simple, idea. Unfortunately at that time (and now, arguably) very few people really understood this idea, or even thought about it.

The more and more I thought about it, too, the more I realized that data, that ephemeral substance that we all capture in databases, is fundamentally without meaning and value. Data in its most basic form is merely a point or set of points on a continuum – e.g. 4, 15, 6. But when you provide additional information to the members of your data set, you suddenly gain additional information: Lemonade sales were 4 times higher when the temperature was 15 degrees higher than average in June.

There’s a distinct information lifecycle that most people are aware of, but here it is anyway if you’ve forgotten it:

Data is given meaning and becomes
Information which is processed to become
Knowledge which leads to
Understanding

Now, the knowledge and understanding part, that deals with the messy process of thinking and ideation. While thinking and ideation are fascinating topics, they deal with a number of individual biases and things like psychology and neurology and other things that I don’t find as interesting. As such, I’m not going to really deal with them at all. So, we’ll pretend they don’t exist for the purposes of this discussion.

Essentially, to get back to the main point, one of the most fascinating things to me is the process used to give meaning to data in order to turn it into information. What’s also interesting is that there is seldom a single right answer, as so many of us have discovered. The right answer depends on the question asked and the important thing that we do, as keepers of data, is enable people to ask those questions and turn facts and figures and meaningless piles of data into information for the purpose of making business decisions (which hopefully doesn’t turn that 4, 15, 6 into ‘In the 4th quarter, revenue was down 15% so we’re laying off 6 of you’).

Equally as important is metadata. The more data you can collect to describe another data point, the more meaning you can give to that data point. Let’s look at a similar example: On August 16th, our little lemonade stand sold twice as much lemonade as was sold on August 23rd. Without any additional data to describe August 16th and August 23rd, we aren’t able to do any more than report raw sales figures. But what happens if we track temperature and other weather conditions? Maybe August 16th was particularly hot. Maybe it rained all day on August 23rd. Add in more information about local events. Suddenly we have an additional descriptor for these two days and we know that there was a town parade on August 16th that passed right by our lemonade stand. This is the type of metadata that turns meaningless data points into valuable pieces of information. By leveraging technology it’s possible to easily associate these points of data with their descriptors and build a meaningful piece of information that is surrounded by descriptive metadata that enables rapid decision making and facilitates easier search and browsing related topics and ideas.

Where does that leave us? Well, if the point of information is to be processed into knowledge that enables understanding then it’s fairly clear. Information retrieval systems need to provide as much context as possible to the underlying data points. The information storage systems need to be designed in a way that facilitates data collection and storage. Specifically, storage systems need to be designed in a way that allows for the storage of diverse types of metadata – documents, images, raw text, audio, and video files all need to be stored to enable the transformation of raw data points into information.

At what point do we stop collecting data and start aggregating data from disparate sources? There comes a point when we simply can’t store enough data fast enough from all potential collection sources. At this point, we need to rely on others to help us turn our data into information. In turn, there is probably some of our data that will help our data providers turn their data into information. Slowly but surely, our growing need to turn data into information via included metadata will enable us to access an increasingly complex and interconnected world.

Links for the Week of 2009.04.03

SQL Server

How MySpace.com is running on SQL Server Video talk about the MySpace backend. Good stuff.

Microsoft® Visual Studio Team System 2008 Database Edition Power Tools The VSTSDD Power Tools are some sweet extensions to visual studio that will make life much much easier for anyone who is using Database Developer GDR. My personal favorite is sqlspp.exe which takes the SQLCMD scripts that are generated on deploy and turns them into something that anyone can run in SSMS.

Development

Cincinnati Object Relational Mapping (ORM) Firestarter Want to learn more about ORMs? Live near Cinci? This is a good event for you! I’m sad to see that my favorite ORM (LLBLGen) isn’t on the list. It’s an impressive tool, but it goes far beyond being a simple mapper, so maybe it doesn’t really belong.

The Toughest Developer Puzzle Ever

Stuff & Things

30 Fresh and Promising Design Blogs to Follow Design is key to helping people understand information. These 30 blogs cover web, graphic, and print design. Let’s hope that they help someone turn a good design into a great one! Besides, design is always cool.

We’re All Gonna Die – 100 meters of existence A unique photo montage.

Interviewing a Potential Employer Aaron Alton provides a slew of helpful information about how to interview your employer while they’re interviewing you. These go a bit more in depth than some of the previous interview tips I’ve seen and they’re a great way to get to the bottom of what a company is all about.

Fun with PATINDEX() and DB_NAME()

I’m going to guess everyone can guess that DB_NAME() will give you the name of the current database when called with no parameters. If not, now you know.

PATINDEX() is a little bit more fun, so before we get into things, here’s how PATINDEX() works:

DECLARE @my_var AS NVARCHAR(30);
SET @my_var = 'fun with patterns';

SELECT PATINDEX('%pattern%', @my_var);
-- returns 10

PATINDEX() returns the position of the pattern in the search expression. It’s 1 indexed for those of you who come from a 0-based string world.

Moving forward!

So far, I haven’t done anything cunning. But what if you have a crazy situation like this:

You have a publisher database. There are two subscriptions set up with bidirectional transactional replication. They are named Subscriber1 and Subscriber2. They are stored on separated physical servers, but they have identical schemas. Funnily enough, and this is where it gets tricky, there’s another database in the picture. One each server there’s a Web database (WebOne and WebTwo). It does reporting and uses tables in the subscriber. It also holds all the stored procedures. Suddenly things get a lot more entertaining because you now have to fully reference table names.

(Is this confusing enough without pictures?)

What to do?!?

Well, for 99% of of your stored procedures, you can use CTRL+H and ignore this blog post. You probably can ignore this blog post for the other 1%. Unless you’re insane, like me, and generate ad hoc SQL from a CLR assembly. If that’s the case, keep reading.

So, I have a CLR assembly which contains a function. That function emits SQL code which is executed via sp_executesql. Rather than maintain two versions of this assembly, one for each server, I decided to use REPLACE, PATINDEX, and DB_NAME to make my life more fun.

DECLARE @condition AS NVARCHAR(10);
SET @condition = '%Two';

IF (SELECT PATINDEX(@condition, DB_NAME())) > 0
BEGIN
  SELECT @magic = REPLACE(@magic, 'Subscriber1', 'Subscriber2');
END

That’s it. It’s pretty simple, really. This is the part where you remark on how smart I am and send me money via PayPal.

PASS Summit Call For Speakers – 8 Days Left

I finally put together my abstracts and submitted them to PASS for the 2009 Summit. If you want to speak, but haven’t submitted abstracts yet, you better hop to it.

I figured that I would go ahead and list my abstracts here so you can copy them, fix the typos, and submit them as your own.

A Dynamic World Demands Dynamic SQL

Dynamic SQL is a misunderstood and much maligned part of a DBA’s tool kit – it can be used to solve difficult business problems, respond to diverse data needs, and alleviate performance problems. Many DBAs reject dynamic SQL outright as a potential source of SQL injections, being poorly performing, or just for being a hacky solution in general. Not so!

Jeremiah Peschka has been making extensive use of dynamic SQL throughout his career to solve a variety of problems. He’ll set about dispelling these misconceptions and demonstrate how dynamic SQL can become a part of every DBA’s tool kit.

Session Goals

  • Learn how to work effectively with diverse inputs.
  • Learn how to avoid common pitfalls of dynamic SQL.
  • Develop an understanding of real world uses for dynamic SQL.

Digits, Dates, and Databases

Calendar and number tables migh not help you get a date, but they can help you accomplish difficult tasks in the database while sticking true to your set-based roots. Nobody wants to admit that they’ve used a while loop in their SQL code, but there are ways to avoid looping constructs and use a set-based solution using a numbers table for loop control. Date manipulation can be cumbersome to peform, can create unwieldy and unreadable code, and can even create performance bottlenecks. Using a calendar table can alleviate these performance bottlenecks and make many calculations easy.

Session Goals

  • Learn what number and calendar tables are.
  • Learn how to effectively use a numbers table in set-based SQL.
  • Learn how to work with a calendar table to remove the need for complex date manipulation.

What are you waiting for? Submit your sessions today!

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