April 29, 2010 at 1:16 pm
Long time since I've posted here (haven't needed to since you guys have almost all other questions answered!!!) and I hope everyone is doing well.
SQL Server 2008 with all the lastest service packs and updates (NOT R2). Standard edition.
Running on Windows Server 2008 with all the lastest service packs and updates (NOT R2). Standard Edition.
We have several Access 2000 databases that we are slowly but surely moving to SQL Server but we hit a snag.
We moved one of the Access 2000 db's to SQL Server and it works like a charm. Good performance, etc.
I then moved two more to the same SQL Server DB just different schemas and they perform like dogs. Inserts, updates, and deletes are ssslllooww but selects are as fast as they are in the first schema.
To move the databases, we follow this procedure:
1. Use the import wizard to move the data to a new schema.
2. Execute this SQL Statement to create views:
use [PMX3]
begin transaction;
DECLARE @COLUMN_NAME SYSNAME;
DECLARE @TABLE_NAME SYSNAME;
DECLARE @DATA_TYPE NVARCHAR(128);
DECLARE @CLIENT_CURSOR_FS INTEGER;
DECLARE @TABLE_CURSOR_FS INTEGER;
DECLARE @COLUMN_CURSOR_FS INTEGER;
DECLARE @CLIENTID NVARCHAR(5);
DECLARE @COLUMN_LIST NVARCHAR(2048);
DECLARE @VARIABLE_LIST NVARCHAR(2048);
DECLARE @SCHEMA_NAME SYSNAME;
DECLARE @SQL VARCHAR(4096);
DECLARE @TABLES_VERSION BIGINT;
DECLARE @VIEWS_VERSION BIGINT;
DECLARE @SCRIPT_MAX_TABLES_VERSION BIGINT;
DECLARE @SCRIPT_MAX_VIEWS_VERSION BIGINT;
SET @SCRIPT_MAX_TABLES_VERSION = 1;
SET @SCRIPT_MAX_VIEWS_VERSION = 1;
DECLARE CLIENT_CURSOR CURSOR FOR SELECT ClientID,SchemaName, TablesVersion, ViewsVersion FROM [dbo].[ClientList];
OPEN CLIENT_CURSOR;
FETCH NEXT FROM CLIENT_CURSOR INTO @CLIENTID,@SCHEMA_NAME,@TABLES_VERSION,@VIEWS_VERSION;
SET @CLIENT_CURSOR_FS=@@FETCH_STATUS;
WHILE @CLIENT_CURSOR_FS = 0
BEGIN
IF @TABLES_VERSION<@SCRIPT_MAX_TABLES_VERSION OR @VIEWS_VERSION<@SCRIPT_MAX_VIEWS_VERSION
BEGIN
DECLARE TABLE_CURSOR CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@SCHEMA_NAME AND TABLE_TYPE='BASE TABLE';
OPEN TABLE_CURSOR;
FETCH NEXT FROM TABLE_CURSOR INTO @TABLE_NAME;
SET @TABLE_CURSOR_FS=@@FETCH_STATUS;
WHILE @TABLE_CURSOR_FS = 0
BEGIN
IF @TABLES_VERSION<1
BEGIN
SET @SQL='ALTER TABLE [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '] ADD';
SET @SQL=@SQL + ' IOPS_SysID int NOT NULL IDENTITY (1, 1),';
SET @SQL=@SQL + ' IOPS_ClientID int NOT NULL CONSTRAINT DF_' + @TABLE_NAME + '_IOPS_ClientID DEFAULT ' + str(@CLIENTID) + ',';
SET @SQL=@SQL + ' IOPS_TimeStamp timestamp NOT NULL;';
SET @SQL=@SQL + 'ALTER TABLE [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '] SET (LOCK_ESCALATION = TABLE);';
PRINT @SQL;
EXEC (@SQL);
SET @SQL='CREATE UNIQUE CLUSTERED INDEX [PK] ON [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + ']';
SET @SQL=@SQL + ' ([IOPS_SysID] ASC) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]';
PRINT @SQL;
EXEC (@SQL);
DECLARE COLUMN_CURSOR CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLE_NAME AND TABLE_SCHEMA=@SCHEMA_NAME;
OPEN COLUMN_CURSOR;
FETCH NEXT FROM COLUMN_CURSOR INTO @COLUMN_NAME,@DATA_TYPE;
SET @COLUMN_CURSOR_FS=@@FETCH_STATUS;
WHILE @COLUMN_CURSOR_FS = 0
BEGIN
IF @DATA_TYPE='bit'
BEGIN
SET @SQL='ALTER TABLE [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '] ADD CONSTRAINT [DF_' + @TABLE_NAME + '_' + @COLUMN_NAME + '] DEFAULT 0 FOR [' + @COLUMN_NAME + ']';
PRINT @SQL;
EXEC (@SQL);
END;
FETCH NEXT FROM COLUMN_CURSOR INTO @COLUMN_NAME,@DATA_TYPE;
SET @COLUMN_CURSOR_FS=@@FETCH_STATUS;
END;
CLOSE COLUMN_CURSOR;
DEALLOCATE COLUMN_CURSOR;
END;
IF @VIEWS_VERSION<1
BEGIN
DECLARE COLUMN_CURSOR CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLE_NAME AND TABLE_SCHEMA=@SCHEMA_NAME;
OPEN COLUMN_CURSOR;
FETCH NEXT FROM COLUMN_CURSOR INTO @COLUMN_NAME,@DATA_TYPE;
SET @COLUMN_CURSOR_FS=@@FETCH_STATUS;
SET @COLUMN_LIST='';
SET @VARIABLE_LIST='';
WHILE @COLUMN_CURSOR_FS = 0
BEGIN
IF SUBSTRING(@COLUMN_NAME,1,5)!='IOPS_'
BEGIN
SET @COLUMN_LIST += ',[' + @COLUMN_NAME + ']'
SET @VARIABLE_LIST += ',@' + @COLUMN_NAME
END;
FETCH NEXT FROM COLUMN_CURSOR INTO @COLUMN_NAME,@DATA_TYPE;
SET @COLUMN_CURSOR_FS=@@FETCH_STATUS;
END;
CLOSE COLUMN_CURSOR;
DEALLOCATE COLUMN_CURSOR;
SET @COLUMN_LIST = SUBSTRING(@COLUMN_LIST,2,4096);
SET @VARIABLE_LIST = SUBSTRING(@VARIABLE_LIST,2,4096);
--SET @SQL='CREATE VIEW [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '_view] with schemabinding AS SELECT ' + @COLUMN_LIST + ', convert(int,IOPS_SYSID) as SYSID FROM [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + ']'
SET @SQL='CREATE VIEW [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '_view] with schemabinding AS SELECT ' + @COLUMN_LIST + ' FROM [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + ']'
PRINT @SQL;
EXEC (@SQL);
SET @SQL='CREATE UNIQUE CLUSTERED INDEX [PK] ON [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '_view]';
SET @SQL=@SQL + ' ([SysID] ASC) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]';
PRINT @SQL;
--EXEC (@SQL);
FETCH NEXT FROM TABLE_CURSOR INTO @TABLE_NAME;
SET @TABLE_CURSOR_FS=@@FETCH_STATUS;
END;
END;
CLOSE TABLE_CURSOR;
DEALLOCATE TABLE_CURSOR;
SET @SQL='UPDATE [dbo].[ClientList] set TablesVersion=' + str(@SCRIPT_MAX_TABLES_VERSION) + ',ViewsVersion=' + str(@SCRIPT_MAX_VIEWS_VERSION) + ' where ClientId=' + STR(@CLIENTID) + ';';
PRINT @SQL;
EXEC (@SQL);
END;
FETCH NEXT FROM CLIENT_CURSOR INTO @CLIENTID,@SCHEMA_NAME,@TABLES_VERSION,@VIEWS_VERSION;
SET @CLIENT_CURSOR_FS=@@FETCH_STATUS;
END;
CLOSE CLIENT_CURSOR;
DEALLOCATE CLIENT_CURSOR;
--rollback;
commit;
3. Execute this SQL Statement to create indexes, keys, etc (the schema name is changed for whatever schema has been added - yes, I know I need to use a variable there).
USE [PMX3]
GO
Create NONCLUSTERED INDEX [ChargeID] on [FAT].[Allocation] ( [ChargeID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [CreditID] on [FAT].[Allocation] ( [CreditID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[Allocation] ( [CreditID ] ASC, [ChargeID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[Allocation_view] ( [CreditID ] ASC, [ChargeID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [ID] on [FAT].[ANSISetup] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[ANSISetup] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[ANSISetup_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [ReceiverID] on [FAT].[ANSISetup] ( [ClearingHouse ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [Description] on [FAT].[BankAccount] ( [Description ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [ID] on [FAT].[BankAccount] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[BankAccount] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[BankAccount_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [AccountID] on [FAT].[BillingHistory] ( [AccountID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [GuarantorID] on [FAT].[BillingHistory] ( [GuarantorID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [PatientID] on [FAT].[BillingHistory] ( [PatientID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[BillingHistory] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[BillingHistory_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [ID] on [FAT].[Bills] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [PatientBills] on [FAT].[Bills] ( [PatientID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [PatientID_Link] on [FAT].[Bills] ( [PatientID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [PriPolicyID_Link] on [FAT].[Bills] ( [PriPolicyId ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [SecPolicyID_Link] on [FAT].[Bills] ( [SecPolicyId ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [SecPolicyId1] on [FAT].[Bills] ( [TerPolicyId ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [PatientID] on [FAT].[Coverage] ( [PatientID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [PolicyID] on [FAT].[Coverage] ( [PolicyID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[Coverage] ( [PatientID ] ASC, [PolicyID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[Coverage_view] ( [PatientID ] ASC, [PolicyID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [Date_Index] on [FAT].[Deposit] ( [Date ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [ID] on [FAT].[Deposit] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [ID] on [FAT].[Deposit_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [Unapplied_Index] on [FAT].[Deposit] ( [Unapplied ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[Environment] ( [VariableName ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[Environment_view] ( [VariableName ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [VariableCategory] on [FAT].[Environment] ( [VariableCategory ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[FeeSchedule] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[FeeSchedule_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[ImportedAPPFiles] ( [Filename ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[ImportedAPPFiles_view] ( [Filename ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [Name] on [FAT].[Location] ( [Name ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[Location] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[Location_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[NextID] ( [Code ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[NextID_view] ( [Code ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [FileNumber_Index] on [FAT].[Patient] ( [FileNumber ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [GuarantorID_Link] on [FAT].[Patient] ( [GuarantorID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [ID] on [FAT].[Patient] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [ID] on [FAT].[Patient_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [Name_Index] on [FAT].[Patient] ( [Name ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [PolicyPatient] on [FAT].[Patient] ( [PrimaryPolicyID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [PolicyPatient1] on [FAT].[Patient] ( [SecondaryPolicyID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [PolicyPatient2] on [FAT].[Patient] ( [TertiaryPolicyID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [Telephone_Index] on [FAT].[Patient] ( [HomePhone ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[PatientNotes] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[PatientNotes_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [CarrierID_Link] on [FAT].[Policy] ( [CarrierID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [HolderID_Link] on [FAT].[Policy] ( [HolderID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [ID] on [FAT].[Policy] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [ID] on [FAT].[Policy_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [Name] on [FAT].[Practice] ( [Name ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [Primary] on [FAT].[Practice] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [Primary] on [FAT].[Practice_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [Code] on [FAT].[Provider] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [Code] on [FAT].[Provider_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [Name_Index] on [FAT].[Provider] ( [Name ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [Code] on [FAT].[Referral] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [Code] on [FAT].[Referral_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [Name] on [FAT].[Referral] ( [LastName ] ASC, [FirstName ] ASC, [Middle ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [Name1] on [FAT].[Referral] ( [Name ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [AdjGroupCode] on [FAT].[Transaction] ( [AdjGroupCode ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [AdjReasonCode] on [FAT].[Transaction] ( [AdjReasonCode ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [BillID_Link] on [FAT].[Transaction] ( [BillID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [BillsTransaction] on [FAT].[Transaction] ( [BillID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [ChargeID] on [FAT].[Transaction] ( [ChrgID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [Date_Index] on [FAT].[Transaction] ( [Date ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [DepositID_Link] on [FAT].[Transaction] ( [DepositID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [EntryDate_Index] on [FAT].[Transaction] ( [EntryDate ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique NONCLUSTERED INDEX [ID] on [FAT].[Transaction] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create Unique CLUSTERED INDEX [ID] on [FAT].[Transaction_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [PatientID_Link] on [FAT].[Transaction] ( [PatientID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
Create NONCLUSTERED INDEX [PatientTransaction] on [FAT].[Transaction] ( [PatientID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
CREATE UNIQUE CLUSTERED INDEX [PrimaryKey_IOPS] ON [FAT].[Accounting_view] ([AgedBy] ASC,[ByTransactions] ASC,[DelinquentWhen] ASC,[FinanceAPR] ASC,[MinimumFinance] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
CREATE UNIQUE CLUSTERED INDEX [PrimaryKey_IOPS] ON [FAT].[Bills_view] ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
CREATE UNIQUE CLUSTERED INDEX [PrimaryKey_IOPS] ON [FAT].[FeeScheduleNames_view] ([NameA] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create NONCLUSTERED INDEX [AppointmentLookup] on [FAT].[Appointments_FDD] ( [RepeatType ] ASC, [Date ] ASC, [Time ] ASC, [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create NONCLUSTERED INDEX [Date_Index] on [FAT].[Appointments_FDD] ( [Date ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[Appointments_FDD] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[Appointments_FDD_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create NONCLUSTERED INDEX [Description] on [FAT].[AppointmentType_FDD] ( [Description ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create NONCLUSTERED INDEX [ID] on [FAT].[AppointmentType_FDD] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[AppointmentType_FDD] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[AppointmentType_FDD_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create NONCLUSTERED INDEX [ID] on [FAT].[NextApptID_FDD] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[NextApptID_FDD] ( [Code ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[NextApptID_FDD_view] ( [Code ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create Unique NONCLUSTERED INDEX [ID] on [FAT].[Provider_FDD] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create Unique CLUSTERED INDEX [ID] on [FAT].[Provider_FDD_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create NONCLUSTERED INDEX [Name] on [FAT].[Provider_FDD] ( [Name ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[Provider_FDD] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create NONCLUSTERED INDEX [Description] on [FAT].[Resource_FDD] ( [Description ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create NONCLUSTERED INDEX [ID] on [FAT].[Resource_FDD] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create Unique NONCLUSTERED INDEX [PrimaryKey] on [FAT].[Resource_FDD] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--Create Unique CLUSTERED INDEX [PrimaryKey] on [FAT].[Resource_FDD_view] ( [ID ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--CREATE UNIQUE CLUSTERED INDEX [PrimaryKey_IOPS] ON [FAT].[Options_FDD_view] ([LinkType] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
GO
4. Go to Access database and create linked tabled to views in SQL Server database.
This worked perfectly for the first Access 2000 db we moved over. It has been purring like a kitten for 2 months now. But every other one I move, as a different schema in the same database (PMX3 in case you didn't notice), has slow updates, deletes, and inserts - but selects work just fine.
By the way, each workstation has one SYSTEM DSN ODBC set up to connect to the SQL Server (the Access linked tables are via ODBC).
Any ideas?
JamesNT
April 29, 2010 at 1:24 pm
Looks like you have created indexed views for all your tables!!!!
To starters get rid of all "Create Unique CLUSTERED INDEX ...." for <tablename>_<view>
* Noel
April 29, 2010 at 1:34 pm
We created the views in order to allow us to change the underlying tables. We are hoping to better normalize this database as time moves on but the application itself will need to see the tables as they are - hence the views.
I take it having the views is a bad idea? Please keep in mind that the first schema works just fine and it has the views.
The linked tables in the Access 2000 databases are all linked to views, not the underlying tables.
JamesNT
April 29, 2010 at 3:40 pm
A clustered index on column(s) containing non-sequential data is one explanation for extremely poor performing inserts and updates. With a clustered table, SQL Server clusters the data pages (basically like, but not exactly the same as sorting) in a b-tree fashion similar to how pages are structured in an indexe. If you cluster on something like customer_name or phone_number, then each batch of inserts or updates may require the re-shuffling of records or entire pages in the table to different locations.
Also, the fill factor, specified when creating the table, determines how much additional free space is added to each page to accomodate the relocation of records. A higher fill factor means that records can be moved around, if needed, wihout having to move entire pages. Even if the tables in both schemas are clustered the same way, it possible they were created using a different fill factor setting for some reason.
Also, if you can't see any obvious differences in the definition of the objects in each schema, then perform some sample inserts and updates and compare the Query Execution Plan, which will provide some additional clues about what is going on behind the scenes.
Execution Plan Basics
http://www.simple-talk.com/sql/performance/execution-plan-basics/
Viewing the Execution Plan of a Running Query in SQL Server
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply