Firstly, apologies for the late response, slightly busy period:-D

I am not going to even bother with the code I posted earlier, it doesn't stand a chance; three function calls and Tally table of 32 entries compared to the maximum 8 needed for the byte value. Instead I did two more optimised functions, one scalar and the other iTVF.

This quick test code compares the system function to the two alternatives by the number of iterations, somewhat interesting results.

😎

Test code

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @SAMPLE_SIZE INT = 256000;

DECLARE @INT_BUCKET INT = 0;

DECLARE @BYTE_BUCKET BINARY(1) = 0x00;

/* First alternative function */

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME = 'FN_COUNT_1_RIGHT_OF_POS'

AND ROUTINE_SCHEMA = 'dbo')

DROP FUNCTION dbo.FN_COUNT_1_RIGHT_OF_POS;

/* Create function #1 */

DECLARE @SQL_STR_1 NVARCHAR(MAX) = N'

CREATE FUNCTION dbo.FN_COUNT_1_RIGHT_OF_POS

(

@byte BINARY(1)

,@loc INT

)

RETURNS INT

AS

BEGIN

RETURN (

SELECT

SUM(CASE WHEN (@byte & N) <> 0 THEN 1 ELSE 0 END)

FROM ( VALUES (1,1),(2,2),(3,4),(4,8),(5,16),(6,32),(7,64),(8,128)) AS X(P,N)

WHERE X.P <= @loc)

END'

EXEC (@SQL_STR_1);

/*

CODE TO VERIFY THE FUNCTIONALITY

SELECT DISTINCT

sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,8)

,dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,8)

FROM dbo.BYTE_TESTSET BT

*/

/* Second alternative function */

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME = 'ITVFN_COUNT_1_RIGHT_OF_POS'

AND ROUTINE_SCHEMA = 'dbo')

DROP FUNCTION dbo.ITVFN_COUNT_1_RIGHT_OF_POS;

/* Create function #1 */

DECLARE @SQL_STR_2 NVARCHAR(MAX) = N'

CREATE FUNCTION dbo.ITVFN_COUNT_1_RIGHT_OF_POS

(

@byte BINARY(1)

,@loc INT

)

RETURNS TABLE

AS

RETURN (

SELECT

SUM(CASE WHEN (@byte & N) <> 0 THEN 1 ELSE 0 END) AS ONE_COUNT

FROM ( VALUES (1,1),(2,2),(3,4),(4,8),(5,16),(6,32),(7,64),(8,128)) AS X(P,N)

WHERE X.P <= @loc)

'

EXEC (@SQL_STR_2);

DECLARE @timer TABLE

(

TIMER_ID SMALLINT IDENTITY(1,1) NOT NULL

,TIMER_STAMP DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME())

,TIMER_TEXT VARCHAR(128) NOT NULL

);

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES I

WHERE TABLE_NAME = N'BYTE_TESTSET'

AND TABLE_SCHEMA = N'dbo')

DROP TABLE dbo.BYTE_TESTSET;

CREATE TABLE dbo.BYTE_TESTSET (VAL BINARY(1) NOT NULL);

INSERT INTO @timer(TIMER_TEXT) VALUES('Create test set of ' + CAST(@SAMPLE_SIZE AS VARCHAR(12)) + ' entries')

INSERT INTO dbo.BYTE_TESTSET(VAL)

SELECT TOP (@SAMPLE_SIZE)

CONVERT(BINARY(1),(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 256) -1,0)

FROM sys.all_objects X1, sys.all_objects X2

INSERT INTO @timer(TIMER_TEXT) VALUES('Create test set of ' + CAST(@SAMPLE_SIZE AS VARCHAR(12)) + ' entries')

/* DRY RUN 1 */

INSERT INTO @timer(TIMER_TEXT) VALUES('Run full sample select into a bucket, #1')

SELECT

@BYTE_BUCKET = BT.VAL

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('Run full sample select into a bucket, #1')

/* DRY RUN 2 */

INSERT INTO @timer(TIMER_TEXT) VALUES('Run full sample select into a bucket, #2')

SELECT

@BYTE_BUCKET = BT.VAL

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('Run full sample select into a bucket, #2')

/* DRY RUN 3 */

INSERT INTO @timer(TIMER_TEXT) VALUES('Run full sample select into a bucket, #3')

SELECT

