I have 2 tables with the cascade delete rule - [dbo].[Invoices] and [dbo].[InvoiceRows]:

CREATE TABLE [dbo].[Invoices]
(
	[InvoiceId] [int] IDENTITY(1,1) NOT NULL,
	--other columns

    CONSTRAINT [PK_Invoices] 
        PRIMARY KEY CLUSTERED ([InvoiceId] ASC)
)
GO

CREATE TABLE [dbo].[InvoiceRows]
(
	[InvoiceRowId] [int] IDENTITY(1,1) NOT NULL,
	[ProductId] [int] NOT NULL,
	[Price] [money] NOT NULL,
	[Quantity] [int] NOT NULL,
	[InvoiceId] [int] NOT NULL
	--other columns

    CONSTRAINT [PK_InvoiceRows] 
        PRIMARY KEY CLUSTERED ([InvoiceRowId] ASC)
)

ALTER TABLE [dbo].[InvoiceRows] WITH CHECK 
    ADD CONSTRAINT [FK_InvoiceRows_Invoices] 
        FOREIGN KEY([InvoiceId]) REFERENCES [dbo].[Invoices] ([InvoiceId])
             ON UPDATE CASCADE ON DELETE CASCADE
GO

ALTER TABLE [dbo].[InvoiceRows] CHECK CONSTRAINT [FK_InvoiceRows_Invoices]
GO

[](https://i.sstatic.net/2roWnuM6.png) I want any change in the [dbo].[InvoiceRows] to cause a recalculation of some register. For this I added a trigger:

CREATE TRIGGER [dbo].[TrInvoiceRows_Delete_UpdateProductRegister]
ON [dbo].[InvoiceRows]
AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

	PRINT 'TRIGGER Tr_InvoiceRows_Delete_UpdateProductRegister fired'
	
	--trigger logic
END

All triggers fire correctly when I work directly with [dbo].[InvoiceRows]. The triggers work when I delete [dbo].[Invoices] using SSMS. But recently I noticed that deleting [dbo].[Invoices] using Entity Framework generates the following code and the trigger IS NOT FIRED. That is, there are no any errors; it just ignores the trigger.

EXEC sp_executesql
    N'
    SET IMPLICIT_TRANSACTIONS OFF; 
    SET NOCOUNT ON; 
    DELETE FROM [Invoices]
    OUTPUT 1 
    WHERE [InvoiceId] = @p0; ',
    N'@p0 int',
    @p0=19936;

I noticed that the problem is OUTPUT 1 and compared the queries: [](https://i.sstatic.net/Z4ib3OmS.png) I realize that I have many options to fire a trigger on the application side (do not use EF or delete the cascade table rows first). I want to know if it is possible to solve the problem on SQL Server ? That is, make the cascading table delete trigger always fire without any surprises.

1.) @MULTIPLE_RESULTS_TABLEVAR has fields (x, y, OrdersTableID) and values: [a,b,Null], [c,d,Null], [e,f,Null] 2.) Goal is to bulk insert @MULTIPLE_RESULTS_TABLEVAR data into an OrdersTable having fields (id, x, y) with each ORDERS_TABLE.id (aka identity) returned to ***update*** @MULTIPLE_RESULTS_TABLEVAR to make the values: [a,b,1], [c,d,2], [e,f,3] 3.) But using OUTPUT INSERTED.id INTO @MULTIPLE_RESULTS_TABLEVAR ***adds*** new rows to @MULTIPLE_RESULTS_TABLEVAR yielding values: [a,b,Null], [a,b,Null], [a,b,Null], [NULL,NULL,1], [NULL,NULL,2], [NULL,NULL,3] 4.) I can't find a documentation option or non-kludgy strategy to ***UPDATE*** the existing rows. Specifically I don't want to trust a ( LAST_INSERT_@@SCOPE_IDENTITY - count(MULTIPLE_RESULTS_TABLEVAR.id) ) while echoing to a new #temptable or a CURSOR/LOOP to INSERT then UPDATE with @@SCOPE_IDENTITY seems to defeat the whole purpose of **OUTPUT INSERTED**.

**Is there an undocumented restriction in the OUTPUT clause, or is this a bug?** Given the following table:

CREATE TABLE t1 (SomeId int, flag bit, value int);

I would like to use a calculated value in an UPDATE statement, and then use OUTPUT to output the value. Note that the calculated value is not being used in the SET part, which might have allowed a workaround by outputting the left-hand-side column. The following works fine, it is a perfectly standard updatable subquery (derived table).

UPDATE subT1
SET flag = 1
OUTPUT inserted.SomeValue
FROM (
    SELECT *,
      SomeValue = t1.value + 123
    FROM t1
) subT1;

However using a window function gets a weird error:

UPDATE subT1
SET flag = 1
OUTPUT inserted.Sum
FROM (
    SELECT *,
      Sum = SUM(t1.value) OVER (PARTITION BY t1.SomeId)
    FROM t1
) subT1;

Msg 404 Level 16 State 1 Line 3
The column reference "inserted.Sum" is not allowed because it refers to a base table that is not being modified in this statement.

Which doesn't make sense as we already established that calculated columns can be referred to using inserted. OK let's try using OUTPUT subT1.Sum anyway, rather than inserted.Sum:

Msg 4104 Level 16 State 1 Line 3
The multi-part identifier "subT1.Sum" could not be bound.

which makes sense, since that table reference has been modified, and the docs say: > If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix. ____ Meanwhile, if I use a join on a second table:

CREATE TABLE t2 (SomeId int, flag bit, value int);

it works fine

UPDATE t1
SET flag = 1
OUTPUT subT2.Sum
FROM t1
JOIN (
    SELECT t2.*,
      Sum = SUM(t2.value) OVER (PARTITION BY t2.SomeId)
    FROM t2
) subT2 ON subT2.SomeId = t1.SomeId;

dbfiddle **So is the fact that a window function throws that error a bug, or an undocumented restriction?** Having said all that, a join *inside* a subquery is also not allowed, so most likely it is an undocumented restriction.

UPDATE subT1
SET flag = 1
OUTPUT inserted.Sum
FROM (
    SELECT t1.*,
      Sum = t2.value
    FROM t1
    JOIN t2 ON t2.SomeId = t1.SomeId
) subT1;

Msg 404 Level 16 State 1 Line 3
The column reference "inserted.Sum" is not allowed because it refers to a base table that is not being modified in this statement.

I am trying to get SQL version from a linked server and save it in a variable, but the query runs fine and showed me the result but when I review the output variable is Null. DECLARE @cmd NVARCHAR(MAX); DECLARE @servername NVARCHAR(MAX); DECLARE @result NVARCHAR(500); SET @servername = 'SERVERNAME'; SET @cmd = 'SELECT * FROM OPENQUERY([' + @servername + '], ''SELECT @@VERSION'')'; EXEC sp_executesql @cmd, N'@queryresult NVARCHAR(500) OUT', @result OUT; SELECT @result;

Consider the following minimal, complete, and verifiable example code (see [dbfiddle here](https://dbfiddle.uk/qm2q8bkS)) :

CREATE TABLE [dbo].[test]
(
      [i] bigint NOT NULL 
        identity(1,1) 
        PRIMARY KEY CLUSTERED
    , [d] varchar(10) NOT NULL
);
GO

With an INSTEAD OF INSERT, UPDATE trigger:

CREATE TRIGGER [dbo_test_trigger]
ON [dbo].[test]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    IF ROWCOUNT_BIG() = 0 RETURN;

    SET NOCOUNT ON;

    MERGE INTO [dbo].[test] [target]
    USING [inserted] [source] ON [target].[i] = [source].[i]
    WHEN NOT MATCHED THEN
        INSERT
        (
            [d]
        )
        VALUES 
        (
            [source].[d]
        )
    WHEN MATCHED THEN 
        UPDATE
        SET [target].[d] = [source].[d];
END;
GO

I'm running an insert into the table, hoping to get the inserted identity value, however the value returned is 0:

DECLARE @output TABLE
(
      [i] bigint NOT NULL
    , [d] varchar(10) NOT NULL
);

INSERT INTO [dbo].[test]
(
    [d]
)
OUTPUT 
      [inserted].[i]
    , [inserted].[d]
INTO @output 
(
      [i]
    , [d]
) 
VALUES ('test');

/* shows [i] is 0 */
SELECT *
FROM @output;

/* shows [i] is 1 */
SELECT *
FROM [dbo].[test];

Results are: | i | d | |---|------| | 0 | test | and | i | d | |---|------| | 1 | test | The desired outcome would be that both sets of output match, however they don't. What am I doing wrong? I have seen [this](https://dba.stackexchange.com/questions/34258/getting-identity-values-to-use-as-fk-in-an-instead-of-trigger) however that seems quite different since I'm not using a view at all. The trigger is on the table in my example.

For a mature .Net project I support we're currently exploring options to remove Entity Framework Core from the project while still maintaining some of the EF functionality we rely on. At the top of this list is our ability to keep track of exactly which values have changed as a result of an update request that an outside system sends to us - we have no control over that system, and it's not unusual for us to receive update requests that contain identical data several times. We currently use EF's change tracker to keep a view on whether or not the update we're processing really makes any changes to a set of specific columns so that we know whether or not to inform our users of these changes. Looking into this how we might achieve this without having EF and its overhead involved led me to SQL Server's [OUTPUT Clause](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16) , which would theoretically allow us to perform an update to a table and return a view of the prior and updated state of the key columns we use for notifications triggers. So far so good, however, there is a warning under the [Parallelism](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16) section of that document that says that "[an] OUTPUT clause that returns results to the client, or table variable, will always use a serial plan.". I'm afraid that my knowledge of SQL is not strong enough at this time to be able to tell if this is likely to be a problem or not. Should this concern me from a performance or reliability standpoint? Every update that we perform is keyed on a set of columns that form (in almost every case) a compound unique key, so even if the query portion of the update is run serially would it be a noticeable impact? Take as an example the following schema:

CREATE TABLE user_profile
(
  [id] INT IDENTITY (1,1) NOT NULL PRIMARY KEY
  [username] NVARCHAR(100) NOT NULL,
  [tenancy] NVARCHAR(20) NOT NULL,
  [city] NVARCHAR(50) NULL,
  [state] NVARCHAR(50) NULL,
  [first_name] NVARCHAR(50) NULL,
  [last_name] NVARCHAR(50) NULL,
  [external_system_access_key] NVARCHAR(200) NULL,
  CONSTRAINT [UX_user] UNIQUE ([username], [tenancy])
)

In this example, a user manages their own city, state, first_name and last_name values, but an external system manages external_system_access_key through a request to our service like

-none
POST /{tenancyId}/user/{username}/profile/external

{
  "accessKey": "1224567890"
}

If we receive that same update several times without the value of accessKey changing, we're wanting to know if the value differs pre- and post-update execution, so we know whether or not to inform the user that the key has changed. Each request would result in an update like this:

DECLARE @accessKey NVARCHAR(200) = '1234567890';
DECLARE @username NVARCHAR(100) = 'username';
DECLARE @tenancy NVARCHAR(20) = 'tenancy';
UPDATE [user_profile]
SET [remote_system_access_key] = @accessKey
OUTPUT INSERTED.id, DELETED.[remote_system_access_key] AS OLD_remote_system_access_key, INSERTED.[remote_system_access_key] AS NEW_remote_system_access_key
WHERE 
[username] = @username AND [tenancy] = @tenancy;

In the case where the request gave us a new value for that column there would be different values for each of the OLD_ and NEW_ output columns, and if it's a request we've had previously then they will match, allowing us to evaluate any changes after the insert is done. But SQL Server's documentation says that this will always result in a serial execution plan. What I need to know is: is this a problem? Any assistance I could get understanding this and its potential impacts would be greatly appreciated. Generally, each update will only hit one row at a time as its where clause uses a compound unique key. We're just trying to avoid the thing that EF does and require querying the data out first - instead, since SQL server already provides a mechanism to know what the state before and after the update was, we would like to get that data back afterwards if it's not going to cause performance issues. The example I used above is quite simplistic compared to our actual cases, where we'll be updating large numbers of columns in each statement - all in the one table, but multiple columns in each. It would be prohibitively complex to try and cover every possible permutation of what might be updated in each request.

I have a relatively simple question with an implied answer, but not an explicit one. Here's the background. Here are the 3 schemas I'm working with: --Source Data: ProjectID, ProjectName, CompanyName --SQL Tables: Project ( ProjectID [PK\Identity], ProjectName, CompanyID ) Company ( CompanyID [PK\Identity], CompanyName ) Project.CompanyID is a FK to Company.CompanyID. All pretty basic. Now because I have to transform CompanyName in the source data into its normalised form, insertion is a multi-step process. The first step is to merge into Company: MERGE INTO [Company] AS tgt USING (SELECT CompanyName FROM [Source Data]) as src (CompanyName) ON tgt.CompanyName = src.CompanyName WHEN NOT MATCHED THEN INSERT (CompanyName) VALUES (src.CompanyName) OUTPUT inserted.CompanyID ; In a nutshell, if the CompanyName isn't in the table, insert it, and output the identity value. Sensible enough, but not actually useful because the result of the OUTPUT clause isn't ordered. Here's my approach to that: MERGE INTO [Company] AS tgt USING (SELECT ProjectID, CompanyName FROM [Source Data]) as src (ProjectID, CompanyName) ON tgt.CompanyName = src.CompanyName WHEN NOT MATCHED THEN INSERT (CompanyName) VALUES (src.CompanyName) OUTPUT inserted.CompanyID, src.ProjectID ; By using the **from_table_name** syntactical element in the dml_select_list, I can (theoretically) establish a direct relationship between a ProjectID and CompanyID and use that in the second insert into the Project table. My question is - *can I actually trust that relationship?* The documentation is not well written (https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16) : >A column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete. I'm not using a FROM clause for one, and I'm inserting, not updating or deleting for two. It also doesn't actually describe the relationship between the columns. I can assume or infer that there is a direct correlation between inserted.CompanyID and src.ProjectID because without that, the functionality of using a from_table_name seems kind of useless. *So does anyone know the exact nature of the underlying join between **inserted** and **from_table_name**?*

Find all 5 errors in the SQL script below and explain what the script is trying to do select A.BIZ_DT, A.ACTL_PARENT_SVC_NUM, A.DIRCTN_IND, A.BUS_STOP_CD, A.BUS_STOP_ARRVL_TM, A.OPR_ID_NUM, A.REG_NUM, A.BUS_REGISTER_NO, A.TRIP_NUM, B.PSGR_CC_CNT, sum(A.BOARD_CNT) Board, sum(A.ALIGHT_CNT) Alight, A-B NetBoard NetBoard/B.PSGR_CC_CNT as Utilization from PLAP_VIEW_PTQ.V_OD_NET_ON_BOARD_SEP as A LEFT JOIN PLAP_TWM_USERSPACE_PTQ.BUs_fleet_2022 AS B A.BUS_REGISTER_NO=B.BUS_REGISTER_NO AND A.OPR_ID_NUM=B.OPR_ID_NUM where A.SVC_NUM in (147) where A.BUS_STOP_CD in(63199) and A.BIZ_DT='2022-01-04' and '2022-01-09' group 1,2,3,4,5,6,7,8,9,10 The errors that i managed to find are, A.SVC_Num which is wrongly written, '2022-01-09' is wrongly written, and the A-B netboard which is missing. Im still unable to find the remaining 2 errors and interpret what the SQL code above actually means. Could someone help explains. Thanks!

I want to add a record to my app's "SystemSettings" table and set the PK value using the value from an UPDATE. The PK value comes from the "TS_LASTIDS" table which contains the maximum PK value for each table in this application (MicroFocus SBM). I need to increment the "TS_LASTID" column in that table and use the new value as the PK when INSERTING a new record into the "SystemSettings" table. Insert Into ts_SystemSettings ( ts_Id, ts_Name, ts_LongValue) Values ( ( -- ******************** This subquery updates the PK in TS_LASTIDS and outputs the new value Update ts_LastIds Set ts_LastId=ts_LastId+1 Output INSERTED.ts_LastId Where ts_Name = 'SystemSettings' ) , -- ******************** 'NSLastChangeId' , 1 , ) ; I can't figure out the syntax. This is MS SQL server 2012.

I know that the output parameter does not guarantees parallelism logic. And the logic I want to implement is failing in a multi thread environment. To test this, just open 2 windows and execute them side by side as fast as you can. Then compare the Ids, some will be duplicated. declare @id int select @id = 1 while @id >=1 and @id <= 100 begin UPDATE sys_QueueJob SET StartedOn = GETDATE() OUTPUT DELETED.Id as Result WHERE Id = (select top 1 qj.Id from sys_QueueJob qj where qj.ProcessedOn is null AND qj.StartedOn is null order by CreatedOn asc) print 'Count: ' + convert(nvarchar(max), @id) select @id = @id + 1 end I just want to get the oldest entry from the qeueJob table that has not yet being started and at the same time tell that now it has. And the same code needs to provide me 100% certainty that the next execution will give me the next correct Id. How can I do this?

How do you retrieve the identity value for an inserted row when that row is inserted from the OUTPUT of an UPDATE statement? Neither @@IDENTITY nor SCOPE_IDENTITY() appears to be set properly. Consider this code: DECLARE @UpdateTable table (UpdateTableID int IDENTITY, UpdateTableValue int); DECLARE @InsertTable table (InsertTableID int IDENTITY, UpdateTableValue1 int, UpdateTableValue2 int); DECLARE @TestValue int = 5; INSERT INTO @UpdateTable (UpdateTableValue) VALUES (1),(2),(3); SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY(); INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2) SELECT UpdateTableValue1, UpdateTableValue2 FROM ( UPDATE @UpdateTable SET UpdateTableValue = UpdateTableValue + @TestValue OUTPUT deleted.UpdateTableValue, inserted.UpdateTableValue WHERE UpdateTableID = 2 ) AS UpdateResults (UpdateTableValue1, UpdateTableValue2); SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY(); The last-inserted row has an identity value of 1, yet the @@IDENTITY and SCOPE_IDENTITY() functions are returning their original values from the original INSERT prior to the last statement executed. @@VERSION: > Microsoft SQL Azure (RTM) - 12.0.2000.8 May 2 2019 20:11:13 > Copyright (C) 2019 Microsoft Corporation

I would like to do this : DECLARE @Id INT; UPDATE Logins SET SomeField = 'some value' OUTPUT @Id = Id WHERE EmailAddress = @EmailAddress -- this is a parameter of the sproc Is this even possible? I know I can declare a local table variable and direct the output there but I would prefer to skip it if possible

I need to make three select queries over three different tables, using the outputs of each select query, the catch is each one gives multiple results. Here is how I do it. Select "Title", "Num" from "Table" where "Id" in ( Select "Id" from "Table2" where "Id" in ( select distinct "Id" from "Table3" where foo-clause ) ) İt only gives me the result of one of the results. How can I make each one use multiple inputs?

I am writing a batch processing insert statement and would like to use a temp table to keep track of inserted ID's instead of looping through the items myself and calling SCOPE_IDENTITY() for each inserted row. The data that needs to be inserted has (temporary) ID's linking it to other data that also should be inserted into another table, so I need a cross reference of the actual Id and the temporary Id. This is an example of what I have so far: -- The existing table DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX)); -- My data I want to insert DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX)); INSERT INTO @MyInsertData ( ID,Name) VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last'); DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT); INSERT INTO @MyTable ( [Name] ) OUTPUT Inserted.ID, INS.ID INTO @MyCrossRef SELECT [NAME] FROM @MyInsertData INS -- Check the result SELECT * FROM @MyCrossRef The problem is that I cannot get the OUTPUT INTO clause to accept the ID, I've tried @MyInsertData.ID and other tricks joining the table to itself, but nothing seems to work.

