The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

"Documentation is the castor oil of programming. The managers know it must be good because programmers hate it so much" (p.262)"

– From 'The Psychology of Computer Programming', Gerald M. Weinberg 1971

It would be a great pleasure to point to a golden age where it was a standard practice to create the user documentation before the application. It is a pleasure I must forego because, unfortunately, it was always an unusual, though excellent, practice. The natural inclination of any developer has always been to shy away from documentation like the devil from holy water. Instead, we maintain the foolish myth that the source code 'is its own documentation', or that it is pointless to produce it because nobody would read it: a wild theory that I've never seen put to the test, though it is true that few developers even read their own code, let alone any documentation. However, it could be that with Agile's idea of user stories, use cases and usage scenarios, we are stumbling towards the realization that this is a most effective way of creating applications and one that almost guarantees success.

I discovered how effective the "document before you code" technique was in the late seventies, while running a programming house that created commercial applications for local businesses. We did fixed-price contracts, so if we got the estimation wrong, we didn't eat. It concentrated our minds towards getting our time and cost estimates right.

We hit on this method: first, we wrote the user-documentation, which we used to gain the contract and estimate the development effort. Then, the developers created the application to fit the documentation. As well as the operator's manual, we created a maintenance manual that specified all the support tasks and interfaces. It worked fine, though it was far less exciting than the instinctive method of making one's mind a blank and wandering towards the keyboard to write something vaguely splendid.

Nowadays, one cannot write user documentation the way that old-timers like I did it, since the user-interfaces are now too complicated to permit mere text description. No. Nowadays, the way to design an application is to write the user documentation along with mock-ups of the screen, describing all the user processes, workflows, and alternative navigation paths through the system. If you get aspects wrong, you delete the offending part and redo it until it is right. It isn't a substitute for getting the architecture of the application right; it forces you to get the architecture right, even if it isn't made explicit.

There are unexpected benefits from writing documentation for an application that doesn't yet exist:

• Mistakes in the user interface stick out like a sore thumb.
• The users tend to understand it easily, if done well, and can spot the mistakes easier than if you give them UML with fluffy clouds.
• It is much quicker and effective to alter it than to wait until you've started writing the application itself. A description of a form, or dialog box, is far easier to change than the actual thing created by computer code.
• The morale of the developers suddenly increases when they realize that they have avoided going anywhere near documentation.
• By doing the documentation, it becomes obvious if you have left vague any areas of the application, which are likely undo all your time and cost estimations.
• It forces us to learn the language and concepts of the business processes before designing the schema.

Let's face it, it is boring to do, but developers work best when singing from the same hymn sheet, and user documentation is the hymn sheet of application development.

Phil Factor

Phil Factor from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Tech News : General Interest

Book written in DNA code - Deoxyribonucleic acid or DNA – the chemical that stores genetic instructions in almost all known organisms – has an impressive data capacity. One gram can store up to 455bn gigabytes: the contents of more than 100bn DVDs, making it the ultimate in compact storage media....(more)

The Twitter Underground Economy: A Blooming Business - On the strange underground Twitter economy based on buying and selling Twitter followers......(more)

Tech News : Security

Hacking Brain-Computer Interfaces - In this fascinating piece of research, the question is asked: can we surreptitiously collect secret information from the brains of people using brain-computer interface devices?...(more)

Database of 12 Million Apple UDIDs Leaked - In this story, we learn that hackers got their hands on a database of 12 million Apple Unique Device Identifiers (UDIDs) by hacking an FBI laptop....(more)

Widely used fingerprint reader exposes Windows passwords in seconds - The UPEK Protector Suite is marketed as a secure means for logging into Windows computers using an owner's unique fingerprint, rather than a user-memorized password. When activated, the software writes Windows account passwords to the registry and encrypts them with a key that is easy for hackers to retrieve. Once the key has been acquired, it takes seconds to decrypt the password....(more)

Microsoft News : General Interest

First look: Windows Server 2012 brings the cloud down to earth - The highlights of the new operating system from the perspective of a small or mid-size organization. We take a look at the new storage and file server features of the operating system, and some of the ways that Server 2012 will make it easier for organizations to do high-availability and disaster recovery without making big investments in other hardware or software....(more)

First look: Hyper-V 2012 targets VMware's air supply - With the release of Windows Server 2012 yesterday, Microsoft significantly updated its Hyper-V virtualization platform. Available both as part of Server 2012 and as a free (as in beer) standalone installation, the third generation of Hyper-V brings with it a collection of features that largely commoditize the sorts of things you’ve gotten used to having to pay extra for....(more)

Windows Server 2012 is Here...And so is the Cloud OS - Official announcement of the release of Windows Server 2012......(more)

Hardware News

