INDEX ON TABLE IS NOT USED WHILE EXECUTING QUERY (Network Steve Forum)

INDEX ON TABLE IS NOT USED WHILE EXECUTING QUERY
Hi,
We have an Appointments table with daily appointments with respect to patient and clinic that need to be schedule for notification.
Tables In Business Logic
[ Database1 tables]
- Appointments (Huge data and Transaction table storing appointment records w.r.t patient for a clinic and clinic location)
- patient (master table for patients)
- providers (master table for Doctors handling that patient)
- patient transaction (OLTP table for all transaction of a patient to a clinic)
[Database2 view]
- vwlocation (Master table , which provide clinic location details, this is a view from another database on same server instance)
[Database3 Table]
- AppointmentAlerts (Destination Table to populate the data of daily appointments of patients in above table, this is created in another database on same server instance)
When I'm trying to club data of database1 and database2 object and checking not existance data in database3 ,
the index built on database3 table is not used.
Problem :
1) Right now we are scheduling any purging of data of Database3 Table, so if data i going to increase its going to
use clustered index scan for table every time for whole data in table (My current worrying point).
2) I've noticed my non clustered index (which is required to be used in point 1) is used only when if the data
after collecting from database1 and database2 tables and views is huge.( this sounds weird).
FYI- I'm attaching the database object schema and data scripts, please suggest if there's something wrong in query w.r.t
my expectation and if there's any other brilliant way to process this w.r.t performance do
March 19th, 2015 3:19am
/*Datbase 1 objects starts*/

