T-SQLpowered

Constraints

24. 7. 2025

Add default constraint to column inline: ALTER TABLE [dbo].[SampleTable] ADD Is_Archived BIT NOT NULL CONSTRAINT DF_dbo_Sample_Table_Is_Archived DEFAULT 0 ALTER TABLE [dbo].[SampleTable] ADD Is_Cleaned_Up BIT CONSTRAINT DF_dbo_Sample_Table_Is_Archived DEFAULT 0 NOT NULL  

Read more
T-SQLpowered

VALUES

22. 7. 2025
SELECT from VALUES: SELECT * FROM (VALUES (1), (2)) a (Id)  
T-SQLpowered

WHILE

15. 6. 2024
Iterate with MIN/MAX Id. Doesn’t perform well for broken number sequences. DECLARE @Current_Id INT DECLARE @Max_Id INT DECLARE @Ids TABLE ( [Id] INT) INSERT INTO @Ids ([Id]) SELECT [Id] FROM (VALUES (1),(2),(3)) [v] ([Id]) SELECT @Current_Id = MIN([Id]) FROM @Ids SELECT @Max_Id = MAX([Id]) FROM @Ids WHILE @Current_Id <= @Max_Id…
T-SQLpowered

CREATE TABLE

5. 2. 2023
Create Table with clustered Primary Key on Identity column: CREATE TABLE [dbo].[SampleTable] ( [Id] INT IDENTITY (1,1) NOT NULL, CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED ([Id]) )  
T-SQLpowered

Table Type

17. 2. 2022
Declare with PK constraint, insert and retrieve data: CREATE TYPE [dbo].[utt_Generic_Int] AS TABLE ( [Id] [INT] NOT NULL PRIMARY KEY CLUSTERED ) GO DECLARE @Ids utt_Generic_Int INSERT INTO @Ids ( [Id] ) VALUES (1), (2), (3) SELECT * FROM @Ids GO  
T-SQLpowered

Jobs

4. 1. 2022
Enable/Disable job: — enable EXEC msdb.dbo.sp_update_job @job_name=’SampleJobName’, @enabled = 1 — disable EXEC msdb.dbo.sp_update_job @job_name=’SampleJobName’, @enabled = 0    
T-SQLpowered

MERGE

31. 12. 2021
Using OUTPUT clause: MERGE INTO [dbo].[TargetTable] [trg] USING ( SELECT [ID], [Value], [OtherValue] FROM [dbo].[SourceTable] ) AS [src] ON 1 = 0 WHEN NOT MATCHED THEN INSERT ( [ID],[Value] ) VALUES ([src].[ID], [src].[Value]) OUTPUT [Inserted].[ID], [Inserted].[Value], [src].[OtherValue]  
T-SQLpowered

UPDATE

31. 12. 2021

Examples for SQL Server 2025. Each block is a separate example. Replace the table and column names as needed; Id is assumed to be unique. Basics Values and expressions Large strings and binary data Values from another source Conditions and changed values CTEs and batches OUTPUT and variables SQL Server…

Read more