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
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

Continue reading Preventing the usage of SELECT *