(Because people are wondering what would be the purpose of such a database) I am writing a SQL Server program playing CHESS! I already have a working code playing chess on the ELO 2000 level. I want to optimize it and to play faster.

Of course, T-SQL is perhaps the worst language to play chess, but I am doing it as a challenge.

In my current working version, chess board is stored as char(64) and I have 64 tinyint columns representing each square with numeric values representing chess pieces. The code works and plays the game reasonably well, but it is relatively slow.

I want to convert the code to using bit-based board and piece locations representation. I hope it will be faster, or at least, as fast as technically possible.

Let's say we have two values expressed in bits:

Value #1:

1000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000

Value #2:

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000

Those values are 8 bytes (64 bits). Those values have to be converted to bigint in SQL Server in order to perform bitwise operations.

The problem: because there is only signed bigint in SQL Server, therefore the 1st bit is a sign bit.

The value #1 will be converted to bigint -0 (update: I was wrong here), and then bigint 0. When all conversions are done, value #1 is converted to 0 (update: I was wrong here), and value #2 is converted to 0.

This way the 1st bit is lost! (update: I was wrong here)

The lack of unsigned bigint/int types make bitwise operations useless in many cases!

Any ideas?

I am trying to use bitwise operations in SQL Server. Unfortunately, I cannot use bitwise in SQL Server because 1st bit is used for sign and it is lost in some cases.

---

An update: below is a sample code. My tables have a lot of bit columns (>100). There are a few critical queries where I join the tables on 64 bit columns.

My hope is that replacing groups of 64 bit columns with bigint columns will significantly improve performance.

I was wrong about conversion to bigint: not a single bit is lost. The conversion is still unintuitive because of the sign bit...

Code description: 2 tables #a and #b are defined at the top. Then some data is generated. The critical part of the code starts here: PRINT 'Start: '

Sample code:

