Return to Answer

In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table: WITH (TABLOCK, HOLDLOCK). I just tested this on a View and it does block access until the transaction completes, unless a query uses the WITH (NOLOCK) hint.

Regardless, you can do an easy test with your code to see how your current code works and how any changes might do what you are wanting:

Right before the EXEC sp_executesql @DynamicPivotQuery line, add the following two lines:

RAISERROR('Make a change now...', 10, 1) WITH NOWAIT;
WAITFOR DELAY '00:01:00.000' -- 1 minute pause

Then, run the code you have posted above. When you see "Make a change now" in the Messages tab, make a change to the underlying table. Once the WAITFOR finishes it will run the dynamic SQL and you should see the effects of your changes.

So the following should work:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

BEGIN TRAN;
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course
      FROM CourseSalesView WITH (TABLOCK, HOLDLOCK)) AS Courses;

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning) 
          FOR Course IN (' + @ColumnName + ')) AS PVTTable';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;
COMMIT TRAN;

Beyond that basic interaction, there are two other options:

• Storing the results of the View into a local Temp Table (i.e. #TableName). This has the benefit of not locking the underlying table(s) of the View, but it will have to write the entire results of the view into tempdb and that does not sound like a scalable solution if the View returns 10k rows or more.

• Enable SNAPSHOT ISOLATION (introduced in SQL Server 2005) which handles this for you without needing to explicitly lock the table:

• Snapshot Isolation in SQL Server

• ALTER DATABASE SET Options

In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table: WITH (TABLOCK, HOLDLOCK). I just tested this on a View and it does block access until the transaction completes, unless a query uses the WITH (NOLOCK) hint.

Regardless, you can do an easy test with your code to see how your current code works and how any changes might do what you are wanting:

Right before the EXEC sp_executesql @DynamicPivotQuery line, add the following two lines:

RAISERROR('Make a change now...', 10, 1) WITH NOWAIT;
WAITFOR DELAY '00:01:00.000' -- 1 minute pause

Then, run the code you have posted above. When you see "Make a change now" in the Messages tab, make a change to the underlying table. Once the WAITFOR finishes it will run the dynamic SQL and you should see the effects of your changes.

So the following should work:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

BEGIN TRAN;
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course
      FROM CourseSalesView WITH (TABLOCK, HOLDLOCK)) AS Courses;

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning) 
          FOR Course IN (' + @ColumnName + ')) AS PVTTable';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;
COMMIT TRAN;

Beyond that basic interaction, there are two other options:

• Storing the results of the View into a local Temp Table (i.e. #TableName). This has the benefit of not locking the underlying table(s) of the View, but it will have to write the entire results of the view into tempdb and that does not sound like a scalable solution if the View returns 10k rows or more.

• Enable SNAPSHOT ISOLATION (introduced in SQL Server 2005) which handles this for you without needing to explicitly lock the table:

• Snapshot Isolation in SQL Server

• ALTER DATABASE SET Options

• You will still need to wrap the two queries in an explicit Transaction, but no need for the Table Hint

• If you don't enable READ_COMMITTED_SNAPSHOT then you will need to issue SET TRANSACTION ISOLATION LEVEL SNAPSHOT before the BEGIN TRAN

In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table: WITH (TABLOCK, HOLDLOCK). I just tested this on a View and it does block access until the transaction completes, unless a query uses the WITH (NOLOCK) hint.

Regardless, you can do an easy test with your code to see how your current code works and how any changes might do what you are wanting:

Right before the EXEC sp_executesql @DynamicPivotQuery line, add the following two lines:

RAISERROR('Make a change now...', 10, 1) WITH NOWAIT;
WAITFOR DELAY '00:01:00.000' -- 1 minute pause

Then, run the code you have posted above. When you see "Make a change now" in the Messages tab, make a change to the underlying table. Once the WAITFOR finishes it will run the dynamic SQL and you should see the effects of your changes.

So the following should work:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

BEGIN TRAN;
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course
      FROM CourseSalesView WITH (TABLOCK, HOLDLOCK)) AS Courses;

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning) 
          FOR Course IN (' + @ColumnName + ')) AS PVTTable';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;
COMMIT TRAN;

In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table: WITH (TABLOCK, HOLDLOCK). I just tested this on a View and it does block access until the transaction completes, unless a query uses the WITH (NOLOCK) hint.

Regardless, you can do an easy test with your code to see how your current code works and how any changes might do what you are wanting:

Right before the EXEC sp_executesql @DynamicPivotQuery line, add the following two lines:

RAISERROR('Make a change now...', 10, 1) WITH NOWAIT;
WAITFOR DELAY '00:01:00.000' -- 1 minute pause

Then, run the code you have posted above. When you see "Make a change now" in the Messages tab, make a change to the underlying table. Once the WAITFOR finishes it will run the dynamic SQL and you should see the effects of your changes.

So the following should work:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

BEGIN TRAN;
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course
      FROM CourseSalesView WITH (TABLOCK, HOLDLOCK)) AS Courses;

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning) 
          FOR Course IN (' + @ColumnName + ')) AS PVTTable';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;
COMMIT TRAN;

Beyond that basic interaction, there are two other options:

• Storing the results of the View into a local Temp Table (i.e. #TableName). This has the benefit of not locking the underlying table(s) of the View, but it will have to write the entire results of the view into tempdb and that does not sound like a scalable solution if the View returns 10k rows or more.

• Enable SNAPSHOT ISOLATION (introduced in SQL Server 2005) which handles this for you without needing to explicitly lock the table:

• Snapshot Isolation in SQL Server

• ALTER DATABASE SET Options

In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table: WITH (TABLOCK, XLOCK). I just tested this on a View and it does block access until the transaction completes, unless a query uses the WITH (NOLOCK) hint.

Regardless, you can do an easy test with your code to see how your current code works and how any changes might do what you are wanting:

Right before the EXEC sp_executesql @DynamicPivotQuery line, add the following two lines:

RAISERROR('Make a change now...', 10, 1) WITH NOWAIT;
WAITFOR DELAY '00:01:00.000' -- 1 minute pause

Then, run the code you have posted above. When you see "Make a change now" in the Messages tab, make a change to the underlying table. Once the WAITFOR finishes it will run the dynamic SQL and you should see the effects of your changes.

In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table: WITH (TABLOCK, HOLDLOCK). I just tested this on a View and it does block access until the transaction completes, unless a query uses the WITH (NOLOCK) hint.

Regardless, you can do an easy test with your code to see how your current code works and how any changes might do what you are wanting:

Right before the EXEC sp_executesql @DynamicPivotQuery line, add the following two lines:

RAISERROR('Make a change now...', 10, 1) WITH NOWAIT;
WAITFOR DELAY '00:01:00.000' -- 1 minute pause

Then, run the code you have posted above. When you see "Make a change now" in the Messages tab, make a change to the underlying table. Once the WAITFOR finishes it will run the dynamic SQL and you should see the effects of your changes.

So the following should work:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

BEGIN TRAN;
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course
      FROM CourseSalesView WITH (TABLOCK, HOLDLOCK)) AS Courses;

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning) 
          FOR Course IN (' + @ColumnName + ')) AS PVTTable';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;
COMMIT TRAN;