Understanding Single quotes

Often I see that newbies struggle to understand how single quotes work in SQL Server. The problem happens when you try to add text data which has single quote as part of it to a table.

I have seen newbies worrying why the following doesn’t work

SELECT columns from mytable where col ='Lifco's'

When you specify a value which has single quote, you need to double it

SELECT columns from mytable where col ='Lifco''s'

The following may be helpful (Run and see the result)

SELECT '','''','''''','''''''',''''''''''

When you use a static sql and express a value in a single quote then first and last single quotes specify that the value is a string. Then within those single quotes every double single quotes represent a single single quote

When you use a Dynamic sql then first and last single quotes specify that it is a dynamic sql. Then within those single quotes every double single quotes specify that it is a string.Then within those single quotes every four single quotes represent a single single quote

Execute the following code and see the result

EXEC
(
'SELECT 
'''','''''''','''''''''''','''''''''''''''',
'''''''''''''''''''''
)

Fun with GO

In SQL Server, GO is the default Batch Separator. Here is a Fun when you use GO as Stored Procedure name

Create the following stored Procedure with the name GO

CREATE PROCEDURE GO
(
    @I INT
)
AS
    SELECT @I AS number

The procedure is created and let us try to execute with the following statement

GO 2

What we see is

Command(s) completed successfully

Because it becomes a syntax to execute a batch for two times

EXECUTE GO 2 

Result is 2

Now I let you to try running the following statements to understand what happens

Execution 1

[GO] 2

Execution 2

[GO] 2
GO 2

Execution 3

[GO] 2
GO
EXECUTE GO 2
GO

Execution 4

[GO] 2
GO 2
GO 2

Execution 5

[GO] 2
GO
GO

Execution 6

EXECUTE GO 2
EXECUTE GO 2
GO 2

Execution 7

[GO] 2
GO
GO 2
GO 2
EXECUTE GO 2
GO
GO 2
GO 2
GO 2 
Execution 8
GO;

Execution 9

GO;2

Execution 10

GO 2;

DROP IF EXISTS with multiple tables

SQL Server 2016 has introduced new DDL statement DROP IF EXISTS which will drop the table if exists.

In my earlier post on Dropping multiple tables with one DROP statement I have shown the method on how you can use single DROP statement to drop multiple tables

In the similar way, the new DDL also supports dropping multiple tables if they exists

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

DROP TABLE IF EXISTS #t1, #t2, #t3

 

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