Why the Beatles Broke up - a 1976 Research Paper

Long ago, in a high school, far, far away, I researched and wrote a paper on why the Beatles broke up. Given Paul McCartney's recent comments on why he felt that Beatles broke up, I thought I would add my original research on the topic.  You can find a copy of the paper here:  Why the Beatles Broke up - May 1976.

Heaps - would it be faster than a table with a clustered index?

In design, we almost always automatically setup a clustered index. But should we?

The site SQLPerformance decided to take the challenge and find out if there are times when a heap just might be faster than a table with a clustered index. You can find all the details here:  Is a RID Lookup faster than a Key Lookup? [SQL Performance].

Supported Features - 2016 SQL Server

Microsoft published the feature list for the various editions of SQL Server 2016.

Some of the best news is that the Query Store will be included in all versions.  MS also killed off the BI edition.  The BI edition was always a strange edition. It sounded good, but it was missing many of the key features one would want with a BI platform - those were only included with the Enterprise Edition.

And, as we earlier have commented, the developer edition is now free (see Free SQL Server Developer Edition - insane!).

Compare Editions:  Compare SQL Server 2016 SP1 editions1
( high level overview of scale, memory, cores, etc.)

You can find the full listing of supported features here:  Features Supported by the Editions of SQL Server 2016.

Duplicates: Discover and Delete with T-SQL

Occasionally, you need to locate and delete exact duplicate records in a SQL Server table. Prior to the introduction of the Row_Number function the process required a somewhat complex process often using the GROUP BY and  HAVING commands along with temporary staging tables.  Microsoft support has one possible approach at the page:  How to remove duplicate rows from a table in SQL Server.

Fortunately, with Row_Number, we now have a more elegant approach.

Initial Discovery

The first process is to establish the correct discovery query, using Row_Number.

       
   select [Column_1],[Column_2],[Column_3],[Column_4], [Column_etc]
       ROW_NUMBER() over    
        ( partition by  [Column_1],[Column_2],[Column_3],[Column_4], [Column_etc]
              order by  [Column_1],[Column_2],[Column_3],[Column_4], [Column_etc]
         ) RowNumber
   from [dbo].[TargetTable];       
 

Here we are using the Row_Number function with both the partition by and order by commands.  All columns are listed, and listed three (3) times, in the same order.

       
   [Column_1],[Column_2],[Column_3],[Column_4], [Column_etc]
        

The Duplicate Record List

Modifying the above query, we can filter down to just the offending duplicates. Essentially, we are just reconfiguring the initial query as a sub-query and then adding a WHERE clause. You always want to test the query before deleting records, and this is a simple way to test your sub-query format.

       
   select *
   from (
    select [Column_1],[Column_2],[Column_3],[Column_4], [Column_etc]
        ROW_NUMBER() over    
         ( partition by  [Column_1],[Column_2],[Column_3],[Column_4], [Column_etc]
               order by  [Column_1],[Column_2],[Column_3],[Column_4], [Column_etc]
          ) RowNumber
    from [dbo].[TargetTable]
    ) as p
    where p.RowNumber > 1;       
 

Delete The offending Duplicates

Simply reconfigure your sub-query into a delete

       
   delete from p 
   from (
    select [Column_1],[Column_2],[Column_3],[Column_4], [Column_etc]
        ROW_NUMBER() over    
         ( partition by  [Column_1],[Column_2],[Column_3],[Column_4], [Column_etc]
               order by  [Column_1],[Column_2],[Column_3],[Column_4], [Column_etc]
          ) RowNumber
    from [dbo].[TargetTable]
    ) as p
    where RowNumber > 1 ;      
 

SQL Server 2016 - June 1st release date

Today, on the SQL Server Blog, the team announced that their target date for general availability (GA) will be June 1st, 2016.

Over the past year, Microsoft has made some major enhancements to SQL Server 2016, many of which you find here in our blog. PolyBase is one of the biggest, allowing real-time, T-SQL based access to all of your data stored in Hadoop or Azure. They have completely rebuilt Reporting Services (SSRS) in the image of Power BI 2.0, and made significant upgrades to Master Data Services (MDS). A good starting point is our article on Redmond Magazine:
SQL Server 2016 Preview Reveals Path to Azure and Big Data.

You can find more detail about the pending release at Get ready, SQL Server 2016 coming on June 1st