Click here to monitor SSC
Phil Factor
Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also : Google + To translate this article...

Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google. Besides the obvious Full-Text search, there are plenty of techniques for finding that pesky data that resists the normal SELECT blandishments. Phil Factor describes some alternative techniques. Read more...

Continuous Delivery and the Database

Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of software delivery and deployment by making build, integration and delivery into a routine. The way that databases fit into the Continuous Delivery story has been less-well defined. Phil Factor explains why he's an enthusiast for databases being full participants, and suggests practical ways of doing so. Read more...

Primary Key Primer for SQL Server

Every database developer uses keys, but without always understanding all the ramifications. They come with few hard and fast rules, but if you get them right from the start with a database design, the whole process of database development is simpler, and the result is likely to perform better. We asked Phil for advice, little knowing that the explanation might take a while. Read more...

Getting Data between Excel and SQL Server using ODBC

With ODBC, you can summarise, and select just the data you need, in an Excel workbook before importing it into SQL Server. You can join data from different areas or worksheets. You can even get data from the result of a SQL Server SELECT statement into an Excel spreadsheet. Phil Factor shows how, and warns of some of the pitfalls. Read more...

SQL Server ALTER TABLE syntax diagrams

The words in the documentation for the ALTER TABLE syntax on MSDN are accurate with forensic precision, but the potentially-useful 'syntax diagrams' look, to the untrained eye, to be the result of someone accidentally sitting on the keyboard. The answer for ordinary mortals like us who need to understand the syntax is to have railroad diagrams as well. Read more...

SQL Server CREATE TABLE syntax diagrams

Many of us have seen, on MSDN, the heading 'Syntax', followed by a rash of nested brackets and keywords, enough to put off the most resolute of code-cutters. However, there is a goldmine of information there, and Phil had an ambition to get at it, and share the gold. The result is this article, full of railroad diagrams Read more...

Database Deployment: The Bits - Agent Jobs and Other Server Objects

Databases often need more than just the database objects to run. There may be certain server objects and components, and SQL Agent objects, that are required as well. For an automated deployment, these need to be identified and their build script placed in source control. They then need to be deployed via the pre, or post deployment script. Phil spells out how and why. Read more...

PowerPoint Presentation Burnout

Phil's dread of Powerpoint sales presentations is already known to his readers, but we've never before heard the story of how an intern in his team gave them the necessary insights to find a cure for their unfortunate tendency to doze off, and convince them that they were charmless geeks. Read more...

The Art of the One-Pager

In which Phil finds himself in a place of work where, despite his penchant for insulting or upsetting senior managers, he survives purely due to his ability to distill complex IT documents into one-page strategy papers Read more...

Project Mismanager

In IT, it is difficult to measure individual productivity, and it is particularly difficult to determine whether a project manager for a development project is 'pulling his weight'. Read more...

Database Deployment: The Bits - Versioning

Although databases have no inherent way of recording their version numbers, SQL Server provides the means of doing so, and much more besides. This is a great advantage to anyone faced with the task of deploying databases without errors. Read more...

Database Deployment: The Bits - Database Version Drift

When you are about to deploy a new version of a database by updating the current version, one of the essential pre-deployment checks is to make sure that the version in production is exactly what it should be. If changes have somehow slipped in, you'll need to understand them and deal with them before you can deploy. Read more...

Database Deployment: The Bits - Copying Data Out

Occasionally, when deploying a database, you need to copy data out to file from all the tables in a database. Phil shows how to do it, and illustrates its use by copying an entire database from one server to another. Read more...

Database Deployment: The Bits - Getting Data In

Quite often, the database developer or tester is faced with having to load data into a newly created database. What could be simpler? Quite a lot of things, it seems. Read more...

Fifty Shades of Gray: The SQL and PowerShell

Phil was struck by a comment by a DBA on a Simple-Talk article that complained that the PowerShell examples weren't simple enough. The traditional "hello world" was too simple (that's actually the program), but he was suddenly struck by the literary fuss over 'Fifty Shades of Gray' to decide to do a 'Fifty Shades of Gray' Wallchart in both TSQL and PowerShell. Read more...

Registered Servers and Central Management Server Stores

SQL Server Management Studio (SSMS) has many features that make it particularly convenient for the database developer and DBA. Registered Servers are typical: poorly documented, but essential for the busy DBA, since they are easily scriptable via TSQL or PowerShell as well as the SSMS GUI, to access groups of servers to perform a wide range of tasks. Read more...