SET NOCOUNT ON
DROP TABLE IF EXISTS #a,#b;
CREATE TABLE #a (id int NOT NULL IDENTITY 
,aa1 bit
,aa2 bit
,aa3 bit
,aa4 bit
,aa5 bit
,aa6 bit
,aa7 bit
,aa8 bit
,ab1 bit
,ab2 bit
,ab3 bit
,ab4 bit
,ab5 bit
,ab6 bit
,ab7 bit
,ab8 bit
,ac1 bit
,ac2 bit
,ac3 bit
,ac4 bit
,ac5 bit
,ac6 bit
,ac7 bit
,ac8 bit
,ad1 bit
,ad2 bit
,ad3 bit
,ad4 bit
,ad5 bit
,ad6 bit
,ad7 bit
,ad8 bit
,ae1 bit
,ae2 bit
,ae3 bit
,ae4 bit
,ae5 bit
,ae6 bit
,ae7 bit
,ae8 bit
,af1 bit
,af2 bit
,af3 bit
,af4 bit
,af5 bit
,af6 bit
,af7 bit
,af8 bit
,ag1 bit
,ag2 bit
,ag3 bit
,ag4 bit
,ag5 bit
,ag6 bit
,ag7 bit
,ag8 bit
,ah1 bit
,ah2 bit
,ah3 bit
,ah4 bit
,ah5 bit
,ah6 bit
,ah7 bit
,ah8 bit
,ba1 bit
,ba2 bit
,ba3 bit
,ba4 bit
,ba5 bit
,ba6 bit
,ba7 bit
,ba8 bit
,bb1 bit
,bb2 bit
,bb3 bit
,bb4 bit
,bb5 bit
,bb6 bit
,bb7 bit
,bb8 bit
,bc1 bit
,bc2 bit
,bc3 bit
,bc4 bit
,bc5 bit
,bc6 bit
,bc7 bit
,bc8 bit
,bd1 bit
,bd2 bit
,bd3 bit
,bd4 bit
,bd5 bit
,bd6 bit
,bd7 bit
,bd8 bit
,be1 bit
,be2 bit
,be3 bit
,be4 bit
,be5 bit
,be6 bit
,be7 bit
,be8 bit
,bf1 bit
,bf2 bit
,bf3 bit
,bf4 bit
,bf5 bit
,bf6 bit
,bf7 bit
,bf8 bit
,bg1 bit
,bg2 bit
,bg3 bit
,bg4 bit
,bg5 bit
,bg6 bit
,bg7 bit
,bg8 bit
,bh1 bit
,bh2 bit
,bh3 bit
,bh4 bit
,bh5 bit
,bh6 bit
,bh7 bit
,bh8 bit
,ca1 bit
,ca2 bit
,ca3 bit
,ca4 bit
,ca5 bit
,ca6 bit
,ca7 bit
,ca8 bit
,cb1 bit
,cb2 bit
,cb3 bit
,cb4 bit
,cb5 bit
,cb6 bit
,cb7 bit
,cb8 bit
,cc1 bit
,cc2 bit
,cc3 bit
,cc4 bit
,cc5 bit
,cc6 bit
,cc7 bit
,cc8 bit
,cd1 bit
,cd2 bit
,cd3 bit
,cd4 bit
,cd5 bit
,cd6 bit
,cd7 bit
,cd8 bit
,ce1 bit
,ce2 bit
,ce3 bit
,ce4 bit
,ce5 bit
,ce6 bit
,ce7 bit
,ce8 bit
,cf1 bit
,cf2 bit
,cf3 bit
,cf4 bit
,cf5 bit
,cf6 bit
,cf7 bit
,cf8 bit
,cg1 bit
,cg2 bit
,cg3 bit
,cg4 bit
,cg5 bit
,cg6 bit
,cg7 bit
,cg8 bit
,ch1 bit
,ch2 bit
,ch3 bit
,ch4 bit
,ch5 bit
,ch6 bit
,ch7 bit
,ch8 bit);
CREATE TABLE #b (id int NOT NULL IDENTITY 
,aa1 bit
,aa2 bit
,aa3 bit
,aa4 bit
,aa5 bit
,aa6 bit
,aa7 bit
,aa8 bit
,ab1 bit
,ab2 bit
,ab3 bit
,ab4 bit
,ab5 bit
,ab6 bit
,ab7 bit
,ab8 bit
,ac1 bit
,ac2 bit
,ac3 bit
,ac4 bit
,ac5 bit
,ac6 bit
,ac7 bit
,ac8 bit
,ad1 bit
,ad2 bit
,ad3 bit
,ad4 bit
,ad5 bit
,ad6 bit
,ad7 bit
,ad8 bit
,ae1 bit
,ae2 bit
,ae3 bit
,ae4 bit
,ae5 bit
,ae6 bit
,ae7 bit
,ae8 bit
,af1 bit
,af2 bit
,af3 bit
,af4 bit
,af5 bit
,af6 bit
,af7 bit
,af8 bit
,ag1 bit
,ag2 bit
,ag3 bit
,ag4 bit
,ag5 bit
,ag6 bit
,ag7 bit
,ag8 bit
,ah1 bit
,ah2 bit
,ah3 bit
,ah4 bit
,ah5 bit
,ah6 bit
,ah7 bit
,ah8 bit
,ba1 bit
,ba2 bit
,ba3 bit
,ba4 bit
,ba5 bit
,ba6 bit
,ba7 bit
,ba8 bit
,bb1 bit
,bb2 bit
,bb3 bit
,bb4 bit
,bb5 bit
,bb6 bit
,bb7 bit
,bb8 bit
,bc1 bit
,bc2 bit
,bc3 bit
,bc4 bit
,bc5 bit
,bc6 bit
,bc7 bit
,bc8 bit
,bd1 bit
,bd2 bit
,bd3 bit
,bd4 bit
,bd5 bit
,bd6 bit
,bd7 bit
,bd8 bit
,be1 bit
,be2 bit
,be3 bit
,be4 bit
,be5 bit
,be6 bit
,be7 bit
,be8 bit
,bf1 bit
,bf2 bit
,bf3 bit
,bf4 bit
,bf5 bit
,bf6 bit
,bf7 bit
,bf8 bit
,bg1 bit
,bg2 bit
,bg3 bit
,bg4 bit
,bg5 bit
,bg6 bit
,bg7 bit
,bg8 bit
,bh1 bit
,bh2 bit
,bh3 bit
,bh4 bit
,bh5 bit
,bh6 bit
,bh7 bit
,bh8 bit
,ca1 bit
,ca2 bit
,ca3 bit
,ca4 bit
,ca5 bit
,ca6 bit
,ca7 bit
,ca8 bit
,cb1 bit
,cb2 bit
,cb3 bit
,cb4 bit
,cb5 bit
,cb6 bit
,cb7 bit
,cb8 bit
,cc1 bit
,cc2 bit
,cc3 bit
,cc4 bit
,cc5 bit
,cc6 bit
,cc7 bit
,cc8 bit
,cd1 bit
,cd2 bit
,cd3 bit
,cd4 bit
,cd5 bit
,cd6 bit
,cd7 bit
,cd8 bit
,ce1 bit
,ce2 bit
,ce3 bit
,ce4 bit
,ce5 bit
,ce6 bit
,ce7 bit
,ce8 bit
,cf1 bit
,cf2 bit
,cf3 bit
,cf4 bit
,cf5 bit
,cf6 bit
,cf7 bit
,cf8 bit
,cg1 bit
,cg2 bit
,cg3 bit
,cg4 bit
,cg5 bit
,cg6 bit
,cg7 bit
,cg8 bit
,ch1 bit
,ch2 bit
,ch3 bit
,ch4 bit
,ch5 bit
,ch6 bit
,ch7 bit
,ch8 bit);

