Today's question (by Thomas Franz):

Temporary data and Differential Backup size

IF DB_ID('test') IS NULL CREATE DATABASE test
GO
USE test
GO
-- each row will be 4011 byte long, so only 2 records will fit onto one page
CREATE TABLE dbo.test_for_backup (id INT NOT NULL IDENTITY PRIMARY KEY, txt CHAR(4000) NOT NULL)
GO
-- create the FULL backup
BACKUP DATABASE [test] TO DISK = N'C:MSSQLBackuptest1.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

-- insert 10k records, it will fill 5000 pages a 8kB = 40,000 kB = 3.9 MB
INSERT INTO dbo.test_for_backup (txt)
SELECT CAST(ROW_NUMBER() OVER (ORDER BY t1.n) AS VARCHAR(10)) AS txt
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t1(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t2(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t3(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t4(n)

-- remove the records again
TRUNCATE TABLE dbo.test_for_backup

-- what do you think, how many pages the DIFFERENTIAL backup will process?
BACKUP DATABASE [test] TO DISK = N'C:MSSQLBackuptest_diff.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Think you know the answer? Click here, and find out if you are right.

Using temporary tables with OUTPUT clause

I execute the code block below:

-- Create Table MyTable --
Create Table MyTable
 (ID SmallInt Identity Primary Key Clustered,
  Numbers Int,
  CreationDate Date,
  ManipulationDate Date)
Go
  
-- Insert Rows --
Insert Into MyTable(Numbers, CreationDate, ManipulationDate) 
 Values(10, GETDATE(), GETDATE()+1),
       (20, GETDATE(), GETDATE()+2),
       (30, GETDATE(), GETDATE()+3),
       (40, GETDATE(), GETDATE()+4)
Go

-- Create Temporary Table #MyTempTable --
Create Table #MyTempTable
 (ID SmallInt Identity Primary Key Clustered,
  Numbers Int,
  CreationDate Date,
  ManipulationDate Date)
Go

-- Update Rows with Output clause --
Update MyTable
Set ManipulationDate=DateAdd(d,5,ManipulationDate)

Output Inserted.Numbers, 
       Inserted.CreationDate, 
   Inserted.ManipulationDate
 Into #MyTempTable

Go

What will be the returned  in SQL Server 2017?

Answer: (4 rows affected)

Explanation: The correct answer is: (4 rows affected) Explanation: Unlike the Select Into command, we do not need to create the previous table that will receive the data processed by the Select command.

Msg 208, Level 16, State 0, Line 26 Invalid object name '#MyTempTable'.

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.