PowerShell SMO: Just Writing Things Once

Sometimes, you can tire of writing the same PowerShell code once again. After this happened to Phil whilst keying in an SMO pipeline to access databases, it occurred to him that he should have only one pipeline to access databases in PowerShell, one reusable pipeline Read more...

Handling Constraint Violations and Errors in SQL Server

The database developer can, of course, throw all errors back to the application developer to deal with, but this is neither kind nor necessary. How errors are dealt with is very dependent on the application, but the process itself isn't entirely obvious. Phil became gripped with a mission to explain... Read more...

The TSQL of CSV: Comma-Delimited of Errors

Despite the neglect of the basic ODBC drivers over the years, they still afford a neat way of reading from, and writing to, CSV files; and to be able to do so in SQL as if they were tables is somewhat magical. Just to prove it is possible, Phil creates a CSV version of AdventureWorks as a linked server. Read more...

Generating Data for Database Tests

It is more and more essential for developers to work on development databases that have realistic data in both type and quantity, but without using real data. It isn't exactly easy, even with third-party tools to hand. Phil Factor shows how it can be done, taking the classic PUBS database and giving it a more realistic set of data. Read more...

Automated Script-generation with Powershell and SMO

In the first of a series of articles on automating the process of building, modifying and copying SQL Server databases, Phil Factor demonstrates how one can generate TSQL scripts for databases, selected database objects, or table contents from PowerShell and SMO. Read more...

Confessions of a DBA: My worst mistake

Over the next few months, we'll be asking various well-known DBAs to describe their worst disaster caused by a mistake they made. To kick off the series, we asked Phil Factor to confess. He came up with a classic: The mistaken belief that a backup WITH CHECKSUM guaranteed a good backup that could be restored, and the ensuing disaster. Read more...

Temporary Tables in SQL Server

Temporary tables are used by every DB developer, but they're not likely to be too adventurous with their use, or exploit all their advantages. They can improve your code's performance and maintainability, but can be the source of grief to both developer and DBA if things go wrong and a process grinds away inexorably slowly. We asked Phil for advice, thinking that it would be a simple explanation. Read more...

Making HTML tables easier on the eye- CSS Structural Pseudo-classes

We asked Phil why his PowerShell tabular reports looked so nice. 'CSS structural pseudo-classes' he muttered mystically. Later on, without any further warning, he popped up with this article that explains for anyone who has missed them, how to go about doing intricate formatting of an HTML file, the contents of which you cannot alter. Read more...

SQL Programmer's workshop

Phil Factor records, as closely as possible, the twists and turns of creating a SQL Server T-SQL stored procedure, describing the methods that work for him. Read more...

How to develop TSQL Code

The basic texts for developing SQL code tend to leave unsaid the basic techniques for building routines such as stored procedures in T-SQL. Phil is well-known for his more lengthy and complex stored procedures, so we asked him to explain in more detail how he goes about developing things like that without the comfort of Visual Studio Read more...

COM Automation of Office Applications via PowerShell

There need be no shame in using Office by automating it via COM. It was designed to be used that way, and with PowerShell, the various Office applications can be used as glorious output devices for data. Phil Factor uses some practical examples to try to persuade you to take the plunge. Read more...

PATINDEX Workbench

The PATINDEX function of SQL Server packs powerful magic, but it is easy to get it wrong. Phil Factor returns to the Workbench format to give a tutorial of examples, samples and cookbook ideas to demonstrate the ways that this underrated function can be of practical use. It is intended to be pasted into SSMS and used as a basis for experiment Read more...

How to Import Data from HTML pages

It turns out that there are plenty of ways to get data into SQL Server from websites, whether the data is in tables, lists or DIVs Phil finds to his surprise that it is easier to use Powershell and the HTML Agility Pack, than some of the more traditional approaches. Web-scraping suddenly becomes more resilient. Read more...

RegEx-Based Finding and Replacing of Text in SSMS

So often, one sees developers doing repetitive coding in SQL Server Management Studio or Visual Studio that would be much quicker and easier by using the built-in Regular-Expression-based Find/Replace functionality. It is understandable, since the syntax is odd and some features are missing, but it is still well-worth knowing about. Read more...

The Parodist: A SQL Server Application

