Here’s the announcement – and Microsoft SQL Server Developer Edition is now free! Is it big news? Yes, but if you’re a developer the question of whether to favour an upgrade in due course is more nuanced than it used to be. Continue reading Sql Server 2016 will be Released on 1 June
Category Archives: Transact-Sql
Enforcing Upper/Lower Case in a Sql Server Column
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
JSON Support in Sql Server 2016 CTP3.2 – Quick Start
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
Simplify Reconciliation Code with Filtered Indexes
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
Sql Server Filtered Index as a Unique Constraint on a Specific Value
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
Forgotten Features of Sql Server
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
Sql JOIN Cheat Sheet
Someone accidentally found my Sql Collation Cheat Sheet while searching for a “Sql JOIN Cheat Sheet” so I thought I’d knock out a post on the topic. Towards the end, you’ll find a script that demonstrates them. The script was run in Azure Sql. Some joins may be Microsoft extensions to the ANSI standard. Look at the documentation for fuller information.
When I was a beginner I had thought there was a deep meaning to the words “left” and “right”, as in LEFT OUTER JOIN. It was a mild let-down to find out that it only refers to placement of the table name in relation to the “JOIN” keyword; switching a LEFT OUTER to a RIGHT OUTER join defines the table named after the join keyword as the outer table. Continue reading Sql JOIN Cheat Sheet
Raising Errors in Transact-Sql
There seems to be quite a bit of confusion about the way errors should be raised in newer versions of Transact-Sql. The documentation for RAISERROR says: “New applications should use THROW instead.” It doesn’t say that RAISERROR is deprecated; when you look at the list of deprecated features what you find is that invoking RAISERROR without an error number is now deprecated, but it’s unclear whether the command itself will go. Continue reading Raising Errors in Transact-Sql
JSON Support in Sql Server 2016 CTP2 – Quick Start
Please note: CTP2 has been superseded. See this post for information about JSON in Sql Server CTP3.2
-
This post is a quick tour of JSON Support in Sql Server 2016. Three key points:
1) There won’t be a native JSON type, instead it will be represented as an nvarchar.
2) There is no BSON or JSONB support.
3) Some JSON features will not be available until CTP3. Continue reading JSON Support in Sql Server 2016 CTP2 – Quick Start
Sql Server CONTEXT_INFO with Numeric Values
A trigger takes no arguments but it is possible to use CONTEXT_INFO() to make 128 bytes of binary data values accessible to it. I wanted to provide an 8-byte (bigint) value from a sequence which the trigger would write to a primary key column.
Storing and retrieving a numeric value in CONTEXT_INFO is not difficult, but it’s another thing that doesn’t come up very often. For this type of problem I like to find concise explanations with source code so that I can get back to the main problem quickly. Once again my searches didn’t turn anything up so I’m trying to fill the gap with this post. Continue reading Sql Server CONTEXT_INFO with Numeric Values
blog.philipsymons.com 