adam hutson

I love taking on new projects, and I'm a do-it-yourself kind of person. If there is something that needs to be done around the house, I don't hire it out, I do it myself. It's not just physical stuff, either. That DIY mentality applies to everything in my life … doing taxes, starting businesses, writing websites/software, & designing logos. I really try to tackle all of these things myself.

However, as of lately, I've been feeling stretched really thin; too thin, actually. My "To Do" list hasn't been getting items moved over to the "Done" side very quickly. Two of the bigger items on that list have been a pair of websites, my personal website and my wife's business website, that both need similar functionality. I've written both from scratch and included a lot of really cool features on both. But the features are always taking me more & more time to add which is taking away from being able to get anything else done. The biggest feature is a blog engine. Now I know there are a ton of free blogging services out there, but I was really enjoying writing my own and figuring out all the nuances. And it has been a really fun challenge. But I need to let them go.

So I am deciding to change my focus a bit and give up control of having to do everything myself. That means that I will be transitioning both of the websites over to WordPress. It'll cost a little bit more money than what it currently does, but the opportunity cost of being able to work on other things will far outweigh cost of switching over. Plus I'll have more time to actually write content for the blog instead of the piping for it. And my wife will absolutely love the WordPress app for her iPhone. That alone should be the biggest reason to move.

With this change I will be losing some of my pride of creativity and accomplishment, but honestly, I'll get those back with my next project. Move two more "To Dos" over to the "Done" column.

This morning, when I came into work, I discovered that Windows Update had graciously restarted my machine for me. No big deal. But wait, then I realized I had some SQL work that I hadn't saved yet. NOOOOOO!!! I know, I know, ... always save your work. But yesterday, I didn't. So you can imagine how irritated I was to discover that all of that hard work was gone. Or so I thought.

A co-worker heard me getting mad at myself and chimed in that SQL Server Management Studio (SSMS) has an autorecover feature. WHAT!? Yep, when ever you start typing a query in SSMS, a file is automatically created and saves itself every 5 minutes. The location of this AutoRecover document is located in "\My Documents\SQL Server Management Studio\Backup Files\Solution [n]\". The [n] represents an integer that corresponds to how many SSMS IDE windows you have open. I only had one IDE open, with two query windows open, so I had a "\Solution 1\" folder with two files in it: ~AutoRecover.~vs47E8.sql & ~AutoRecover.~vsD5FD.sql.

Try it out. Open the folder mentioned above and open a new SSMS IDE and Query window and start typing out a quick SELECT. After about 5 minutes, you will see a new file prefixed with "~AutoRecover.~vsXXXX.sql". The X's are alphanumeric values that change with every file. The new file will get auto saved in 5 min increments from there on. When you close SSMS or a query window and it asks you if you want to save, click No, and watch the auto-generated file delete itself.

Wow, that was a HUGE relief. SSMS 2005 and higher has this feature built in. Thank you Microsoft for saving me from my own carelessness.

Simple-Talk Publishing has released a new free eBook: Defensive Database Programming with SQL Server, by Alex Kuznetsov.

The goal of Defensive Programming is to produce resilient code that responds gracefully to the unexpected. To the SQL Server programmer, this means T-SQL code that behaves consistently and predictably in cases of unexpected usage, doesn't break under concurrent loads, and survives predictable changes to database schemas and settings.

Too often as developers, we stop work as soon as our code passes a few basic tests to confirm that it produces the 'right result' in a given use case. We do not stop to consider what other possible ways in which the code might be used in the future, or how our code will respond to common changes to the database environment, such as a change in the database language setting, or a change to the nullability of a table column, and so on.

This book is filled with practical, realistic examples of the sorts of problems that beset database programs, including:

• Changes in database objects, such as tables, constraints, columns, and stored procedures.
• Changes to concurrency and isolation levels.
• Upgrades to new versions of SQL Server.
• Changes in requirements.
• Code reuse.
• Problems causing loss of data integrity.
• Problems with error handling in T-SQL.

You can download the eBook in PDF format here.

