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 errormessage.

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.

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 EXECUTETRUNCATE 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.

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

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

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

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.

I was reading a function in Oracle namely “Decode”. It is very handy when it comes to search a variable and return the result based on search. It has been asked on multiple forums, what the EQUIVALENT of DECODE IN SQL SERVER is? Basically, we can achieve the same in SQL Server using Case and IIF statements.

Column identity is something that we come across every now and then. We use it to generate an auto number column in any table.
Today, we will discuss multiple ways to find identity column in the entire user tables.

Method 1 : (sys.columns)

Use Adventureworks
GO
Select Object_Name([object_id]) as [Table Name]
,[name] as [Column Name]
,is_identity
from sys.columns
Where is_identity=1 And Objectproperty(object_id,'IsUserTable')=1

Method 2 : (sys.objects & sys.all_columns)

Use Adventureworks
GO
Select A.[name] as [Table Name]
,B.[name] as [Column Name]
,B.is_identity
from sys.objects A
Inner Join sys.all_columns B
On A.[object_id]=B.[object_id]
Where A.type='U' And is_identity=1

Method 3 : (sys.tables & sys.all_columns)

Use Adventureworks
GO
Select A.[name] as [Table Name]
,B.[name] as [Column Name]
,B.is_identity
from sys.tables A
Inner Join sys.all_columns B
On A.[object_id]=B.[object_id]
Where A.type='U' And is_identity=1

Blog Stats

Follow Blog via Email

Disclaimer

This blog/website is a personal blog/website and all articles, postings and opinions contained herein are my own. The contents of this blog/website are not intended to defame, purge or humiliate anyone should they decide to act upon or reuse any information provided by me. Comments left by any independent reader are the sole responsibility of that person. Should you identify any content that is harmful, malicious, sensitive or unnecessary, please contact me via email (imran@raresql.com) so I may rectify the problem.