SQL Sunday Word Scramble

SQL Server – TSQL Analytics Word Search

ScrambleAnalytics

 

Find the following words in the word scramble above.

TOP

RANK

DENSE_RANK

ROW_NUMBER

NTILE

OVER

LAG

LEAD

FIRST_VALUE

LAST_VALUE

PERCENT_RANK

PERCENTILE_DISC

PERCENTILE_CONT

CUME_DIST

PARTITION

ROWS

PRECEDING

FOLLOWING

UNBOUNDED

 

Download the Printable PDF of the Word Search.

See Also

Downloads for this weeks SQL Training

As posted earlier.

This weeks SQL Queries training for the 70-461 certification will be on Thursday at 9:00am (US pacific time). Please join the live broadcast on Google On Air Hangouts, or catch the recorded version on myYouTube Channel.

Aaron Buma will be presenting the first half of the session and I will be presenting the second half.

We will start off with all the features around the TOP(N) in your query, followed by using the RANK(), DENSE_RANK and ROW_NUMBER. Then on to Analytic Functions to help with your statistical analysis of data. Similar to aggregate functions, analytics functions compute over a group of rows with multiple results within each group. Learn how to properly use each of these analytics functions in combination with enhancements to the OVER clause. Learn the value of CUME_DIST and PERCENT_RANK in your analysis, use LAST_VALUE and FIRST_VALUE along with PERCENTILE_DISC, PERCENTILE_CONT, LEAD and LAG you can look at data in the next and previous rows.

 

Here is the download: TopRankingNtileAndAnalytics.zip

Check back in a day or two to get the edited video of the training.

Enjoy.

This Weeks SQL Server Queries Training

This weeks SQL Queries training for the 70-461 certification will be on Thursday at 9:00am (US pacific time). Please join the live broadcast on Google On Air Hangouts, or catch the recorded version on my YouTube Channel.

Aaron Buma will be presenting the first half of the session and I will be presenting the second half.

We will start off with all the features around the TOP(N) in your query, followed by using the RANK(), DENSE_RANK and ROW_NUMBER. Then on to Analytic Functions to help with your statistical analysis of data. Similar to aggregate functions, analytics functions compute over a group of rows with multiple results within each group. Learn how to properly use each of these analytics functions in combination with enhancements to the OVER clause. Learn the value of CUME_DIST and PERCENT_RANK in your analysis, use LAST_VALUE and FIRST_VALUE along with PERCENTILE_DISC, PERCENTILE_CONT, LEAD and LAG you can look at data in the next and previous rows.

This presentation will be a few Power Point slides that present the high level overview, followed by in depth query demos. Check back for the sample code after the presentation.

SQL Sunday Fun – Word Search with New T-SQL 2012 Functionality

 

 

new_sql2012_word_jumbleClick on the grid to enlarge, then print it.

Find the following words in the grid up, down, left, right, horizontal, vertical, and diagonal.

ROWS  RANGE  PRECEDING 
FOLLOWING  UNBOUNDED  IIF 
CHOOSE  OFFSET  FETCH 
FORMAT  CONCAT  SEQUENCE 
PARSE  TRYPARSE  TRYCONVERT 
THROW  LEAD  LAG 
FIRSTVALUE  LASTVALUE  PERCENTRANK 
CUMEDIST  PERCENTILEDISC  PERCENTILECONT 

NOTE:  some of these terms normally have an underscore in them, but for the purpose of the word scramble the underscores have been omitted.

 

Related Links:

SEQUENCE object

CHOOSE function

Analytics – Analytic Functions – PERCENT_RANK

Analytics PERCENTILE_DISC and PERCENTILE_CONT

IIF function in TSQL.

TSQL Analytic Functions LEAD and LAG

OVER clause with ROWS PRECEDING and FOLLOWING in TSQL 2012

OFFSET / FETCH in a SELECT statement

Cumulative Distribution Function (CDF) or as it is known in TSQL CUME_DIST()

 

 

Happy Birthday SQL Server 2012

It has been a year since SQL Server 2012 released.

There were some fun posts on Twitter about the SQL Server 2012 birthday or anniversary. For instance:

declare @greetings varchar(100)

SET @greetings= 'Happy Birthday @SQLServer 2012'

SELECT @greetings

and another anniversary query (which doesn’t actually run):

SELECT Happy FROM Anniversary WHERE Year = 1 AND Version = '11.0.2100.60';

1 year out since SQL 2012 released, and here are some things to note:

There is no more Business Intelligence Development Studio (BIDS) in SQL Server 2012, instead it has been replaced with the SQL Server Data Tools that uses the core of Visual Studio 2010, with the Report Builder and Report Designer built in to the new Visual Studio.

SQL Server 2012 introduced a new T-SQL object used to generate numbers, a SEQUENCE, similar to an identity column.

SQL Server 2012 introduced the IIF statement. It is very similar to the IIF statement in MS Access.

In SQL Server 2012 there is a new function called CHOOSE that takes in an offset, and a list of options.

The OFFSET and FETCH functionality was added  to achieve data paging server side in TSQL.

Other Notable features:

I hope you are able to get as much out of SQL Server 2012 as I am.  Happy Birthday to SQL Server 2012.

My Top 10 TSQL Enhancements in SQL Server 2012

So far after playing around with SQL Server 2012 there are many new features to SQL.  Overall I think my favorites are in the Analytic Functions category.  Here is my breakdown of the top 10 transact SQL enhancements to SQL Server 2012.

10.  New SEQUENCE object

9.  CHOOSE function

8.  Analytics – Analytic Functions – PERCENT_RANK

7.  Analytics PERCENTILE_DISC and PERCENTILE_CONT

6. IIF function in TSQL.

5. TSQL Analytic Functions LEAD and LAG

4.  OVER clause with ROWS PRECEDING and FOLLOWING in TSQL 2012

3.  OFFSET / FETCH in a SELECT statement

2.  LAST_VALUE and FIRST_VALUE

1.  Cumulative Distribution Function (CDF) or as it is known in TSQL CUME_DIST()

Whats your favorite?