Microsoft Press has released a free ebook: Introducing Microsoft SQL Server R2, by Ross Mistry and Stacia Misner. There are 10 chapters and 216 pages. Here is a quick overview of the chapters:

Chapter 1: SQL Server 2008 R2 Editions and Enhancements
Chapter 2: Multi-Server Administration
Chapter 3: Data-Tier Applications
Chapter 4: High Availability and Virtualization Enhancements
Chapter 5: Consolidation and Monitoring
Chapter 6: Scalable Data Warehousing
Chapter 7: Master Data Services
Chapter 8: Complex Event Processing with StreamInsight
Chapter 9: Reporting Services Enhancements
Chapter 10: Self-Service Analysis with PowerPivot

You can download the ebook in PDF format here.

Update your readers to follow my new syndication feed at http://feeds.feedburner.com/adamhutson.

I have moved my syndication feed over to feedburner.  For those of you that are subscribers, please update the links in your readers.  My new feed is http://feeds.feedburner.com/AdamHutson.  I will be rolling out a new website in the near future and the old feed will no longer be active.  I'll send out a last post as a reminder when it's officially closed.  Thanks for understanding.

I've signed up to take my first Microsoft Certification exam.  I will be taking the MCTS Exam 70-433: SQL Server 2008 Database Development exam.  The date is scheduled for May 26th with 3 hours allotted.  I'm hoping that it'll be a breeze, since I've been writing TSQL for so long, but I'm still nervous.  I've been reading the Training Kit on it and it's a really quick read.  Wish me luck.

Got a fun requirement today.  Using SQL, the developer needed to convert an integer representing seconds into the format of dd:hh:mm:ss.  So 125 seconds would be 00:00:02:05.

I put together a quick script that creates a table variable, inserts some test values, and selects back out of it.

I have been really getting into RSS feeds lately and have subscribed to alot of ones that interest me.   I personally have been using the Google Reader on the iGoogle homepage.  It's a neat way to keep tabs on my varied readings.  One that caught my eye was a challenge by Pinal Dave.   Pinal challenged his readers to solve a puzzle that would return the size of each index for a speficied table. 

He started with a simple sp_spaceused [tableName] command that returned the index_size of the table and a selection from sys.indexes that returned a list of all of the indexes on said table.  But it was up to his readers to figure out how to get the index_size of each index, so that the sum of all of them would add up to teh result from sp_spaceused.  Read the article to follow along.  My answer is the 7th down (mine is simpler and suprising different from the other answers).  Pinal will post the solution with due credit on his blog.  Hopefully, I did well, seeing that he has almost 11 million readers.

Here is a quick overview of the puzzle:

My answer was:
DECLARE
      @objname nvarchar(776),
      @id int

SELECT
      @objname = 'TableToExamine',
      @id = object_id(@objname)

SELECT
      i.*,
      CASE
            WHEN ps.usedpages > ps.pages THEN (ps.usedpages - ps.pages)
            ELSE 0
      END * 8 indexsize
FROM sys.indexes i
INNER JOIN (
      SELECT
            OBJECT_ID,
            index_id,
            SUM (used_page_count) usedpages,
            SUM (
                  CASE
                        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                        ELSE lob_used_page_count + row_overflow_used_page_count
                  END
            )pages
      FROM sys.dm_db_partition_stats
      WHERE object_id = @id
      GROUP BY object_id, index_id
) ps on i.index_id = ps.index_id
WHERE i.object_id = @id

Well, that wasn't my exact answer.  I had a variable declaration that I really didn't need, but hey, I did it in like 5 mins.  So this version is a little cleaner. :)

ENJOY!  And in the spirit of fun, See if you can do it better!

Last week, one of our developers asked us in the DBA group what our standard was for currency columns.  My first thought was DECIMAL(19,4), but I couldn’t give a reason why it was standard.  I knew MONEY was essentially the same definition, but had heard that it was dangerous to use.  Suspecting a rounding issue, I began with a simple test.

DECLARE
    @dOne DECIMAL(19,4),
    @dThree DECIMAL(19,4),
    @mOne MONEY,
    @mThree MONEY
