May 7, 2008 at 2:25 pm
Great article! I too avoid using SELECT * unless I am using it during testing and will not put it in production. A lot of the legacy code that I have to maintain uses SELECT * (probably the least of the problems with the code) and as I have to fix things, I will use only the required fields (90% of the time only a handful of fields are used, but don't even get me started on that :hehe: ).
Thanks to all for the useful ways of getting around typing every field name. I will say the tip about pasting in Excel will be useful for other things I do.
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
May 13, 2008 at 12:20 pm
The same caveat re: * applies to views & stored procedures. Yes, voice of experience.
In any code compiled in a SQL Server DB, one should not use *. It's OK for quick use in QA/SSMS and in ad hoc queries if you actually allow the latter to be used by your dev team.
September 11, 2009 at 3:07 am
Stephen,
Nice article.
This is another strong reason to say not to use SELECT *, instead use SELECT [column names].
September 11, 2009 at 3:28 am
Excellent article - never thought that SQL would work differently for SPs and for functions.
However, to get the list of columns, why go into all the trouble (min. 3 steps) of firing a query, going to Excel, writing even the simplest of all formulae and then copy pasting stuff back?
Instead, the answer was right there in the article (1 step!):
Select cols.name as [Column name], ','
from sysobjects as objs
inner join syscolumns as cols
on cols.id = objs.id
where objs.name = 'MyTable'
order by cols.colorder
I just modified the other query in the article a bit, and SQL Server itself will present you with a list of columns that you can use in the select statement.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
September 11, 2009 at 4:26 am
Just a tip which is kind of related to big lists of things (but not column names!) - sometimes you have a massive list of values that you need to use for an IN () clause in SQL - recently I was given a list of file names I needed to exclude from a procedure temporarily - of course the list was just the filenames, each on it's own line and it didn't contain quotes or commas to separate them
So to avoid opening up excel and messing about in there...
Simply paste the list into a query window in Management Studio (you need at least management studio - Query Analyzer doesn't support regex), do a replace (CTRL+H) on the list and tick the 'use regular expressions' box at the bottom of the search popup.
Type {()} into the 'Find What' box and type '\1', into the 'Replace With' box. (Please type both of these as I've pasted them, unless you know what you are doing with regexes)
Make sure you've highlighted your list of items and the 'Look In' dropdown is set to 'Selection' if you are doing this on an already written query
Click replace and voila, your list should now have commas and quotes, you will need to remove the last comma though 🙂
September 11, 2009 at 4:29 am
Ian, I don't have words enough to thank you for this trick.
I would really be interested in knowing where you got this from.
-- Gianluca Sartori
September 11, 2009 at 4:53 am
Having an aversion to wild card columns from the word go in any code rather than a quick query i have always been on the lookout for shortcuts for typing but never knew it could have this effect on table functions so thanks for that.
For those of you who like SQL Prompt you might want to Check out Apex SQL Edit. It's a whole query environment that's been doing code completion since before Prompt existed. It also give you things like sortable and filterable results panes that you can then detach while you run another query so you can compare results, code formatting and source control integration among other things.
I only looked at the free version of Prompt (community alpha) and it was too full of errors for me to use and I already had Apex. I expect it's orders of magnitude better now though.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
September 11, 2009 at 5:54 am
If you're a script / text based kind of person who doesn't like using the mouse (drag and drop) you can generate the script using the following.
Note: Zip file includes .sql script for this code as well.
DECLARE@TABLENAMENVARCHAR(128)
DECLARE@TABLEALIASNVARCHAR(128)
DECLARE@SCHEMANVARCHAR(128)
DECLARE@COLUMNSNVARCHAR(MAX)
DECLARE@CRLFNCHAR(2)
DECLARE@tabNCHAR(1)
SELECT@TABLENAME=N'TABLE_NAME',
@TABLEALIAS=N'AC',
@SCHEMA=N'dbo',
@CRLF=CHAR(13) + CHAR(10),
@tab=CHAR(9),
@COLUMNS=N''
SELECT@COLUMNS=CASE
WHENISNULL(@COLUMNS,'')= ''
THEN('SELECT' + @tab + @TABLEALIAS + '.' + C.COLUMN_NAME) + ',' + @CRLF
ELSE(@COLUMNS + @tab + @tab + @TABLEALIAS + '.' + C.COLUMN_NAME) + ',' + @CRLF
END
FROMINFORMATION_SCHEMA.COLUMNSC
INNER JOININFORMATION_SCHEMA.TABLEST
ONC.TABLE_NAME=T.TABLE_NAME
WHERET.TABLE_NAME=@TABLENAME
ANDT.TABLE_SCHEMA=@SCHEMA
ANDT.TABLE_TYPE='BASE TABLE'
ORDER BY
C.ORDINAL_POSITIONASC
IF LEN(@COLUMNS) > 0
BEGIN
SELECT@COLUMNS=STUFF(@COLUMNS,LEN(@COLUMNS)-2,3,'') -- Remove the trailing comma from the last column name
PRINT@COLUMNS + @CRLF + 'FROM ' + @tab + @SCHEMA + '.' + @TABLENAME + @tab + @TABLEALIAS
END
ELSE
BEGIN
PRINT'No Columns Selected for Table Name ' + @TABLENAME
END
September 11, 2009 at 7:10 am
An other way to get column names is to use a View. In the SQL Management Studio, select View, New, Select the table(s) you want and select all columns. The advantage here is that you can also have joins etc... IMHO, the View is very useful when creating significant joins across many tables; when done, copy and paste the code into the UDF or the stored proc.
As an aside, there are additional IO and CPU costs to retrieve MORE fields than you need. This is very significant when your tables contain varbinary (max) or Image/Text fields. An other good reason NOT to use *.
September 11, 2009 at 7:50 am
First, let me thank you for posting this gotcha. It's an important one to know.
However, I wonder about the usefulness of your solution. Certainly, if you don't want the outcome of the function to be changed by a change in the underlying table structure, listing all of the required fields is the best way to go. But if you write it as "SELECT *", then you would expect the function to really select all columns, even if the underlying table changes. So in your example, if you add a column to the table, you would want that columns value included in the functions outcome. By manually typing, copying, or drag-and-dropping each column name into your function, every time you make a change to a table, you will now have to look around for all of the functions that reference that table, and add the updated columns to them as well.
I would suggest a better method would be to have a script or set of scripts that blows away and recreates each function for a particular table. This way it's a one or two click deal to update all functions that use SELECT *.
September 11, 2009 at 8:03 am
But that's where good structure and documentation come in.
Without a doubt, SELECT * is a bad thing in many ways.
Besides, if you have a function with SELECT * and you change the table structure, thus ultimately the output of the function then any front end software relying on the output of that function is then going to be thrown off unless you then "go looking through all of your code" to update that and the interface between the DB and front end software is notoriously more flaky.
Don't forget, at any time you can look up dependencies on a table to see exactly what tables, view functions and procedures may be affected by a change even without documentation but it is far harder to look up what front end functionality will be affected by a change to a function or a view.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
September 11, 2009 at 8:05 am
Why don't you just use sprocs or views instead? I can't think of a good reason I'd want to return the data from a table in a function as a result set - I may pass in a string and return a list of data after splitting that string on commas or something, but to select * from a table... what are people doing to the data which warrants writing a function that does a SELECT * from a table?
I've got a better idea - write SELECT * FROM table in a new query window - see, same thing!
Also on one of the comments above - if you've got enough functions that are filled with SELECT <columns> FROM <tables> that making a change to a table requires you to edit 50 functions, then you are doing something wrong in your code!
September 11, 2009 at 8:50 am
I liked the article, but since most of the discussion revolved around generating column lists I thought I'd add my two cents.
All of the obvious methods (drag-and-drop Columns from Object Browser, SQL Prompt, etc.) are great but there's not much else to say about them that hasn't already been said. One technique I did not see mentioned was to highlight a "SELECT * FROM" query in Management Studio and click on the "Design Query in Editor" tool. It will expand the column list, add table prefixes if joins are used, and add bracket quotes where required. It helps to have SQL Refactor to fix the awful formatting though.
Even with these shortcuts there seems to be a significant audience for scripting. One advantage is that it gives you better control of the format. All the scripts used concatenation aggregation, but in SQL 2005 and later it's better to use FOR XML PATH. Use "Results to Text" mode for these queries.
-- Horizontal format column list
SELECT SUBSTRING(
(SELECT ', [' + [name] + ']'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('sch.table')
ORDER BY column_id
FOR XML PATH('')), 3, 9999)
-- Vertical formats require line breaks, XML queries change them to "& #x0D;"
-- A workaround is to use a delimiter such as |, and REPLACE it with line breaks.
-- Vertical format column list, leading commas
SELECT REPLACE(SUBSTRING(
(SELECT '|, [' + [name] + ']'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('sch.table')
ORDER BY column_id
FOR XML PATH('')), 4, 9999), '|', CHAR(13))
-- Vertical format column list, trailing commas
SELECT REPLACE(SUBSTRING(
(SELECT ',|[' + [name] + ']'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('sch.table')
ORDER BY column_id
FOR XML PATH('')), 3, 9999), '|', CHAR(13))
If you are organized enough to have useful metadata in the column Description properties, then ather reason to use a script is to include the Description in comments. (The == is supposed to be --, it was changed because it confuses the syntax highlighting in this forum.)SELECT REPLACE(SUBSTRING(
( SELECT '|, [' + sc.[name] + ']'
+ ISNULL(' == '
+ CAST(ep.value AS VARCHAR), '')
FROM sys.columns AS sc
LEFT OUTER JOIN sys.extended_properties AS ep ON sc.[object_id] = ep.major_id
AND sc.column_id = ep.minor_id
WHERE sc.[object_id] = OBJECT_ID('MarxBros')
AND ( ep.class = 1 ) AND ( ep.name = N'MS_Description' )
ORDER BY column_id
FOR XML PATH('')), 4, 9999), '|', CHAR(13))
Results:
[font="Courier New"][Groucho] -- Cigar, fake mustache
, [Chico] -- Pretends to be Italian
, [Harpo] -- Goofy hair, doesn't talk
, [Zeppo] -- Relatively unknown
, [Gummo] -- Unknown even to relatives[/font]
September 11, 2009 at 8:55 am
Of course if you want to be really lazy, convince your boss to buy you a copy of redgate, then you only have to press tab after placing the cursor after the *
September 11, 2009 at 9:04 am
In ApexSQL Edit you can add custom scripts as table options so that you can right click on a table, select your custom script and it will run the query for you passing the {tablename} in as an auto replacement.
I've found that very handy for Generate Table Inserts that generates insert scripts for all the data in a table. Though Edit also includes an intellilist column picker as well as * to all column replacement I'll definitely be having a look at some of these scripts for adding my own custom table scripts.
We have some very badly designed DBs thrown at us from a 3rd party with 40 of columns of things that need to be summed in some cases so quick generation of col1 + col2 + col3.... would come in handy when they throw new ones at us though on the main one I now have a view that unpivots them all.
In response to Charles:
Yes, procedures and views are the way to go for generating results sets for output to a front end but posts here suggest views suffer from the same need to be refreshed if SELECT * is used.
The point of the article is to highlight yet another reason you should never use column wildcards outside of quick one off queries and supplying a way to make full column qualification easier.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
Viewing 15 posts - 46 through 60 (of 151 total)
You must be logged in to reply to this topic. Login to reply