I wish i knew that yesterday!

•Download as PPTX, PDF•

0 likes•692 views

This document contains a summary of SQL Server 2012 features presented by Matt Horn. It discusses sequences, paging, dates, string functions, conversion functions, and other useful features. The presentation covers setting min/max values for sequences, using OFFSET and FETCH for paging through query results, new date/time functions like DATEFROMPARTS, string concatenation with CONVERT and formatting with FORMAT, and conversion functions like PARSE and TRY_CONVERT.

I wish I knew that yesterday!
SQL Server 2012 Awesomeness
and some other stuff

Matt Horn
• Senior Consultant – Aphelion Software
• Microsoft® Certified Technology Specialist
• Co-lead JHB Business Intelligence
Developer Network
• Lead - IntelliCape B.I. User Group
9/27/2013
|
Managing role based security with SharePoint2 |

Agenda
 Sequences
 Paging
 Dates
 String Functions
 Conversion Functions
 Other useful @#$%
9/27/2013
|
Footer Goes Here3 |

Sequences
 Used to generate numeric sequences.
 Set min and max values
 Caching of future values minimizing disk
IO. 0/

OFFSET & FETCH
 ORDER BY is mandatory
 OFFSET and FETCH are a pair.
 TOP is a no no.

New Functions
 DATEFROMPARTS
 DATETIMEFROMPARTS
 DATETIME2FROMPARTS
 SMALLDATETIMEFROMPARTS
 DATETIMEOFFSETFROMPARTS
 TIMEFROMPARTS
 EOMONTH

String Functions
 CONCAT
 Does what it says on the box
 FORMAT
 Looks simple. MASSIVELY useful

Conversion Functions
 PARSE
 TRY_PARSE
 TRY_CONVERT

I wish i knew that yesterday!

1. I wish I knew that yesterday! SQL Server 2012 Awesomeness and some other stuff

2. Matt Horn • Senior Consultant – Aphelion Software • Microsoft® Certified Technology Specialist • Co-lead JHB Business Intelligence Developer Network • Lead - IntelliCape B.I. User Group 9/27/2013 | Managing role based security with SharePoint2 |

3. Agenda  Sequences  Paging  Dates  String Functions  Conversion Functions  Other useful @#$% 9/27/2013 | Footer Goes Here3 |

4. Sequences

5. Sequences  Used to generate numeric sequences.  Set min and max values  Caching of future values minimizing disk IO. 0/

6. Paging

7. OFFSET & FETCH  ORDER BY is mandatory  OFFSET and FETCH are a pair.  TOP is a no no.

8. Dates

9. New Functions  DATEFROMPARTS  DATETIMEFROMPARTS  DATETIME2FROMPARTS  SMALLDATETIMEFROMPARTS  DATETIMEOFFSETFROMPARTS  TIMEFROMPARTS  EOMONTH

10. Strings

11. String Functions  CONCAT  Does what it says on the box  FORMAT  Looks simple. MASSIVELY useful

12. Conversion Functions  PARSE  TRY_PARSE  TRY_CONVERT

13. Other useful .. stuff

Editor's Notes

A sequence is a user-defined schema bound object that generates a sequence of numeric values.NOT LIKE IDENTITY INSERTS – Don’t need to insert a row to get the next value.You can either use to insert identity values into a table (or multiple tables) or you can also use it in some T-SQL script, since it is an object.Incredibly useful for partition switching
A sequence is a user-defined schema bound object that generates a sequence of numeric values.NOT LIKE IDENTITY INSERTS – Don’t need to insert a row to get the next value.You can either use to insert identity values into a table (or multiple tables) or you can also use it in some T-SQL script, since it is an object.Incredibly useful for partition switching
Paging is one of the most used patterns in application dev.It’s also the one that tends to get borked the most. SQL Server 2012 introduces a few new things to help out in this regard.
The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set.There are some limitations with offset & fetch
The DATEFROMPARTS function, returns a date value with the date part set to the specified year, specified month and the specified day, and the time portion set to the default.The DATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified milliseconds.The DATETIME2FROMPARTS function, returns full datetime2 value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified precision.The SMALLDATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour and the specified minute.The DATETIMEOFFSETFROMPARTS function returns a full datetimeoffset. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.
FORMAT(), expects at three parameters,Value – Expression of a supported data type to format.Format – nvarchar format pattern.Culture – Optional nvarchar argument specifying a culture.“If the culture argument is not provided, then the language of the current session is used. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL Server . If the culture argument is not valid, FORMAT raises an error.”DEMO
PARSEAttempts to parse a string and convert it to a specified datatype.Can only convert to a number or datetimeUses the .Net CLRTRY_PARSE()Like PARSE but returns a NULL if the PARSE failsstill uses .NETCLRTRY_CONVERTAttempts to cast a value into a specified data type. Returns NULL if CONVERT fails.

�ݺ�ߣ

I wish i knew that yesterday!

More Related Content

I wish i knew that yesterday!

Editor's Notes