INSERT INTO #a VALUES (
 1,1,1,1,1,1,1,1
,0,0,0,0,0,0,0,0
,1,0,1,0,1,0,1,0
,1,0,1,0,1,0,1,0
,1,0,1,0,1,0,1,0
,1,0,1,0,1,0,1,0
,1,0,1,0,1,0,1,0
,1,0,1,0,1,0,1,0

,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1

,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
)
,(
 0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,1,0
,0,0,0,0,0,0,0,0

,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,0,1,0,1,0,1,0
,0,0,0,0,0,0,1,0
,1,0,1,0,1,0,1,0
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1

,1,1,1,1,1,1,1,1
,0,0,0,0,0,0,0,1
,1,1,1,1,1,1,1,1
,0,1,0,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
)

INSERT INTO #b (aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,ab1,ab2,ab3,ab4,ab5,ab6,ab7,ab8,ac1,ac2,ac3,ac4,ac5,ac6,ac7,ac8
,ad1,ad2,ad3,ad4,ad5,ad6,ad7,ad8,ae1,ae2,ae3,ae4,ae5,ae6,ae7,ae8,af1,af2,af3,af4,af5,af6,af7,af8
,ag1,ag2,ag3,ag4,ag5,ag6,ag7,ag8,ah1,ah2,ah3,ah4,ah5,ah6,ah7,ah8,ba1,ba2,ba3,ba4,ba5,ba6,ba7,ba8
,bb1,bb2,bb3,bb4,bb5,bb6,bb7,bb8,bc1,bc2,bc3,bc4,bc5,bc6,bc7,bc8,bd1,bd2,bd3,bd4,bd5,bd6,bd7,bd8
,be1,be2,be3,be4,be5,be6,be7,be8,bf1,bf2,bf3,bf4,bf5,bf6,bf7,bf8,bg1,bg2,bg3,bg4,bg5,bg6,bg7,bg8
,bh1,bh2,bh3,bh4,bh5,bh6,bh7,bh8,ca1,ca2,ca3,ca4,ca5,ca6,ca7,ca8,cb1,cb2,cb3,cb4,cb5,cb6,cb7,cb8
,cc1,cc2,cc3,cc4,cc5,cc6,cc7,cc8,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,ce1,ce2,ce3,ce4,ce5,ce6,ce7,ce8
,cf1,cf2,cf3,cf4,cf5,cf6,cf7,cf8,cg1,cg2,cg3,cg4,cg5,cg6,cg7,cg8,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8)
SELECT aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,ab1,ab2,ab3,ab4,ab5,ab6,ab7,ab8,ac1,ac2,ac3,ac4,ac5,ac6,ac7,ac8
,ad1,ad2,ad3,ad4,ad5,ad6,ad7,ad8,ae1,ae2,ae3,ae4,ae5,ae6,ae7,ae8,af1,af2,af3,af4,af5,af6,af7,af8
,ag1,ag2,ag3,ag4,ag5,ag6,ag7,ag8,ah1,ah2,ah3,ah4,ah5,ah6,ah7,ah8,ba1,ba2,ba3,ba4,ba5,ba6,ba7,ba8
,bb1,bb2,bb3,bb4,bb5,bb6,bb7,bb8,bc1,bc2,bc3,bc4,bc5,bc6,bc7,bc8,bd1,bd2,bd3,bd4,bd5,bd6,bd7,bd8
,be1,be2,be3,be4,be5,be6,be7,be8,bf1,bf2,bf3,bf4,bf5,bf6,bf7,bf8,bg1,bg2,bg3,bg4,bg5,bg6,bg7,bg8
,bh1,bh2,bh3,bh4,bh5,bh6,bh7,bh8,ca1,ca2,ca3,ca4,ca5,ca6,ca7,ca8,cb1,cb2,cb3,cb4,cb5,cb6,cb7,cb8
,cc1,cc2,cc3,cc4,cc5,cc6,cc7,cc8,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,ce1,ce2,ce3,ce4,ce5,ce6,ce7,ce8
,cf1,cf2,cf3,cf4,cf5,cf6,cf7,cf8,cg1,cg2,cg3,cg4,cg5,cg6,cg7,cg8,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8
    FROM #a 


INSERT INTO #b (aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,ab1,ab2,ab3,ab4,ab5,ab6,ab7,ab8,ac1,ac2,ac3,ac4,ac5,ac6,ac7,ac8
,ad1,ad2,ad3,ad4,ad5,ad6,ad7,ad8,ae1,ae2,ae3,ae4,ae5,ae6,ae7,ae8,af1,af2,af3,af4,af5,af6,af7,af8
,ag1,ag2,ag3,ag4,ag5,ag6,ag7,ag8,ah1,ah2,ah3,ah4,ah5,ah6,ah7,ah8,ba1,ba2,ba3,ba4,ba5,ba6,ba7,ba8
,bb1,bb2,bb3,bb4,bb5,bb6,bb7,bb8,bc1,bc2,bc3,bc4,bc5,bc6,bc7,bc8,bd1,bd2,bd3,bd4,bd5,bd6,bd7,bd8
,be1,be2,be3,be4,be5,be6,be7,be8,bf1,bf2,bf3,bf4,bf5,bf6,bf7,bf8,bg1,bg2,bg3,bg4,bg5,bg6,bg7,bg8
,bh1,bh2,bh3,bh4,bh5,bh6,bh7,bh8,ca1,ca2,ca3,ca4,ca5,ca6,ca7,ca8,cb1,cb2,cb3,cb4,cb5,cb6,cb7,cb8
,cc1,cc2,cc3,cc4,cc5,cc6,cc7,cc8,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,ce1,ce2,ce3,ce4,ce5,ce6,ce7,ce8
,cf1,cf2,cf3,cf4,cf5,cf6,cf7,cf8,cg1,cg2,cg3,cg4,cg5,cg6,cg7,cg8,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8) 
VALUES (
 0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0

,1,1,1,1,1,1,1,1
,1,0,1,0,1,0,1,0
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,0,1,0,1,0,1,0
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1

,0,0,0,1,1,0,0,0
,0,0,0,0,1,0,0,0
,0,0,0,0,0,0,0,0
,1,1,1,1,1,1,1,1
,0,0,0,1,1,0,0,0
,0,0,0,1,1,0,0,0
,1,1,1,1,1,1,1,1
,0,0,0,1,1,0,0,0
);

INSERT INTO #a (aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,ab1,ab2,ab3,ab4,ab5,ab6,ab7,ab8,ac1,ac2,ac3,ac4,ac5,ac6,ac7,ac8
,ad1,ad2,ad3,ad4,ad5,ad6,ad7,ad8,ae1,ae2,ae3,ae4,ae5,ae6,ae7,ae8,af1,af2,af3,af4,af5,af6,af7,af8
,ag1,ag2,ag3,ag4,ag5,ag6,ag7,ag8,ah1,ah2,ah3,ah4,ah5,ah6,ah7,ah8,ba1,ba2,ba3,ba4,ba5,ba6,ba7,ba8
,bb1,bb2,bb3,bb4,bb5,bb6,bb7,bb8,bc1,bc2,bc3,bc4,bc5,bc6,bc7,bc8,bd1,bd2,bd3,bd4,bd5,bd6,bd7,bd8
,be1,be2,be3,be4,be5,be6,be7,be8,bf1,bf2,bf3,bf4,bf5,bf6,bf7,bf8,bg1,bg2,bg3,bg4,bg5,bg6,bg7,bg8
,bh1,bh2,bh3,bh4,bh5,bh6,bh7,bh8,ca1,ca2,ca3,ca4,ca5,ca6,ca7,ca8,cb1,cb2,cb3,cb4,cb5,cb6,cb7,cb8
,cc1,cc2,cc3,cc4,cc5,cc6,cc7,cc8,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,ce1,ce2,ce3,ce4,ce5,ce6,ce7,ce8
,cf1,cf2,cf3,cf4,cf5,cf6,cf7,cf8,cg1,cg2,cg3,cg4,cg5,cg6,cg7,cg8,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8)
SELECT aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,ab1,ab2,ab3,ab4,ab5,ab6,ab7,ab8,ac1,ac2,ac3,ac4,ac5,ac6,ac7,ac8
,ad1,ad2,ad3,ad4,ad5,ad6,ad7,ad8,ae1,ae2,ae3,ae4,ae5,ae6,ae7,ae8,af1,af2,af3,af4,af5,af6,af7,af8
,ag1,ag2,ag3,ag4,ag5,ag6,ag7,ag8,ah1,ah2,ah3,ah4,ah5,ah6,ah7,ah8,ba1,ba2,ba3,ba4,ba5,ba6,ba7,ba8
,bb1,bb2,bb3,bb4,bb5,bb6,bb7,bb8,bc1,bc2,bc3,bc4,bc5,bc6,bc7,bc8,bd1,bd2,bd3,bd4,bd5,bd6,bd7,bd8
,be1,be2,be3,be4,be5,be6,be7,be8,bf1,bf2,bf3,bf4,bf5,bf6,bf7,bf8,bg1,bg2,bg3,bg4,bg5,bg6,bg7,bg8
,bh1,bh2,bh3,bh4,bh5,bh6,bh7,bh8,ca1,ca2,ca3,ca4,ca5,ca6,ca7,ca8,cb1,cb2,cb3,cb4,cb5,cb6,cb7,cb8
,cc1,cc2,cc3,cc4,cc5,cc6,cc7,cc8,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,ce1,ce2,ce3,ce4,ce5,ce6,ce7,ce8
,cf1,cf2,cf3,cf4,cf5,cf6,cf7,cf8,cg1,cg2,cg3,cg4,cg5,cg6,cg7,cg8,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8
    FROM #a
