Sunday, 8 June 2014

Check constraints not what you thought they were?

I was recently adding a check constraint to a table in SQL Server, and noted some behaviour which surprised me, so I thought I'd share it with you. I've tried this on SQL Server 2012 and 2008 R2.

I added two fields, which either both needed to be null or both filled in. (Now, it is possible this requirement points to a normalisation requirement - but that's not the point at hand). So the check constraint is simply to ensure that either:

  • Field A is null AND Field B is null
OR
  • Field A is not null AND Field B is not null 

So I used the following check constraint:


USE tempdb


CREATE TABLE AllOrNothing
    
(
      
id INT IDENTITY(1, 1) ,
      
FieldA VARCHAR(100) ,
      
FieldB VARCHAR(100)
    )


ALTER TABLE AllOrNothing ADD CONSTRAINT UK_AllOrNothing 
    CHECK (
             (FieldA IS NULL AND FieldB IS NULL) 
           OR 
             (FieldA IS NOT NULL AND FieldB IS NOT NULL)
           )


Now, what surprised me was when I queried sys.constraints on this:



and again if I modify the constraint in SQL Server Management Studio:



The brackets have gone! Now, this isn't going to affect the functionality due to the precedence order of the logical operators, but will still have a SQL Developer looking at it and wondering if the intended functionality is what's been put in place. (Of course, the best way to be sure is to unit test your code).

I thought I'd blog about this, as whilst it doesn't change the function of what's been coded, it does change the form of it, which is unusual and was to me unexpected.


Thursday, 24 April 2014

What do you use to version control your databases?

Many developers version control (AKA source control) the databases upon which they work, to give them the same benefits as we receive when using source control for applications, as well as the benefit that we have the two systems version controlled together. But a recent tweet from Red Gate got me thinking about those that don't use source control for databases.



I asked Red Gate about the source of their figures, and they helpfully supplied the results of the polls on SQLServerCentral.com upon which they had based this. The figures make interesting reading, and I wanted to draw out a couple of trends which I found interesting. Here's a plot of the results

A graph of the poll results supplied by Red Gate
"Which Version Control System do you currently use to store your database scripts?"
Results from SQL Server Central.com
24% of developers not using database source control actually seemed a little low to me - my experience is that a great number of companies don't use source control for databases, but this may be due to a number of things, not least that as a consultant I tend to visit companies that need assistance! Looking at the figures however, I can see that it has varied between 31% and 21% of people don't use source control in each poll, and doesn't seem to be dropping dramatically. Now, this may be a different set of people each time, or it may be due to the self-selection nature of such polls, but it is clearly a not-inconsiderable amount of people.

I've also noted with relief that the number of users of VSS has dropped dramatically since support for it was discontinued - if you still use VSS please consider migrating to a supported system. TFS is growing in use, probably due to the inclusion of licenses with MSDN subscriptions.

The use of Git and Mercurial is also surprisingly low - perhaps the distributed nature of these systems do not lend themselves as naturally to database source control, but I have used Git with database scripts with no real issues in the past.

I found these results interesting, and if you have not yet adopted source control for your databases, you may be interested to read my series of articles entitled Stairway to Database Source Control, which are currently being published on SQL Server Central.com.