Schema-Based Access Control for SQL Server Databases

Access-control within the database is important for the security of data, but it should be simple to implement. It is easy to become overwhelmed by the jargon of principals, securables, owners, schemas, roles, users and permissions, but beneath the apparent complexity, there is a schema-based system that, in combination with database roles and ownership-chaining, provides a relatively simple working solution.… Read more

The SQL of The Game of Life

Joe finds a reference to Conway's Game of Life whilst clearing out his desk, and is suddenly gripped with nostalgia. It wasn't just flares, mullets and disco, but simple computer games in interpreted basic. Somehow, Conway s Game of Life was too intriguing to be abandoned in the attic. Can it be implemented in SQL? Joe sets up a challenge.… Read more

Nesting Levels in SQL

The 'Structured' part of SQL denotes the fact that queries can be nested inside each other in such a way that, wherever you can use a table, you can use a table expression. Such derived tables can provide powerful magic, to which is added CTEs and Lateral Tables. Joe Celko explains.… Read more

Test-driven Database Development – Why Bother?

Test-Driven Development is a practice that can bring many benefits, including better design, and less-buggy code, but is it relevant to database development, where the process of development tends to me much more interactive, and the culture more test-oriented? Greg reviews the support for TDD for Databases, and suggests that it is worth giving it a try for the range of advantages it can bring to team-working.… Read more

How to Document and Configure SQL Server Instance Settings

Occasionally, when you install identical databases on two different SQL Server instances, they will behave in surprisingly different ways. Why? Most likely, it is down to different configuration settings. There are around seventy of these settings and the DBA needs to be aware of the effect that many of them have. Brad McGehee explains them all in enough detail to help with most common configuration problems, and suggests some best practices.… Read more

Table Value Constructors in SQL Server 2008

Table Value Constructors (TVCs) are a useful feature of 2008, allowing you to specify tables of values and expressions. This has all sorts of uses. Users who are stuck with previous versions of SQL Server can play along, since Rob demonstrates that there have, for a long time, been ways of doing this in SQL Server, though less elegantly.… Read more

Yet Another SQL Strategy for Versioned Data

There is a popular design for a database that requires a built-in audit-trail of amendments and additions, where data is never deleted, but merely superseded by a later version. Whilst this is conceptually simple, it has always made for complicated SQL for reporting the latest version of data. Alex joins the debate on the best way of doing this with an example using an indexed view and the filtered index.… Read more

Subqueries in SQL Server

Subqueries and derived tables can add great versatility to SQL statements, cut down complexity, but can occasionally be a curse when their effect on performance is poorly understood. Surely everyone understands the various types of subqueries and how they are used? If you felt a twinge of doubt, here is Rob Sheldon's easy guide to the subject. … 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