Category: TSQL 2012

By Posted on Posted in Analytic TSQL, SQL 2012, TSQL, TSQL 2012 No Comments Tagged with , , ,

After the last post on Cumulative Distribution Function (CDF) or as it is known in TSQL CUME_DIST(), I realized that although I showed how to use it, I didn’t really explain what it means, or when to use it.  That is where this example comes in. First lets take an example that generates simulated dice rolls.  What are the odds …

Cumulative Distribution Function (CDF) – Analyzing the Roll of Dice with TSQL Read more »

By Posted on Posted in SQL 2012, TSQL, TSQL 2012 No Comments Tagged with , ,

Percent rank is defined as the number of values that are the same or less than the current value divided by one less than the number of values. Percent rank is different than PERCENTILE, stay tuned for PERCENTILE_DISC and PERCENTILE_CONT which are different from PERCENT_RANK. For this example I will be using the same revenue table in the sample database …

More TSQL Analytic Functions – PERCENT_RANK Read more »

By Posted on Posted in 70-461 Training, TSQL 2012 No Comments Tagged with , , , , , ,

Here is a quick video training on how to use the T-SQL EOMONTH function on SQL Server. This was originally part of my free SQL query training for the 70-461 certification exam. The EOMONTH function returns the last day of a given month, or a future or past month by using the second parameter of offset. EOMONTH was introduced in SQL Server …

Using The TSQL EOMONTH Function Read more »

By Posted on Posted in Academy, DBA, SQL 2012, TSQL 2012 1 Comment Tagged with

The new Joes2Pros Academy has just launched. The Academy provides an interactive classroom experience that can be accessed on your own time as you need it. This site has high quality content, quizzes and exams. Students can interact with other students and instructors to get the best experience out of the Joes2Pros Academy. Courses offered in the following areas: SQL Server Administration SQL …

Introducing the Joes2Pros Academy Read more »

By Posted on Posted in 70-461 Training, SQL 2008, SQL 2012, SQL 2014, TSQL, TSQL 2012 1 Comment Tagged with , , , , , , , ,

SQL Server 2012 adds a new function called DateFromParts.  This new function simplifies the creating of a DATE type in TSQL over the older ways of doing it. The information here has been extracted from my SQL Saturday presentation on Whats New in TSQL 2012. Pre-SQL 2012 First lets take a look at how you would do the equivalent to DateFromParts before SQL …

DATEFROMPARTS function in TSQL Read more »

By Posted on Posted in Analytic TSQL, SQL 2012, TSQL, TSQL 2012 22 Comments Tagged with , , , , , , , , , , ,

SQL Server 2012 adds many new features to Transact SQL (T-SQL).  One of my favorites is the Rows and Range enhancements to the over clause. These enhancements are often times referred to as the windowing functions. Want to learn more about the windowing functions? Check out this aggregation class that I have created: Overview:ROWS PRECEDING, FOLLOWING, UNBOUNDED, refers to the …

Rows and Range, Preceding and Following Read more »

By Posted on Posted in SQL 2012, SQL Saturday, TSQL, TSQL 2012 2 Comments Tagged with , , , , , , , ,

SQL Server 2012 adds a new stored procedure called sp_describe_first_result_set.  This new procedure returns metadata for the result set returned from a query. The metadata is information about what the results will look like.  sp_describe_first_result_set is an alternative to sp_columns, and appears to perform much better than sp_columns. The information here has been extracted from my SQL Saturday presentation on Whats New in TSQL …

T-SQL 2012 Procedure sp_describe_first_result_set Read more »

By Posted on Posted in SQL 2012, TSQL, TSQL 2012 No Comments Tagged with , , , , , , ,

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: and another anniversary query (which doesn’t actually run): 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 …

Happy Birthday SQL Server 2012 Read more »

By Posted on Posted in TSQL, TSQL 2012 No Comments Tagged with , , , , , , ,

SQL Server 2012 introduces a new T-SQL object used to generate numbers, a SEQUENCE, similar to an identity column. You can think of a sequence as a take a number object, you just ask it to give you a number, and you get a number. When creating a SEQUENCE you need to specify the following: Start Increment Min Value, Max Value Cycle / …

Using a SEQUENCE object to generate letters. Read more »

By Posted on Posted in SQL 2012, TSQL 2012 No Comments Tagged with , , , , , , ,

SQL Server 2012 introduces the IIF statement. It is very similar to the IIF statement in MS Access. I have extracted the IIF part of my presentation on Whats new in SQL Server 2012, and turned it into a video with a demo of how to use the IIF statement. The IIF statement is a quick shortcut to simple CASE …

SQL Server 2012 IIF Statement Read more »