A friend of mine pointed out that I really should not be using "*" in select statements and leaving out field names in Insert statements and that by doing so it might lead someone into bad habits, so I thought I would post this for everyone's understanding.

As you may have noticed I tend to use "*" a lot on my code examples in my blog post. I assume that as DBAs you know to replace the "*" from a "Select * from Table Name" with the actual fields. And in the case of inserts you should name the fields that the data is being inserted into as well. If fact I would recommend that you always name the fields in an insert and / or a select statement. Let us fact reality, database structure does change over time and the use of "*" can really get you into trouble very fast.

The reason I do this on my post is really quite simple and easy to explain. First, as I have stated before I am lazy, it is much easier for me to type "*" in a blog post rather than all the field names. Yes, I could cut and paste actual code but most time I actually do not, and even with I do cut and paste my code I tend to remove the field names from insert statements and replace field names in select statements for several reasons.

One is so that the general post will still be valid for newer versions of the database even after the database structure has changed.

Second, not everyone is on the same version and sometimes fields that are valid for one person reading my post is totally nonexistent for someone else reading my post. If fact, in "Example 1" below one of the field names is not valid in the 8.x version of Dynamics GP, I will let you guess which one.  J  

Third, and the main reason I use the "*" is for readability. Take the following two examples:

Example 1 - The way it should be

insert into gp.gpcar.dbo.upr10302(COMPTRNM,BACHNUMB,EMPLOYID,COMPTRTP, SALCHG, UPRTRXCD, TRXBEGDT, TRXENDDT, TRXHRUNT, HRLYPYRT, PAYRTAMT, VARDBAMT, VARDBPCT, RECEIPTS, DAYSWRDK, WKSWRKD, DEPRTMNT, JOBTITLE, STATECD, LOCALTAX, SUTASTAT, WRKRCOMP, LASTUSER, LSTDTEDT, TRXSORCE, DOCTYPE, VOIDED, NOTEINDX, INADDNTOSAL, SHFTCODE, SHFTPREM, JOBNUMBR, UNIONCD)
SELECT COMPTRNM, BACHNUMB, pd.EMPLOYID, COMPTRTP, SALCHG, UPRTRXCD, TRXBEGDT, TRXENDDT, TRXHRUNT, HRLYPYRT, PAYRTAMT, VARDBAMT, VARDBPCT, RECEIPTS, DAYSWRDK, WKSWRKD, DEPRTMNT, pd.JOBTITLE, pd.STATECD, pd.LOCALTAX, SUTASTAT, WRKRCOMP, ASTUSER, LSTDTEDT, TRXSORCE, DOCTYPE, VOIDED, NOTEINDX, INADDNTOSAL, SHFTCODE, SHFTPREM, JOBNUMBR, pd.UNIONCD
FROM POSTDETAIL as pd

 

Example 2 - What I type would actually type in a blog post

insert into gp.gpjac.dbo.upr10302 
     select * from PostDetails as pd

Now, personally I find the second example a lot easier to read in a post and as a DBA I know to add the "(field names)" after the insert and then replace "*" in the select statement part. And it has the advantage the two different people on two structurally different versions of the database can still use the information from the post by adding the field names for their versions of the database.

So whenever you see a "Select *" please just take for granted that you should replace the "*" with the actual field names. Also on insert statements you should always list the field names that are used in the order of the following select statement.

till later,
John