/*
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 技術農場

分享工作上實戰經驗,如SQL Server,NetCore,C#,WEBApi,Kafka,Azure…等,Architect,Software Engineer, Technical Manger,Writer and Speaker