INSERT INTO #a (aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,ab1,ab2,ab3,ab4,ab5,ab6,ab7,ab8,ac1,ac2,ac3,ac4,ac5,ac6,ac7,ac8
,ad1,ad2,ad3,ad4,ad5,ad6,ad7,ad8,ae1,ae2,ae3,ae4,ae5,ae6,ae7,ae8,af1,af2,af3,af4,af5,af6,af7,af8
,ag1,ag2,ag3,ag4,ag5,ag6,ag7,ag8,ah1,ah2,ah3,ah4,ah5,ah6,ah7,ah8,ba1,ba2,ba3,ba4,ba5,ba6,ba7,ba8
,bb1,bb2,bb3,bb4,bb5,bb6,bb7,bb8,bc1,bc2,bc3,bc4,bc5,bc6,bc7,bc8,bd1,bd2,bd3,bd4,bd5,bd6,bd7,bd8
,be1,be2,be3,be4,be5,be6,be7,be8,bf1,bf2,bf3,bf4,bf5,bf6,bf7,bf8,bg1,bg2,bg3,bg4,bg5,bg6,bg7,bg8
,bh1,bh2,bh3,bh4,bh5,bh6,bh7,bh8,ca1,ca2,ca3,ca4,ca5,ca6,ca7,ca8,cb1,cb2,cb3,cb4,cb5,cb6,cb7,cb8
,cc1,cc2,cc3,cc4,cc5,cc6,cc7,cc8,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,ce1,ce2,ce3,ce4,ce5,ce6,ce7,ce8
,cf1,cf2,cf3,cf4,cf5,cf6,cf7,cf8,cg1,cg2,cg3,cg4,cg5,cg6,cg7,cg8,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8)
SELECT aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,ab1,ab2,ab3,ab4,ab5,ab6,ab7,ab8,ac1,ac2,ac3,ac4,ac5,ac6,ac7,ac8
,ad1,ad2,ad3,ad4,ad5,ad6,ad7,ad8,ae1,ae2,ae3,ae4,ae5,ae6,ae7,ae8,af1,af2,af3,af4,af5,af6,af7,af8
,ag1,ag2,ag3,ag4,ag5,ag6,ag7,ag8,ah1,ah2,ah3,ah4,ah5,ah6,ah7,ah8,ba1,ba2,ba3,ba4,ba5,ba6,ba7,ba8
,bb1,bb2,bb3,bb4,bb5,bb6,bb7,bb8,bc1,bc2,bc3,bc4,bc5,bc6,bc7,bc8,bd1,bd2,bd3,bd4,bd5,bd6,bd7,bd8
,be1,be2,be3,be4,be5,be6,be7,be8,bf1,bf2,bf3,bf4,bf5,bf6,bf7,bf8,bg1,bg2,bg3,bg4,bg5,bg6,bg7,bg8
,bh1,bh2,bh3,bh4,bh5,bh6,bh7,bh8,ca1,ca2,ca3,ca4,ca5,ca6,ca7,ca8,cb1,cb2,cb3,cb4,cb5,cb6,cb7,cb8
,cc1,cc2,cc3,cc4,cc5,cc6,cc7,cc8,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,ce1,ce2,ce3,ce4,ce5,ce6,ce7,ce8
,cf1,cf2,cf3,cf4,cf5,cf6,cf7,cf8,cg1,cg2,cg3,cg4,cg5,cg6,cg7,cg8,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8
    FROM #a
