January 10, 2020 at 4:53 pm
Is it okay to have negative values in primary key. Also, is there a script that can be used to find out tables that have negative primary key values across a database. It is going to be hard to tackle it one at a time if I have to do it manually. I hope there would be something out there to do a quick check.
January 10, 2020 at 5:17 pm
Sure, a negative value is just a value.
What is your exact concern?
January 10, 2020 at 6:18 pm
Sure, a negative value is just a value.
What is your exact concern?
My client has requested me to firstly let them know if a negative generated value in the primary key is an issue; in case if it is then we need to find out all the tables that have negative values and then a solution to fix those values. I assume negative value is totally okay but I am not sure how can find it out across tables in the database as there could be many tables and manual inspection is not possible. Hence, was looking for some code in case if the client insists on finding those tables. Also, could yo also let me know how we would end up fixing these tables to eliminate negative values.
January 10, 2020 at 7:36 pm
No there's nothing inherently wrong with negative values in a primary key.
If you wanted to find any you could run a query that would give you a list of all the PK columns in the DB with a number data type and use that to generate a list of selects to run that check for negative values. Something like,
SELECT TOP 1 'dbo.TEST_ONE.COL_ONE' FROM dbo.TEST_ONE WHERE COL_ONE < 0.0
January 10, 2020 at 7:54 pm
it may be possible to find it using sys.dm_db_stats_histogram:
You could use a query such as:
SELECT OBJECT_SCHEMA_NAME(ss.object_id) AS schema_name, ao.name AS table_name, ac.name AS column_name,
ss.name AS stats_name, ss.stats_id, shr.last_updated
FROM sys.stats ss
INNER JOIN sys.all_objects ao ON ss.object_id = ao.object_id
INNER JOIN sys.stats_columns sc ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id
INNER JOIN sys.all_columns ac ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr
CROSS APPLY (SELECT MIN(range_high_key) AS range_high_key FROM sys.dm_db_stats_histogram(ss.object_id, ss.stats_id)) sh
WHERE sh.range_high_key < 0
AND ao.is_ms_shipped = 0
AND ac.system_type_id IN (52,56,127);
just an FYI, it looks like a number of the system tables do use negative key values so that's why I filter is_ms_shipped = 0
January 10, 2020 at 9:05 pm
it may be possible to find it using sys.dm_db_stats_histogram:
You could use a query such as:
SELECT OBJECT_SCHEMA_NAME(ss.object_id) AS schema_name, ao.name AS table_name, ac.name AS column_name,
ss.name AS stats_name, ss.stats_id, shr.last_updated
FROM sys.stats ss
INNER JOIN sys.all_objects ao ON ss.object_id = ao.object_id
INNER JOIN sys.stats_columns sc ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id
INNER JOIN sys.all_columns ac ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr
CROSS APPLY (SELECT MIN(range_high_key) AS range_high_key FROM sys.dm_db_stats_histogram(ss.object_id, ss.stats_id)) sh
WHERE sh.range_high_key < 0
AND ao.is_ms_shipped = 0
AND ac.system_type_id IN (52,56,127);just an FYI, it looks like a number of the system tables do use negative key values so that's why I filter is_ms_shipped = 0
My bad I should've posted this question in sql 2012 forum. The column range_high_key won't work out in 2012 for the dmf.
January 10, 2020 at 9:06 pm
No there's nothing inherently wrong with negative values in a primary key.
If you wanted to find any you could run a query that would give you a list of all the PK columns in the DB with a number data type and use that to generate a list of selects to run that check for negative values. Something like,
SELECT TOP 1 'dbo.TEST_ONE.COL_ONE' FROM dbo.TEST_ONE WHERE COL_ONE < 0.0
I am too bad when it comes to writing queries not that I don't understand it but find it difficult to get the logic right. Hence, I was thinking if someone has something that they might have done in the community that can help me out or others who might have a similar issue.
January 10, 2020 at 9:24 pm
Another question that comes to my mind is the table structure is as below. I am not sure why would it end up having negative values if the identity range starts from (1, 1). Any obvious reasons behind this behavior
CREATE TABLE [dbo].[UserAnswers](
[UserAnswersID] [int] IDENTITY(1,1) NOT NULL,
[AnswerNumeric] [int] NULL,
[AnswerText] [nvarchar](255) NULL,
[AssessmentQuestionID] [int] NULL,
[AssessmentUserID] [int] NULL,
[AnswerDateTime] [smalldatetime] NULL,
CONSTRAINT [PK_UserAnswersActive] PRIMARY KEY NONCLUSTERED
(
[UserAnswersID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
January 11, 2020 at 1:44 pm
Another question that comes to my mind is the table structure is as below. I am not sure why would it end up having negative values if the identity range starts from (1, 1). Any obvious reasons behind this behavior
CREATE TABLE [dbo].[UserAnswers](
[UserAnswersID] [int] IDENTITY(1,1) NOT NULL,
[AnswerNumeric] [int] NULL,
[AnswerText] [nvarchar](255) NULL,
[AssessmentQuestionID] [int] NULL,
[AssessmentUserID] [int] NULL,
[AnswerDateTime] [smalldatetime] NULL,
CONSTRAINT [PK_UserAnswersActive] PRIMARY KEY NONCLUSTERED
(
[UserAnswersID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
Someone could have manually inserted the rows with SET IDENTITY_INSERT ON
For dimension tables on a data-warehouse it's a standard practice to manually insert negative values into the primary to denote default things like missing values. This is used when the column on the table that has the foreign key (the fact table) is NOT NULL so it always has a reference to the table even if there is no real value in the column.
https://www.kimballgroup.com/2010/10/design-tip-128-selecting-default-values-for-nulls/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply