Tags » T-SQL

T-SQL Updates

Simple update

UPDATE Sales.MyOrderDetails SET discount += 0.05 
WHERE orderid = 10251;

Update while joining
Only one table can be updated at a time.

UPDATE OD
SET OD.discount += 0.05
FROM Sales.MyCustomers AS C
INNER JOIN Sales.MyOrders AS O
ON C.custid = O.custid
INNER JOIN Sales.MyOrderDetails AS OD
ON O.orderid = OD.orderid
WHERE C.country = N'Norway'; 160 more words
70-461

T-SQL - Inserts

Simple inserts

INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) 
VALUES(2, 19, '20120620', N'USA', 30.00); --explicitly select columns

INSERT INTO Sales.MyOrders
VALUES(2, DEFAULT, '20120620', N'USA', 30.00); --insert data to all existing columns

SET IDENTITY_INSERT Sales.MyOrders ON; -- when you want to insert data into identity column (alter table privilege neccessary)
INSERT INTO Sales.MyOrders (orderid, custid, empid, orderdate, shipcountry, freight)
VALUES(9999, 2, 19, '20120620', N'USA', 30.00);
SET IDENTITY_INSERT Sales.MyOrders OFF;
… 104 more words
70-461

T-SQL - Synonyms

Synonym
It is an alternative name for an existing database object (i.e. table, function, procedure, view, …). You can shorten your query using synonyms.
Synonyms are not allowed in DDL queries. 18 more words

70-461

T-SQL - Views and Inline Functions

View can be used to hide complicated query. You can give access to the view, but not to the table used by the view. Only select used to create the view is stored, data (rows) is not. 204 more words

70-461

T-SQL - Creating, altering, deleting tables

A few examples how to create and alter tables.

Create
Simple table with different data types and constraints:

CREATE TABLE .
(
	  IDENTITY(1,1) PRIMARY KEY,
	 (100) NOT NULL,
	 (30) NULL DEFAULT (N''),
	 (20) NOT NULL UNIQUE,
	 (4, 0) NOT NULL CHECK ( >= 1900),
	  NULL,
	  AS (datepart(year,getdate())-), -- computed column, data type is not explicitly given
	 (0) NULL, -- time only with 0 fractional seconds
	 (0) NULL, -- date and time with timezone
	 (7) NOT NULL DEFAULT (getdate()) -- timespan data type is obsolete
) 406 more words
70-461

Running maintenance on Azure SQL databases

This article was recently published on dev.getroadmap.com:

To keep your data healthy and topfit, we all know you need to run something called database maintenance on your databases. 1,382 more words

SQL Server

T-SQL - Windowing

Windowing functions calculate result for every row given in OVER clause. Rows in OVER clause are called ‘rows window’. Calculation is performed for each such group. 513 more words

70-461