Skip to content

Instantly share code, notes, and snippets.

@develax

develax/T-SQL-Reminder.MD

Last active May 10, 2019 09:37
Show Gist options
  • Save develax/5b867e3277bfcf80bbc4cb081fc7e23c to your computer and use it in GitHub Desktop.
Save develax/5b867e3277bfcf80bbc4cb081fc7e23c to your computer and use it in GitHub Desktop.
Download ZIP
My T-SQL Reminder
Raw
T-SQL-Reminder.MD

T-SQL

The order of processing clauses

  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
    • Expressions
    • DISTINCT
  • ORDER BY
    • TOP/OFFSET FETCH

The precedence among operators, from highest to lowest

  1. () (Parentheses)
  2. * (Multiplication), / (Division), % (Modulo)
  3. + (Positive), – (Negative), + (Addition), + (Concatenation), – (Subtraction)
  4. =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
  5. NOT
  6. AND
  7. BETWEEN, IN, LIKE, OR
  8. = (Assignment)

Types

  • DATETIME2 Has a bigger date range and better precision than the legacy types.

  • DATETIMEOFFSET Is similar to DATETIME2, but it also includes the offset from UTC.

  • DECIMAL [(p[,s])] / NUMERIC [(p[,s])] They have fixed precision and scale and are synonyms and can be used interchangeably. The precision must be a value from 1 through the maximum precision of 38. It means maximum total number of decimal digits to be stored. This number includes both the left and the right sides of the decimal point.The default precision is 18. When maximum precision is used, valid values are from - 10^38 + 1 through 10^38 - 1. The scale is the number of decimal digits that are stored to the right of the decimal point. This number is subtracted from precision to determine the maximum number of digits to the left of the decimal point. Scale must be a value from 0 through precision, and can only be specified if precision is specified. The default scale is 0 and so 0 <= s <= p.

  • MONEY From -922,337,203,685,477.58 to 922,337,203,685,477.58

  • SMALLMONEY From - 214,748.3648 to 214,748.3647

Currency or monetary data does not need to be enclosed in single quotation marks ( ' ). It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value.

  • CHAR[(n)] Fixed-length string data. n defines the string length in bytes and must be a value from 1 through 8,000. For single-byte encoding character sets such as Latin, the storage size is n bytes and the number of characters that can be stored is also n. For multibyte encoding character sets, the storage size is still n bytes but the number of characters that can be stored may be smaller than n.

  • VARCHAR[(n|max)] Variable-length string data. n defines the string length in bytes and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). For single-byte encoding character sets such as Latin, the storage size is n bytes + 2 bytes and the number of characters that can be stored is also n. For multibyte encoding character sets, the storage size is still n bytes + 2 bytes but the number of characters that can be stored may be smaller than n. When n isn't specified in a data definition or variable declaration statement, the default length is 1. If n isn't specified when using the CAST and CONVERT functions, the default length is 30.

Any data type without the VAR element (CHAR, NCHAR) in its name has a fixed length, which means that SQL Server preserves space in the row based on the column’s defined size and not on the actual number of characters in the character string. For example, when a column is defined as CHAR(25), SQL Server preserves space for 25 characters in the row regardless of the length of the stored character string. Because no expansion of the row is required when the strings are expanded, fixed-length data types are more suited for write-focused systems.

A data type with the VAR element (VARCHAR, NVARCHAR) in its name has a variable length, which means that SQL Server uses as much storage space in the row as required to store the characters that appear in the character string, plus two extra bytes for offset data. For example, when a column is defined as VARCHAR(25), the maximum number of characters supported is 25, but in practice, the actual number of characters in the string determines the amount of storage. Because storage consumption for these data types is less than that for fixed-length types, read operations are faster. However, updates might result in row expansion, which might result in data movement outside the current page. Therefore, updates of data having variable-length data types are less efficient than updates of data having fixed-length data types.

  • Use CHAR when the sizes of the column data entries are consistent.
  • Use VARCHAR when the sizes of the column data entries vary considerably.
  • Use VARCHAR(MAX) when the sizes of the column data entries vary considerably, and the string length might exceed 8,000 bytes.

Using language-neutral formats

Such as ‘YYYYMMDD’ is a best practice, because such formats are interpreted the same way regardless of the LANGUAGE/DATEFORMAT settings.

The OFFSET-FETCH filter

SELECT	orderid, orderdate, custid, empid
FROM	Sales.Orders
ORDER	BY orderdate, orderid
OFFSET	50 ROWS FETCH NEXT 25 ROWS ONLY;
  • OFFSET-FETCH must have an ORDER BY clause.
  • T-SQL doesn’t support the FETCH clause without the OFFSET clause.
  • OFFSET without FETCH is allowed.
  • ROW and ROWS are interchangeable.
  • FIRST and NEXT are interchangeable.

NULLs

  • For grouping and sorting purposes, two NULLs are considered equal.
  • IN T-SQL a UNIQUE constraint considers two NULLs as equal (allowing only one NULL).

Sort NULLs last

SELECT custid, region
FROM Sales.Customers
ORDER BY 
	CASE WHEN region IS NULL THEN 1 ELSE 0 END,
	region;

CASE expressions

SELECT empid, firstname, lastname, titleofcourtesy, 
	CASE titleofcourtesy
		WHEN N'Ms.' THEN N'Female'
		WHEN N'Mrs.' THEN N'Female'
		WHEN N'Mr.' THEN N'Male'
		ELSE N'Unknown'
	END AS gender
FROM HR.Employees;
SELECT empid, firstname, lastname, titleofcourtesy, 
	CASE
		WHEN titleofcourtesy IN (N'Ms.', N'Mrs.') THEN N'Female'
		WHEN titleofcourtesy = N'Mr.' THEN N'Male'
		ELSE N'Unknown'
	END AS gender
FROM HR.Employees;
SELECT col1, col2
FROM dbo.T1
WHERE
  CASE
    WHEN col1 = 0 THEN 'no' -- or 'yes' if row should be returned
    WHEN col2/col1 > 2 THEN 'yes'
    ELSE 'no'
  END = 'yes';

Window-functions

SELECT custid, orderdate, orderid,
	ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders
ORDER BY custid

DB Schemas

dbo is created automatically in every database and is also used as the default schema for users who are not explicitly associated with a diffenect schema.

Data integrity

There are 2 kinds of data integrity:

  1. Declarative data integrity.
  2. Procedural data integrity.

Constraints

Primary key constraints

A primary-key constraint enforces the uniqueness of rows and also disallows NULLs in the constraint attributes. SQL Server will create a unique index behind the scenes.

Unique constraints

Unlike with primary keys, you can define multiple unique constraints within the same table. Also, a unique constraint is not restricted to columns defined as NOT NULL.

According to standard SQL, a column with a unique constraint is supposed to allow multiple NULLs (as if two NULLs were different from each other). However, SQL Server’s implementation rejects duplicate NULLs (as if two NULLs were equal to each other). To emulate the standard unique constraint in SQL Server you can use a unique filtered index that filters only non-NULL values. For example, suppose that the column ssn allowed NULLs, and you wanted to create such an index instead of a unique constraint. You would have used the following code:

CREATE UNIQUE INDEX idx_ssn_notnull ON dbo.Employees(ssn) WHERE ssn IS NOT NULL;

Foreign Keys

NULLs are allowed in the foreign-key columns.

No action means that attempts to delete rows from the referenced table or update the referenced candidate-key attributes will be rejected if related rows exist in the referencing table.

Create table

CREATE TABLE dbo.SalesManagers (
	Id int IDENTITY(1, 1) PRIMARY KEY,
	FullName nvarchar(100) NOT NULL,
	QualificationId int NOT NULL CONSTRAINT FK_SalesManagers_Qualifications FOREIGN KEY REFERENCES dbo.Qualifications(Id) ON UPDATE CASCADE,
	LengthOfService int NOT NULL,
	PersonalTransport bit NOT NULL
);

Alter table

ALTER TABLE dbo.SalesManagers
  ADD CONSTRAINT FK_SalesManagers_Qualifications
  FOREIGN KEY(QualificationId)
  REFERENCES dbo.Qualifications(Id);

Alter table (autogenerated by MSSMS)

ALTER TABLE [dbo].[SalesManagers]  WITH CHECK ADD  CONSTRAINT [FK_SalesManagers_Qualifications] FOREIGN KEY([QualificationId])
REFERENCES [dbo].[Qualifications] ([Id])
GO

ALTER TABLE [dbo].[SalesManagers] CHECK CONSTRAINT [FK_SalesManagers_Qualifications]
GO

EXAMPLES

Temporary Stored Procedures / TRY..CATCH

USE master;

DROP PROC IF EXISTS #createDb;
GO

CREATE PROC #createDb
@dbName varchar(MAX),
@dropExistingDb bit
AS
BEGIN
	IF DB_ID(@dbName) IS NULL OR @dropExistingDb = 1
	BEGIN
		BEGIN TRY
			DECLARE @cmd AS varchar(MAX);
			
			IF @dropExistingDb = 1
			BEGIN
				SET @cmd = 'DROP DATABASE ' + @dbName;
				PRINT @cmd;
				EXECUTE(@cmd);
			END;

			SET @cmd = 'CREATE DATABASE ' + @dbName;
			PRINT @cmd;
			EXECUTE(@cmd);
			EXECUTE('ALTER DATABASE ' + @dbName + ' SET RECOVERY SIMPLE;');
		END TRY
		BEGIN CATCH
			PRINT 'ERROR: creating database `' + @dbName + '` failed. Details: ';
			PRINT '    Error Number  : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
			PRINT '    Error Message : ' + ERROR_MESSAGE();
			PRINT '    Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
			PRINT '    Error State   : ' + CAST(ERROR_STATE() AS VARCHAR(10));
			PRINT '    Error Line    : ' + CAST(ERROR_LINE() AS VARCHAR(10));
			PRINT '    Error Proc    : ' + COALESCE(ERROR_PROCEDURE(), 'Not within proc');
		END CATCH;
	END;
	ELSE BEGIN
		PRINT '`' + @dbName + '` already exists.';
	END;
END
GO

DECLARE @dropExistingDb AS bit = 1;

EXEC #createDb 'NewDbName', @dropExistingDb;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment