SQL Query Basics

Needless to say, I’m a SQL n00b. For the longest time I drank the Microsoft Kool-aide and believed “you shouldn’t touch the SQL side of SharePoint.” And when I say “shouldn’t touch” I mean absolutely no touching or querying of any kind. Well, I’ve wised up. It’s not that you can’t touch anything, just be mindful of what you can and can’t do. Querying – as long as you do it with no lock – will not adversely impact anything. And above all else, you must have a healthy respect for what you’re doing lest you decide to anger the SharePoint Gods. This post is for all my peers out there that do a little SP Admin work on the side, but – for a variety of reasons – don’t get into the SQL side of things much.

SQL Management Studio is the best (and only?) tool used to query SQL. It’s a pretty straightforward, easy-to-use product. Open it up, connect to a database, and click the “New Query” button:

image

Pretty simple. From here you can query SQL (i.e. “ask” SQL things):

USE [database name]

select * from [database table]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The Use statement dictates which database you want to query. There is a dropdown near the “New Query” button, but the Use statement is so much easier to use. Plus, I’ve – on many occasions – forgot to select the correct database from the dropdown. I just use the Use statement from now on. “Select *” means you want to return all the columns in that table. This can be helpful to use when you just want to poke around and learn about SharePoint’s innards.

Use [database name]

select * from [database table] with(nolock)

 

“with(nolock)” is important because it doesn’t lock up the table while you’re querying it. I again learned this one the hard way. Locking up tables is a bad thing, meaning no one else can use said table until the query is done. Don’t do what I did and lock up the content database. Does not make for good job security. Furthermore, it’s generally a good idea to run just about any query (at least in the case of SharePoint) with no lock on it.

One last query nugget of wisdom. Replace the * in the select statement with column names (comma separated). So for example:

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Use [database name]

select LastName,FirstName,Address from [database table] with(nolock)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Your query will return the results with just the Last Name, First Name, and Address columns. Doing so will help clear the query results up and remove any extraneous data.

These 3 basic queries – while n00b’ish – are very valuable to know and will hopefully help you when working with Management Studio.

#TechOnTap review

So that whole promise I made earlier in the week to write everyday? Yeah, didn’t work out so well. Sorry…

Yesterday I gave my PowerPivot presentation at #TechOnTap up in Appleton, WI. This is a very cool IT speaker series that takes place at the Stone Cellar Brewpub. I highly recommend both #TechOnTap and the Stone Cellar Brewpub. Together they’re an out-of-this-world experience.

Chuck Heinzelman started things off with a nice overview of Kerberos in SharePoint. The use case scenario was around SharePoint 2010 & SQL 2012 SSRS…something I’ve been testing off-and-on for the last 4-months or so. I’ve got it 90% of the way and (I’m hoping) Chuck filled in the remaining 10%. When I get a free minute in the next week or two (yeah right) I’ll see if I can finally fill in the remaining gaps and get Kerberos going all the way (blog post to follow that).

Rob Bogue was up next with an overview of Forms based authentication. I’ve been busy at work putting together an Internet site on SharePoint and I’ve had to blaze this trail before. Let’s just say it’s a somewhat confusing prospect. Rob is always an entertaining presenter.

Rick Fischer rounded things out with a talk on InfoPath. Nicely done overview of InfoPath’s functionality. There’s a lot of good stuff in the product and it can open up a lot of possibilities for an organization. In my mind, InfoPath is to SharePoint like Outlook is to Exchange.

I was the day’s headliner (i.e. I went last). Had some awesome questions from the audience. The demo always seems to blow minds. It’s that type of reaction that makes me enjoy speaking about PowerPivot.

Lunch was plentiful (sandwich bar) and the beer was even better. Did I forget to mention it was all you could drink? The next event will be in October and you can bet I’ll be attending. I hope to bring some of my Trek cohorts up there with me too.

Thanks to the 3 “brewmasters” for throwing #TechOnTap and inviting me to speak: Derek Schauland, Jes Borland, and Mark Cyrulik. Very cool people indeed. Went to dinner afterwards with Derek, Mark, and Tim Florek. Had a good time and learned even more that the Appleton area has an extremely tight-knit group of IT practitioners/users. It’s so refreshing to see a group root for each other so much. Madison has similar groups, but nothing consolidated and cohesive like they do up in Appleton. Awesome sauce.  

