Hello, my name is Robert Matthew Cook. This blog is autoposted to from my main blog at www.sqlmashup.com. For more profile information or to leave a comment, please visit me there.
SQL Server Database SUSPECT state – SQL Server DBA According to BOL , a database in SUSPECT state is : “At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of…
How to Create Secure SQL Server Service Accounts I’m looking to install SQL Server and I know it will require a service account. In this tip we cover what should be done to configure the SQL Server service accounts…
Just Ship it. Good enough is almost always better than nothing at all. Many of you will smile and nod sagely when you read that. Sure, that seems pretty obvious. But many people…
Keyboard Shoes – QWERTY – Pics | Geekosystem If only you could write the next great American novel while taking a walk in the park. Well, now, you still can’t, but you can take a walk in the park in these kick-ass…
If you have experience with joining the military then thank you sir or ma’am but as a SQL Server DBA you are sure to have experience with joining tables using Transact-SQL. My first blog post was on this exact same subject and this post will be a condensed version of it, SQL Server Joins using Playing Cards There are three primary JOIN operators the Query Optimizer will pick from: Nested Loops, Merge Join and Hash Match.
Nested-Loops
Imagine you were asked to find all the Aces in a deck of cards. How would you accomplish that? You would probably flip them all over and scan across them looking for the Aces. You would likely do the same if you were told that this would repeat for three, four or five different ranks. What if you were told it would be for eleven, twelve or even all thirteen different ranks?
Hash Match
If you were going to match many different ranks then you would probably decide scanning would be too repetitive. Instead you might break the deck into a pile for each rank. The time it took to reorganize the cards will be made-up by not having to look at every single card when another rank is asked for. What if you were told to match all fifty-two cards one-to-one between two decks?
Merge Join
We need to take a small leap of faith to continue using this card matching analogy. For now image that you have the ability to perform a perfect shuffle, where exactly one card from each the left and the right stacks fall together through-out the entire shuffle. If you had to find all fifty-two card matches you would likely go with a Hash Match but now that you are bestowed with the gift of Perfect Shuffle you have a better option. You quickly sort each deck then perform a perfect shuffle and BAM! The analogy is more appreciable when the cards are already sorted or the number of them is much higher than fifty-two.
Find Last Restart of SQL Server How can I tell when my SQL Server instance was last started? In this tip we look at various methods of how to get the start time for a SQL Server instance.
Data Head | SQL RNNR Well, it’s official. I am a Data Head. It doesn’t change too much my level of geekiness. But I may have to rethink one of the answers given during that
Aaron Bertrand : Connect Digest : 2011-09-22 Where is SYSDATE()? I’ve talked about this one before, but due to a recent conversation about building a datetime value from separate date/time columns or variables,…
September Performance Contest | SQL RNNR This month SafePeak is sponsoring a contest centered around improving performance in SQL Server. The host of the contest is my friend Robert Pearl. You can
SQL University | The SQL UPDATE Statement Come learn SQL Server from the ground up! Great learning resource for new DBA’s,accidental DBA’s or experienced DBA’s who want to refresh on their basics. This blogging…
SSMS Tools Pack 2.0 is out! | Jason Crider I know I’m a little late on mentioning that this great resource has been updated. Hopefully I can do a series on “tools in your SQL Toolbox” someday, but for …
Replication DDL Triggers – Never Say Never Because this was a problem that Google was zero help on and I did find a couple other people afflicted with it, I figured I’d write a post about it. The issue was…
Aaron Bertrand : Want your bug fixed? File a good bug! In some circles I’m known as “the Connect guy” because I’ve filed a heck of a lot of suggestions and bugs that I’ve envisioned or encountered, and I’m often willing…
Andy Leonard : The Sky Is Falling Alarmist? Perhaps. Accurate? In one sense, but not in the most important sense. The Cloud is coming. Are you ready? “Figures Don’t Lie” – Foghorn Leghorn Consider the…
Indexed Views To explain what an indexed view is, let’s first look at what constitutes a view. A view may sound like a fancy elaborate thing, however all it is, is a saved…
Database Mirroring for Disaster Recovery If you’re currently using log shipping for disaster recovery, consider using asynchronous mirroring instead. Asynchronous mirroring offers enhanced manageability and…
SSIS Junkie : Delete all data from a database Sometimes I use this blog to share scripts that I know I’m going to need at some point in the future, this is one of those times. The script below will remove all…
Tagged as [SITE]
DBA Twibe – DBA Twitter People This page is for Twitter people interested in DBA. All things of interest to DBA’s of SQL Server, Oracle, MySQL & DB2 databases. Also some fun things
PASS First Timers Guide | SQL Awesomesauce Hi there ho there everybody! I am now a Big Sister Fearless Leader of a group of PASS Summit first timers! This is the page wherein I record all of my sage
The Perils Of Try…Catch Blocks | Shaun J Stuart The new Try…Catch T-SQL error trapping mechanism introduced in SQL 2005 is quite handy. But it does have some perils if you don’t think things through
The Modern History of the Resume [INFOGRAPHIC] While the look and feel of the resume has been quite constant for most of modern history, the resume has recently been turned on its head to including infographic,…
The sqlhelp hash tag and sponsored tweets. Please don’t. It isn’t that we hate marketing, but the sqlhelp hash tag is for Q&A only. All the other hash tags are totally fair game, but…
Tagged as [DENALI]
The Case to Upgrade to SQL Server Denali See if SQL Server Denali’s features, such as AlwaysOn, SQL Server Developer Tools, and columnar indexes, are compelling enough to justify upgrading to the new release.
XEVENT Provider in Denali XEVENT provider is new sqlps provider in Denali CTP3. To admit, I was surprised when I saw it. In SQL Server 2008R2 we don’t have any GUI for XEVENT (Extended…
Pointy-haired bosses & SQL Not my usual type of blog post but I couldn’t resist sharing this! Thanks to Scott Barrett . (Hopefully that doesn’t instantly remind you of your boss!!)
Tagged as [MASHUP]
SQL Server Graphical Query Plans Tutorial In this tutorial we will cover how to create SQL Server graphical query plans and how to read them to assist in tuning your queries.
Dealing With Multiple Filegroups Everything is good in moderation, and when it comes to having multiple filegroups in your database, I believe this to be true there as well. Having multiple
PASS Summit 2011 Alumni – Support now! – Twibbon.com Support PASS Summit 2011 Alumni – Say it loud, say it proud: You’re a seasoned veteran of the world’s ultimate SQL Server and BI training and networking event. Help…
Great Tools for Formatting T-SQL | SQL Awesomesauce I have a big rant I add to….well, to every single SQL session I give. That rant is on formatting code. You see, (I say,) you can’t even begin to understand or
Robert Pearl has heeded the call of hosting the T-SQL Tuesday blog party this month. Please click on the #tsql2day image above to learn more about why the topic is “data-presentation”. If you want to read more party-style blog posts then also check out the summary by Adam Machanic called A Year of Tuesdays: T-SQL Tuesday Meta-Roundup.
Presents
Who presents the data to your users? There are occasions when the DBA will be asked to write T-SQL for Manipulating Result Sets. Mr. Pearl mentioned having experience with Using Common Table Expressions in the invitation. I had a similar experience with Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS. Who should present data to the users? Although this blog party has T-SQL in the title this post will be more theoretical than practical.
Tiers
There are always exceptions to any rule but as a general rule I subscribe to the N-Tier / Multi-tier architecting of applications. There are entire books about the benefits but for the sake of a short post I would like to share just two benefits of this architecture for a DBA. If you plan to stay for the soapboxing then please take a minute and look over this diagram by Damon Armstrong on Multi-tier architecture from his post .NET Application Architecture: the Data Access Layer.
Tools
Transact-SQL and SQL Server are used to scatter and gather data. CLR and Business Objects are used for control flow logic. Windows Presentation Foundation and Forms/Controls are used for user interface presentation. Each is a great tool for performing their intended task. You have NEVER used a crescent wrench as a hammer right? In the same tongue in cheek tone, you such NEVER use T-SQL to figure out the next user product suggestion and NEVER use T-SQL to display a dollar sign on the right side of a number.
Really?
Let us pretend that you inherited a stored procedure that does format the output so that there is a dollar sign displayed on the right side of a number. Who do you suppose would be tasked if the user wanted a different format? (Euros, two decimal places, in millions, negatives in parenthesis) Each of these has the potential to become a major time suck and also cause some wicked stored procedure sprawl. It has happened, it is still happening and will probably continue to happen but hopefully you are better informed for avoiding the problem.