Using SQL Server 2008R2. I have a single table with a bunch of columns. I have built a new database with multiple tables to copy the data too. How do I copy the data, and still keep the relationships? For example, I have a table with columns such as: StudentFirstName, StudentLastName, TeacherName, Class1, class2, class3, class4, AdvisorName, etc. I want to copy that to multiple tables, such as: Student - StudentId, FirstName, LastName Teacher - TeacherId, FirstName, LastName StudentTeacher - Id, StudentId, TeacherId etc. When I insert the data into the Student and Teacher tables, I need to grab the ID fields from them (StudentId, TeacherId) and load those into the the StudentTeacher table, in the correct order. Is this best done with SSIS? If so, will I need a bunch of data flows because of the "one source, one destination" thing? If I do it with a script, I'm thinking the OUTPUT clause might help, but I have not yet figured out how to make that work. I have tried this: INSERT INTO Student (Name) OUTPUT Inserted.StudId INTO dbo.StudentTeacher (StudentId) VALUES ('Jimmy'), ('Bobby'), ('Nanacy'), ('Suzie') INSERT INTO Teacher(Name) OUTPUT Inserted.TeachId INTO dbo.StudentTeacher (TeacherId) VALUES ('Mr Jim'), ('Mr Bob'), ('Ms Nancy'), ('Ms Suzie') But that doesn't load at the same time, so I get multiple rows, one with a studentid, one with a teacherid. How do I get them to load at the same time? Just looking for some direction

