Is my table just set up wrong?

No, I just meant I wouldn't use EXISTS in a case like this.  This is not an unusual type of query, and your...

I can't create a primary key on a calculated field

Yes, you can:

CREATE TABLE TestId(

Id int IDENTITY(0,1) NOT NULL,

FakeId AS ISNULL(Id%100,-1),

Dat datetime NOT NULL DEFAULT GETDATE(),

PRIMARY KEY(FakeId,Dat))

1)

DECLARE @t datetime

SET @t = GETDATE()

--first query here

PRINT DATEDIFF(ms,@t,GETDATE())

SET @t = GETDATE()

--second query here

PRINT DATEDIFF(ms,@t,GETDATE())

2)   I should study some basic SQL, your...

You may want to study some more basic SQL, as your new subquery used with EXISTS should of course be just:

(SELECT *

FROM dbo.tbl_MachineInstalls AS MIi WITH(NOLOCK)

WHERE MIi.MachineID = MIo.MachineID AND...

Your first and last queries are essentially the same, and I doubt the execution plans differ.  When you're dealing with such short times, one second is probably within the margin...

Well, you of course mean "TOP 100...ORDER BY DATE DESC".  But what happens when you've got 150 rows and someone deletes rows 50-99?  Your TOP query would then return 50 pairs...

Actually, you should rethink this design.  You are violating Codd's "Information Rule" by using the table identifier to convey information.  This will lead to many more kludges like this.  You...

By moving the predicate from the join clause to the where clause, you are restricting the result set to just those rows satisfying the predicate, rather than including rows that...

You can use DBCC CHECKIDENT (<TableName>, RESEED, 1) or just truncate the table.

1. How this binary store the information and why

CAST(1 as binary(1)) + cast(2 as binary(1)) is not equal to

CAST(2 as binary(1)) + cast(1 as binary(1))

which in my case...

Do you mean that you also want to ensure that there are no more than 100 rows in the table?  And somehow re-use the numbers that were assigned to randomly...

Unless obscurantism and complexity are priorities, this seems like a quixotic exercise.  Why not just use a binary(20) so you can at least decompose the thing later.  That would allow for...

Well, obviously it makes a difference. 

This issue has to do with the way the query optimizer works with a batch.  The entire script...

Please explain how "up to 20 numbers within the range of 00 to 20" corresponds to a varchar(100) value of '1236547890-951736824-978645312-582963174'.

If you must "compare" the values "entered" by two "users," what...

You haven't posted the relevant information.  Does this work for you?

use pubs

go

select top 0 * into #MyTable from authors

alter table #MyTable add constraint PK_MyTable...