SQL Server : SELECT all columns to be good or bad in database system

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am going to write about one of the most common mistakes of Database Developers.

“SELECT * ” (SELECT all columns) to be good or bad in a Database System.

I will discuss the pros and cons of “SELECT * ” and what should be our best practice with SELECT all columns.

Don’t go with ” SELECT * “:

• When you are using ” SELECT * “, you are selecting more columns from the database, and your application might not use some of this column. This will create extra cost and load on the database system, and unnecessary data travels across the network.
  

  ---

• When you are using JOIN between two or more tables, and in that query, you use “SELECT * “, guess about two columns with the same name. It will return more columns than you needed and will create a binding problem.
  

  ---

• If you are using “SELECT * “, you may get all columns in arbitrary sequence, and if specified the column name, it will return in the specified order, and you can also refer to this column by numerical index.
  If such a code exists in an application which requires all columns in a predefined order, you have to specify all the columns name in the correct order.

  ---

• When you are using “SELECT *”, you will face performance issue because this is not advisable to apply an index on all the columns of the table.
  

  ---

• When you are using “SELECT *”, you require to create documentation for those columns which are returning and using by an application otherwise, this will create confusion.
  

  ---

• When you are using “SELECT * ” and in the future, someone adds one new column with TEXT data type, you can imagine performance and other errors.
  

  ---

• If you need to select all columns, also I would suggest providing a full list of columns because putting “SELECT *” needs fetching the name of the columns from stored metadata or system information which will impact the query performance.
  

  ---

• Now Imagine that you are using “SELECT *” in all your stored procedure and now you require to find one column name from the text of the stored procedure which is not possible with “SELECT *”.

Why you go with “SELECT *” ?

• The Lazy developers can only go with “SELECT *”.
  

  ---

• If you have special requirements and create a dynamic environment when add or delete column automatically handle by application code. In this particular case, you don’t require to change application and database code, and this will automatically be done in a production environment. In this case, you can use “SELECT *”.
  

  ---

• I didn’t find any other reason to use “SELECT *”.

SQL Server: Basic performance test between SELECT INTO and INSERT INTO SELECT

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am doing a basic performance test between SELECT INTO and INSERT INTO SELECT option of SQL Server.

Database Developers are using these options very frequently for copying data between the tables. But SELECT INTO is little faster than INSERT INTO SELECT option.

Note: I am testing using a very small number of sample records. If you are testing with a huge number of records, you may find a big difference in the performance.

Please check the below examples:

Set Statistics:

1 2	SET STATISTICS IO ON SET STATISTICS TIME ON

Create sample tables:

1 2 3 4 5

CREATE TABLE tbl_MyData (ID INT, Name VARCHAR(10)) CREATE TABLE tbl_Mytable (ID INT, Name VARCHAR(10))   INSERT INTO tbl_MyData VALUES (1,'dbrnd') GO 1000000

Perform INSERT INTO SELECT:

1 2 3

INSERT INTO tbl_Mytable SELECT ID, Name FROM tbl_MyData

Execution time:

1 2	SQL Server Execution Times: CPU time = 16 ms, elapsed time = 37 ms.

Perform SELECT INTO:

1 2 3

SELECT ID, Name INTO tbl_MyTable2 FROM tbl_MyData

Execution time:

1 2	SQL Server Execution Times: CPU time = 16 ms, elapsed time = 21 ms.