Personal cheat sheet for querying relational database in SQL SERVER
SELECT select_list
[ FROM table_source ]
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
-- AND
SELECT column_name FROM table_name
WHERE condition1 AND condition2
-- OR
SELECT column_name FROM table_name
WHERE condition1 OR condition2
-- EXISTS
SELECT column_name FROM table_name
WHERE EXISTS (SELECT column_name FROM table_name)
-- ANY
SELECT column_name FROM table_name
WHERE column = ANY (SELECT column_name FROM table_name)
-- ALL
SELECT column_name FROM table_name
WHERE column = ALL (SELECT column_name FROM table_name)
-- WHERE NOT
SELECT column_name FROM table_name
WHERE NOT condition
CASE
WHEN condition THEN 'true'
ELSE 'false'
END
INSERT INTO table_name (column, column)
VALUES (value, value)
INSERT INTO table_name
VALUES (value, value)
UPDATE table_name
SET column = value,
column = value,
column = value
WHERE condition;
DELETE FROM table_name WHERE condition;
TRUNCATE TABLE table_name;
SELECT TOP [ number | percent ] column_name
FROM table_name
WHERE condition;
Returns the smallest/biggest value in selected column
SELECT [ MIN | MAX ] (column_name)
FROM table_name
WHERE condition;
SELECT [ COUNT | AVG | SUM] (column_name)
FROM table_name
WHERE condition;
SELECT column
FROM table_name
WHERE column LIKE pattern;
WHERE column LIKE 'a%' --Finds any values that start with "a"
WHERE column LIKE '%a' --Finds any values that end with "a"
WHERE column LIKE '%or%' --Finds any values that have "or" in any position
WHERE column LIKE '_r%' --Finds any values that have "r" in the second position
WHERE column LIKE 'a_%_%' --Finds any values that start with "a" and are at least 3 characters in length
WHERE column LIKE 'a%o' --Finds any values that start with "a" and ends with "o"
The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position
-- Syntax
STUFF (character_expression, start, length, new_string )
-- Example: deletes the second digit of the product ID in the Productstable and replaces it with the characters '000'
SELECT STUFF([Product_ID], 2,1, '000')
FROM Products
-- OUTPUT: 20 becomes 2000
-- Syntax
REPLACE(string, old_string, new_string)
-- Replaces 'A' with 'C'
SELECT REPLACE('AB AB', 'A','C')
SELECT COALESCE(NULL, NULL, NULL, 'JigJun', NULL, 1);
-- OUTPUT: 'JigJun'
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
SELECT column_name(s)
FROM table A
JOIN table B
ON A.column_name = B.column_name;
CREATE TABLE table_name (
column int IDENTITY(1,1) PRIMARY KEY, -- primary key and "IDENTITY(1,1)" for auto increment
column data_type NOT NULL, -- normal column
column data_type NOT NULL FOREIGN KEY REFERENCES table_name(column) -- foreign key
);
-- ADD Column
ALTER TABLE table_name
ADD column_name datatype;
-- DROP COLUMN
ALTER TABLE table_name
DROP COLUMN column_name datatype;
-- ALTER COLUMN
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
-- The CHECK constraint is used to limit the value range that can be placed in a column
CREATE TABLE table_name (
ID int NOT NULL,
[Percentage] DECIMAL(5,4),
CHECK ([Percentage] <= 1.0000)
);
-- DEFAULT constraint is used to provide a default value for a column
CREATE TABLE table_name (
ID int NOT NULL,
VARCHAR_column varchar(255) DEFAULT 'Text',
INT_column INT DEFAULT 1
);
IF (@variable = 1)
BEGIN
--insert code here
END
ELSE
BEGIN
--insert code here
END
DECLARE @Temp TABLE
(
column INT,
column2 VARCHAR(10)
);
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
/******************************************************************************
** Change History
**
** CID Date Author Description
** ----- ---------- ---------- -----------------------------------------------
** CH001 08/10/2019 N.Sun Initial Version
*******************************************************************************/
-- exec [SP_Template_Get] 1
CREATE PROCEDURE [dbo].[SP_Template_Get]
@Parameter INT,
@PageNumber INT = 1,
@PageSize INT = 20,
AS
BEGIN
DECLARE @Result TABLE
(
column INT,
column2 VARCHAR(10)
);
INSERT INTO @Result
(
column,
column2,
)
SELECT column,
@column2
FROM dbo.table_name A
INNER JOIN dbo.table_name B
ON A.column = B.column
WHERE A.column = @Parameter
SELECT
column,
column2
FROM @Result
ORDER BY
CASE WHEN @SortingMode = 'DateAsc' THEN SampleDateColumn END ASC,
CASE WHEN @SortingMode = 'DateDesc' THEN SampleDateColumn END DESC
OFFSET ((@PageNumber - 1) * COALESCE(@PageSize, @PageCount)) ROWS
FETCH NEXT COALESCE(@PageSize, @PageCount) ROWS ONLY
END;
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
/******************************************************************************
** Change History
**
** CID Date Author Description
** ----- ---------- ---------- -----------------------------------------------
** CH001 08/10/2019 N.Sun Initial Version
*******************************************************************************/
-- exec [SP_Template_Set] 1
CREATE PROCEDURE [dbo].[SP_Template_Set]
@Parameter INT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
-- Do something here
COMMIT TRANSACTION
SELECT 1 As Success
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT 0 As Success
END CATCH
END;
GO
-- Specifies a temporary named result set, known as a common table expression (CTE).
-- This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
-- Defining the column list is optional
WITH CTE_Name (column1, column2)
AS
-- Define the CTE query.
(
SELECT column1, column2
FROM Table1
WHERE column1 IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT *
FROM CTE_Name
GROUP BY column1, column2
ORDER BY column1, column2;
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Find_Text_In_SP]
@StringToSearch VARCHAR(100),
@StringToSearch2 VARCHAR(100) = '',
@StringToSearch3 VARCHAR(100) = '',
@Name VARCHAR(100) = ''
AS
SET @StringToSearch = '%' +@StringToSearch + '%'
SET @StringToSearch2 = '%' +@StringToSearch2 + '%'
SET @StringToSearch3 = '%' +@StringToSearch3 + '%'
SET @Name = '%' +@Name + '%'
SELECT ROUTINE_NAME, LEN(OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME))) AS SP_Length
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) LIKE @stringtosearch
AND OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) LIKE @StringToSearch2
AND OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) LIKE @StringToSearch3
AND (ROUTINE_TYPE='PROCEDURE' OR ROUTINE_TYPE='FUNCTION')
AND ROUTINE_NAME LIKE @Name
ORDER BY routine_name
GO
-- Skip first 10 rows from the sorted result set and return the remaining rows.
SELECT column1, column2 FROM table_name ORDER BY column1 OFFSET 10 ROWS;
-- Skip first 10 rows from the sorted resultset and return next 5 rows.
SELECT column1, column2 FROM table_name ORDER BY column1 OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
exec sp_rename '[schema.old_table_name]', 'new_table_name'
exec sp_rename 'table_name.[oldColumName]' , 'newColumName', 'COLUMN'
-- returns the last IDENTITY value inserted into an IDENTITY column in the same scope
-- returns the last identity value generated for any table in the current session and the current scope
-- A scope is a module; a Stored Procedure, trigger, function, or batch
SELECT SCOPE_IDENTITY()
SELECT
OBJECT_NAME(o.parent_object_id)
FROM
sys.objects o
WHERE
o.name = 'MyConstraintName' AND o.parent_object_id <> 0
BEGIN TRY
BEGIN TRANSACTION
-- Do something here
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (
@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
ROLLBACK TRANSACTION
END CATCH
-- using '=' operator
WHERE Column = IIF(@Variable IS NULL ,@Variable, Column)
-- using 'LIKE, IN, etc.'
WHERE (@Variable IS NULL OR Column LIKE '%' + @Variable + '%' )
DECLARE @String = '1, 4, 3'
DECLARE @Tbl TABLE(ID INT);
INSERT INTO @Tbl
(
ID
)
(SELECT value
FROM STRING_SPLIT(@String, ',')
WHERE RTRIM(value) <> '');
UPDATE Table1
SET Table1.Column = B.Column
FROM Table1 A
INNER JOIN Table2 B
ON A.ID = B.ID
DELETE A
FROM Table1 A
INNER JOIN Table2 B
ON B.Id = A.Id
WHERE A.Column = 1 AND B.Column = 2
SET IDENTITY_INSERT YourTable ON
-- UPDATE/INSERT STATEMENT HERE
SET IDENTITY_INSERT YourTable OFF
SELECT
OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE
OBJECT_NAME (f.referenced_object_id) = 'Table_Name'
-- JSON Data sample:
-- {
-- "label": "test ",
-- "value": 1
-- },
-- {
-- "label": "test2 ",
-- "value": 2
-- }
DECLARE @tbl TABLE (id INT, label VARCHAR(500));
DECLARE @json VARCHAR(max);
SELECT @json = BulkColumn
FROM OPENROWSET (BULK 'C:\jsonFile.json', SINGLE_CLOB) as j
INSERT INTO @tbl (id, label)
SELECT [value], label
FROM OPENJSON(@json)
WITH ([value] int,
label nvarchar(max))
SELECT * FROM @tbl
ALTER TABLE [Table1]
ADD CONSTRAINT FK_Table2_Id FOREIGN KEY (Table1_Id)
REFERENCES Table2(Table2_Id);
SELECT [Name], [Definition], [Type_desc]
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE [Type_desc] like '%function%'
UPDATE dbo.[Table]
SET Value = REPLACE(Value, '123\', '')
WHERE ID <=4
---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random
DECLARE @FromDate DATE = '2019-09-01';
DECLARE @ToDate DATE = '2019-12-31';
SELECT DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, @FromDate, @ToDate)), @FromDate)
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Table')
BEGIN
-- exists
END
SELECT LEFT(CAST(RAND()*1000000000+999999 AS INT),6) AS OTP
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%%'
--convert to date to ignore time
SELECT * FROM Table T
WHERE CONVERT(DATE,T.DateColumn) BETWEEN COALESCE(CONVERT(DATE,@DateFrom), CONVERT(DATE,T.DateColumn)) AND COALESCE(CONVERT(DATE,@DateTo), CONVERT(DATE,T.DateColumn))
--Output: 21/03/2018
SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date
--Output: 21/03/2018, 11:36:14
SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date
--Output: Wednesday, March, 2018
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as date
--Output: Mar 21 2018
SELECT FORMAT (getdate(), 'MMM dd yyyy') as date
--Output: 03.21.18
SELECT FORMAT (getdate(), 'MM.dd.yy') as date
--Output: 03-21-18
SELECT FORMAT (getdate(), 'MM-dd-yy') as date
--Output: 11:36:14 AM
SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date
--Output: 03/21/2018
SELECT FORMAT (getdate(), 'd','us') as date
create trigger t1 on table1
after insert
as
begin
insert into Audit
(Column)
select 'Insert New Row with Key' + cast(t.Id as nvarchar(10)) + 'in table1'
from table1 t where Id IN (select Id from inserted)
end
go
SELECT c.name AS 'ColumnName'
,t.name AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%COLUMN_NAME%'
ORDER BY TableName
,ColumnName;
SELECT 'DROP TABLE ' + TABLE_NAME + ''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'PREFIX_%'
DECLARE @SQL varchar(4000)=''
SELECT @SQL =
@SQL + 'ALTER TABLE ' + s.name+'.'+t.name + ' DROP CONSTRAINT [' + RTRIM(f.name) +'];' + CHAR(13)
FROM sys.Tables t
INNER JOIN sys.foreign_keys f ON f.parent_object_id = t.object_id
INNER JOIN sys.schemas s ON s.schema_id = f.schema_id
WHERE t.name LIKE 'PREFIX_%'
--EXEC (@SQL)
PRINT @SQL
-- two variables to hold product name and list price (gonna be used on the loop)
DECLARE
@product_name VARCHAR(MAX),
@list_price DECIMAL;
--defines the result set for the cursor
DECLARE cursor_product CURSOR
FOR SELECT
product_name,
list_price
FROM
dbo.products;
-- open cursor
OPEN cursor_product;
--fetch a row from the cursor into one or more variables
FETCH NEXT FROM cursor_product INTO
@product_name,
@list_price;
-- loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- use current product_name and list_price from current index of the cursor in the loop
PRINT @product_name + CAST(@list_price AS varchar);
-- fetch next row from the cursor
FETCH NEXT FROM cursor_product INTO
@product_name,
@list_price;
END;
-- close cursor
CLOSE cursor_product;
-- deallocate the cursor to release it
DEALLOCATE cursor_product;
SELECT schema_name(tab.schema_id) as schema_name,
tab.name as table_name,
col.column_id,
col.name as column_name,
t.name as data_type,
col.max_length,
col.precision
FROM sys.tables as tab
INNER JOIN sys.columns as col
on tab.object_id = col.object_id
LEFT JOIN sys.types as t
on col.user_type_id = t.user_type_id
ORDER BY schema_name,
table_name,
column_id;