Dropping multiple tables with one DROP statement

Do you know that you can drop multiple tables using a sing DROP statement?

Assume that in your code or procedure you use many temporary tables and at the end you want to drop all of them. One method is to drop them one by one

Assume the following temporary tables

create table #t1(id int);
create table #t2(id int);
create table #t3(id int);

Usual method of dropping them is

drop table #t1
drop table #t1
drop table #t1

However, simply you can use single DROP statement with each table names seperated by comma

drop table #t1,#t2,#t3

How many of you know this?

Shortest method to extract decimal value

One of the questions asked in a forum. ¬†“What are the different methods to extract decimal part from a number?”.

There can be many methods. Two simple methods are as shown below

1 CAST to INT and subtract from original number

declare @amount decimal(12,2)
set @amount=1200.75
select @amount-cast(@amount as int) as decimal_value

2 Use Modulus operator

declare @amount decimal(12,2)
set @amount=1200.75
select @amount % 1 as decimal_value

The result is

decimal_value
---------------------------------------
0.75

Using modulus operator is the shortest method of all methods that you can use

Finding If Date is LastDay of the Month – Part 2

In my earlier post I showed two methods on how to know if the given date is the last day of month. There are two more methods to know the same which I provide in this post

Consider the following data set

create table #temp(dates datetime)

insert into #temp
select '20090114' union all
select '20080131 10:10:10' union all
select '20070111' union all
select '20090330' union all
select '20120229' union all
select '20100228'

Method 1 : Use EOMONTH function (works from version 2012 onwards)

select 
	dates,
	case 
		when cast(dates as date)=eomonth(dates) then 
			'yes' 
		else 
			'no' 
	end 
		as is_lastday
from 
	#temp

Method 2 : Subtract day part from original date and add one month

select 
	dates,
	case 
		when dates=dateadd(month,1,dates-day(dates)) then 
			'yes' 
		else 'no' 
	end 
		as is_lastday
from 
	#temp

The result of the above two queries are

dates                   is_lastday
----------------------- ----------
2009-01-14 00:00:00.000 no
2008-01-31 10:10:10.000 yes
2007-01-11 00:00:00.000 no
2009-03-30 00:00:00.000 no
2012-02-29 00:00:00.000 yes
2010-02-28 00:00:00.000 yes

Happy Holidays in T-SQL style

Want to know the result of the following query? All you need to do is goto SSMS, set the result mode to Text (Press CTRL+T) and then execute the following code

set nocount on select space(17-len(replicate(char(182),no)))+ replicate(char(182),no*2-1)from (select top 10 row_number() over (order by (select 1)) as no from (select 0 as no union all select 0 union all select 0) as t1 cross join (select 0 as no union all select 0 union all select 0) as t2) as t union all select space(14)+replicate(char(124),5) union all select space(10)+cast(0x486170707920486F6C6964617973 as varchar(100))

Different methods to know structure of a table

There are many methods to know the structure of a table. Here are some of them

1 In Query Analyser type the name of the table, highlight it and press Alt+F1

2 Generate SQL Script option from Enterprise Manager/Management Studio

3 Use sp_help system stored procedure

EXEC sp_help 'table_name'

4 Use sp_columns system stored procedure

EXEC sp_columns 'table_name'

5 Use information_schema.columns catelogue view

SELECT 
	* 
FROM 
	INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='table_name'

Find out tables with Primary key

One of the questions asked in the forum is “How do I find out the tables that have primary key defined?”

Well. There can be several methods. These are two among them

Let us create the following tables in tempdb database

use tempdb

create table test1
(
id int primary key,
name varchar(100)
)
GO
create table test2
(
id int ,
name varchar(100)
)

Method 1 : Query on INFORMATION_SCHEMA.TABLE_CONSTRAINTS system catalog view

SELECT
	table_name
FROM
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
	CONSTRAINT_TYPE='PRIMARY KEY'

Method 2 : Query on SYS.TABLES system table

SELECT
	name AS table_name
FROM
	SYS.TABLES
WHERE
	OBJECTPROPERTY(OBJECT_ID,'TABLEHASPRIMARYKEY') = 1

Both the queries return the following result

table_name
--------------
test1

Question of the month November 2016 – Why does semicolon followed by GO give error?

You know that specifying a number after GO statement will execute the above statement by as many number as specified in it.

Example

select 1
go 2

As you see you will get two result sets with value 1

But when you put semicolon after go 2

select 1
go 2;

You get the following error

A fatal scripting error occurred.
Incorrect syntax was encountered while parsing go.

Why does it give error?

NULL – Typed and Non-Typed

We know that NULL denotes the absence of value or NULL is known as undefined. Direct use of NULL in some cases may work or throw an error.

Consider the following example 1

SELECT SUM(NULL) as No

It throws an error

Msg 8117, Level 16, State 1, Line 10
Operand data type NULL is invalid for sum operator.

Consider another example 2

declare @no int
set @no=null 
SELECT SUM(@no) as No

The result is

No
-----------
NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

Do you know why the first example failed whereas second example worked well? Because in example 1 the NULL does not have any datatype and SUM function does not recognize it and throws an error. In second example NULL is assigned to a variable of type INT and SUM function worked well.

In first example, the NULL is Non-Typed and in the second example the NULL is Typed (INT datatype)

You can also do implicit conversion to make Non-Typed NULL into Typed NULL as shown below

SELECT SUM(NULL+1) as No

The result is

No
-----------
NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

How to find out datatype of an expression?

Well. You have an expression that produces a result and you want to know the datatype of it. How will you do it?

For example you have the following expression 10+’20’

select 10+'20'

The result is 30

But you do not know if the result is datatype of INT or VARCHAR

You can find out the resultant datatype in two methods

1 Create a table based on expression

select 10+'20' as data into #t

Now see the structure of the column and find the datatype

EXEC TEMPDB..SP_COLUMNS '#t'

The result is

dt1

2 Use SQL_VARIANT_PROPERTY system function

SELECT SQL_VARIANT_PROPERTY(10+'20','basetype') as datatype

The result is

datatype
-----------------
INT

You can also use a variable in place of an expression to find out it’s datatype