/*
RiCo技術學院
https://ricochen-s-school.teachable.com/p/home

SQL Server 2022 - Bit Manipulation

#LEFT_SHIFT、RIGHT_SHIFT、BIT_COUNT、GET_BIT、SET_BIT
#支援Tinyint、Smallint、int、Bigint、binary (n) 和Varbinary (n)
#不支援Linked Server的分散式查詢
#可更有效率計算整數或二進位，透過移動、設定、擷取或計算單一位元
#SQL Server將最左邊視為最大，從右到左進行編號。如:3 等於 00000011
*/

SELECT 32--10_0000
,LEFT_SHIFT(32, 1) --100_0000
,LEFT_SHIFT(32, 2);--1000_0000


SELECT 32--100000
,RIGHT_SHIFT(32, 1) --1_0000
,RIGHT_SHIFT(32, 2);--1000


SELECT BIT_COUNT(32)----10_0000
,BIT_COUNT(31)--1_1111
,BIT_COUNT(cast(1 as smallint))--0001
,BIT_COUNT(cast(2 as smallint))--0010
,BIT_COUNT(cast(-1 as smallint))
,BIT_COUNT(cast(-2 as smallint))

💯RiCo技術學院

 [GlobalSetup]
        public void GlobalSetup()
        {
            _enumStringValues = GetEnumStrings();
        }

        [BenchmarkCategory("NativeToString"), Benchmark(Baseline = true)]
        public string NativeToString()
            => Season.Summer.ToString();

        [BenchmarkCategory("BetterToString"), Benchmark()]
        public string BetterToString()
            => nameof(Season.Summer);

        [BenchmarkCategory("ToStringViaArray"), Benchmark()]
        public string ToStringViaArray()
        {
            return _enumStringValues[(int)Season.Summer];
        }

        private static string[] GetEnumStrings()
        {
            System.Collections.IList list = Enum.GetValues(typeof(Season));

            var result = new string[list.Count];
            for (int i = 0; i < list.Count; i++)
            {
                result[i] = list[i].ToString();
            }

            return result;
        }

結果

/*

RiCo技術學院

SQL Server 2022 — IS [NOT] DISTINCT FROM 新語法

#語法: expression IS [NOT] DISTINCT FROM expression

#比較兩個運算的相等或不相等,但考慮了null(允許欄位或值),也保證true或false結果,

where或join可避免額外處理

#建議習慣並使用該語法進行比較

*/

SELECT SalesPersonID,count(*)

FROM [Sales].[SalesOrderHeader]

Where SalesPersonID<>279 — 不包含null

group by SalesPersonID

SELECT SalesPersonID,count(*)

FROM [Sales].[SalesOrderHeader]

where SalesPersonID<>279 or SalesPersonID is null — 包含null

group by SalesPersonID

SELECT SalesPersonID,count(*)

FROM [Sales].[SalesOrderHeader]

where isnull(SalesPersonID,278)<>279 — 包含null

group by SalesPersonID

select SalesPersonID,count(*)

from [Sales].[SalesOrderHeader]

where SalesPersonID is distinct from 279 — 返回不等於279且含null

group by SalesPersonID

select [TerritoryID],count(*)

from [Sales].[SalesOrderHeader]

where [TerritoryID] is distinct from [ShipMethodID] — 返回不等於ShipMethodID欄位且含null

group by [TerritoryID]

SELECT [CurrencyRateID],count(*)

FROM [Sales].[SalesOrderHeader]

Where [CurrencyRateID] is not distinct from 4

group by [CurrencyRateID]

💯RiCo技術學院

💯RiCo技術學院