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

Update with the expression: UPDATE [dbo].[SampleTable] SET [Col1] = @a + @b + @c Update using the .WRITE() clause: UPDATE [dbo].[SampleTable] SET [Col1].WRITE(‘New Value, 0, NULL) UPDATE [dbo].[SampleTable] SET [Col1].WRITE(@a + @b + @c, 0, NULL) UPDATE [dbo].[SampleTable] SET [Col1].WRITE(@x, 500, 1000)  

Read more