Question of the month July 2015 – Find out the result of select 1.sqlserver

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.

Advertisements

COUNT vs COUNT_BIG

As you know COUNT function is used to count number of rows from a table or a derived result set. But did you know that there is another function named COUNT_BIG?

The return type of COUNT_BIG function is BIGINT whereas return type of COUNT is INT. So if you have really huge dataset you can use COUNT_BIG function to avoid overflow error

Another difference is that when you use COUNT function in a view, you can’t index it

Consider the following objects

create table test
(
prod_id int,
qty int,
price decimal(12,2),
sales_date datetime
)

Create this view

create view [dbo].[test_view] with schemabinding
as
select year(sales_date) as year, count(*) as total_volumne from dbo.test
group by year(sales_date)

Create clustered index on year column

create unique clustered index test_index on test_view (year)

You get the following error

Msg 10136, Level 16, State 1, Line 3
Cannot create index on view "tempdb.dbo.test_view" because it uses the aggregate COUNT. Use COUNT_BIG instead.

Now alter the view and use COUNT_BIG function

alter view [dbo].[test_view] with schemabinding
as
select year(sales_date) as year, count_big(*) as total_volumne from dbo.test
group by year(sales_date)

You will be able to create clustered index now

create unique clustered index test_index on test_view (year)

You need to keep this in mind whenever you use use COUNT function in a view

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