InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 15:55:39 GMT20http://www.sqlservercentral.com/Forums/Topic1453813-65-1.aspxI have been asked to look a a sql 2000 database that has been neglected for some time.It has an audit database linked to the system and no maintenance has been done on this database on over three years,The database has grown to around 322GbI have started doing some maintenance on the table and deleted over 9million entries for just one months worth of auditing data for back in 2010 and I will need to continue doing this on a nightly basis until we only have around 6 month worth of audit data left.Since I am deleting so much data, from the database, I thought it might be a good idea to shrink the database. I started shrinking the database and left it to run. The next day back at work it was still running and I had complaints from users using the system. I was reluctant to stop the shrink as I felt it woild stop soon. It continued to run and the next day before work started I decided to cancel the shrink.Any suggestions on how to get this done more efficiently?Thu, 16 May 2013 17:19:23 GMTterry.homehttp://www.sqlservercentral.com/Forums/Topic1440027-65-1.aspxJust fair warning, I am definitely a newbie, I fell into this work so to speak.I have a query that is taking between 30 seconds to 30 minutes depending on how much data is being run at the current time. I used SQL Server Management Studio Express to analyze the query and show me the execution plan with statistics ON.I have since discovered where the problem is. I have one table in my database where we collect information, this information is updated daily. In fact it's not really updated, it's brand new data everyday collected from various websites.Because this table collects brand new data daily it is not indexed, it's a HEAP. There are between 4-6 million rows of data on any given day depending on how much data is run that day. We clear all records older then 3 days from this table every night.Now that I've explained the table a little, here's where the problem comes in: (This is not the entire stored procedure just the part that takes 98% operator cost time)--------------------------------------------------------------------- GET THE RATES IN TEMP TABLE-------------------------------------------------------------------SELECT u.*, NULL AS RateTypeID, NULL AS RoomTypeID, NULL AS RatePriority, NULL AS RoomPriorityINTO #RESULTSFROM TFX_V2_UnfilteredRates uWHERE SiteID IN ( SELECT * FROM #SITES )AND HotelID IN ( SELECT * FROM #HOTELS )AND timeInserted > @JobDateDROP TABLE #SITESDROP TABLE #HOTELS----------------------------------------------It takes far too long to scan this table for the appropriate rates. Any suggestions/ideas on how to make this process run more efficiently would greatly be appreciated. I've thought about creating an index for the TFX_v2_unfiltedrates table which is the one that is 5million rows give or take. However I am worried that it would slow down my inserts on that table, since we need to insert data very quickly.If you need more info please let me know, however I was trying to prevent from pasting the entire stored procedure.Thanks-JoshMon, 08 Apr 2013 14:48:27 GMTjrestucciohttp://www.sqlservercentral.com/Forums/Topic1439548-65-1.aspxI am having sql server 2008 r2 server. I saw that yesterday the server so slow. When i see performance counters some of the values are so high . So how to troubleshoot sql server performance according to the data below.I have taken the average value and maximum value of each counter for a day.[img]http://incometaxsoft.com/sqlperformance-1.jpg[/img]Sat, 06 Apr 2013 01:25:24 GMT IT researcherhttp://www.sqlservercentral.com/Forums/Topic1422193-65-1.aspxHi All,I have a table in SQL Server 2000 that cannot be queried. Even a simple "select count(*) from dbo_tblAllocations" runs with no end. I've let this query run for 12 minutes with no results returned.Here's the table:CREATE TABLE [dbo].[dbo_tblAllocations] ( [IngAllocationsID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [Serial_Nbr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [IngAccountID] [int] NULL , [ACCT] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Invoice_Nbr] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Invoice_Date] [smalldatetime] NULL , [Process_Date] [smalldatetime] NULL , [Records] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [IngSubAccountID] [int] NULL , [SUB_ACCT] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [BTN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [IngVendorID] [int] NOT NULL , [Vendor] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [IngLocationsID] [int] NULL , [IngNumbersID] [int] NULL , [DDN] [nvarchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TYPE] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DESCRIPTION] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UNIT] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Per_Line_Chg] [float] NULL , [Line_Chg] [float] NULL , [CDR_Chg] [float] NULL , [Acct_Per_Line_Chg] [float] NULL , [Inv_Total_Chg] [float] NULL , [MNTHLY] [float] NULL , [OTC] [float] NULL , [IBLD] [float] NULL , [OBLD] [int] NULL , [OUC] [float] NULL , [TAX] [float] NULL , [LATE] [float] NULL , [ADJ] [float] NULL , [CM] [int] NULL , [UM] [int] NULL , [FM] [int] NULL ) ON [PRIMARY]Here's the indexes on the table:index_name index_description index_keys============================================================================ IX_dbo_tblAllocations nonclustered, unique, unique key located on PRIMARY Serial_NbrPK_dbo_tblAllocations clustered, unique, primary key located on PRIMARY IngAllocationsIDHere's what I get when I run sp_spaceused on the table:name rows reserved data index_size unused============================================================================dbo_tblAllocations 129669 120112 KB 54432 KB 5624 KB 60056 KBDoes anyone know what could be causing this and how to fix it? From what I can tell it's just this one table and the others look fine. Thanks,MarkWed, 20 Feb 2013 10:20:16 GMTMark Eckeardhttp://www.sqlservercentral.com/Forums/Topic1418763-65-1.aspxHi, I have 81 columns in my table. and record count=6690237. clustered index is been applied on PK. when i execute below select statement it is taking 3 min to process.select distinct divCode ,convert(varchar(4),glPeriodYear) + '/' + right('00' + convert(varchar(2),glPeriodMonth),2) + '/' + '01' as glPeriod from tblGLJnlEntrySum where divCode = @vsDivCode order by divCode,glPeriod desc Please tell me how i can speed up my query. need some suggestion on performance tuning.Mon, 11 Feb 2013 23:47:10 GMTp.panneerselvamhttp://www.sqlservercentral.com/Forums/Topic1414713-65-1.aspxWe have configured transactional replication on the database. Is there a way without drop the replication to restore the existing database ?Fri, 01 Feb 2013 08:44:27 GMTbkamarajhttp://www.sqlservercentral.com/Forums/Topic1395553-65-1.aspxHi All,Please give your valuable suggestions on how can i improve the performance of tables which having 2 crores of data.ThanksBhanuWed, 12 Dec 2012 03:29:31 GMTkbhanu15http://www.sqlservercentral.com/Forums/Topic1407665-65-1.aspxhi there,We currently have an issue where on one of our SQL Servers, there is a high number of SQL Compilations\sec. There was no new code promoted to the database.Does anyone perhaps know what we should do to investigate what the cause was for these compilations or any other way we can check why these compilations are happening on the server?RegardsICWed, 16 Jan 2013 01:26:26 GMTImke Cronjehttp://www.sqlservercentral.com/Forums/Topic1409427-65-1.aspxHow to Query Tune The Stored procedure.. Any helpful document..Where to starthow to do..ThanksMon, 21 Jan 2013 02:10:22 GMTm.rajesh.ukhttp://www.sqlservercentral.com/Forums/Topic19087-65-1.aspxHi I'm doing some performance monitoring and was wondering if someone could shed some light on this counter? In particular what is considered low and what is considered high?Or what is the maximum sql server can handle?Thanks Wed, 10 Dec 2003 15:32:00 GMTgrowlhttp://www.sqlservercentral.com/Forums/Topic1394734-65-1.aspxI created a table with a single varchar(10) column and ran a select statement against it. In Profiler, it shows 25 reads but in SSMS using Statistics IO, it shows only 1 logical read. What is the difference between the two values? Also, I did the same thing against another SQL 2005 server and the reads in Profiler show only 3 and I'm trying to figure out why one shows 3 and one shows 25 for such a simple statement. The table has no triggers, indexes.Thanks.Mon, 10 Dec 2012 11:56:28 GMTSteve-443559http://www.sqlservercentral.com/Forums/Topic1392338-65-1.aspxHi,I have the following query that I'm using to extract some demo report data.[b]SELECT HOURS.EMPID, HOURS.WORKDATE, HOURS.MINS, CODES.CORRCODEID, CODES.CORRCODE, CODES.NAME AS [CORRCODE NAME], CODES.CWTYPE, DEFAULT_STORE.[STORE NAME] AS [DEFAULT STORE NAME], DEFAULT_STORE.[STORE ID] AS [DEFAULT STORE ID], DEFAULT_STORE.[STORE SQUARE FOOTAGE] AS [DEFAULT STORE SQUARE FOOTAGE], ACTUAL_STORE.[STORE NAME] AS [ACTUAL STORE NAME], ACTUAL_STORE.[STORE ID] AS [ACTUAL STORE ID], ACTUAL_STORE.[STORE SQUARE FOOTAGE] AS [ACTUAL STORE SQUARE FOOTAGE], WORKED_DEPT_REC.PARENTID [WORKED_DEPT_PARENTID], WORKED_DEPT_REC.NAME [WORKED_DEPT_NAME], WORKED_DEPT_REC.COSTCENTRE [WORKED_DEPT_COSTCENTRE], WORKED_DEPT_REC.DEPTID [WORKED_DEPT_DEPTID], DEFAULT_DEPT_REC.PARENTID [DEFAULT_DEPT_PARENTID], DEFAULT_DEPT_REC.NAME [DEFAULTDEPT_DEPT_NAME], DEFAULT_DEPT_REC.COSTCENTRE [DEFAULT_DEPT_COSTCENTRE], DEFAULT_DEPT_REC.DEPTID [DEFAULT_DEPT_DEPTID], DEFAULT_SHIFT.SHIFTID [DEFAULT_SHIFT_SHIFTID], DEFAULT_SHIFT.TARGET [DEFAULT_SHIFT_TARGET_MINS], DEFAULT_SHIFT.NAME [DEFAULT_SHIFT_NAME], ACTUAL_SHIFT.SHIFTID [ACTUAL_SHIFT_SHIFTID], ACTUAL_SHIFT.TARGET [ACTUAL_SHIFT_TARGET_MINS], ACTUAL_SHIFT.NAME [ACTUAL_SHIFT_NAME]--INTO-- REPORT_DATAFROM CORRECTI HOURS-- GET THE CORRECTIONS CODE DETAILSINNER JOIN CORRCODE CODESON HOURS.CORRCODE = CODES.CORRCODEID-- JOIN ON THE EMPLOYEE RECORDINNER JOIN EMPLOYEE EON HOURS.EMPID = E.EMPID-- GET THE WORKED DEPT RECORD FROM COMPANY STRUCTURE TABLELEFT OUTER JOIN COMPSTRU WORKED_DEPT_RECON WORKED_DEPT_REC.DEPTID = ISNULL(HOURS.DEPTID, E.DEPTID) -- GET THE DEFAULT DEPT ID FROM THE EMPLOYEE HISTORYINNER JOIN EMP_HIST DEFAULT_DEPT ON DEFAULT_DEPT.FINDEX = '-z' AND DEFAULT_DEPT.EMPID = HOURS.EMPID AND HOURS.WORKDATE BETWEEN DEFAULT_DEPT.EFFECTIVE AND DEFAULT_DEPT.EFFECTIVEEND-- GET THE DEFAULT DEPT RECORD FROM COMPANY STRUCTURE TABLELEFT OUTER JOIN COMPSTRU DEFAULT_DEPT_RECON DEFAULT_DEPT_REC.DEPTID = ISNULL(DEFAULT_DEPT.NEWVALUE, E.DEPTID) -- GET THE ROSTER ID FROM THE EMPLOYEE HISTORYINNER JOIN EMP_HIST DEFAULT_ROSTER ON DEFAULT_ROSTER.FINDEX = '-x' AND DEFAULT_ROSTER.EMPID = HOURS.EMPID AND HOURS.WORKDATE BETWEEN DEFAULT_ROSTER.EFFECTIVE AND DEFAULT_ROSTER.EFFECTIVEEND-- GET THE DEFAULT SHIFT ID FROM ROSTDAYSLEFT OUTER JOIN ROSTDAYS RDON DEFAULT_ROSTER.NEWVALUE = ISNULL(RD.ROSTERID, E.CLK_ROSTER) AND RD.WORKDATE = HOURS.WORKDATELEFT OUTER JOIN SHIFTS DEFAULT_SHIFTON DEFAULT_SHIFT.SHIFTID = RD.SHIFTID -- GET THE ACTUAL SHIFT RECORD FROM BASIC LEFT OUTER JOIN BASIC BON B.WORKDATE = HOURS.WORKDATE AND B.EMPID = HOURS.EMPID AND B.SUBALLOC = '0'LEFT OUTER JOIN SHIFTS ACTUAL_SHIFTON ACTUAL_SHIFT.SHIFTID = ISNULL(B.SHIFTID, RD.SHIFTID)LEFT OUTER JOIN( SELECT STORE.NAME [STORE NAME], STORE.DEPTID [STORE ID], STORE.OTHER [STORE SQUARE FOOTAGE], DEPTS.DEPTID FROM COMPSTRU STORE OUTER APPLY dbo.Tesco_udf_Dept_Tree(STORE.DEPTID,1) AS DEPTS WHERE STORE.PARENTID = '!#' ) DEFAULT_STOREON DEFAULT_DEPT_REC.DEPTID = DEFAULT_STORE.DEPTID LEFT OUTER JOIN( SELECT STORE.NAME [STORE NAME], STORE.DEPTID [STORE ID], STORE.OTHER [STORE SQUARE FOOTAGE], DEPTS.DEPTID FROM COMPSTRU STORE OUTER APPLY dbo.Tesco_udf_Dept_Tree(STORE.DEPTID,1) AS DEPTS WHERE STORE.PARENTID = '!#' ) ACTUAL_STOREON WORKED_DEPT_REC.DEPTID = ACTUAL_STORE.DEPTID[/b]It runs quickly enough for my needs. It's just that when I add the following where clause:[b](WHERE DEFAULT_STORE.[STORE ID] <> ACTUAL_STORE.[STORE ID])[/b]the query ends up going from taking 18 seconds to run to failing after an hour with the tempdb expanding to 50GB and running out of disk space. Can someone enlighten me as to why the where clause has such an effect as I'm only filtering on columns already included in the query without the where clause? Also, the actual execution plan changes significantly with the addition of the WHERE.ThanksMickTue, 04 Dec 2012 03:52:05 GMTmickquinlanhttp://www.sqlservercentral.com/Forums/Topic1382528-65-1.aspxI need to copy more than a 100 large tables on a daily basis from Informix to SQL Server.It seems like that on the Informix side most fields are nchar. With a few exceptions these are implicitly casted as varchar on the SQL Server side (that's how the data is stored). We use linked server/openquery.The copying takes forever even though we managed to improve the performance by a factor 20 or so.Now what I was considering was to Cast the nchar columns to varchar in the openquery statement, so make Informix doing the explicit conversion and [b]resulting in a higher effective throughput[/b] (based on the thought varchar consumes less space than nchar). The linked in Server is making use of a ODBC connection.I thought the syntax has to be something like:...SELECT * FROM OPENQUERY(xxx, 'Select ...CAST([invoice_no] AS VARCHAR(50)) invoice_no... from xxx.invoice')where invoice_no is nchar(50)Problem is that I cannot access the system at the moment so I cannot test this out myself.Does it make any sense what I'm doing? Would this actually result in a better performance?Is the syntax correct?HenkThu, 08 Nov 2012 08:24:44 GMTHenk de Whttp://www.sqlservercentral.com/Forums/Topic1380868-65-1.aspxI found out that it seems flush from memory very soon make it unable to use the cache data, results in slow to getting results.1) How long will it cache the data? 2) Any setting can be set for the interval of caching?3)Any hot fix /patches in SQL2012 to solve this issue?Sun, 04 Nov 2012 21:30:57 GMTangel.wong 72408http://www.sqlservercentral.com/Forums/Topic1096678-65-1.aspxHi,Can anyone please let me know the query to rebuild all the indexes in an SQL Server 2000 Database??I need to rebuild all the indexes in database at a time using single query.Thanks in advance,Vamshi.Wed, 20 Apr 2011 16:26:11 GMTvamshi.sqlhttp://www.sqlservercentral.com/Forums/Topic1358529-65-1.aspxIs there a best practice for creating index to reduce the IO for inserted scans that originate from a trigger? I have a table with roughly 2 million rows that has an insert and an update trigger that fires every time a new row is inserted or when an existing row is updated. From the estimated execution plan, the EstimatedIO for these scans is ~64. Currently, the table only has a clustered primary key index. Is there a specific way to approach index for inserted scans to reduce the overall IO of the process?**edit**My only thought right now is to read the inserted row into a temp table, and then create a clustered index on the primary key of the temp table. The thinking is that the trigger is making subsequent calls for the inserted row, which causes a table scan to retrieve the data. Anything behind this logic?Thu, 13 Sep 2012 07:05:58 GMTcr259http://www.sqlservercentral.com/Forums/Topic1349399-65-1.aspxHi All,I would like to improve the performance of view created referencing table on a different server using OPENROWSETI’ve created a view say, ViewA in ServerA that referances TableB in ServerB.CREATE VIEW [dbo].[ViewA]ASSELECT Col1,Col2,Col3 FROM OPENROWSET('SQLOLEDB','ServerB';'Login';'Pass', 'SELECT Col1,Col2,Col3 FROM DB.dbo.TableA ')The view is running slow so inorder to improve performance I tried to create an index and ended up with the below error, saying cannot create index without SCHEMABINDINGCREATE UNIQUE CLUSTERED INDEX IX_ViewA ON [dbo].[ViewA] (Col1)Error:Msg 1939, Level 16, State 1, Line 1Cannot create index on view 'ViewA' because the view is not schema bound.When I try to create schema binding using ‘WITH SCHEMABINDING’ getting below errorError:Msg 1054, Level 15, State 3, Procedure vPRSummaryWBSList, Line 23Syntax 'Openrowset/Openquery/Opendatasource' is not allowed in schema-bound objects. Now my questions how can I improve the performance of the view created referencing table on a different server using OPENROWSET if I cannot have SCHEMABINDING which is required for INDEX?I really appreciate if someone could assist in improving the performance of this viewThanks in advance!Thu, 23 Aug 2012 17:45:41 GMTsql server developerhttp://www.sqlservercentral.com/Forums/Topic522523-65-1.aspxHi AllDoes anybody know if and how I can get SQL Profiler to start automatically on a test server but to log the events of a production server ?From the test server, I created & scripted the trace, connecting to the production server. Then placed the scripted trace into a stored proc on the test server and created a SQL job to run the stored proc. The only problem is that it logs the activity of the test server.Thanks in advance.PreetTue, 24 Jun 2008 07:17:41 GMTPreet_Shttp://www.sqlservercentral.com/Forums/Topic1339375-65-1.aspxHi all,I'm having some troubles working on this requirement, because the last time I tried to run this query it ran for 9 hours. I know it can be improved, but I'm limited using a SQL Server 2000 SP4 server.Could you give me some ideas on how to attack this problem? I'm not really asking for a complete solution, but any ideas will help.First of all, I have a view based on 2 views based on one table each. Every stage is on a different DB. Here's the DDL for them.[code="sql"]-- ========================================================--Original Table with indexes-- ========================================================CREATE TABLE [dbo].[CLMTRK]( [CUSNO] [char](5) NOT NULL, [PATNUM] [numeric](6, 0) NOT NULL, [INVNO] [char](8) NOT NULL, [LNENO] [numeric](3, 0) NOT NULL, [EVNTDATE] [numeric](8, 0) NOT NULL, [EVNTTIME] [numeric](6, 0) NOT NULL, [PAYORPOS] [numeric](2, 0) NOT NULL, [EVNTTYP] [numeric](2, 0) NOT NULL, [INSNO] [char](3) NOT NULL, [PATCVG_SEQ] [numeric](4, 0) NOT NULL, [INSFORM] [numeric](3, 0) NOT NULL, [BRANCH] [char](3) NOT NULL, [TERR] [char](3) NOT NULL, [USERNAME] [varchar](20) NOT NULL, [BATCHMODE] [numeric](1, 0) NOT NULL, [ROLLOVER] [numeric](1, 0) NOT NULL, [PROCDE] [varchar](12) NOT NULL, [PROCDEMOD] [varchar](4) NOT NULL, [SUPP] [char](3) NOT NULL, [PART] [varchar](25) NOT NULL, [LOC] [char](3) NOT NULL, [DESCR] [varchar](32) NOT NULL, [GASFLG] [numeric](1, 0) NOT NULL, [UNIT] [varchar](5) NOT NULL, [STDOS] [numeric](8, 0) NOT NULL, [ENDOS] [numeric](8, 0) NOT NULL, [ALLINS1] [char](3) NOT NULL, [ALLINS2] [char](3) NOT NULL, [ALLINS3] [char](3) NOT NULL, [INSASSN] [numeric](1, 0) NOT NULL, [BILAMT] [numeric](8, 2) NOT NULL, [AFACT] [numeric](1, 0) NOT NULL, [ALLOWABLE] [numeric](15, 6) NOT NULL, [PFACT] [numeric](1, 0) NOT NULL, [PRICE] [numeric](15, 6) NOT NULL, [QTYDEC] [numeric](1, 0) NOT NULL, [QTYSH] [numeric](12, 3) NOT NULL, [PRTQTY] [numeric](12, 3) NOT NULL, [CUFDEC] [numeric](1, 0) NOT NULL, [CUFT] [numeric](12, 3) NOT NULL, [CMNSENT] [numeric](1, 0) NOT NULL, [AUTHSENT] [numeric](1, 0) NOT NULL, [NARSENT] [numeric](1, 0) NOT NULL, [AUTHNO] [varchar](20) NOT NULL, [PRIORAUTHNO] [varchar](20) NOT NULL, [MODIFIER1] [varchar](4) NOT NULL, [MODIFIER2] [varchar](4) NOT NULL, [MODIFIER3] [varchar](4) NOT NULL, [MODIFIER4] [varchar](4) NOT NULL, [DIGCDE1] [varchar](6) NOT NULL, [DIGCDE2] [varchar](6) NOT NULL, [DIGCDE3] [varchar](6) NOT NULL, [DIGCDE4] [varchar](6) NOT NULL, [PHYNO] [varchar](4) NOT NULL, [PRNTWERR] [numeric](1, 0) NOT NULL, [CHCLMTYPE] [numeric](6, 0) NOT NULL, [CHPLNTYPE] [numeric](6, 0) NOT NULL, [CHINSPROVID] [varchar](15) NOT NULL, [DISCOUNT] [numeric](2, 2) NOT NULL, [PCN] [varchar](12) NULL, [CLEARINGHOUSEPROCESSDATE] [datetime] NULL)GOCREATE CLUSTERED INDEX [clmtrk_primary] ON [dbo].[CLMTRK] ( [CUSNO] ASC, [PATNUM] ASC, [INVNO] ASC, [LNENO] ASC, [EVNTDATE] ASC, [EVNTTIME] ASC, [PAYORPOS] ASC, [EVNTTYP] 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]GOCREATE NONCLUSTERED INDEX [IX_CLMTRK] ON [dbo].[CLMTRK] ( [SUPP] 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]GOCREATE NONCLUSTERED INDEX [IX_CLMTRK_1] ON [dbo].[CLMTRK] ( [PHYNO] 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]GOCREATE NONCLUSTERED INDEX [IX_CLMTRK_2] ON [dbo].[CLMTRK] ( [PCN] 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-- ========================================================-- First view-- ========================================================CREATE view [dbo].[tblCLMTRK] ( ALW_AMT_CT, ALW_PF_CT, ASSIGN_CT, AUTHCLM_CT, AUTHNUM_CT, BATCHMODE_CT, BILAMT_CT, BRANCH_CT, CHCATGRY_CT, CHPLNTYP_CT, CHPRVDR_CT, CMNSNT_CT, CMP_EVNTDT_CT, CMP_EVNTTM_CT, CUSTNUM_CT, DECQTY_CT, DECVOL_CT, DESCRIPTION_CT, DIAGCD001_CT, DIAGCD002_CT, DIAGCD003_CT, DIAGCD004_CT, DISCPCT_CT, ENDDOS_CT, EVNT_DT_CT, EVNT_TM_CT, EVNT_TYP_CT, GAS_CT, INS001_CT, INS002_CT, INS003_CT, INSFRM_CT, INSNUN_CT, INVNUM_CT, ITEMNUM_CT, LNNUM_CT, LOC_CT, MODIFIER001_CT, MODIFIER002_CT, MODIFIER003_CT, MODIFIER004_CT, NARSNT_CT, PATNUM_CT, PAYOR_CT, PF_CT, PHYCD_CT, PRIORAUTH_CT, PRNTERR_CT, PROCDEMOD_CT, PROCDE_CT, PRT_QTY_CT, QTY_CT, ROLLOVR_CT, STDOS_CT, SUP_CT, TER_CT, UNIT_PRICE_CT, UOM_CT, USERNAME_CT, VOL_CT)asselect ctrk.allowable ALW_AMT_CT, --??this figure looks like the raw amount (no pfact translation) in datawarehouse ctrk.afact ALW_PF_CT, ctrk.insassn ASSIGN_CT, ctrk.authsent AUTHCLM_CT, ctrk.authno AUTHNUM_CT, ctrk.batchmode BATCHMODE_CT, ctrk.bilamt BILAMT_CT, --??explicit decimals convert(int,ctrk.branch) BRANCH_CT, ctrk.chclmtype CHCATGRY_CT, ctrk.chplntype CHPLNTYP_CT, ctrk.chinsprovid CHPRVDR_CT, ctrk.cmnsent CMNSNT_CT, '00' CMP_EVNTDT_CT, ctrk.evnttime CMP_EVNTTM_CT, ctrk.cusno CUSTNUM_CT, ctrk.qtydec DECQTY_CT, ctrk.cufdec DECVOL_CT, ctrk.descr DESCRIPTION_CT, ctrk.digcde1 DIAGCD001_CT, ctrk.digcde2 DIAGCD002_CT, ctrk.digcde3 DIAGCD003_CT, ctrk.digcde4 DIAGCD004_CT, convert(int,ctrk.discount * 100) DISCPCT_CT, case when isdate(ctrk.endos) = 1 then convert(datetime,cast(ctrk.endos as char(8)),112) else '1/1/1800' end ENDDOS_CT, case when isdate(ctrk.evntdate) = 1 then convert(datetime,cast(ctrk.evntdate as char(8)),112) else '1/1/1800' end EVNT_DT_CT, --actual date ctrk.evnttime EVNT_TM_CT, --actual time HHMMSS right('00',2-len(ctrk.evnttyp)) + convert(varchar(2),ctrk.evnttyp) EVNT_TYP_CT, ctrk.gasflg GAS_CT, ctrk.allins1 INS001_CT, ctrk.allins2 INS002_CT, ctrk.allins3 INS003_CT, ctrk.insform INSFRM_CT, ctrk.insno INSNUN_CT, convert(int, ctrk.invno) INVNUM_CT, ctrk.part ITEMNUM_CT, ctrk.lneno LNNUM_CT, ctrk.loc LOC_CT, ctrk.modifier1 MODIFIER001_CT, ctrk.modifier2 MODIFIER002_CT, ctrk.modifier3 MODIFIER003_CT, ctrk.modifier4 MODIFIER004_CT, ctrk.narsent NARSNT_CT, ctrk.patnum PATNUM_CT, ctrk.payorpos PAYOR_CT, ctrk.pfact PF_CT, ctrk.phyno PHYCD_CT, ctrk.priorauthno PRIORAUTH_CT, ctrk.prntwerr PRNTERR_CT, ctrk.procdemod PROCDEMOD_CT, ctrk.procde PROCDE_CT, ctrk.prtqty PRT_QTY_CT, ctrk.qtysh QTY_CT, ctrk.rollover ROLLOVR_CT, case when isdate(ctrk.stdos) = 1 then convert(datetime,cast(ctrk.stdos as char(8)),112) else '1/1/1800' end STDOS_CT, ctrk.supp SUP_CT, convert(int,ctrk.terr) TER_CT, ctrk.price UNIT_PRICE_CT, ctrk.unit UOM_CT, ctrk.username USERNAME_CT, ctrk.cuft VOL_CT from AR_LOAD_DATA.dbo.clmtrk ctrk WITH(NOLOCK)-- ========================================================-- Work View (Basically the same as the other one but with lesser fields/columns)-- ========================================================CREATE view [dbo].[vuClaimTrackAll] asSELECT ALW_AMT_CT, ALW_PF_CT, BILAMT_CT, BRANCH_CT, CUSTNUM_CT, DESCRIPTION_CT, DIAGCD001_CT, DIAGCD002_CT, DIAGCD003_CT, DIAGCD004_CT, EVNT_DT_CT, EVNT_TM_CT, EVNT_TYP_CT, INS001_CT, INS002_CT, INS003_CT, INSFRM_CT, INSNUN_CT, INVNUM_CT, LNNUM_CT, MODIFIER001_CT, MODIFIER002_CT, MODIFIER003_CT, MODIFIER004_CT, PAYOR_CT, PF_CT, PROCDEMOD_CT, PROCDE_CT, QTY_CT, STDOS_CT, ROLLOVR_CT, ITEMNUM_CTFROM AR.dbo.tblCLMTRK WITH (nolock)/*UNIONSELECT ALW_AMT_CK, ALW_PF_CK, BILAMT_CK, BRANCH_CK, CUSTNUM_CK, DESCRIPTION_CK, DIAGCD001_CK, DIAGCD002_CK, DIAGCD003_CK, DIAGCD004_CK, EVNT_DT_CK, EVNT_TM_CK, EVNT_TYP_CK, INS001_CK, INS002_CK, INS003_CK, INSFRM_CK, INSNUM_CK, INVNUM_CK, LNNUM_CK, MODIFIER001_CK, MODIFIER002_CK, MODIFIER003_CK, MODIFIER004_CK, PAYOR_CK, PF_CK, PROCDEMOD_CK, PROCDE_CK, QTY_CK, STDOS_CK, ROLLOVR_CT, ITEMNUM_CKFROM AR.dbo.tblCLTRKH WITH (nolock)*/[/code]The problem seems to be when I make a self join, but I'm not sure how to approach this problem.It's an overlapping issue and I've seen better ways to work with it but from 2005 and later.Here's the code:[code="sql"]DECLARE @tCodes table( code varchar(5), days int, years int)INSERT INTO @tCodes SELECT 'A4310', 90,0 UNION ALLSELECT 'A4311', 90,0 UNION ALLSELECT 'J3490', 0,5 UNION ALLSELECT 'L0631', 0,5 UNION ALLSELECT 'L7900', 0,5SELECT COUNT(*)FROM dbo.vuClaimTrackAll c JOIN @tCodes t ON c.PROCDE_CT = t.code--SELECT COUNT(*) FROM dbo.vuClaimTrackAll--71,407,538--SELECT COUNT(*)--FROM dbo.vuClaimTrackAll c --JOIN @tCodes t ON c.PROCDE_CT = t.code--2,452,814INSERT INTO dbo.Log1262_OverlapViolations( customer , invoice , hcpc , lineNumber , insurance , stdos , allowedDate , prevDate , billAmount )SELECT c.CUSTNUM_CT , c.INVNUM_CT , c.PROCDE_CT , c.LNNUM_CT , c.INSNUN_CT , c.STDOS_CT , DATEADD( yy, t.years, DATEADD( dd, (t.days) - 5, MAX( h.STDOS_CT))) AS allowedDate, MAX( h.STDOS_CT), c.BILAMT_CTFROM dbo.vuClaimTrackAll c WITH (NOLOCK)LEFTJOIN dbo.vuClaimTrackAll h WITH (NOLOCK) ON c.CUSTNUM_CT = h.CUSTNUM_CT AND c.PROCDE_CT = h.PROCDE_CT AND c.INSNUN_CT = h.INSNUN_CT AND c.STDOS_CT > h.STDOS_CTJOIN @tHCPCs t ON c.PROCDE_CT = t.hcpcWHERE c.INSNUN_CT IN ('MCA', 'MCB', 'MCC', 'MCD', 'MC1', 'MC2', 'MC3', 'MC4')AND NOT EXISTS( SELECT * FROM dbo.Log1262_OverlapViolations v WITH (NOLOCK) WHERE c.CUSTNUM_CT = v.customer AND c.PROCDE_CT = v.hcpc AND c.INSNUN_CT = v.insurance AND c.STDOS_CT = v.stdos)GROUP BY c.CUSTNUM_CT , c.INVNUM_CT , c.PROCDE_CT , c.LNNUM_CT , c.INSNUN_CT , c.INS001_CT , c.STDOS_CT , t.years , t.days , c.BILAMT_CT[/code]And here's some sample data, it might need some extra work and I can correct it later.[code="sql"]INSERT INTO [dbo].[CLMTRK]SELECT '0000E','1','17091095','1','20120327','152816','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120319','20120319','MCD','OXX',' ','1','724.40','2','490.490000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','443.9','401.9',' ','4YMW','0','0','0','0333860001','0.00','170910959421',NULL UNION ALL SELECT '0003C','1','17136042','2','20120329','150448','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15006',' 1','PROLIGN BACK BRACE XXLRG F30/32','2','EA','20120327','20120327','MCA','B52',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','847.2',' ',' ',' ','39SJ','0','0','0','0333860001','0.00','171360421500',NULL UNION ALL SELECT '0007A','1','17079561','1','20120320','141909','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120316','20120316','MCB','B01',' ','1','689.90','2','487.610000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','4KGD','0','0','0','0333860001','0.00','170795618872',NULL UNION ALL SELECT '0007X','1','17055723','2','20120404','151822','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120312','20120312','MCD','PXC',' ','1','1384.29','2','946.220000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02',' ',' ',' ','NQ80','0','0','0','0333860001','0.00','170557236961',NULL UNION ALL SELECT '0007X','1','17055723','2','20120426','142451','2','1','PXC','2','405','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120312','20120312','MCD','PXC',' ','1','1384.29','2','946.220000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02',' ',' ',' ','NQ80','0','0','0',' ','0.00','170557232227',NULL UNION ALL SELECT '0008Z','1','17104499','1','20120323','141745','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120321','20120321','MCA','J51',' ','1','689.90','2','475.940000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','1AEC','0','0','0','0333860001','0.00','171044996270',NULL UNION ALL SELECT '0008Z','1','17104499','1','20120412','141521','2','1','J51','2','405','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120321','20120321','MCA','J51',' ','1','689.90','2','475.940000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','1AEC','0','0','0',' ','0.00','171044997969',NULL UNION ALL SELECT '00090','1','17060022','1','20120329','144750','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007003',' 1','ENCORE REVIVE BATTERY VAC SYSTEM','2','EA','20120313','20120313','MCD','836',' ','1','724.40','2','490.490000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','0IET','0','0','0','0333860001','0.00','170600224201',NULL UNION ALL SELECT '00090','1','17060022','1','20120514','143213','2','1','836','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007003',' 1','ENCORE REVIVE BATTERY VAC SYSTEM','2','EA','20120313','20120313','MCD','836',' ','1','724.40','2','490.490000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','0IET','0','0','0','650193983','0.00','170600224568',NULL UNION ALL SELECT '0009R','1','17099854','1','20120322','135631','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','MWV',' ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','YIQ5','0','0','0','0333860001','0.00','170998543298',NULL UNION ALL SELECT '0009R','1','17099854','1','20120420','140641','2','1','MWV','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','MWV',' ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','YIQ5','0','0','0','ZZ332B00000X','0.00','170998549236',NULL UNION ALL SELECT '000A7','1','17055724','1','20120329','154413','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15001',' 1','PROLIGN BACK BRACE XSM F4/12','2','EA','20120312','20120312','MCC','836',' ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02','724.2',' ',' ','5R6K','0','0','0','0333860001','0.00','170557240493',NULL UNION ALL SELECT '000B8','1','17155001','1','20120419','171126','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120330','20120330','MCC',' ',' ','1','724.40','2','503.580000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','401.9',' ',' ','XTC1','0','0','0','0333860001','0.00','171550011824',NULL UNION ALL SELECT '000E8','1','17104500','1','20120323','141745','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120321','20120321','MCA','935',' ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','443.9','401.9','49WN','0','0','0','0333860001','0.00','171045006294',NULL UNION ALL SELECT '000FA','1','17148817','1','20120419','171120','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120329','20120329','MCC','B92',' ','1','689.90','2','504.980000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','OD16','0','0','0','0333860001','0.00','171488170133',NULL UNION ALL SELECT '000FA','1','17148817','1','20120514','143215','2','1','B92','2','405','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120329','20120329','MCC','B92',' ','1','689.90','2','504.980000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','OD16','0','0','0',' ','0.00','171488174948',NULL UNION ALL SELECT '000FK','1','17060296','1','20120329','150456','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120313','20120313','MCA','836',' ','1','724.40','2','497.150000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.61','443.9','401.9','OA24','0','0','0','0333860001','0.00','170602963047',NULL UNION ALL SELECT '000FP','1','17286604','1','20120427','140232','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15004',' 1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120425','20120425','MCB','AW1',' ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02',' ',' ',' ','MK07','0','0','0','0333860001','0.00','172866045737',NULL UNION ALL SELECT '000GB','1','17099899','1','20120322','135141','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCB','FH8',' ','1','724.40','2','487.610000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.61',' ',' ','4VUH','0','0','0','0333860001','0.00','170998991293',NULL UNION ALL SELECT '000GB','1','17099899','1','20120412','141520','2','1','FH8','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCB','FH8',' ','1','724.40','2','487.610000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.61',' ',' ','4VUH','0','0','0',' ','0.00','170998997900',NULL UNION ALL SELECT '000I7','1','17066072','1','20120320','141949','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120314','20120314','MCA','QJI',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','3CUD','0','0','0','0333860001','0.00','170660727903',NULL UNION ALL SELECT '000IV','1','17292310','2','20120430','142023','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15003',' 1','PROLIGN BACK BRACE MED F20/22','2','EA','20120426','20120426','MCB','836',' ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','4CRF','0','0','0','0333860001','0.00','172923102972',NULL UNION ALL SELECT '000JN','1','17098025','1','20120417','150426','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','OE7',' ','1','724.40','2','504.980000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','332X','0','0','0','0333860001','0.00','170980256835',NULL UNION ALL SELECT '000JN','1','17098025','1','20120507','142933','2','1','OE7','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','OE7',' ','1','724.40','2','504.980000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','332X','0','0','0',' ','0.00','170980254004',NULL UNION ALL SELECT '000JN','1','17104423','2','20120417','150426','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15004',' 1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120321','20120321','MCC','OE7',' ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','332X','0','0','0','0333860001','0.00','171044236836',NULL UNION ALL SELECT '000JN','1','17104423','2','20120507','142933','2','1','OE7','2','405','110','001','batchop0','1','0','L0631',' ','DER','15004',' 1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120321','20120321','MCC','OE7',' ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','332X','0','0','0',' ','0.00','171044234005',NULL UNION ALL SELECT '000JO','1','17078945','1','20120419','163015','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120316','20120316','MCD','F40',' ','1','689.90','2','503.580000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','IX24','0','0','0','0333860001','0.00','170789454307',NULL UNION ALL SELECT '000JO','1','17078945','1','20120516','145109','2','1','F40','2','405','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120316','20120316','MCD','F40',' ','0','689.90','2','503.580000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','IX24','0','0','0',' ','0.00','170789453335',NULL UNION ALL SELECT '000KR','1','17090344','2','20120419','153306','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120319','20120319','MCA','B98','8D2','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','2OE9','0','0','0','0333860001','0.00','170903440723',NULL UNION ALL SELECT '000KR','1','17090344','2','20120425','160959','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120319','20120319','MCA','B98','8D2','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','2OE9','0','0','0','0333860001','0.00','170903446490',NULL UNION ALL SELECT '000KS','1','17153878','1','20120410','144802','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120330','20120330','MCD','MMT',' ','1','724.40','2','503.580000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','272.0',' ',' ','2THI','0','0','0','0333860001','0.00','171538788070',NULL UNION ALL SELECT '000L3','1','17141333','1','20120330','135848','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120328','20120328','MCD','MWY',' ','1','724.40','2','503.580000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','53YI','0','0','0','0333860001','0.00','171413335002',NULL UNION ALL SELECT '000L3','1','17141333','1','20120420','140641','2','1','MWY','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120328','20120328','MCD','MWY',' ','1','724.40','2','503.580000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','53YI','0','0','0','111874900','0.00','171413339239',NULL UNION ALL SELECT '000NO','1','17097954','1','20120329','144737','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCD','836',' ','1','689.90','2','490.490000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','5QKT','0','0','0','0333860001','0.00','170979542192',NULL UNION ALL SELECT '000NO','1','17097954','1','20120529','140801','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCD','836',' ','1','689.90','2','490.490000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','5QKT','0','0','0','0333860001','0.00','170979544083',NULL UNION ALL SELECT '000PA','1','17141627','1','20120330','135552','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120328','20120328','MCA','B1E',' ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','EV65','0','0','0','0333860001','0.00','171416273883',NULL UNION ALL SELECT '000PB','1','17066469','1','20120320','143325','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007003',' 1','ENCORE REVIVE BATTERY VAC SYSTEM','2','EA','20120314','20120314','MCD',' ',' ','1','724.40','2','503.580000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','QYF9','0','0','0','0333860001','0.00','170664695880',NULL UNION ALL SELECT '000QN','1','17118438','1','20120329','144715','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15005',' 1','PROLIGN BACK BRACE XLRG F28/30','2','EA','20120323','20120323','MCD','836',' ','1','1384.29','2','963.230000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','4QTV','0','0','0','0333860001','0.00','171184389247',NULL UNION ALL SELECT '000R8','1','17213175','1','20120413','141149','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120411','20120411','MCB','B0S',' ','1','724.40','2','487.610000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','4WLL','0','0','0','0333860001','0.00','172131750234',NULL UNION ALL SELECT '000RA','1','17104268','1','20120323','144058','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120321','20120321','MCD','L39',' ','1','1384.29','2','963.230000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','722.52',' ',' ',' ','5RJK','0','0','0','0333860001','0.00','171042681175',NULL UNION ALL SELECT '000RF','1','17154625','1','20120419','153330','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15006',' 1','PROLIGN BACK BRACE XXLRG F30/32','2','EA','20120330','20120330','MCA','1LR',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','847.2',' ',' ',' ','5ROO','0','0','0','0333860001','0.00','171546254260',NULL UNION ALL SELECT '000RF','1','17154625','1','20120425','161025','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15006',' 1','PROLIGN BACK BRACE XXLRG F30/32','2','EA','20120330','20120330','MCA','1LR',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','847.2',' ',' ',' ','5ROO','0','0','0','0333860001','0.00','171546250012',NULL UNION ALL SELECT '000WS','1','17091916','2','20120404','144125','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15001',' 1','PROLIGN BACK BRACE XSM F4/12','2','EA','20120319','20120319','MCB',' ',' ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','RUY9','0','0','0','0333860001','0.00','170919167998',NULL UNION ALL SELECT '000WS','1','17207160','1','20120412','140712','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120410','20120410','MCB',' ',' ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','RUY9','0','0','0','0333860001','0.00','172071605483',NULL UNION ALL SELECT '000WS','1','17237671','1','20120418','134855','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15004',' 1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120416','20120416','MCB',' ',' ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','RUY9','0','0','0','0333860001','0.00','172376710833',NULL UNION ALL SELECT '000XH','1','17154403','1','20120419','171134','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120330','20120330','MCC','B44',' ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','JPA1','0','0','0','0333860001','0.00','171544034428',NULL UNION ALL SELECT '000ZF','1','17155605','1','20120402','150607','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15001',' 1','PROLIGN BACK BRACE XSM F4/12','2','EA','20120330','20120330','MCA',' ',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','847.2',' ',' ','AHMI','0','0','0','0333860001','0.00','171556058909',NULL UNION ALL SELECT '0010J','1','17097290','2','20120322','135629','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15003',' 1','PROLIGN BACK BRACE MED F20/22','2','EA','20120320','20120320','MCC','B44',' ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','1VZV','0','0','0','0333860001','0.00','170972903118',NULL UNION ALL SELECT '0010Q','1','17172595','1','20120410','144757','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120403','20120403','MCD','X97',' ','1','724.40','2','490.490000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','414.04',' ',' ','FGQ8','0','0','0','0333860001','0.00','171725957437',NULL UNION ALL SELECT '0010R','1','17098737','1','20120329','154410','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','836',' ','1','724.40','2','505.010000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','52S8','0','0','0','0333860001','0.00','170987379695',NULL UNION ALL SELECT '0014M','1','17248353','1','20120420','143819','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15003',' 1','PROLIGN BACK BRACE MED F20/22','2','EA','20120418','20120418','MCC','B44',' ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','BKR2','0','0','0','0333860001','0.00','172483531013',NULL UNION ALL SELECT '0014Q','1','17078261','2','20120419','161545','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15004',' 1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120316','20120316','MCC','115',' ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02',' ',' ',' ','5RCH','0','0','0','0333860001','0.00','170782619867',NULL UNION ALL SELECT '0015C','1','17145759','1','20120419','163000','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15003',' 1','PROLIGN BACK BRACE MED F20/22','2','EA','20120328','20120328','MCD','B98',' ','1','1384.29','2','946.220000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','AZJ9','0','0','0','0333860001','0.00','171457591017',NULL UNION ALL SELECT '0015C','1','17145759','1','20120510','141029','2','1','B98','2','405','110','001','batchop0','1','0','L0631','NU','DER','15003',' 1','PROLIGN BACK BRACE MED F20/22','2','EA','20120328','20120328','MCD','B98',' ','1','1384.29','2','946.220000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ','NU',' ',' ',' ','724.2',' ',' ',' ','AZJ9','0','0','0',' ','0.00','171457596160',NULL UNION ALL SELECT '0015L','1','17080356','1','20120329','150438','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120316','20120316','MCA','836',' ','1','689.90','2','475.940000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.61',' ',' ','BT04','0','0','0','0333860001','0.00','170803569979',NULL UNION ALL SELECT '0017H','1','17080320','1','20120327','144826','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120316','20120316','MCC',' ',' ','1','689.90','2','505.010000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.60',' ',' ','5QJP','0','0','0','0333860001','0.00','170803204000',NULL UNION ALL SELECT '001A6','1','17142125','1','20120330','135847','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120328','20120328','MCD','0XY',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','5RYM','0','0','0','0333860001','0.00','171421254737',NULL UNION ALL SELECT '001BR','1','17097956','1','20120419','161622','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC',' ',' ','1','689.90','2','505.010000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.60',' ',' ','ABO6','0','0','0','0333860001','0.00','170979564661',NULL UNION ALL SELECT '001BR','1','17097956','1','20120525','140752','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC',' ',' ','1','689.90','2','505.010000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.60',' ',' ','ABO6','0','0','0','0333860001','0.00','170979568969',NULL UNION ALL SELECT '001BV','1','17092613','1','20120327','144846','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120319','20120319','MCC',' ',' ','1','724.40','2','505.010000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','302.74',' ',' ','1K4B','0','0','0','0333860001','0.00','170926137289',NULL UNION ALL SELECT '001D0','1','17111768','2','20120327','141424','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15005',' 1','PROLIGN BACK BRACE XLRG F28/30','2','EA','20120322','20120322','MCB','B01',' ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.4',' ',' ','4KP5','0','0','0','0333860001','0.00','171117683034',NULL UNION ALL SELECT '001EF','1','17097292','1','20120327','144839','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15001',' 1','PROLIGN BACK BRACE XSM F4/12','2','EA','20120320','20120320','MCC','DL8',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','5RE6','0','0','0','0333860001','0.00','170972926050',NULL UNION ALL SELECT '001EF','1','17097292','1','20120416','135956','2','1','DL8','2','405','110','001','batchop0','1','0','L0631',' ','DER','15001',' 1','PROLIGN BACK BRACE XSM F4/12','2','EA','20120320','20120320','MCC','DL8',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','5RE6','0','0','0',' ','0.00','170972929623',NULL UNION ALL SELECT '001ER','1','17091107','1','20120327','144823','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120319','20120319','MCC',' ',' ','1','689.90','2','504.980000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.60',' ',' ','0AQ2','0','0','0','0333860001','0.00','170911073472',NULL UNION ALL SELECT '001GB','1','17134620','1','20120329','150455','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120327','20120327','MCA',' ',' ','1','724.40','2','497.150000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','2ULG','0','0','0','0333860001','0.00','171346202818',NULL UNION ALL SELECT '001HE','1','17097293','1','20120327','144836','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120320','20120320','MCC','BI2',' ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','737.30',' ',' ',' ','1MSQ','0','0','0','0333860001','0.00','170972935674',NULL UNION ALL SELECT '001HE','1','17097293','1','20120416','135956','2','1','BI2','2','405','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120320','20120320','MCC','BI2',' ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','737.30',' ',' ',' ','1MSQ','0','0','0','650193983','0.00','170972939575',NULL UNION ALL SELECT '001HE','1','17097293','1','20120606','134745','2','1','AW1','3','405','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120320','20120320','MCC','AW1',' ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','737.30',' ',' ',' ','1MSQ','0','0','0','650193983','0.00','170972932066',NULL UNION ALL SELECT '001J2','1','17112600','2','20120329','154414','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15004',' 1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120322','20120322','MCC','836',' ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','5RVN','0','0','0','0333860001','0.00','171126000773',NULL UNION ALL SELECT '001J9','1','17094008','2','20120419','153323','1','1','MCA','1','400','110','001','batchop0','1','0','A4310','KX','BRD','802130',' 1','INSERTION TRAY W/BZK 30CC SYR','2','EA','20120319','20120319','MCA','MMD',' ','1','30.42','4','7.670000','4','10.140000','0','3.000','3.000','0','1.000','0','0','1',' ',' ','KX',' ',' ',' ','952.9',' ',' ',' ','1VXS','0','0','0','0333860001','0.00','170940082971',NULL UNION ALL SELECT '001J9','1','17094008','2','20120425','161018','1','1','MCA','1','400','110','001','batchop0','1','0','A4310','KX','BRD','802130',' 1','INSERTION TRAY W/BZK 30CC SYR','2','EA','20120319','20120319','MCA','MMD',' ','1','30.42','4','7.670000','4','10.140000','0','3.000','3.000','0','1.000','0','0','1',' ',' ','KX',' ',' ',' ','952.9',' ',' ',' ','1VXS','0','0','0','0333860001','0.00','170940088728',NULL UNION ALL SELECT '001J9','1','17094008','2','20120511','142603','2','1','MMD','2','405','110','001','batchop0','1','0','A4310','KX','BRD','802130',' 1','INSERTION TRAY W/BZK 30CC SYR','2','EA','20120319','20120319','MCA','MMD',' ','1','30.42','4','7.670000','4','10.140000','0','3.000','3.000','0','1.000','0','0','1',' ',' ','KX',' ',' ',' ','952.9',' ',' ',' ','1VXS','0','0','0','113400100','0.00','170940084897',NULL UNION ALL SELECT '001JZ','1','17111151','1','20120327','144834','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007003',' 1','ENCORE REVIVE BATTERY VAC SYSTEM','2','EA','20120322','20120322','MCC','B0S',' ','1','724.40','2','505.010000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','401.9','185',' ','0BGP','0','0','0','0333860001','0.00','171111515309',NULL UNION ALL SELECT '001LJ','1','17165826','1','20120404','144103','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120402','20120402','MCB','836',' ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','715.09',' ',' ',' ','0AQA','0','0','0','0333860001','0.00','171658265064',NULL UNION ALL SELECT '001LK','1','17105828','1','20120327','141423','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15003',' 1','PROLIGN BACK BRACE MED F20/22','2','EA','20120321','20120321','MCB','B0S',' ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','5RSF','0','0','0','0333860001','0.00','171058282729',NULL UNION ALL SELECT '001LW','1','17288037','1','20120427','140835','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120425','20120425','MCD','MCL',' ','1','689.90','2','490.490000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','5AFB','0','0','0','0333860001','0.00','172880376683',NULL UNION ALL SELECT '001MK','1','17170852','1','20120405','135343','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15004',' 1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120403','20120403','MCD','DL8',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','716.90',' ',' ',' ','KHB0','0','0','0','0333860001','0.00','171708520782',NULL UNION ALL SELECT '001N3','1','17111153','1','20120327','152820','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120322','20120322','MCD','OXX',' ','1','724.40','2','490.490000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','WSN6','0','0','0','0333860001','0.00','171111530035',NULL UNION ALL SELECT '001NU','1','17104507','1','20120327','141505','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120321','20120321','MCA','B44',' ','1','724.40','2','497.150000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','V332','0','0','0','0333860001','0.00','171045075736',NULL UNION ALL SELECT '001OH','1','17104505','1','20120327','144835','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120321','20120321','MCC','B44',' ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','CVZ9','0','0','0','0333860001','0.00','171045055440',NULL UNION ALL SELECT '001PE','1','17148818','1','20120402','145417','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120329','20120329','MCB',' ',' ','1','724.40','2','487.610000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','TBN2','0','0','0','0333860001','0.00','171488187318',NULL UNION ALL SELECT '001PJ','1','17170839','2','20120405','135028','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120403','20120403','MCB','GO6',' ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','5MJU','0','0','0','0333860001','0.00','171708390368',NULL UNION ALL SELECT '001PJ','1','17170839','2','20120425','142227','2','1','GO6','2','405','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120403','20120403','MCB','GO6',' ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','5MJU','0','0','0','650193983','0.00','171708395236',NULL UNION ALL SELECT '001QE','1','17137289','1','20120329','144742','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15004',' 1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120327','20120327','MCD','127',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','PJW8','0','0','0','0333860001','0.00','171372892979',NULL UNION ALL SELECT '001QJ','1','17134621','1','20120404','150432','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120327','20120327','MCA',' ',' ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','5RY9','0','0','0','0333860001','0.00','171346212058',NULL UNION ALL SELECT '001QJ','1','17134621','1','20120430','144701','2','1','017','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120327','20120327','MCA','017',' ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','5RY9','0','0','0','3400118','0.00','171346214969',NULL UNION ALL SELECT '001SB','1','17212357','1','20120419','171105','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120411','20120411','MCC','8CJ','G10','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','847.2',' ',' ','0XE2','0','0','0','0333860001','0.00','172123575544',NULL UNION ALL SELECT '001TY','1','17154410','2','20120402','150842','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120330','20120330','MCB','B44',' ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','4RI3','0','0','0','0333860001','0.00','171544109228',NULL UNION ALL SELECT '001TZ','1','17177283','1','20120410','160011','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120404','20120404','MCC','B44',' ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','4BZL','0','0','0','0333860001','0.00','171772838341',NULL UNION ALL SELECT '001UL','1','17191318','1','20120409','140205','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120406','20120406','MCB','B0S',' ','1','689.90','2','487.610000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.01','401.9',' ','TP39','0','0','0','0333860001','0.00','171913182825',NULL UNION ALL SELECT '001VH','1','17298310','1','20120430','144430','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120427','20120427','MCB','OXX',' ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02',' ',' ',' ','NTU2','0','0','0','0333860001','0.00','172983109895',NULL UNION ALL SELECT '001X2','1','17118466','1','20120404','151827','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15003',' 1','PROLIGN BACK BRACE MED F20/22','2','EA','20120323','20120323','MCD','QW1',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','4A9D','0','0','0','0333860001','0.00','171184667845',NULL UNION ALL SELECT '001XU','1','17201362','1','20120417','150402','1','1','MCC','3','402','110','001','batchop0','1','0','L0631',' ','DER','15002',' 1','PROLIGN BACK BRACE SML F14/18','2','EA','20120409','20120409','MCC','B44',' ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','590P','0','0','0','0333860001','0.00','172013622278',NULL UNION ALL SELECT '001YD','1','17334483','1','20120507','143920','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','AUG','94009201',' 1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120504','20120504','MCB','B01',' ','1','689.90','2','487.610000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','0W2G','0','0','0','0333860001','0.00','173344834683',NULL UNION ALL SELECT '001YO','1','17134623','1','20120404','150430','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120327','20120327','MCA',' ',' ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','401.9',' ',' ','JS48','0','0','0','0333860001','0.00','171346231440',NULL UNION ALL SELECT '001YS','1','17104426','1','20120329','144751','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15004',' 1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120321','20120321','MCD','14X',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','RVW2','0','0','0','0333860001','0.00','171044264233',NULL UNION ALL SELECT '001YS','1','17279331','1','20120426','140821','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15005',' 1','PROLIGN BACK BRACE XLRG F28/30','2','EA','20120424','20120424','MCD','14X',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','RVW2','0','0','0','0333860001','0.00','172793318100',NULL UNION ALL SELECT '0020M','1','17170843','1','20120405','135028','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','ENC','44007001',' 1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120403','20120403','MCB','BT1',' ','1','724.40','2','487.610000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','443.9','401.9','OM92','0','0','0','0333860001','0.00','171708430311',NULL UNION ALL SELECT '0022G','1','17171280','2','20120405','134927','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15004',' 1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120403','20120403','MCA','836',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','DQ56','0','0','0','0333860001','0.00','171712808574',NULL UNION ALL SELECT '0023T','1','17148705','1','20120419','153327','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15003',' 1','PROLIGN BACK BRACE MED F20/22','2','EA','20120329','20120329','MCA','BF9',' ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','847.2',' ',' ',' ','4R0H','0','0','0','0333860001','0.00','171487053582',NULL[/code]I couldn't save the execution plan and only managed to copy the screenshots.If there's anything more I could do, I'll do my best.Thu, 02 Aug 2012 10:54:30 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic444012-65-1.aspxI have created a full text index on a column of a table. The issue is i need to search for AT&T.the query i use for search isSELECT * FROM TABLE_NAME WHERE CONTAINS(COLUMN_NAME, '"AT&T*"')It returns many records but doesn't return the records with AT&T in it. I guess '&' is killing it, but i have no idea how to escape it in case of full text index search.Any help will be deeply appreciated.Wed, 16 Jan 2008 14:22:52 GMTjeepee_inhttp://www.sqlservercentral.com/Forums/Topic1008730-65-1.aspxThe following is a short list of tool I found performing a quick search on the web and I was curious if anyone had any experiences with these tools or could recommend other tools.Redgate SQL Response ( http://www.red-gate.com/products/SQL_Response/index.htm )Idera SQL Diagnostic Manager ( http://www.idera.com/Products/SQL-Server/SQL-diagnostic-manager/ )embarcadera DB Performance Center XE ( http://www.embarcadero.com/products/performance-center?gclid=CI_hzsvC5KQCFVvW5wod-R-WMg )Confio Ignite ( http://www.confio.com/ppc/FreeTrial-SQLServer.php?source=GoogSqlS&gclid=CKOcoM3C5KQCFYK5Kgod4zxJMw )Quest Spotlight ( http://www.quest.com/spotlight-on-sql-server-enterprise/ )Heroix Longitude ( http://www.heroix.com/agentless/agentless_database_monitoring.htm )SQLSentry ( http://www.sqlsentry.com/ )Personally, I have been working with Idera SQL Diagnostic Manager. However, it has been a while since I have researched what tools are available.Any suggestions or comments would be appreciated.Thu, 21 Oct 2010 12:42:12 GMTeccentricDBAhttp://www.sqlservercentral.com/Forums/Topic1321091-65-1.aspxHi Guys,Can you please figure out the reason why insert with this table is sometimes slow. Some insert 2 ms, some insert 500 ms. Here is the schemaCREATE TABLE [dbo].[TT_TSAPIDataA]( [TempCallDataId] [int] IDENTITY(1,1) NOT NULL, [SiteId] [tinyint] NOT NULL, [ReferenceId] [smallint] NULL, [CallId] [int] NOT NULL, [ResultingCallId] [int] NULL, [SourceIP] [char](15) NOT NULL, [DestinationIP] [char](15) NOT NULL, [ProtocolId] [tinyint] NULL, [ApplicationId] [int] NULL, [OperationValue] [varchar](50) NULL, [SpecificEvent] [varchar](50) NULL, [DNIS] [int] NULL, [ANI] [varchar](20) NULL, [CallingNumber] [varchar](20) NULL, [CallingAddressType] [varchar](20) NULL, [CalledNumber] [varchar](20) NULL, [CalledAddressType] [varchar](20) NULL, [DomainExtension] [int] NULL, [Count] [tinyint] NULL, [OriginationPort] [int] NULL, [ConnectedNumber] [varchar](20) NULL, [ConnectedNumber2] [varchar](20) NULL, [ConnectedAddressType] [varchar](20) NULL, [TrunkGroup] [smallint] NULL, [TrunkMember] [smallint] NULL, [Cause] [varchar](50) NULL, [DateReceived] [datetime] NOT NULL, [TimeZoneOffset] [smallint] NULLCONSTRAINT [PK_TT_TSAPIDataA] PRIMARY KEY CLUSTERED ( [TempCallDataId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[TT_TSAPIDataA] ADD CONSTRAINT [DF_TT_TSAPIDataA_DateReceived] DEFAULT (getutcdate()) FOR [DateReceived]GOTue, 26 Jun 2012 02:26:18 GMTjmooghttp://www.sqlservercentral.com/Forums/Topic1321715-65-1.aspxHi,From the Below Script, how to find Actual row count at Step 4 & Step 5.Basially, need to validated between Index Scan and Index Seek.Using SQL Server 2000./* Script Begin */Step 1 :Create table T1 (id int, name varchar(10))Step 2 :declare @rows int, @name varchar(3)set @rows = ascii('A')while (@rows <= ascii('Z'))begin set @name = char(@rows) + char(@rows) + char(@rows) insert into T1 values (@rows, @Name) set @rows = @rows + 1endStep 3 :-- Table Scan will appear when no Index existsSET STATISTICS XML OFFselect * from T1 Step 4 : -- Creating Clustered index on ID Column to appear Index Scan while searching for Name FieldCreate Clustered index IX_ID on T1(ID)goselect * from T1 (nolock) where Name = 'BBB'-- Question : In this Stage, technically I want to prove how many records were scanned in the table T1 ( Acutal Row Count ). -- This is not appearing in the Execution Plan ( Acutual Row Count ) using SQL Server 2000Step 5 :-- Creating NonClustered index on Name Column to appear Index Seek while searching for Name FieldCreate NonClustered index IX_Name on T1(Name)goselect * from T1(nolock) where Name = 'BBB'-- How to technically prove to others that Index Seek touching one record from the Table T1 and Index Scan touching all the records as step 4/* Script End */Wed, 27 Jun 2012 00:53:01 GMTNew2SQL-343122http://www.sqlservercentral.com/Forums/Topic1313112-65-1.aspxMy Sql server version : Microsoft SQL Server 2000 - 8.00.2249 (Intel X86) If run the following sql statement with the Execution plan , it show no of executive : 24select tbl1.sys_key, tbl2.sys_key,tbl3.syskey from tbl1,tbl2,tbl3where tbl1.sys_key= tbl2.tbl1key and tbl2.sys_key=tbl3.tbl2keyBut if i run following statement, it shows number of executive: 1 select tbl1.sys_key, tbl2.sys_key,tbl3.syskey from tbl1,tbl2,tbl3where tbl1.sys_key= tbl2.tbl1key and tbl2.sys_key=tbl3.tbl2keyand tbl1.sys_key=100How i can reduce the number of executive of first query?Please adviceMathewFri, 08 Jun 2012 07:12:14 GMTMathew M.Varghesehttp://www.sqlservercentral.com/Forums/Topic1307282-65-1.aspxHi, I've been taking some stats from one of our production servers that we've not long taken on from another company. Installed version is SQL Server 2000 SP2(!) so I've been taking some stats to find out how it's used and what sort of load it's under.The contents of my normal toolbox won't work (Brent Ozar's sp_blitz, Adam Machanic's who_is_active for example) so I came up with this to get some I/O stats:[code="sql"]SET NOCOUNT ONDECLARE @upSince DATETIMESET @upSince = (SELECT TOP 1 crdate FROM master.sys.sysdatabases WHERE name='tempdb')SELECT CAST(GETDATE()-@upSince AS BIGINT) AS uptime_in_daysSELECT dbs.name, vfs.FileID, vfs.NumberReads, vfs.IoStallReadMS, vfs.NumberWrites, vfs.BytesWritten/1024 AS KB_Written, vfs.IOStallWriteMS, vfs.BytesOnDisk/1024 AS KB_On_Disk_NowFROM fn_virtualfilestats(null,null) vfsLEFT JOIN master.sys.sysdatabases dbs ON vfs.DBId = dbs.dbidWHERE name NOT IN ('master','msdb','model','tempdb')ORDER BY name ASC[/code]This gave me some results which, once I'd thrown them into Excel and played with them a bit, showed me that on balance the databases on this server are write-heavy.So I looked into the disk arrays - C: for boot, D: for data files, E: for log files, physical RAID 1 arrays.Next, I went into perfmon and looked at a few stats, but the one that stood out was an average 40ms for transfers (Avg. Disk/sec / Transfer) on D:. Good values (from MSDN) are < 10ms as excellent, 10-20ms OK, 20-40ms slow and > 40ms - big problem.There's been no complaints so far from the users that the applications are running slow but I'd like to improve on the write performance.My first thoughts are to move some DBs onto a separate server to ease the load. Are there any other tips you guys have come across that might alleviate the write load?What I'm thinking is perhaps force more memory use, or restrict the I/O in some (beneficial) way?ThanksMon, 28 May 2012 06:51:57 GMTderek.colleyhttp://www.sqlservercentral.com/Forums/Topic1295073-65-1.aspxHi,I am using SQL server 2008R2 STD edition and my server configuration is win 2008R2 STD Edition 64bit and Dual core Processor with 8 GB of RAM, but recently i noticed that my CPU utilization is almost 70% and Physical memory utilization is also around 6 GB, so can any one suggest how to do minimize both CPU and Physical memory utilization and do performance tuning can any one help in thisFri, 04 May 2012 01:33:03 GMTsyam_pydimarri86http://www.sqlservercentral.com/Forums/Topic1300530-65-1.aspxHow to change non clustered index scan to non clustered index seek?The concerned tables already have non clustered index configured.Tue, 15 May 2012 13:08:30 GMTAlonehttp://www.sqlservercentral.com/Forums/Topic1295357-65-1.aspxHi My development and test databases are hosted on the same box. A stored procedure that runs for about 10 minutes in development database is running for about 4 hours in the test database.Could you tell me how to root cause this performance issue and fix?Thanks in advance.Fri, 04 May 2012 09:40:28 GMTkarthik srinivasanhttp://www.sqlservercentral.com/Forums/Topic683071-65-1.aspxDear all,We are using SQL Server 2005 for production and the database compatability level is kept for 80 not 90. I have a qery for finding the long running queries which supports on SQL 2005 not 2000 database. Its throwing error when i run the queryto identify the long running queries.Query : select top 50qs.total_worker_time / execution_count as avg_worker_time,substring(st.text, (qs.statement_start_offset/2)+1,((case qs.statement_end_offsetwhen -1 then datalength(st.text)else qs.statement_end_offsetend - qs.statement_start_offset)/2) + 1) as statement_text,*fromsys.dm_exec_query_stats as qscross apply sys.dm_exec_sql_text(qs.sql_handle) as storder byavg_worker_time descERROR : Server: Msg 170, Level 15, State 1, Line 11Line 11: Incorrect syntax near 'apply'.Can anyone please have a look into this to work fine for 2000 compatibility databases. Or else anyone please provide me the query for identifying the long running queries.Thanks,CH&HU.Wed, 25 Mar 2009 00:17:41 GMTCH&HU-1021641http://www.sqlservercentral.com/Forums/Topic1287248-65-1.aspxGood Morning To All :)i would like to ask if there is a 3rd party tool that we can use to measure the Execution of a Stored Procedure. and would point which part of the SP is causing the performance issue? and at the same time will suggest possible code change to the user to correct the Performance issue? Thank you very much !Fri, 20 Apr 2012 09:19:22 GMTStylezhttp://www.sqlservercentral.com/Forums/Topic1278505-65-1.aspxBackground:Previously, my company was using a User Defined Function to html encode some data in a where clause of a stored procedure. Example below:[code="sql"]DECLARE @LName --HTML encoded last name as input parameter from userSELECT * FROM (SELECT LName FROM SomeView xtra WHERE (( @LName <> '' AND dbo.EncodingFunction(dbo.DecodingFunction(xtra.LName))=@LName) OR @Lname=''))[/code]I simplified this for clarity sake.The problem is, when the stored procedure with this query was called 45 times in quick succession, the average performance on a table with 62,000 records was about 85 seconds. When I removed the UDF, the performance improved to just over 1 second to run the sproc 45 times.So, we consulted and decided on a solution that included a computed column in the table accessed by the view, SomeView. The computed column was written into the table definition like this:[code="sql"][LNameComputedColumn] AS (dbo.EncodingFunction(dbo.DecodingFunction([LName])))[/code]I then ran a process that updated the table and automatically populated that computed column for all 62,000 records. Then I changed the stored procedure query to the following:[code="sql"]DECLARE @LName --HTML encoded last name as input parameter from userSELECT * FROM (SELECT LNameComputedColumn FROM SomeView xtra WHERE (( @LName <> '' AND xtra.LNameComputedColumn=@LName) OR @Lname='')[/code]When I ran that stored procedure, the average run time for 45 executions increased to about 90 seconds. My change actually made the problem worse!What am I doing wrong? Is there a way to improve the performance?As a side note, we are currently using SQL Server 2000 and are planning to upgrade to 2008 R2 very soon, but all code must work in SQL Server 2000.Wed, 04 Apr 2012 19:11:54 GMTcrackedcornjimmyhttp://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxHi, thank you for reading my first post.I’m migrating a Classic ASP app from ACCESS to SQL Server. I RE-wrote queries, [u]I’m not using Access with Linked Tables to SQL SRV.[/u] I have a page that is almost 10 times slower than the Access version of the app!!! It is a soccer school. The page is to Staff Coaches to Classes. It has VERY SIMPLE queries, but each one is repeated many times (one per coach). For example:1) Main query gets all coaches that are ACTIVE. All the rest happens inside this loop2) For each coach on the loop, I query the DB to check it he/she is available for the class (date and time)3) If available, I have another query that check if she/he has any VACATION request (thus voiding availability).4) If coach has no vacation, I query the DB again to see if coach if already staffed in a conflicting class (date/ time overlaps the class im searching) 5) If not in a conflicting class, I list the coach as available.6) Cycle repeater per coach.All tables have proper indexes (I rebuilt them). When running any query listed above isolated to test latency, SQL Server runs it faster than access. Im using SQL Express 2008 for development. I even tried with an INSTANCE that is on another computer. Same problem. I even tried migrating tables used in this page to MySql just to see performance, and it works wonderfully. I don’t think that a stored procedure helps here. The logic is on the page as explained. Queries are simple and work ok, so no bottle neck hereDoes any want know what is going on? I’m searching for weeks.In Performance monitor I see there are many batch req /sec and compilations per sec.looks like Sql Server doesn't like to compile many many queries in a short time, even being simple queries?I REALLY appreciate any help.Thank you!MunscioMon, 02 Apr 2012 16:02:41 GMTbullohttp://www.sqlservercentral.com/Forums/Topic1275224-65-1.aspxHi all,We have a tool that collects stats, and today about 4 sessions have excessive lengths (6-7,000 seconds), 2 of which are regular (noticed them yesterday too)We do not have any complaints but this is part of an overall cleanup of our environment. I presume these are the session wait times, any ideas/resources on how to ID+resolve?Thanks, JBThu, 29 Mar 2012 10:39:50 GMTjblovesthegymhttp://www.sqlservercentral.com/Forums/Topic1270308-65-1.aspxIt is clear that sql 2008 r2 will not automatically parallelize inserts - it will only automatically parallelize the select part of those inserts. Have they improved this in sql 2012 ?Large Inserts from one table to another are much faster generally when tablock is used. Is there anything better - maybe something that turns of all logging ?It is possible to manually parallelize inserts of course but then tablock wont work so I found I had to use many parallel inserts to equal the speed of one insert with tablock with a typical query. Partitioning did not seem to speed up parallel inserts. Anybody have better ideas on manual parallelization ?Wed, 21 Mar 2012 10:07:42 GMTjohnb4801http://www.sqlservercentral.com/Forums/Topic1269208-65-1.aspxHi all, I've been taking a look around the Perf fora recently and comparing the scenarios to my own findings, and I was curious as to which types of solution you all have found are the most prevalent. I'd say the following types of fix are most common, so imo, in descending order;1) Code rewrite;2) Memory/CPU upgrade;3) SQL Server reconfiguration;4) Disk System upgrade/reconfiguration5) OS reconfiguration6) Server/Service restartLike I said, I'm curious to know if this agrees with (or how this differs from) your own experiences.Mon, 19 Mar 2012 14:20:54 GMTJake Sheltonhttp://www.sqlservercentral.com/Forums/Topic461479-65-1.aspxWe recently bought some new pc's with office 2007. Before we only had office 2000 and 2003. We use a mdb with a SQL server odbc connection and this works very well with access 2000 and 2003, but with access 2007 it's very slow.If we create a new record, just typing is extremely slow. Anyone has some suggestions how to resolve this?Thanks in advance,TwThu, 28 Feb 2008 01:24:53 GMTtw_fakehttp://www.sqlservercentral.com/Forums/Topic1262046-65-1.aspxHi,Please someone advice me on this.SQLServer 2000----------------Server: Msg 15009, Level 16, State 1, Procedure sp_recompile, Line 18The object 'function1' does not exist in database 'database1.The 'function1' exists in database 'database1' when I checked.Thanks and Regards,Ravi.Tue, 06 Mar 2012 04:09:28 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1259943-65-1.aspxHi,How can i eliminate IN from following query ?select * from idata a where isnull(validationstatus,'Y')='Y' and srno not in (select srno from IFDetails where eby =1 ) and edate between '20.02.2012' and '20.02.2012'this query takes too long time due to both tables having more then 1.5 million rows.thanksThu, 01 Mar 2012 01:18:43 GMTKcVhttp://www.sqlservercentral.com/Forums/Topic544612-65-1.aspxHi AllI'm a long time lurker - this is my first post, so hello and I hope you can help! :DI have just enabled trace flags 1204 and 1205 on a new server, which is at SQL Server 2000 SP4. Funny thing is I am not seeing the output which I expected to see in the event log, I'm not seeing KEY, TAB or PAG references, and also nothing from the buffer. Has anyone else experienced anything similar? Has anyone got any ideas how to get all the detail back in the output from these trace flags?Thu, 31 Jul 2008 11:56:52 GMTKitKaterinahttp://www.sqlservercentral.com/Forums/Topic1238378-65-1.aspxHi, If we create Temporay Tables in a Stored Procedure will degrade the performance of the query?Ex:CREATE TABLE #TempTable(id int,name varchar(50))INSERT INTO #TempTableSELECT id,name FROM EmployeeHow to improve the performance of stored procedure while using temporary tables?Thanks,Suresh Wed, 18 Jan 2012 16:33:01 GMTsuresh0534