Feeds:
Posts
Comments

Posts Tagged ‘Transact-SQL Enhancements’

SQL Server 2016 shipped with a lot of TSQL Enhancements and one of them is DROP IF EXISTS. It actually minimizes the size of the script which you can use to drop any object (AGGREGATE, ASSEMBLY, COLUMN, CONSTRAINT, DATABASE, DEFAULT, FUNCTION, INDEX, PROCEDURE, ROLE, RULE, SCHEMA, SECURITY POLICY, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, USER, and VIEW). In other words, you DO NOT need to write long IF EXISTS Statements anymore to drop any object.
The beauty of this TSQL is that if it fails (in case, if object does not exist), it DOES NOT give you error message.

Lets me create a sample table to demonstrate how it works but as I mentioned earlier you can use this script to drop any object.

SAMPLE:


USE tempdb
GO
--Create Sample table
CREATE TABLE dbo.tbl_Sample
(
[ID] INT,
[Name] NVARCHAR(50)
)
GO

OLD METHOD:
Given below methods are compatible with SQL Server 2005 and above.

--Method 1
USE tempdb
GO
IF OBJECT_ID('dbo.tbl_Sample', 'U') IS NOT NULL
DROP TABLE dbo.tbl_Sample
GO

--Method 2
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tbl_Sample')
DROP TABLE dbo.tbl_Sample
GO

NEW METHOD:
Given below method is compatible with SQL Server 2016 and above.

USE tempdb
GO
DROP TABLE IF EXISTS tbl_Sample;
GO

Reference : MSDN

Advertisements

Read Full Post »

Truncate table is always my preference when I need to delete all the records from any table. The reason for the preference is, actually Truncate Table is faster than delete and it takes less system and transaction log resources. However, there is a problem with TRUNCATE TABLE and that is when you EXECUTE TRUNCATE TABLE, it actually deletes all the records in the table and you cannot specify any criteria (WHERE CLAUSE) for deletion like Delete.

The Good News is in SQL Server 2016 you can specify the partition you want to delete using Truncate Table.

Let me create a sample partition table and insert some sample data in that table and then demonstrate how it works in few easy steps.

Sample :


USE master
GO
ALTER DATABASE [Sample_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
--Drop Sample database
DROP DATABASE [Sample_DB]
GO
--Create Sample database
CREATE DATABASE Sample_DB
GO

-- Create Filegroups
ALTER DATABASE Sample_DB ADD FILEGROUP [2014]
GO
ALTER DATABASE Sample_DB ADD FILEGROUP [2015]
GO

--Create one file for each filegroup and
--so that you can store partition data

ALTER DATABASE Sample_DB
  ADD FILE
  (NAME = N'FG_2014',
  FILENAME = N'C:\Data\FG_2014.ndf'
  )
  TO FILEGROUP [2014]
GO  

ALTER DATABASE Sample_DB
  ADD FILE
  (NAME = N'FG_2015',
  FILENAME = N'C:\Data\FG_2015.ndf'
  )
  TO FILEGROUP [2015]
GO  

USE Sample_DB
GO

-- Drop the partition function if it exists
IF( EXISTS( SELECT * FROM sys.partition_functions
WHERE name = 'OrderDateRangePFN' ) )
BEGIN
DROP PARTITION FUNCTION OrderDateRangePFN
END
GO

--Create partition function
CREATE PARTITION FUNCTION OrderDateRangePFN(DATETIME)  AS
  RANGE LEFT FOR VALUES
  ('2014-12-31',
  '2015-12-31'
  )
GO

-- Drop the partition Scheme if it exists
IF( EXISTS( SELECT * FROM sys.partition_schemes
WHERE name = 'OrderDateRangePScheme' ) )
BEGIN
DROP PARTITION SCHEME OrderDateRangePScheme
END
GO

--Create partition scheme
CREATE PARTITION SCHEME OrderDateRangePScheme AS
  PARTITION OrderDateRangePFN  TO
  ([2014],
   [2015],
   [PRIMARY]  )
GO

-- Drop sample table if exists
IF( OBJECT_ID( 'tbl_Sample', 'U' ) IS NOT NULL )
BEGIN
   DROP TABLE tbl_Sample
END
GO

-- Create sample table
CREATE TABLE tbl_Sample
(OrderID INT NOT NULL,
[OrderDate] DATETIME)
ON OrderDateRangePScheme ([OrderDate]);
GO

-- Insert some sample data
insert into tbl_Sample Values (1,'2014-01-01')
insert into tbl_Sample Values (2,'2014-02-01')
insert into tbl_Sample Values (3,'2014-03-01')
insert into tbl_Sample Values (4,'2015-01-01')
insert into tbl_Sample Values (5,'2015-02-01')
insert into tbl_Sample Values (6,'2015-03-01')
insert into tbl_Sample Values (7,'2015-04-01')
insert into tbl_Sample Values (8,'2016-01-01')
insert into tbl_Sample Values (9,'2016-02-01')
GO

SELECT * FROM tbl_Sample
GO

Truncate Table 1.1

Step 1 :
Lets find out if the data exists in the partitions properly. Given below is the script.

USE Sample_DB
GO
-- Check if data exists in the partition properly
SELECT OBJECT_NAME(OBJECT_ID) AS [Table Name]
, partition_number AS [Partition Number]
, rows AS [Number of rows]
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='tbl_Sample';
GO

Truncate Table 1.2

Step 2 :
Lets TRUNCATE TABLE the data of PARTITION 2 in the sample table. Given below is the script.

USE Sample_DB
GO
TRUNCATE TABLE tbl_Sample
WITH (PARTITIONS (2));
GO

Truncate Table 1.3

Step 3 :
Lets find out if the data of partition 2 has been deleted in the sample table or as usual TRUNCATE TABLE has deleted all the data in the table. Given below is the script.

USE Sample_DB
GO
-- Check if data exists in the partition properly
SELECT OBJECT_NAME(OBJECT_ID) AS [Table Name]
, partition_number AS [Partition Number]
, rows AS [Number of rows]
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='tbl_Sample';
GO

Truncate Table 1.4

Wow, as expected only partition 2 data has been deleted from sample data. This feature is very helpful for the DBAs handling lots of partition in a single table and if they want to delete any particular partition(s) data quickly.

Reference : MSDN

Read Full Post »