Michael J. Swart

January 18, 2012

My favourite search terms

Filed under: Miscelleaneous SQL,Tongue In Cheek — Tags: — Michael J. Swart @ 12:00 pm

So I recently looked through my web statistics and I wanted to share some of my favourite search terms. These are words or phrases that people have searched for and for good or bad, they've wound up here on my site. Google Analytics helps me browse these search terms and looking through them I learned that
  • Michael is apparently hard to spell
  • Some people still include question marks in their search queries. It's quaint. I always assume they're asking "Jeeves"
Any way here are my favourites, in no particular order.
  • dba humour
  • trololo
  • trololo guy
  • monkey throwing darts
  • people running from atomic bomb
  • ?
  • swart guts
  • ???
  • is read uncommitted bad?
  • Yes
  • rid lookup good or bad
  • Bad
  • sql undelete
  • Nope, sorry about that. Although
  • how to forget something
  • Someone googled that. There's a story there.
  • reporting services is fun
  • you betcha
  • 10 pockets utility belt
  • Career in construction or Batman wannabe?
  • vampire hierarchy
  • Sorry, you've come to the wrong place, random googler.
  • cartoon cow tossing dog
  • (strangely enough) You've come to the right place, random googler.
  • how to avoid swart
  • HAHAHA! If you find out, let me know.
  • my software never has bugs. it just develops random features
    and
    pivot tables are like good wine you need to learn how to appreciate them
  • 2 things: (1) How did my site come up for these searches and (2) Can we be best friends?
By the way, the phrase "You've come to the right place" reminds me of Engywook, the toothless scientist from the movie The Neverending Story. He's an expert on the Southern Oracle (it's his speciality). So I include him here in the hopes that I get at least one ironic google hit for "Oracle expert":

the (Southern) Oracle Expert

the (Southern) Oracle Expert

January 17, 2012

Clearing out the closet…

Filed under: Data Cartoons,Tongue In Cheek — Michael J. Swart @ 2:31 pm

Hey fellow SQL bloggers,

Have you ever written an amazing blog post that just didn’t pan out because when you reread what you wrote, you found out it was crap? That happens to me sometimes and it’s frustrating. Here are some illustrations that I’ve created in the past that just never made the cut. So I’ll just leave these here.

Clean Your Data

SQLHomies

Just slightly hipper than SQLPeople.net

Love Your Data

Inspired by Karen Lopez’s favourite quote (Maybe this should have gone before the first picture).

Brent Ozar PLF

I admit it, I’m a Brent OPLF fanboy. And once upon a time, Jeremiah Peschka’s and his beard reminded me of Yukon Cornelius:

 

 

December 22, 2011

A simple message: Merry Christmas

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 10:00 am

From my family to yours, Merry Christmas.

And just to throw in some SQL, here is a query that will return the complete lyrics to Feliz Navidad, (the most repetitive Christmas song in the world):

