November 12, 2014 at 1:03 pm
In a Stored Proc I am creating the following temp table and index:
CREATE TABLE [dbo].[#ShipTo](
[Ship_to_Num] [int] NOT NULL,
[Country_key] [nvarchar](3) NULL,
CONSTRAINT [PK_ShipTo] PRIMARY KEY CLUSTERED
(
[ship_to_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The stored Proc runs fine from "exec", but when you batch into a Job it gives the error that "PK_ShipTo" already exists! I even put in a drop table on #ShipTo, but the same effect.
November 12, 2014 at 1:22 pm
Don't name the constraint. Unlike temp table names, constraint names aren't 'uniqified', so by naming it you're ensuring that it is impossible for two sessions to run that proc at the same time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2014 at 1:38 pm
Gail,
How do you not name the constraint?
I get the following error : An object or column name is missing or empty.
If I try the following:
CONSTRAINT [] PRIMARY KEY CLUSTERED
(
[ship_to_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY
November 12, 2014 at 1:42 pm
Get rid of the square brackets after the word constraint. So instead of "CONSTRAINT [] PRIMARY KEY CLUSTERED " have "CONSTRAINT PRIMARY KEY CLUSTERED "
November 12, 2014 at 1:45 pm
Since it's a one column key, you can put the constraint on the column definition:
CREATE TABLE [dbo].[#ShipTo]
([Ship_to_Num] [int] not null PRIMARY KEY CLUSTERED,
[Country_key] [nvarchar](3) NULL)
Don Simpson
November 12, 2014 at 1:46 pm
Drop the [] where the name would go.
CREATE TABLE [dbo].[#ShipTo](
[Ship_to_Num] [int] NOT NULL,
[Country_key] [nvarchar](3) NULL,
PRIMARY KEY CLUSTERED
(
[ship_to_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
November 12, 2014 at 2:44 pm
dogramone (11/12/2014)
Get rid of the square brackets after the word constraint. So instead of "CONSTRAINT [] PRIMARY KEY CLUSTERED " have "CONSTRAINT PRIMARY KEY CLUSTERED "
Ok, thanks...
CREATE TABLE [dbo].[#ShipTo](
[Ship_to_Num] [int] NOT NULL,
[Country_key_2] [nvarchar](3) NULL,
PRIMARY KEY CLUSTERED
(
[ship_to_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
November 12, 2014 at 2:48 pm
DonlSimpson (11/12/2014)
Since it's a one column key, you can put the constraint on the column definition:
CREATE TABLE [dbo].[#ShipTo]
([Ship_to_Num] [int] not null PRIMARY KEY CLUSTERED,
[Country_key] [nvarchar](3) NULL)
Forgot about that.... used to having more in a index... thanks.
April 1, 2015 at 2:44 pm
I have not run across a problem.. yet.. but does this problem also cover indexes on temp tables? Thus is there any issue with me doing the following?
CREATE TABLE [dbo].[#History](
[Extract_Date] [date] NOT NULL,
[Plant_ID] [nvarchar](4) NULL,
[Order_Type] [nvarchar](4) NULL,
[Material_Number] [nvarchar](200) NULL,
[Work_Center] [nvarchar](9) NULL,
[Version_ID] [nvarchar](9) NULL,
[Avg_Std_Batch_Time] [real] NULL,
[Avg_Act_Batch_Time] [real] NULL,
[Avg_Act_Batch_Size] [real] NULL,
[Nbr_Batches_in_period] [real] NULL,
[Total_QT_Produced] [real] NULL,
[Month] [datetime] NULL,
[Lot] [nvarchar](12) NULL,
[Day_DT_CD] [datetime] NULL,
[Concatenation] [nvarchar](216) NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [#X_History] ON [dbo].[#History]
(
[Extract_Date] ASC,
[Day_DT_CD] ASC,
[Plant_ID] ASC,
[Material_Number] ASC,
[Work_Center] ASC,
[Lot] 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
USE [Zemeter.NET]
GO
/****** Object: Index [XI_GlobalControlSchedule_PRODRATEVER_40_History] Script Date: 04/01/2015 16:32:23 ******/
CREATE NONCLUSTERED INDEX [#XI_History] ON [dbo].[#History]
(
[Day_DT_CD] ASC
)
INCLUDE ( [Extract_Date],
[Plant_ID],
[Material_Number],
[Work_Center],
[Lot]) 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
April 1, 2015 at 2:50 pm
BTW I am doing this so I can clean up the nvarchar's, and avoid doing this in the Join's and the where, so the data will match what is needed. Interesting.. changing the three sql statements, (that use this table), with all the called functions to clean up the code... takes longer with the addition of writing the data into this temp table. So back to the drawing board on how to improve the 10 min run time. (we want to run this data load every 20 min)
April 2, 2015 at 10:28 am
dwilliscp (4/1/2015)
I have not run across a problem.. yet.. but does this problem also cover indexes on temp tables? Thus is there any issue with me doing the following?
CREATE TABLE [dbo].[#History](
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [#X_History] ON [dbo].[#History]
(
[Extract_Date] ASC,
[Day_DT_CD] ASC,
[Plant_ID] ASC,
[Material_Number] ASC,
[Work_Center] ASC,
[Lot] ASC
)WITH ([removed index options to save space]) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [#XI_History] ON [dbo].[#History]
(
[Day_DT_CD] ASC
)
INCLUDE ( [Extract_Date],
[Plant_ID],
[Material_Number],
[Work_Center],
[Lot]) WITH (([removed index options to save space]) ON [PRIMARY]
You can't name the indexes yourself unless you insure they're unique. It's a pain, but using dynamic SQL you can do it.
It's typically overkill on a temp table to have a clustered index and a very similar nonclustered index. Are you sure the clustered index itself just shouldn't start with [Day_DT_CD]? Carefully review the clustered index to make sure it is the best index possible, and hopefully you can dispense with the nonclus index.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 2, 2015 at 10:29 am
dwilliscp (4/1/2015)
I have not run across a problem.. yet.. but does this problem also cover indexes on temp tables?
No. Index names are only unique within the table. You can have 100 tables each with an idx_Test on them, no problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2015 at 10:31 am
ScottPletcher (4/2/2015)
You can't name the indexes yourself unless you insure they're unique. It's a pain, but using dynamic SQL you can do it.
That's the case for constraints, not for indexes. Constraint names have to be unique in the database, indexes only unique within the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2015 at 10:37 am
GilaMonster (4/2/2015)
ScottPletcher (4/2/2015)
You can't name the indexes yourself unless you insure they're unique. It's a pain, but using dynamic SQL you can do it.That's the case for constraints, not for indexes. Constraint names have to be unique in the database, indexes only unique within the table.
D'OH, quite right. If the name is not in sys.objects, or another "shared name" table, it doesn't have to be unique.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 3, 2015 at 8:13 am
Ok, thanks for the help.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply