Alternative of OR in WHERE condition

Alternative of OR in WHERE condition:


CREATE PROCEDURE [dbo].[TestSP]
  @IsTest      bit = 0 

AS
-- Alternative of OR in WHERE condition.
BEGIN

  SET NOCOUNT ON;

  IF @IsTest = 1 
  BEGIN
    SET @IsTest = NULL 
  END

  SELECT ID 
  FROM dbo.Test WITH(NOLOCK) 
  WHERE IsTest = ISNULL(@IsTest, IsTest) 

END

 

 

Query data by unix timestamp

Query data by unix timestamp:

CREATE PROCEDURE [dbo].[TestSP]
  @UnixTimestamp  BIGINT

AS
--Query data by unix timestamp.
BEGIN

  SET NOCOUNT ON;

  DECLARE
    @LastUpdateTime    DATETIME,
    @LocalTimestamp    BIGINT = 0

  IF @UnixTimestamp > 0
  BEGIN
    SET @LocalTimestamp = @UnixTimestamp - DATEDIFF(SECOND, CURRENT_TIMESTAMP, GETUTCDATE())
  END

  SET @LastUpdateTime = DATEADD(SECOND, @LocalTimestamp, {d '1970-01-01'})

  SELECT
    ID
  FROM
    dbo.Test WITH(NOLOCK, INDEX=[IX_Time])
  WHERE
    LastUpdateTime >= @LastUpdateTime

END

Reference:

1) How can I convert bigint (UNIX timestamp) to datetime in SQL Server? https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server
2) Convert Datetime to Unix timestamp https://stackoverflow.com/questions/34455408/convert-datetime-to-unix-timestamp

TVP in SQL Server

Table-Valued Parameters (TVP) in SQL Server:

1. Create Type [TVP_Int]:


USE [DB01]
GO

CREATE TYPE [dbo].[TVP_Int] AS TABLE(
    [Val] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
        [Val] ASC
    )WITH (IGNORE_DUP_KEY = OFF)
)
GO

 

SSMS : programmability => Types => User-Defined Table Types;

 
2. Stored Procedure:


CREATE PROCEDURE [dbo].[List_TestInfo] 
  @IDs As TVP_Int Readonly

AS
BEGIN

  SET NOCOUNT ON;

  SELECT 
    ID, Name 
  FROM
    TestInfo WITH(NOLOCK) 
  WHERE 
    ID IN (SELECT Val FROM @IDs)

END

 

3. Test SP:


DECLARE @Tbl AS [TVP_Int];
INSERT INTO @Tbl (Val) VALUES (1001)
INSERT INTO @Tbl (Val) VALUES (1002)
INSERT INTO @Tbl (Val) VALUES (1003)

EXEC [List_TestInfo]
    @IDs = @Tbl 

 

4. (C#) Convert SqlParameter (List to DataTable):


private DataTable ToDataTable<T>(List<T> list)
{
  DataTable t = new DataTable();
  t.Columns.Add("Val", typeof(T));

  foreach (var i in list)
  {
    t.Rows.Add(i);
  }

  return t;
}

 

Reference:

1) SQL 2008 TVP資料匯入之火力展示 https://blog.darkthread.net/blog/import-with-sql-tvp
2) SqlBulkCopy Vs. TVP Vs. XML Parameter Vs. Individual Insert Procs for 20-200 Inserts? https://stackoverflow.com/questions/8618261/sqlbulkcopy-vs-tvp-vs-xml-parameter-vs-individual-insert-procs-for-20-200-ins
3) Table-Valued Parameters https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

 

PIVOT syntax in SQL Server 2005

PIVOT syntax in SQL Server 2005:

Quoted from ‘TIPS-Using PIVOT In SQL 2005’ by Jeffrey https://blog.darkthread.net/blog/tips-using-pivot-in-sql-2005

1. Using GROUP BY:


SELECT
  LogTime
  , StatusCode
  , COUNT(*) AS Cnt  --COUNT(*) GROUP BY.
FROM
  IISLogTable
GROUP BY
  LogTime
  , StatusCode
ORDER BY
  LogTime

Snipaste_2019-03-08_16-20-10

 

2. Using GROUP BY and PIVOT:


SELECT
  *
FROM
(
  SELECT
    LogTime  --PIVOT COL 1 (not RQD).
    , StatusCode  --PIVOT COL N.
    , COUNT(*) AS Cnt  --COUNT(*) GROUP BY.
  FROM
    IISLogTable
  GROUP BY
    LogTime
    , StatusCode
) AS X
PIVOT
(
    SUM(Cnt)  --PIVOT COL Values: SUM(), MAX(), etc.
    FOR
      StatusCode IN ([200],[302],[304],[401],[404],[500])  --PIVOT COL N.
) AS PVT 

Snipaste_2019-03-08_16-21-05