David.42.Hall
Joined: 18 Aug 2010
Posts: 5
|
 Posted: Wed Jun 13, 2012 9:59 am Post subject: FileTable different in SQLConnect SQLCompare & SQLSourceCtrl |
 |
|
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:
| Code: |
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:
| Code: |
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 |
|
|
