Click here to monitor SSC
Robert Sheldon

The Evolution of SQL Server BI

It is sometimes hard to keep up with Microsoft's direction in Business Intelligence. Over the years, we've been presented with so many ways of aggregating, processing, graphing, reporting and visualising data, that we felt it was time to take stock and present the Simple BI Timeline, and explain how BI has evolved over the years. Read more...

Phil Factor

Microsoft and Database Lifecycle Management (DLM): The DacPac

The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx), provides an alternative way to automate the process of scripting out, or deploying a SQL Server database, reporting on changes, or checking version-drift. As such, it seems to provide an obvious cost-free start for tackling automated Database Delivery as part of Database Life-cycle Management (DLM). We asked Phil Factor if it is ready for Prime-time. Read more...

Edward Elliott

Non-Breaking Online Database Deployments

For an experienced database developer, the idea of doing non-breaking database deployments while the database is still online holds respect but only a little fear. If your test procedures are good, you attend to the detail, and the application interfaces are properly managed, it can be achieved. Ed Elliott explains some of the issues to tackle. Read more...

William Brewer

What is Database Lifecycle Management (DLM)?

When the different teams that are involved throughout the life of a database fail to reconcile their different roles and priorities, and so fail to cooperate, or work adaptively, the result is gridlock: databases defined as though carved in stone rather than by code and data. DLM offers an alternative that allows databases to respond quickly to business change. Read more...

Robert Sheldon

How to Get NULLs Horribly Wrong in SQL Server

NULLs in SQL are a mixed blessing. The Three-Valued Logic of SQL has its uses but can cause difficulties to anyone who assumes that NULL has a 'value'. It can make reports go horribly wrong in a number of alarming ways, as Robert Sheldon explains. Read more...

Feodor Georgiev

Getting to know your customers better – cohort analysis and RFM segmentation in R

It often pays to use a tool like R, in conjunction with a relational database, to quickly perform a range of analyses, and graphs, in order to ensure that you're answering the right question, to explore alternative hypotheses, or to provide insight into a problem. Feodor demonstrates how to quickly, and interactively, explore the ways that customers purchase goods and services using cohort analysis. Read more...

Adam Aspin

SQLXML Bulk Loader Basics

SQLXML isn't exactly new technology, but like the even more venerable BCP, it remains the quickest and most reliable way of heaving large quantities of data into SQL Server databases. SQLXML is very versatile, and once set up is wonderfully reliable ETL system, but isn't trivial to learn. Adam Aspin comes to the rescue with a simple guide. Read more...

Feodor Georgiev

Using R to Explore Data by Analysis - for SQL Professionals

What's the best way for a SQL programmer to learn about R? It's probably by trying out various experiments with an interesting set of data, and some helpful suggestions that point out the parallels with SQL. Feodor provides the data and the helpful suggestions. The rest is up to you. Read more...

Alex Kuznetsov

Reusing T-SQL Code

Database programmers are often caught in the paradox of wanting to implement business rules and complex functionality in one place only, but being concerned by the performance hit of having generic functions or procedures that have a wide range of parameters and outputs. Alex Kuznetsov, in an article taken from his book 'Defensive Database Programming with SQL Server', shows how DRY principles can be put in practice with constraints, stored procedures, triggers, UDFs and indexes. Read more...

Tim Hidalgo

Managing Test Data as a Database CI Component - Part 2

Once you're clear about what data the developers and testers need to be able to be effective, you have several alternatives approaches to providing them exactly the data that will give you rock solid database tests that will allow deployments with far less risk. Tim goes through some practical demos to show you how you can automatically keep your testing environments up to date, and thereby ship more robust databases. Read more...

Robert Sheldon

How to Get SQL Server Dates and Times Horribly Wrong

One of the times that you need things to go right is when you are doing analysis and reporting. This is generally based on time and date. A sure-fire way of getting managers upset is to get the figures horribly wrong by messing up the way that you handle datetime values in SQL Server. In the interests of peace, harmony and a long career in BI, Robert Sheldon outlines some of the worst mistakes you can make when using SQL Server dates. Read more...

Dwain Camps

Using the T-SQL PERCENTILE Analytic Functions in SQL Server 2000, 2005 and 2008

Percentiles give meaning to measurements by telling you the percentage of the population being measured who get higher or lower values. They are now easier to calculate in SQL, and are useful for reporting; but are the new analytic functions faster and more efficient than the older methods? Dwain Camps demonstrates, and investigates their relative performance performance. Read more...

Dennes Torres

Optimizing Batch Process in SQL Server

SQL Server batch processes are usually run from SQL Agent in background. They can take significant time and resources, especially if they are ETL tasks. Quite often, the responsibility for creating these tasks belongs entirely to the developer. Dennes demonstrates that DBAs can advise and assist with this type of batch job by bringing their expertise to bear on the problem of reducing their impact on the working system to a minimum. Read more...

Phil Factor

Documenting your SQL Server Database

One of the shocks that a developer can get when starting to program in T-SQL is that there is no simple way of generating documentation for routines, structures and interfaces, in the way that Javadocs or Doxygen provides. To embed the documentation in the source is so obvious and easy that it is a wrench to be without this facility. Phil Factor suggests a solution. Read more...

Robert Sheldon

How to Get SQL Server Security Horribly Wrong

It is no good doing some or most of the aspects of SQL Server security right. You have to get them all right, because any effective penetration of your security is likely to spell disaster. If you fail in any of the ways that Robert Sheldon lists and describes, then you can't assume that your data is secure, and things are likely to go horribly wrong. Read more...

Feodor Georgiev

Making Data Analytics Simpler: SQL Server and R

R and SQL Server are a match made in heaven. You don't need anything special to get started beyond the basic instructions. Once you have jumped the hurdle of reliably and quickly transferring data between R and SQL Server you are ready to discover the power of a relational database when when combined with statistical computing and graphics. Read more...

Dwain Camps

Time Slots - An Essential Extension to Calendar Tables

After answering many forum entries from developers asking for help with dealing with SQL that involved time intervals and ranges, Dwain dreamed of a generalized tool that sets up time slots of various sorts without the need to experiment; that could do the heavy lifting, so that developers could do aggregations and reports based on time intervals without the hard graft. Here is Dwain's dream made reality. Read more...

Adam Aspin

Mobile BI with SQL Server Reporting Services

For users to get the information they need from mobile reports, we frequently have to tailor the way we design those reports to the characteristics of the target device. Adam Aspin demonstrates some of the ways that we can deliver easy-to-use Business Intelligence to tablets and smartphones using Reporting Services. Read more...

Grahaeme Ross

Understanding Cross-Database Transactions in SQL Server

Microsoft 'Always On' technology does not support distributed or cross-database transactions. Why not? Grahaeme Ross shows how to investigate cross-database transactions to understand the problem in more detail, and concludes that a Cross-Database transaction can cause loss of data integrity in the commit phase of the two-phase commit Read more...

Phil Factor

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...

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
Microsoft and Database Lifecycle Management (DLM): The DacPac

The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx), provides an... 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...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... 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...

Why Join

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