Without actually executing the following code, can you guess the result?
select 1.sqlserver
Choose the answer from following
(1) 1.sqlserver
(2) 1
(3) incocrrect syntax near .
(4) The multi-part identifier “1.sqlserver” could not be bound.
COUNT vs COUNT_BIG
Please note that this post is available now at https://exploresql.com/2015/07/22/count-vs-count_big/
Working with functions to Concatenate columns
Pinal Dave, Blogger and Speaker posted about SQL SERVER – Puzzle – Working with functions to Concatenate columns in which he asked to show a method to concatenate columns of different data types that take care of NULL as well
The best way I can think of is to use CONCAT function introduced in version 2012
CREATE TABLE #temp (
emp_name NVARCHAR(200) NOT NULL,
emp_middlename NVARCHAR(200) NULL,
emp_lastname NVARCHAR(200) NOT NULL
);
INSERT INTO #temp VALUES( ‘SQL’, NULL, ‘Authority’ );
INSERT INTO #temp VALUES( ‘SQL’, ‘Server’, ‘Authority’ );
SELECT concat(emp_name ,emp_middlename ,emp_lastname) AS Result
FROM #temp;
DROP TABLE #temp;
The result is
Result
----------------------
SQLAuthority
SQLServerAuthority
Note : CONCAT will also take care different datatypes and you do not need to explicitely convert the values.
SELECT CONCAT(1,'TEST')
The result is 1TEST
GO
UNPIVOT trick using VALUES clause
Pinal Dave, Blogger and Speaker, posted about SQL SERVER – Creating Dataset Using VALUES Clause Without Creating A Table Using VALUES clause you can also do what UNPIVOT does in SQL Server
Consider the following data set
create table #t (id int , amount1 decimal(12,2), amount2 decimal(12,2), amount3 decimal(12,2)) insert into #t select 1, 200, 267.40, 188.90
insert into #t select 2, 455.75, 310.25, 4000
insert into #t select 3, 200, 1900, 450
insert into #t select 4, 230.80, 36000, 2100
insert into #t select 5, 1200, 900, 2500.10
If you want to find out the maximum value available in the columns amount1, amount2 and amount3 for each id, you can use UNPIVOT operator
select id,max(amount) as max_amount from #t
unpivot (amount for column_name in (amount1,amount2,amount3)) as t
group by id
The result is
Id max_amount
---- --------------------
1 267.40
2 4000.00
3 1900.00
4 36000.00
5 2500.00
The same can be done using VALUES clause from version 2008 onwards
select id,
(
select max(amount) from (values(amount1),(amount2),(amount3)) as t (amount)
) as max_amount
from #t
The result is
Id max_amount
---- --------------------
1 267.40
2 4000.00
3 1900.00
4 36000.00
5 2500.00
Madhivanan's SQL blog 