Question of the month July 2016 – How does this UPDATE work?

Consider the following dataset

create table #t1(id int, amount money)
create table #t2(id int, tamount money)

insert into #t1(id,amount)
select 1,10 union all
select 2,20 union all
select 3,30 

insert into #t2(id,tamount)
select 1,55 union all
select 2,44

Now consider the following UPDATE statement

update #t1
set
 amount=tamount
from #t2

What do you think the result is? How does this UPDATE work despite not relating the tables #t1 and #t2?

sp_sproc_columns – List out parameters of stored procedure

There are several methods to list out parameters used in the stored procedure. One of them is to make use of System catalogue procedure named SP_SPROC_COLUMNS

Let us create the following stored procedure

use tempdb

GO

create procedure proc_add_customers
(
@customer_name varchar(100),
@dob date,
@address varchar(200),
@contact_no varchar(15)
)
as
insert into customers(customer_name,dob,address,concat_no)
select @customer_name,@dob,@address,@contact_no

Now to know the parameters of this store procedure , use sp_sproc_columns
as shown below

EXEC sp_sproc_columns proc_add_customers

The result is shown in the following picture. Look at the column named COLUMN_NAME

For tables, you have SP_COLUMNS. Similarly, for Stored Procedures, you have SP_SPROC_COLUMNS.

Note : You can also use the same catalogue procedure for User Defined function as well.

Exploring SQL server 2016 T-SQL functions- DECOMPRESS

In the earlier post on  Exploring SQL server 2016 T-SQL functions- COMPRESS we have learnt how to compress the values using COMPRESS function. In this post we will learn how to use DECOMPRESS function to get the original value from the compressed value

Let us create this compressed dataset

 create table #test_compress(data varbinary(max))
 insert into #test_compress(data)
 select compress('test') as compressed_Data

When you select data from this table,

 select data from #test_compress

you get the following result

data
------------------------------------------------------
0x1F8B08000000000004002B492D2E01000C7E7FD804000000

To get the original value, you can use DECOMPRESS function

select
    cast(decompress(data) as varchar(max)) as data
from
    #test_compress

The result is

data
---------------
test

Note : The decompressed data should be converted to varchar datatype to see the value in original format

Other posts on new features of SQL Server 2016
Exploring SQL Server 2016 T-SQL functions – STRING_SPLIT
SQL Server 2016 – Drop If Exists
Exploring SQL server 2016 T-SQL functions- COMPRESS