How to STOP the Usage of SELECT * For Views? – Interview Question of the Week #193

Question: How to STOP the Usage of SELECT * For Views?

Answer: Last week was a very interesting week, during the recent Comprehensive Database Performance Health Check, we identified a query which was running very slow for our customer.

After careful investigation, we figured out that the customer was using a view which had many columns in it. One of the developers had written a query over that view and again he was retrieving many other columns using further SELECT *.

A little bit later we fixed the performance issue by removing unnecessary columns from the query which were written poorly with SELECT *. Once the query started to work fine, the developer asked me the following question:

“Is there any way to stop the users from using “SELECT *” on a view? I want users to explicitly type out the column names. Is it even possible?”

Well. As you know “SELECT *” is one of the bad practices that should be avoided. There may be many solutions to stop usage of “SELECT *” but one easy solution I know is to make use of VIEW.

Let us create the following table.

USE tempdb
GO
CREATE TABLE customer_master
(
customer_id INT,
customer_name VARCHAR(100),
dob DATETIME
);

INSERT INTO customer_master (customer_id,customer_name,dob)
SELECT 10001,'Johnson','1992-10-19' UNION ALL
SELECT 10002,'Peter','1957-03-22' UNION ALL
SELECT 10003,'Clara','1981-12-15'
GO

Now create a view with an additional derived column which will throw an error

CREATE VIEW customer_master_view
AS
SELECT customer_id,customer_name,dob, <span style="color: #ff0000;">'a'+100 AS error</span>
FROM customer_master

Note that the expression ‘a’+100 is not valid and will throw an error

Now if you apply SELECT * on this view

SELECT *
FROM customer_master_view;

You will get the following error

Msg 245, Level 16, State 1, Line 185
Conversion failed when converting the varchar value ‘a’ to data type int.

So the only way to query this view is to explicitly type out the column names and exclude derived column

SELECT customer_id,customer_name,dob
FROM customer_master_view;

Now you will get the required results.

I believe it is a very neat trick which worked out great in this case. If you have any alternative trick, please a comment and I will be happy to publish the same with due credit to you.

Important note: In order to make this possible, Database Administrator should GRANT SELECT permission on the VIEW and not on the table.

Reference: Pinal Dave (https://blog.sqlauthority.com)