FileTable different in SQLConnect SQLCompare & SQLSourceCtrl

David.42.Hall

I linked a SQL 2012 database (using SQL Source Control) to an existing SVN repository which contained a SQLConnct project pointing to the same database. When I go to the Commit Changes tab in SSMS, it shows pending changes for the one FileTable in the database.

SQLConnect and SQLCompare generate a slightly different script than SQL Source Control, so SQL Source Control shows that table in the database as being different from what's in SVN. SQL Source Control shows things like the original comments in the constraints and some of the objects have square brackets around them in SQLConnect and SQLCompare but not in SQL Source Control. SQL Source Control also shows the default constraint for the path_locator column with a lot fewer parenthesis than the other two. SQL Source Control also shows the actual name of the default FileStream where the other two just say [default].

I haven't tried running any of the create (or change) scripts because the table really doesn't need to change and I suspect that even if it works (syncing SQL Source Control), then the other apps would see it as out of sync since they script it a little differently.

I'd like to see all three of the apps generate the same script so they recognize that no changes are needed for a FileTable that was originally created with the CREATE TABLE ... AS FILETABLE command in SSMS.

Another note: All three of them (SQLConnect, SQLCompare and SQL Source Control) generate a more conventional "CREATE TABLE" script than SSMS does when I have it "Script table to new Query Window".

SSMS scripts it like this:

CREATE TABLE [dbo].[OrganizationFile] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [Primary_FileStream]
WITH
(
FILETABLE_DIRECTORY = N'OrganizationFiles', FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS
)

... while SQL Source Control scripts it like this (red indicates the places where a difference is shown):

CREATE TABLE [dbo].[OrganizationFile]
(
[stream_id] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF__Organizat__strea__158603F9] DEFAULT (newsequentialid()),
[file_stream] [varbinary] (max) FILESTREAM NULL,
[name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[path_locator] [sys].[hierarchyid] NOT NULL CONSTRAINT [DF__Organizat__path___167A2832] DEFAULT (convert(hierarchyid, '/' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 6))) + '.' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 7, 6))) + '.' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 13, 4))) + '/')),
[parent_path_locator] AS (case when [path_locator].[GetLevel]()=(1) then NULL else [path_locator].[GetAncestor]((1)) end) PERSISTED,
[file_type] AS (getfileextension([name])) PERSISTED,
[cached_file_size] AS (datalength(file_stream)) PERSISTED,
[creation_time] [datetimeoffset] NOT NULL CONSTRAINT [DF__Organizat__creat__176E4C6B] DEFAULT (sysdatetimeoffset()),
[last_write_time] [datetimeoffset] NOT NULL CONSTRAINT [DF__Organizat__last___186270A4] DEFAULT (sysdatetimeoffset()),
[last_access_time] [datetimeoffset] NULL CONSTRAINT [DF__Organizat__last___195694DD] DEFAULT (sysdatetimeoffset()),
[is_directory] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_di__1A4AB916] DEFAULT ((0)),
[is_offline] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_of__1B3EDD4F] DEFAULT ((0)),
[is_hidden] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_hi__1C330188] DEFAULT ((0)),
[is_readonly] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_re__1D2725C1] DEFAULT ((0)),
[is_archive] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_ar__1E1B49FA] DEFAULT ((1)),
[is_system] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_sy__1F0F6E33] DEFAULT ((0)),
[is_temporary] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_te__2003926C] DEFAULT ((0))
CONSTRAINT [UQ__Organiza__9DD95BAF30286A96] UNIQUE NONCLUSTERED ([stream_id]) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [Primary_FileStream]
GO
-- Constraints and Indexes

ALTER TABLE [dbo].[OrganizationFile] ADD CONSTRAINT [CK__OrganizationFile__10C14EDC] CHECK (/*IsFilenameValid*/ (isfilenamevalid([name])=(1)))
GO
ALTER TABLE [dbo].[OrganizationFile] WITH NOCHECK ADD CONSTRAINT [CK__OrganizationFile__11B57315] CHECK NOT FOR REPLICATION (/*CheckValidAttributes*/ (filetable_check_valid_attributes(CONVERT([varbinary](892),[path_locator],0),[is_directory],case when [file_stream] IS NULL then (1) else (0) end)=(1)))
GO
ALTER TABLE [dbo].[OrganizationFile] WITH NOCHECK ADD CONSTRAINT [CK__OrganizationFile__12A9974E] CHECK NOT FOR REPLICATION (/*ParentsAreDirectories*/ (filetable_parents_are_directories((217103864),CONVERT([varbinary](892),[path_locator],0),[is_directory])=(1)))
GO
ALTER TABLE [dbo].[OrganizationFile] WITH NOCHECK ADD CONSTRAINT [CK__OrganizationFile__139DBB87] CHECK NOT FOR REPLICATION (/*LockAllDescendants*/ (filetable_lock_all_descendants((217103864),(233103921),[name],CONVERT([varbinary](892),[path_locator],0))=(1)))
GO
ALTER TABLE [dbo].[OrganizationFile] ADD CONSTRAINT [PK__Organiza__5A5B77D5A7F8E9D1] PRIMARY KEY NONCLUSTERED ([path_locator]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrganizationFile] ADD CONSTRAINT [UQ__Organiza__A236CBB3D9CD9C89] UNIQUE NONCLUSTERED ([parent_path_locator], [name]) ON [PRIMARY]
GO
-- Foreign Keys

ALTER TABLE [dbo].[OrganizationFile] ADD CONSTRAINT [FK__Organizat__paren__1491DFC0] FOREIGN KEY ([parent_path_locator]) REFERENCES [dbo].[OrganizationFile] ([path_locator])
GO

... and SQLConnect and SQLCompare script it like this:

CREATE TABLE [dbo].[OrganizationFile]
(
[stream_id] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF__Organizat__strea__158603F9] DEFAULT (newsequentialid()),
[file_stream] [varbinary] (max) FILESTREAM NULL,
[name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[path_locator] [sys].[hierarchyid] NOT NULL CONSTRAINT [DF__Organizat__path___167A2832] DEFAULT (CONVERT([hierarchyid],((((('/'+CONVERT([varchar](20),CONVERT([bigint],substring(CONVERT([binary](16),newid(),0),(1),(6)),0),0))+'.')+CONVERT([varchar](20),CONVERT([bigint],substring(CONVERT([binary](16),newid(),0),(7),(6)),0),0))+'.')+CONVERT([varchar](20),CONVERT([bigint],substring(CONVERT([binary](16),newid(),0),(13),(4)),0),0))+'/',0)),
[parent_path_locator] AS (case  when [path_locator].[GetLevel]()=(1) then NULL else [path_locator].[GetAncestor]((1)) end) PERSISTED,
[file_type] AS ([getfileextension]([name])) PERSISTED,
[cached_file_size] AS (datalength([file_stream])) PERSISTED,
[creation_time] [datetimeoffset] NOT NULL CONSTRAINT [DF__Organizat__creat__176E4C6B] DEFAULT (sysdatetimeoffset()),
[last_write_time] [datetimeoffset] NOT NULL CONSTRAINT [DF__Organizat__last___186270A4] DEFAULT (sysdatetimeoffset()),
[last_access_time] [datetimeoffset] NULL CONSTRAINT [DF__Organizat__last___195694DD] DEFAULT (sysdatetimeoffset()),
[is_directory] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_di__1A4AB916] DEFAULT ((0)),
[is_offline] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_of__1B3EDD4F] DEFAULT ((0)),
[is_hidden] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_hi__1C330188] DEFAULT ((0)),
[is_readonly] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_re__1D2725C1] DEFAULT ((0)),
[is_archive] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_ar__1E1B49FA] DEFAULT ((1)),
[is_system] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_sy__1F0F6E33] DEFAULT ((0)),
[is_temporary] [bit] NOT NULL CONSTRAINT [DF__Organizat__is_te__2003926C] DEFAULT ((0))
CONSTRAINT [UQ__Organiza__9DD95BAF30286A96] UNIQUE NONCLUSTERED  ([stream_id]) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [default]
GO
-- Constraints and Indexes

