May 8, 2012 at 6:21 pm
The Identity constraint applied to the column that normally (at least throughout the rest of the database which has this column in it) is the Primary Key.
My question is - shouldn't this be an easy fix? I cannot think of a good reason for it not to have a PKey on it (even with the clustered index being on another column and a non-clustered index being on the one that normally has the PKey and Clustered index in every other table where this column appears in the database).
Here is the T-SQL...
Table
USE [MyDatabase]
GO
/****** Object: Table [dbo].[BW_RECORD_TEMPERATURE] Script Date: 05/08/2012 17:16:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BW_RECORD_TEMPERATURE](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[EVENT_ID_FK] [int] NOT NULL,
[TEMP1] [float] NULL,
[TEMP2] [float] NULL,
[TEMP3] [float] NULL,
[TEMP4] [float] NULL
) ON [FG8]
GO
ALTER TABLE [dbo].[BW_RECORD_TEMPERATURE] ADD CONSTRAINT [DF_BW_RECORD_TEMPERATURE_TEMP1] DEFAULT ((1000.0)) FOR [TEMP1]
GO
ALTER TABLE [dbo].[BW_RECORD_TEMPERATURE] ADD CONSTRAINT [DF_BW_RECORD_TEMPERATURE_TEMP2] DEFAULT ((1000.0)) FOR [TEMP2]
GO
ALTER TABLE [dbo].[BW_RECORD_TEMPERATURE] ADD CONSTRAINT [DF_BW_RECORD_TEMPERATURE_TEMP3] DEFAULT ((1000.0)) FOR [TEMP3]
GO
ALTER TABLE [dbo].[BW_RECORD_TEMPERATURE] ADD CONSTRAINT [DF_BW_RECORD_TEMPERATURE_TEMP4] DEFAULT ((1000.0)) FOR [TEMP4]
GO
Clustered Index
USE [MyDatabase]
GO
/****** Object: Index [IX_BW_RECORD_TEMPERATURE] Script Date: 05/08/2012 17:18:13 ******/
CREATE CLUSTERED INDEX [IX_BW_RECORD_TEMPERATURE] ON [dbo].[BW_RECORD_TEMPERATURE]
(
[EVENT_ID_FK] 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 [FG8]
GO
Non-Clustered Index
USE [MyDatabase]
GO
/****** Object: Index [PK_BW_RECORD_TEMPERATURE] Script Date: 05/08/2012 17:18:57 ******/
CREATE NONCLUSTERED INDEX [PK_BW_RECORD_TEMPERATURE] ON [dbo].[BW_RECORD_TEMPERATURE]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NC7]
GO
It's kind of odd, but this is how it exists in PROD today. Looks like more bad development to me, but just want to be sure.
Thanks!
May 9, 2012 at 7:54 am
Wow your project seems to be full of bad development choices. The column names too are painful. Do all the tables have a column named ID that is the primary key and then when it is referenced as a foreign key it gets a name that means something? That is so painful. This certainly breaks the idea of a data point having 1 name throughout the system. There is little more frustrating than trying to chase down what a certain column is named "this time". We have a bunch of that stuff in our system and it just drives me insane.
I have to agree with your assessment. Not sure why the clustered index is on the foreign key but it is ok. There is nothing that says your primary key has to have a clustered index.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2012 at 11:03 am
Actually - no FKeys on this table...I was just wondering if I shouldn't put a the PKey on the ID column (every other table in this mess has it as the PKey, except for this one - thus far :crazy:).
May 9, 2012 at 2:35 pm
I just decided to put the PKey on the column, and leave the Non-Clustered index on there as well. I need to see what the reasonsing was, but at least that should help for the Identity Column.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply