Sql. How to Check If Record Exists in Table

SQL: How to properly check if a record exists

It's better to use either of the following:

-- Method 1.
SELECT 1
FROM table_name
WHERE unique_key = value;

-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE unique_key = value;

The first alternative should give you no result or one result, the second count should be zero or one.

How old is the documentation you're using? Although you've read good advice, most query optimizers in recent RDBMS's optimize SELECT COUNT(*) anyway, so while there is a difference in theory (and older databases), you shouldn't notice any difference in practice.

Fastest way to determine if record exists

SELECT TOP 1 products.id FROM products WHERE products.id = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.

Query to check if record exists and also column value in table is null

Based on your clarifications in the comments I think you want to test for the following 2 conditions:

Condition 1: User record exists but user_id is null

select 1
from UserAccount UA
inner join Contact as C on UA.email = C.email and C.[type] = 'Director'
where C.InvId = @InvId
-- User Record exists
and exists (select 1 from Users U where U.email = UA.email)
-- user_id is null
and UA.[user_id] is null

Condition 2: User record doesn't exist

select 1
from UserAccount UA
inner join Contact as C on UA.email = C.email and C.[type] = 'Director'
where C.InvId = @InvId
-- User Record doesn't exist
and not exists (select 1 from Users U where U.email = UA.email)

These can be combined with an OR to test both conditions as follows:

declare @userExists int;

if exists (
select 1
from UserAccount UA
inner join Contact as C on UA.email = C.email and C.[type] = 'Director'
where C.InvId = @InvId
and (
(UA.[user_id] is null and exists (select 1 from Users U where U.email = UA.email))
or not exists (select 1 from Users U where U.email = UA.email)
)
)
begin
set @userExists = 1;
end;

As an aside should the variable really be called @userExists? Isn't it @userNotExists?

Note 1: It would be worth knowing what you then use the variable @userExists for - for example if you use it to update a record then most likely the update and test can be combined into a single statement.

Note 2: I have to assume this is only for test purposes? Because joining on email address isn't a recommended practise - unless you are prepared to put a unique constraint on email address, but some people still do share an email address.

Note 3: Its best practice to be consistent in your table naming as to whether you use the singular or plural form. You've used Users (plural) and Contact (singular).

How to check if a specific record exists in a table in SQL Server?

It's so easy to use Exist keyword,
I have written correct syntax below , check it out

if exists (select * from  [dbo].[table] where id= [the id you want to check] ) 
select 'True'
else
select 'False'
return

What is the best way to check if a record exists in a SQL Server table using C#?

Exists is more efficient than Count, because count needs to scan all rows to match the criteria and include in the count, exist dont.

So exists with ExecuteScalar is better.

As more info backing this:

According to http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx

Both queries scanned the table but the EXISTS was able to at least do
a partial scan do to the fact it can stop after it finds the very
first matching row. Where as the COUNT() must read each and every row
in the entire table to determine if they match the criteria and how
many there are. That is the key folks. The ability to stop working
after the first row that meets the criteria of the WHERE clause is
what makes EXISTS so efficient. The optimizer knows of this behavior
and can factor that in as well. Now keep in mind that these tables are
relatively small compared to most databases in the real world. So the
figures of the COUNT(
) queries would be multiplied many times on
larger tables. You could easily get hundred's of thousands of reads or
more on tables with millions of rows but the EXISTS will still only
have just a few reads on any queries that can use an index to satisfy
the WHERE clause.

As a simple experiment using AdventureWorks with MSSQL 2012

set showplan_all on

-- TotalSubtreeCost: 0.06216168
select count(*) from sales.Customer

-- TotalSubtreeCost: 0.003288537
select 1 where exists (select * from sales.Customer)

See also

http://sqlmag.com/t-sql/exists-vs-count

UPDATE: On ExecuteScalar vs ExecuteReader.
Having a look with a disassembler (like Reflector) on the Implementation of System.Data.SqlClient.SqlCommand methods, shows something surprising, they are kind of equivalent: both end up calling the internal helper
internal SqlDataReader RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, bool returnStream, string method, TaskCompletionSource completion, int timeout, out Task task, bool asyncWrite = false)

which returns a SqlDataReader, the ExecuteReader returns it as is.
While ExecuteScalar consumes it with another helper:

private object CompleteExecuteScalar(SqlDataReader ds, bool returnSqlValue)
{
object obj2 = null;
try
{
if (!ds.Read() || (ds.FieldCount <= 0))
{
return obj2;
}
if (returnSqlValue)
{
return ds.GetSqlValue(0);
}
obj2 = ds.GetValue(0);
}
finally
{
ds.Close();
}
return obj2;
}

As a side note, same goes with MySQL Connector/NET (The official ADO.NET open source driver for MySQL), the method ExecuteScalar internally creates an DataReader (MySqlDataReader to be more precise) and consumes it. See on source file /Src/Command.cs (from https://dev.mysql.com/downloads/connector/net/ or https://github.com/mysql/mysql-connector-net).

Summary: Regarding the ExecuteScalar vs ExecuteReader both incurr in the overhead of creating a SqlDataReader, I would say the difference is mostly idiomatic.

Fastest way to check the records if exists in the SQL table

First, You should use the EXISTS statement instead of selecting top 1:

SET @getRowCount = EXISTS(select 1
from MYTable
where ID IN ('3','5','2','4','1')
AND datewithTime >= '2015-01-01 07:00:00'
AND datewithTime < '2016-01-01 07:00:00'
)

Second, you should check the execution plan to see if you can improve performance by adding indices or altering existing indices.

update

Sorry, I wasn't paying enough attention to what I'm writing.
Exists returns a boolean value, but sql server does not have a boolean data type, this is why you get the incorrect syntax error.

Here is the correct syntax:

DECLARE  @getRowCount bit = 0

IF EXISTS(select 1
from MYTable
where ID IN ('3','5','2','4','1')
AND datewithTime >= '2015-01-01 07:00:00'
AND datewithTime < '2016-01-01 07:00:00'
) SET @getRowCount = 1

SELECT @getRowCount

Check if record exists then insert new row in database table?

I don't know/work-with coldfusion so not sure I'm reading the logic correctly ...

  • if record does not exist in table1 but
  • record does exit in contact then
  • insert a row into inter_work_tbl

The general T-SQL query would look like (note: mixing T-SQL with references to the coldfusion variables):

insert into inter_work_tbl

(user_id
,first_name
,last_name
,password)

select '#session.user_id#',
c.fname,
c.lname,
'#password#'

from contact c

where c.userid = #session.user_id#
and not exists(select 1
from table1 t
where t.user_id = c.userid)

Check if record exists before adding it to the database

If you work in MS Excel, this is a sample of what you can use:

Public Sub CheckDataInSQLServer()

Dim cnLogs As Object
Dim rsData As Object

On Error GoTo CheckDataInSQLServer_Error

Set cnLogs = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")

cnLogs.Open ConnectionString 'this is a string function

rsData.ActiveConnection = cnLogs
rsData.Open "SELECT * FROM A WHERE B = C;"

If rsData.EOF Then
debug.print "no values"
Else
debug.print "has values"
End If

End Sub

In VBA the function that you need is called DCount, if you work with MS Access:

You can use the DCount function to determine the number of records that are in a specified set of records (a domain). Use the DCount function in Visual Basic, a macro, a query expression, or a calculated control. For example, you could use the DCount function in a module to return the number of records in an Orders table that correspond to orders placed on a particular date.
MSDN

See more how to use DCount - DCount with 2 criteria

This is a sample how to build DCount in a function and to use it:

Public Function OrdersCount(ByVal strCountry As String, ByVal dteShipDate As Date) As Long
OrdersCount = DCount("[ShippedDate]", "Orders", _
"[ShipCountry] = '" & strCountry & _
"' AND [ShippedDate] > #" & dteShipDate & "#")
End Function

If OrdersCount("Bulgaria", Now()) = 0 then
'Execute the SQL query.
End if


Related Topics



Leave a reply



Submit