Every year, we ask Phil Factor to celebrate the holiday season with an article on SQL Server Programming that is fun. This year, he responded with 'The Parodist'. This is a SQL Server application, the like of which I doubt if you've seen before. Read more...

Consuming JSON Strings in SQL Server

It has always seemed strange to Phil that SQL Server has such complete support for XML, yet is completely devoid of any support for JSON. In the end, he was forced, by a website project, into doing something about it. The result is this article, an iconoclastic romp around the representation of hierarchical structures, and some code to get you started. Read more...

SQL Server CRUD-Generation from System Views

If you are not keen on repetitive typing, you can still rapidly produce production-quality documented code by planning ahead and using Extended properties, and system views. Phil Factor explains, with some Scary SQL Read more...

Exploring SQL Server table metadata with SSMS and TSQL

Phil shows how to start squeezing powerful magic from SSMS for doing a detailed exploration of the metadata of your routines and tables, in this third part to his series on exploring your database schema with SQL. Read more...

The Cobra Programming Language

There are suddenly a number of strong alternatives to C# or VB. F#, IronPython and Iron Ruby are now joined by an open-source alternative called Cobra. Phil is taken by surprise at a language that is so intuitive to use that it is almost like pseudocode. Read more...

Exploring your database schema with SQL

In the second part of Phil's series of articles on finding stuff (such as objects, scripts, entities, metadata) in SQL Server, he offers some scripts that should be handy for the developer faced with tracking down problem areas and potential weaknesses in a database. Read more...

Finding Stuff in SQL Server Database DDL

You'd have thought that nothing would be easier than using SQL Server Management Studio (SSMS) for searching through the DDL for both the names and definitions of the structural metadata of your databases, for the occurrence of a particular string of letters. Not so easy, it turns out, though Phil Factor is able to come up with various methods for various purposes. Read more...

Laying out SQL Code

It is important to ensure that SQL code is laid out the best way for the team that has to use and maintain it. Before you work out how to enforce a standard, one has to work out what that standard should be for the application. So do you dive into detail or create an overall logic to the way it is done? Read more...

The SQL of Scrabble and Rapping

In which Phil decides to use a table consisting of all the common words in English to explore ways of cheating at Scrabble and writing doggerel using SQL Server. He then issues a SQL challenge. Read more...

Connection Strings, an Overview

We asked Phil to come up with a simple explanation of connection strings. We somehow weren't expecting a 'quote of the day' for your database, or a C# application to gather data from the internet. However, sometimes the oblique approach is the best, especially when the knowledge comes from hard-won experience by a cynical man. Read more...

Pivoting, Un-pivoting and Aggregating: A Quick Spin Around the Block

In which Phil is asked to write a nice simple quick-start guide about aggregation, pivoting and un-pivoting techniques. To do so, he takes us all the way from getting the data from a published source, transferring it to SQL Server, un-pivoting it, storing it in a relational table, aggregating it and finally pivoting the data in a variety of ways Read more...

To Boldly Ask IT for Development Work

Phil has always been mystified by the way that, in Science-Fiction films, the crew of space-ships are able to reprogram their ships' computers in order to respond to emergencies, needing no more than a brief klip...klop...klip on the keyboard to effect a huge software change. A life in IT has seemed so different, and so he wonders if there a more realistic way that one might imagine IT's contribution to Space adventures Read more...

The Book 'Confessions of an IT Manager'

For three and a half years, the 'Confessions of an IT Manager', by Phil Factor, have been a distinctive part of Simple-Talk's output. Now compiled into a 300 page book, they form a unique commentary of the comic, and someties bizarre, nature of a career in IT. Read more...

Confessions of an IT Manager, 2nd Edition

The software industry is, just occasionally, more absurd than one would dare to imagine. Having spent most of his working life in its clutches, Phil Factor has pretty much "seen it all" and what's more he's prepared to tell what he knows. The second edition of Phil's "Confessions of an IT Manager" contains Phil's full repertoire of tales of institutional mayhem and software projects gone awry. Read more...

CLR Assembly RegEx Functions for SQL Server by Example

Phil Factor presents a simple CLR Assembly to allow SQL Server users to access all the powerful RegEx library methods in .NET. In the spirit of 'get something up and running', he takes a hands-on approach and demonstrates that you needn't be a C# hotshot or Visual Studio expert to develop a CLR assembly for SQL Server Read more...

Phrenetic Phil and the SQL Shootout

Phil Factor turns to burlesque in order to reinforce his point that an ability to answer SQL Technical questions isn't really a measure of anyone's fitness for a real job of work. He is struck by the expression 'SQL Shootout', and the rest is left to his over-active imagination. The first SQL Cowboy story? Read more...

The TSQL of Text Files

Phil returns to the old subject of 'Getting text-based data in and out of SQL Server'. He shows various easy ways of getting a file listings of directories from the file system, shows how one can access the Shell automation Objects, and demonstrates several ways of reading or writing data between database and file Read more...

The Data Center that Exploded

A while back, in a Simple-Talk editorial meeting, someone bet Phil that he couldn't come up with a Halloween story. To our surprise he said he could, as long as he didn't have to keep to the strict literal truth. In the end, he came up with a story about a story, and it is true that he first told the story in a data Centre at Halloween! Read more...

Bunnikins!

When an IT manager is selected as a victim of office politics of a large corporate, it is time for him to engage in intelligence-gathering. Phil Factor exposes a ruthless streak when called to account over an Engineering Workflow system. Read more...

The Wrong Fabia

There is often more than a twinge of embarrassment when an Email goes astray, and is received by the wrong person. Phil remembers an occasion when it nearly ended a career, but somehow, ironically, changed it for the better. Read more...

The Walrus and the Manager

Why do Phil's eyes water whenever he hears the poem 'The Walrus and the Carpenter'? Is it the voice of his conscience or memories of struggles selling software services to large companies? Why does he identify so strongly with the eldest oyster? Read more...

The Time Bomb

Phil cheerfully admits to his fair share of accidental IT disasters. His experiences some time ago with the Time Bomb, where his software crashed and threatened civil unreast in a far-away country were, however, deliberate. Read more...

The New Man

Working in IT, one meets all sorts of people, but rarely quite so odd, or creepy, as a 'New Man'. Phil Factor relates how a brush with such a character got him the sack on the second day in a new job. Read more...

Creation by Committee

Phil Factor got to wondering how on earth the wonderful frescos of the Sistine Chapel ever got painted, if the type of corporate 'best-practice' and management now seen in IT development projects were in place then. Phil Factor imagines the pressures on Michelangelo and the Pope, from strategists, MVPs,  project managers, analysts, architects, sponsors, and bureaucrats, and conjures up two visionaries in a very modern predicament. Read more...

Tomorrow will be our Dancing Day

Someone recently told Phil that it was impossible to tell a good ghost story about IT Development. Great mistake. Noone of a nervous disposition should read this.... Read more...

The Computer that Swore

Database Developers occasionally get crazy ideas into their heads. Phil Factor should know; He confesses that he's had a few completely loopy notions that he pursued to destruction before realising his error. Read more...

The Joy of NAD

Phil Factor convenes a short meeting to discuss the data architecture for storing a client's contact details...how painful could it be? Phil Factor discovers that even the simplest database can be a major cause of grief and complexity Read more...

Captain Codd and the Simple Proposition

A Pantomime for Database Professionals. Read more...

Smoke and Mirrors

It seems hard to imagine that a presentation on a new IT application to the directors can go too well. Phil doesn't need to imagine- it happened to him. He winces at the memory. Read more...

The Challenge

In which Phil is challenged to organize a concerted beer drinking session in an establishment dedicated to beer manufacture. Read more...

Hens that Crow

Short on results, but long on superficially plausible technical excuses, Phil Factor describes his dealings with the hen that crowed… Read more...

The Simple-Talk SQL Prettifier

Tired of struggling through dull, monchrome SQL, C# and VB code in website blogs and forums? Then check out the all-new Simple-Talk code prettifier! Read more...

Fired With Enthusiasm

After years working his fingers to the bone on his own IT business, Phil was enjoying the relative tranquillity of work at a City of London bank. However, he was shocked by the number of "light arms" in the IT department, who didn't do a stroke of useful work all day, and became animated only when the magical hour of 5PM approached... Read more...

Reading and Writing Files in SQL Server using T-SQL

SQL Server provides several "standard" techniques by which to read and write to files but, just occasionally, they aren't quite up to the task at hand – especially when dealing with large strings or relatively unstructured data. Phil Factor provides some T-SQL stored procedures, based on use of the FileSystem Object (FSO), that may just get you out of a tight corner… Read more...

Writing to Word from SQL Server

Never a man to walk away from a challenge, Phil Factor set himself the task of automating the production of Word reports from SQL Server, armed only with OLE automation and a couple of stored procedures. Read more...

