Basic operations
16. 4. 2023
Split long string to multiple lines (\ separator): SELECT ‘abc\ def’ AS [Result]; SELECT 0xabc\ def AS [Result];
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]) )
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
Configuration (sp_configure)
17. 1. 2022
Show advanced options: USE [master] GO EXEC [sys].[sp_configure] ‘Show advanced options’,1 GO RECONFIGURE WITH OVERRIDE GO
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
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]
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)
ALTER TABLE
30. 12. 2021
Add New Column: ALTER TABLE [dbo].[SampleTable] ADD [NewColum_1] NVARCHAR(MAX) — will be added as NULLable ALTER TABLE [dbo].[SampleTable] ADD [NewColum_1] NVARCHAR(MAX) NULL ALTER TABLE [dbo].[SampleTable] ADD [NewColum_1] NVARCHAR(MAX) NOT NULL Add Primary Key: ALTER TABLE [dbo].[SampleTable] ADD CONSTRAINT PK_Sample_Table PRIMARY KEY CLUSTERED (Id) Change data type for Primary Key column…
INSERT - Basic
28. 12. 2021
Insert one row with default values for all table columns: INSERT INTO [dbo].[SampleTable] DEFAULT VALUES SELECT INTO and specify a filegroup (2016 SP2+): SELECT * INTO [dbo].[SampleTable_Copy] ON [OtherFilegoup] FROM [dbo].[SampleTable]
SQLCMD - Basic commands
27. 12. 2021
Check if SQLCMD mode is enabled in SSMS: :setvar __IsSqlCmdEnabled “True” GO IF N’$(__IsSqlCmdEnabled)’ NOT LIKE N’True’ BEGIN PRINT N’SQLCMD mode must be enabled to successfully execute this script.’; SET NOEXEC ON; END GO Declare database name as variable and switch database context: :SETVAR Database “MyDatabase” USE [$(Database)] GO Execute/Include…