Category Archives: Data Warehouse

Data Warehouse, SQL Server, Transact-Sql

Enforcing Upper/Lower Case in a Sql Server Column

Leave a comment

Case sensitivity in Sql Server depends on the collation applicable to the column, which of course is often the same as the database default collation. Unlike Oracle, Sql Server out of the box is case-insensitive (at least in English-speaking Western countries).

This means that you don’t need to consider whether data is upper- or lower-case when you’re searching for it, which tends to make an application more robust. You probably won’t want to change that. On the other hand, if your database stores codes or descriptions used as query filters (not including proper nouns like personal names where the capitalisation is part of the data) there may be an advantage to standardising on upper or lower case. You’ve reduced the need to take account of upper/lower case in client code, where string comparisons may be case-sensitive.

Is there an easy way? Yes. Continue reading Enforcing Upper/Lower Case in a Sql Server Column →

C#/SQL Server, Data Warehouse, NoSql, SQL Server, Transact-Sql

JSON Support in Sql Server 2016 CTP3.2 – Quick Start

Leave a comment

The JSON has landed (in beta at least) – and I think it’s now some practical use. I’ve collected a few technical references here. There may not be a native JSON type yet, but there are workarounds to provide the kind of assurance control-freak RDBMS developers find comforting. I’ve shown some of them in the code sample below. Continue reading JSON Support in Sql Server 2016 CTP3.2 – Quick Start →

Data Warehouse, SQL, SQL Server, Transact-Sql

Simplify Reconciliation Code with Filtered Indexes

Leave a comment

Some hand-matching of internal/external records may be needed even in an automated reconciliation. The matches will be stored in a database. Rules on matching can be enforced in the application, but there are often good reasons to place them in the database. In Sql Server, a filtered index (with a WHERE clause, in this case excluding Nulls) on the table can make this easier and more correct. Continue reading Simplify Reconciliation Code with Filtered Indexes →

Data Warehouse, Database, ETL, SQL Server, Transact-Sql

Sql Server Filtered Index as a Unique Constraint on a Specific Value

Leave a comment

Indexes often double as constraints. Each combination of values must only appear once among the columns of an index defined as UNIQUE. The “filtered” index allows some refinement. Suppose that in one column you won’t allow a specific value to appear more than once, but you don’t mind other values being duplicated.

A good example would be the IS_LATEST_VERSION column in a data warehouse table. Continue reading Sql Server Filtered Index as a Unique Constraint on a Specific Value →

Data Warehouse, Performance, SQL, SQL Server, Transact-Sql

Forgotten Features of Sql Server

Leave a comment

Well, not literally – these are features I tend to overlook because I don’t often need them. If you’re the same, this may be helpful. It isn’t a complete list (I’ve forgotten the others) but I’ll try to add to it as things come to mind. Continue reading Forgotten Features of Sql Server →

Data Warehouse, SQL Server

“Stretch Db” in Sql Server 2016 CTP2 – Quick Start

Leave a comment

“Stretching” the database now means migrating some or all data from local database tables into the Azure Cloud. The result is a hybrid design: each local table then has a counterpart in the cloud. Any query against the local table will be transparently re-written to include data from the “cloud table”. Continue reading “Stretch Db” in Sql Server 2016 CTP2 – Quick Start →

Agile, Change Management, Continuous Integration and Delivery, Data Warehouse, Transact-Sql

Agile Continuous Delivery of Databases (3)

2 Comments

A phase of my Agile Blue/Green deployment project is now complete, as I have a Proof of Concept which has been tested successfully. As you may recall from prior posts, the Blue/Green technique features two databases, the “green” live one that users connect to (I’ve made an arbitrary assignment of colours), and the offline “blue” one which has a different version of the schema (a previous or future release). Continue reading Agile Continuous Delivery of Databases (3) →

Data Warehouse, ETL, SQL Server, Tools, Transact-Sql

OPENROWSET and BULK INSERT (4)

Leave a comment

{Previous Article in Series}
Here are some final notes on this topic. I’m going to describe an import I did recently, and hope this may help you decide where OPENROWSET and BULK INSERT could fit into your development activity and what tools you might use.

I was doing an ad hoc load of some data from a small csv file. There was no corresponding table in the database to store this data, and I don’t like doing work for the sake of it, so my first stop was the “Sql Server 2014 Import and Export Data” wizard. I was hoping it would be a simple matter for the wizard to build a table and read the data into it. It crashed with an error which wasn’t very specific. Continue reading OPENROWSET and BULK INSERT (4) →

Data Warehouse, Database, ETL, SQL Server, Transact-Sql

The INSERT statement and Column Defaults in Sql Server

Leave a comment

When you INSERT into a table which has a column with a default value defined, and you omit that column from your statement, what will happen? The default will be written. What if you write an explicit Null (and the column is nullable)? In nearly all cases the Null is written.

You may not want this – it’s one of the cases where it might be nice if there were more than one type of Null, as some people advocated in the early days. Then we could distinguish a “known unknown” (© D. Rumsfeld) Continue reading The INSERT statement and Column Defaults in Sql Server →

Agile, Change Management, Data Warehouse, Database, SQL Server, Tools, Transact-Sql

“Test-First” Database Development (2)

Leave a comment

I’ve made additions and changes to the GitHub repository for “Test First” development of the database schema. All procedure names will now start with “Test”, so that they’re easier to find in Object Explorer and Intellisense. I’ve added a “TestHeader” stored procedure. You can put this at the top of your TDD script and it’ll add the date/time, database user, Sql Server version etc., to your results.

The spObjectExists procedure has been replaced by TestObjectExists, with the following signature:
CREATE PROCEDURE [dbo].[TestObjectExists]
@i_ObjectType sysname
,@i_ObjectName sysname = Null
,@i_ParentSchemaName sysname = Null --not needed if testing schema's existence
,@i_DbName sysname = Null
AS Continue reading “Test-First” Database Development (2) →