Click here to monitor SSC
Roy Ernest and Surenda Djaoen

Introduction to SQL Server Spatial Data

More and more applications require the handling of geospatial data. It is easy to store spatial data, but it takes rather more thought to retrieve and manipulate it. Tasks like searching neighborhoods, and calculating distances between points is often required from databases. But how do you start? Roy and Surenda take you through the basics. Read more...

Alex Kuznetsov

Painless Refactoring of SQL Server Database Objects

Refactoring a database object can often cause unexpected behavior in the code that accesses that object. In this article, adapted from his excellent book, Defensive Database Programming with SQL Server, Alex Kuznetsov discusses several techniques that will harden your code, so that it will not break, or behave unpredictably, as a result such changes. Read more...

Roy Ernest and Surenda Djaoen

SQL Server Spatial Indexes

SQL Server implements the spatial index? As already mentioned, SQL Server starts with a B+ tree structure, which organizes data into a linear fashion. Because of this, the indexes must have a way to represent the two-dimensional spatial information as linear data. For this, SQL Server uses a process referred to as the hierarchical uniform decomposition of space. When the index is created, the database engine decomposes, or refactors, the space into a collection of axes aligned along a four-level grid hierarchy. Figure 1 provides an overview of what this process looks like. Read more...

Fabiano Amorim

Never Ignore a Sort Warning in SQL Server

It is always bad news if your SQL queries are having to use the SORT operator. It is worse news if you get a warning that sort operations are spilling onto TempDB. If you have a busy, slow TempDB, then the effect on performance can be awful. You should check your query plans to try to eliminate SORTs and never leave a SORT warning unheeded. Fabiano Amorim shows the range of ways of getting information on what is going on with a query that is doing a SORT and when requests are made for memory. Read more...

Dennes Torres

Identifying and Solving Index Scan Problems

When you're developing database applications, it pays to check for index scans in the SQL Server query plan cache. Once you've identified the queries, what next? Dennes Torres gives some preliminary guidelines on how to find out why these index scans are being chosen for these queries and how to make the queries run faster and more efficiently. Read more...

Dwain Camps

Self-maintaining, Contiguous Effective Dates in Temporal Tables

'Temporal' tables contain facts that are valid for a period of time. When they are used for financial information they have to be very well constrained to prevent errors getting in and causing incorrect reporting. This makes them more difficult to maintain. Is it possible to have both the stringent constraints and simple CRUD operations? Well, yes. Dwain Camps patiently explains the whole process. Read more...

Jason Crease

Integrating Database Lifecycle Management into Microsoft's Application Delivery Process

In order to automate the delivery of an application together with its database, you probably just need the extra database tools that allow you to continue with your current source control system and release management system by integrating the database into it. If you're using the Microsoft stack, then Redgate's tools can help with some of the difficult database parts of the process, as Jason demonstrates. Read more...

Robert Sheldon

How to get Database Design Horribly Wrong

Database Design is one of those tasks where you have to carefully get all the major aspects right. If you mess-up just one of these, it can all go horribly wrong. So what are these aspects that can ruin database design, and how can you get them right? Robert Sheldon explains. Read more...

Seb Rose

What is Behaviour Driven (Database) Development?

Behaviour Driven Development is not always clearly understood, and the term is particularly unfamiliar in database circles. Seb Rose introduces us to the fundamentals of BDD, and make some suggestions for how it might be relevant to database development. Read more...

Thomas LeBlanc

Defusing Database Time Bombs: Avoiding the Need to Refactor Databases

Where applications are evolved by gradually molding them to a growing understanding of the business domain, this presents great challenges to database development. If databases are designed too loosely, and initial errors are allowed to fester, the results become harder and harder to refactor until eventually they constitute a database time bomb. Thomas LeBlanc describes how to avoid a few basic, but very common, database time bombs. Read more...

Tony Davis

Questions About SQL Server Transaction Log You Were Too Shy To Ask