@BYTE_BUCKET = BT.VAL

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('Run full sample select into a bucket, #3')

/* 8 BITS */

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 8 BITS')

SELECT

@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,8)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 8 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 8 BITS')

SELECT

@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,8)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 8 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 8 BITS')

SELECT

@BYTE_BUCKET = X.ONE_COUNT

FROM dbo.BYTE_TESTSET BT

CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,8) AS X

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 8 BITS')

/* 7 BITS */

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 7 BITS')

SELECT

@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,7)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 7 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 7 BITS')

SELECT

@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,7)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 7 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 7 BITS')

SELECT

@BYTE_BUCKET = X.ONE_COUNT

FROM dbo.BYTE_TESTSET BT

CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,7) AS X

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 7 BITS')

/* 6 BITS */

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 6 BITS')

SELECT

@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,6)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 6 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 6 BITS')

SELECT

@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,6)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 6 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 6 BITS')

SELECT

@BYTE_BUCKET = X.ONE_COUNT

FROM dbo.BYTE_TESTSET BT

CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,6) AS X

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 6 BITS')

/* 5 BITS */

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 5 BITS')

SELECT

@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,5)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 5 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 5 BITS')

SELECT

@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,5)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 5 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 5 BITS')

SELECT

@BYTE_BUCKET = X.ONE_COUNT

FROM dbo.BYTE_TESTSET BT

CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,5) AS X

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 5 BITS')

/* 4 BITS */

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 4 BITS')

SELECT

@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,4)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 4 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 4 BITS')

SELECT

@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,4)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 4 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 4 BITS')

SELECT

@BYTE_BUCKET = X.ONE_COUNT

FROM dbo.BYTE_TESTSET BT

CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,4) AS X

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 4 BITS')

/* 3 BITS */

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 3 BITS')

SELECT

@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,3)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 3 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 3 BITS')

SELECT

@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,3)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 3 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 3 BITS')

SELECT

@BYTE_BUCKET = X.ONE_COUNT

FROM dbo.BYTE_TESTSET BT

CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,3) AS X

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 3 BITS')

/* 2 BITS */

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 2 BITS')

SELECT

@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,2)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 2 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 2 BITS')

SELECT

@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,2)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 2 BITS')

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 2 BITS')

SELECT

@BYTE_BUCKET = X.ONE_COUNT

FROM dbo.BYTE_TESTSET BT

CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,2) AS X

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 2 BITS')

/* 1 BIT */

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 1 BIT')

SELECT

@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,1)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 1 BIT')

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 1 BIT')

SELECT

@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,1)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 1 BIT')

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 1 BITS')

SELECT

@BYTE_BUCKET = X.ONE_COUNT

FROM dbo.BYTE_TESTSET BT

CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,1) AS X

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 1 BITS')

/* 0 BIT */

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 0 BIT')

SELECT

@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,0)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #0 0 BIT')

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 0 BIT')

SELECT

@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,0)

FROM dbo.BYTE_TESTSET BT

INSERT INTO @timer(TIMER_TEXT) VALUES('SCALAR Function #1 0 BIT')

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 0 BITS')

SELECT

@BYTE_BUCKET = X.ONE_COUNT

FROM dbo.BYTE_TESTSET BT

CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,0) AS X

INSERT INTO @timer(TIMER_TEXT) VALUES('iTVF Function #1 0 BITS')

SELECT

X.TIMER_STAMP

,X.EXEC_TIME

,CAST(X.EXEC_TIME AS FLOAT) / @SAMPLE_SIZE AS T_ROW

,X.TIMER_TEXT

FROM

(

SELECT

T1.TIMER_ID

,ROW_NUMBER() OVER (PARTITION BY T1.TIMER_TEXT ORDER BY T1.TIMER_ID DESC) AS TT_RID

,T1.TIMER_STAMP

,DATEDIFF(MICROSECOND,ISNULL(T2.TIMER_STAMP,T1.TIMER_STAMP),T1.TIMER_STAMP) AS EXEC_TIME

,T1.TIMER_TEXT

FROM @timer T1

LEFT OUTER JOIN @timer T2

ON T1.TIMER_ID = T2.TIMER_ID + 1

) AS X

WHERE X.TT_RID = 1

ORDER BY X.TIMER_ID;

/* 2012 AND LATER */

/*

SELECT

X.TIMER_STAMP

,X.EXEC_TIME

,CAST(X.EXEC_TIME AS FLOAT) / @SAMPLE_SIZE AS T_ROW

,X.TIMER_TEXT

FROM

(

SELECT

tt.TIMER_ID

,ROW_NUMBER() OVER (PARTITION BY TT.TIMER_TEXT ORDER BY TT.TIMER_ID DESC) AS TT_RID

,TT.TIMER_STAMP

,DATEDIFF(MICROSECOND,LAG(TT.TIMER_STAMP,1,TT.TIMER_STAMP) OVER (ORDER BY TT.TIMER_ID),TT.TIMER_STAMP) AS EXEC_TIME

,TT.TIMER_TEXT

FROM @timer TT

) AS X

WHERE X.TT_RID = 1

ORDER BY X.TIMER_ID;'

END

*/

Results

TIMER_STAMP EXEC_TIME T_ROW TIMER_TEXT

--------------------------- ----------- ---------------------- -------------------------------------------

2014-07-12 13:19:56.9834977 432024 1.68759375 Create test set of 256000 entries

2014-07-12 13:19:57.0154996 32002 0.1250078125 Run full sample select into a bucket, #1

2014-07-12 13:19:57.0485015 33002 0.1289140625 Run full sample select into a bucket, #2

2014-07-12 13:19:57.0825034 33002 0.1289140625 Run full sample select into a bucket, #3

2014-07-12 13:19:59.6936528 2611149 10.19980078125 SCALAR Function #0 8 BITS

2014-07-12 13:20:01.0507304 1357078 5.3010859375 SCALAR Function #1 8 BITS

2014-07-12 13:20:01.5697601 519030 2.0274609375 iTVF Function #1 8 BITS

2014-07-12 13:20:03.9358954 2366135 9.24271484375 SCALAR Function #0 7 BITS

2014-07-12 13:20:05.2389699 1303074 5.0901328125 SCALAR Function #1 7 BITS

2014-07-12 13:20:05.6929959 454026 1.7735390625 iTVF Function #1 7 BITS

2014-07-12 13:20:07.8311182 2138123 8.35204296875 SCALAR Function #0 6 BITS

2014-07-12 13:20:09.1061911 1275073 4.98075390625 SCALAR Function #1 6 BITS

2014-07-12 13:20:09.5232150 417024 1.629 iTVF Function #1 6 BITS

2014-07-12 13:20:11.4133231 1890108 7.383234375 SCALAR Function #0 5 BITS

2014-07-12 13:20:12.6553941 1242071 4.85183984375 SCALAR Function #1 5 BITS

2014-07-12 13:20:13.0404162 385022 1.5039921875 iTVF Function #1 5 BITS

2014-07-12 13:20:14.7015112 1661095 6.48865234375 SCALAR Function #0 4 BITS

2014-07-12 13:20:15.9315815 1230070 4.8049609375 SCALAR Function #1 4 BITS

2014-07-12 13:20:16.2746011 343020 1.339921875 iTVF Function #1 4 BITS

2014-07-12 13:20:17.7136834 1439082 5.6214140625 SCALAR Function #0 3 BITS

2014-07-12 13:20:18.8937509 1180067 4.60963671875 SCALAR Function #1 3 BITS

2014-07-12 13:20:19.1897679 296017 1.15631640625 iTVF Function #1 3 BITS

2014-07-12 13:20:20.3848362 1195069 4.66823828125 SCALAR Function #0 2 BITS

2014-07-12 13:20:21.5359021 1151066 4.4963515625 SCALAR Function #1 2 BITS

2014-07-12 13:20:21.7819161 246014 0.9609921875 iTVF Function #1 2 BITS

2014-07-12 13:20:22.7419710 960055 3.75021484375 SCALAR Function #0 1 BIT

2014-07-12 13:20:23.8700356 1128064 4.4065 SCALAR Function #1 1 BIT

2014-07-12 13:20:24.0170440 147009 0.57425390625 iTVF Function #1 1 BITS

2014-07-12 13:20:24.6430798 626035 2.44544921875 SCALAR Function #0 0 BIT

2014-07-12 13:20:25.6831393 1040060 4.062734375 SCALAR Function #1 0 BIT

2014-07-12 13:20:25.7181413 35002 0.1367265625 iTVF Function #1 0 BITS