ALTER TABLE [dbo].[OrganizationFile] ADD CONSTRAINT [CK__OrganizationFile__10C14EDC] CHECK (([isfilenamevalid]([name])=(1)))
GO
ALTER TABLE [dbo].[OrganizationFile] WITH NOCHECK ADD CONSTRAINT [CK__OrganizationFile__11B57315] CHECK NOT FOR REPLICATION (([filetable_check_valid_attributes](CONVERT([varbinary](892),[path_locator],(0)),[is_directory],case  when [file_stream] IS NULL then (1) else (0) end)=(1)))
GO
ALTER TABLE [dbo].[OrganizationFile] WITH NOCHECK ADD CONSTRAINT [CK__OrganizationFile__12A9974E] CHECK NOT FOR REPLICATION (([filetable_parents_are_directories]((217103864),CONVERT([varbinary](892),[path_locator],(0)),[is_directory])=(1)))
GO
ALTER TABLE [dbo].[OrganizationFile] WITH NOCHECK ADD CONSTRAINT [CK__OrganizationFile__139DBB87] CHECK NOT FOR REPLICATION (([filetable_lock_all_descendants]((217103864),(233103921),[name],CONVERT([varbinary](892),[path_locator],(0)))=(1)))
GO
ALTER TABLE [dbo].[OrganizationFile] ADD CONSTRAINT [PK__Organiza__5A5B77D5A7F8E9D1] PRIMARY KEY NONCLUSTERED  ([path_locator]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrganizationFile] ADD CONSTRAINT [UQ__Organiza__A236CBB3D9CD9C89] UNIQUE NONCLUSTERED  ([parent_path_locator], [name]) ON [PRIMARY]
GO
-- Foreign Keys

ALTER TABLE [dbo].[OrganizationFile] ADD CONSTRAINT [FK__Organizat__paren__1491DFC0] FOREIGN KEY ([parent_path_locator]) REFERENCES [dbo].[OrganizationFile] ([path_locator])
GO

Brian Donahue

Thanks for contacting us.
Just to clarify - I believe you are looking at a longstanding issue with the SQL Compare Engine which is used in all products. The scripts you pasted in from Source Control come from the SQL view windows and the comparison in that case uses a different (and less than accurate) text parsing engine. If you compare the script output from SQL Compare to the script that SQL Source Control saved to disk (rather than what you see in the history window) then the scripts should be identical.

The only exception to the rule is that there are configurable options in SQL Compare, so you may be employing different comparison options, such as ignoring the names of filegroups. There is nascent support for setting options in Source Control, but they are not exposed in the UI.

In all cases, the support for SQL 2012 is probably not complete - as far as I know the state of play is that new features like FileTable haven't been realized fully yet. I'll see what I can find out.

David.42.Hall

Thanks for the reply. The only reason that this issue came up is because my SQL SourceControl and SQLConnect are using the same SVN repository and the same database, but SQLConnect says the File Table is in sync with the project and SQL Source Control says they are out of sync. That's the real issue I'm trying to resolve. I am aware of the fact that the data shown in the UI is not exactly the same as used by the comparison engine. That's bit me before, so you're right, the data I've shown is from the respective UIs. I'll leave it for you to find out why the the underlying comparison engines disagree in the different products. If it's any help, I'm using the default comparison options in all applications. If there's a way I can tweak the SQL SourceControl comparison options so that they are the same as SQLConnect and SQLCompare, I'd be willing to give that a try. Just let me know how if you think that's an appropriate approach.

Brian Donahue

SQL Compare errors on the script:

CREATE TABLE [dbo].[OrganizationFile] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [Primary_FileStream] 
WITH 
( 
FILETABLE_DIRECTORY = N'OrganizationFiles', FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS 
)

What I can't understand is how it ended up picking up a definition for a conventional table and showing you that. Maybe it's because the OrganizationFile table used to be a regular table and then you converted it to a FileTable?

As far as showing differences I don't see how there would be a difference between the latest version of SQL Connect and SQL Source Control as the build numbers of SQL Compare Engine are only one off (10.2.0.419 vs 10.2.0.420) and there is no note in the history saying anything important had changed.

David.42.Hall

The "CREATE TABLE ... AS FILETABLE" syntax is the script that SSMS generates. That's the way I originally generated the OrganizationFile table. It was and is a standard FileTable from the beginning. None of the red-gate products generate this script, nor should they. I suspect that the reason is because the exact names of essential elements of the FileTable are hidden (and inconsistent) when that command is used. Specifically the names of the various table constraints and self-referencing foreign key appear to be dynamically generated with that command, so it's not surprising that the comparison engines treat them like conventional tables. In fact, I would expect just that so it can accurately compare the individually named components.

Have you actually tried reproducing the issue? It should be simple to reproduce with these steps:
1) Create a DB with a single file table generated by using the "CREATE TABLE ... AS FILETABLE" syntax in SSMS.
2) Create a SQLConnect project and sync it to this new database.
3) Save the SQLConnect project in Subversion
4) Use SQL Source Control to put the new database itself in source control using the same subversion repository URL as the SQLConnect project.
5) Note that SQL Source Control shows the database (specifically the file table) as being out of sync when it really isn't.

Are you able to reproduce that scenario?

Let me know if you need more info from me.

Thanks,
David