Microsoft SQL Server 2017

Ignition
1

Greetings,

I am thinking about using Microsoft SQL Server 2017 Standard Edition as the database server for our Ignition system. Does anyone use it? What is your experience with it? Any feedback is much appreciated.

Thanks,
Sam

2

We use it, and it works (if you work in a MS environment).

If you use it just as historian, alarm and audit log, it works out of the box. Just like any other big database. We’ve once had issues with fragmentation on big historian tables, so we had to keep our tables a bit smaller. But that’s about it.

If you plan to create custom tables and queries for it, then you probably have to investigate the SQL syntax. There are differences between SQL dialects, and you can use raw SQL in Ignition. So once you start using a certain DB more and more, and start writing scripts for it, you’re kinda stuck with the dialect you’re using.

3

This is true, but can be mitigated if you practice safe SQL.... using ANSI standard quoting and explicit capitalization of identifiers, in particular. (Table, column, and schema names.) You may have to turn on support of standard quoting in MySQL, but the other major brands will accept it out of the box.
This is one of the reasons I use PostgreSQL in development -- it natively uses ANSI quoting and otherwise tries hard to be extremely standards-compliant. I can generally take any properly-quoted Postgres query to any other brand with little or no changes. The differences are then usually the availability of specific functions and the spelling of them, not the core syntax.
If using capitalization in tables and columns, this works pretty much everywhere:

SELECT "SomeColumn", "SomeOtherColumn"
FROM "MyTable"
WHERE "AKeyColumn" = ?
ORDER BY "AnotherColumn" DESC

Especially avoid Microsoft's square brackets and MySQL's back-tick quoting styles.

4

Yep,

We always use valid table and column names, and avoid quoting of those names as much as possible. This makes the queries very portable.

But then we also have T-SQL scripts to generate f.e. audit triggers and create audit tables. Translating those triggers to a different dialect will cause quite some work, and keeping the code for 2 or more dialects in sync will be even harder. Granted, it's not something you start with, but eventually you have lots of tools for your dialect, making it hard to move away.

5

Danger Will Robinson!
This will be true only if you are creating your own DDL with unquoted identifiers. The major brands all have different capitalization rules for unquoted identifiers, and they all generate quoted DDL in their GUIs. I strongly recommend you use mixed capitalization in all identifiers, and always quote them in your queries.

6

Rules for capitalisation of quoted identifiers are also different. In PostgreSQL, the query below is valid, but in MS SQL, it isn’t.

CREATE TABLE "Test" (
	Id Int,
	Val VarChar(50)
)

CREATE TABLE "test" (
	Id Int,
	Val VarChar(50)
)

The table “test” already exists in MS SQL, as table and column name comparisons are case insensitive, while queries are case sensitive. Quoting also doesn’t make a difference to capitalisation handling in MS SQL. In postgreSQL, it’s the other way around, table and column name comparisons are case sensitive, (so “Test” isn’t equal to “test”), while queries are normally case insensitive (querying Test is equal to querying test), unless when you quote them.

You can choose to either never, or always quote your column and table names in PostgreSQL, and all queries will work as long as you stick to that choice. But there will be a difference in the returned data. This can bite you when you process it further with scripting.

Quoted column names are indeed more portable that way. But you can still bump into strange differences.

7

Thank you Sanderd17 & pturmel for the feedback.
Sam