Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

How to use values keyword as parameter of a function Expand / Collapse
Author
Message
Posted Thursday, November 29, 2012 4:02 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 1,757, Visits: 2,574
FYI, I think you can touch-up the original function to get rid of the variable and the COUNT(DISTINCT):

alter function AreEqual (@t as GenericTable readonly)
returns bit
as
begin
return (
select case when min(D) = max(D) then 1 else 0 end
from @t
)
end



SQL DBA,SQL Server MVP('07, '08, '09)
I'm not fat, I'm gravity challenged.
Post #1390903
Posted Thursday, November 29, 2012 11:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 36,002, Visits: 30,297
Sorry.... forum had a long delay and I doubled up on a post. I removed the dupe here...

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1391041
Posted Thursday, November 29, 2012 11:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 36,002, Visits: 30,297
CELKO (11/28/2012)
I would like to have a function which accepts any number of parameters with the same type and returns true if they are all equal and false otherwise.


You have to have a fixed number of parameters, but it can be up to 2K of them. Here is a skeleton:

CREATE PROCEDURE Equal
(@p1 INTEGER = NULL,
@p2 INTEGER = NULL,
@p3 INTEGER = NULL,
@p4 INTEGER = NULL,
@p5 INTEGER = NULL)
AS
SELECT CASE WHEN MIN(parm) = MAX(parm) THEN 'True' ELSE 'False' END
FROM (SELECT parm
FROM (VALUES (@p1), (@p2), (@p3), (@p4), (@p5)) AS X1(parm)
WHERE parm IS NOT NULL)
AS X2(parm);

EXEC Equal 12,12,12,12,12;
EXEC Equal 12,12,13;
EXEC Equal 12;

The use of the VALUES() constructor is new to SQL Server programmers, but other products have had it for awhile now.

I have a two articles on this topic at Simple Talk;

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/


That's ok but it's RBAR. Do it for the cte in the original post.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1391047
Posted Thursday, November 29, 2012 11:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 36,002, Visits: 30,297
capnhector (11/29/2012)
EDIT: This is also for me to play with a couple of things and get more experience with them. so it may not be fast or work in your situation but does use some things i have been studying.


No.... you're absolutely on the correct path. The MIN=MAX method the others used is twice as slow as the original function. You can make it a bit faster still by turning it into an iTVF instead of a scalar function. And lose the join. If you rework the GenericTable TYPE just a bit, you won't need it. Give it a shot. You've got this, Cap'n!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1391048
Posted Friday, November 30, 2012 1:42 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:29 AM
Points: 945, Visits: 1,760
Jeff Moden (11/29/2012)
capnhector (11/29/2012)
EDIT: This is also for me to play with a couple of things and get more experience with them. so it may not be fast or work in your situation but does use some things i have been studying.


No.... you're absolutely on the correct path. The MIN=MAX method the others used is twice as slow as the original function. You can make it a bit faster still by turning it into an iTVF instead of a scalar function. And lose the join. If you rework the GenericTable TYPE just a bit, you won't need it. Give it a shot. You've got this, Cap'n!


I cant believe i forgot to post the iTVF and new GenericTable rework as well. the things i was playing with was the cross apply values to unpivot the data.
The join was just to show that it came out with the right results. having the Generic Table type with just ID and Item allows any number of items to be passed in by just changing the cross apply unpivot.

CREATE TYPE GenericTable AS TABLE (ID INT, D sql_variant)
GO

CREATE FUNCTION AreEqual (@t as dbo.GenericTable readonly)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN SELECT ID, CASE WHEN COUNT(DISTINCT D) = 1 THEN 1 ELSE 0 END AS 'TF'
FROM @t
GROUP BY ID
GO


It is slow as hell for any thing larger than about 50,000 records on my system but chuggs them out just fine.

Here is the test bed i used

IF OBJECT_ID('tempdb..#Tmp') IS NOT NULL DROP TABLE #Tmp
--The temp table makes things a little easier
SELECT TOP (10000) --10 thousand is still nice and fast can change it later
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) I,
ABS(CHECKSUM(NEWID())) % 2 D1, --Gives us a small sub set so we will get more positive returns than a random seed of higher values
ABS(CHECKSUM(NEWID())) % 2 D2,
ABS(CHECKSUM(NEWID())) % 2 D3
INTO #Tmp
FROM sys.all_columns a, sys.all_columns b

DECLARE @T AS GenericTable

--Normalize the data so it plays nicely with our iTVF
INSERT INTO @T
SELECT I, Value
FROM #Tmp
CROSS APPLY (VALUES (D1),(D2),(D3))X(Value)


SELECT *
FROM AreEqual (@T) a
INNER JOIN #Tmp b
ON a.ID = b.I