SELECT
    @dOne = 1,
    @dThree = 3,
    @mOne = 1,
    @mThree = 3
SELECT
    @dOne / @dThree * @dThree AS DecimalResult,
    @mOne / @mThree * @mThree AS MoneyResult

--------------------------------------------------------------------
DecimalResult  MoneyResult
1.000000       0.9999

Interesting!  Why did the MONEY type get truncated?  So another quick query gave me the output precision and scale.

SELECT
    SQL_VARIANT_PROPERTY(@dOne / @dThree * @dThree, 'basetype') AS DecimalBasetype,
    SQL_VARIANT_PROPERTY(@dOne / @dThree * @dThree, 'precision') AS DecimalPrecision,
    SQL_VARIANT_PROPERTY(@dOne / @dThree * @dThree, 'scale') AS DecimalScale,
    SQL_VARIANT_PROPERTY(@mOne / @mThree * @mThree, 'basetype') AS MoneyBasetype,
    SQL_VARIANT_PROPERTY(@mOne / @mThree * @mThree, 'precision') AS MoneyPrecision,
    SQL_VARIANT_PROPERTY(@mOne / @mThree * @mThree, 'scale') AS MoneyScale

-------------------------------------------------------------------------------------------
DecimalBasetype  DecimalPrecision  DecimalScale  MoneyBasetype  MoneyPrecision  MoneyScale
Decimal          38                6             money          19              4          

The decimal is now a DECIMAL(38,6) and the MONEY is still a DECIMAL(19,4).  Hmmm.  Why did one change and the other didn’t change. 

For an answer, I started digging into MSDN for precision & scale of data types.  I came across this article.  It had a fantastic table that described it.

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

So let’s expand on the first calculation, @dOne / @dThree * @dThree, which is actually two calculations, division and then multiplication.

e1 / e2 = DECIMAL(19,4) / DECIMAL(19,4)

precision = p1 – s1 + s2 + max(6, s1 + p2 + 1) = 19 – 4 + 4 + max(6, 4 + 19+ 1) = 43

scale = max(6, s1 + p2 + 1) = max(6, 4+19+1) = 24

So that leaves us with a DECIMAL (43,24) for the division portion, which is not a legal data type.  So SQL subtracts 5 from the precision to get the 43 down to a 38, and a matching 5 from the scale, which leaves us with a DECIMAL(38,19).

e1 * e2 = DECIMAL(38,19) * DECIMAL(19,4)

precision = p1 + p2 + 1 = 38 + 19 + 1 = 58

scale = s1 + s2 = 19 + 4 = 23

This leaves us with a DECIMAL(58,23) which is also an illegal data type.  So, again, SQL subtracts 20 from the precision to get the 58 down to a 38, and a matching 20 from the scale, which leaves us with a DECIMAL(38,3).  But wait, there is an undocumented rule that if the scale is being lowered by this process, it is never lowered below 6.  So we actually end up with a DECIMAL(38,6). 

With the second calculation, @mOne / @mThree * @mThree, all of the results are cast back to a MONEY or DECIMAL(19,4) which truncates data and is why we lose the accuracy of our scale.

What did we learn, … Don’t use the MONEY datatype in SQL Server.

When adding a new column with a default constraint to an existing table, keep in mind what you want your existing records to contain.  If you add the new column and set it to be allow nulls, then all of the existing records will contain a NULL, and any inserted records will have the default value.  However, if you set new column’s nullability to be NOT NULL, then the existing records will get back-filled with the default value.

Here is a quick sql snippet to show the behavior:

CREATE TABLE Test (Id INT IDENTITY(1,1) NOT NULL, Name VARCHAR(10) NULL)
INSERT Test (Name) VALUES ('you')
INSERT Test (Name) VALUES ('me')
SELECT * FROM Test
ALTER TABLE Test ADD FlagNull BIT NULL CONSTRAINT DF_Test_FlagNull DEFAULT ((0))
ALTER TABLE Test ADD FlagNotNull BIT NOT NULL CONSTRAINT DF_Test_FlagNotNull DEFAULT ((0))
SELECT * FROM Test
INSERT INTO Test (Name) VALUES ('us')
SELECT * FROM Test

