August 22, 2012 at 5:34 pm
SQL Kiwi (8/22/2012)
There is a better, and easier way to do it using NOT EXISTS and INTERSECT. I describe the technique here: http://bit.ly/EqualityComparison
That. Is. AWESOME.
I stand humbly corrected, thanks for bringing this to the table Paul. I believe I have a query (or 10) to mark for modification once we get out of 2k5. Thanks again!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 22, 2012 at 5:51 pm
Evil Kraig F (8/22/2012)
I believe I have a query (or 10) to mark for modification once we get out of 2k5.
The NOT EXISTS...INTERSECT thing works in SQL Server 2005 of course. I guess you mean you are waiting for 2008 to use MERGE?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 22, 2012 at 6:00 pm
SQL Kiwi (8/22/2012)
Evil Kraig F (8/22/2012)
I believe I have a query (or 10) to mark for modification once we get out of 2k5.The NOT EXISTS...INTERSECT thing works in SQL Server 2005 of course. I guess you mean you are waiting for 2008 to use MERGE?
Um, yes? :blush:
Right, and now that I've thoroughly looked like I'm completely distracted today, time to go look at a few queries... *whistles as he walks away hoping noone notices...*
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 22, 2012 at 8:53 pm
Evil Kraig F (8/22/2012)
SQL Kiwi (8/22/2012)
There is a better, and easier way to do it using NOT EXISTS and INTERSECT. I describe the technique here: http://bit.ly/EqualityComparisonThat. Is. AWESOME.
I stand humbly corrected, thanks for bringing this to the table Paul. I believe I have a query (or 10) to mark for modification once we get out of 2k5. Thanks again!
+1 that is super cool Paul!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2012 at 9:30 am
SQL Kiwi (8/22/2012)
sqlfriends (8/22/2012)
Because I have quite a few <> comparisons in my sql, ( in my post it is just a part of them), if I write something like :Student.WithdrawDate <> esis.WithdrawDate
OR (student.WithdrawDate is NULL AND esis.WithdrawDate is NOT NULL)
OR (student.WithdrawDate is NOT NULL AND esis.withdrawdate is NULL)
It will get very long, is it an easier way to do it?
can I use isnull(student.mailingaddress,'')<>isnull(esis.mailingaddress,'')
There is a better, and easier way to do it using NOT EXISTS and INTERSECT. I describe the technique here: http://bit.ly/EqualityComparison
This handles NULLs correctly, means you don't have to find a 'magic value' to use with ISNULL or COALESCE, and is SARGable. The basic pattern is as follows, but please read the full blog post to make sure you understand how and why it works.
WHERE NOT EXISTS
(
SELECT
Student.SchoolID,
Student.GradeLevel,
Student.LegalName,
Student.WithdrawDate,
Student.SPEDFlag,
Student.MailingAddress
INTERSECT
SELECT
esis.SchoolID,
esis.GradeLevel,
esis.LegalName,
esis.WithdrawDate,
esis.SPEDFlag,
esis.MailingAddress
)
Listing the columns is easy in SSMS (drag them from the object explorer to the query pane). In the case where all columns are significant, you can also use the star syntax:
WHERE NOT EXISTS
(
SELECT
Student.*
INTERSECT
SELECT
esis.*
)
Thanks Paul.
So in the merge statement,
Right below when matched statement I can add where not exists like below ? Thanks
When Matched
and WHERE NOT EXISTS (
SELECT
Student.SchoolID,
Student.GradeLevel,
Student.LegalName,
Student.WithdrawDate,
Student.SPEDFlag,
Student.MailingAddress
INTERSECT
SELECT
esis.SchoolID,
esis.GradeLevel,
esis.LegalName,
esis.WithdrawDate,
esis.SPEDFlag,
esis.MailingAddress
)[/Then update
Set Student.Schoolid=esis.schoolid,
....
August 23, 2012 at 7:11 pm
sqlfriends (8/23/2012)
So in the merge statement, Right below when matched statement I can add where not exists like below ? Thanks
Don't just copy and paste, read the article and understand it! You have an extra 'WHERE' that would cause a syntax error. I'm amazed you replied without trying it first.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 6, 2014 at 4:16 pm
Paul - THANK YOU for this * 100000000000000000000
thank you thank you thank you.
And yes I read the whole article, and sent on to my jr. dba. 😉
May 6, 2014 at 4:36 pm
tiffanyjanetblack (5/6/2014)
Paul - THANK YOU for this * 100000000000000000000thank you thank you thank you.
And yes I read the whole article, and sent on to my jr. dba. 😉
No worries.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 7, 2014 at 9:35 am
Paul do you have a similar trick that will work in the output clause? Output statements do not allow subqueries 🙁
I have a store procedure that is called with a row of values and invokes a merge statement. I capture the output of the merge statement into a temp table and I want to check if any value has changed, if so I create an audit.
Right now I use this contraption, but ideally I'd like to simplify it as I find the isnull(nullif statement horribly ugly:
output
inserted.$identity [id],
convert(binary(1),isnull(nullif(inserted.colName1,deleted.colName1),nullif(deleted.colName1,inserted.colName1))),
convert(binary(1),isnull(nullif(inserted.colName2,deleted.colName2),nullif(deleted.colName2,inserted.colName2)))
into @ai(id,colName1,colName2)
...
insert into audit(TableName,attribute,Value,Identifier,auditdate)
select 'TableName',target.attribute,target.value,z.id,@auditDate
from @ai z
cross apply (VALUES
('colName1',convert(sql_variant,@colName1),z.colName1),
('colName2',convert(sql_variant,@colName2),z.colName2),
) target (attribute,value,includeIfNotNull)
where target.includeIfNotNull is not null
May 7, 2014 at 9:58 am
mburbea (5/7/2014)
Paul do you have a similar trick that will work in the output clause? Output statements do not allow subqueries
You can't put the EXISTS...INTERSECT check in the MERGE statement body?
Can you post a complete repro (perhaps as a new question?) I sort of see what you're getting at, but a complete code example with sample data and expected output always helps avoid unnecessary to-and-fro.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 7, 2014 at 12:42 pm
Sure understood. Here is an example script that shows what I want.
Basically, I want to only create an audit when a user changes data for the table "tableName". I use the output clause right now to determine if a change was made. The audit trail that this query outputs will ignore the initial inserts of null (which is what I want), and report the changes that each of the users made and when they made them. This should be a self-contained repo. The procedure is pretty similar to what is actually called.
I absolutely despise how verbose the way the is variable changed check is
convert(binary(1),isnull(nullif(inserted.col1,deleted.col1),nullif(deleted.col1,inserted.col1))),
[/sql]
It would be much simpler to have something like convert(binary(1),(select inserted.col1 intersect inserted.col2))
use tempdb;
GO
if (object_id('tableName') is not null)
drop table tableName
create table tableName
(
id int not null identity primary key clustered,
col1 int null,
col2 varchar(255) null,
isActive bit not null default (1)
)
if (object_id('auditName') is not null)
drop table tableName
create table audit
(
auditId int not null identity primary key clustered,
tableName sysname not null,
attribute sysname not null,
value sql_variant null,
tableId int not null,
auditUser sysname not null,
auditdate datetime not null
)
if(object_id('upsertTableName') is not null)
drop procedure upsertTableName
Go
create procedure upsertTableName
(
@Id int,
@col1 int,
@col2 varchar(255),
@isActive bit,
@userName varchar(255)
)
AS
BEGIN
declare @auditDate datetime = getdate();
declare @ai table(
id int,
col1 bit,
col2 bit,
isActive bit
)
begin tran
;merge tableName as target
using (select @Id [id],
@col1 [col1],
@col2 [col2],
@isActive [isActive]) src
on target.id=src.id
when matched then
update set
target.col1 = src.col1,
target.col2 = src.col2,
target.isActive = src.isActive
when not matched by target then
insert(col1,col2,isActive)
values(col1,col2,isActive)
output
inserted.$identity [id],
convert(binary(1),isnull(nullif(inserted.col1,deleted.col1),nullif(deleted.col1,inserted.col1))),
convert(binary(1),isnull(nullif(inserted.col2,deleted.col2),nullif(deleted.col2,inserted.col2))),
convert(binary(1),isnull(nullif(inserted.isActive,deleted.isActive),nullif(deleted.isActive,inserted.isActive)))
into @ai(id,col1,col2,isActive)
output inserted.id [id];
insert into audit(TableName,attribute,Value,TableId,auditdate,auditUser)
select 'TableName',target.attribute,target.value,z.id,@auditDate,@userName
from @ai z
cross apply (VALUES
('col1',convert(sql_variant,@col1),z.col1),
('col2',convert(sql_variant,@col2),z.col2),
('isActive',convert(sql_variant,@isActive),z.isActive)
) target (attribute,value,includeIfNotNull)
where target.includeIfNotNull is not null
commit
END
GO
declare @t table(id int)
declare @id int;
insert @t
exec upsertTableName null,null,null,1,'jim'
select @id = (select * from @t)
waitfor delay '00:00:00.5'
;exec upsertTableName @id,1,'a',1,'eric'
waitfor delay '00:00:00.5'
;exec upsertTableName @id,2,'a',1,'susan'
waitfor delay '00:00:00.5'
;exec upsertTableName @id,null,null,0,'john'
select attribute,value,audituser FROM audit
where tableId = @id
and tableName = 'tableName'
order by auditDate
/*
attributevalueaudituser
isActive1jim
col11eric
col2aeric
col12susan
col1NULLjohn
col2NULLjohn
isActive0john
*/
drop table audit
drop table tableName
drop procedure upsertTableName
May 8, 2014 at 5:28 am
mburbea (5/7/2014)
I absolutely despise how verbose the way the is variable changed check is
The only idea that leaps to mind is that while the OUTPUT clause does not allow a subquery, it does allow a scalar function:
CREATE FUNCTION dbo.IsDistinctFrom
(
@value1 sql_variant,
@value2 sql_variant
)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
RETURN
CASE WHEN NOT EXISTS
(
SELECT @value1
INTERSECT
SELECT @value2
)
THEN 1
ELSE 0
END;
END;
The OUTPUT clause would then become something like:
OUTPUT
Inserted.$identity AS id,
dbo.IsDistinctFrom(Inserted.col1, Deleted.col1) AS col1,
dbo.IsDistinctFrom(Inserted.col2, Deleted.col2) AS col2,
dbo.IsDistinctFrom(Inserted.isActive, Deleted.isActive) AS isActive
It's not perfect, but perhaps it gives you some ideas.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 8, 2014 at 7:44 am
That's what I figured. I hate scalar udfs in sql server as they tend to perform so poorly 🙁
May 8, 2014 at 8:19 am
mburbea (5/8/2014)
That's what I figured. I hate scalar udfs in sql server as they tend to perform so poorly 🙁
Me too. I rarely (very rarely!) use them or recommend them, but in this case it seems guaranteed to only ever operate on a single data item, so it should be ok. Depends whether you think the neatness is worth forcing yourself to use a scalar function, I suppose!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply