I always thought the definition of getting screwed twice was this; A German student who auctioned her virginity and ended up paying 50% in taxes.

But it turns out that identity values in SQL Server can also screw you twice!

I used to really like identity values and problems have been rare, but this week, inside of a few days I've been screwed twice by identity values.  This is also the year of embracing merge replication, time to meet your new friend the guid!

No need to rehash what happenned with identity values, but altering a table from int to bigint with 30 billion+ rows is not possible.  Found two very helpful posts, i include their links here as they were most helpful:

To resolve (albeit temporarily) identity values running out for an int, do not forget you have the negative values.

http://sqlfool.com/2008/11/max-int-identity-value-reached-dbcc-checkident/
http://dbwhisperer.blogspot.com/2009/04/which-identity-column-is-running-out-of.html

Definetly we'll add a new automated health check, will probably fit in nicely with our script that check row counts occasionally to also start checking identity columns and raising alerts on certain thresholds.

I'll also be pushing back much harder on development teams that want identity columns with an int data type.

Well, it continues to be a pain in my ass, the inability to grant truncate table permissions on a table to specific users without giving them excessive rights.  SQL 2K5, no solution, SQL 2K8 no solution, well we did get a bunch of other stuff, so we'll continue to suffer.  I originally posted an article on Granting Truncate Table permissions in 2008, here is that link.  Recently the need has arisen to have the ability log failures for the solution.  The code was modified by Holland Humphrey and I'm posting the solution again with his updates.

The solution basically uses a schema, dba, to hold 3 tables for a stored procedure which is used to truncate tables.  The 3 tables, Truncate_List, Truncate_Audit and Truncate_Fail.  A user is granted permissions to a stored procedure in the dbo schema that performs the work and verifies that the table is authorized for truncate, and logs the success to truncate_audit and the failure to truncate_fail.  One could argue that truncate_audit could hold both success and failure, but I'll leave any possible enhancements up to those that desire to do so.

The original inspiration for this actually came from the Oracle group where I work.  Oracle has the same problem as sql "Grant Truncate on {table} to {User}"...so simple....but it's not there in SQL or Oracle.  I basically translated the Oracle solution into SQL Server.

Every upgrade we do from SQL 2000 to 2K5 or 2K8 uses this solution and allows us to reduce our permissions set to the lowest possible necessary.

Here are the scripts for the solution.

1.  Create a Schema Called DBA {if you prefer something else, adjust the tables and proc}.

2.  TruncateSolution_CreateTables.txt (4.67 KB)

3.  TruncateSolution_CreateProc.txt (4.34 KB)

Starting in December I purchased the following books.  Most of these were related to new projects where I work and knew I'd need to sharpen a few skills, Encryption, SQL Express, Merge Replication and security.

• Beginning SQL Server 2008 Express for Developers: From Novice to Professional - Robin Dewson
• Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5
• Mobile Merge Replication - Rob Tiffany
• SQL Server MVP Deep Dives
• Expert SQL Server 2008 Encryption - Michael Coles, Rodney Landrum
• SQL Server Forensic Analysis - Kevvie Fowler [bought but not reviewed]
  

Reviews:

Beginning SQL Server 2008 Express for Developers: From Novice to Professional - Robin Dewson