As shown the FlagNull column has a NULL for the first 2 records as opposed to the FlagNotNull column has the default value for the same records.

Today, I got to go play in the fall leaves with my family out at the Nature Center.  What a great day! We are still having awesome weather and the leaves are perfect right now.  We even got to actually watch them fall from the trees.  The kids ate it up!  It was Suzanne's idea to go take them out.  In the past years, we never needed to go somewhere to experience leaves.  In our old house, we could just look out the windows and see all of the 50-yr old trees in our neighborhood in full color.  But our new house is in an area of newer homes, so there's not any established trees to enjoy.  That's one of the main things that I really miss about the old house.  Oh well, my kids are still getting to experience the fantastic fall leaves of the Ozarks.  Thanks to my wife's resourcefulness. 

Yesterday, my 5-yr old son, Logan, and my step-father-in-law, Jerry, and I went to play golf ... in November!  Being in the hign-60's, it was just TOO nice of a day to not take advantage of it.  Logan really has a  surprisingly good tee-shot.  He made it on the green with one tee-shot and chipped one in from 15-ft away in the rough!  Pretty stinking amazing for a kid that doesn't get to play too much.  We were playing out at Rivercut.  I think it's a well kept secret that Rivercut has a short 4-hole, chip-n-putt course next to the main 18-hole course.  Most golfers can see the short course from the 3rd hole on the main course, but don't realize what it is.  All you need to play is a wedge and a putter, but having a lob-wedge will help the ball stop on the hard greens a little better.  Each of the holes are par 3 (I think) and about 50-75 yards in length.  The tee-box and green is turf, but the rest of the field is grass.    The best part is that it's free!  It's a fantastic place to practice and let kids learn.  Jerry and I were able to just watch Logan and help him with his mechanics.  We didn't have anyone in front of or behind of us, so we played through a second round.  It was such a great experience.  We played a few balls at a time on each hole, so there was a lot of practice time.   Now Logan is ready to go back real soon.  So am I.

Recently, I had the opportunity to brush up on my SQL Server memory tuning skills.  It's been a while since I needed them, but I thought I'd throw out what I used for someone else's future benefit.

First, some background knowledge:
There are different physical memory limits for each Windows release.
The virtual address space (set of virtual memory addresses) is limited to 4 GB for 32-bit Windows.
The virtual address space is divided into two 2 GB partitions: one for use by the processes and other reserved for use by the system.
The address space (amount of physical memory) is limited to 2 GB for a 32-bit process.

In tuning SQL Server, there are 3 key things to look at: Physical Address Extension (PAE), Address Windows Extensions (AWE), &  4 Gigabyte Tuning (4GT).

Physical Address Extension (PAE) is a switch (/PAE) that is added to the boot.ini that allows an x86 processor to access and use more than 4GB of physical memory.  It's only used for 32-bit versions of Windows, as x64 processors can access more than 4 GB natively.  Most versions of Windows since 2000 Advanced Server support it.

4 Gigabyte Tuning (4GT) is a switch (/3GB) that is added to the boot.ini that increases the processes portion of the virtual address space from 2 GB to 3 GB.  4GT will limit the available RAM to 16GB, so if the server has more than 16GB RAM, 4GT should not be used. 

Address Windows Extensions (AWE) is a set of APIs that allows applications to access more than 4GB of RAM.

Which ones to use is determined by how much RAM your server has.  Here is a general rule of thumb for what to use and when:

Of course, doing research is part of the fun, so here are some references to keep handy:
Memory Limits for Windows Releases:  http://msdn.microsoft.com/en-us/library/aa366778(VS.85).aspx
4GT:  http://msdn.microsoft.com/en-us/library/bb613473(VS.85).aspx
4GT Technical Reference:  http://technet.microsoft.com/en-us/library/cc778496(WS.10).aspx
AWE:  http://msdn.microsoft.com/en-us/library/aa366527(VS.85).aspx
PAE:  http://msdn.microsoft.com/en-us/library/aa366796(VS.85).aspx

Happy tuning!

