Wednesday, November 30, 2016

Big Announcements in SQL Server 2016

As of SQL Server 2016 SP1, which was shipped this month, from a programmability perspective there is no difference between the editions anymore. All programmability features will be available in all editions from LocalDB to Enterprise edition.

Read more at http://itknowledgeexchange.techtarget.com/sql-server/big-announcements-in-sql-server-2016/

Tuesday, November 29, 2016

Redgate Announces New SQL Server Database Cloning Tool

Redgate, a Cambridge-UK based software company that develops SQL Server tools, has launched the beta of its new database cloning tool called SQL Clone that enables databases to be cloned quickly, while saving up to 99% of disk space.
According to the company, the new technology resolves a long-standing issue in software development. Typically, this involves database administrators having to provision a copy of the database for each developer request, which takes up valuable time as well as disk space. The result is that teams end up working on outdated versions of the database in a shared environment, rather than having the freedom to work on isolated local versions that can be created and deleted rapidly.
Read more http://www.dbta.com/Editorial/News-Flashes/Redgate-Announces-New-SQL-Server-Database-Cloning-Tool-114985.aspx

Friday, November 18, 2016

CREATE OR ALTER In One Statment

When ever we execute stored procedure script, we always drop the existing one and recreate it.

IF EXISTS (SELECT * FROM sys.procedures WHERE Name = 'Test')
BEGIN
DROP PROC Test
END
GO

CREATE PROCEDURE Test
AS
BEGIN
       SELECT 1
END

However, with SQL Server 2016 SP1, now there is easy way of doing with one statement.

CREATE OR ALTER PROCEDURE Test
AS
BEGIN
         SELECT 1
END


Obvious advantage is this is much cleaner now. This feature is available for Functions, Triggers and Views as well. 

There is an another important benefits out of this.

If you check the stored procedure stats for the drop and create method by running the following query, you will end up with below results. 

SELECT name,create_date,modify_date
 FROM sys.procedures WHERE Name = 'Test'


In this, both the create and the modify dates are same. However, for the new method, now it has a different modify date which means you can track your objects much better. 






Thursday, November 17, 2016

SQL Server 2016 Service Pack 1 (SP1) released

Microsoft has announced the availability of SQL Server 2016 Service Pack 1 (SP1). With SQL Server 2016 SP1 key improvements were made allowing a consistent programmability surface area for developers and organizations across SQL Server editions.

2. Now express edition has the lot of features where it was limited to enterprise before. Partitioning, Compression ad In Memory OLTP will be available now for Standard and Express as well.




2. DBCC CLONEDATABASE is added which was missing in SQL Server 2016 RTM and was with SQL Server 2014.

3. Tempdb supportability – A new Errorlog message indicating the number of tempdb files and notifying different size/autogrowth of tempdb data files at server startup.

4, DROP TABLE support for replication – DROP TABLE DDL support for replication to allow replication articles to be dropped.

For all the details, please refer to https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/