Going in circles

Julia1

CREATE PROCEDURE LoadAllFoo AS SELECT * FROM Foo WHERE FooName IN (SELECT FooName FROM V_FooName)

CREATE VIEW V_FooName AS SELECT FooName FROM Foo

Enough said.

toon

At first, I thought: this might simply be a permission thing. But then I saw the second SQL line. Is this by any chance the odd one out in a bunch of similar constructions where the second SQL line has a filter of some sort?

Julia1

There's one view which is like this but does an is-not-null test. The rest, sadly, slavishly follow the pattern even though they clearly add nothing of any value...

Cassidy

The only purpose I can see here is either a permissions thing (as toon said) or to introduce a layer of abstraction between tables and connecting applications (such as a reporting tool) that permits changes to the underlying table structures and tweaking the view without affecting the reports.

Without knowing the reasons why they're doing it.. it does look like a WTF.

galgorah

That gets really dangerous. If someone adds a column in the middle of the table you can get a situation where the view returns a column that actually has a different columns data in it. This is because internally the view uses an ordinal mapping instead of the actual column name. You need to drop and recreate the view to fix this. You can also test this in SQL Server, by creating a table with few columns then create a view based on that table. Then add a column into the middle of the table. Then check the view.

Nesting views can also be really really bad as it can confuse the database engine.

toon

@galgorah said:

Nesting views can also be really really bad as it can confuse the database engine.

I've had some recent experience with nesting views, and in my experience, it's not the DBMS that gets confused.

PJH

@galgorah said:

Then add a column into the middle of the table.

You can specify the 'order' the columns are stored? What's the syntax?

toon

@PJH said:

@galgorah said:
Then add a column into the middle of the table.
You can specify the 'order' the columns are stored? What's the syntax?

In MySQL you can say "AFTER new_column_goes_after_this_column" right after the column definition in an ALTER TABLE query, but I don't know about other DBMSes. Anyway, in MySQL columns definitely have orders, of course basing program logic on that is not a very good idea but that's a different discussion.

pkmnfrk

@PJH said:

@galgorah said:
Then add a column into the middle of the table.
You can specify the 'order' the columns are stored? What's the syntax?

It's really easy in Sql Server:

BEGIN TRANSACTION;

SELECT *
INTO MyTable_tmp
FROM MyTable;

DROP CONSTRAINT FK_MyTable_MyOtherTable;
-- ... etc ...

DROP TABLE MyTable;

CREATE TABLE MyTable (
    MyTableID int NOT NULL PRIMARY KEY,
    MyOtherTableID int NOT NULL FOREIGN KEY REFERENCES MyOtherTable(MyOtherTableID),
    MyNewColumn int NULL,
    MyOldColumn int NULL
);

SET IDENTITY_INSERT MyTable ON;

INSERT INTO MyTable (MyTableID, MyOtherTableID , MyOldColumn)
SELECT * FROM MyTable_tmp;

SET IDENTITY_INSERT MyTable OFF;

DROP TABLE MyTable_tmp;

COMMIT;

See? Easy!

galgorah

@pkmnfrk said:

@PJH said:

@galgorah said:
Then add a column into the middle of the table.
You can specify the 'order' the columns are stored? What's the syntax?

It's really easy in Sql Server:

BEGIN TRANSACTION;

SELECT *
INTO MyTable_tmp
FROM MyTable;

DROP CONSTRAINT FK_MyTable_MyOtherTable;
-- ... etc ...

DROP TABLE MyTable;

CREATE TABLE MyTable (
    MyTableID int NOT NULL PRIMARY KEY,
    MyOtherTableID int NOT NULL FOREIGN KEY REFERENCES MyOtherTable(MyOtherTableID),
    MyNewColumn int NULL,
    MyOldColumn int NULL
);

SET IDENTITY_INSERT MyTable ON;

INSERT INTO MyTable (MyTableID, MyOtherTableID , MyOldColumn)
SELECT * FROM MyTable_tmp;

SET IDENTITY_INSERT MyTable OFF;

DROP TABLE MyTable_tmp;

COMMIT;

See? Easy!

SQL Server does this all behind the scenes if you use the GUI. So yes it is easy. Also It's not uncommon in a datawarehouse to recreate tables as part of the build process. So if the structure changes as part of the build, you'll risk getting the issue then as well.

blakeyrat

@pkmnfrk said:

@PJH said:
@galgorah said:
Then add a column into the middle of the table.
You can specify the 'order' the columns are stored? What's the syntax?

It's really easy in Sql Server:
Open up SQL Server Management Studio

Right-click one of your tables and choose "Design"

Draw the header for the column you want to move into the position you want to move it to

Click Ok
See? Easy!

FTFY.

pkmnfrk

@blakeyrat said:

@pkmnfrk said:
@PJH said:
@galgorah said:
Then add a column into the middle of the table.
You can specify the 'order' the columns are stored? What's the syntax?

It's really easy in Sql Server:
Open up SQL Server Management Studio

Right-click one of your tables and choose "Design"

Draw the header for the column you want to move into the position you want to move it to

Click Ok
See? Easy!
FTFY.

(Shh, you want people to think this stuff is easy??)

dhromed

@galgorah said:

where the view returns a column that actually has a different columns data in it. This is because internally the view uses an ordinal mapping instead of the actual column name.

I've been bitten by this. It was a WTF moment. I fixed by ALTERing the view with no change at all, i.o.w. recompiling the query.

dhromed

@pkmnfrk said:

(Shh, you want people to think this stuff is easy??)

Both ways are good to know.