T-SQL Tuesday #010 – Indexes

Invitation and roundup from Michael J. Swart.

Indexes are strange things. You never need to explicitly create one to create a fully-functional database, but if you want a database to perform well, they’re indispensable.

And there are so many aspects to write about! Like internals, covering, clustered, xml, fulltext, b-trees, hints, maintenance of, included columns, filtered, redundant, missing and tons more.

In fact my SQL Server 2008 Administrator’s Pocket Consultant (The first handy textbook I could grab) has an index entry on “indexes” that has 22 sub-entries.

 

T-SQL Tuesday #009: Beach Time

Invitation and roundup from Jason Brimhall.

Congratulations!  You have been chosen as a finalist in the Vacation Getaway package of a lifetime.  You will be flown to a resort destination of your choosing.  For this vacation, we only ask that you leave your cell-phone, laptop, twitter and facebook behind.  You have earned a break from the emergency Database repair calls and the urgent last-minute report requests.  To take advantage of this opportunity to put your “toes in the water and ass in the sand” and to feel like there’s “Not a worry in the world” and “life is good today,” just share what you did to earn it! (Lyrics courtesy of “Toes” by the Zac Brown Band)

What do you do as a DB professional to earn a little “Beach Time?”  What do you do prior to “Beach Time” to ensure that the beach time will not involve work?  The topic for this installment in TSQL2sday is to write about what you have done to be able to get a break from the job.  Beach time is usually vacation time, but is really anything that can create a break in the work-place.  If you automated a process to lighten your load – tell us about that process.  If you had to pull a 72-hr shift to ensure that your vacation plans would be unaltered by work – tell us about it.  If you turn off the cell-phone and pager and ignore email for that vacation – tell us about it.

T-SQL Tuesday #008: Gettin’ Schooled

Invitation and roundup from Robert Davis.

This month’s topic will be all about learning and teaching.

We return to our days of youth to take a fresh look at learning. How do you learn? How do you teach? What are you learning or teaching? Or the coup de grace post would be learning something new and telling us about it.

T-SQL Tuesday #007 – Summertime in the SQL

Invitation and roundup from Jorge Segarra.

In the last few months we’ve had the release of SQL Server 2008 R2 which brought along with it a slew of new features. So this T-SQL Tuesday I ask: What’s your favorite hot new feature in the R2 (I’ll be nice and include 2008 in general) release? Got some code that takes advantage of a new feature? Post it! Got an example of how PowerPivot let you slice and dice data you simply couldn’t before due to contraints? Show us! Make sure to apply your SPF 1433 and get to writing.

T-SQL Tuesday #006: “What About BLOB?”

Invitation and roundup from Michael Coles.

MSDN conveniently defines Large Object (“LOB”) data types for us: “LOB data types are those that exceed the maximum row size of 8 kilobytes (KB).”

There have been a several improvements in LOB data functionality in SQL Server 2008 (there were even some in SQL Server 2005).  In 2008 the XML, GEOMETRY, GEOGRAPHY data types can all hold 2.1 GB of data.  CLR data types can also hold up to 2.1 GB of data.  So the question of the day is how do you use LOB data?  Here are a few possible starting points:

  • LOB data storage, optimization, limitations, “under-the-hood”
  • Indexing, querying, optimization, tricks, tips, performance tuning of LOB data
  • Interesting uses/projects for LOB data types:
    • The MAX data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX))
    • XML
    • GEOMETRY/GEOGRAPHY (spatial)
    • CLR data types
  • FILESTREAM hints, tips, tricks, .NET SqlFileStream Class

The only rule is that your topic has to involve SQL Server’s LOB data types in some form.  If you want to demonstrate handling LOB data in .NET, for instance, go for it.  If you want to demonstrate Oracle LOB data handling, this might not be the place to do it (although a comparison of the two might be interesting…) 🙂

T-SQL Tuesday #005: Reporting

Invitation and roundup from Aaron Nelson.

