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

 

 

 

Types of SQL Joins

Types of SQL Joins:

Quoted from ‘Joins in SQL Server 2012’ by Deepak Middha https://www.c-sharpcorner.com/UploadFile/63f5c2/joins-in-sql-server

1. INNER JOIN


SELECT *
FROM [emp] e INNER JOIN [emp_add] e1 ON e.empID = e1.empID 

010_inner-join-in-sql

 

2. OUTER JOIN

2.1) LEFT OUTER JOIN


SELECT * 
FROM [emp] e LEFT OUTER JOIN [emp_add] e1 ON e.empID = e1.empID

021_left-outer-join-in-sql

 

2.2) RIGHT OUTER JOIN


SELECT * 
FROM [emp] e RIGHT OUTER JOIN [emp_add] e1 ON e.empID = e1.empID

022_right-outer-join-in-sql

 

2.3) FULL OUTER JOIN


SELECT * 
FROM [emp] e FULL OUTER JOIN [emp_add] e1 ON e.empID = e1.empID

023_full-outer-join-in-sql

 

3. CROSS JOIN


SELECT * 
FROM [emp] e CROSS JOIN [emp_add] e1 

030_cross-join-in-sql

 

4. SELF JOIN


-- To find the employees that are the manager of other employees.
SELECT e.empName, e.mngrName 
FROM [emp_mngr] e INNER JOIN [emp_mngr] e1 ON e.empName = e1.mngrName

Table [emp_mngr]:
040_01_join-in-sql-emp_mngr

Output of SELF JOIN:
040_02_self-join-in-sql

 

 

EXEC against SQL server database

EXEC against SQL server database:

Stored Procedure:


USE [TestDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_Name_1]
  @Param1 INT,
  @Param2 VARCHAR(20),
  @RtnCode INT OUTPUT,
  @RtnMsg VARCHAR(100) OUTPUT
AS
BEGIN

  SET NOCOUNT ON;

  SELECT @RtnCode = 0, @RtnMsg = ''

  IF (@@ERROR <> 0)
    BEGIN
      SELECT @RtnCode = @@ERROR, @RtnMsg = '...'
    END

END

 

Utility:


using System.Data;
using System.Data.SqlClient;

namespace Test.Util
{
  public class Database
  {
    private int _timeout;
    private string _connStr;
    private SqlConnection _sqlConn;
    private SqlDataAdapter _sqlDataAdapter;

    public SqlConnection Connection
    {
      get
      {
        return _sqlConn;
      }
    }

    public Database(string connStr, int timeout = 5)
    {
      _timeout = timeout;
      _connStr = string.Format("{0}; Connection Timeout={1};", connStr, timeout);
      _sqlConn = new SqlConnection(_connStr);
    }

    public DataTable ExecStoredProcedure(string spName, SqlParameter[] paramArray)
    {
      DataTable result;

      try
      {
        Open();

        //#region If input SQL string
        //_sqlDataAdapter = new SqlDataAdapter(sqlStr, _sqlConn);
        //new SqlCommandBuilder(_sqlDataAdapter);
        //#endregion

        #region If input params
        SqlCommand cmd = new SqlCommand();
        cmd.CommandTimeout = (_timeout > 0) ? _timeout : cmd.CommandTimeout;
        cmd.Connection = Connection;
        cmd.CommandText = spName;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(paramArray);
        _sqlDataAdapter = new SqlDataAdapter(cmd);
        #endregion

        DataTable table = new DataTable();
        _sqlDataAdapter.Fill(table);
        result = table;
      }
      catch (SqlException ex)
      {
        throw ex;
      }
      finally
      {
        Close();
      }

      return result;
    }

    private void Open()
    {
      try
      {
        if (_sqlConn.State != ConnectionState.Open)
        {
          _sqlConn.ConnectionString = _connStr;
          _sqlConn.Open();
        }
      }
      catch (SqlException ex)
      {
        throw ex;
      }
    }

    private void Close()
    {
      try
      {
        if (_sqlConn.State == ConnectionState.Open)
        {
          _sqlConn.Close();
        }
      }
      catch (SqlException ex)
      {
        throw ex;
      }
    }
  }
}

 

Test code:


public DataTable Test(int param1, string param2, out int rtnCode, out string rtnMsg)
{
  rtnCode = 0;
  rtnMsg = string.Empty;
  DataTable result = null;

  SqlParameter paramRtnCode = new SqlParameter("@RtnCode", SqlDbType.Int) { Direction = ParameterDirection.Output };
  SqlParameter paramRtnMsg = new SqlParameter("@RtnMsg", SqlDbType.VarChar, 100) { Direction = ParameterDirection.Output };

  SqlParameter[] paramArray = new SqlParameter[]
  {
    new SqlParameter("@Param1", param1),
    new SqlParameter("@Param2", param2),
    paramRtnCode,
    paramRtnMsg
  };

  Database db = new Database(decryptedConnStr);

  try
  {
    result = db.ExecStoredProcedure("SP_Name_1", paramArray);
  }
  catch (Exception ex)
  {
    _logger.Error(ex);
  }

  rtnCode = (int)paramRtnCode.Value;
  rtnMsg = paramRtnMsg.Value as string;

  return result;
}

 

Reference:

https://forums.asp.net/t/1798529.aspx?DataAdapter+vs+SqlCommand+or+DataAdapter+SqlCommand+vs+Impact+on+Performance

SQL statements notes

SQL statements notes:

1. Avoid Column functions;

2. Follow SARG (Serarch Arguments) Statement in WHERE statement, use: <, >, =, <=, >=, BETWEEN, LIKE ‘XXX%’;
avoid: <>, !=, !<, !>, NOT, NOT IN, NOT EXISTS, NOT LIKE…;

3. Avoid OR operator;

4. Fill conditions in ON statements when JOIN tables;

5. Avoid Subquery;

6. Avoid CASE WHEN;

7. Avoid SELECT *;

8. SET NOCOUNT ON in stored procedures;

9. 查詢、更新頻率不同的table, 分開存於不同磁碟組的檔案群組;

10. 現有資料、歷史資料分割儲存。