Amazon Kindle Preview: Paperwhite, Fire (2012), and Fire HD 7" & 8.9" - Amazon revealed four new Kindle devices: a new Touch e-reader called the Kindle Paperwhite; the latest update to the Fire (not the Fire 2, just Kindle Fire); the Kindle Fire HD 7", and Kindle Fire HD 8.9"....(more)

Blogs : .NET

Shape Up Your Data with Visual Studio LightSwitch 2012 - LightSwitch is a designer-based addition to Visual Studio 2012 to assist in working with data-centric services and applications....(more)

Blogs : Administration

What should a DBA do? - Most DBAs I know are mired in day-to-day firefighting and time-consuming tedium. This forces them to operate in reactive mode (because they don’t have enough time to “get caught up”), and keeps them from more valuable things they could be doing....(more)

CHECKDB internals: what is the BlobEater? - Having been asked several times over the last month about the query that drives DBCC CHECKDB and other consistency checking commands, which has a variable called BlobEater in it, Paul Randal explains what the query is doing. ...(more)

Strange Sch-S / Sch-M Deadlock on Machines with 16 or More Schedulers - When a system presents SQL Server with 16 or more CPUs, and you are using a high end SQL Server SKU, SQL Server will enable lock partitioning. Under a rare condition SQL Server may not associate the proper lock partition with the lock request, leading to additional locking overhead or possible deadlocks....(more)

When the table was last accessed (Unused Tables) - A DBA should keep a list of tables which have not been used / referenced by anyone in last few weeks. This script finds all unused tables since the service last started....(more)

Installing SQL Server on Windows 8 - Bob Ward provides shares his experiences installing SQL Server on Windows 8: "One of the things I often try to do is make sure I understand the experience of customers when installing new versions of SQL Server or existing versions of SQL Server on new Windows Operating Systems"....(more)

Blogs : Analysis Services / BI

Optimize SUMMARIZE with ADDCOLUMNS in Dax - If you started using DAX as a query language, you might have encountered some performance issues by using SUMMARIZE. The problem is related to the calculation you put in the SUMMARIZE, by adding what are called extension columns, which compute their value within a filter context defined by the rows considered in the group that the SUMMARIZE uses to produce each row in the output....(more)

Blogs : Backup and Recovery

Demystifying SQL Server Differential Database Backups - Incorporating differential backups will add complexity to your backup strategy but the benefits can be staggering. Between the storage savings and reduction in recovery time it’s clear that differential backups should be in your tool belt....(more)

Blogs : Computing in the Cloud

Azure Virtual Machine TempDb Placement - The recommended location for the tempDb on an Azure virtual machine, is the non-persisted D:\ drive. This drive is actually a hard drive connected to the underlying physical hardware, so it should offer better performance then the persisted data and OS disks. ...(more)

Blogs : Database Design, Theory and Development

Does This Datatype Make My Column Look Fat? - Tom LaRock provides a script to evaluate defined datatypes to the current data values in those columns. The idea being that if a column is defined as BIGINT but contains values that are best defined as a SMALLINT the script would recognize this and flag that column as a candidate for being altered....(more)

Timewarp: What Is a Relational Database? - As the cool-cat computing services (Amazon, Facebook, Google, Pinterest, etc) are make heavier use of NoSQL technology, Kevin Kline reflects on why Relational Databases were needed and why they aren't going away....(more)

Utility Queries–Structure of Tables with Identity Column - Louis Davidson provides some queries to interrogate the structure of tables, to find, for example, tables with no primary key or no identity column....(more)

Reddit’s database has two tables - In 2010, Steve Huffman explained how Reddit adapted their data storage while scaling their website. Kevin Burke discusses what can be learned from their approach....(more)

Blogs : Deep Into Windows

Microsoft Technical Fellow Mark Russinovich Updates Sysinternals, Adds Windows 8 Support - Mark Russinovich has been diligently updating his popular Sysinternals suite of free Windows tools over the last few months, fixing bugs, adding new features (including Windows 8 support) and generally giving many of these popular utilities a fresh coat of paint....(more)

Windows Slowdown, Investigated and Identified - An investigation of a Windows 7 home laptop running slower than usual. "It seemed to be spending a lot of time waiting on disk I/O. When I looked at Task Manager it showed that my laptop was caching about one fifth as much disk data as normal, but the cause of this was not obvious."...(more)

Blogs : DMO/SMO/Powershell

PowerShell Split-Path Bug And A Workaround - Split-Path is a cmdlet built into Windows PowerShell that returns a specific part of a path, e.g. a parent directory or a filename. But try it on a Path that doesn't exist on the machine running the command and you get an error......(more)

Tracking SQL Server Instances across the network - This script is pretty simple but offers great power to the DBA. How many times did you need to search for SQL Server Instances across the network ? I believe every DBA already had to do this at least once....(more)

Extract worksheets from Excel into separate files with PowerShell - I recently had need to dust off an old VB script I'd written years ago to get worksheets out of Excel files. I've also been curious about doing more with PowerShell, and besides feeling guilty about putting a VB script into use in 2012, it seemed like a really good learning opportunity....(more)