You can give a deep-dive presentation about SQL Server's transaction log, and round it off by inviting questions. Your audience will stare awkwardly at their boots. Afterwards, to your surprise there will be a queue of questioners, and the questions are the ones they were too shy to ask out loud. Tony Davis answers these apparently simple, yet tricky questions. Read more...

Joe Celko

The DRI Subject of References

A database must be able to maintain and enforce the business rules and relationships in data in order to maintain the data model. It does this through referential constraints. They aren't complex, but are powerful, especially with the means to attach DRI actions to them. Joe Celko explains all, and pines for the ANSI CREATE ASSERTION statement Read more...

Dennes Torres

Checking the Plan Cache Warnings for a SQL Server Database

How often do you check your query plans during development to see if they contain any warnings? If you're missing them, it means that you're not getting all those hints about missing indexes, join predicates or statistics. Is the query optimiser trying to tell you about implicit conversions? Dennes shows how to view the warnings in plan cache for a particular database using SQL Read more...

Feodor Georgiev

Curing Data-Obesity in OLTP Databases

OLTP databases work best when data that becomes no longer current is then transferred to a separate database for analysis and reporting. There are many ways to do this, but Feodor describes a rapid technique that takes advantage of partitions to automates the rotation of the data and moving it to the analysis server. Read more...

Jonathan Watts

The Promise - and the Pitfalls - of In-Memory OLTP

When SQL Server 2014 was released, it included Hekaton, Microsoft’s much talked about memory-optimized engine that brings In-Memory OLTP into play. With memory-optimized tables 30 times faster than disk-based tables, higher performance is promised – but at what cost? Jonathan Watts looks at the features that have improved, and those that need careful consideration. Read more...

Michael K Campbell

The Importance of Caching

Performance tuning and optimization definitely have their place in minimizing SQL Server Licensing costs – by helping keep CPU utilization low. But it’s important to remember that the fastest and most efficient query possible is the one that you never execute against your SQL Server. That might sound trite, but it’s at the heart of caching – which is key to helping organizations save significant money on SQL Server licensing costs while simultaneously enabling better application performance and increased scalability. Read more...

Tim Hidalgo

Managing Test Data as a Database CI Component - Part 1

Constructing a test environment for your databases can be a difficult task at the best of times. Once you’ve actually acquired the hardware needed and architected the environment, you still have to arrange and securely transport the data. And with the rising demand for fast feedback and continuously integrated processes, having all of this automated and operating at speed is a challenge all of its own. Read more...

Dwain Camps

Bowled Over by SQL Window Functions

What better way to learn how to construct complex CHECK CONSTRAINTs, use the SQL 2012 window frame capability of the OVER clause and LEAD analytic function, as well as how to pivot rows into columns using a crosstab query? Create the SQL code to score Ten-pin Bowling, of course. Dwain Camps explains the how, what and why. Read more...

Robert Sheldon

Questions About T-SQL Control-of-Flow Language You Were Too Shy to Ask

Surely, we all know how T-SQL Control-of-flow language works? In fact it is surprisingly easy to get caught out. What, for example, do the BREAK, RETURN and CONTINUE keywords do, precisely, in their various contexts? the answers to this and other questions aren't all entirely obvious, but we're too shy to ask them in public, and risk displaying our ignorance. Read more...

Dennes Torres

Centralize Your Database Monitoring Process

SQL Server Data Collector, together with Management Data Warehouse, is a fine and useful component for gathering information centrally about how SQL Server instances are being used, and thereby keeping an eye out for problems. It comes into its own when you have figured out how to configure it to run on maybe hundreds of instances using Central Management Server. Dennes describes how to tame the system so that it scales. Read more...

Simple-Talk Database Delivery

DLM
Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
A Start with Automating Database Configuration Management

For a number of reasons, it pays to have the up-to-date source of all the databases and servers that you're... Read more...

 View the blog

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... 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... 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.