Switching to a new venue!!

So as I posted before I was kicking around the idea of making a website with my name on it rather than just SQLRed.  I went ahead and pulled the trigger and moved my site to wordpress.  I'm going to leave this site up for a while but if you are interested in anything new I'm working on, make sure to check out my new site at https://chriswolffdba.wordpress.com/.

Let's get back to this blogging thing...

So it has been quite a while since I have done any posts so I figured I would give it a try again.  I'm of two minds about it though and I thought I would open it up for a discussion.  Speaking about SQL all the time is a little boring.  I was thinking I should put some posts about my interests as well in here (video games, music, movies, etc, etc..).  Talking to some fellow speakers, they say I should make another blog to address those subjects.

The other issue is should I continue to blog under this name or use my real name instead.  With Twitter I use tickytong so it can be a bit confusing I'll admit.  I'm tempted to get a wordpress one for free that way I can create it with my name.  Hard to tell if that matters or not.

If you have any suggestions or comments about these subjects, let me know.

Any who, I'll try to write a blog having to do with SQL and tech in general every week on Sunday.  I'm thinking about starting with subjects having to do Continuous Deployment and passing the certification exams.  Hope you guys enjoy them.

Another day, Another job and SQL Tip...

Apologies for not posting for a while but moving to a new job can be hectic.  I have been trying to get my head around new databases and ran into some code that I felt I needed to talk about.  This has to do with truncating the time on a datetime field.  I've seen this in many flavors but this is the one I currently ran across.

CAST(LEFT(datefield, 11) AS DATETIME)

My personal favorite back in my early days of SQL is this.

CONVERT(varchar,datefield,101)

When I went to PASS for the first time one of my favorite speakers, Itzik Ben-Gan suggested a much better solution.  When we are treating the datefield as a string, we are converting it from a number which takes up time.  A better way to to keep it in numbers by using the DATEDIFF function like so.

DATEDIFF(d,datefield,GETDATE())

This keeps it as a number and you don't have to convert back and forth in order to do comparisons like so.

