How SQL Server Blocks SELECT * Statements

Each of us knows it's a bad idea, but sometimes we do it: we execute a SELECT * statement. There are many drawbacks to this approach:

• You return each column from your table, even the post-add column. Think about what will happen if you add a varchar (MAX) to your query in the future ...
• For the specified query, you cannot define the overwrite nonclustered index to overcome the lookup operator in the execution plan, because you will repeat your data in the additional index ...

Now the question is, how do you block the SELECT * statement? Of course you can do code reviews, you can provide the best mode guidance, but who will eventually pay attention to these? Basically no one--unfortunately this is the sad fact ...

But there is a very simple way to block the SELECT * statement, which is addressed in the technical aspect of the table.

The solution to this problem is simple: Add a computed column that produces a 0 error on your table definition. This method is super simple, but it really works. Let's look at the following table definition:

1--Create a simple table with a computed column, generates 2--a divide by zero exception. 3 CREATE TABLE Foo 4 (5     Col1 INT IDENTITY (1, 1) NOT null PRIMARY KEY, 6     Col2 char (+) NOT NULL, 7     Col3 char (  Not NULL, 8 Developerspain as     (1/0) 9) GO11 – Insert some test data13 insert into Foo VALUES (' A ', ' a '), (' B ', ' B '), (' C ', ' C ') + GO

As you can see, I have added a computed column to perform the addition of zeros. This means that when you query this column, you get an error message-for example, in a SELECT * statement:

1--A SELECT * statement doesn ' t work anymore, ouch ... 2 SELECT * from Foo3 GO

But on the other hand if you specify a query column by name, you do not regret the computed column, and your query executes as expected:

1--This SQL statement works2 SELECT Col1, Col2, Col3 from Foo3 GO

It's pretty good, isn't it?

Summary

At various meetings I often mention: sometimes we just get too complicated! This method of calculating columns is very simple-it certainly requires table schema modification. But the next time you design a new table, remember to use this method.

Thanks for your attention!

Reference article:

www.sqlpassion.at/archive/2015/10/26/how-to-prevent-select-statements/

How SQL Server Blocks SELECT * Statements