This book was a disappointment, but I knew when purchasing it that it was probably not going to be of much help; but I had to start somewhere.  We're preparing to migrate from MSDE to SQL Express 2K8 in 10,000 locations; I was involved in the intial deployment (and patchings) of MSDE and I really need to figure out how to avoid the gotchas with SQL Express; this book wasn't it.  Over-all it's a good book, but it's not meant for an existing SQL Server DBA, I would expect even a Jr. DBA to already be familiar with the content.  It truly is meant for an access developer who wants to learn SQL Express; I gave it to our local access programmer at work {though it's actually a she and she's not in IT or a programmer, seems that is where most access db's begin life}, maybe she'll develop a new career path, we seriously need more women in IT, I digress.

Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5 Mobile Merge

This book was great.  We're deploying Merge replication with up to possibly 10,000 differnt locations to SQL Express 2k5 and that is why I purchased this book.  While not an exact guide or road map for what we're doing [we're not using SQL Server compact edition], most of the concepts apply.  It has really been helpful, we're still in the planning and proof of concept stages, so it remains to be seen if we'll continue with this design at work, but I feel this book helped us greatly.

SQL Server MVP Deep Dives

This book was great.  I kind of avoided it at first, as I don't like books that too many people jump on as good, but this one lived up to that promise.  Very few parts of the book were lame ! [there's a glowing recommendation].  I put a sticky next to each new thing I learned and when I was done, i'd say there were at least 15-20 stickeys across the book, that's worth the money I paid for it.  It's also nice as you can read this book in quick "snippets" flipping to what subject interests you and what you have time to read.

Expert SQL Server 2008 Encryption - Michael Coles, Rodney Landrum

This book is good, though a bit dry, I'm still not all the way through it...reading about encryption is not what I'd consider an exciting topic, but it is a skill i'm going to need and it was seriously lacking.  We're preparing to bring in a 3rd party hardware device to provide consistent encryption and key management across the entire enterprise, which is why I needed this book.  So far it's been a great help, as I don't feel like a complete moron as I attend the endless meetings on implementing enterprise encryption where i work.

SQL Server Forensic Analysis - Kevvie Fowler

I haven't got to reading this one yet.  I'm looking forward to it, as security has become a major focus in ways I couldn't even imagine 2 years ago.  I thought the description of this book is interesting, as it approaches security from the aspect of determine what was compromised, from that I hope to learn better practices in security data....I'll update on that later.

This has to be one of the worst ever toys for kids.  There are some positive reviews out there for this, and I'm convinced those are paid reviews or something.

This toy does not work, it requires heavy objects to weigh it down and it is near impossible to get the car to run on the rope.

A huge disappointment for the kid awaits with this toy.

I don't blog product reviews, but this one sucks so bad i had no choice.

I'd pay money for someone to show me this toy working...

Need an H&R Block Coupon ?

Coupon's for new H&R Block Customers, $30.00.

Drop me a comment.

Stock Break Even Work Sheet.

This is something I worked out a while back.  Oddly I couldn't find an easy formula to plug in for excel, so I had to create one.  Recently somebody asked me for it, so I wanted to post it here so I could reference it.  The reason for creating this is I was trying to buy some ford stock at a 1.60 a share and wanted to know what the break even price was if I had to bale out of it.  Of course I didn't bail out and today it's a $9 something a share, "Quality is job 1"; Ford made my year !

Basically I needed a formula that would help me calculate how much a stock must rise in price to cover my commissions and taxes and break even.

There is some really good reading here in this book, Page 116, "Calculating the Stock Break Even Sales Price", my formula is adapted from that, good book to read.

http://books.google.com/books?id=YmZV-Jxt28kC&pg=PA114&lpg=PA114&dq=break+even+formula+for+selling+stocks&source=bl&ots=dR84EvLZGH&sig=dL-LdOG4oEptNx1wrrW9xaisjUY&hl=en&ei=NUGxSeHSAo3Btgequ-TDBw&sa=X&oi=book_result&resnum=1&ct=result#v=onepage&q=&f=false

Technincally my formula is not 100% accurate as depending on your particular tax situation the rate can can change between 15% or 28% and there is some "cost basis" versus "individual lots".  The forumula is just meant to be used as a guide, your actual mileage may vary.

I've updloaded the excel spreadhseet here.
Stock_Break_Even_Price.xls (22.5 KB)

Here is the formula, surprisingly my Algebra was terrible and it took me awhile to solve this.

Variable Description
 
N # of Shares
B  Buy Price per Share
C1  Buy Commission
C2 Sell Commission
P  Purchase Cost (Cash out)
X Sell Price Per Share
T Total Cash In
TR  Tax Rate
TX Total Taxes
G Gain
 Calculate Gain

Example Ford Stock 1.60 per share, 100 shares, 10 commission for both buy and sell
 
Description   Formulas                          Example
Cost            (N*B) + C1 = P                 (100 * 1.6) + 10 = 170
Gross           (N*X) - C2 = T                 (100 * X) - 10 = T
Taxes           ((N*X) - (N*B))*TR=TX     (( 100 * X) - (100 * 1.6)) * .15 = TX
Break Even    T-P-TX=0                        ((100 * X) - 10) - 170 - ((( 100 * X ) - (100 * 1.6)) * .15) = 0
                                                        ((100 * X) - 10) - ((( 100 * X ) - (100 * 1.6)) * .15) = 170
 How to solve for x ?                            (100X - 10) - ((100X  - 160) * .15) = 170
                                                        (100X - 10) - (15X  + 24) = 170
                                                        100X - 10 - 15X  + 24 = 170
                                                        100X  - 15X = 156
                                                        85X = 156
                                                        X=1.835294118

Hunting Public land evokes images and stories of many orange hats grouped together shooting each other !

But when you don't have access to private land, you have to go public!  This past weekend for opening day of deer season, my buddy Dale and I went down to Clinton Missouri, around Truman Lake to hunt.  There is a lot of pulic land around Truman lake for hunting, i've read estimate of up to 75,000 acres.

The key is scouting, scouting, scouting, and lots of walking.

We ended up not doing as much scouting as we wanted, but found what we thought was a nice area with lots of potential.  We didn't see much.

There was a lot of shooting around us, but they must have been poor shots as we didn't see anyone with a deer.

Lots of "road hunters", many would drive down a path, jump out for 30 minutes and then walk back, not sure what these guys were doing; maybe they weren't seeing much either and looking for a better place.

Ended up having some of these road hunters blast 3 rounds in quick succession at some running does (there's a good idea !), and scared them right to the side of me, about 25 yards.  Unfortunately they were so spooked that I was unable to swing around 45 degrees and get a shot off, so I just got to stare at them for a bit and hope to see them another day.

I finally found some private land to hunt for no cost, we'll see how that goes next.