insert Table1 OUTPUT inserted.ID, s.[Id] INTO @Inserted select s.[Field1], s.[Field2], from @TableVariable s why is s.[Id] marked by red line ? Is it non-correct syntax to save value in @Inserted table variable ?

I'm doing an update like this: UPDATE dbo.Table1 SET BirthDate = b.BirthDate FROM Table1 a JOIN Table2 b ON a.ID = b.ID And I want to use the OUTPUT clause to back up my changes. UPDATE dbo.Table1 SET BirthDate = b.BirthDate OUTPUT inserted.Id, inserted.BirthDate AS New_BirthDate, deleted.BirthDate AS Old_BirthDate INTO OutputTable FROM Table1 a JOIN Table2 b ON a.ID = b.ID What I want to know is if there is a way for the OUTPUT clause to create the table OutputTable or do I have to make sure it already exists before running the statement?

I have a series of updateable views we are exposing to end users as the interface for a back end process. One of these views references two tables and requires an INSTEAD OF trigger for UPDATE and INSERTs. The structure of the tables is (greatly simplified): Claim (DataRowID bigint IDENTITY PRIMARY KEY ,) ClaimExtended (ClaimDataRowID bigint FOREIGN KEY references dbo.Claim(DataRowID) NOT NULL ,) My original plan was to do this in the trigger like so: CREATE TRIGGER [dbo].[MyTrigger] ON [dbo].[MyView] INSTEAD OF INSERT AS DECLARE @IDLink TABLE (RowID int ,ClaimDataRowID bigint) DECLARE @Inserted TABLE (RowID int identity (1,1) NOT NULL ,) INSERT INTO @Inserted () SELECT () FROM Inserted INSERT INTO Claim () OUTPUT I.RowID ,inserted.ClaimDataRowID INTO @IDLink (RowID, ClaimDataRowID) SELECT () FROM @Inserted I INSERT INTO ClaimExtended (ClaimDataRowID, ) SELECT C.ClaimDataRowID, FROM @Inserted I INNER JOIN @IDLink C ON C.RowID = I.RowID The OUTPUT clause here is not working, however (Multi-part identifier I.RowID could not be bound) I'm assuming because I can't reference the source table in an INSERT OUTPUT clause. What other method could I use here besides making the view a table? For other reasons this needs to be a VIEW and the underlying tables are pretty much set in stone.

