--
Date/Datetime Functions
SELECT DATETIMEFROMPARTS(2012, 2, 12, 18, 10, 5, 997)
SELECT DATETIMEFROMPARTS(2012, 2, 12, 18, 10, 5, 998)
Be careful the millisecond part, 998 returns the same result as 997.
Be careful the millisecond part, 999 returns the result as 000 and add 1 to second part.
Because the milliseconds part of the end point 999 is not a multiplication of the precision unit, so SQL Server ends up rounding the value to next second.
SELECT DATETIMEFROMPARTS(2012, 2, 12, 18, 10, 5, 999)
SELECT DATEFROMPARTS(2012, 2, 12)
--★★★--
--Returns the end of input month date.
SELECT EOMONTH(GETDATE())
--====================================================================================================
--
String Functions
Substitutes a NULL input with an empty string.
SELECT CONCAT(NULL, '1', '2')
--★★--
Formats an input value based on a format string.
For more details, refer here: http://msdn.microsoft.com/en-us/library/26etazsy%28v=vs.110%29.aspx
SELECT FORMAT(123456789,'####-##-#.00')
Accepts a list of expressions as input and returns the first that is not NULL, or NULL if all are NULLs Or return something else by designating the last expression.
SELECT COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product
The result above is same as the following, after testing, its performance is the same as well,
the only advantage is simplify the statement.
SELECT CASE
WHEN Class IS NOT NULL THEN Class
WHEN Color IS NOT NULL THEN Color
WHEN ProductNumber IS NOT NULL THEN ProductNumber
ELSE NULL
END
FROM Production.Product
There are a couple of subtle differences between COALESCE and ISNULL. The type of the COALESCE is determined by the returned element, whereas the type of the ISNULL expression is determined by the first input.
DECLARE @x AS VARCHAR(3) = NULL, @y AS VARCHAR(10) = '1234567890'
SELECT COALESCE(@x, @y) AS [COALESCE], ISNULL(@x, @y) AS [ISNULL]
Returns NULL instead of failing convert the input expression to the target type.
SELECT TRY_CONVERT(DATE, 2012)
SELECT TRY_CONVERT(INT, 'TEST')
Note that it still needs to follow the rule of conversions table.
--====================================================================================================
--
Order Functions
Specify the OFFSET clause indicating how many rows you want to skip (0 if you don’t want to skip any); you then optionally specify the FETCH clause indicating how many rows you want to filter.
SELECT CREATED
FROM TransmaxDW.dbo.JiraIssues
ORDER BY CREATED DESC
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY
--====================================================================================================
--
Aggregate Functions
--★★★--
--Calculate accumulating value
In the window frame clause, you indicate the window frame units (ROWS or RANGE) and the window frame extent (the delimiters). With the ROWS window frame unit, you can indicate the delimiters as one of three options:
1. UNBOUNDED PRECEDING or FOLLOWING, meaning the beginning or end of the partition, respectively.
2. CURRENT ROW, obviously representing the current row.
3. <n> ROWS PRECEDING or FOLLOWING, meaning n rows before or after the current, respectively.
SELECT custid, orderid, orderdate, val,
SUM(val) OVER (
PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
--ROWS UNBOUNDED PRECEDING --Shorter form, same result as above
) AS runningtotal
FROM TSQL2012.Sales.OrderValues;
If you define a order clause without a frame clause, the default is as follows, unless you are after the special behavior you get from RANGE that includes peers (tied rows), make sure you explicitly use the ROWS option. In SQL Server 2012, the ROWS option usually gets optimized much better than RANGE when using the same delimiters.
RANGE UNBOUNDED PRECEDING
--====================================================================================================
--
Offset Functions
--★★★--
--Retrieve data from a previous row or next row
SELECT custid, orderid, orderdate, val,
LAG(val) OVER
(
PARTITION BY custid
ORDER BY orderdate, orderid
) AS prev_val,
LEAD(val) OVER
(
PARTITION BY custid
ORDER BY orderdate, orderid
) AS next_val
FROM TSQL2012.Sales.OrderValues;
The second argument represents offset position, the number of rows back/forward from the current row from which to obtain a value. If not specified, the default is 1. It can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. The third argument represents the value to return when scalar_expression (first argument) at offset is NULL. If a default value is not specified, NULL is returned. It can be a column, subquery, or other expression, but it cannot be an analytic function, it must be type-compatible with scalar_expression.
SELECT custid, orderid, orderdate, val,
LAG(val, 2, 0) OVER
(
PARTITION BY custid
ORDER BY orderdate, orderid
) AS prev_val,
LEAD(val, 2, 0) OVER
(
PARTITION BY custid
ORDER BY orderdate, orderid
) AS next_val
FROM TSQL2012.Sales.OrderValues;
--★--
Return a value expression from the first or last rows in the window frame.
SELECT custid, orderid, orderdate, val,
FIRST_VALUE(val) OVER
(
PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS first_val,
LAST_VALUE(val) OVER
(
PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS last_val
FROM TSQL2012.Sales.OrderValues;
When a window frame is applicable to a function but you do not specify an explicit window frame
clause, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Also, if you’re after the first row in the partition, using the FIRST_VALUE function with the default frame at least gives you the correct result. However, if you’re after the last row in the partition, using the LAST_VALUE function with the default frame won’t give you what you want because the last row in the default frame is the current row. So with the LAST_VALUE, you need to be explicit about the window frame in order to get what you are after. And if you need an element from the last row in the partition, the second delimiter in the frame should be UNBOUNDED FOLLOWING.
--====================================================================================================
--
Full-Text Data
You can create a search property list to define searchable properties for your documents. You can include properties that a specific filter can extract from a document. See the Books OnLine for SQL
Server 2012 article
"Find Property Set GUIDs Property Integer IDs for Search Properties" for the list of some well-known ones, or refer to this article
"Search Document Properties with Search Property Lists" see how Full-Text Search works with search properties.
CREATE SEARCH PROPERTY LIST WordSearchPropertyList
GO
ALTER SEARCH PROPERTY LIST WordSearchPropertyList
ADD 'Authors'
WITH (
PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9',
PROPERTY_INT_ID = 4,
PROPERTY_DESCRIPTION = 'System.Authors - authors of a given item.'
);
--====================================================================================================
--
Sequence Object
A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables.
CREATE SEQUENCE Sales.SeqOrderIDs AS INT
MINVALUE 1
CYCLE;
It is similar to IDENTITY, all numeric types with a scale of 0 are supported. If you don’t indicate a type explicitly, SQL Server will assume BIGINT by default. If you need a different type, you need to ask for it explicitly by adding AS <type> after the sequence name. There are a number of properties that you can set, all with default options in case you don’t provide your own. The following are some of the properties and their default values:
1. INCREMENT BY Increment value. The default is 1.
2. MINVALUE
The minimum value to support. The default is the minimum value in the type. For
example, for an INT type, it will be -2147483648.
3. MAXVALUE The maximum value to support. The default is the maximum value in the type.
4. CYCLE | NO CYCLE Defines whether to allow the sequence to cycle or not. The default is NO
CYCLE.
5. START WITH The sequence start value. The default is MINVALUE for an ascending sequence
(positive increment) and MAXVALUE for a descending one. */
SELECT NEXT VALUE FOR Sales.SeqOrderIDs;
ALTER SEQUENCE TSQL2012.Sales.SeqOrderIDs
RESTART WITH 1;
INSERT INTO TSQL2012.Sales.MyOrders(orderid, custid, empid, orderdate)
VALUES (NEXT VALUE FOR Sales.SeqOrderIDs, 1, 2, '20120620'),
(NEXT VALUE FOR Sales.SeqOrderIDs, 1, 3, '20120620');
INSERT INTO TSQL2012.Sales.MyOrders(orderid, custid, empid, orderdate)
SELECT NEXT VALUE FOR Sales.SeqOrderIDs OVER(ORDER BY orderid), custid, empid, orderdate
FROM TSQL2012.Sales.Orders
WHERE custid = 1;
ALTER TABLE Sales.MyOrders
ADD CONSTRAINT DFT_MyOrders_orderid
DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs) FOR orderid;
INSERT INTO TSQL2012.Sales.MyOrders( custid, empid, orderdate)
VALUES (1, 2, '20120620')
If you accidentally reset start value, and the target table also has unique constraint on sequencing column to cause failure of violating constraint. Execute the following command to restart the sequence.
DECLARE @orderid_max INT, @str_alter VARCHAR(500)
SELECT @orderid_max = MAX(orderid) FROM TSQL2012.Sales.MyOrders
SET @str_alter = ' ALTER SEQUENCE TSQL2012.Sales.SeqOrderIDs
RESTART WITH ' + CONVERT(VARCHAR(5), @orderid_max + 1)
EXEC(@str_alter)
--====================================================================================================
--
Error Handling
DECLARE @message AS NVARCHAR(1000) = 'Error in % stored procedure';
SELECT @message = FORMATMESSAGE (@message, N'usp_InsertCategories');
RAISERROR (@message, 16, 0);
SELECT @message = FORMATMESSAGE (@message, N'usp_InsertCategories');
THROW 50000, @message, 0;
The THROW command behaves mostly like RAISERROR, with some important exceptions. Errors must have an error number of at least 50000. The statement before the THROW statement must be terminated by a semicolon (;). This reinforces the best practice to terminate all T-SQL statements with a semicolon. RAISERROR does not normally terminate a batch; however, THROW does terminate the batch.
