Wednesday, December 30, 2009

Goals for 2010

I have been thinking a lot about what where I wanted to focus next year and have been writing things down as they came to me. I have a lot of personal goals I want to accomplish next year like reading more non-technical books, but I am going to keep this list related to SQL Server. I did a lot of things last year that I deemed personal accomplishment like writing my first book, presenting at my first SQL Server event, and even becoming an MVP. This year I am going to focus more on hard-core learning and continue branching out and trying new things. I tend to add and remove things from my list throughout the year as they become relevant or irrelevant, but here is what I am starting out with.

  • Give an Online presentation – Every presentation I have done so far has been in front of a group of people. I want to venture out and experience the world of virtual presentations.
  • Present at one of the national SQL Server events – I started to put a goal here to submit a presentation for a national event, but how hard is it really to submit something. I think the ultimate goal would be to actually get accepted and present. My first thought is that I had no control over whether I was accepted or not. Then I realized I was wrong. I know there is certain criteria, and I can do my best to meet it.
  • Present at a minimum of four local events – SQLSaturday, User Groups, Code Camps, etc. 
  • Record a series of videos for SQLShare.com – I think recording videos would be another good way to sharpen my presentation skills.
  • Read all the whitepapers from the MCM Pre-Reading List - There are several links here that point to blog categories. I want to knock out all of the whitepapers first and if I have time, I will circle back around to the blogs.
  • Get SQL 2008 Certifications
    • MCITP: Database Administrator
    • MCITP: Database Developer

Tuesday, December 15, 2009

Yes, Table Variables and Temp Tables both use the tempdb

Pinal Dave recently wrote a blog post called SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth which basically said that he often hears that Table Variables are stored in memory and Temp Tables are stored in the tempdb. He went on to show an example by loading rows into both and showing the page count. He also asked if anyone knew of another method to prove that both Temp Table and TableVariable are created in tempdb.

I found this post interesting, because it seems like I have answered that same questions at least a few times in the last couple of weeks. So here is my take on it. The best way for me to prove that they both reside in the tempdb (other than pointing to this kb article) is just to look for it there.

The Code

Let’s take a look at the code we can use to look for the objects in the tempdb.

  1. Create a Table Variable and immediately insert the current time.
  2. Wait ten seconds and then create a Temp Table.
  3. Select the values inserted into the tables.
  4. Look for the objects in the tempdb.
  5. Cleanup by dropping the Temp Table.
--1
DECLARE @TableVariable TABLE (DT DateTime DEFAULT GETDATE() NOT NULL)
INSERT INTO @TableVariable DEFAULT VALUES

--2
WAITFOR DELAY '00:00:10'

CREATE TABLE #TempTable (DT DateTime DEFAULT GETDATE() NOT NULL)
INSERT INTO #TempTable DEFAULT VALUES

--3
SELECT DT AS TableVariableTime FROM @TableVariable
SELECT DT AS TempTableTime FROM #TempTable

--4
SELECT * FROM tempdb.sys.objects
WHERE type = 'U'

--5
DROP TABLE #TempTable

You can see the output of the preceding code in the following image.

image

As you can see the TableVariableTime corresponds to the object created in the tempdb named #73BA3083, which is just hexadecimal of its object_id 1941581955, and the TempTableTime corresponds to the #TempTable object created ten seconds later. For space reasons, I didn’t include the entire object name for the Temp Table, but each object that gets created will have unique name as well.

Just for Fun

Now that I know I have objects in the tempdb, what do you think will happen if I try to manipulate them by the name in the tempdb instead of the names we gave them? Well, one of the first things I learned when I started working with SQL is that if I know the name of a table I should be able to insert some data. I can get the name of the table that exists while I am running the code and execute the INSERT statement using dynamic SQL by using the following code.

DECLARE @TableVariable TABLE (DT DateTime DEFAULT GETDATE() NOT NULL)

DECLARE @SQL VARCHAR(1000)
DECLARE @Name VARCHAR(50)

SELECT @Name = name 
FROM tempdb.sys.objects
WHERE type = 'U' AND create_date > DATEADD(s,-2, GETDATE()) 

SET @SQL = 'INSERT INTO tempdb.dbo.[' + @Name + '] DEFAULT VALUES'
PRINT 'Table Name: ' + @Name
PRINT ''
PRINT 'Query: ' + @SQL
PRINT '' 

EXEC (@SQL)

Ehh. Wrong Answer.

image

You can see in the preceding image that SQL Server ignores the request and generates an error. So, while the objects exist, and you even know the name, the only way to interact with them is through the front end.

Sunday, December 13, 2009

What is your Biggest Weakness?

David Stein (BlogTwitter) started a chain post the other day titled “What is your Biggest Weakness?” The Classic Interview Question. He tagged Brent Ozar (BlogTwitter) who tagged Thomas LaRock (BlogTwitter) who then tagged me. So, here goes…

I think one of the hardest things I have had to overcome since becoming a DBA is that I have always been the type of person to just “wing it” or just play things out and see what happens. I have never been the type of person to book a room in advance when traveling, learn a song note for note when playing music, follow a recipe when cooking, or follow directions when assembling a product. Besides, most of these instructions are common sense anyway.

Instructions by ppz.
Do not plug the coffee machine into the baby!

Even when I was a developer, this mentality worked out great. I knew what the end result had to be, and as long as I met all the requirements on or before the deadline, everyone was happy. Nobody really cared what happened in between. Meaning, I could just do my favorite thing and “wing it”.  

While this philosophy has gotten me through most of my life without a hitch, I found this not to be the case when I changed my career to a DBA. All of a sudden I was working on highly critical production systems and the smallest mistake could be disastrous. It took me a while to come around, but I soon started creating checklists, back out plans, and even scripts with step-by-step instructions.

I still don’t really like to have a detailed plan for most things, but I really have to consider the possible outcomes now before I throw caution to the wind. If I have an extra washer left over after putting together a grill, I’ll probably be okay. On the other hand, if I forget to backup a database and I need to rollback an upgrade, I may be sitting in the basement the next day with a can of bug spray looking for my red stapler and wondering if anyone’s seen my paycheck.

I’m gonna tag some Florida folks Kendal Van Dyke (BlogTwitter)  and Jack Corbett (BlogTwitter) to see what they have to say.

Thursday, December 10, 2009

Top 10 Policy-Based Management Links

 

I have been doing a lot of research on Policy-Based Management lately, so I thought I would publish a top 10 list on the PASS website. It hasn’t showed up there yet, so I thought I would post it here as well. There are a lot of other good links, so if I’ve missed your favorite, you can add it in the comments of this post.

Top 10 Policy-Based Management Links

1. Using the New Policy-Based Management Framework in SQL Server 2008

2. MSDN SQL Server Policy-Based Management Blog

3. SQL Server 2008 Policy-Based Management

4. Enterprise Policy Management Framework with SQL Server 2008

5. Evaluating Policies On Demand Through PowerShell

6. SQL Server 2008 Compliance Guide

7. Governing Your Enterprise with Policy-Based Management

8. Programming Policy-Based Management with SMO (6 Part Series)

9. Deploying SQL Server 2008 Based on Payment Card Industry Data Security Standards

10. Defining Complex Server "Health" Policies in SQL 2008

Lone Heading and Subheadings

I’ve been doing a lot of writing this year, and the editor for Apress that I have been working with published an article today that I thought was worth sharing.

http://gennick.com/LoneHeadings.html

One of the mistakes that I made when I started writing was creating a level 1 heading and then immediately creating a level 2 heading without introducing it. That was an easy fix, but he goes a step further and talks about why you shouldn’t have a single subheading. It is definitely worth reading if you are currently doing or plan on doing any writing.