Bah, for the forth time in a week I encounter a star in the select-list of a view. So if no-one told you yet; when creating views, don’t do a SELECT * FROM source, it can (and in time will) hurt you badly. Let’s show by creating a table and two views based on that table;
CREATE TABLE tblSource(
Col1 nchar(10),
Col2 nchar(10))
GO
CREATE VIEW vwBad
AS
SELECT * FROM tblSource
GO
CREATE VIEW vwGood
AS
SELECT Col1, Col2 FROM tblSource
GO
INSERT tblSource (Col1, Col2) VALUES ('Column 1', 'Column 2')
GO
So far, so good. Selecting from both views shows you the expected results:
SELECT 'vwGood' view_name, * FROM vwGood
GO
SELECT 'vwBad' view_name, * FROM vwBad
GO
Now add a column to the table, and add some data for that column;
ALTER TABLE tblSource
ADD Col3 tinyint
GO
UPDATE tblSource
SET Col3 = 3
GO
SELECT 'vwGood' view_name, * FROM vwGood
GO
SELECT 'vwBad' view_name, * FROM vwBad
GO
vwGood still behaves as expected, but vwBad doesn’t return ALL columns as you may have expected based on the *. Now let's see what happens if a column is removed from the table;
ALTER TABLE tblSource
DROP COLUMN Col2
GO
SELECT 'vwGood' view_name, * FROM vwGood
GO
SELECT 'vwBad' view_name, * FROM vwBad
GO
You will notice that vwGood throws an error (rightfully so, it is instructed to select from a non-existing column), but vwBad doesn’t… it pretends Col3 is Col2. Based on the data, we know better. However this might not be so obvious if based on this change the purchase price is shown as sales price due to changes in the data model and your company starts losing money.
I bet now you want to know which views contain * so you can code them properly, try this query if you're on SQL Server 2005:
SELECT v.name view_name
, s.name schema_name
, u.name user_name
, v.create_date
, v.modify_date
, sc.text
FROM sys.views v
INNER JOIN sys.syscomments sc ON v.object_id = sc.id
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
INNER JOIN sys.sysusers u ON s.principal_id = u.uid
WHERE sc.text LIKE '%*%'
And this one for SQL Server 2000:
SELECT so.name view_name
, u.name user_name
, so.crdate create_date
, sc.text
FROM dbo.sysobjects so
INNER JOIN dbo.syscomments sc ON so.uid = sc.uid
INNER JOIN dbo.sysusers u ON so.uid = u.uid
WHERE sc.text LIKE '%*%' AND v.type = 'V'
And don't forget to clean up the example:
DROP VIEW vwGood
GO
DROP VIEW vwBad
GO
DROP TABLE tblSource
GO
Technorati tags:
SQL Server,
T-SQL