USE [Scratch]
GO
/****** Object:  Table [dbo].[appointments]    Script Date: 03/19/2015 12:02:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[appointments]') AND type in (N'U'))
DROP TABLE [dbo].[appointments]
GO
/****** Object:  Table [dbo].[patient_transactions]    Script Date: 03/19/2015 12:02:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND type in (N'U'))
DROP TABLE [dbo].[patient_transactions]
GO
/****** Object:  Table [dbo].[patients]    Script Date: 03/19/2015 12:02:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[patients]') AND type in (N'U'))
DROP TABLE [dbo].[patients]
GO
/****** Object:  Table [dbo].[Providers]    Script Date: 03/19/2015 12:02:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Providers]') AND type in (N'U'))
DROP TABLE [dbo].[Providers]
GO
/****** Object:  Table [dbo].[Providers]    Script Date: 03/19/2015 12:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Providers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Providers](
	[clinic_id] [smallint] NOT NULL,
	[provider_id] [smallint] NOT NULL,
	[name_last] [char](30) NULL,
	[name_first] [char](24) NULL,
	[name_mid] [char](16) NULL,
	[name_suffix] [char](16) NULL,
	[name_prefix] [char](16) NULL,
	[name_degree] [char](16) NULL,
	[provider_type] [char](1) NULL,
	[active_yn] [char](1) NULL,
	[addr_street] [char](24) NULL,
	[addr_other] [char](24) NULL,
	[addr_city] [char](24) NULL,
	[addr_state] [char](2) NULL,
	[addr_zip] [char](10) NULL,
	[phone_1] [char](13) NULL,
	[search_type] [smallint] NULL,
	[minutes_unit] [smallint] NULL,
	[name_full] [char](30) NULL,
	[location_default] [char](2) NULL,
	[sched_provider_yn] [char](1) NULL,
	[prod_provider_yn] [char](1) NULL,
 CONSTRAINT [pk_providers] PRIMARY KEY CLUSTERED 
(
	[clinic_id] ASC,
	[provider_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Providers]') AND name = N'indx_provider_id_clinic_id_active_yn')
CREATE NONCLUSTERED INDEX [indx_provider_id_clinic_id_active_yn] ON [dbo].[Providers] 
(
	[provider_id] ASC,
	[clinic_id] ASC,
	[active_yn] ASC
)
INCLUDE ( [name_last],
[name_first]) 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, FILLFACTOR = 80) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Providers]') AND name = N'indx_provider_type')
CREATE NONCLUSTERED INDEX [indx_provider_type] ON [dbo].[Providers] 
(
	[provider_type] 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, FILLFACTOR = 80) ON [PRIMARY]
GO
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 1, N'DOCTOR                        ', N'LATE                    ', N'CHARGE          ', NULL, NULL, NULL, N'1', N'Y', NULL, NULL, NULL, NULL, NULL, NULL, 2, 10, N'LATE CHARGE DOCTOR            ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 2, N'DOCTOR                        ', N'SCHEUDLE                ', NULL, NULL, NULL, NULL, N'1', N'Y', NULL, NULL, NULL, NULL, NULL, NULL, 2, 10, N'SCHEUDLE DOCTOR               ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 3, N'DOCTOR                        ', N'CAPITATION              ', NULL, NULL, NULL, NULL, N'0', N'Y', NULL, NULL, NULL, NULL, NULL, NULL, 2, 10, N'CAPITATION DOCTOR             ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 4, N'REFUND DOCTOR                 ', N'CREDIT                  ', N'BALANCE         ', NULL, NULL, NULL, N'0', N'Y', NULL, NULL, NULL, NULL, NULL, NULL, 2, 10, N'CREDIT BALANCE REFUND DOCTOR  ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 7, N'DOCTOR DU                     ', N'INACTIVE                ', N'PATIENT         ', NULL, NULL, NULL, N'0', N'Y', NULL, NULL, NULL, NULL, NULL, NULL, 2, 10, N'INACTIVE PATIENT DOCTOR DU    ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 8, N'DOCTOR                        ', N'TERMINATED              ', NULL, NULL, NULL, NULL, N'0', N'Y', NULL, NULL, NULL, NULL, NULL, NULL, 2, 10, N'TERMINATED DOCTOR             ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 9, N'DOCTOR                        ', N'HOUSE                   ', NULL, NULL, NULL, NULL, N'1', N'Y', NULL, NULL, NULL, NULL, NULL, NULL, 2, 10, N'HOUSE DOCTOR                  ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 11, N'STRECKER                      ', N'SHANNON                 ', NULL, NULL, NULL, N'RDH HD          ', N'1', N'Y', N'7725 NE HWY 99A         ', NULL, N'VANCOUVER               ', N'WA', N'98665     ', N'(360)696-4487', 2, 10, N'SHANNON STRECKER, RDH HD      ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 12, N'BRODSKY                       ', N'RUVIN                   ', NULL, NULL, NULL, N'RDH             ', N'1', N'Y', NULL, NULL, NULL, NULL, NULL, NULL, 2, 10, N'RUVIN BRODSKY, RDH            ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 13, N'LOUEY                         ', N'LAURA                   ', NULL, NULL, NULL, N'RDH             ', N'1', N'Y', N'1710 SW 9TH AVE #120    ', NULL, N'BATTLE GROUND           ', N'WA', N'98604     ', N'(360)666-5248', 2, 10, N'LAURA LOUEY, RDH              ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 14, N'LESCANO                       ', N'TYLER                   ', NULL, NULL, NULL, N'RDH             ', N'1', N'Y', N'2458 SE BURNSIDE        ', NULL, N'GRESHAM                 ', N'OR', NULL, N'(503)666-8045', 2, 10, N'TYLER LESCANO, RDH            ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 15, N'PRATT                         ', N'RUTH                    ', NULL, NULL, NULL, N'RDH             ', N'1', N'Y', N'20673 SW ROY ROGERS #201', NULL, N'SHERWOOD                ', N'OR', N'97140     ', N'(503)925-0588', 2, 10, N'RUTH PRATT, RDH               ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 16, N'RODGERS                       ', N'SARAH                   ', NULL, NULL, NULL, N'RDH WV          ', N'1', N'Y', N'8309 SW MAIN ST, STE 100', NULL, N'WILSONVILLE             ', N'OR', N'97070     ', N'(503)682-0550', 2, 10, N'SARAH RODGERS, RDH WV         ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 17, N'DORJEE                        ', N'SEDOL                   ', NULL, NULL, NULL, N'RDH             ', N'1', N'Y', NULL, NULL, NULL, NULL, NULL, N'(360)253-9757', 2, 10, N'SEDOL DORJEE, RDH             ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 18, N'MASSINGER                     ', N'LISA                    ', NULL, NULL, NULL, N'RDH GS          ', N'1', N'Y', N'11982 NE GLISAN         ', NULL, N'PORTLAND                ', N'OR', N'97220     ', N'(503)257-8787', 2, 10, N'LISA MASSINGER, RDH GS        ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 20, N'BURNEY                        ', N'ROCHELLE                ', NULL, NULL, NULL, N'RDH LC          ', N'1', N'Y', N'2038 LLOYD CENTER       ', NULL, N'PORTLAND                ', N'OR', N'97232     ', N'(503)288-5361', 2, 10, N'ROCHELLE BURNEY, RDH LC       ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 21, N'PEHRSON                       ', N'DEANNA                  ', NULL, NULL, NULL, N'RDH CP          ', N'1', N'Y', N'12503 SE MILL PLAIN #222', NULL, N'VANCOUVER               ', N'WA', N'98684     ', N'(360)253-9757', 2, 10, N'DEANNA PEHRSON, RDH CP        ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 23, N'GEMBALA                       ', N'LUANN                   ', NULL, NULL, NULL, N'RDH             ', N'1', N'Y', NULL, NULL, NULL, NULL, NULL, NULL, 2, 10, N'LUANN GEMBALA, RDH            ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 24, N'FLEMING                       ', N'TASHA                   ', NULL, NULL, NULL, N'RDH BG          ', N'1', N'Y', N'1710 SW 9TH AVE #120    ', NULL, N'BATTLE GROUND           ', N'WA', N'98604     ', N'(360)666-5248', 2, 10, N'TASHA FLEMING, RDH BG         ', NULL, N'Y', NULL)
INSERT [dbo].[Providers] ([clinic_id], [provider_id], [name_last], [name_first], [name_mid], [name_suffix], [name_prefix], [name_degree], [provider_type], [active_yn], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [phone_1], [search_type], [minutes_unit], [name_full], [location_default], [sched_provider_yn], [prod_provider_yn]) VALUES (501, 25, N'LY                            ', N'AUDREY                  ', NULL, NULL, NULL, N'RDH             ', N'1', N'Y', N'2038 LLOYD CENTER       ', NULL, N'PORTLAND                ', N'OR', N'97232     ', N'(503)288-5361', 2, 10, N'AUDREY LY, RDH                ', NULL, N'Y', NULL)
/****** Object:  Table [dbo].[patients]    Script Date: 03/19/2015 12:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[patients]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[patients](
	[clinic_id] [smallint] NOT NULL,
	[location_id] [char](2) NULL,
	[name_last] [varchar](60) NULL,
	[name_first] [varchar](60) NULL,
	[name_mid] [varchar](60) NULL,
	[name_suffix] [varchar](60) NULL,
	[addr_street] [varchar](40) NULL,
	[addr_other] [varchar](40) NULL,
	[addr_city] [varchar](40) NULL,
	[addr_state] [char](2) NULL,
	[addr_zip] [char](10) NULL,
	[date_birth] [datetime] NULL,
	[date_first_visit] [datetime] NULL,
	[gender] [char](1) NULL,
	[referral_source_id] [int] NULL,
	[phone_1] [char](13) NULL,
	[phone_2] [char](13) NULL,
	[account_type] [char](2) NULL,
	[acct_holder_yn] [char](1) NULL,
	[provider_id] [smallint] NULL,
	[patient_id] [int] NOT NULL,
	[unique_id] [int] NOT NULL,
	[plan_pri] [int] NULL,
	[plan_sec] [int] NULL,
	[plan_addl] [int] NULL,
	[plan_med] [int] NULL,
	[marital_status] [char](1) NULL,
	[employment_status] [char](1) NULL,
	[alerts] [char](8) NULL,
	[deleted_patient_yn] [char](1) NULL,
	[account_comment_1] [char](28) NULL,
	[account_comment_2] [char](28) NULL,
	[patient_comment_1] [char](28) NULL,
	[patient_comment_2] [char](28) NULL,
	[ss_num] [char](11) NULL,
	[chart_number] [char](16) NULL,
	[chart_number_1] [char](16) NULL,
	[chart_number_2] [char](16) NULL,
	[chart_number_3] [char](16) NULL,
	[chart_number_4] [char](16) NULL,
	[bill_supp_code] [char](1) NULL,
	[email] [char](60) NULL,
	[account_site] [char](2) NULL,
	[relationship] [char](1) NULL,
	[derived_location_id] [char](2) NULL,
 CONSTRAINT [pk_patients] PRIMARY KEY CLUSTERED 
(
	[clinic_id] ASC,
	[unique_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patients]') AND name = N'indx_account_type')
CREATE NONCLUSTERED INDEX [indx_account_type] ON [dbo].[patients] 
(
	[clinic_id] ASC,
	[account_type] 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  = OFF, FILLFACTOR = 80) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patients]') AND name = N'indx_clinic_id_patient_id_derived_location_id')
CREATE NONCLUSTERED INDEX [indx_clinic_id_patient_id_derived_location_id] ON [dbo].[patients] 
(
	[clinic_id] ASC,
	[patient_id] ASC,
	[derived_location_id] ASC,
	[deleted_patient_yn] ASC
)
INCLUDE ( [phone_1],
[phone_2],
[account_type],
[unique_id],
[alerts]) 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, FILLFACTOR = 80) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patients]') AND name = N'indx_name_last')
CREATE NONCLUSTERED INDEX [indx_name_last] ON [dbo].[patients] 
(
	[name_last] ASC,
	[name_first] 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, FILLFACTOR = 80) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patients]') AND name = N'indx_patient_id')
CREATE NONCLUSTERED INDEX [indx_patient_id] ON [dbo].[patients] 
(
	[patient_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, FILLFACTOR = 80) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patients]') AND name = N'indx_phone1')
CREATE NONCLUSTERED INDEX [indx_phone1] ON [dbo].[patients] 
(
	[phone_1] 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  = OFF, FILLFACTOR = 80) ON [PRIMARY]
GO
INSERT [dbo].[patients] ([clinic_id], [location_id], [name_last], [name_first], [name_mid], [name_suffix], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [date_birth], [date_first_visit], [gender], [referral_source_id], [phone_1], [phone_2], [account_type], [acct_holder_yn], [provider_id], [patient_id], [unique_id], [plan_pri], [plan_sec], [plan_addl], [plan_med], [marital_status], [employment_status], [alerts], [deleted_patient_yn], [account_comment_1], [account_comment_2], [patient_comment_1], [patient_comment_2], [ss_num], [chart_number], [chart_number_1], [chart_number_2], [chart_number_3], [chart_number_4], [bill_supp_code], [email], [account_site], [relationship], [derived_location_id]) VALUES (501, N'05', N'LEITNER', N'NEIL', NULL, NULL, N'621 NE 127TH ST', NULL, N'VANCOUVER', N'WA', N'98685     ', CAST(0x0000380E00000000 AS DateTime), CAST(0x0000892200000000 AS DateTime), N'M', 7900010, N'(360)573-9001', NULL, N' F', N'Y', 159, 1010170, 847, 28724, NULL, NULL, NULL, NULL, NULL, NULL, N'N', NULL, NULL, NULL, NULL, NULL, N'103307420       ', NULL, NULL, NULL, NULL, NULL, N'NEILLEITNER@GMAIL.COM                                       ', NULL, NULL, N'05')
INSERT [dbo].[patients] ([clinic_id], [location_id], [name_last], [name_first], [name_mid], [name_suffix], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [date_birth], [date_first_visit], [gender], [referral_source_id], [phone_1], [phone_2], [account_type], [acct_holder_yn], [provider_id], [patient_id], [unique_id], [plan_pri], [plan_sec], [plan_addl], [plan_med], [marital_status], [employment_status], [alerts], [deleted_patient_yn], [account_comment_1], [account_comment_2], [patient_comment_1], [patient_comment_2], [ss_num], [chart_number], [chart_number_1], [chart_number_2], [chart_number_3], [chart_number_4], [bill_supp_code], [email], [account_site], [relationship], [derived_location_id]) VALUES (501, N'05', N'GATTIS', N'JEFFREY', NULL, NULL, N'14600 LLOYDS LANE NE', NULL, N'AURORA', N'OR', N'97002     ', CAST(0x0000418900000000 AS DateTime), CAST(0x000088D400000000 AS DateTime), N'M', 7900020, N'(503)869-0026', N'(503)869-0061', N' F', N'Y', 7, 1051710, 5323, 13939, NULL, NULL, NULL, NULL, NULL, N'7       ', N'N', N'PAYS FROM STMT              ', NULL, NULL, NULL, NULL, N'555642933       ', NULL, NULL, NULL, NULL, NULL, N'20131203@GD.GD                                              ', NULL, NULL, N'05')
INSERT [dbo].[patients] ([clinic_id], [location_id], [name_last], [name_first], [name_mid], [name_suffix], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [date_birth], [date_first_visit], [gender], [referral_source_id], [phone_1], [phone_2], [account_type], [acct_holder_yn], [provider_id], [patient_id], [unique_id], [plan_pri], [plan_sec], [plan_addl], [plan_med], [marital_status], [employment_status], [alerts], [deleted_patient_yn], [account_comment_1], [account_comment_2], [patient_comment_1], [patient_comment_2], [ss_num], [chart_number], [chart_number_1], [chart_number_2], [chart_number_3], [chart_number_4], [bill_supp_code], [email], [account_site], [relationship], [derived_location_id]) VALUES (501, N'05', N'PIERCE', N'ROBIN', NULL, NULL, N'3015 NEALS CT', NULL, N'VANCOUVER', N'WA', N'98661     ', CAST(0x0000583600000000 AS DateTime), CAST(0x00008E9000000000 AS DateTime), N'F', 7900000, N'(360)695-9506', NULL, N' F', N'Y', 677, 1903260, 93686, 32595, NULL, NULL, NULL, NULL, NULL, NULL, N'N', NULL, NULL, NULL, NULL, NULL, N'544902344       ', NULL, NULL, NULL, NULL, NULL, N'20140315@GD.GD                                              ', NULL, NULL, N'05')
INSERT [dbo].[patients] ([clinic_id], [location_id], [name_last], [name_first], [name_mid], [name_suffix], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [date_birth], [date_first_visit], [gender], [referral_source_id], [phone_1], [phone_2], [account_type], [acct_holder_yn], [provider_id], [patient_id], [unique_id], [plan_pri], [plan_sec], [plan_addl], [plan_med], [marital_status], [employment_status], [alerts], [deleted_patient_yn], [account_comment_1], [account_comment_2], [patient_comment_1], [patient_comment_2], [ss_num], [chart_number], [chart_number_1], [chart_number_2], [chart_number_3], [chart_number_4], [bill_supp_code], [email], [account_site], [relationship], [derived_location_id]) VALUES (501, N'05', N'SLOAN', N'RICHARD', NULL, NULL, N'19412 SE 42ND CIRCLE', N'BILL', N'CAMAS', N'WA', N'96807     ', CAST(0x00005E0F00000000 AS DateTime), CAST(0x0000A1F900000000 AS DateTime), N'M', 7900010, N'(503)998-2577', N'(503)285-4470', N' F', N'Y', 821, 1926500, 96400, 23552, NULL, NULL, NULL, NULL, NULL, NULL, N'N', NULL, NULL, NULL, NULL, NULL, N'567698300       ', NULL, NULL, NULL, NULL, NULL, N'20130712@GD.GD                                              ', NULL, NULL, N'05')
INSERT [dbo].[patients] ([clinic_id], [location_id], [name_last], [name_first], [name_mid], [name_suffix], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [date_birth], [date_first_visit], [gender], [referral_source_id], [phone_1], [phone_2], [account_type], [acct_holder_yn], [provider_id], [patient_id], [unique_id], [plan_pri], [plan_sec], [plan_addl], [plan_med], [marital_status], [employment_status], [alerts], [deleted_patient_yn], [account_comment_1], [account_comment_2], [patient_comment_1], [patient_comment_2], [ss_num], [chart_number], [chart_number_1], [chart_number_2], [chart_number_3], [chart_number_4], [bill_supp_code], [email], [account_site], [relationship], [derived_location_id]) VALUES (501, N'05', N'FEHLEN', N'DOUGLAS', NULL, NULL, N'16309 NE 76TH WAY', NULL, N'VANCOUVER', N'WA', N'98682     ', CAST(0x0000715E00000000 AS DateTime), CAST(0x00008F0A00000000 AS DateTime), N'M', 7900010, N'(360)606-1385', NULL, N' F', N'Y', 159, 1927320, 96479, 15297, NULL, NULL, NULL, NULL, NULL, NULL, N'N', NULL, NULL, NULL, NULL, NULL, N'543066814       ', NULL, NULL, NULL, NULL, NULL, N'20130402@GD.GD                                              ', NULL, NULL, N'05')
INSERT [dbo].[patients] ([clinic_id], [location_id], [name_last], [name_first], [name_mid], [name_suffix], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [date_birth], [date_first_visit], [gender], [referral_source_id], [phone_1], [phone_2], [account_type], [acct_holder_yn], [provider_id], [patient_id], [unique_id], [plan_pri], [plan_sec], [plan_addl], [plan_med], [marital_status], [employment_status], [alerts], [deleted_patient_yn], [account_comment_1], [account_comment_2], [patient_comment_1], [patient_comment_2], [ss_num], [chart_number], [chart_number_1], [chart_number_2], [chart_number_3], [chart_number_4], [bill_supp_code], [email], [account_site], [relationship], [derived_location_id]) VALUES (501, N'11', N'STACKHOUSE', N'COREY', NULL, NULL, N'13706 NE 88TH STREET', NULL, N'VANCOUVER', N'WA', N'98682     ', CAST(0x0000707B00000000 AS DateTime), CAST(0x000098DB00000000 AS DateTime), N'M', 7900150, N'(360)609-1890', N'(360)606-7031', N' F', N'Y', 865, 2609640, 203401, 24471, NULL, NULL, NULL, NULL, NULL, NULL, N'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'CLSTHEMAN@MSN.COM                                           ', NULL, NULL, N'11')
INSERT [dbo].[patients] ([clinic_id], [location_id], [name_last], [name_first], [name_mid], [name_suffix], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [date_birth], [date_first_visit], [gender], [referral_source_id], [phone_1], [phone_2], [account_type], [acct_holder_yn], [provider_id], [patient_id], [unique_id], [plan_pri], [plan_sec], [plan_addl], [plan_med], [marital_status], [employment_status], [alerts], [deleted_patient_yn], [account_comment_1], [account_comment_2], [patient_comment_1], [patient_comment_2], [ss_num], [chart_number], [chart_number_1], [chart_number_2], [chart_number_3], [chart_number_4], [bill_supp_code], [email], [account_site], [relationship], [derived_location_id]) VALUES (501, N'05', N'VALADEZ', N'NATALYA', NULL, NULL, N'2814 FALK RD', N'APT D', N'VANCOUVER', N'WA', N'98661     ', CAST(0x00006F7D00000000 AS DateTime), CAST(0x0000A30600000000 AS DateTime), N'F', 7900000, N'(360)910-5231', NULL, N' F', N'N', 821, 3477201, 407427, 32595, NULL, NULL, NULL, NULL, NULL, NULL, N'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'20140402@GD.GD                                              ', NULL, N'W', N'05')
INSERT [dbo].[patients] ([clinic_id], [location_id], [name_last], [name_first], [name_mid], [name_suffix], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [date_birth], [date_first_visit], [gender], [referral_source_id], [phone_1], [phone_2], [account_type], [acct_holder_yn], [provider_id], [patient_id], [unique_id], [plan_pri], [plan_sec], [plan_addl], [plan_med], [marital_status], [employment_status], [alerts], [deleted_patient_yn], [account_comment_1], [account_comment_2], [patient_comment_1], [patient_comment_2], [ss_num], [chart_number], [chart_number_1], [chart_number_2], [chart_number_3], [chart_number_4], [bill_supp_code], [email], [account_site], [relationship], [derived_location_id]) VALUES (501, N'05', N'SPEROPULOS', N'HARRY', NULL, NULL, N'2801 NW 219TH ST', NULL, N'RIDGEFIELD', N'WA', N'98642     ', CAST(0x0000470000000000 AS DateTime), CAST(0x0000A3FE00000000 AS DateTime), N'M', 3605230, N'(503)713-7110', NULL, N' F', N'N', 821, 3605231, 429678, 35664, NULL, NULL, NULL, NULL, NULL, NULL, N'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'HNSPEROPULOS@BPA.GOV                                        ', NULL, N'H', N'05')
INSERT [dbo].[patients] ([clinic_id], [location_id], [name_last], [name_first], [name_mid], [name_suffix], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [date_birth], [date_first_visit], [gender], [referral_source_id], [phone_1], [phone_2], [account_type], [acct_holder_yn], [provider_id], [patient_id], [unique_id], [plan_pri], [plan_sec], [plan_addl], [plan_med], [marital_status], [employment_status], [alerts], [deleted_patient_yn], [account_comment_1], [account_comment_2], [patient_comment_1], [patient_comment_2], [ss_num], [chart_number], [chart_number_1], [chart_number_2], [chart_number_3], [chart_number_4], [bill_supp_code], [email], [account_site], [relationship], [derived_location_id]) VALUES (501, N'05', N'GRANDE-PALEN', N'JACQUELINE', NULL, NULL, N'502 NW 77TH', NULL, N'VANCOUVER', N'WA', N'98665     ', CAST(0x00006A0900000000 AS DateTime), CAST(0x0000A42000000000 AS DateTime), N'F', 7900040, N'(360)281-4350', N'(360)721-5234', N' F', N'Y', 159, 3623490, 433060, 38775, NULL, NULL, NULL, NULL, NULL, NULL, N'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'JGRANDEPALEN@YAHOO.COM                                      ', NULL, NULL, N'05')
INSERT [dbo].[patients] ([clinic_id], [location_id], [name_last], [name_first], [name_mid], [name_suffix], [addr_street], [addr_other], [addr_city], [addr_state], [addr_zip], [date_birth], [date_first_visit], [gender], [referral_source_id], [phone_1], [phone_2], [account_type], [acct_holder_yn], [provider_id], [patient_id], [unique_id], [plan_pri], [plan_sec], [plan_addl], [plan_med], [marital_status], [employment_status], [alerts], [deleted_patient_yn], [account_comment_1], [account_comment_2], [patient_comment_1], [patient_comment_2], [ss_num], [chart_number], [chart_number_1], [chart_number_2], [chart_number_3], [chart_number_4], [bill_supp_code], [email], [account_site], [relationship], [derived_location_id]) VALUES (501, N'05', N'LINES', N'ROMAN', NULL, NULL, N'711 W 38TH ST', NULL, N'VANCOUVER', N'WA', N'98660     ', CAST(0x0000795100000000 AS DateTime), CAST(0x0000A44A00000000 AS DateTime), N'M', 7900000, N'(360)936-9090', NULL, N' F', N'N', 821, 3567921, 437088, 32593, NULL, NULL, NULL, NULL, NULL, NULL, N'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'H', N'05')
/****** Object:  Table [dbo].[patient_transactions]    Script Date: 03/19/2015 12:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[patient_transactions](
	[clinic_id] [smallint] NOT NULL,
	[location_id] [char](2) NULL,
	[date_posted] [datetime] NOT NULL,
	[provider_id] [smallint] NOT NULL,
	[trans_account_type] [char](2) NULL,
	[trans_code] [smallint] NULL,
	[category_id] [char](2) NULL,
	[tr_id] [int] NOT NULL,
	[date_year] [smallint] NULL,
	[date_month] [smallint] NULL,
	[date_day] [smallint] NULL,
	[patient_id] [int] NOT NULL,
	[account_id] [int] NOT NULL,
	[unique_id] [int] NOT NULL,
	[charge_ucf] [numeric](10, 2) NULL,
	[amount_pat] [numeric](10, 2) NULL,
	[amount_pri] [numeric](10, 2) NULL,
	[amount_sec] [numeric](10, 2) NULL,
	[amount_tot] [numeric](10, 2) NULL,
	[remain_pat] [numeric](10, 2) NULL,
	[remain_pri] [numeric](10, 2) NULL,
	[remain_sec] [numeric](10, 2) NULL,
	[remain_tot] [numeric](10, 2) NULL,
	[trans_type] [char](1) NULL,
	[charge_yn] [char](1) NULL,
	[payment_yn] [char](1) NULL,
	[adjustment_yn] [char](1) NULL,
	[pay_adj_type] [char](5) NULL,
	[tooth] [char](4) NULL,
	[surface] [char](6) NULL,
	[plan_pri] [int] NULL,
	[plan_sec] [int] NULL,
	[employee] [smallint] NULL,
	[claim_number_pri] [int] NULL,
	[claim_number_sec] [int] NULL,
	[claim_status_pri] [char](1) NULL,
	[claim_status_sec] [char](1) NULL,
	[modifier1] [char](2) NULL,
	[modifier2] [char](2) NULL,
	[accept_assign_yn] [char](1) NULL,
	[referral_source_id] [int] NULL,
	[assistant_id] [smallint] NULL,
	[date_of_service] [datetime] NULL,
	[claim_number] [int] NULL,
	[amount_other] [numeric](10, 2) NULL,
	[last_updated] [datetime] NULL,
	[units] [numeric](4, 2) NULL,
	[description] [char](30) NULL,
	[bank_number] [char](12) NULL,
	[step_code] [char](2) NULL,
	[purpose_code] [char](2) NULL,
	[ins_rec_number_pri] [smallint] NULL,
	[ins_rec_number_sec] [smallint] NULL,
	[anes_start_time] [smallint] NULL,
	[anes_end_time] [smallint] NULL,
	[anes_base_units] [smallint] NULL,
	[anes_modifier_units] [smallint] NULL,
	[anes_time_units] [smallint] NULL,
	[anes_time_minutes] [smallint] NULL,
	[office] [smallint] NULL,
	[date_exported] [datetime] NULL,
	[date_created] [datetime] NULL,
	[user_id_created] [char](7) NULL,
	[derived_location_id] [char](2) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND name = N'indx_account_id')
CREATE NONCLUSTERED INDEX [indx_account_id] ON [dbo].[patient_transactions] 
(
	[clinic_id] ASC,
	[account_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, FILLFACTOR = 70) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND name = N'indx_bank_number')
CREATE NONCLUSTERED INDEX [indx_bank_number] ON [dbo].[patient_transactions] 
(
	[bank_number] 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  = OFF, FILLFACTOR = 70) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND name = N'indx_clinic_id_date_year_date_month_date_day_patient_id_payment_yn_charge_yn')
CREATE NONCLUSTERED INDEX [indx_clinic_id_date_year_date_month_date_day_patient_id_payment_yn_charge_yn] ON [dbo].[patient_transactions] 
(
	[clinic_id] ASC,
	[date_year] ASC,
	[date_month] ASC,
	[date_day] ASC,
	[patient_id] ASC,
	[payment_yn] ASC,
	[charge_yn] 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, FILLFACTOR = 70) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND name = N'indx_clinic_id_derived_location_id')
CREATE NONCLUSTERED INDEX [indx_clinic_id_derived_location_id] ON [dbo].[patient_transactions] 
(
	[clinic_id] ASC,
	[derived_location_id] ASC,
	[date_posted] ASC
)
INCLUDE ( [provider_id],
[unique_id],
[trans_code],
[tr_id],
[account_id]) 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, FILLFACTOR = 70) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND name = N'indx_clinic_id_patient_id_provider_id_charge_yn')
CREATE NONCLUSTERED INDEX [indx_clinic_id_patient_id_provider_id_charge_yn] ON [dbo].[patient_transactions] 
(
	[clinic_id] ASC,
	[patient_id] ASC,
	[provider_id] ASC,
	[charge_yn] 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  = OFF, FILLFACTOR = 70) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND name = N'indx_clinic_id_provider_id')
CREATE NONCLUSTERED INDEX [indx_clinic_id_provider_id] ON [dbo].[patient_transactions] 
(
	[clinic_id] ASC,
	[provider_id] ASC,
	[charge_yn] ASC,
	[date_year] ASC,
	[date_month] ASC,
	[patient_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, FILLFACTOR = 70) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND name = N'indx_date_posted')
CREATE NONCLUSTERED INDEX [indx_date_posted] ON [dbo].[patient_transactions] 
(
	[date_posted] ASC,
	[clinic_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, FILLFACTOR = 70) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND name = N'indx_location_id')
CREATE NONCLUSTERED INDEX [indx_location_id] ON [dbo].[patient_transactions] 
(
	[clinic_id] ASC,
	[location_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, FILLFACTOR = 70) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND name = N'indx_patient_id')
CREATE NONCLUSTERED INDEX [indx_patient_id] ON [dbo].[patient_transactions] 
(
	[clinic_id] ASC,
	[patient_id] ASC,
	[date_posted] 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, FILLFACTOR = 70) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND name = N'indx_trans_code_date_posted')
CREATE NONCLUSTERED INDEX [indx_trans_code_date_posted] ON [dbo].[patient_transactions] 
(
	[trans_code] ASC,
	[date_posted] ASC
)
INCLUDE ( [clinic_id],
[date_year],
[date_month],
[patient_id],
[unique_id],
[amount_tot],
[derived_location_id]) 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, FILLFACTOR = 70) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[patient_transactions]') AND name = N'IX_Clinic_idUnique_id')
CREATE NONCLUSTERED INDEX [IX_Clinic_idUnique_id] ON [dbo].[patient_transactions] 
(
	[clinic_id] ASC,
	[unique_id] ASC,
	[derived_location_id] ASC
)
INCLUDE ( [trans_code]) 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, FILLFACTOR = 80) ON [PRIMARY]
GO
Free Windows Admin Tool Kit Click here and download it now
March 19th, 2015 3:29am
This topic is archived. No further replies will be accepted.