The March of Technology

In which Phil, under the influence of a surfeit of lobster, some fine wine and a large book, conjures up an imaginary Bill Gates, lets him know why people aren't flocking to SQL 2005, and offers him a turning off of the path to ever more complex and bloated database software. Read more...

The Data Dialog

Most companies have long since jettisoned the role of the grizzled old data architect, whose job it was to ensure a common understanding of the meaning of all data entities in the enterprise, and the actions carried out on it. Many are just now coming to realize what a costly mistake that might have been... Read more...

The Ultimate Excuse Database

Is your IT project in trouble? Do you need to create a smokescreen so that no-one can pin the blame on you? No problem! Marvel at Phil Factor's versatile use of T-SQL functions in creating the "ultimate excuse database" and then start generating your excuses immediately! Read more...

Phil Factor on the Law

Taking the adversarial system to task with vigour and courage, Phil Factor gives us his view on how to handle the trials of litigation if all cannot be solved by a hearty pub lunch. Read more...

Free Phil Factor eBook: Confessions of an IT Manager

Download a 108-page eBook in which Phil tells all on a career spent in the rough-and-tumble of the IT industry. It's FREE for registered users! Read more...

Automating Common SQL Server Tasks using DMO

Phil Factor's DMO automation procedures provide a valuable additon to any DBAs toolkit. Read more...

On Training Your IT Manager

A keen new manager, with ambitions to make his mark, can cause all sorts of unpleasantness in the workplace. Phil Factor explains how to train your new manager to the required standards... Read more...

More Database Administration and Development Automation using DMO

Phil Factor provides a handy DMO automation toolkit to take care of some core SQL Server administrative tasks. Read more...

The Yancey Men

Ever wondered what the perfect IT director might be like? Phil Factor experimented... Read more...

The Essex Men

A band of muscly Essex men take on the geeky IT department in a basketball match - if you think the victor of this challenge sounds like a foregone conclusion, read on... Read more...

Was Offshoring Responsible for Hamlet?

Phil Factor imagines how Hamlet ended up as the play we know and..err..love, with a little help from cost-cutting and offshoring. Read more...

If IT had been responsible for the Creation

In which Phil Factor takes the liberty of retelling the Creation story to make it 'relevant' to the modern IT professional. Read more...

The Whipping Boy

Talented and highly-valued IT professional or convenient corporate whipping boy? Phil Factor walks the line... Read more...

The Ghost in the Machine

I have always felt rather second-rate as an IT pundit as I have yet to introduce my own TLA (Three-letter Acronym ) into the IT industry. I would therefore like to suggest a new branch of IT: solving IT problems by Antiquarian Research in Technology. Or ART, to us aficionados. Read more...

Talking Technical

Developers, programmers and designers tend to be deeply geeky types, immersed in the minutiae of the technology. In stark contrast, many IT managers appear to have difficulty working the remote controls on their television. This cultural gap between the foot soldier and officer classes of the IT industry can have interesting consequences. Read more...

Betting on Promotion

The process of appointing managers in large IT department is shrouded in mystery. The bewilderingly random and illogical nature of the process makes it a gambler's delight, and it's often difficult for workers to resist the odd wager on the outcome. Read more...

The Writing on the Wall

Phil Factor offers an intriguing theory on why so many, hugely complex, government IT projects fail. Is it because there is a world of difference between the business processes that really take place in a company and the management's understanding of those processes? Read more...

IT Agencies and the Devil

Whenever I worry about how I shall earn a crust when my mental powers start failing, I always console myself with the thought that I can go and start an IT employment agency. Many before me have done so. It is remarkably easy, as the profession of "Employment Agent" seems to be almost free of regulation. Read more...

Two stops short of Dagenham

Phil Factor mourns the demise of the Eccentric in the IT World - "One of the best programmers I ever met was referred to, in Essex jargon, as being 'two stops short of Dagenham' (Barking)*. Read more...

Survival tips for powerpoint

The Database Mole Presents Survival tips for PowerPoint boredom Read more...

Cursors and embedded SQL

The database mole turns his attention to take a look at cursors and embedded SQL Read more...

Secrets of successful IT projects

The database mole shares his secrets of successful IT projects, and pitfalls to avoid. Read more...

The database mole

Bogus resumes and unblushing lies: navigating the database hiring waters and selecting a development team. Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.