|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Issue with T-SQL and SQL 7 to SQL2000 conversionhave come up against a problem. The following code executes correctly in the existing SQL7 database and runs to completion in about 1 minute. In the 2000 database, it runs until I cancel execution -- I've let it run up to 30 minutes without showing any signs of finishing. I am using the identical code to populate the tables in both databases -- the data actually gets fed in from text files via a BULK INSERT command, and that runs correctly in the both databases. The indexes and primary keys are created via T-SQL code, and that runs correctly in both databases. I have deleted and re-created the stored procedure containing this code to no avail. It will still run until I cancel execution. Did something change between SQL7 and 2000 with the UPDATE command, or am I missing something else? Any help will be appreciated -- Carl UPDATE tblProcedureHistory SET tblProcedureHistory.Status_A = [derived].MaxPostingDate FROM ( SELECT T1.OFFICE_NUM, T1.PatientID, T1.PatientType, T1.StudentID, T1.ProcedureID, T1.ProcedureSuffix, T1.Tooth, T1.Surface, MAX(T1.PostingDate) AS MaxPostingDate FROM tbl_AHSTDN AS T1 INNER JOIN tblProcedureHistory AS T2 ON T1.OFFICE_NUM = T2.OFFICE_NUM AND T1.PatientID = T2.PatientID AND T1.PatientType = T2.PatientType AND T1.StudentID = T2.StudentID AND T1.ProcedureID = T2.ProcedureID AND T1.ProcedureSuffix = T2.ProcedureSuffix AND T1.Tooth = T2.Tooth AND T1.Surface = T2.Surface AND T1.Status = 'A' GROUP BY T1.OFFICE_NUM, T1.PatientID, T1.PatientType, T1.StudentID, T1.ProcedureID, T1.ProcedureSuffix, T1.Tooth, T1.Surface ) AS [derived] WHERE tblProcedureHistory.OFFICE_NUM = [derived].OFFICE_NUM AND tblProcedureHistory.PatientID = [derived].PatientID AND tblProcedureHistory.PatientType = [derived].PatientType AND tblProcedureHistory.StudentID = [derived].StudentID AND tblProcedureHistory.ProcedureID = [derived].ProcedureID AND tblProcedureHistory.ProcedureSuffix = [derived].ProcedureSuffix AND tblProcedureHistory.Tooth = [derived].Tooth AND tblProcedureHistory.Surface = [derived].Surface Let's see your DDL including Primary Keys and Indexes.
Show quote "Carl Imthurn" <nospam@all.thanks> wrote in message news:%23di7RAWlGHA.1208@TK2MSFTNGP02.phx.gbl... > I'm in the process of converting a database from SQL 7 to SQL 2000 and > have come up against a problem. The following code executes correctly in > the existing SQL7 database and runs to completion in about 1 minute. > In the 2000 database, it runs until I cancel execution -- I've let it run > up to 30 minutes without showing any signs of finishing. > I am using the identical code to populate the tables in both databases -- > the data actually gets fed in from text files via a BULK INSERT command, > and that runs correctly in the both databases. The indexes and primary > keys are created via T-SQL code, and that runs correctly in both > databases. > I have deleted and re-created the stored procedure containing this code to > no avail. It will still run until I cancel execution. > > Did something change between SQL7 and 2000 with the UPDATE command, or am > I missing something else? > > Any help will be appreciated -- > > Carl > > UPDATE tblProcedureHistory > SET tblProcedureHistory.Status_A = [derived].MaxPostingDate > FROM > ( > SELECT T1.OFFICE_NUM, > T1.PatientID, > T1.PatientType, > T1.StudentID, > T1.ProcedureID, > T1.ProcedureSuffix, > T1.Tooth, > T1.Surface, > MAX(T1.PostingDate) AS MaxPostingDate > > FROM tbl_AHSTDN AS T1 > INNER JOIN tblProcedureHistory AS T2 > ON T1.OFFICE_NUM = T2.OFFICE_NUM > AND T1.PatientID = T2.PatientID > AND T1.PatientType = T2.PatientType > AND T1.StudentID = T2.StudentID > AND T1.ProcedureID = T2.ProcedureID > AND T1.ProcedureSuffix = T2.ProcedureSuffix > AND T1.Tooth = T2.Tooth > AND T1.Surface = T2.Surface > AND T1.Status = 'A' > > GROUP BY > T1.OFFICE_NUM, > T1.PatientID, > T1.PatientType, > T1.StudentID, > T1.ProcedureID, > T1.ProcedureSuffix, > T1.Tooth, > T1.Surface > ) > AS [derived] > > WHERE > tblProcedureHistory.OFFICE_NUM = [derived].OFFICE_NUM > AND tblProcedureHistory.PatientID = [derived].PatientID > AND tblProcedureHistory.PatientType = [derived].PatientType > AND tblProcedureHistory.StudentID = [derived].StudentID > AND tblProcedureHistory.ProcedureID = [derived].ProcedureID > AND tblProcedureHistory.ProcedureSuffix = [derived].ProcedureSuffix > AND tblProcedureHistory.Tooth = [derived].Tooth > AND tblProcedureHistory.Surface = [derived].Surface Here's the DDL for table/PK/index creation:
The table data gets sucked out of an AS/400 every morning into text files and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is not subject to updates; ie, it's a static table. Thanks in advance -- Carl CREATE TABLE [dbo].[tbl_AHSTDN] ( [DNHSTD] [char] (1) NULL , [PatientID] [int] NULL , [PatientType] [int] NULL , [DNTYY] [int] NULL , [DNTMM] [int] NULL , [DNTDD] [int] NULL , [DNSEQ] [int] NULL , [DNID] [int] NULL , [DNIDTY] [char] (1) NULL , [TicketID] [int] NULL , [ProcedureID] [int] NULL , [ProcedureSuffix] [int] NULL , [DNTICX] [int] NULL , [Discipline] [varchar] (100) NULL , [SessionID] [int] NULL , [Grade] [int] NULL , [DNMTHS] [int] NULL , [StudentID] [char] (3) NULL , [DNCGCD] [char] (1) NULL , [DOCMASID] [int] NULL , [DollarAmount] [money] NULL , [DNIN01] [int] NULL , [DNDAT1] [int] NULL , [DNIN02] [int] NULL , [DNDAT2] [int] NULL , [DNCLM_NUM] [char] (5) NULL , [Status] [char] (1) NULL , [DNFILE] [char] (1) NULL , [DNSEQN] [int] NULL , [DNBK06] [char] (6) NULL , [DNFLAG] [char] (1) NULL , [BatchID] [int] NULL , [Tooth] [char] (2) NULL , [Surface] [char] (5) NULL , [DNTTH2] [char] (2) NULL , [DNSUR2] [char] (5) NULL , [DNTTH3] [char] (2) NULL , [DNSUR3] [char] (5) NULL , [DNTTH4] [char] (2) NULL , [DNSUR4] [char] (5) NULL , [DNTTH5] [char] (2) NULL , [DNSUR5] [char] (5) NULL , [Location] [char] (4) NULL , [DNCDAT] [int] NULL , [User] [varchar] (10) NULL , [DNUQID] [int] NULL , [DNBL19] [varchar] (19) NULL , [DNPTS] [real] NULL , [DNGRP] [int] NULL , [DNCMDT] [int] NULL , [OFFICE_NUM] [int] NULL , [TransactionDate] [datetime] NULL , [CompletionDate] [datetime] NULL , [PostingDate] [datetime] NULL ) ON [PRIMARY] --------------------------- PRIMARY KEY ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED ( [PatientID], [PatientType], [DNTYY], [DNTMM], [DNTDD], [DNSEQ], [OFFICE_NUM] ) ON [PRIMARY] ---------------------------- INDEXES CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY] CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON [PRIMARY] CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY] CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON [PRIMARY] CREATE INDEX [PROCEDURESUFFIX] ON [dbo].[tbl_AHSTDN]([ProcedureSuffix]) ON [PRIMARY] CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY] CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY] CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON [PRIMARY] CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY] CREATE INDEX [TRANSACTIONDATE] ON [dbo].[tbl_AHSTDN]([TransactionDate]) ON [PRIMARY] CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate]) ON [PRIMARY] CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON [PRIMARY] -- added on 7 December 2004 to improve performance on clinic attendance stored procedures CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID, TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY] -- added on 20 January 2005 to improve performance on ticket count reports for PBO CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM, Location, [User], TicketID, PostingDate, Status) ON [PRIMARY] First thing, that's a heckuva lot of nullable columns. Even your PRIMARY
KEY columns are all nullable!? :( I don't know if it's just me, but I also don't see a clustered index on this table anywhere either :(. Anyways, here's one suggestion - before the BULK INSERT drop all indexes (except the clustered index should you decide to add one), do the BULK INSERT, and then rebuild the indexes. At the very least I would imagine the table could stand to be reindexed big time. One more quick suggestion - check to see if the database size is near the upper limit; i.e., is Auto-Grow likely to kick in during the BULK INSERT process? If so, resize the database to make it bigger. And if possible use the simple recovery model for this database. Show quote "Carl Imthurn" <nospam@all.thanks> wrote in message news:%23g6sjYWlGHA.1208@TK2MSFTNGP02.phx.gbl... > Here's the DDL for table/PK/index creation: > The table data gets sucked out of an AS/400 every morning into text files > and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is not > subject to updates; ie, it's a static table. > > Thanks in advance -- > > Carl > > CREATE TABLE [dbo].[tbl_AHSTDN] ( > [DNHSTD] [char] (1) NULL , > [PatientID] [int] NULL , > [PatientType] [int] NULL , > [DNTYY] [int] NULL , > [DNTMM] [int] NULL , > [DNTDD] [int] NULL , > [DNSEQ] [int] NULL , > [DNID] [int] NULL , > [DNIDTY] [char] (1) NULL , > [TicketID] [int] NULL , > [ProcedureID] [int] NULL , > [ProcedureSuffix] [int] NULL , > [DNTICX] [int] NULL , > [Discipline] [varchar] (100) NULL , > [SessionID] [int] NULL , > [Grade] [int] NULL , > [DNMTHS] [int] NULL , > [StudentID] [char] (3) NULL , > [DNCGCD] [char] (1) NULL , > [DOCMASID] [int] NULL , > [DollarAmount] [money] NULL , > [DNIN01] [int] NULL , > [DNDAT1] [int] NULL , > [DNIN02] [int] NULL , > [DNDAT2] [int] NULL , > [DNCLM_NUM] [char] (5) NULL , > [Status] [char] (1) NULL , > [DNFILE] [char] (1) NULL , > [DNSEQN] [int] NULL , > [DNBK06] [char] (6) NULL , > [DNFLAG] [char] (1) NULL , > [BatchID] [int] NULL , > [Tooth] [char] (2) NULL , > [Surface] [char] (5) NULL , > [DNTTH2] [char] (2) NULL , > [DNSUR2] [char] (5) NULL , > [DNTTH3] [char] (2) NULL , > [DNSUR3] [char] (5) NULL , > [DNTTH4] [char] (2) NULL , > [DNSUR4] [char] (5) NULL , > [DNTTH5] [char] (2) NULL , > [DNSUR5] [char] (5) NULL , > [Location] [char] (4) NULL , > [DNCDAT] [int] NULL , > [User] [varchar] (10) NULL , > [DNUQID] [int] NULL , > [DNBL19] [varchar] (19) NULL , > [DNPTS] [real] NULL , > [DNGRP] [int] NULL , > [DNCMDT] [int] NULL , > [OFFICE_NUM] [int] NULL , > [TransactionDate] [datetime] NULL , > [CompletionDate] [datetime] NULL , > [PostingDate] [datetime] NULL > ) ON [PRIMARY] > > --------------------------- PRIMARY KEY > > ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD > CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED > ( > [PatientID], > [PatientType], > [DNTYY], > [DNTMM], > [DNTDD], > [DNSEQ], > [OFFICE_NUM] > ) ON [PRIMARY] > > ---------------------------- INDEXES > > CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY] > CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON > [PRIMARY] > CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY] > CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON > [PRIMARY] > CREATE INDEX [PROCEDURESUFFIX] ON [dbo].[tbl_AHSTDN]([ProcedureSuffix]) > ON [PRIMARY] > CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY] > CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY] > CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON > [PRIMARY] > CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY] > CREATE INDEX [TRANSACTIONDATE] ON [dbo].[tbl_AHSTDN]([TransactionDate]) > ON [PRIMARY] > CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate]) ON > [PRIMARY] > CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON > [PRIMARY] > > -- added on 7 December 2004 to improve performance on clinic attendance > stored procedures > CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID, > TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY] > > -- added on 20 January 2005 to improve performance on ticket count reports > for PBO > CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM, > Location, [User], TicketID, PostingDate, Status) ON [PRIMARY] Hi Mike --
Thanks for your reply. Actually, this stored procedure has been running without a hitch for so long that I had to go back and refresh my memory about the columns, NULLs, etc. Here's what happens: 1) The table is dropped and recreated every morning with no indexes or primary keys 2) The data is fed in from text files via BULK INSERT 3) The nullable columns in the primary key are modified to be NOT NULL 4) The primary key is added 5) The indexes are added No clustered index -- I need to rectify that one. Thanks for catching it. And, since the bulk insert is done and then indexes are added, do I need to do a reindex? Anyway, I appreciate your time -- I will keep at it to figure out why it works in SQL7 but not in SQL2000 Carl Mike C# wrote: Show quote > First thing, that's a heckuva lot of nullable columns. Even your PRIMARY > KEY columns are all nullable!? :( I don't know if it's just me, but I also > don't see a clustered index on this table anywhere either :(. > > Anyways, here's one suggestion - before the BULK INSERT drop all indexes > (except the clustered index should you decide to add one), do the BULK > INSERT, and then rebuild the indexes. > > At the very least I would imagine the table could stand to be reindexed big > time. One more quick suggestion - check to see if the database size is near > the upper limit; i.e., is Auto-Grow likely to kick in during the BULK INSERT > process? If so, resize the database to make it bigger. And if possible use > the simple recovery model for this database. > In adddition to adding the clustered index, if this database has been
updated from SQL7 to SQL2K, you should update the statistics (see the sp_updatestats stored procedure documentation in BOL) after the conversion. If you have not already done this, you should. WARNING, if you have a large database, this can take a considerable length of time. Also, look at the query plans for the updates in both databases and see if there is any differences. If there are, you may need different indexes on SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might consider using index hints if that signifigantly improves performance. As an aside, it seems a shame to be moving to SQL2K at this time, expecially if you are having trouble with it. Any posibility of going directly to SQL 2005? Tom Show quote "Carl Imthurn" <nospam@all.thanks> wrote in message news:us5wC1WlGHA.1240@TK2MSFTNGP04.phx.gbl... > Hi Mike -- > > Thanks for your reply. Actually, this stored procedure has been running > without a hitch for so long that I had to go back and refresh my memory > about the columns, NULLs, etc. > Here's what happens: > 1) The table is dropped and recreated every morning with no indexes or > primary keys > 2) The data is fed in from text files via BULK INSERT > 3) The nullable columns in the primary key are modified to be NOT NULL > 4) The primary key is added > 5) The indexes are added > > No clustered index -- I need to rectify that one. Thanks for catching it. > And, since the bulk insert is done and then indexes are added, do I need > to do a reindex? > Anyway, I appreciate your time -- I will keep at it to figure out why it > works in SQL7 but not in SQL2000 > > Carl > > Mike C# wrote: >> First thing, that's a heckuva lot of nullable columns. Even your PRIMARY >> KEY columns are all nullable!? :( I don't know if it's just me, but I >> also don't see a clustered index on this table anywhere either :(. >> >> Anyways, here's one suggestion - before the BULK INSERT drop all indexes >> (except the clustered index should you decide to add one), do the BULK >> INSERT, and then rebuild the indexes. >> >> At the very least I would imagine the table could stand to be reindexed >> big time. One more quick suggestion - check to see if the database size >> is near the upper limit; i.e., is Auto-Grow likely to kick in during the >> BULK INSERT process? If so, resize the database to make it bigger. And >> if possible use the simple recovery model for this database. >> Tom --
Thanks for your help. I updated the statistics -- no improvement. I will look at the query plans and check for differences. I used the index tuning wizard in SQL2K and it suggested an additional index on tbl_AHSTDN.Status (that column is already indexed, but only in conjunction with other columns in a composite index). I tried that with no success. I will also check out index hints to see if that makes a difference. And as far as SQL2005 goes, I would like to, but need to get a little more up-to-speed with it first. Thanks again -- I appreciate your time. Carl Tom Cooper wrote: Show quote > In adddition to adding the clustered index, if this database has been > updated from SQL7 to SQL2K, you should update the statistics (see the > sp_updatestats stored procedure documentation in BOL) after the conversion. > If you have not already done this, you should. WARNING, if you have a large > database, this can take a considerable length of time. > > Also, look at the query plans for the updates in both databases and see if > there is any differences. If there are, you may need different indexes on > SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might > consider using index hints if that signifigantly improves performance. > > As an aside, it seems a shame to be moving to SQL2K at this time, expecially > if you are having trouble with it. Any posibility of going directly to SQL > 2005? > > Tom > sp_updatestats is weak, IMHO. Try doing an UPDATE STATISTICS WITH FULLSCAN.
That made a huge difference for me. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Toronto, ON Canada .. "Carl Imthurn" <nospam@all.thanks> wrote in message Thanks for your help. I updated the statistics -- no improvement.news:OYuDJOYlGHA.380@TK2MSFTNGP05.phx.gbl... Tom -- I will look at the query plans and check for differences. I used the index tuning wizard in SQL2K and it suggested an additional index on tbl_AHSTDN.Status (that column is already indexed, but only in conjunction with other columns in a composite index). I tried that with no success. I will also check out index hints to see if that makes a difference. And as far as SQL2005 goes, I would like to, but need to get a little more up-to-speed with it first. Thanks again -- I appreciate your time. Carl Tom Cooper wrote: Show quote > In adddition to adding the clustered index, if this database has been > updated from SQL7 to SQL2K, you should update the statistics (see the > sp_updatestats stored procedure documentation in BOL) after the > conversion. > If you have not already done this, you should. WARNING, if you have a > large > database, this can take a considerable length of time. > > Also, look at the query plans for the updates in both databases and see if > there is any differences. If there are, you may need different indexes on > SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might > consider using index hints if that signifigantly improves performance. > > As an aside, it seems a shame to be moving to SQL2K at this time, > expecially > if you are having trouble with it. Any posibility of going directly to > SQL > 2005? > > Tom > Thanks for the suggestion Tom -- I ran a UPDATE STATISTICS tbl_AHSTDN
WITH FULLSCAN and the SP still ran until I cancelled it (more than 30 minutes -- it should finish within 2-3 minutes max). I'm beginning to wonder if there's something different about creating a database in SQL2K as compared to SQL7? Anyway, I'll keep plugging away at this one and post back when (and if) I get it figured out. Thanks again for your time! Carl Tom Moreau wrote: Show quote > sp_updatestats is weak, IMHO. Try doing an UPDATE STATISTICS WITH FULLSCAN. > That made a huge difference for me. > Hmmm... I thought the table was static - and that new records were just
being appended to the end. If you're dropping and recreating it each time, then no need to reindex. Just create a clustered index before you start the BULK INSERT, and create your other indexes after the fact. I'm not sure where the speed problem is coming from. You don't happen to be pulling the file from across the network (mapped drive or something)? And your database is large enough to accomodate the new data (i.e., it's not autoshrinking and autogrowing)? Show quote "Carl Imthurn" <nospam@all.thanks> wrote in message news:us5wC1WlGHA.1240@TK2MSFTNGP04.phx.gbl... > Hi Mike -- > > Thanks for your reply. Actually, this stored procedure has been running > without a hitch for so long that I had to go back and refresh my memory > about the columns, NULLs, etc. > Here's what happens: > 1) The table is dropped and recreated every morning with no indexes or > primary keys > 2) The data is fed in from text files via BULK INSERT > 3) The nullable columns in the primary key are modified to be NOT NULL > 4) The primary key is added > 5) The indexes are added > > No clustered index -- I need to rectify that one. Thanks for catching it. > And, since the bulk insert is done and then indexes are added, do I need > to do a reindex? > Anyway, I appreciate your time -- I will keep at it to figure out why it > works in SQL7 but not in SQL2000 > > Carl > > Mike C# wrote: >> First thing, that's a heckuva lot of nullable columns. Even your PRIMARY >> KEY columns are all nullable!? :( I don't know if it's just me, but I >> also don't see a clustered index on this table anywhere either :(. >> >> Anyways, here's one suggestion - before the BULK INSERT drop all indexes >> (except the clustered index should you decide to add one), do the BULK >> INSERT, and then rebuild the indexes. >> >> At the very least I would imagine the table could stand to be reindexed >> big time. One more quick suggestion - check to see if the database size >> is near the upper limit; i.e., is Auto-Grow likely to kick in during the >> BULK INSERT process? If so, resize the database to make it bigger. And >> if possible use the simple recovery model for this database. >> This can't be the actual DDL. You cannot put the primary key on a NULLable
column: Msg 8111, Level 16, State 1, Line 1 Cannot define PRIMARY KEY constraint on nullable column in table 'tbl_AHSTDN'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors. -- Show quoteHTH Kalen Delaney, SQL Server MVP "Carl Imthurn" <nospam@all.thanks> wrote in message news:%23g6sjYWlGHA.1208@TK2MSFTNGP02.phx.gbl... > Here's the DDL for table/PK/index creation: > The table data gets sucked out of an AS/400 every morning into text files > and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is not > subject to updates; ie, it's a static table. > > Thanks in advance -- > > Carl > > CREATE TABLE [dbo].[tbl_AHSTDN] ( > [DNHSTD] [char] (1) NULL , > [PatientID] [int] NULL , > [PatientType] [int] NULL , > [DNTYY] [int] NULL , > [DNTMM] [int] NULL , > [DNTDD] [int] NULL , > [DNSEQ] [int] NULL , > [DNID] [int] NULL , > [DNIDTY] [char] (1) NULL , > [TicketID] [int] NULL , > [ProcedureID] [int] NULL , > [ProcedureSuffix] [int] NULL , > [DNTICX] [int] NULL , > [Discipline] [varchar] (100) NULL , > [SessionID] [int] NULL , > [Grade] [int] NULL , > [DNMTHS] [int] NULL , > [StudentID] [char] (3) NULL , > [DNCGCD] [char] (1) NULL , > [DOCMASID] [int] NULL , > [DollarAmount] [money] NULL , > [DNIN01] [int] NULL , > [DNDAT1] [int] NULL , > [DNIN02] [int] NULL , > [DNDAT2] [int] NULL , > [DNCLM_NUM] [char] (5) NULL , > [Status] [char] (1) NULL , > [DNFILE] [char] (1) NULL , > [DNSEQN] [int] NULL , > [DNBK06] [char] (6) NULL , > [DNFLAG] [char] (1) NULL , > [BatchID] [int] NULL , > [Tooth] [char] (2) NULL , > [Surface] [char] (5) NULL , > [DNTTH2] [char] (2) NULL , > [DNSUR2] [char] (5) NULL , > [DNTTH3] [char] (2) NULL , > [DNSUR3] [char] (5) NULL , > [DNTTH4] [char] (2) NULL , > [DNSUR4] [char] (5) NULL , > [DNTTH5] [char] (2) NULL , > [DNSUR5] [char] (5) NULL , > [Location] [char] (4) NULL , > [DNCDAT] [int] NULL , > [User] [varchar] (10) NULL , > [DNUQID] [int] NULL , > [DNBL19] [varchar] (19) NULL , > [DNPTS] [real] NULL , > [DNGRP] [int] NULL , > [DNCMDT] [int] NULL , > [OFFICE_NUM] [int] NULL , > [TransactionDate] [datetime] NULL , > [CompletionDate] [datetime] NULL , > [PostingDate] [datetime] NULL > ) ON [PRIMARY] > > --------------------------- PRIMARY KEY > > ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD > CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED > ( > [PatientID], > [PatientType], > [DNTYY], > [DNTMM], > [DNTDD], > [DNSEQ], > [OFFICE_NUM] > ) ON [PRIMARY] > > ---------------------------- INDEXES > > CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY] > CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON > [PRIMARY] > CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY] > CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON > [PRIMARY] > CREATE INDEX [PROCEDURESUFFIX] ON [dbo].[tbl_AHSTDN]([ProcedureSuffix]) > ON [PRIMARY] > CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY] > CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY] > CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON > [PRIMARY] > CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY] > CREATE INDEX [TRANSACTIONDATE] ON [dbo].[tbl_AHSTDN]([TransactionDate]) > ON [PRIMARY] > CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate]) ON > [PRIMARY] > CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON > [PRIMARY] > > -- added on 7 December 2004 to improve performance on clinic attendance > stored procedures > CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID, > TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY] > > -- added on 20 January 2005 to improve performance on ticket count reports > for PBO > CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM, > Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]  |
|||||||||||||||||||||||
Other interesting topics