T-SQL
Statements
SELECT
TOP with variable
SELECT TOP(@count) * FROM DataTable;
LIKE with variables
SELECT * FROM DataTable WHERE Field1 LIKE @pattern + '%';
ORDER BY clause
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
Operators
Set Operators
EXCEPT
https://www.essentialsql.com/get-ready-to-learn-sqlserver-18-how-to-use-the-except-operator/
INTERSECT
UNION
Functions
Aggregate Functions
Count
Conversion
CAST and CONVERT
CAST (CASE WHEN [ASSIGNABLE] = 'Yes' THEN 1 ELSE 0 END AS BIT)
Key differences between SQL CAST vs. CONVERT functions
Know when to use Cast versus Convert
CAST is the more ANSI-standard of the two functions, meaning that while it's more portable, it's also less powerful. CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression. For those reasons, it's best to use CAST first, unless there is some specific thing that only CONVERT can provide in the work you're doing.
String to string
hhmmss to hh:mm:ss
SELECT STUFF(STUFF('091213', 5, 0, ':'), 3, 0,':')
String to datetime
http://www.sqlusa.com/bestpractices/datetimeconversion/
String to date
http://sqlusa.com/bestpractices2005/sqlserverstringtodate/
yyyy-MM-dd
SELECT CONVERT(DATETIME, '2012-08-17', 111);
SELECT CAST(REPLACE('2012-08-17', '-', '') AS date);
yyyyMMdd
SELECT CAST('20120817' AS date);
String to time
hh:mm:ss
SELECT CAST('9:12:13' AS time);
hhmmss
SELECT CAST(STUFF(STUFF('091213', 5, 0, ':'), 3, 0,':') AS time)
Date to string
https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/
yyyy-mm-dd
SELECT CONVERT(char(10), GetDate(), 126);
Number to string with leading zero
SELECT RIGHT(100000 + 12, 5)
Date and Time
DATEADD
String
removeNumericChars
/*
To remove all numeric characters
Usage:
SELECT prp.removeNumericChars ('abc343434309');
*/
ALTER FUNCTION prp.removeNumericChars (
@Str varchar(max)
) RETURNS varchar(max)
AS
BEGIN
SELECT
@Str = REPLACE(@Str,NUMBER,'')
FROM MASTER.dbo.spt_values
WHERE TYPE ='P'
AND number BETWEEN 0 AND 9
;
RETURN TRIM(@Str); -- For SQL Server 2017
-- RETURN LTRIM(RTRIM(@Str));
END
GO
Ranking Functions
How
To get Row Count for all Tables in a Database
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY [TableName]
GO
SQL Server Row Count for all Tables in a Database