SoFunction
Updated on 2025-04-04

Detailed explanation of common keywords and functions of SQL Server

1. Control flow keywords

The control flow keyword is used to control the execution logic of SQL scripts, including conditional judgments and loops.

1.1 IF and ELSE

  • Used for conditional judgment and execution of different branches.
IF EXISTS (SELECT 1 FROM Users WHERE UserID = 1)
    PRINT 'User exists'
ELSE
    PRINT 'User does not exist'

1.2 IIF

  • ternary operator, similar toCASE WHEN, used for simple conditional judgment.
SELECT IIF(Age >= 18, 'Adult', 'Minor') AS AgeGroup
FROM Users

1.3 CASE

  • For multi-condition branch selection.
SELECT 
    CASE 
        WHEN Age >= 18 THEN 'Adult'
        ELSE 'Minor'
    END AS AgeGroup
FROM Users

1.4 BEGIN and END

  • Used to define a logical code block.
BEGIN
    PRINT 'Start of block'
    SELECT * FROM Users
END

1.5 WHILE

  • Used for loop operation.
DECLARE @Counter INT = 0
WHILE @Counter < 5
BEGIN
    PRINT @Counter
    SET @Counter = @Counter + 1
END

2. Data operation keywords

These keywords are mainly used to add, delete, modify and search data.

2.1 SELECT

  • Used to query data.
SELECT * FROM Users

2.2 INSERT

  • Used to insert data.
INSERT INTO Users (UserID, UserName)
VALUES (1, 'John')

2.3 UPDATE

  • Used to update data.
UPDATE Users SET UserName = 'Jane' WHERE UserID = 1

2.4 DELETE

  • Used to delete data.
DELETE FROM Users WHERE UserID = 1

2.5 MERGE

  • Used to merge data (insert, update, or delete).
MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON  = 
WHEN MATCHED THEN
    UPDATE SET  = 
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name) VALUES (, )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

3. Data processing function

SQL Server provides a wealth of built-in functions for processing data.

3.1 String function

  • RTRIMandLTRIM: Removes spaces to the right or left of the string.
SELECT RTRIM('Hello   ') AS Trimmed
  • STUFF: Replaces the substring at the specified position in the string.
SELECT STUFF('abcdef', 2, 3, '123') AS Result -- Output: a123ef
  • LEN: Returns the length of the string.
SELECT LEN('Hello') AS StringLength

3.2 Mathematical Functions

  • ABS: Returns the absolute value.
  • ROUND: Rounded.
  • POWER: Calculate the power.
SELECT POWER(2, 3) AS Result -- Output: 8

3.3 Date function

  • GETDATE: Returns the current system time.
  • DATEADD: Add the specified time interval to the date.
SELECT DATEADD(DAY, 1, '2024-01-01') AS NextDay
  • DATEDIFF: Calculate the difference between two dates.
SELECT DATEDIFF(DAY, '2024-01-01', '2024-01-10') AS DaysDifference

4. Window function keywords

Window functions are used to perform grouping or sorting related operations in a query.

4.1 OVER

  • Used with the aggregate function to provide additional calculation results for each row.
SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees

4.2 PARTITION BY

  • Used for grouping, cooperationOVERuse.
SELECT Name, Department, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM Employees

5. Temporary table keywords

5.1 TEMPDB

  • System database for storing temporary tables and objects.

5.2 # and ##

  • #: Create a local temporary table.
  • ##: Create a global temporary table.
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50))
INSERT INTO #TempTable VALUES (1, 'John')
SELECT * FROM #TempTable

6. Transaction management keywords

Transactionality used to manage data operations.

6.1 BEGIN TRAN and COMMIT

  • Used to enable and commit transactions.
BEGIN TRAN
UPDATE Users SET Name = 'NewName' WHERE ID = 1
COMMIT

6.2 ROLLBACK

  • Used to roll back transactions.
BEGIN TRAN
UPDATE Users SET Name = 'NewName' WHERE ID = 1
ROLLBACK

7. Security and permission management keywords

7.1 GRANT and REVOKE

  • Used to assign and revoke permissions.
GRANT SELECT ON Users TO UserRole
REVOKE SELECT ON Users FROM UserRole

7.2 DENY

  • Used to explicitly disable permissions.
DENY SELECT ON Users TO UserRole

8. Advanced keywords

8.1 WITH (NOLOCK)

  • Used to reduce the overhead of locks and allow reading of uncommitted data.
SELECT * FROM Users WITH (NOLOCK)

8.2 FOR XML PATH

  • Used to convert the result set to XML format.
SELECT Name FROM Users FOR XML PATH('User')

8.3 CROSS APPLY and OUTER APPLY

  • Used to apply table value functions to each row of data.
SELECT * FROM Users u CROSS APPLY ()

Summarize

SQL Server keywords cover all aspects such as data operation, query optimization, data processing, transaction management, etc. In actual development, flexibly using these keywords and functions can effectively complete various complex data operations.

suggestion

  • Multi-use keyword combination optimization query.
  • NoticeWITH (NOLOCK)and transaction use to avoid inconsistent data problems.
  • Use window functions to simplify complex grouping calculations.

The above is the detailed explanation of the commonly used keywords and functions of SQL Server. For more information about SQL Server keywords and functions, please pay attention to my other related articles!