Reporting is a potentially huge topic so here are just a handful of ideas that might get you started:

  • Server Performance Reporting
  • Technologies to help offload reporting
  • Reporting services tricks
  • T-SQL tricks for presentation and formatting
  • Database design considerations for reporting
  • Self-service reports
  • Reports for executives
  • Reporting from Oracle in SSRS

Don’t forget that the DMVs are pretty much qualified as ‘Reports’ so those count too!

T-SQL Tuesday #004: IO

Hosted by Mike Walsh

Invitation and roundup.

IO, IO, It’s Off To Disk We Go!

IO is on my mind lately. It could be some recent “discussions” with a SAN administrator, clients with disk performance issues or helping developers with some queries that are doing lots and lots of needless reads. It could be that I just changed my son’s diaper and it was heavy on the Output side (time to start potty training, I think…)

Actually, as a DBA, IO is often on my mind. So that is what this month’s theme is: IO.

Like last month’s theme, you could treat this topic in a few different ways. Perhaps some best practices that you have implemented for disk allocation. A professional development topic on working better with your storage administrators? A case study with a vendor or type of storage system? A developer writing about better managing reads in your queries? You could brag about your latest experiment with SSDs? Maybe a walk down memory lane of storage performance even. How about writing a beginners guide to setting up optimal storage? Have some really busy SQL Servers running on a virtual? How is your IO configured?

Well, you get the idea, the post has to have something to do with IO but it doesn’t have to be about T-SQL necessarily.

T-SQL TUESDAY #003: RELATIONSHIPS

Hosted by Rob Farley.

Invitation and summary

Valentine’s Day is coming up. Hopefully I don’t need to tell you that it’s on February 14th, but if you’ve read this far into the post then perhaps you’re involved with databases for some reason and may need reminding. Shopping centres around the world have signs up reminding us to buy flowers for our loved ones, but I know many people in IT circles who don’t tend to go to such places, lurking in dark corners of houses until all hours of the night, surviving on pizza. Hopefully this theme will not only prompt some interesting posts, but also prompt people to go out and invest in the meaningful relationships in their own lives. Actually, if you don’t know that Valentine’s Day is February 14th, I’m guessing you don’t have anyone in your life worth buying for. ?

For me, Valentine’s Day is only three days after my wedding anniversary, so I can’t forget either – as if I would.

So the theme for this month’s T-SQL Tuesday is Relationships.

There are a massive number of options you could go with for this theme. You could talk about Foreign Keys in the relational world. You could wax lyrical about the benefits of attribute relationships in cube design. You could write a poem for your loved one, apologising for all those hours spent in front of a Management Studio window, trying to tune a query, rather than tuning your guitar to serenade her.

Other ideas include: Relationships between Devs & DBAs, Clients & Vendors, Entities, data types, concepts (eg: Report Model & Cube), and more… if you’re struggling to think of something, drop me a line (twitter, Msgr, email, whatever – a list of contact options is over on the left) and I can help.

But so long as you can loosely tie your post to both the theme and some aspect of SQL Server, that’s fine. Be creative, informative, reflective, and hopefully relevant.

T-SQL Tuesday #002: A Puzzling Situation

Hosted by Adam Machanic

Invite and roundup

Have you ever found yourself unable to figure out the intricacies of how some piece of code works? Ever been confused by the results you’ve gotten back from a query, only to find out that something totally unrelated was going on? Or have you ever been compelled to wile away your spare time working on a “challenge” posted by some blogger?

For this month’s T-SQL Tuesday, I’m asking participants to write a blog post on a “puzzling” topic, along the lines of some of the following ideas:

  • Describe a confusing situation you encountered, and explain how you debugged the problem and what the resolution was
  • Show a piece of code that doesn’t behave as most people might expect, and illustrate the reasoning behind the discrepancy
  • Create a challenge for your readers to solve

As always, even given the event’s name the posts are not limited to T-SQL! Any component of, or software product related to SQL Server, is fair game. MDX, SSIS, LINQ to SQL, Entity Data Model, NHibernate, and any other software product that deals with SQL Server data can be featured in your post. Be creative!