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
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
To escape special characters in a LIKE expression you prefix them with an escape character. You get to choose which escape char to use with the ESCAPE keyword. (MSDN Ref) For example this escapes the % symbol, using \ as the escape char: select * from table where myfield like ‘%15\% off%’ ESCAPE ‘\’ If … Read more
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
Try changing Tools > Options > Database Tools > Data Connections > SQL Server Instance Name. The default for VS2013 is (LocalDB)\v11.0. Changing to (LocalDB)\MSSQLLocalDB, for example, seems to work – no more version 782 error.
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
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
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;
This is the error you get (emphasis mine): The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. So, how can you avoid the error? By specifying TOP, would be one possibility, I guess. SELECT ( SELECT TOP 100 PERCENT … Read more
A column can definitely be an identity without being a PK. An identity is simply an auto-increasing column. A primary key is the unique column or columns that define the row. These two are often used together, but there’s no requirement that this be so.