Window Functions are functions on subsets of rows that meet a pre-defined, user defined criteria. Any subset of user-defined records or rows is called a “window”.
SQL Server Denali takes the Window Functions to the next step by adding functionality.
Window Functions are, as examples, ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE(), OVER(), LEAD and LAG.
LEAD and LAG
Return the previous (LAG) and the next (LEAD) value
LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )
SELECT LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS 'LAG' FROM .....
LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] )
SELECT LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS 'LEAD' FROM .....
FIRST_VALUE and LAST_VALUE
Return the first or the last value in the "window".
FIRST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause [rows_range_clause ] )
LAST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )
More T-SQL stuff
TRY_CONVERT(), PARSE() and TRY_PARSE()
TRY_CONVERT() - Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
SELECT TRY_CONVERT(float,'test') -- result: NULL
SELECT TRY_CONVERT(datetime2, '12/31/2010') -- result: 2010-12-31 00:00:00.0000000
PARSE() - Returns a value cast to the specified data type if the cast succeeds; otherwise, raise an error.
SELECT PARSE('10' AS TIME) -- result: ERROR!
SELECT PARSE('10' AS Float(10,2)) -- result: 10.00
TRY_PARSE() - Returns a value cast to the specified data type if the cast succeeds; otherwise, return null.
SELECT TRY_PARSE('10' AS TIME) -- result: NULL
SELECT TRY_PARSE('10' AS Float(10,2)) -- result: 10.00
FORMAT()
Returns a value formatted with the specified format and optional culture.
Mainly useful for Dynamic SQL.
FORMAT ( value, format [, culture ] )
SELECT FORMAT(@d, N'yyyy-MM')
SELECT FORMAT(@d, N'yyyy-MM-dd'),
SELECT FORMAT(@d, N'dddd, MMMM dd, yyyy', N'fr-fr')
SELECT Format(0.5,'000.0')
SELECT Format(1234,'#,###')
IIf()
IIf(Condition, value_if_true, value_if_false)
SELECT IIF(GETDATE() > '20110101', '2011', 'earlier)'
Choose
Choose(index, val_1, val_2 [, val_n ])
SELECT Choose(3, 'A', 'B', 'C', 'D') -- result: 'C'
SELECT Choose(ColInt, 'one', 'two', 'three', 'four', ...) FROM TABLENAME
Concat ()
Concat is similar to the operator +, with those differences:
- convertion of numbers and dates without 'Cast'.
- Null = '' and not make all the result to be null.
Select Concat(@S,',',object_id) From sys.objects
-- result: all the IDs from sys.objects, separated by ','.
Datetime Functions creation
DateTimeFromParts
DateTime2FromParts
DateTimeOffsetFromParts
SmallDateTimeFromParts
Functions that create datetime from divided parts.
WITH RESULT SETS
Change the names and/or data types of the returning result set of a stored procedure.
SQL built-in Paging
Returns only metadata
SET FMTONLY returns only metadata to the client.
SEQUENCE
SEQUENCE allows you to define a central place where SQL Server will maintain a counter of values for you.
Using a sequence number in multiple tables is one of the adventages of SEQUENCE.
http://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx
--CREATE SEQUENCE:
CREATE SEQUENCE dbo.Seq1
AS INT -- could be any type of int
MINVALUE 1 -- default: min value of the defined type
MAXVALUE 100 -- default: max value of the defined type
START WITH 1
INCREMENT BY 1 -- or any other number
CYCLE
GO
-- note: CYCLE is not must parameter - if it chosen, when arrive to the max. vakue, the SEQUENCE will start again from the min. value.
-- get new number from SEQUENCE:
SELECT NextID = NEXT VALUE FOR dbo.dbo.Seq1
http://msdn.microsoft.com/en-us/library/ff878370(v=sql.110).aspx
-- ALTER SEQUENCE:
ALTER SEQUENCE dbo.Seq1 RESTART WITH 15
Notes:
1. SEQUENCE will not provide you a transactionally consistent and gap-free stream of values. Like IDENTITY, if your transaction rolls back, the next value is still taken.
2. One of the nice adventage of SEQUENCE relatively to IDENTITY is that you can get the next ID before the insert statement:
DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR dbo.Seq1
.........
INSERT TABLENEME (ID, Name) VALUES (@NextID, 'Itai')
Column based database - Apollo Project
Column Store Index
Column Store Index is new type of index which stored columns in separate set of data pages instead of rows stored in data pages.
Column Store Index, that can improve performance, mostly for queries which are used aggregation , warehouse processing and BI application.
Column Store Index is read only.
CREATE NONCLUSTERED COLUMNSTORE INDEX MyIndex ON MyTable(col1, col2, …)
SQL Server Developer Tools, Codename "Juneau"
"Juneau" is the new SSMS for developers in Denali.
"Juneau" (SSDT) is a tool for on and off-premise database development within Visual Studio.
The Server Explorer in VS gives you an SSMS-like view of your live database objects.
SSDT also provides database developers with code navigation, IntelliSense, language support that parallels what is available for C# and VB, platform specific validation, debugging and declarative editing in the TSQL Editor, as well as a visual Table Designer for both database projects and online database instances.
Reporting Services - Project Crescent
"Crescent" is the new Reporting Services in Denali.
Project “Crescent” is a new interactive data exploration and visual presentation experience. It will offer a fun, visual, and powerful drag-and-drop ad hoc reporting experience. It is an web-based end-user BI tool based on Silverlight.
Demo for SQL Server Project "Crescent":
HADR - AlwaysOn
HADR, (or AlwaysOn, or Availability Groups)
The Always On Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring.
AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise.
Deploying AlwaysOn Availability Groups involves creating and configuring one or more availability groups.
More Useful links:
MSDN: What's New (SQL Server "Denali")
Download the SQL Server Denali Community Technical Preview 3 (CTP3) Product Guide:
SQL Server code name Denali Links:
