Instantly share code, notes, and snippets.

View TemporalTables_UpdateTables.sql
UPDATE dbo.Department
SET DeptName = 'Sales and Marketing'
UPDATE dbo.Manager
SET ManagerName = 'Greg Dodd'
View CreateSystemVersionedTables.sql
CREATE TABLE Department
(
DeptID INT NOT NULL PRIMARY KEY CLUSTERED
,DeptName VARCHAR(50) NOT NULL
,ManagerID INT NULL
,SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
,SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON)
View ReadCommittedSnapshotOn.sql
ALTER DATABASE StackOverflow
SET READ_COMMITTED_SNAPSHOT ON
View AggregateFunctionsinanUpdateStatementquery4.sql
WITH AggregateData AS
(
SELECT Gate
,MAX(RecordedTime) AS MaxRecordedTime
FROM dbo.SFOGateInfo
)
UPDATE AggregateTest
SET LastUsedDateTime = AggregateData.MaxRecordedTime
FROM AggregateTest
INNER JOIN AggregateData
View AggregateFunctionsinanUpdateStatementquery3.sql
UPDATE AggregateTest
SET LastUsedDateTime = MAX(recordedTime)
FROM SFOGateInfo
View AggregateFunctionsinanUpdateStatementquery2.sql
INSERT INTO AggregateTest (Gate, LastUsedDateTime)
SELECT Gate
MAX(RecordedTime)
FROM SFOGateInfo
GROUP BY Gate
View AggregateFunctionsinanUpdateStatementquery1.sql
CREATE TABLE AggregateTest
(
ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL
,Gate VARCHAR(5) NULL
,LastUsedDateTime DATETIME2(7) NULL
)
View StringTruncated.sql
use tempdb
GO
CREATE TABLE dbo.Test1
(
Col1 VARCHAR(6) NOT NULL PRIMARY KEY
)
GO