Compare Performance Difference of T-Sql Between and '<' '>' Operator

Compare performance difference of T-SQL Between and '' '' operator?

You can check this easily enough by checking the query plans in both situations. There is no difference of which I am aware. There is a logical difference though between BETWEEN and "<" and ">"... BETWEEN is inclusive. It's equivalent to "<=" and "=>".

BETWEEN operator vs. = AND =: Is there a performance difference?

No benefit, just a syntax sugar.

By using the BETWEEN version, you can avoid function reevaluation in some cases.

SQL : BETWEEN vs = and =

They are identical: BETWEEN is a shorthand for the longer syntax in the question that includes both values (EventDate >= '10/15/2009' and EventDate <= '10/19/2009').

Use an alternative longer syntax where BETWEEN doesn't work because one or both of the values should not be included e.g.

Select EventId,EventName from EventMaster
where EventDate >= '10/15/2009' and EventDate < '10/19/2009'

(Note < rather than <= in second condition.)

Should I use != or for not equal in T-SQL?

Technically they function the same if you’re using SQL Server AKA T-SQL. If you're using it in stored procedures there is no performance reason to use one over the other. It then comes down to personal preference. I prefer to use <> as it is ANSI compliant.

You can find links to the various ANSI standards at...

http://en.wikipedia.org/wiki/SQL

T-SQL speed comparison between LEFT() vs. LIKE operator

Your best bet would be to measure the performance on real production data rather than trying to guess (or ask us). That's because performance can sometimes depend on the data you're processing, although in this case it seems unlikely (but I don't know that, hence why you should check).

If this is a query you will be doing a lot, you should consider another (indexed) column which contains the lowercased first letter of name and have it set by an insert/update trigger.

This will, at the cost of a minimal storage increase, make this query blindingly fast:

select * from table where name_first_char_lower = @firstletter

That's because most database are read far more often than written, and this will amortise the cost of the calculation (done only for writes) across all reads.

It introduces redundant data but it's okay to do that for performance as long as you understand (and mitigate, as in this suggestion) the consequences and need the extra performance.

T-SQL Performance of != operator against IN operator

The obvious solution is to test out both.

First set up a sample schema:

IF OBJECT_ID(N'dbo.T', 'U') IS NOT NULL DROP TABLE dbo.T;
CREATE TABLE dbo.T 
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    RECONCILIATION_STATUS TINYINT NOT NULL CHECK (RECONCILIATION_STATUS IN (0, 1, 2)),
    Filler CHAR(100) NULL
);

INSERT dbo.T (RECONCILIATION_STATUS)
SELECT  TOP (100000) FLOOR(RAND(CHECKSUM(NEWID())) * 3)
FROM    sys.all_objects a, sys.all_objects b;

Then test with no indexes

SELECT  COUNT(Filler)
FROM    dbo.T
WHERE   RECONCILIATION_STATUS != 1;

SELECT  COUNT(Filler)
FROM    dbo.T
WHERE   RECONCILIATION_STATUS IN (0, 2);

The plans for each are:

As you can see there is a negligible difference here, with no index a clustered index scan is required for both queries.

With so few possible values, a non clustered index is unlikely to be of any use unless you either include all the columns you need regularly as non key columns, or don't have much data. With a standard non clustered index on the 100,000 sample rows built as follows:

CREATE NONCLUSTERED INDEX IX_T__RECONCILIATION_STATUS
    ON dbo.T (RECONCILIATION_STATUS);

The execution plan remains the same with a clustered index scan.

With other columns included as a non key index:

CREATE NONCLUSTERED INDEX IX_T__RECONCILIATION_STATUS
    ON dbo.T (RECONCILIATION_STATUS) INCLUDE (Filler);

The plan for != 1 becomes quite convoluted, and although I wouldn't place much emphasis on its importance, the estimated costs are the same:

However, the IO statistics show that the actual reads required is hardly any different:

Table 'T'. Scan count 2, logical reads 935, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'T'. Scan count 2, logical reads 934, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So thus far, there is little difference, but it really would depend on your data distribution, and what indexes and constraints you have.

