Question of the month September 2015? – Why is table variable not affected by rollback?

Consider the following code

declare @t table(id int)
begin transaction
insert into @t (id) select 100
rollback transaction
select id from @t

When you execute the above code, the result is 100.

Why is table variable not affected by rollback?





Finding If Date is LastDay of the Month

Pinal Dave, blogger and speaker, posted about SQL SERVER – Finding If Date is LastDay of the Month in which two simple methods are shown to know if a given date is a last day of that month.

Here are two more methods to find the same

Consider the following set of data

create table #temp(dates datetime)

insert into #temp
select ‘20090114’ union all
select ‘20080131’ union all
select ‘20070111’ union all
select ‘20090330’ union all
select ‘20120229’ union all
select ‘20100228’

Method 1 : Add 1 day and see if the month is different. If it is then it is the last day

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

Method 2 : Find the last day for a given date. If the given date is equal to it, then it is a last day

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


Both the methods return the following results

dates                            is_lastday
———————————-  ————-
2009-01-14 00:00:00.000 no
2008-01-31 00:00:00.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



COL_NAME function to know the column name

In SQL Server, there are several methods to get the column names of a table. You can use system views like sys.columns, INFORMATION_SCHEMA.COLUMNS, etc. However did you know that there is a function named COL_NAME that accepts the object id of a table and column number and returns the column name?

Consider the following table

use tempdb
create table test(id int, name varchar(100), dob datetime)

If you want to know the first column name of the table, you can use the following code

select col_name(object_id('test'),1) as column_name;

The result is


Similarly you can get the other column names by using

select col_name(object_id('test'),2) as column_name;
select col_name(object_id('test'),3) as solumn_name;

You get NULL if there is an error or if you do not have permission to view the object

Have you ever used this function? I do not see practical usage of this function but it may avoid querying on the  system views.

Understanding PRINT statement

PRINT statement in SQL Server is used to return user-defined message to a client. A Simple usage is

PRINT 'Hello SQL World'

which prints the string ‘Hello SQL World’

However you need to understand that the return type of PRINT statement is varchar or Unicode depends on the input string. Also if you use other values, they will be implicitly converted to character data type

declare @date datetime
set @date='20151019'
print @date

The result is Oct 19 2015 12:00AM. The result is same as explicitly converting it to varchar

declare @date datetime
set @date='20151019'
select cast(@date as varchar(30)) as date

The result is

Oct 19 2015 12:00AM.

If you use SELECT statement the resultant data type is datetime

declare @date datetime
set @date='20151019'
@date as date

The result is

2015-10-19 00:00:00.000

Consider the following example

declare @m money
set @m=92734.9256
print @m

Result is 92734.93 (adjusted to 2 decimals). See what happens if you just SELECT it

declare @m money
set @m=92734.9256
select @m as money

Result is


See what happens when you convert that value into varchar

declare @m money
set @m=92734.9256
select cast(@m as varchar(30)) as money

Result is


Also a value of datatype that can not be implicitly converted to varchar, cannot be directly used in PRINT statement

declare @t xml
set @t ='<a>sql</a>'
print @t

You get the following error

Msg 257, Level 16, State 3, Line 3

Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.

If you do explicit conversion it works fine

declare @t xml
set @t ='<a>sql</a>'
print cast(@t as nvarchar(100))

The result is <a>sql</a>

All inputs should be either implicitly converted or explicitly converted to character data types. So beware of these and use PRINT statement accordingly