Blogs : Events

SQL Server vs. Oracle - Intrigued by the fundamental differences between Oracle and SQL Server? Join Grant Fritchey and Jonathan Lewis in a webcast, where they will cover a lot of the myths and misperceptions surrounding temp tables on both Oracle and SQL Server, how they work and how they affect performance. ...(more)

Agile Database Practice Training - The authors of the tSQLt database unit testing framework (which is what I use for database unit testing and test driven development) are running a course on how to get started....(more)

Blogs : High Availability/Disaster Recovery

SQL Server Cluster Failover Root Cause Analysis–the what, where and how - I know many of you get into situations where SQL Server fails over from one node of a cluster to the other, and you’re hard-pressed to find out why. In this post, I shall seek to answer quite a few questions about how to about conducting a post-mortem analysis for SQL Server cluster failover, aka Cluster Failover RCA. ...(more)

Blogs : Integration Services/ETL

SSIS Design Patterns - Michelle Ufford on how she came to co-author SSIS Design Patterns, a “recipe book” of common tasks that nearly every ETL professional encounters at some point in his or her career....(more)

Blogs : Performance and Tuning

How It Works: Online Index Rebuild - Can Cause Increased Fragmentation - SQL Server Books Online alludes to the fragmentation possibility but does not fully explain that the Online Index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF directives....(more)

SQL Server Statistics: Explained - Several DBA’s asked me these questions about SQL Server statistics at different customer places. Should we disable Auto update stats for the database and run maintenance plans instead? What sampling rate does Auto Stats use? Can we control it? Should we update statistics after rebuilding indexes during maintenance plans?...(more)

Plan Cache effects when dropping columns - If you drop a column on a table, does it also drop the statistics and remove the cached plans that relates to the column? To answer the question on statistics directly, yes. SQL Server will remove any statistics for the column that is dropped on the table. For the plan cache and any plans that relate back to either the statistics or the column, the answer isn’t quite as easy....(more)

Blogs : Professional Development

Write a Killer Technical Resume in Five Steps - Ever dreaded writing a resume so much that you put it off until the last minute? When you finally wrote it in desperation, were you happy with it? Are you kind of afraid to look at it now?...(more)

Where’s Your Company’s #SQLHelp Resource? - If you’re a SQL Server data professional on Twitter, you’ve probably seen the #SQLHelp hash tag. Which leads to the question – would something like this work in your own company? Could you start an open dialog between the admins and power users to get easy problems solved fast before they escalate to help desk tickets?...(more)

Management Myth #8: I Can Still Do Significant Technical Work - The temptation can be incredibly strong for managers—especially new ones—to step in when a technical problem arises. But, that isn’t a very good show of faith in one’s team members. As a manager, you have to delegate a problem and leave it delegated....(more)

Blogs : Security and Auditing

Dependency Injection: Huh? - Chances are, at some point in your learning, you’ve come across the term, “dependency injection.” If you’re still relatively early in your learning, you likely formed a confused expression and skipped over that part. Still, this is an important aspect of writing maintainable (and testable) code. In this article, I’ll explain it in as simple a way as I’m capable of....(more)

Blogs : Software Development

Using the TFS as a source control repository for SSMS projects - As TFS is mostly the choice for .NET projects, it becomes obvious that for DB too TFS needs to be used so as to have all source code in one environment and there is consistent way of making changes to such source code...(more)

The Winds of Change are a Blowin’ - Ajarn Mark Caldwell needed more visibility into the progress of the development team, and better project management tools. He explains why he thinks Team Foundation Server is the right choice for his team....(more)

Blogs : T-SQL

Announcing the AdventureWorks OData Feed sample - The AdventureWorks OData service exposes resources based on specific SQL views....(more)

Table-Valued Parameters need an alias! - I’m always the one to say RTFM but this one stumped me for a while. I had problems using a Table-Valued Parameter in a Stored Procedure today. The explanation is indeed in the documentation but it’s perhaps not as clear as it should be......(more)

Temp Table vs. Table Variable - Aaron Lowe provides a comparison table to describe the differences between Temp Tables vs. Table Variables....(more)

Compute Scalars, Expressions and Execution Plan Performance - The humble Compute Scalar is one of the least well-understood of the execution plan operators, and usually the last place people look for query performance problems. It often appears in execution plans with a very low (or even zero) cost, which goes some way to explaining why people ignore it....(more)

TSQL - Solve it YOUR Way - Gaps and Islands... With a Twist! - As part of the blog series TSQL - Solve it YOUR Way, today's topic is a variation of the popular T-SQL question titled "Gaps and Islands" followed by three different solutions presented by three of the more helpful and creative contributors in the TSQL MSDN forums, Steven Wang, Alejandro Mesa, and Tom Cooper. ...(more)