select [processing-instruction(complete_lyrics)] = REPLICATE(REPLICATE(REPLICATE('
Feliz Navidad, ', 3) + 'próspero año y felicidad', 2) + REPLICATE(REPLICATE('
I wanna wish you a Merry Christmas, ', 3) + 'from the bottom of my heart', 2),3)
for xml path(''),type

November 28, 2011

SQL Fingertips

They Might Be Giants (TMBG) released an album called Apollo 18. It has 38 tracks and it runs for 42 minutes. It’s an album that will always remind me of my friend and first University roommate (Hey, what’s up Chris!). So the average track is about a minute long and this is due to a set of 21 tracks collectively called “fingertips”. Most of these fingertips are five to fifteen seconds long. Some are a bit longer. These fingertips are probably not good enough to be put on an album on their own. But collectively, they make a really cool album (especially on shuffle mode).

The List

So when I found myself with a collection of tiny tips that might not qualify as a blog post on their own, I thought of that album and a microsecond later, we’ve got SQL Fingertips!

So here they are, in no particular order.

  • Stackoverflow tip: Use the bounty feature without being afraid of the hit to your reputation. A bounty offered on a decent question usually attracts enough attention to get your question up-votes. And unless it’s a crummy question, those up-votes will earn you back the reputation points you spent.
  • T-SQL tip: You can use the following script as a template to be able to run a single query on multiple databases and have it return a single result set:
    DECLARE @temp TABLE (
    	ServerName sysname,
    	DBName sysname,
    	[Row Count] int
    );
     
    declare @schemaname sysname = 'Production';
    declare @tablename sysname = 'Product';
    declare @sql nvarchar(max) = N'use [?];
    	if exists (select 1 from sys.tables where name = ''' + @tablename + N''' and SCHEMA_NAME(schema_id) = ''' + @schemaname + N''')
    		exec sp_executesql N''
    			select @@servername, db_name(), Count(1) as [count]
    			from ' + QUOTENAME(@tablename) + '''';
     
    insert @temp (ServerName, DBName, [Row Count])
    exec master..sp_MSforeachdb @sql;
    select * from @temp;
  • SQL Server Management Studio tip: GO can take a parameter. If you write GO 100, your batch will be executed 100 times. Many people know this but I always here a couple “wow”s each time I demonstrate it. And here’s a couple more notes about that batch separator.
    • Don’t forget to SET ROWCOUNT OFF.
    • The batch separator doesn’t have to be GO. It’s configurable! I used to set it to ENGAGE for a while, but the joke got old and I switched it back.
  • Blog tip: If you like following blogs or people, but maybe you’re falling out of love with your RSS reader. No worries. You can get any RSS feed at all sent straight to your inbox.  There are lots of RSS-to-inbox websites out there. The one I recommend is FeedMyInbox.com. So, for example, to get my own blog delivered to your inbox, start here.
  • Data → Eye → Brain, A Data Visualization tip: Data visualizations (charts, diagrams, etc..) help us gain knowledge and understand data that maybe we couldn’t understand otherwise (See Anscombe’s Quartet). And for large and complex datasets, the best visualizations are usually custom designed and interactive. There’s a lot of power in customized visualizations: See what David McCandless and Miriah Meyers have to say.
  • Twitter DBA Tip #1: I put a call on twitter asking people to tweet the one database tip they thought more people should know. The first one comes from @BrentO: “Don’t leave your database in full recovery mode if you’re not doing t-log backups.” He’s right. Full recovery mode usually implies a backup strategy that includes transaction log backups. (And full or differential backups are not enough either).
  • Twitter DBA Tip #2: This one’s from @wnylibrarian“If using SQLExpress & no SQLAgent, try using SQLCMD in a batchfile.” I’m glad Gary tweeted this one. I’ve come up against this problem in my own work. It turns out that when downgrading editions of SQL Server, often one of the first things that you miss is SQL Agent. If you follow Gary’s advice, it’s not a hurdle, it’s only an inconvenience.
  • Twitter DBA Tip #3: Next I heard from @DaveH0ward who wants to remind people that “You need to size your data /log files and set autogrowth amounts appropriately.” I think a lot of people don’t know that the default auto-growth settings for SQL Server databases are a bad idea. Tim Ford warns us of dire consequences for not following this tip.
  • Twitter DBA Tip #4: Finally I heard from @SQLSandwhiches (aka Adam Mikolaj). He shared a pet peeve “I’ve seen so many servers that don’t have logs to auto cycle. Don’t you love opening a log file with 300,000 entries?” Adam wrote about his experience earlier this year (including a solution of course).
  • MixedTape Fingertip Tip: Say you find yourself back in the nineties making a mixed tape and you find that you have a minute left to fill on one side of the tape. They Might Be Giants to the rescue! Pick several fingertips from their Apollo 18 album and optimize every second of that mixed tape! My personal favorite is the 45 second Minimum Wage off of their other album Flood.

So how many of these SQL Fingertips did you know? Have you got a small tip? Share it in the comments. Have you got a whole bunch of small tips? Write your own SQL Fingertip post. I’d be especially grateful to read a set of Business Intelligence fingertips.

November 7, 2011

T-SQL Tuesday #024: Procedures “By The Numbers”

Filed under: SQLServerPedia Syndication,Tongue In Cheek — Tags: , , , — Michael J. Swart @ 10:12 pm

T-SQL Tuesday Logo
I’m super excited about this month’s T-SQL Tuesday, for a couple reasons. First of all, this month’s T-SQL Tuesday is hosted by Brad Schulz. Brad is a guy that I got to interview a couple years ago. Second, is that the topic is Sprox ‘n’ Funx (Stored Procedures and Functions). Which I like because the topic is nice and technical.

I started out thinking that I could treat the question “Are stored procedures a good thing?” But that issue is a contentious one and it would take a book to treat that issue properly (So maybe later)

Today, I’m giving some attention to some interesting numbers related to stored procedures (the format is taken from Harper’s Index a popular feature in Harper’s magazine)

Stored Procedures By The Numbers

250: The largest size in megabytes allowed for the source text of a stored procedure.

2,100: The largest number of parameters allowed for a stored procedure.

32: The maximum number of nested levels allowed for stored procedures.

1,598: The number of mentions in sys.messages that contain the word procedure.

858: The number of mentions for function.

389,000: The number of Google results for should use stored procedures.

29,500: The number of Google results reported for should not used stored procedures.

1 in 866: The odds that a random word taken from MichaelJSwart.com is procedure.

1 in 879: The odds that a random word taken from the 2011 State of the Union address is budget.

18: The number of participating blogs in Brad Schulz’ T-SQL Tuesday on the subject of stored procedures and functions.

1875: Earliest year I could find a published mention for the word sproc*

* ~  Lives of the Irish Saints (1875) mentions a St. Sproc daughter of Colum. I am not making that up!

October 11, 2011

Missing the PASS Summit

Filed under: SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 8:30 am

Well I hope this won’t turn into an annual thing! Last year, I missed the PASS summit:
Why Michael J. Swart won’t be at the PASS Summit.

And this year…

So if you’re in Seattle this week: have fun, spread some knowledge, meet some smart people.

If you’re not in Seattle like me.

  • Follow Jen McCown’s blog post here: “Attend the PASS Summit from Home”. I’ll be doing a lot of this. I’ll be watching the keynotes as they’re broadcast, but without SQL Server Denali being released, I’m not sure what’s in store.
  • Leave me a comment here or on Twitter @MJSwart. There’s no reason not to get a little networking in 🙂

I hope to see you next year in Seattle.

September 21, 2011

Artificial Intelligence versus SQL Server Help

Filed under: Tongue In Cheek — Tags: , , — Michael J. Swart @ 10:17 pm

Something lighthearted for this week. ~MJS

You Remember Watson?

Do you remember Watson? It's the A.I. program that IBM developed that answers natural language questions. It kicked butt last year on Jeopardy. Well, IBM recently noticed that there was a large store of SQL Server knowledge to be had by looking through SQL Server questions on stackoverflow and by looking through #sqlhelp tagged questions on twitter. Well it wasn't too long before they had Watson answering SQL Help questions!

I love artificial intelligence. What could go wrong!


IBM planned to deliver Watson's software and hardware to Chicago (but somehow they got lost at Toronto's airport). With one thing and another, I was lucky enough to get involved and I'm proud to say that they let me preview their Watson vs. SQL Server solution that they've (embarassingly) called The SQL Server Help Oracle.

I've given it a spin and I have to say it's in fine form! It gives answers that are just like a DBA's answers. Without further ado... Here it is! Give it a spin! Ask away.

Introducing the SQL Server help oracle:


September 2, 2011

Me and My Big Mouth (Literally)

Filed under: Miscelleaneous SQL,Tongue In Cheek — Tags: , , , , — Michael J. Swart @ 9:20 am

SAAAAAAAAAN guy!.

I First Learned About SQL as a Speaker

One of my jobs in University was to tutor first year computer science students. My friends would tease me and call me a “computer tutor” in a really nasally voice. But it was a good job and a good experience.

I did a large variety of things in that job. I did the usual things like running tutorials, marking papers, and helping students with their assignments. But I also gave campus tours and I gave workshops to other students.These workshops were very very brief introductions to various computer science topics.

I was assigned to give one of those workshops on the topic of something called SQL. It was the first time I had ever encountered anything database related and I was supposed to teach it! I had never even seen the word SQL before and it was years before I got used to pronouncing it sequel instead of ess cue ell. I was nervous then, but I don’t think I needed to be. I learned enough about that subject to teach it well and the preparation paid off. That experience made me comfortable around SQL and when I encountered this “database language” again, I found it easy to pick up where I left off.

During that job, I didn’t learn a lot about computers that I didn’t already know, but I did learn a  lot about speaking and teaching. I learned how tricky it was to pace yourself. If you talk too quick, the subject matter goes over everyone’s head. Speak too slow and it sounds like you’re condescending and talking down to people.

I still like talking about SQL Server. At work, I often give lunch-and-learns. These are lunch-hour talks put on by coworkers for coworkers to talk about standards or to teach something that needs explaining. In the past few years, I’ve learned a lot about web development and I hope my colleagues have learned a bit about databases.

In general, I think public speaking is a good skill to have. Some are naturals (or seem to be) and others (like me) need the practice. So having said that …

I’ll Be Giving My First Talk at SQL Saturday #93 in Toronto

And so I’m super excited about giving a talk at SQL Saturday #93 in Toronto (September 17, 2011). SQL Saturday #93 is a free one-day workshop for SQL Server. (Register here!). The talk I’m going to give is called Obliterate Excessive Blocking and Deadlocking As a DB Developer, I think avoiding blocking is one of the most valuable skills to have. I’ll advertise this talk a bit more in a blog post next week. In the meantime…

Name that Caption!

That creepy picture of me up there is screaming for a better caption. Let me know your ideas in the comments, or put it in a tweet (@MJSwart). Let me know by the end of Wednesday (Sept. 7th). I’ll pick my favourite and let you know on Thursday!

Update September 15, 2011: So John Sansom is the lucky winner of the caption contest. John, I’ll buy you a drink next time we’re in the same city.

June 22, 2011

Midyear Resolutions

Filed under: Tongue In Cheek — Tags: , , — Michael J. Swart @ 12:00 pm

So it’s been about half a year since Jen McCown invited SQL Bloggers to post their new years resolutions. I made mine and I’ve kept up with it, but I’m going to reassess that resolution now.

If you remember, my resolution was to blog once a week (with articles to go live every Wednesday at noon), keep to technical content, and include an illustration with each post. Check, check and check. My goal was to get 4 times as many readers than the previous year.

Well, I’m not quite there. Google Analytics tells me I’m at 3.3 times as many readers as the first half of 2010 …  So what do I need to do to get to 4.0?

Some ideas that came to mind were:

  • I could post five or six times a month instead of four. Going for volume.
  • I could focus on plugging my site in other places.
  • I could participate in every meme monday, t-sql tuesday and un-sql friday out there.

But none of those are appealing. Those thoughts don’t light a fire under me. So I’m going to say “You know what? 3.3 is good enough“.

So what does light a fire under me?

  • Writing about SQL
  • Drawing stupid illustrations.

I’m Giving Up My New Year’s Resolution

So I’m giving up the goal of the resolution, but I’m not changing my blogging habits… At least not too much. You’ll notice this post still got posted on a Wednesday at noon.

In The End It Wasn’t About Readership

Besides, I’ve realized that it’s not really readership I was after, it was the commenters! (Commenters! Commenters! Commenters!). The lurkers can continue lurking, but I’d rather have 10 comments on a post than 10,000 page views. The feedback is really good to have and I’m happy with that.

And to other bloggers out there, I suspect that you feel the same. My guess is that you’d prefer ten new active readers versus ten thousand new passive readers.

An Illustration

So I’m breaking my own rules. This isn’t a technical post and the illustration here has nothing to do with the subject of this post. So why do I include an illustration of Wicket Baggins here?

Because I can!

April 13, 2011

Whew… Microsoft Certification Exam 70-433

Filed under: SQLServerPedia Syndication,Technical Articles,Tongue In Cheek — Tags: , , , — Michael J. Swart @ 12:00 pm

Yesterday was April 12, 2011, and as far as I know mainstream support for SQL Server 2005 ended. See Aaron Bertrand’s A Quick Note About SQL Server 2005 Mainstream Support.

And if you care, this also means that the Microsoft Certifications that are tied to that product also expire. At least that’s what I gather from this Microsoft FAQ which says:

Q. How long will my certification be valid?
A. Today, most of our Microsoft Certified Technology Specialist (MCTS), Microsoft Certified IT Professional (MCITP), and Microsoft Certified Professional Developer (MCPD) exams retire when Microsoft discontinues mainstream support for the related technology…

But that’s just the exam, I’m not too sure about the certification. In any case it really doesn’t matter at all. I mean, the skills a person has with SQL Server does not diminish or become obsolete with the expiration of SQL Server 2005. SQL Server 2008 and other versions are still alive and kicking.

Bad News: So my MCTS (Microsoft Certified Technology Specialist) certification for SQL Server 2005 expired yesterday (don’t worry I’ll get over it).
Good News: My MCTS certification for SQL Server 2008 takes effect last Friday when I passed the 70-433 exam (yaay!!) It’s my first step towards a new certification for myself, the MCITP (IT Professional).

It was a decent exam, I thought it was fair. There were a couple of things I would have changed:

  • I found at least four typos in the questions and at least once I had to answer the question they meant to ask not the question they actually asked.
  • Two thirds of the way through the exam, there was a power outage. No one could have foreseen it, but I was glad to discover that my questions weren’t lost and the time without electricity was not on-the-clock.

Section 5, My Nemesis

I did well in every section except one, Working With Additional SQL Server Components. In that section I got two questions correct out of six. I guess I should have given more focus to it while studying. To be honest, I didn’t actually work the plan I wrote out for myself for that section. I had heard scary things about some of the other sections and focused on them. But even so, two out of six is pretty rotten for a multiple choice test. Random guesses might have even fared better. A lucky monkey has a good chance (46%) of doing at least as well or better than I did simply by throwing darts randomly as a strategy for choosing answers.

A picture of a monkey with holding a dart.

My 70-433 Study Plan

Of course I can’t give details about the content of the exam for obvious reasons, but I do want to share the study plan I followed when studying for this exam. I wrote the plan based on Skills Measured as described by Microsoft. Everything I write here can be found online:

Tables and Views

  • What does with CHECK OPTION mean when creating views
  • What does with ENCRYPTION mean when creating views
  • what’s the syntax for fill factor?
  • write (without checking syntax) a nci with fill factor
  • rebuild an index (without checking syntax) with a fillfactor
  • create statistics without checking syntax
  • add a pk (without checking syntax)
  • add a fk with cascading deletes
  • disable/enable pk
  • disable/enable check
  • disable/enable fk
  • disable/enable unique
  • Create table with column that has filestream
  • create a spatial column (without checking)
  • What’s a structured vs semi-structured column?
  • name collation varieties (case sensitive etc…)
  • Create partitioned table (without checking syntax)
  • Split merged table into two, merge back together
  • Create empty table and switch into partition

Programming objects

  • Without checking syntax, pass in a tvp
  • without checking syntax, write sprocs with all varieties of “Execute as”
  • Without checking syntax, create sproc that specifies RECOMPILE
  • Without checking syntax, create sproc that specifies WITH ENCRYPTION
  • What permissions are there on sprocs? 2 flavors of security here: GRANT, DENY, REVOKE versus EXECUTE AS
  • Build Function without checking syntax
  • What permissions are there on udfs? flavors of security here: GRANT, DENY, REVOKE versus EXECUTE AS
  • What is schemabinding on udf?
  • what are the three (four?) kinds of functions
  • Explore flavours of triggers.
  • Create trigger that uses INSERTED/DELETED
  • Explore security on triggers, (EXECUTE AS)
  • Create DDL trigger.
  • Create DDL trigger that returns data.
  • Create CLR assembly.
  • Explore clr: SAFE/UNSAFE/EXTERNAL_ACCESS
  • Explore CLR: set trustworthy
  • Implement TRY CATCH RAISERROR
  • error handling, look at Rusanu’s service broker scripts, he’s an awesome error handler.

Query Fundamentals

  • Explore different DELETE syntax (i.e. DELETE A FROM A…)
  • Without checking syntax, use the OUTPUT clause
  • Without checking syntax, use the MERGE statement
  • Write a query that uses grouping sets.
  • What’s the difference between LEN and DATALENGTH
  • without checking syntax, use patindex and charindex

Additional Query techniques

  • write a subqueries (correlated)
  • write a subqueries (simple)
  • write a subqueries (scalar)
  • write a subqueries (list)
  • write a subqueries (table valued)
  • Without checking syntax, Use rank
  • Without checking syntax, Use dense rank
  • Without checking syntax, Use row_number
  • Without checking syntax, Use ntile
  • Without checking syntax, Use row_number, and partition by
  • look through table hints to understand any unused ones
  • look through query hints to understand any unused ones
  • manage collations? how can they be managed.
  • what’s the diff between column/db/server collations
  • (international) define custom errors

Other SQL Components

  • Set up db mail
  • implement full text search
  • full text: Use CONTAINS
  • full text: Use CONTAINSTABLE
  • full text: Use FREETEXT
  • full text: Use FREETEXTTABLE
  • full text: Use STOPLIST
  • Use powershell and smo to create .net cmdlet demonstrates smo
  • Take service broker tutorial, compare sample syntax to rusanu’s syntax
  • Implement change tracking
  • Create db audit spec
  • Use CHANGETABLE

XML Data

  • use each FOR XML type
  • without checking syntax, use OPENXML, and sp_XML_docs
  • without checking syntax, shred xml.
  • review xpath syntax
  • what’s the difference between xquery and xpath?
  • Read through books online topic on xml

Gathering Perf Info

  • Use SHOWPLAN to (showplanxml?) to look through query
  • look at how paul (@sql_kiwi) displays query plan
  • Run through DTA, Prepare a workload <– remember options in DTA here!!!
  • Review catalog views. (Is this INFORMATION_SCHEMA?)
« Newer PostsOlder Posts »

Powered by WordPress