"Select * from Table" VS "Select Cola, Colb, etc. from Table" Interesting Behaviour in SQL Server 2005

select * from table vs select colA, colB, etc. from table interesting behaviour in SQL Server 2005

sp_refreshview to fix the view, or use WITH SCHEMABINDING in the view definition

If a view is not created with the
SCHEMABINDING clause, sp_refreshview
should be run when changes are made to
the objects underlying the view that
affect the definition of the view.
Otherwise, the view might produce
unexpected results when it is queried.

Select * from table OR select id,field1, field2, field3 from table - best practice?

Use select col1, col2, col3 from table instead of select * from table1. This has numerous advantages, as mentioned here and here.

Also see:
http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/26/60271.aspx

Is there a difference between Select * and Select [list each col]

What is the reason not to use select *?

The essence of the quote of not prematurely optimizing is to go for simple and straightforward code and then use a profiler to point out the hot spots, which you can then optimize to be efficient.

When you use select * you're make it impossible to profile, therefore you're not writing clear & straightforward code and you are going against the spirit of the quote. select * is an anti-pattern.

---

So selecting columns is not a premature optimization. A few things off the top of my head ....

1. If you specify columns in a SQL statement, the SQL execution engine will error if that column is removed from the table and the query is executed.
2. You can more easily scan code where that column is being used.
3. You should always write queries to bring back the least amount of information.
4. As others mention if you use ordinal column access you should never use select *
5. If your SQL statement joins tables, select * gives you all columns from all tables in the join

The corollary is that using select * ...

1. The columns used by the application is opaque
2. DBA's and their query profilers are unable to help your application's poor performance
3. The code is more brittle when changes occur
4. Your database and network are suffering because they are bringing back too much data (I/O)
5. Database engine optimizations are minimal as you're bringing back all data regardless (logical).

---

Writing correct SQL is just as easy as writing Select *. So the real lazy person writes proper SQL because they don't want to revisit the code and try to remember what they were doing when they did it. They don't want to explain to the DBA's about every bit of code. They don't want to explain to their clients why the application runs like a dog.

In SQL Server 2005, when does a Select query block Inserts or Updates to the same or other table(s)?

When does a Select query block Inserts or Updates to the same or
other table(s)?

When it holds a lock on a resource that is mutually exclusive with one that the insert or update statement needs.

Under readcommitted isolation level with no additional locking hints then the S locks taken out are typically released as soon as the data is read. For repeatable read or serializable however they will be held until the end of the transaction (statement for a single select not running in an explicit transaction).

serializable will often take out range locks which will cause additional blocking over and above that caused by the holding of locks on the rows and pages actually read.

How to SELECT * but without Column names must be unique in each view

I had gone with this in the end, building off of Madhivanan's suggestion. It's similar to what t-clausen.dk later suggested (thanks for your efforts) though I find the xml path style more elegant than cursors / rank partitions.

The following recreates the MasterView definition when run. All columns in the underlying tables are prepended with the table name, so I can include two similarly named columns in the view by default. This alone solves my original problem, but I also included the "WHERE column_name NOT IN" clause to specifically exclude certain columns that will never be used in the MasterView.

create procedure Utility_RefreshMasterView 
as
begin

    declare @entity_columns varchar(max)
    declare @drop_view_sql varchar(max)
    declare @alter_view_definition_sql varchar(max)

    /* create comma separated string of columns from underlying tables aliased to avoid name collisions */
    select @entity_columns = stuff((
        select ','+table_name+'.['+column_name+'] AS ['+table_name+'_'+column_name+']' 
        from information_schema.columns
        where table_name IN ('entity_1', 'entity_2')
        and column_name not in ('column to exclude 1', 'column to exclude 2')
        for xml path('')), 1, 1, '')


    set @drop_view_sql = 'if exists (select * from sys.views where object_id = object_id(N''[dbo].[MasterView]'')) drop view MasterView'

    set @alter_view_definition_sql = 
    'create view MasterView as select ' + @entity_columns + '
    from entity_1
    inner join entity_2 on entity_2 .id = entity_1.id
    /* other joins follow */'

    exec (@drop_view_sql)
    exec (@alter_view_definition_sql)

end

Sql wildcard: performance overhead?

SELECT * FROM...

and

SELECT every, column, list, ... FROM...

will perform the same because both are an unoptimised scan

The difference is:

• the extra lookup in sys.columns to resolve *
• the contract/signature change when the table schema changes
• inability to create a covering index. In fact, no tuning options at all, really
• have to refresh views needed if non schemabound
• can not index or schemabind a view using *
• ...and other stuff

Other SO questions on the same subject...

• What is the reason not to use select * ?
• Is there a difference betweeen Select * and Select list each col
• SQL Query Question - Select * from view or Select col1,col2…from view
• “select * from table” vs “select colA,colB,etc from table” interesting behaviour in SqlServer2005

Does the number of columns returned affect the speed of a query?

You better avoid SELECT *

• It leads to confusion when you change the table layout.
• It selects unneeded columns, and your data packets get larger.
• The columns can get duplicate names, which is also not good for some applications
• If all the columns you need are covered by an index, SELECT columns will only use this index, while SELECT * will need to visit the table records to get the values you don't need. Also bad for performance.

can I rely on the order of fields in a sql view?

Column order may not be guaranteed or reliable if both of these are true

• the view definition has SELECT * or SELECT tableA.* internally
• any changes are made to the table(s) concerned

You'd need to run sp_refreshview: see this question/answer for potential issues.

Of course, if you have simple SELECT * FROM table in a view, why not just use the table and save some maintenance pain?

Finally, and I have to say it, it isn't recommeded to use SELECT *... :-)

How do you recursively select all child elements under a parent element?

Declare @YourTable Table ([NAME] varchar(50),[ID] varchar(50),[ManagerID] varchar(50))
Insert Into @YourTable Values
 ('John',1,3)
,('Sally',2,3)
,('Paul',3,4)
,('Jane',4,5)
,('Jennifer',5,8)
,('Boss',8,null) 


Declare @Top    int         = 4      --<<  NULL for Full Hier
Declare @Nest   varchar(25) = '|-----'  --<<  Optional: Added for readability

;with cteP as (
      Select Seq  = cast(10000+Row_Number() over (Order by Name) as varchar(500))
            ,ID
            ,ManagerId 
            ,Lvl=1
            ,Name 
      From   @YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(ManagerId ,-1) else ID end
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',10000+Row_Number() over (Order by r.Name)) as varchar(500))
            ,r.ID
            ,r.ManagerId 
            ,p.Lvl+1
            ,r.Name 
      From   @YourTable r
      Join   cteP p on r.ManagerId  = p.ID)
Select A.ID
      ,A.ManagerId 
      ,A.Lvl
      ,Name = Replicate(@Nest,A.Lvl-1) + A.Name
 From  cteP A
 Order By Seq

Returns

ID  ManagerId   Lvl Name
4   5           1   Jane
3   4           2   |-----Paul
1   3           3   |-----|-----John
2   3           3   |-----|-----Sally

---

Related Topics