T-SQL

SQL Server notes

Cheatsheet

  • Check duplicate records:

    SELECT category_id, group_id, category_level, cashflow_type, COUNT(*)
FROM ct_cashflow_category
GROUP BY category_id, group_id, category_level, cashflow_type
ORDER BY COUNT(*) DESC

    In this case, category_id, group_id, category_level, cashflow_type is the natural ID of the table, which could be changed to any combinations of columns to your liking.

  • Date/Time condition in WHERE clause

    • Use DATEPART function to extract the corresponding Date section

      SELECT *
FROM events_log
WHERE DATEPART(year, progress_date) = 2019
AND DATEPART(month, progress_date) = 1
AND DATEPART(day, progress_date) = 3

    • Use string for comparison

      SELECT *
FROM hist_tlimit_progress_f_events
WHERE event_date > '2018-12-20 12:00:00'
ORDER BY event_date

    • Extracting Date component from a DateTime value

      CONVERT(date, datetime_field_you_want_to_convert)

sqlcmd utility

  • Command-line options

    • -U <login id>
    • -P <password>
    • -S <ComputerName>\<InstanceName>
    • -i <script file>
    • -o <output file>

  • GO

    • You can write as many lines of code as you want, but they will not be executed until the GO command is specified.
    • You can think of the GO command very much the same as the Execute button on the SQL Editor toolbar.

  • Reference

Statements

  • BEGIN...END
    • Equivalent to braces that enclose code block in other languages
  • Update table using values from another table
UPDATE
    Sales_Import
SET
    Sales_Import.AccountNumber = RAN.AccountNumber
FROM
    Sales_Import SI
INNER JOIN
    RetrieveAccountNumber RAN
ON
    SI.LeadID = RAN.LeadID;
  • Inserting a row if it doesn't exist, or updating a row if it exists
MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN
        UPDATE SET Name = source.Name
    WHEN NOT MATCHED THEN
        INSERT (UnitMeasureCode, Name) VALUES (source.UnitMeasureCode, source.Name)
    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
  • OUTPUT Clause
    • Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.

Table Value Constructor

USE AdventureWorks2012;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'),
       (N'Y3', N'Cubic Yards', '20080923');
GO
  • Specifying multiple values as a derived table in a FROM clause
SELECT a, b
FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);

Subquery

Scenarios

First level table and Second level table, listing all first level table given criteria, also listing some statistics of corrsponding second level table
SELECT result.abs_hist_seq_number,
        result.hist_create_date_time,
        (CASE
            WHEN result.application_type_ind = 'E' AND result.others_value <= 0 AND
                    result.efonly_value > 0
                THEN 'ONLY_EF'
            WHEN result.application_type_ind = 'E' AND result.others_value > 0
                THEN 'NOT_ONLY_EF'
            WHEN result.application_type_ind = 'N'
                THEN 'NEW_CLIENT'
            WHEN result.application_type_ind = 'R' AND
                    result.review_category IN ('FULLREVIEW', 'AUTOREVIEW', 'SHORTREVIEW', 'SMALLEXP')
                THEN 'EXISTING_CLIENT'
            END) AS Label,
        result.seasonal_facility_end_date
FROM (SELECT hc.hist_client_id,
                hc.abs_hist_seq_number,
                hc.hist_create_date_time,
                hcv.application_type_ind,
                hcv.review_category,
                ISNULL((
                        SELECT SUM(hce.additional_lending)
                        FROM hist_client_exposure hce
                        WHERE hce.abs_client_number = hc.abs_client_number
                            AND hce.abs_hist_seq_number = hc.abs_hist_seq_number
                            AND hce.exposure_type_code = 'EQUIPFIN'
                        GROUP BY hce.abs_client_number, hce.abs_hist_seq_number
                    ), 0)                                  AS efonly_value,
                ISNULL((
                        SELECT SUM(hce.additional_lending)
                        FROM hist_client_exposure hce
                        WHERE hce.abs_client_number = hc.abs_client_number
                            AND hce.abs_hist_seq_number = hc.abs_hist_seq_number
                            AND hce.exposure_type_code != 'EQUIPFIN'
                        GROUP BY hce.abs_client_number, hce.abs_hist_seq_number
                    ), 0)                                  AS others_value,
                CONVERT(date, frm.seasonal_facility_end_date) AS seasonal_facility_end_date
        FROM hist_client hc
                JOIN hist_credit_review hcv
                    ON hcv.abs_client_number = hc.abs_client_number
                        AND hcv.abs_hist_seq_number = hc.abs_hist_seq_number
                JOIN hist_facility_require_master AS frm
                    ON frm.abs_client_number = hc.abs_client_number
                        AND frm.abs_hist_seq_number = hc.abs_hist_seq_number
        WHERE hc.abs_client_number = ?
        AND hc.wip_status = 800
        AND (
                hcv.application_type_ind = 'N'
                OR
                (hcv.application_type_ind = 'R' AND
                hcv.review_category IN ('FULLREVIEW', 'AUTOREVIEW', 'SHORTREVIEW', 'SMALLEXP'))
                OR
                hcv.application_type_ind = 'E'
            )
        ) result
ORDER BY result.hist_create_date_time DESC
abs_hist_seq_numberhist_create_date_timeLabelseasonal_facility_end_date
302018-07-11 09:16:53.277NOT_ONLY_EF2019-04-01
282017-05-02 16:32:21.157EXISTING_CLIENT2018-03-01
272016-07-18 13:29:40.357EXISTING_CLIENT2017-07-01
262015-07-13 11:58:27.190EXISTING_CLIENT2016-07-01
242014-03-19 11:20:10.100NOT_ONLY_EF2015-02-01

Constraint

  • UNIQUE
    • UNIQUE constraints allow for the value NULL
    • As with any value participating in a UNIQUE constraint, only one null value is allowed per column.
    • A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.

Function

FunctionParameterResult Type
EXISTS (opens in a new tab)subqueryBoolean (Returns TRUE if a subquery contains any rows. SELECT NULL will still return TRUE.)
OBJECT_ID (opens in a new tab)Object nameObject ID

CTE

  • Alternative to derived tables (nested queries)
WITH AffectedClients (
    abs_client_number,
    proposed_viability_code,
    credit_risk_rating
) AS (SELECT wcs.abs_client_number,
             wcs.proposed_viability_code,
             wcs.credit_risk_rating
             FROM work_client_summary wcs
             WHERE wcs.abs_client_number IN (SELECT DISTINCT abs_client_number FROM client
                                             WHERE rfs_client_group_number IN (
                                                                   2156495,
                                                                   1933498,
                                                                   2192102,
                                                                   2181527,
                                                                   2023018
                                                                   )))
SELECT *
FROM AffectedClients

Table variable

  • Table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they're defined.
  • Performance not suitable for tables with more than 100 rows
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);

Reference

JacksonIntelliJ IDEA