WHERE DATEDIFF(d,datefield,GETDATE()) = DATEDIFF(d,'2012-11-02,GETDATE())

Just a quick one today but I'll try to get back on the ball on studying for the next post.

Bumps in the Road to Certification

I went through both of the study guides I mentioned in my previous post and looked up all the answers but a few of them threw me for a loop.  I'm going to go over a few of the head scratchers and show what I found.

Disable/Enable Unique

This one might seem simple but I have never disabled a unique constraint before.  After digging for a while I found that is was simpler than I thought.

ALTER TABLE testtable NOCHECK CONSTRAINT UN_test

This works for other constraints as well.  To enable you just take the no out.

ALTER TABLE testtable CHECK CONSTRAINT UN_test

I was expecting the syntax to be tailored to UNIQUE itself so this one threw me for a loop.

Split Merged table into two, Merge back together

 I got confused about this one because I thought it was referring to the MERGE command.  After realizing that this was in a group about partitioned tables, I found this nice little tutorial.  Basically the first thing you have to do is create a partition function.

CREATE PARTITION FUNCTION PFPartition(DataType)
AS
RANGE LEFT FOR VALUES (DATA)

Then alter the partition using the function.

ALTER PARTITION FUNCTION [PFPartition]() SPLIT RANGE (testrange)

ALTER PARTITION FUNCTION [PFPartition]() MERGE RANGE (testrange)

I work in a pretty small instance of SQL Server (Less than 100GB) so I don't use partitioned tables but this was still interesting to learn.  This also seems like something a DBA should take care of but I guess that line between Dev and DBA is becoming fuzzier as time goes on.

 Explore Security on Triggers (EXECUTE AS)

I'm not really sure on this one but I think the author means to use it in the syntax of the trigger itself.  Here is an example I came up with but if you think I'm incorrect on this please leave a comment.

 CREATE TRIGGER tr_test
ON Person.Address
FOR INSERT
AS
    EXECUTE AS LOGIN = 'sa'
   
    SELECT * FROM INSERTED;

So basically before the select statement runs in this trigger, I designate the user I want it to run as.  Like I said, I'm not 100% sure on this answer but it seems pretty logical.

 Others

Here are few ones that require a little more study on there own and I may post in the future about them specifically.

• Use Powershell and SMO (SQL Server Managment Objects) to create a .NET cmdlet
•  I have not used Powershell at all but I do occasionally help our web developer to create .NET applications so SMO would be good to know.  I found a tutorial on it here.
• Service Broker
• Working at a smaller IT shop I really don't have a need for this service but I did run into a good tutorial on MSDN 
• Run through DTA (Database Engine Tuning Advisor)
• I usually just play with queries until they work correctly, but this tool might save me some time.  I found a tutorial on MSDN. 
• Review Catalog Views
• I use the systems table quite a bit but usually only sys.objects and sys.columns.  This tutorial gives a good explanation and  shows all the information schema views.

 Well I'm going to get back to studying but if you have any feedback please post in the comments.  Good luck with your studying!

Power View in Excel 2013

I was at the Denver SQL User group last week and had a chance to listen to Brian Knight's presentation (brilliant speaker by the way) about Office 2013 with Power View built into Excel.  It inspired me to take a little break from my studying and dig in a little deeper.  I was trying to get this to work last year in 2010 but you had to set up a SharePoint server and a server farm and I'm not an admin so it was just a pain to do so.  With the new version it is built into Excel and there is no need for SharePoint (unless of course you want to share it online with every one).  I work for a company that uses a lot of natural gas data so I was especially interested in the new map add on that uses lat and long coordinates.  So using this article to guide me, I sallied forth and made my first attempt.

Before I start on how it works I just want to take a moment to talk about the install for Office 2013.  To be fully honest I had a lot issues installing the new preview.  When I got it going every time I would delete the new Power View tab, it would crash the program.  I tried to uninstall all my past versions of office because I thought was causing issues but when I tried to reinstall, it was a no go.  Looking around I was able to find an exe rather than the stream system Microsoft provided and it worked perfectly.  I'm willing to overlook this for now because it is still in preview, but just keep that in mind before you try.

Ok onto the meat and potatoes.  I took a sample of our gas production data which included the well name, date of the reading, MCF, and the lat and long coordinates.

 

The data is just a simple sample to play with. So using the article I mentioned earlier, I created two charts; a map and a scatter plot.

 

First lets talk about the map.  It shows a circle representing the amount of MCF or gas that the particular well produced over the entire data set.  to make it easier to look at, I made it color by the well name as well.  If we click on a particular well, all the others will dim so it's easier to look at.

 

You can also zoom in which is helpful for when the wells are close together.

 

 Now onto the scatter plot.  This was really cool because it has an animation feature which will move the data points over time.  I can't really show you in my blog, but if you look at this video starting at about 4 minutes in you can see the cool feature in action.  As for what I created, you can see in my chart it shows us starting on 1/1/2004.

When you click on the play button at the bottom near the years it quickly moves forward in time and the bubbles move around.  You can also pause it or click on a certain point in the timeline as well.

 

 Another great feature is that the two charts are interactive with each other.  If you click on the bubble on one it will do similar behavior in the other.  I haven't played with this too much to see if that can be disabled for certain situations.  More than likely you will just have to make another data set to make it independent.  The other part that I love is you can pull this all into Power Point and make changes to the presentation on the fly while presenting.

Once I finally got it installed, I was able to make the visuals above in about 10 minutes.  If you have spent any time using BI tools or Excel in the past you truly understand how much of a time savings that is.  This probably would of taken me all day to do in the past.  Microsoft defiantly needed to make a drastic change with Office and I for one think this is a game changer.

I have to present this sometime next week to my VP for his consideration, but after that it's back to studying for the test.  Till next time.