Interestingly, if you create a temporary table for the test and define a check constraint on it:

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL DROP TABLE #T;
CREATE TABLE #T 
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    RECONCILIATION_STATUS TINYINT NOT NULL CHECK (RECONCILIATION_STATUS IN (0, 1, 2)),
    Filler CHAR(100) NULL
);

INSERT #T (RECONCILIATION_STATUS)
SELECT  TOP (100000) FLOOR(RAND(CHECKSUM(NEWID())) * 3)
FROM    sys.all_objects a, sys.all_objects b;

The optimiser will actually rewrite this query:

SELECT  COUNT(Filler)
FROM    #T
WHERE   RECONCILIATION_STATUS != 1;

As

SELECT  COUNT(Filler)
FROM    #T
WHERE   RECONCILIATION_STATUS = 0
OR      RECONCILIATION_STATUS = 2;

As demonstrated in this execution plan:

I have not been able to replicate this behaviour on permanent tables though. Nonetheless this leads me to believe the best option is

WHERE   RECONCILIATION_STATUS IN (0, 2);

Not only in terms of performance, although it appears to be marginal or not at all in most cases, but certainly in terms of readability and future proofing for additional values.

There is however, no better way to find out than to run these kind of tests for yourself on your own data. This is going to give you a far better idea of what performs better than any hypothesis I can pull together from a small sample set of data.

SQL: Using = and = to compare string with wildcard

Wildcards are only interpreted when you use LIKE opterator.

So when you are trying to compare against the string, it will be treated literally. So in your comparisons lexicographical order is used.

1) There are no letters before *, so you don't have any rows returned.

2) A is first letter in alphabet, so rest of names are bigger then Abby, only Abby is equal to itself.

3) Opposite of 2)

4) See 1)

5) See 1)

6) This condition is equivalent to Name = 'Abby'.

What is difference between != and in sql server

There is no difference. You can use both in MSSQL.

The MSSQL doc says:

!= functions the same as the <> (Not Equal To) comparison operator.

But <> is defined in the ANSI 99 SQL standard and != is not. So not all DB engines may support it and if you want to generate portable code I recommend using <>.

What is the meaning of the prefix N in T-SQL statements and when should I use it?

It's declaring the string as nvarchar data type, rather than varchar

You may have seen Transact-SQL code that passes strings around using
an N prefix. This denotes that the subsequent string is in Unicode
(the N actually stands for National language character set). Which
means that you are passing an NCHAR, NVARCHAR or NTEXT value, as
opposed to CHAR, VARCHAR or TEXT.

To quote from Microsoft:

Prefix Unicode character string constants with the letter N. Without
the N prefix, the string is converted to the default code page of the
database. This default code page may not recognize certain characters.

---

If you want to know the difference between these two data types, see this SO post:

What is the difference between varchar and nvarchar?

Performance differences between equal (=) and IN with one literal value

There is no difference between those two statements, and the optimiser will transform the IN to the = when IN has just one element in it.

Though when you have a question like this, just run both statements, run their execution plan and see the differences. Here - you won't find any.

After a big search online, I found a document on SQL to support this (I assume it applies to all DBMS):

If there is only one value inside the parenthesis, this commend [sic] is equivalent to,

WHERE "column_name" = 'value1

Here is the execution plan of both queries in Oracle (most DBMS will process this the same):

EXPLAIN PLAN FOR
select * from dim_employees t
where t.identity_number = '123456789'

Plan hash value: 2312174735
-----------------------------------------------------
| Id  | Operation                   | Name          |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |               |
|   1 |  TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES |
|   2 |   INDEX UNIQUE SCAN         | SYS_C0029838  |
-----------------------------------------------------

And for IN() :

EXPLAIN PLAN FOR
select * from dim_employees t
where t.identity_number in('123456789');

Plan hash value: 2312174735
-----------------------------------------------------
| Id  | Operation                   | Name          |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |               |
|   1 |  TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES |
|   2 |   INDEX UNIQUE SCAN         | SYS_C0029838  |
-----------------------------------------------------

As you can see, both are identical. This is on an indexed column. Same goes for an unindexed column (just full table scan).

---

Related Topics