Consider the following statements
Statement 1:
SELECT COALESCE('TEST',100.25)
Statement 2:
SELECT ISNULL('TEST',100.25)
One of the above will throw an error. What is it and why?
Shortest method to extract decimal part
One of my friends told me that in an interview he was asked “What is the shortest method to extract only decimal part from a decimal number?”
One method I know is to find the remainder by dividing by 1 using modulus operator
declare @number decimal(12,2)
set @number=887.45
select @number as number,@number%1 as decimal_part
The result is
number decimal_part
----------- --------------------
887.45 0.45
Preventing the usage of SELECT *
One of my friends asked “Is it possible to prevent users from using SELECT * from a table in the queries?” Well. The one way I can think of is to create a view that has all the columns from the table and add an extra column as an invalid derived expression.
Let us consider the following table
create table employee(
emp_id char(10),
emp_name varchar(100),
dob datetime,
address varchar(100),
phone varchar(20)
)
insert into employee(emp_id,emp_name,dob,address,phone)
select 'EMP0000001','Madhivanan','20000101','Chennai,India','+91299200200'
You can now use * in the SELECT statement
select * from employee
To prevent this create the following view
create view emp_view
as
select *,cast('@@@' as datetime) as error_col from employee
Now you can not use SELECT * on this view
select * from emp_view
The above results to
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
So you need to explicitly type out the required column names to get the data.
select emp_id,emp_name,dob,address,phone from emp_view
Madhivanan's SQL blog 