Lists With, or Without, Ranges in both T-SQL and PowerShell

Whether you are working in a procedural language like PowerShell or in T-SQL, there is something slightly bothersome about having to deal with parameters that are lists, or worse with ranges amongst the values. In fact, once you have a way of dealing with them, they can be convenient, especially when bridging the gulf between application and the database. Phil Factor shows how to deal with them.… Read more

Representing Hierarchical Data for Mere Mortals

Why is it that we use XML, but with so little enthusiasm when it does so much, and is so feature-rich? Phil Factor argues that there are better ways of doing it, more complete than JSON, but easier to read than XML. To try to convince you, he gives a set of flying demos, using PowerShell and his PSYaml module, to illustrate how YAML can let you work faster, and more accurately.… Read more

PSYaml: PowerShell does YAML

PSYaml is a simple PowerShell module that I’ve written that allows you to serialize PowerShell objects to “YAML Ain’t Markup Language” (YAML) documents and deserialize YAML documents to PowerShell objects. It uses Antoine Aubry’s excellent YamlDotNet library To start, you can simply load the PowerShell file and the manifest from its home on GitHub PSYaml … Read more

On Being Economical with the Truth

A while back, I attended a presentation about a suite of software that allowed IT managers to track in some detail the progress of a development project, via static code analysis, a range of source control metrics and other magic. Bathed in these insights, management could instantly track the detail of what was going on … Read more

The SQL of Textonyms

The task of finding textonyms in SQL involves importing a list of common words and doing transformations on every word to convert it into what you'd need to type into the numeric keypad of your mobile phone to get that word. It's not that hard to do, but what is the quickest and most efficient way of doing it? Phil Factor investigates.… Read more

Data Security: Time to be Grown-Up

How do you quickly clear a room-full of application developers, short of shouting ‘fire’ and setting off a smoke-bomb? The answer, of course, is to stand up in front of them and announce that you are giving a talk about database access-control and security. Once, at a conference, I sat through a brilliant talk on … Read more

Measure of a DBA

Marketing people are often surprised when a new version of SQL Server doesn’t quite generate the atmosphere of excited anticipation that they expected. After all, people seem to get themselves in a state of frenzy when a new iPhone is introduced, and suddenly their present phone looks like a museum item. The urge to upgrade … Read more

Unmasking the Dynamic Data Masking

/* Revised 27th June   Dynamic data masking is a great product and solves some niche problems that come if you need to do certain testing with live data at the application level. You should, however, beware of using it as a database-level security device. I haven’t yet used it in testing because I don’t … Read more

How to Build and Deploy a Database from Object-Level Source in a VCS

It is easy for someone who is developing a database to shrug and say 'if only my budget would extend to buying fancy tools, then I could start using version control when developing or maintaining databases'. Phil Factor sets out to show that there are ways of maintaining object-level source control for SQL Server databases just using what Microsoft provides, It may not be perfect, but there are ways of doing it whatever your budget.… Read more

Automatically Creating UML Database Diagrams for SQL Server

SQL Server database developers seem reluctant to use diagrams when documenting their databases. It is probably because it has, in the past, been difficult to automatically draw precisely what you want, other than a vast Entity-relationship diagram. However, you can do it without buying any third-party tool, just using some existing Java-based open-source tools; and can even automate it entirely, using SQL and PowerShell.… Read more

SQL Server Table Smells

Table smells in SQL Server are just like code smells. They are just an indication that something may need to be checked or investigated. They are easy to check as well, because you can generally ferret through the object catalog metadata views to flush out the aspects that just don’t smell right. In this blog, … Read more

The Luhn Algorithm in SQL

The Luhn test is used by most credit card companies to check the basic validity of a credit card number. It is not an anti-fraud measure but a quick check on data corruption. It still allows any digits that are odd or even to be switched in the sequence. Most credit cards are compatible with Luhn algorithm. It is often applied to SSNs, company organization numbers, and OCR numbers for internet payments. How do you handle them in SQL?… Read more

The SQL of the Feast Days of Western Christendom

I’ve been intrigued for a long time by the Christian feasts. Nowadays, we start getting excited about Christmas around July, and it is considered by commerce to be fair-game as a de-walletizing activity from Halloween onwards. I would like far more feast days to get excited about. Our ancestors had so many feast days that … Read more

Late in the Day for a DBA

I first met Joe when I went to Woodworking evening classes several years ago. I saw him from across the room. He was at the lathe, turning a bowl. There was something about the careful, diligent way he was working that activated my DBA-dar. I approached him. “You’re a DBA, aren’t you?” I asked. He … Read more

Dependencies and References in SQL Server

It is important for developers and DBAs to be able to determine the interdependencies of any database object. Perhaps you need to work out what process is accessing that view you want to alter, or maybe find out whether that table-type you wish to change is being used. What are all these dependencies? How do you work out which are relevant? Phil Factor explains.… Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up