During the course of one of my last blog posts on Cursors, I ran across a niche piece of information that I didn't realize.  I had always read that FAST_FORWARD CURSORs were the fastest and least locking of the different CURSOR types.  What I didn't realize was that if the SELECT statement joins on one or more tables with a trigger table (INSERTED/DELETED), the CURSOR is converted to a STATIC CURSOR!  Unfortunately, most of the tables that I work with have some sort of trigger.  So this was really important information.  Read more about it in this MSDN article.

Differences between STATIC and FAST_FORWARD CURSORs:
They are both read-only concurrency model.  This means that no locks are held on the base table(s).  However, while fetching a cursor row, a Shared Lock will be acquired.  The Shared Lock can be avoided with a NOLOCK hint on the SELECT, or via isolation level.  However, once the row is fetched, the locks are released.
STATIC:  Think of this as a snapshot of the result set.  When it is opened, a temporary table is created in tempdb, which adds additional pressure on the database.  It does not reflect any types of changes to the underlying database, such as INSERTs, UPDATEs, or DELETEs.  This is a read-only cursor that can go forward and backwards.  There can be significant impact on server resources for creating, populating, and cleaning up the snapshot in the tempdb database.
FAST_FORWARD:  This will create a fast forward-only, read-only cursor with performance optimizations enabled.  It can only go from the first row forward to the last row.  It operates directly on the base table(s).  Unlike a static cursor, rows are not retrieved from the database until they are fetched.  Any changes to a row (from INSERT, UPDATE, DELETE statements) before the row is fetched, will be reflected in the fetched row.

Disclaimer:  If possible, avoid using cursors.

Here is a quick way to find the last reboot date and time of a SQL Server instance.

For SQL Server 2000 or 2005, use:
SELECT crdate FROM master.dbo.sysdatabases WHERE name = 'tempdb'

For SQL Server 2008, use:
SELECT sqlserver_start_time FROM master.sys.dm_os_sys_info

A few months back, we had a stored proc at work that was using CURSORs pretty heavily.  It had CURSORs inside of CURSORs.  This proc was one of our oldest, biggest, and most involved procs at over 2050 lines.  On larger clients, it could be called thousands of times a day.  All of the cursors were FAST_FORWARD types, which should have at least been somewhat performance friendly.  However they were SELECTing from tables that had triggers which implicitly converted the cursor type to be STATIC.  That's not good, because it was locking up the SELECTed table and causing all kinds of headaches.

So the decision was made to rewrite it to use table variables and WHILE LOOPs.  This sped the proc up immensely, as measured by elapsed time, at the cost of using more in-process memory.  You see, when a CURSOR is used, it basically creates a temp table on TempDB and iterates through it.  With a STATIC cursor, the selected table has locks placed on it in the transaction tables. 
The CURSOR alternative is to create a table variable containing the rows that would have gone in the CURSOR and use a counter variable to loop through it using the IDENTITY column on the table variable.  It's still row-by-row processing, but it's faster, more efficient, and doesn't lock.  The only downside is that it will use more RAM.  So beware if you're using SQL 2000, SQL Express, or an MSDE version as you may see performance degrade if the sqlservr process hits it's memory ceiling. 

I don't think I need to say this, but will anyway.   Changing CURSORs into WHILE LOOPs are better, but CURSORs into a set-based query is best.  It's always best to do a query in a single batch as opposed to row-by-row.

CURSOR code:

DECLARE @SomeID INT
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT SomeID
    FROM dbo.TableA
    WHERE ID = @ID
OPEN myCursor
FETCH NEXT FROM myCursor INTO @SomeID
WHILE(@@FETCH_STATUS <> -1)
BEGIN
    --do activity
END

TABLE VARIABLE, WHILE/LOOP code:

DECLARE @rowCount INT, @currentRow INT, @SomeID INT
DECLARE @tableVariable TABLE (RowID INT IDENTITY(1,1), SomeID INT NULL)
INSERT INTO @tableVariable(SomeID)
    SELECT SomeID
    FROM dbo.TableA
    WHERE ID = @ID
