Shortly after the last release of Database Schema Reader, here's another release.

Last time I added a CopyToSQLite tool, which reads (almost) any database and creates a SQLite clone. I also added experimental support for SQLServer CE 4.0, but it had some big limitations.

So the obvious next step was to fix some of those limitations, and that's what this release focuses on. Unfortunately CopyToSQLite.exe is now a little misnamed.

I added a little bit of conversion code so VARCHAR(MAX) found in the origin database gets converted to NTEXT (and VARBINARY to IMAGE). The reading of SQLServer CE 4 databases got improved too (capturing the  foreign and unique keys and identity columns).

SQLServer allows an integer column to be marked as "Identity" so it will be auto-generated (the equivalent in Oracle is to create a sequence and an insert trigger). But you can't include the identity column when you insert a row. So, when cloning data, SQLServer also allows identity-inserts with SET IDENTITY_INSERT [MyTable] ON/OFF. When you've done that, you should reset the identity seed with DBCC CHECKIDENT.

But in SQLServer CE 4, there is no DBCC CHECKIDENT. You have to do an ALTER TABLE [MyTable] ALTER COLUMN [IdentityColumn] IDENTITY (999,1) (where 999 is the new max(IdentityColumn)). It's another of those little gotchas between SQLServer and SQLServer CE.

SQLServer CE 4 has some great new paging syntax:

SELECT Id, Name FROM MyTable
ORDER BY Name
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY

It's similar to the LIMIT/OFFSET syntax in MySQL and SQLite, but (reasonably enough) must follow an ORDER BY. Rather than offset/skipping a number of rows, I'd like to specify just page number and page size - or another formula. The SQLServer 2011/Denali documentation shows the offset/fetch syntax for a start row/end row expression:

SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC
OFFSET @StartingRowNumber - 1 ROWS
FETCH NEXT @EndingRowNumber - @StartingRowNumber + 1 ROWS ONLY

But that doesn't work in SQLServer CE 4. You can use parameters, or constants, or expressions with constants, but apparently not expressions with constants and parameters. Oh well, it's still much better than horrible OVER subqueries.

Overall, the "CopyToSQLite" to SQLServer CE 4 seems to work well. Using easily deployable file databases like SQLServer CE and SQLite is simple and powerful.