GO
INSERT INTO #a (aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,ab1,ab2,ab3,ab4,ab5,ab6,ab7,ab8,ac1,ac2,ac3,ac4,ac5,ac6,ac7,ac8
,ad1,ad2,ad3,ad4,ad5,ad6,ad7,ad8,ae1,ae2,ae3,ae4,ae5,ae6,ae7,ae8,af1,af2,af3,af4,af5,af6,af7,af8
,ag1,ag2,ag3,ag4,ag5,ag6,ag7,ag8,ah1,ah2,ah3,ah4,ah5,ah6,ah7,ah8,ba1,ba2,ba3,ba4,ba5,ba6,ba7,ba8
,bb1,bb2,bb3,bb4,bb5,bb6,bb7,bb8,bc1,bc2,bc3,bc4,bc5,bc6,bc7,bc8,bd1,bd2,bd3,bd4,bd5,bd6,bd7,bd8
,be1,be2,be3,be4,be5,be6,be7,be8,bf1,bf2,bf3,bf4,bf5,bf6,bf7,bf8,bg1,bg2,bg3,bg4,bg5,bg6,bg7,bg8
,bh1,bh2,bh3,bh4,bh5,bh6,bh7,bh8,ca1,ca2,ca3,ca4,ca5,ca6,ca7,ca8,cb1,cb2,cb3,cb4,cb5,cb6,cb7,cb8
,cc1,cc2,cc3,cc4,cc5,cc6,cc7,cc8,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,ce1,ce2,ce3,ce4,ce5,ce6,ce7,ce8
,cf1,cf2,cf3,cf4,cf5,cf6,cf7,cf8,cg1,cg2,cg3,cg4,cg5,cg6,cg7,cg8,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8)
SELECT aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,ab1,ab2,ab3,ab4,ab5,ab6,ab7,ab8,ac1,ac2,ac3,ac4,ac5,ac6,ac7,ac8
,ad1,ad2,ad3,ad4,ad5,ad6,ad7,ad8,ae1,ae2,ae3,ae4,ae5,ae6,ae7,ae8,af1,af2,af3,af4,af5,af6,af7,af8
,ag1,ag2,ag3,ag4,ag5,ag6,ag7,ag8,ah1,ah2,ah3,ah4,ah5,ah6,ah7,ah8,ba1,ba2,ba3,ba4,ba5,ba6,ba7,ba8
,bb1,bb2,bb3,bb4,bb5,bb6,bb7,bb8,bc1,bc2,bc3,bc4,bc5,bc6,bc7,bc8,bd1,bd2,bd3,bd4,bd5,bd6,bd7,bd8
,be1,be2,be3,be4,be5,be6,be7,be8,bf1,bf2,bf3,bf4,bf5,bf6,bf7,bf8,bg1,bg2,bg3,bg4,bg5,bg6,bg7,bg8
,bh1,bh2,bh3,bh4,bh5,bh6,bh7,bh8,ca1,ca2,ca3,ca4,ca5,ca6,ca7,ca8,cb1,cb2,cb3,cb4,cb5,cb6,cb7,cb8
,cc1,cc2,cc3,cc4,cc5,cc6,cc7,cc8,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,ce1,ce2,ce3,ce4,ce5,ce6,ce7,ce8
,cf1,cf2,cf3,cf4,cf5,cf6,cf7,cf8,cg1,cg2,cg3,cg4,cg5,cg6,cg7,cg8,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8
    FROM #a