I'm trying to improve the performance of a SQL server database backup program that will create a new database to hold one years worth of report records, delete report records out of the main database, and capture the delete output and use it to insert the deleted records into the new 'year named' database. Essentially I am doing a move of the records from one main database, to separate databases for each year. On my test database, that is about 2.5 GB worth of report records, this process is taking around 3:00-3:40 minutes to complete. I have the 'Maximum Server Memory' set to the max, which is 2147483647 MB. As I understand it, SQL server uses whatever memory is available up to that maximum limit, and will release any memory that it does not need if there is memory pressure from other applications that require more memory. On the client SQL servers, we have the 'Maximum Server Memory' set to 2048 MB, because we thought that since SQL server was holding on to too much memory when it was set to the max value. The client databases are usually much larger than my test database, ranging anywhere from 30 GB to over 60 GB in a couple cases. Running the backup utility on the databases on these servers is taking at *least* 10 hours, and we usually tell the client that it could take anywhere from 18-24 hours for the utility to run to completion and create the 'year named' backup databases. We constantly see PAGEIOLATCH_EX and PAGEIOLATCH_SH waits, which I think means that either the disk subsystem is having problems with the IO required, or there is not enough memory available for the operation. Would limiting the 'Maximum Server Memory' like we have cause excessive latch wait times like we are seeing? We don't currently disable the indexes on the Reports table, which I'm sure is causing some slowness, but I'm not sure how much. We actually create an additional index over the date field, because that is the used in the where clause of our delete, in an attempt to speed up finding the appropriate records to delete. Which I now think might not be necessary, because the records should already be in date order, because that is how they are created daily. The process is like this: 1. Drop the full text index on the report table. 2. Create a non-clustered index on the report table over the date field and ID. 3. For each year of records selected 1. Create a new database to move the records to. 2. Create the reports table that has the same fields as the base database. 3. Perform the delete of that years records in 500 record batches, capturing the output and inserting it in the 'year named' database. 4. Create the full text index on the new database. 5. Shrink the main database. (This is because of disk space issues. Most of the clients disks can't finish the operation without running out of space unless we shrink the main database to release the space) 4. Re-enable the full text index on the main reports database. I'll try to include some samples of the script that I think might be relevant to the question. Delete with output clause = DECLARE @Rows INT SET @Rows = 1 WHILE (@Rows > 0) BEGIN DELETE TOP (500) FROM [" + opDbName + @"].[dbo].[Reports] OUTPUT DELETED.[ID] ,DELETED.[datafile] ,DELETED.[report] ,DELETED.[date] ,DELETED.[istext] ,DELETED.[version] ,DELETED.[dateimported] ,DELETED.[archivelocation] INTO [" + newDbName + @"].[dbo].[Reports]([ID],[datafile],[report],[date],[istext],[version],[dateimported],[archivelocation]) WHERE [Date] BETWEEN '" + i + @"-01-01' AND '" + i + @"-12-31' SET @Rows = @@ROWCOUNT END GO Create year database = IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'" + newDbName + @"') BEGIN PRINT('Creating database " + newDbName + @" ...') CREATE DATABASE [" + newDbName + @"] ON PRIMARY ( NAME = N'" + newDbName + @"', FILENAME = N'" + dataDir + newDbName + @".mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'" + newDbName + @"_log', FILENAME = N'" + logsDir + newDbName + @"_log.ldf', MAXSIZE = 2048GB , FILEGROWTH = 10%) END GO Alter year database = I don't even really know what all these settings are, but they come from generating the script to create the base database from above, and I just apply it to the newly created database to keep them the same. PRINT('Altering database " + newDbName + @" ...') GO ALTER DATABASE [" + newDbName + @"] SET COMPATIBILITY_LEVEL = 100 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [" + newDbName + @"].[dbo].[sp_fulltext_database] @action = 'enable' END GO ALTER DATABASE [" + newDbName + @"] SET ANSI_NULL_DEFAULT OFF ALTER DATABASE [" + newDbName + @"] SET ANSI_NULLS OFF ALTER DATABASE [" + newDbName + @"] SET ANSI_PADDING OFF ALTER DATABASE [" + newDbName + @"] SET ANSI_WARNINGS OFF ALTER DATABASE [" + newDbName + @"] SET ARITHABORT OFF ALTER DATABASE [" + newDbName + @"] SET AUTO_CLOSE OFF ALTER DATABASE [" + newDbName + @"] SET AUTO_CREATE_STATISTICS ON ALTER DATABASE [" + newDbName + @"] SET AUTO_SHRINK ON ALTER DATABASE [" + newDbName + @"] SET AUTO_UPDATE_STATISTICS ON ALTER DATABASE [" + newDbName + @"] SET CURSOR_CLOSE_ON_COMMIT OFF ALTER DATABASE [" + newDbName + @"] SET CURSOR_DEFAULT GLOBAL ALTER DATABASE [" + newDbName + @"] SET CONCAT_NULL_YIELDS_NULL OFF ALTER DATABASE [" + newDbName + @"] SET NUMERIC_ROUNDABORT OFF ALTER DATABASE [" + newDbName + @"] SET QUOTED_IDENTIFIER OFF ALTER DATABASE [" + newDbName + @"] SET RECURSIVE_TRIGGERS OFF ALTER DATABASE [" + newDbName + @"] SET DISABLE_BROKER ALTER DATABASE [" + newDbName + @"] SET AUTO_UPDATE_STATISTICS_ASYNC OFF ALTER DATABASE [" + newDbName + @"] SET DATE_CORRELATION_OPTIMIZATION OFF ALTER DATABASE [" + newDbName + @"] SET TRUSTWORTHY OFF ALTER DATABASE [" + newDbName + @"] SET ALLOW_SNAPSHOT_ISOLATION OFF ALTER DATABASE [" + newDbName + @"] SET PARAMETERIZATION SIMPLE ALTER DATABASE [" + newDbName + @"] SET READ_COMMITTED_SNAPSHOT OFF ALTER DATABASE [" + newDbName + @"] SET HONOR_BROKER_PRIORITY OFF ALTER DATABASE [" + newDbName + @"] SET RECOVERY SIMPLE ALTER DATABASE [" + newDbName + @"] SET MULTI_USER ALTER DATABASE [" + newDbName + @"] SET PAGE_VERIFY CHECKSUM ALTER DATABASE [" + newDbName + @"] SET DB_CHAINING OFF ALTER DATABASE [" + newDbName + @"] SET READ_WRITE GO

I am modifying the structure of a database. The content of several columns of the table **FinancialInstitution** has to be transferred into the table **Person**. FinancialInstitution is linked to Person with a foreign key. Each FinancialInstitution needs the Id of its corresponding Person. So, for each new line inserted in Person, the id of this new line (IDENTITY) has to be copied back into the corresponding line of FinancialInstitution. The obvious way of doing this is an iterative T-SQL code. But I'm interested in knowing if it's possible to do it only with set-based operations. I imagined the inner-level of such a request would be something like: INSERT INTO Person (Street1, Number1, City1, State1, PostCode1, CountryId1, WorkDirectPhone1, Fax1, Email1) OUTPUT inserted.Id, FinancialInstitution.Id SELECT Id, Street, Number, City, [State], PostCode, CountryId, PhoneNumber, Fax, Email FROM FinancialInstitution; Unfortunately, it seems OUTPUT can't correlate that way...