The coolest SQL Server function you never heard of

Ever heard of the SQL_VARIANT_PROPERTY function? I didn’t think so.

SQL Server developers very often make the mistake of making their NUMERIC fields too large. When faced with a choice of how to size the column, they’ll often think “make it way larger than I need to be safe”.

This works OK as long as you simply store and read these values, but if you ever have to perform math with these columns, particularly some form of division or multiplication, you may find your results mysteriously losing precision.

This is because SQL Server can only store a maximum of 38 digits per number… if the results of your mathematic expression may yield a number larger than that, SQL Server will be forced to downsize it and remove digits from the mantissa as a result.

Continue reading

Reading from Snapshot Databases With Multiple Tables

If you haven’t explored using Snapshot isolation in SQL Server, I recommend you give it a look. A snapshot enabled database allows the reader to get  a clean read without blocking.

Prior to this capability, the only way to guarantee a non-blocking read from the database was to sprinkle NOLOCK statements all over your queries. Clearly this is a bad idea because you’re getting a dirty read, but it’s really much worse than that.

Enter Snapshot isolation… When querying using Snapshot isolation, your query will read the state of the rows at the moment the query begins, prior to any outstanding transactions against those rows. This allows you to get the last known committed state of those rows (clean data) without having to wait for outstanding transactions to commit. This is critical behavior if you want, say, a website that doesn’t have slow loading pages.

Now, it gets interesting when you’re trying to read multiple tables in one batch from your Snapshot database. Let me show you an example.

Start with two sample tables:

create table t1 ( id int identity(1, 1), value varchar(50))
create table t2 ( id int identity(1, 1), value varchar(50))
insert into t1 values ('Test1')
insert into t2 values ('Test1')

Now, setup a set a couple of update statements, but don’t execute them yet:

-- Update statements
begin transaction
update t1 set value = 'Test2'
update t2 set value = 'Test2'
commit transaction

In another window, setup a read as follows:

-- Select statements
set transaction isolation level read committed
begin transaction
select * from t1
-- create some artificial time between the two queries
waitfor delay '000:00:10'
select * from t2
commit transaction

Now, execute the Select Statements code, then go back to your update statements code and execute the updates including the commit (you’ve got 10 seconds, so move quickly). Now go back to your select statements and wait for them to finish.

Here’s what you’ll get:

Using READ COMMITTED Isolation

Since your first query executed right away, it gets Test1, while the second query reads after 10 seconds (during which your update occurred) and sees Test2.

Now switch your test data back to its original state:

update t1 set value = 'Test1'
update t2 set value = 'Test1'

And change your select query to use Snapshot isolation:

set transaction isolation level snapshot
begin transaction
select * from t1
-- create some artificial time between the two queries
waitfor delay '000:00:10'
select * from t2
commit transaction

Now repeat the process… run your select query, switch windows and run your updates with commit, then switch back and wait for your select query to complete. Now you’ll get this:

shot2

Cool! Now I get Test1 from both tables, even though I updated the data between the two individual queries. How’s that work and why does it matter?

According to MSDN, SNAPSHOT isolation specifies that ‘data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction‘.

This differs from READ COMMITTED in which data ‘can be changed by other transactions between individual statements within the current transaction‘.

This can be pretty important if you are publishing data to a multi-table warehouse and that multi-table publication should be considered as part of a ‘set’. If you use READ COMMITTED in that scenario you can get, essentially, a mismatched set of data. This is usually not a good thing.

If you’re reading from a single table from your Snapshot-enabled database, then using read committed will be fine. You’ll get your nice non-blocking clean read. If you need to read multiple tables in one transaction, however, and you want those reads to be consistent, you need to explicitly use SNAPSHOT isolation and start a transaction!

Yes, that’s right… you need a transaction wrapping your select statements. Transactions are not just for updates… shock and horror!

————–

More information here:

http://msdn.microsoft.com/en-us/library/ms173763.aspx

http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx

http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

Motion Chart using D3.js

Ever since I saw Hans Rosling’s 2006 GapMinder TED Talk on African wealth and saw his use of Motion Charts, I’ve been looking for ways to implement this in my own websites.

Up until now, there really hasn’t been anything ‘framework-y’ enough to use it with your own data. Google bought the rights from GapMinder a year later, and you can do a limited motion chart in Google docs, but it would be difficult to integrate this into your own site.

Enter the new D3.js library…

This new javascript visualization library has an example that actually implements Rosling’s chart! Truly awesome!

I talked a bit about GapMinder and Motion Charts after seeing them in Jessica Moss‘ presentation on Power View at the latest RockNUG meetup. SQL Server’s new data analytics tool includes some motion chart capability which is cool but still limited to use within that application.