For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1391542
Posted Friday, November 30, 2012 1:53 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 1,757, Visits: 2,574
Jeff Moden (11/29/2012)
capnhector (11/29/2012)
EDIT: This is also for me to play with a couple of things and get more experience with them. so it may not be fast or work in your situation but does use some things i have been studying.


No.... you're absolutely on the correct path. The MIN=MAX method the others used is twice as slow as the original function. You can make it a bit faster still by turning it into an iTVF instead of a scalar function. And lose the join. If you rework the GenericTable TYPE just a bit, you won't need it. Give it a shot. You've got this, Cap'n!



Really??

Doesn't the DISTINCT require an (expensive) sort but min/max don't?


SQL DBA,SQL Server MVP('07, '08, '09)
I'm not fat, I'm gravity challenged.
Post #1391548
Posted Friday, November 30, 2012 5:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 36,002, Visits: 30,297
ScottPletcher (11/30/2012)
Jeff Moden (11/29/2012)
capnhector (11/29/2012)
EDIT: This is also for me to play with a couple of things and get more experience with them. so it may not be fast or work in your situation but does use some things i have been studying.


No.... you're absolutely on the correct path. The MIN=MAX method the others used is twice as slow as the original function. You can make it a bit faster still by turning it into an iTVF instead of a scalar function. And lose the join. If you rework the GenericTable TYPE just a bit, you won't need it. Give it a shot. You've got this, Cap'n!



Really??

Doesn't the DISTINCT require an (expensive) sort but min/max don't?


Correct but the min/max still turns out slower. I'll try to remember to post my test code when I get home from work.

I also have an idea for solving the "base" problem here that might make both solutions seem slow.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1391598
Posted Friday, November 30, 2012 10:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 36,002, Visits: 30,297
Actually, I spoke a bit too soon. I was testing with 10 columns per row to be compared. There’s a cross-over point where the MIN/MAX method wins for 1 to 4 comparison columns and the COUNT(DISTINCT) wins for 5 and above.

Here’s the code I’ve been using to test with. To make things faster, I did like CapnHector did and converted both functions to iTVFs instead of leaving them as mTVFs.

--=====================================================================================================================
-- This section conditionally drops all of the objects and rebuilds them to make reruns in SSMS easier.
--=====================================================================================================================
--===== Do this in a nice safe place that everyone has.
USE tempdb
;
--===== Conditionally drop objects in dependency order to make reruns easier in SSMS
IF OBJECT_ID('dbo.AreEqualCount' ,'IF') IS NOT NULL DROP FUNCTION dbo.AreEqualCount;
IF OBJECT_ID('dbo.AreEqualMinMax','IF') IS NOT NULL DROP FUNCTION dbo.AreEqualMinMax;
IF OBJECT_ID('tempdb..#TestTable','U' ) IS NOT NULL DROP TABLE #TestTable;
IF EXISTS (SELECT * FROM sys.types WHERE name = 'GenericTable') DROP TYPE dbo.GenericTable;
GO
--===== First, rework the GenericTable just a bit
CREATE TYPE dbo.GenericTable AS TABLE (I SQL_Variant, D SQL_VARIANT);
GO
--===== Create the COUNT function with column "I" added
CREATE FUNCTION dbo.AreEqualCount
(@T AS dbo.GenericTable READONLY)
RETURNS TABLE WITH SCHEMABINDING
RETURN
SELECT I,
AllEqual = CASE WHEN COUNT(DISTINCT D) = 1 THEN 1 ELSE 0 END
FROM @T
GROUP BY I
;
GO
--===== Create the Min/Max version of the function with column "I" added
CREATE FUNCTION AreEqualMinMax
(@T AS dbo.GenericTable READONLY)
RETURNS TABLE WITH SCHEMABINDING
RETURN
SELECT I,
AllEqual = CASE WHEN MIN(D) = MAX(D) THEN 1 ELSE 0 END
FROM @T
GROUP BY I
;
GO
--===== Create the Test Table and populate it,
-- Note that it has some "L" columns that we don't want to work with.
SELECT TOP 100000
I = IDENTITY(INT,1,1),
D1 = ABS(CHECKSUM(NEWID()))%3+1,
D2 = ABS(CHECKSUM(NEWID()))%3+1,
D3 = ABS(CHECKSUM(NEWID()))%3+1,
D4 = ABS(CHECKSUM(NEWID()))%3+1,
D5 = ABS(CHECKSUM(NEWID()))%3+1,
D6 = ABS(CHECKSUM(NEWID()))%3+1,
D7 = ABS(CHECKSUM(NEWID()))%3+1,
D8 = ABS(CHECKSUM(NEWID()))%3+1,
D9 = ABS(CHECKSUM(NEWID()))%3+1,
D10 = ABS(CHECKSUM(NEWID()))%3+1,
L1 = ABS(CHECKSUM(NEWID()))%3+1,
L2 = ABS(CHECKSUM(NEWID()))%3+1,
L3 = ABS(CHECKSUM(NEWID()))%3+1
INTO #TestTable
FROM master.sys.all_columns ac1
CROSS APPLY master.sys.all_columns ac2
;
--===== Set every 5th row to have all equal values so we can see that things work correctly.
UPDATE #TestTable
SET D1 = I,
D2 = I,
D3 = I,
D4 = I,
D5 = I,
D6 = I,
D7 = I,
D8 = I,
D9 = I,
D10 = I
WHERE I%5 = 0
;
--=====================================================================================================================
-- This is the test section for the objects that we know of, so far.
--=====================================================================================================================
--===== Declare some obviously name variables
DECLARE @T GenericTable,
@StartTime DATETIME,
@BitBucket SQL_VARIANT
;
--===== This unpivots the data and inserts it into the generic table variable.
-- Notice that we have to enter all of the desired column names
INSERT INTO @T
(I,D)
SELECT t.I, cav.D
FROM #TestTable t
CROSS APPLY (VALUES (D1),(D2),(D3),(D4),(D5),(D6),(D7),(D8),(D9),(D10)) cav (D) --Change here to check for cross-over point
;
--===== Test the COUNT function
SELECT @StartTime = GETDATE()
;
SELECT @BitBucket = I,
@BitBucket = AllEqual
FROM dbo.AreEqualCount(@T)
;
SELECT CountDur = DATEDIFF(ms,@StartTime, GETDATE())
;
--===== Test the MinMax function
SELECT @StartTime = GETDATE()
;
SELECT @BitBucket = I,
@BitBucket = AllEqual
FROM dbo.AreEqualMinMax(@T)
;
SELECT MinMaxDur = DATEDIFF(ms,@StartTime, GETDATE())
;

Here are the durations from the run.

CountDur
-----------
3650

MinMaxDur
-----------
4373


Now, my question is, what is the actual functionality that the OP is asking for in the original post? Although it looks like “using VALUES” in a function, I think the actual goal is to be able to point the code at virtually any table and with as little fuss as possible, identify the columns to compare to see if they’re equal. The number of columns can vary, according to the OP. I’ll also assume that the actual columns names will all be similar to each other (i.e. “follow a pattern”).

I’ll also assume that the OP not only wants this to be easy to use and, of course, accurate, but would also like it to be as fast as possible.

Enter the “Dark Horse” in the form of a very old but tried and true method that avoids RBAR, avoids having to unpivot the rows, is easy to understand, can be made to work in virtually any version of SQL, and runs fast as the wind. Here’s the code and the test.

--=====================================================================================================================
-- If the purpose of this exercise is to make it so that we can generically test if multiple similarly named
-- columns in are identical on any table, then does it matter which method we use? Why can't it be a stored
-- procedure that takes the table name and a pattern of the columns we're looking for? And, why don't we use
-- an old trick with "CASE" to really speed up the works?
--=====================================================================================================================
--===== Conditionally drop the stored procedure to make reruns easier in SSMS.
IF OBJECT_ID('tempdb.dbo.AreEqualDynamic','P') IS NOT NULL DROP PROCEDURE dbo.AreEqualDynamic;
GO
--===== This creates the permanent stored procedure that will do all the work directly on the given table
CREATE PROCEDURE dbo.AreEqualDynamic
@pTableName VARCHAR(128),
@pPkColName VARCHAR(128),
@pColNamePattern VARCHAR(128)
AS
--===== Declare the obviously named local variable(s)
DECLARE @SQL VARCHAR(MAX);
;
--===== Build the dynamic SQL. No SQL Injection possible here
WITH
cteEnumerate AS
( --=== Enumerate the column names with the assumption that the first column is the key
SELECT ColNum = ROW_NUMBER() OVER (ORDER BY column_id),
ColName = name
FROM sys.columns
WHERE object_id = OBJECT_ID(@pTableName)
AND name LIKE @pColNamePattern
) --=== Build the comparisons for dynamic SQL
SELECT @SQL = ISNULL(@SQL + ' AND ','') + QUOTENAME(e1.ColName) + '=' + QUOTENAME(e2.ColName)
FROM cteEnumerate e1
JOIN cteEnumerate e2
ON e1.ColNum +1 = e2.ColNum
;
--===== Build the rest of the dynamic SQL and execute it
SELECT @SQL = '
SELECT PK = ' + (SELECT QUOTENAME(name) FROM sys.columns WHERE object_id = OBJECT_ID(@pTableName) AND name = @pPkColName) + ',
AreEqual = CASE
WHEN ' + @SQL + '
THEN 1
ELSE 0
END
FROM #TestTable
;
'
EXEC (@SQL)
print @sql
;
GO
;
--=====================================================================================================================
-- Now, test the code like we did with the others.
--=====================================================================================================================
--===== Test the dynamic CASE stored proc method
DECLARE @StartTime DATETIME
;
SELECT @StartTime = GETDATE()
;
EXEC dbo.AreEqualDynamic 'tempdb..#TestTable', 'I', 'D%'
;
SELECT CaseDur = DATEDIFF(ms,@StartTime, GETDATE())
;


Here’s the duration from that run on the very same data as the other two runs.

CaseDur
-----------
583


Other than the obvious advantage of it blowing the doors off the other two methods even though it didn’t dump its output to variables, there’s a huge hidden advantage. It doesn’t use the memory that a Table Variable requires. Instead, it uses the data from the table directly and it does so in an SQL Injection free manner.

BTW... here's the code that the dynamic SQL came up as...

 SELECT PK = [I],
AreEqual = CASE
WHEN [D1]=[D2] AND [D2]=[D3] AND [D3]=[D4] AND [D4]=[D5] AND [D5]=[D6] AND [D6]=[D7] AND [D7]=[D8] AND [D8]=[D9] AND [D9]=[D10]
THEN 1
ELSE 0
END
FROM #TestTable
;


Simple, easy to understand, and nasty fast.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1391623
Posted Tuesday, December 04, 2012 4:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:09 AM
Points: 156, Visits: 661
@Jeff Moden:
Now, my question is, what is the actual functionality that the OP is asking for in the original post? Although it looks like “using VALUES” in a function, I think the actual goal is to be able to point the code at virtually any table and with as little fuss as possible, identify the columns to compare to see if they’re equal. The number of columns can vary, according to the OP. I’ll also assume that the actual columns names will all be similar to each other (i.e. “follow a pattern”).


Why I need this: our company is preparing and printing lottery games. Every game is created as a sql table and I am responsible for control of the data.

An example: Lottery ticket contains 9 fields (3x3 matrix). If there are 3 equal integers (or chars or bits or strings,...) in each horizontal, vertical or diagonal rows, then the ticket contains a win. The easyest way to test this is to have a function which accepts 3 parameters (of any type) and return 1 if all parameters are true and false otherwise. I could use something like
where T1 = T2 and T1 = T3

which isn't that bad really. But what if I need to test inequality, then I would need to write something like this:
where T1 <> T2 and T1 <> T3 and T2 <> T3

As the number of parameters grows the query gets more and more complex. So a function like
NotEquals(... random number parameters of same type ...)

would reduce complexity of my queries dramaticaly.

As you can imagine, there is a vast amout of possible games with complex logic behind it, so a function which accepts any number of generic parameters and returns a single value is very handy for my job. And it has to be a scalar function (and not procedure), so that I can use it in selects and/or where clause, for example:
select TicketId, Equals(T1, T2, T3), Equals(T1, T5, T9)...
select * from MyTable where Equals(T1, T2, T3)
...

I got a lot of interesting solutions in this thread, but for now I just created functions that I need in a seperate database and wrote a set of tests for them. Then I add a synonym to all other databases with a stored procedure which uses sp_msforeachdb. This way the functions are in one place and thoroughly tested. The good: simple syntax, the bad: I have to write and test each function by it self. Example functions:
create function EqualI2(@i1 int, @i2 int)...
create function EqualI3(@i1 int, @i2 int, @i3 int)...
...
create function EqualS2(@s1 varchar(max), @s2 varchar(max)...
create function EqualS3(@s1 varchar(max), @s2 varchar(max), @s3 varchar(max))...
...
create function EqualB2(@b1 bit, @b2 bit)...
create function EqualB3(@b1 bit, @b2 bit, @b3 bit,...)...
...

Edit:
- yes, this kind of function is easy to write in C# for example (with the 'params' keyword, generic parameters and EqualityComparer), but I like to use sql for set-based problems.
- I don't like the tally table approach, because then you have to use something like 'Equal(convert(varchar, I1) + ', ' + convert(varchar, I2) + ', ' ...
- the basic goal is simple syntax, because the final file of a single control could be hunderts of rows long and I really don't like to make it any longer...
Post #1392378
Posted Tuesday, December 04, 2012 7:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 36,002, Visits: 30,297
_simon_ (12/4/2012)
But what if I need to test inequality, then I would need to write something like this:
where T1 <> T2 and T1 <> T3 and T2 <> T3



I'm on my way to work and will have to do a deep dive on all that you wrote tonight but thought I'd quickly answer this...

I would suggest that you never test for such an inequality in such a manner. Always test for the equality and if that test "fails", then it must be an inequality, right?. Testing for equality is almost always faster than testing for inequalities.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1392482
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse