If you aren’t familiar with join statements then that is where you need to start. Understanding how joins work is key to the rest. Once you’re familiar with joins then understanding the merge is easiest by thinking of it as a full join with instructions on what to do for rows that do or do … Read more
MERGE was introduced in SQL Server 2008. If you want to use that syntax, you’ll need to upgrade. Otherwise, the typical approach will depend on where the source data is from. If it’s just one row and you don’t know if you need to update or insert, you’d probably do: UPDATE … WHERE key = … Read more
These 3 will get the same exact execution plan declare @id varchar(40) select @id = ‘172-32-1176’ select * from authors where au_id <> @id select * from authors where au_id != @id select * from authors where not (au_id = @id) It will also depend on the selectivity of the index itself of course. I … Read more
SELECT CASE CHARINDEX(‘ ‘, @Foo, 1) WHEN 0 THEN @Foo — empty or single word ELSE SUBSTRING(@Foo, 1, CHARINDEX(‘ ‘, @Foo, 1) – 1) — multi-word END You could perhaps use this in a UDF: CREATE FUNCTION [dbo].[FirstWord] (@value varchar(max)) RETURNS varchar(max) AS BEGIN RETURN CASE CHARINDEX(‘ ‘, @value, 1) WHEN 0 THEN @value ELSE … Read more
That method doesn’t work in SQL Server. You can accomplish the same thing in a couple different ways: 1.) Use the code for each aliased column instead of the alias: (SELECT COUNT(*) FROM UserEvent UE WHERE UE.EventTypeID = 1 AND UE.PracticeID = au.PracticeID AND (UE.EventDate BETWEEN @Date1 and @Date2) – COUNT(CASE WHEN udi.DevicePlatform = ‘iOS’ … Read more
this should give you the results you want. CREATE TABLE #temp ( id int, data varchar(50), section varchar(50) ) insert into #temp values(1, ‘1AAA’, ‘AAA’) insert into #temp values(1, ‘1BBB’, ‘BBB’) insert into #temp values(1, ‘1CCC’, ‘CCC’) insert into #temp values(2, ‘2AAA’, ‘AAA’) insert into #temp values(2, ‘2BBB’, ‘BBB’) insert into #temp values(2, ‘2CCC’, ‘CCC’) … Read more
ISNUMERIC will not do – it tells you that the string can be converted to any of the numeric types, which is almost always a pointless piece of information to know. For example, all of the following are numeric, according to ISNUMERIC: £, $, 0d0 If you want to check for digits and only digits, … Read more
How to search all columns of all tables in a database for a keyword? http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm EDIT: Here’s the actual T-SQL, in case of link rot: CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN — Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. — Purpose: To search all columns of all tables for a … Read more
This is similar to Adam Robinson’s, but uses ISNULL instead of COUNT. SELECT ISNULL( (SELECT 1 FROM Sites S WHERE S.Id = @SiteId and S.Status = 1 AND (S.WebUserId = @WebUserId OR S.AllowUploads = 1)), 0) If the inner query has a matching row, then 1 is returned. The outer query (with ISNULL) then returns … Read more
you have to call it like this SELECT dbo.CheckIfSFExists(23, default) From Technet: When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called in order to retrieve the default value. This behaviour is different from using parameters with default values in stored procedures in which … Read more