Now we can do motion charts (as well as hundreds of other visualizations) using this incredible new library in our web apps…

Here’s the Motion Chart example:

http://bost.ocks.org/mike/nations/

Here’s the D3.js sample page:

https://github.com/mbostock/d3/wiki/Gallery

And, if you haven’t seen Hans Rosling’s presentation… well, you simply must:

http://www.ted.com/talks/hans_rosling_shows_the_best_stats_you_ve_ever_seen.html

Deming’s Seven Deadly Diseases

William Edwards Deming was sent to Japan in the early 1950’s and propagated his ideas about quality control and production process throughout Japanese industry.

There’s a wealth of wisdom in Deming’s work, albeit much of it industrially focused, but I’m particularly fond of his “Seven Deadly Diseases” of management (with my comments):

  1. Lack of constancy of purpose
    • It’s clear that having some core concepts about what you are trying to do is helpful… simple, effective statements about what’s important to your company, what your company does, and perhaps what your department’s role is in helping to fulfill the company’s purpose.
  2. Emphasis on short-term profits
    • Encourages what Bob Lutz describes as what-can-we-get-away-with thinking.
  3. Evaluation by performance, merit rating, or annual review of performance
    • These systems reward results rather than process-improvement, which can be counter-productive, and thereby encourage workers to maintain the status quo rather than innovate… their goal is to ‘get it done’ rather than to improve how they do so.
  4. Mobility of management
    • Too much ‘reorganization’ interrupts and breaks process improvement initiatives. Probably happens so much because of #3.
  5. Running a company on visible figures alone
    • You cannot measure everything, but must nonetheless do things you think need to be done. Too many times are we told not to do something if you can’t show me it will be valuable.
  6. Excessive medical costs
    • A very interesting observation made over 60 years ago
  7. Excessive costs of warranty, fueled by lawyers who work for contingency fees
    • Maybe not so relevant to the software industry.

It’s also worth mentioning a few other items from “A Lesser Category of Obstacles”:

  1. Neglecting long-range planning.
    • I’m a little torn on this.  In software, too much long-term planning can be a waste of time, but you certainly can’t neglect it entirely.
  2. Relying on technology to solve problems
    • I see this all the time. Figure out your problems first please… I beg you… before you start buying software you think will solve it for you… it won’t.
  3. Seeking examples to follow rather than developing solutions
  4. Excuses, such as “our problems are different”.
  5. Placing blame on workforces who are only responsible for 15% of mistakes where the system designed by management is responsible for 85% of the unintended consequences.
  6. Relying on quality inspection rather than improving product quality.
    • Relying on software testing rather than changing how we build the software in the first place.

I’m often drawn back to these little pearls of wisdom and I continue to be amazed at the prevalence of many of them throughout the industry. Keep them in mind while you are trying to steer your own efforts.

Find stored procedures that won’t compile

Here’s a handy little code snippet.  Find any stored procedures that won’t compile on SQL Server.

The routine will list all procedures, showing ‘– Failed’ with the error messages for those that fail.

Note that recompiling the procedures may also throw dependency errors also that can’t be caught with try/catch block … these errors likely indicate an issue as well.

SET NOCOUNT ON

DECLARE @ProcedureName VARCHAR(2048)
DECLARE @ProcedureBody VARCHAR(MAX)

DECLARE @RoutineName varchar(500)

DECLARE procCursor CURSOR STATIC FORWARD_ONLY READ_ONLY
 FOR
 SELECT
 --TOP 1
 SCHEMA_NAME(schema_id) + '.' + NAME AS ProcedureName,
 OBJECT_DEFINITION(o.[object_id]) AS ProcedureBody
 FROM sys.objects AS o
 WHERE o.[type] = 'P'
 ORDER BY o.[name]

OPEN procCursor
FETCH NEXT FROM procCursor INTO @ProcedureName, @ProcedureBody

WHILE @@FETCH_STATUS = 0
BEGIN
 -- Might have to play with this logic if you don't have discipline in your create statements
 SET @ProcedureBody = REPLACE(@ProcedureBody, 'CREATE PROCEDURE', 'ALTER PROCEDURE')

 BEGIN TRY
   EXECUTE(@ProcedureBody)
   PRINT @ProcedureName + ' -- Succeeded'
 END TRY
 BEGIN CATCH
   PRINT @ProcedureName + ' -- Failed: ' + ERROR_MESSAGE()
 END CATCH

 FETCH NEXT FROM procCursor INTO @ProcedureName, @ProcedureBody
END

CLOSE procCursor
DEALLOCATE procCursor