#SPSSTL recap

I made a promise to myself to start writing a little everyday. Just 20 minutes every day to get something down on “paper.” I’m long out of writing practice and doing this will help a lot of things. But enough about that, let’s talk about SharePoint Saturday St. Louis!

My wife and I had originally planned to drive down Friday, but we came up with this wild idea to drive down Thursday night instead. So with the car packed and 2 little kids in tow, we left at 5:30 PM. Word to the wise… I don’t recommend starting a 7-hour car ride with two children under 2 that late. Everything went fine until hour 5, then all hell broke loose. I’ll spare you the gory details, but let’s just say my 11-month old had had enough of riding in the car. Family drama aside we made it fine to my father-in-laws just fine.

Friday night was the speaker’s dinner at the Moonrise Hotel. VERY nice. The food was fantastic, lots of new faces for me, and they even had an open bar. The more I go to these things the more I begin to notice that SharePoint Saturdays are very much a local thing. You’ll have the occasional speaker come from far away, but for the most part, the folks speaking and attending are local. Most of the folks I spoke to on both days thought it was crazy that I would come all the way from Wisconsin to attend #SPSSTL, but when I tell them my wife’s father-in-law lives close by they didn’t think I was so crazy.

On Saturday morning I noticed that my CloudShare environment PowerPivot wasn’t refreshing correctly. I spent 95% of Danny Jessee’s presentation on Facebook, Cloud, and SharePoint in the back of the room trying to fix things. By the 1-hour mark I decided to go with my backup environment. Wasn’t difficult to get up and running, but the whole ordeal was a HUGE pain. From what I did see of Danny’s presentation it was pretty slick.

Next presentation was Virgil Carroll’s on Information Architecture in SharePoint. Honestly, this guy could enthrall me just by reading the phone book. I saw him last year at the Twin Cities SharePoint Saturday and he was awesome. If you get a chance to see him I highly recommend it if for nothing else to see his presentation style.

Lunch. I was too busy talking to vendors that the catering vendor (St. Louis Bread Company; Panera to those folks who live outside the area) ran out of box lunches. So I was stuck with a bagel for lunch. Oh well.

Next up was Enrique Lima’s presentation on SQL best practices. SQL is still very much black magic to me in some ways and Enrique removed some of that mysticism for me. You can check out his presentation HERE.

The last session I saw before mine was Todd Kitta’s BI presentation. Now I’d be lying to you if I told you I wasn’t nervous that he would cover every point in my presentation. Thankfully he did not (whew!). Probably the best part of the weekend for me was when he covered Power View from a 100K ft level. I haven’t had a chance to test it yet in my own dev environments so it was nice to get a view of it. I didn’t know that Power View and PowerPivot work pretty closely with one another (i.e. you can take a working PowerPivot and turn it in to a Power View report). I was also curious to find out how you build Power View reports. I was somewhat disappointed to learn that there is yet another app needed to build Power Views; however, it’s all browser based and it’s pretty simple to use. So I’d call that a wash. Good job Todd!

Finally it was time for my presentation. I learned a valuable lesson right from the start of my presentation. When presenting to SQL Saturday’s, the title “Demystifying PowerPivot from the SharePoint Admin’s perspective” means something entirely different than it does to a SharePoint Saturday audience. Had about 12 or so people come. Not bad, but definitely not standing room only like it was at SQL Saturday back in April. My presentation caters to just about anyone, but the folks who did come appeared to get a fair amount of information out of it. At the #SharePint afterwards I talked to a couple of folks who – once I gave them the background on my presentation – were bummed they missed it. Definitely violated the first rule of anticipating your audience. I guarantee I won’t make that mistake again. Any ideas on a new title?

Overall, I had a good time in St. Louis. #SharePint was held at Moonrise again up on their rooftop bar. Certainly a hidden gem in St. Louis that I would recommend to anyone visiting STL this summer. You can find my slides to my presentation at the following: PowerPivotSPSSTL.pptx. Thanks!