INSERT INTO #b (aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,ab1,ab2,ab3,ab4,ab5,ab6,ab7,ab8,ac1,ac2,ac3,ac4,ac5,ac6,ac7,ac8
,ad1,ad2,ad3,ad4,ad5,ad6,ad7,ad8,ae1,ae2,ae3,ae4,ae5,ae6,ae7,ae8,af1,af2,af3,af4,af5,af6,af7,af8
,ag1,ag2,ag3,ag4,ag5,ag6,ag7,ag8,ah1,ah2,ah3,ah4,ah5,ah6,ah7,ah8,ba1,ba2,ba3,ba4,ba5,ba6,ba7,ba8
,bb1,bb2,bb3,bb4,bb5,bb6,bb7,bb8,bc1,bc2,bc3,bc4,bc5,bc6,bc7,bc8,bd1,bd2,bd3,bd4,bd5,bd6,bd7,bd8
,be1,be2,be3,be4,be5,be6,be7,be8,bf1,bf2,bf3,bf4,bf5,bf6,bf7,bf8,bg1,bg2,bg3,bg4,bg5,bg6,bg7,bg8
,bh1,bh2,bh3,bh4,bh5,bh6,bh7,bh8,ca1,ca2,ca3,ca4,ca5,ca6,ca7,ca8,cb1,cb2,cb3,cb4,cb5,cb6,cb7,cb8
,cc1,cc2,cc3,cc4,cc5,cc6,cc7,cc8,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,ce1,ce2,ce3,ce4,ce5,ce6,ce7,ce8
,cf1,cf2,cf3,cf4,cf5,cf6,cf7,cf8,cg1,cg2,cg3,cg4,cg5,cg6,cg7,cg8,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8)
SELECT aa1,aa2,aa3,aa4,aa5,aa6,aa7,aa8,ab1,ab2,ab3,ab4,ab5,ab6,ab7,ab8,ac1,ac2,ac3,ac4,ac5,ac6,ac7,ac8
,ad1,ad2,ad3,ad4,ad5,ad6,ad7,ad8,ae1,ae2,ae3,ae4,ae5,ae6,ae7,ae8,af1,af2,af3,af4,af5,af6,af7,af8
,ag1,ag2,ag3,ag4,ag5,ag6,ag7,ag8,ah1,ah2,ah3,ah4,ah5,ah6,ah7,ah8,ba1,ba2,ba3,ba4,ba5,ba6,ba7,ba8
,bb1,bb2,bb3,bb4,bb5,bb6,bb7,bb8,bc1,bc2,bc3,bc4,bc5,bc6,bc7,bc8,bd1,bd2,bd3,bd4,bd5,bd6,bd7,bd8
,be1,be2,be3,be4,be5,be6,be7,be8,bf1,bf2,bf3,bf4,bf5,bf6,bf7,bf8,bg1,bg2,bg3,bg4,bg5,bg6,bg7,bg8
,bh1,bh2,bh3,bh4,bh5,bh6,bh7,bh8,ca1,ca2,ca3,ca4,ca5,ca6,ca7,ca8,cb1,cb2,cb3,cb4,cb5,cb6,cb7,cb8
,cc1,cc2,cc3,cc4,cc5,cc6,cc7,cc8,cd1,cd2,cd3,cd4,cd5,cd6,cd7,cd8,ce1,ce2,ce3,ce4,ce5,ce6,ce7,ce8
,cf1,cf2,cf3,cf4,cf5,cf6,cf7,cf8,cg1,cg2,cg3,cg4,cg5,cg6,cg7,cg8,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8
    FROM #b
GO 10
SELECT 'Table #a cnt',COUNT(*) cnt FROM #a;
SELECT 'Table #b cnt',COUNT(*) cnt FROM #b;
GO
PRINT 'Start: '+CAST(CAST(GETDATE() as time) as varchar)
SELECT 'Table #a OR #b cnt',COUNT(*)
    FROM #a A JOIN #b B
        ON  A.aa1=B.aa1 OR 
            A.aa2=B.aa2 OR 
            A.aa3=B.aa3 OR 
            A.aa4=B.aa4 OR 
            A.aa5=B.aa5 OR 
            A.aa6=B.aa6 OR 
            A.aa7=B.aa7 OR 
            A.aa8=B.aa8 OR 
            A.ab1=B.ab1 OR 
            A.ab2=B.ab2 OR 
            A.ab3=B.ab3 OR 
            A.ab4=B.ab4 OR 
            A.ab5=B.ab5 OR 
            A.ab6=B.ab6 OR 
            A.ab7=B.ab7 OR 
            A.ab8=B.ab8 OR 
            A.ac1=B.ac1 OR 
            A.ac2=B.ac2 OR 
            A.ac3=B.ac3 OR 
            A.ac4=B.ac4 OR 
            A.ac5=B.ac5 OR 
            A.ac6=B.ac6 OR 
            A.ac7=B.ac7 OR 
            A.ac8=B.ac8 OR 
            A.ad1=B.ad1 OR 
            A.ad2=B.ad2 OR 
            A.ad3=B.ad3 OR 
            A.ad4=B.ad4 OR 
            A.ad5=B.ad5 OR 
            A.ad6=B.ad6 OR 
            A.ad7=B.ad7 OR 
            A.ad8=B.ad8 OR 
            A.ae1=B.ae1 OR 
            A.ae2=B.ae2 OR 
            A.ae3=B.ae3 OR 
            A.ae4=B.ae4 OR 
            A.ae5=B.ae5 OR 
            A.ae6=B.ae6 OR 
            A.ae7=B.ae7 OR 
            A.ae8=B.ae8 OR 
            A.af1=B.af1 OR 
            A.af2=B.af2 OR 
            A.af3=B.af3 OR 
            A.af4=B.af4 OR 
            A.af5=B.af5 OR 
            A.af6=B.af6 OR 
            A.af7=B.af7 OR 
            A.af8=B.af8 OR 
            A.ag1=B.ag1 OR 
            A.ag2=B.ag2 OR 
            A.ag3=B.ag3 OR 
            A.ag4=B.ag4 OR 
            A.ag5=B.ag5 OR 
            A.ag6=B.ag6 OR 
            A.ag7=B.ag7 OR 
            A.ag8=B.ag8 OR 
            A.ah1=B.ah1 OR 
            A.ah2=B.ah2 OR 
            A.ah3=B.ah3 OR 
            A.ah4=B.ah4 OR 
            A.ah5=B.ah5 OR 
            A.ah6=B.ah6 OR 
            A.ah7=B.ah7 OR 
            A.ah8=B.ah8  
            
SELECT 'Table #a AND #b cnt',COUNT(*)
    FROM #a A JOIN #b B
        ON  A.aa1=B.aa1 AND 
            A.aa2=B.aa2 AND 
            A.aa3=B.aa3 AND 
            A.aa4=B.aa4 AND 
            A.aa5=B.aa5 AND 
            A.aa6=B.aa6 AND 
            A.aa7=B.aa7 AND 
            A.aa8=B.aa8 AND 
            A.ab1=B.ab1 AND 
            A.ab2=B.ab2 AND 
            A.ab3=B.ab3 AND 
            A.ab4=B.ab4 AND 
            A.ab5=B.ab5 AND 
            A.ab6=B.ab6 AND 
            A.ab7=B.ab7 AND 
            A.ab8=B.ab8 AND 
            A.ac1=B.ac1 AND 
            A.ac2=B.ac2 AND 
            A.ac3=B.ac3 AND 
            A.ac4=B.ac4 AND 
            A.ac5=B.ac5 AND 
            A.ac6=B.ac6 AND 
            A.ac7=B.ac7 AND 
            A.ac8=B.ac8 AND 
            A.ad1=B.ad1 AND 
            A.ad2=B.ad2 AND 
            A.ad3=B.ad3 AND 
            A.ad4=B.ad4 AND 
            A.ad5=B.ad5 AND 
            A.ad6=B.ad6 AND 
            A.ad7=B.ad7 AND 
            A.ad8=B.ad8 AND 
            A.ae1=B.ae1 AND 
            A.ae2=B.ae2 AND 
            A.ae3=B.ae3 AND 
            A.ae4=B.ae4 AND 
            A.ae5=B.ae5 AND 
            A.ae6=B.ae6 AND 
            A.ae7=B.ae7 AND 
            A.ae8=B.ae8 AND 
            A.af1=B.af1 AND 
            A.af2=B.af2 AND 
            A.af3=B.af3 AND 
            A.af4=B.af4 AND 
            A.af5=B.af5 AND 
            A.af6=B.af6 AND 
            A.af7=B.af7 AND 
            A.af8=B.af8 AND 
            A.ag1=B.ag1 AND 
            A.ag2=B.ag2 AND 
            A.ag3=B.ag3 AND 
            A.ag4=B.ag4 AND 
            A.ag5=B.ag5 AND 
            A.ag6=B.ag6 AND 
            A.ag7=B.ag7 AND 
            A.ag8=B.ag8 AND 
            A.ah1=B.ah1 AND 
            A.ah2=B.ah2 AND 
            A.ah3=B.ah3 AND 
            A.ah4=B.ah4 AND 
            A.ah5=B.ah5 AND 
            A.ah6=B.ah6 AND 
            A.ah7=B.ah7 AND 
            A.ah8=B.ah8  
            
PRINT 'Stop: '+CAST(CAST(GETDATE() as time) as varchar)