SELECT @rowCount = @@RowCount, @currentRow = 1
WHILE @currentRow<=@rowCount
BEGIN
    SELECT @SomeID = SomeID FROM @tableVariable WHERE RowID = @currentRow
    --do activity
    SET @currentRow = @currentRow + 1
END

It seems to me lately that I am continually writing, essentially, the same program for every problem.  The language has changed from VB6 to VB.NET to C#, but the pattern behind the design is the same.  It's always a Winform front end, common logic/objects in a middle dll, and data access from another dll that usually hits stored procedures on a SQL Server.  Not that it's a bad design, because it's not.  But I'm just afraid that I'm not growing as a developer.  I even use the same old design of a common starting point that calls a logical UI object that controls the flow to the user and back down to the database.  I guess it's considered a data centric design for n-tier architecture.

The point is that I need more depth and breadth in my solutions.  Sure I can be an expert in this one area, but how often is another area a better solution to the problem.  What do I do when a client-server WinForm isn't the answer?  I should have a better understanding of the WebForm/Web application world and the design patterns they use.  The funny part is, I've had jobs where I've written to mobile devices and done middle/backend work on web apps; I've even done Javascript on an ASP.NET app.  I was doing my part in those development shops; Providing the needed code to get the job done.  I just never felt completely comfortable with anything but WinForms.  At least to the point where I would tackle one by myself.

So here's a goal for myself: Explore more solutions than just WinForms.  My previous post on free iPhone development training should be a good starting point for completely thinking outside of the WinForm world.  I would ideally like to learn more about ASP.NET web apps and Silverlight.  Certainly the MVC design pattern deserves quite a bit of attention.  But I should also explore the new kid on the block in WinForms, WPF.  Anyway, it should be a fun journey and I'll surely have grown in the process.  I'll try to blog about what I'm doing to achieve this new goal.

~Adam

A few months ago, I was talking with a friend about programming on an iPhone.  We were discussing the potential audience that an iPhone commanded.  I didn't know much how the iPhone operated (I don't own one), however, it did pique my interest enough to research it a bit.  So, shortly after that conversation, I went to the Apple Dev Center and perused the Getting Started Documentation.  Well, it was more than a little confusing.  I'm a .NET developer and certainly didn't have a grasp on the iPhone or it's programming model.  Needless to say, I didn't make it very far in reading that initial documentation. 

Last night, I was surfing blogs and came across David Hayden's blog.  He had an entry on iPhone development and again I was reminded that I didn't really give it my all in getting under the hood of the iPhone.  David's post pointed to free iPhone training by Stanford University on iTunes.  WHAT?!  FREE?!  So now I have downloaded the almost 24 hours of video and 17 PDFs.  This time I will give the iPhone the attention it deserves.  Hopefully, I'll be able to put together a Hello World app on an iPhone.  (Anyone want to let me borrow a Mac OS X and iPhone?!)

Use the following link to find the free training.  It should either open iTunes and allow you to download the videos/PDFs, or it'll prompt you to install iTunes.

http://deimos3.apple.com/WebObjects/Core.woa/Browse/itunes.stanford.edu.2024353965.02024353968

In starting this blog, I have to ask, What amount and kind of content makes up good blog?  My goal is for it to define me as a familyman, and professional.  It should be about what interests me.  That being technology, family, books, & thoughts.  I believe that it should reflect the balance in life in which I do all of those things.  For me, about 50% of my time is consumed by technology, 30% is enjoying family, and remaining 20% is split evenly between books & thoughts.  Hopefully, my content will be worthy of following.  Let me know if I need to include something else, or add/remove anything as I'm sure this will be a rough draft for content composition.

Personal branding is a new concept that I've just recently discovered.  The idea is that your professional and personal lives are interconnected.  Meaning that what you do after work is just as important as what you do during work.  This new blog is the first step of many to brand myself.  I am really excited about this new self-packaging approach.  Hopefully, it will give a better perspective of what goes on in my whole life, what drives me, what I'm thinking about, and where I'm going.  The whole picture can be viewed through this blog, and my personal Facebook, Twitter, and LinkedIn accounts.  Happy reading!