Feeds:
Posts
Comments

Archive for the ‘Transact-SQL’ Category

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 »

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.

Lets view the decode in Oracle.

Select DECODE(PoleDirection, 'North','N', 'South','S', 'East','E','WEST','W', 'Not Applicable') AS [Poles]
FROM TablePoles

Lets view the Equivalent Code in SQL Server.

Declare @PoleDirection as varchar(10)
Set @PoleDirection= 'North'

SELECT CASE WHEN @PoleDirection = 'North' THEN 'N'
WHEN @PoleDirection = 'South' THEN 'S'
WHEN @PoleDirection = 'East' THEN 'E'
WHEN @PoleDirection = 'WEST' THEN 'W'
ELSE 'Not Applicable' END AS [Poles]

Decode function in SQL server

Given below is the DECODE function in SQL SERVER.

Create FUNCTION [dbo].[DECODE] (
	@Expression nvarchar(max),
	@String nvarchar(Max))
RETURNS nvarchar(Max)
AS
Begin
Declare @Delimiter as varchar(1)
Declare @ReturnValue as nvarchar(max)
Set @Delimiter=','
Declare @Xml AS XML 
 
Declare @Table TABLE(
    [ID] int Identity(1,1),
	Splitcolumn VARCHAR(MAX)
)   
SET @Xml = cast(('<A>'+replace(@String,@Delimiter,'</A><A>')+'</A>') AS XML)    
INSERT INTO @Table SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)   

IF (Select Count (*) from @Table A Where @Expression=A.[SplitColumn])=1
Begin
	Select top 1 @ReturnValue=B.[Splitcolumn] from @Table A
	Left Join @Table B On A.[ID]<B.[ID]
	Where @Expression=A.[SplitColumn]
	Order By A.[ID]
END
Else
BEGIN
	Select top 1 @ReturnValue=A.[Splitcolumn]  from @Table A
	Order By A.[ID] DESC
END
RETURN @ReturnValue
End
GO

Syntax :

    Decode (expression , search , result [, search , result]... [, default])

Examples 1:

Create table tbl_Sample1
(
 [ID] varchar(6),
 [Employee Name] varchar(50),
 [Performance Evaluation] varchar(10)
)
GO
Insert into tbl_Sample1 values ('STD001','Bob','Excellent')  
Insert into tbl_Sample1 values ('STD002','Alexander','Good')  
Insert into tbl_Sample1  values ('STD003','Hosanna','Fair')  

GO
SELECT [ID],[Employee Name],[Performance Evaluation]
,dbo.Decode( [Performance Evaluation],'Excellent,100,Good,80,Fair,60,Unknown') 
as [Performace Index]
from tbl_Sample1
GO

Examples 2:

Create table tbl_Sample2
(
 [ID] int ,
 [Country] varchar(50),
 [Short Name] varchar(3)
)
GO

Insert into tbl_Sample2 Values (1,'PAKISTAN','PAK')
Insert into tbl_Sample2 Values (2,'UNITED STATES OF AMERICA','USA')
Insert into tbl_Sample2 Values (3,'UNITED KINGDOM','UK')
Insert into tbl_Sample2 Values (4,'UNITED ARAB EMIRATES','UAE')
go

SELECT [ID],[Country],[Short Name]
,dbo.Decode([Short Name],'PAK,RS,USA,USD,UK,GBP,UAE,AED,Not Matched') 
as [Currency]
from tbl_Sample2
GO

Read Full Post »

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

Method 4 : (sys.objects & sys.identity_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.identity_columns B 
On A.[object_id]=B.[object_id]
Where A.type='U'

Method 5 : (sys.tables & sys.identity_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.identity_columns B 
On A.[object_id]=B.[object_id]
Where A.type='U' And is_identity=1

Method 6 : (INFORMATION_SCHEMA.COLUMNS)

Use Adventureworks
GO

;With CTE AS (Select Table_Schema+'.'+Table_Name as [Table_Name],[Column_name] from 
INFORMATION_SCHEMA.COLUMNS)
Select Table_Name
,[Column_name]
,COLUMNPROPERTY(OBJECT_ID(Table_Name),[Column_name],'IsIdentity')AS 'IsIdentity'
from CTE Where 
COLUMNPROPERTY( OBJECT_ID(Table_Name),[Column_name],'IsIdentity')=1

How SQL Server checks the identity of the table

I opened a table having identity column in the design mode and at the same time opened SQL server profile to view the qurey. Given below is the query.

In this query SQL picks a lot of information but what we need to check is identity column and it is available on line # 4.

select col.name, col.column_id, st.name as DT_name, schema_name(st.schema_id) as DT_schema
, col.max_length, col.precision, col.scale, bt.name as BT_name, col.collation_name
, col.is_nullable, col.is_ansi_padded, col.is_rowguidcol, 
col.is_identity
, case when(idc.column_id is null) then null else CONVERT(nvarchar(40)
, idc.seed_value) end, case when(idc.column_id is null) then null else CONVERT(nvarchar(40)
, idc.increment_value) end
, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed, 
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl')) as IsIdNotForRepl
, col.is_replicated, col.is_non_sql_subscribed, col.is_merge_published, 
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name
, schema_name(robj.schema_id) as Rul_schema, col.default_object_id
, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst
, dobj.name as def_name, schema_name(dobj.schema_id) as def_schema
, CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as is_FullTextCol
, col_name(col.object_id, ftc.type_column_id) FT_type_column, ftc.language_id as FT_language_id
, case when(cmc.column_id is null) then null else cmc.definition end as formular
, case when(cmc.column_id is null) then null else cmc.is_persisted end as is_persisted
, defCst.definition, COLUMNPROPERTY(col.object_id, col.name, 'IsDeterministic') as IsDeterministic
, xmlcoll.name as xmlSchema_name, schema_name(xmlcoll.schema_id) as xmlSchema_schema
, col.is_xml_document, col.is_sparse, col.is_column_set
, CONVERT(bit, case when (ISNULL(ftc.statistical_semantics, 0) = 0) then 0 else 1 end) 
as is_StatisticalSemantics, col.is_filestream 
from sys.columns col 
left outer join sys.types st on st.user_type_id = col.user_type_id 
left outer join sys.types bt on bt.user_type_id = col.system_type_id 
left outer join sys.objects robj on robj.object_id = col.rule_object_id 
and robj.type = 'R' 
left outer join sys.objects dobj on dobj.object_id = col.default_object_id
and dobj.type = 'D' 
left outer join sys.default_constraints defCst on defCst.parent_object_id = col.object_id 
and defCst.parent_column_id = col.column_id 
left outer join sys.identity_columns idc on idc.object_id = col.object_id 
and idc.column_id = col.column_id 
left outer join sys.computed_columns cmc on cmc.object_id = col.object_id 
and cmc.column_id = col.column_id 
left outer join sys.fulltext_index_columns ftc on ftc.object_id = col.object_id 
and ftc.column_id = col.column_id 
left outer join sys.xml_schema_collections xmlcoll 
on xmlcoll.xml_Collection_id = col.xml_Collection_id
where col.object_id = 
object_id(N'dbo.tbl_test') 
order by col.column_id

Read Full Post »