Oh, Select *… You’re a hard habit to break.
OK, let’s talk about a few bad habits that we have when we are first using SQL on any Relational Database. I will give you some tips below that will have everyone fooled into thinking you really know what you are doing!
This is a tough one. We have all used it. But, do you know the harm it can cause?
What it does:
It simply returns all the rows and all the columns from a table. This will scan the entire table to return the data, and very likely cause lock escalation which can block other users from reading or writing to the table. There are other issues like schema locks, but that is another lesson. Just know that it is NOT GOOD.
What are you trying to do?
Are you trying to see a sample of the data in the table?
Use a TOP Clause : Select TOP (100) * from tableA
Are you trying to see the columns that are in the table?
USE built in functions: exec sp_help to to see the columns, datatypes, indexes, and much more. EXEC sp_help ‘tablename’
This hint will nicely ask SQL Server to forgo locks and bring you dirty data. You can use this hint, or set the transaction isolation level to read uncommitted.
Select TOP (100) * from tableA with(NoLock)
This is just the beginning, and I hope this helps. I will continue to post some tips for beginners all the way to architects.