October 18, 2022 at 7:18 am
Hi all,
I have a following SQL query:
UPDATE Individual_TEMP
SET Individual_Id = CONCAT (Record_ID,'-',Individual_Id),
Ind_PK_Value_Updated =1
where Ind_PK_Value_Updated is null;
Objects involved in query:
October 18, 2022 at 7:37 am
can you post the table DDL (including indexes/PK) and explain the process of inserting and updating this table BEFORE you try to do the update above.
what you say above looks a bit unconsistent for me (e.g. you only have an index on individual_id (PK) yet a PK by its nature should not be updated like this (unless you are doing a bulk conversion/update due to system changes.
but assuming the process is ok a filtered index on Ind_PK_Value_Updated should be all you need - no other columns
October 18, 2022 at 11:54 am
can you post the table DDL (including indexes/PK) and explain the process of inserting and updating this table BEFORE you try to do the update above.
what you say above looks a bit unconsistent for me (e.g. you only have an index on individual_id (PK) yet a PK by its nature should not be updated like this (unless you are doing a bulk conversion/update due to system changes.
but assuming the process is ok a filtered index on Ind_PK_Value_Updated should be all you need - no other columns
I am using XML source to read various XML files and inserting their data into the database.
Problem is each file generates a random 'Individual_Id' on insert process.
So file 1 fetched a record whose Individual_Id was 202.
File 3 in next iteration of for each loop fetched another record and it gave it same 202 id.
So, what I do is, when I have finished inserting records of file 1, before fetching new file data, I create my own unique values for this column which is going to be the PK and will link with its child tables.
Let me try this "filtered index" approach, if it doesnt work, I will share the DDL.
October 18, 2022 at 2:18 pm
There are a number of restrictions and possible gotchas with filtered indexes. In theory they are great, in practice they can be difficult.
Until you've had time to fully test out whether a filtered index works cleanly or not in your environment, I'd suggest an index like this:
CREATE UNIQUE NONCLUSTERED INDEX [Individual_TEMP__IX_Ind_PK_value_Updated] ON dbo.Individual_TEMP ( Ind_PK_value_Update, Individual_Id ) INCLUDE ( Record_ID ) ON [PRIMARY] /*or other filegroup*/;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 18, 2022 at 6:10 pm
There are a number of restrictions and possible gotchas with filtered indexes. In theory they are great, in practice they can be difficult.
would you care to explain exactly which issues you referring to? some of the db's (including the biggest one we have both in size and activity) on my company use them extensively without any issues for cases like this one and never had any issues with them for many years.
October 18, 2022 at 6:20 pm
ScottPletcher wrote:There are a number of restrictions and possible gotchas with filtered indexes. In theory they are great, in practice they can be difficult.
would you care to explain exactly which issues you referring to? some of the db's (including the biggest one we have both in size and activity) on my company use them extensively without any issues for cases like this one and never had any issues with them for many years.
How lucky y'all are!
MS's CREATE INDEX documentation has all the restrictions for filtered indexes. And the possibility of failed modifications (DELETE / INSERT /UPDATE) if they aren't met.
For example, one restriction is ANSI_WARNINGS ON. Yes, normally you would like to have that on, but sometimes, for example when doing large data transfers from outside sources, we turn it off so that truncated data doesn't cause the run to fail.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 18, 2022 at 8:05 pm
frederico_fonseca wrote:ScottPletcher wrote:There are a number of restrictions and possible gotchas with filtered indexes. In theory they are great, in practice they can be difficult.
would you care to explain exactly which issues you referring to? some of the db's (including the biggest one we have both in size and activity) on my company use them extensively without any issues for cases like this one and never had any issues with them for many years.
How lucky y'all are!
MS's CREATE INDEX documentation has all the restrictions for filtered indexes. And the possibility of failed modifications (DELETE / INSERT /UPDATE) if they aren't met.
For example, one restriction is ANSI_WARNINGS ON. Yes, normally you would like to have that on, but sometimes, for example when doing large data transfers from outside sources, we turn it off so that truncated data doesn't cause the run to fail.
Not lucky - we know what we are doing, we read the manuals and we check the implications - those that consider something like this to be "lucky" likely don't follow the rules (of which there are a lot more for different aspects of SQL server) and then complain when it fails and don't use the functionality.
with regards to the particular load example you mention - it SHOULD fail - or you end up with bad data on your db that can cause you to reload after you either remove the offending data or increase the size of the destination columns. Yes its a pain but solution again is easy (even if slower) - setup staging tables with the sizes big enough so it won't truncate - and then process and report on the "bad" data.
October 18, 2022 at 8:08 pm
What I am trying to do with above Update query is that I am updating value of Individual_Id of the those records whose Ind_PK_Value_Updated is null (basically those records which are newly inserted in the table).
It seems the value of Individual_Id is wrong on insert. Why not just have an INSTEAD OF INSERT trigger and sort out the value before it is written to the table?
October 18, 2022 at 8:45 pm
ScottPletcher wrote:frederico_fonseca wrote:ScottPletcher wrote:There are a number of restrictions and possible gotchas with filtered indexes. In theory they are great, in practice they can be difficult.
would you care to explain exactly which issues you referring to? some of the db's (including the biggest one we have both in size and activity) on my company use them extensively without any issues for cases like this one and never had any issues with them for many years.
How lucky y'all are!
MS's CREATE INDEX documentation has all the restrictions for filtered indexes. And the possibility of failed modifications (DELETE / INSERT /UPDATE) if they aren't met.
For example, one restriction is ANSI_WARNINGS ON. Yes, normally you would like to have that on, but sometimes, for example when doing large data transfers from outside sources, we turn it off so that truncated data doesn't cause the run to fail.
Not lucky - we know what we are doing, we read the manuals and we check the implications - those that consider something like this to be "lucky" likely don't follow the rules (of which there are a lot more for different aspects of SQL server) and then complain when it fails and don't use the functionality.
with regards to the particular load example you mention - it SHOULD fail - or you end up with bad data on your db that can cause you to reload after you either remove the offending data or increase the size of the destination columns. Yes its a pain but solution again is easy (even if slower) - setup staging tables with the sizes big enough so it won't truncate - and then process and report on the "bad" data.
You're just overly smug. The rules for filtered indexes didn't exist before filtered indexes did -- that's obvious, and obvious that one can't follow rules that don't yet exist.
As to the particular load, no, yours would be a terrible approach for this data. You're not an expert on data you've never seen -- imagine that! The imported data is created by external sources, and we don't control their data but we do, and must, control our own.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 19, 2022 at 4:56 am
Jobs90312 wrote:What I am trying to do with above Update query is that I am updating value of Individual_Id of the those records whose Ind_PK_Value_Updated is null (basically those records which are newly inserted in the table).
It seems the value of Individual_Id is wrong on insert. Why not just have an INSTEAD OF INSERT trigger and sort out the value before it is written to the table?
Can you tell me some more about it? Any example related to my case....?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply