sql-server

Is a single SQL Server statement atomic and consistent?

by

I’ve been operating under the assumption that a single statement in SQL Server is consistent That assumption is wrong. The following two transactions have identical locking semantics: STATEMENT BEGIN TRAN; STATEMENT; COMMIT No difference at all. Single statements and auto-commits do not change anything. So merging all logic into one statement does not help (if … Read more

Fuzzy matching using T-SQL

by

I’ve found that the stuff SQL Server gives you to do fuzzy matching is pretty clunky. I’ve had really good luck with my own CLR functions using the Levenshtein distance algorithm and some weighting. Using that algorithm, I’ve then made a UDF called GetSimilarityScore that takes two strings and returns a score between 0.0 and … Read more

Can I have an optional OUTPUT parameter in a stored procedure?

by

Both input and output parameters can be assigned defaults. In this example: CREATE PROCEDURE MyTest @Data1 int ,@Data2 int = 0 ,@Data3 int = null output AS PRINT @Data1 PRINT @Data2 PRINT isnull(@Data3, -1) SET @Data3 = @Data3 + 1 RETURN 0 the first paramter is required, and the second and third are optional–if not … Read more

Does T-SQL have an aggregate function to concatenate strings? [duplicate]

by

for SQL Server 2017 and up use: STRING_AGG() set nocount on; declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5)) insert into @YourTable VALUES (1,1,’CCC’) insert into @YourTable VALUES (2,2,’B<&>B’) insert into @YourTable VALUES (3,2,’AAA’) insert into @YourTable VALUES (4,3,'<br>’) insert into @YourTable VALUES (5,3,’A & Z’) set nocount off SELECT t1.HeaderValue ,STUFF( (SELECT ‘, … Read more

Combine multiple results in a subquery into a single comma-separated value

by

Even this will serve the purpose Sample data declare @t table(id int, name varchar(20),somecolumn varchar(MAX)) insert into @t select 1,’ABC’,’X’ union all select 1,’ABC’,’Y’ union all select 1,’ABC’,’Z’ union all select 2,’MNO’,’R’ union all select 2,’MNO’,’S’ Query: SELECT ID,Name, STUFF((SELECT ‘,’ + CAST(T2.SomeColumn AS VARCHAR(MAX)) FROM @T T2 WHERE T1.id = T2.id AND T1.name = … Read more

Disable Transaction Log

by

You can’t do without transaction logs in SQL Server, under any circumstances. The engine simply won’t function. You CAN set your recovery model to SIMPLE on your dev machines – that will prevent transaction log bloating when tran log backups aren’t done. ALTER DATABASE MyDB SET RECOVERY SIMPLE;