December, 2014 | Michael J. Swart

December 12, 2014

Obvious and Not-So-Obvious Writing Tips

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 10:54 am

Takeaway: I leave SQL Server behind this week and I give two tips for technical bloggers,

An obvious tip: Practice a lot
A not-so-obvious tip: Help your readers skip reading your article

First the obvious tip.

Practice in Volume

As far as tips go, practice makes perfect is kind of obvious, and ultimately a little disappointing. Just like “Eat right and exercise”, the phrase “Go practice more” is one of those things that is easier said than done.

I first heard about a Composition Derby when I read The Underachieving School by John Holt. John Holt was an English teacher and author and he describes the Composition Derby as a device he used to help kids practice writing. The kids in his English class get divided into teams and they are asked to write about anything they want (spelling and grammar doesn’t count). At the end of the competition, the team who has written the most words wins. That’s the only criteria, number of words. When kids don’t worry about making mistakes they feel free to practice more. And that frees them to improve faster.

But I think the volume of practice is the key here. I believe in Malcolm Gladwell’s 10,000 hours rule. The rule claims that it takes 10,000 hours to become an expert at something. I like the idea of the 10,000 hour rule, but the one thing I don’t like is that it gives a definite number. Eight hours of writing practice can yield results and 10,000 hours implies a finish line. For example, compare these two illustrations I drew. They both use the same reference photo but they’re spaced apart by about 1,000 hours of practice.

It’s easy to compare illustrations when presented side by side. It’s not as easy to compare writing but feel confident that with practice, you’ll improve and your readers will notice.

Make Your Article Skippable

The second tip is a little counter-intuitive. Make it easy for your readers to skim your article or even skip reading your article all together.

You have something important to write, and I get that. But when thinking about the reader-writer relationship, your article is all about your readers. Their need to read actually outweighs your need to write and ultimately your readers will decide what’s important. I’m notoriously bad at predicting whether a post of mine will be well received or not. And so I make my blog posts skippable. The readers who find what I write important will stick around.

Here are some methods I use that help readers stop reading. Consider using these methods in your own writing

Topic sentence (which I frame as a takeaway). Condense your whole blog into a tweet-sized sentence. Give everything away as quickly and clearly as you can. Leave suspense-building for mystery writers. For example, if you only read SQL Server articles, you probably haven’t made it this far. You probably didn’t make it past the first sentence.
Organize your article into sections with headings that can stand alone as an outline. It improves skimmability.
In general, put a high value on your reader’s time. Make every word count in helping you say the one thing you want to say and don’t say anything else.

Now here’s the crazy part, when you make your article skippable it actually has the opposite effect. These methods I use actually help readers stick around. Readers have a better mental roadmap of the content and they stay (see, you’ve stuck around this far!).

-- Comments (6)

December 3, 2014

Materialized Views in SQL Server

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 9:28 am

Update: Materialized views in SQL Server are called Indexed Views. Search for “indexed views sql server” for syntax and an introduction. What follows is a look at some of the differences between Oracle’s Materialized Views and SQL Server’s Indexed Views

What’s the difference between Oracle’s “materialized views” and SQL Server’s “indexed views”? They both persist the results of a query, but how are they different? Sometimes it’s difficult to tell.

I’m on the left (or am I?)

One difference is that SQL Server’s indexed views are always kept up to date. In SQL Server, if a view’s base tables are modified, then the view’s indexes are also kept up to date in the same atomic transaction.

Let’s take a look at Oracle now. Oracle provides something similar called a materialized view. If Oracle’s materialized views are created without the REFRESH FAST ON COMMIT option, then the materialized view is not modified when its base tables are. So that’s one major difference. While SQL Server’s indexed views are always kept current, Oracle’s materialized views can be static.

Static Materialized Views In SQL Server?

Yeah, we just call that a table. You can use a SELECT INTO statement and it’s pretty easy. In fact, for fun I wrote a procedure that does the work for you. Given the name of a view it can create or refresh a table:

/* This is a proof-of-concept and is written for illustration purposes, don't use this in production */
create procedure dbo.s_MaterializeView
  @viewName nvarchar(300),
  @yolo bit = 0 -- use @yolo = 1 to execute the SQL immediately
as
 
declare @persistedViewName nvarchar(300);
 
if not exists (select 1 from sys.views where object_id = object_id(@viewName))
  THROW 50000, N'That @viewName does not exist', 1;
 
select 
  @viewName = QUOTENAME(object_schema_name(object_id)) 
  + N'.'
  + QUOTENAME(object_name(object_id)),
  @persistedViewName = QUOTENAME(object_schema_name(object_id)) 
  + N'.'
  + QUOTENAME(N'persisted_' + object_name(object_id))
from sys.views
where object_id = object_id(@viewName);
 
set xact_abort on;
begin tran
  declare @sql nvarchar(2000);
  set @sql = N'
    IF OBJECT_ID(''' + @persistedViewName + N''') IS NOT NULL
      DROP TABLE ' + @persistedViewName + N';
 
    SELECT *
	INTO ' + @persistedViewName + N'
    FROM ' + @viewName + N';'
 
  if (@yolo = 1)
    exec sp_executesql @sql;  
  else 	
    print @sql;
commit

Which can be used to generate sql something like this:

    IF OBJECT_ID('[dbo].[persisted_vSomeView]') IS NOT NULL
      DROP TABLE [dbo].[persisted_vSomeView];
 
    SELECT *
	INTO [dbo].[persisted_vSomeView]
    FROM [dbo].[vSomeView];

Are Such Static Materialized Views Useful?

Yes:

They can be used to get around all the constraints placed on regular indexed views. And if you’ve ever implemented indexed views, you understand that that’s a lot of constraints. I think this benefit is what makes this whole blog post worth consideration.
Because it’s static, you can avoid all the potential performance pitfalls that accompany the maintenance of an indexed view (more on this next week).
Good or bad, the view doesn’t have to be created with SCHEMABINDING.
Indexing is strictly do-it-yourself. Chances are you want more than a single heap of data for your materialized view.

… and no:

Most obviously, the data is static, which is another way of saying stale. But notice how Microsoft promotes indexed views. They say that indexed views are best suited for improving OLAP, data mining and other warehousing workloads. Such workloads can typically tolerate staleness better than OLTP workloads. And so maybe materialized views are a feasible alternative to indexed views.
You have to manage when these views get refreshed. This means scheduling jobs to do extra maintenance work (yuck). For me that’s a really high cost but it’s less costly if I can incorporate it as part of an ETL process.
Using Enterprise Edition, SQL Server’s query optimizer can choose to expand indexed views or not. It can’t do that with these materialized views.

I didn’t write the procedure for any important reason, I just wrote it because it was fun. But I have used this materialized view technique in SQL Server at work and I’ve been quite successful with it. It’s not something that should be used often, but it’s always worth considering if you can understand the trade-offs.

-- Comments (20)