Expect to have to do lots of tweaking to the code to handle all the things that come up, though.

NULL / NOT NULL for each column (which...

I don't think it helps that there are many, on certain websites (cough Stack overflow cough) that don't help the situation by answering the questions for these people,...

It is possible, however unlikely, that when the query plan was recompiled after the index was created that the optimizer determined that it actually wasn't needed and found a different...

Expect to have to do lots of tweaking to the code to handle all the things that come up, though.

NULL / NOT NULL for each column (which should always...

• This reply was modified 5 years, 9 months ago by Lynn Pettis.
• This reply was modified 5 years, 9 months ago by Lynn Pettis.

To start with the last part of your post, low resources.  I am not sure if low resources would cause deadlocks, but at the same time not saying it couldn't. ...

Since it isn't always possible to write your code to prevent deadlocks, you should write your code to trap for the deadlock error and retry the failed transaction.  This sounds...

Thanks, Scott, those are excellent points. I am considering them as we speak. In this case, the tables all seem to be quite similar (i.e., the app uses a...

Correct.  I do the same thing for an application where the user app does not have the direct ability to insert or update data in tables.  This application uses stored...

Create a stored procedure to create the lookup table.  The create procedure would start looking like this:

CREATE PROCEDURE <schemaname>.<procedurename> [parmeter [, parameter]...]
WITH EXECUTE AS OWNER
AS
<procedure body>

You would...

I have a stored procedure that run a number of update statements using table function to select 10% of the records based on the year the data was...

Looks like I was able to help the OP.  Yea me!

You would think with all the dynamic SQL I have to write I'd count the single quotes correctly.  Try either of the set comments below, see which one works.

            
December 4, 2019 at 11:00 pm
            
#3704551

            
            
                                                
        

I can't test the openquery part of your code but I am pretty sure that the double quotes are your problem.  I also added a bit to your sp_executesql that...

See what happens when you make the following change:

ALTER FUNCTION [dbo].[GetTenPercent_Moss]
(
@parentId int,
@sampleyear int
)
RETURNS TABLE
AS
RETURN
(
  SELECT  TOP (10) PERCENT
       ...

I wonder whether I will be cast out from The Thread as a maverick grammar nutjob for suggesting that NSO would be a better abbreviation, given that 'verbal' means...