SQL Server - bigktmbig/MyExperienceAlgorithm GitHub Wiki

============================================================== WITH tăng id

Declare @T_RESULT TABLE ( countNum BIGINT DEFAULT 0

, PRIMARY KEY ( countNum ) ) ;WITH CTE AS ( -- SELECT 1 AS countNum UNION ALL -- SELECT (CTE.countNum + 1) AS countNum FROM CTE WHERE (CTE.countNum < 6) ) -- INSERT INTO @T_RESULT ( countNum ) SELECT CTE.countNum FROM CTE OPTION -- max loop (MAXRECURSION 1000) select * from @T_RESULT ========================================================= Gom nhom so thu tu theo field ROW_NUMBER() OVER (PARTITION BY #SAMPLE.sample_cd ORDER BY #SAMPLE.sample_cd ) as index_no ============================================================================ De quy ;WITH CTE_SAMPLE (sample_cd, sample_money) AS (SELECT DISTINCT sample_detail_2.sample_cd as sample_cd , sample_detail_1.sample_money FROM sample_detail_2 INNER JOIN sample_detail_1 ON( sample_detail_2.sample_no = sample_detail_1.sample_no AND sample_detail_1.flag = 0 ) INNER JOIN tbl_sample ON ( sample_detail_2.sample_cd = tbl_sample.sample_cd ) WHERE FORMAT(sample_detail_2.sample_date, 'yyyy/MM/dd') >= ISNULL(sample_detail_1.sample_date,'1900/01/01') )

INSERT INTO #SAMPLE_1
SELECT	
	CTE_SAMPLE.sample_cd										
,	SUM(CTE_SAMPLE.sample_money)																
FROM														
	CTE_SAMPLE
GROUP BY
	CTE_SAMPLE.sample_cd	
ORDER BY 
	CTE_SAMPLE.sample_cd

=================================================================================Lỗi thường gặp 1/string or binary data would be truncated => Do update/insert 1 field nào đó có data length quá dài so với dữ liệu khai báo

=================================================================================backup table

  • select * into t_pay_total_bk from t_pay_total

================================================================================= DROP TABLE [dbo].[Example] GO SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

SET ANSI_PADDING ON GO

CREATE TABLE [dbo].[Example]( [example_id] [int] NOT NULL, [example_nm] nvarchar NOT NULL, [position_id] [int] NOT NULL, [description] nvarchar NULL, [color] nvarchar NULL, [created_at] datetime2 NULL, [created_by] nvarchar NULL, [updated_at] datetime2 NULL, [updated_by] nvarchar NULL, [deleted_at] datetime2 NULL, [deleted_by] nvarchar NULL, [deleted_flag] [bit] NOT NULL, CONSTRAINT [PK_Example] PRIMARY KEY CLUSTERED ( [example_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO

SET ANSI_PADDING OFF GO

========================== ** Lưu ý: mọi thao tác phải xử lý dưới DB và Client rồi mới đến Controller(giảm tải cho server).

  • Dùng "Group by" thay thế "Distinct"

  • Dùng ">< =" thay thế "between, and"

  • Dùng IsNull để check các giá trị có thể null(join bảng lấy chính xác các record)

  • Update/Delete: 2 bảng có quan hệ với nhau(cha - con)

    1. Update: Tb_cha trước, Tb_con sau
    2. Delete: Tb_con trước, Tb_cha sau

  • Update: tạo View hoặc bảng tạm lưu dữ liệu đầu vào cho bảng con (parent - child), Join bảng parent - child - #tmp:

    1. Delete: những record có dưới bảng con nhưng ko có trong bảng #tmp
    2. Update: những record có trong bảng con và trên bảng #tmp
    3. Insert: những bảng ghi ko có dưới bảng con nhưng lại có trong bảng #tmp

  • 2 cách để sắp xếp và lấy ra danh sách cây thư mục (parent - child):

    1. Insert 2 bảng tạm #TMP_PARENT và #TMP_CHILD(item_id, parent_id)
      1. #TMP_PARENT: gán item_id cho parent_id, set column isParent = 1
      2. #TMP_CHILD: set column isParent = 0
      3. Join 2 bảng lại với nhau và sort, ta insert vào bảng tạm #TMP_DATA
    2. Xử lý trên controller

  • Tránh convert ngầm (implicit) where item.id = '1' trong khi item.id kiểu integer mà so sánh với string.

    1. dùng hàm chuyển đổi như: CAST, CONVERT.

  • Lệnh Delete và Truncate:

    1. Delete xóa theo điều kiện
    2. Truncate xóa toàn bộ record

  • Default value: string-> space(0), int->0, date-> SYSDATETIME().

  • Can not connect to server: services.msc

    1. start : SQL SERVER
    2. restart : SQL Vss Writter =========================================================Đệ Quy

  • CREATE TABLE #ITEM_DATA (
    parent_item_id INT , item_id INT , category_id NVARCHAR(10) ) BEGIN -- Check whether data level 1 exist or not

      IF EXISTS ( SELECT  1
              FROM    Item WITH (NOLOCK)
              WHERE   
                  Item.item_id = @P_item_id
              AND ISNULL(deleted_flag, 0)     =   0
              )
  BEGIN

      WITH Item_CTE(
          parent_item_id
      ,   item_id
      ,   category_id
      )
      AS
      ( 
          -- Item level 1

          SELECT
              ISNULL(Item.parent_item_id,0)   AS  parent_item_id
          ,   ISNULL(Item.item_id,0)          AS  item_id
          ,   Item.category_id                 AS  category_id
          FROM Item WITH (NOLOCK)

          WHERE 
              (   @P_category_id               =   ''
              OR  (   @P_category_id           <>  '' 
                  AND Item.category_id         =   @P_category_id 
                  )
              )

          AND Item.item_id     =   @P_item_id 
          AND ISNULL(Item.deleted_flag, 0)            =   0
          UNION ALL
          -- Item level 2 and below

          SELECT 
              ISNULL(Item.parent_item_id,0)   AS  parent_item_id
          ,   ISNULL(Item.item_id,0)          AS  item_id
          ,   Item.category_id                 AS  category_id
          FROM Item  WITH (NOLOCK)

          INNER JOIN Item_CTE
          ON (    Item.category_id     =   Item_CTE.category_id
              AND Item.item_id =   Item_CTE.parent_item_id)

          WHERE 
              (   @P_category_id               =   ''
              OR  (   @P_category_id           <> '' 
                  AND Item.category_id         =   @P_category_id 
                  )
              )
          AND (   Item.deleted_flag IS NULL
              OR  Item.deleted_flag = 0
              )
      
          )
          INSERT INTO #ITEM_DATA
          SELECT
              Item_CTE.parent_item_id
          ,   Item_CTE.item_id
          ,   Item_CTE.category_id
          FROM Item_CTE WITH (NOLOCK)
          OPTION (MAXRECURSION 3)
      END
 END
 SELECT * FROM #ITEM_DATA