Just starting SQL, have no real idea what I'm doing, trouble with creating and using a view

Question

I have recently (today) started using SQL, I have absolutely no idea what I'm doing. I made a Query.sql file and started to throw some random commands in. I highlight a SELECT and execute it to see what happens, I add a new SELECT, highlight it and see what that does.

However when I tried to create a view it had some problems. I don't really understand why but using a GO before and after it seemed to solve the problem, but now when I do a select from that view, it does not recognize it (that being said, it works, but it's highlighted with red, usually that means it should not compile).

Could someone explain in layman's terms what's going on ? And how can I resolve the SELECT * FROM METRIC_STATS issue ? (I tried encapsulating it in the same go, other goes, don't exactly know how it should even look like)

GO
CREATE VIEW METRIC_STATS (  ID, MONTH, TEMP_C, RAIN_C) 
AS SELECT   ID, 
            MONTH, 
            (TEMP_F - 32) * 5 /9, 
            RAIN_I * 0.3937 
            FROM STATS;
GO          
SELECT * FROM METRIC_STATS;

Presuming SQL Server + Management Studio; its red because it does not automatically detect new object creation via CREATE XXX so incorrectly thinks METRIC_STATS is invalid (you can edit->intellisense->refresh). GO is a special construct used by Management Studio to separate batches of commands; http://stackoverflow.com/questions/2299249/what-is-the-use-of-go-in-sql-server-management-studio — Alex K., Jan 04 '13 at 12:30

score 2 · Accepted Answer · answered Jan 04 '13 at 12:30

2

now when I do a select from that view, it does not recognize it (that being said, it works, but it's highlighted with red, usually that means it should not compile).

Assuming you're using SQL Server Management Studio: you have to manually reset its view list. You can do that by right clicking Views and then Refresh.

@AlexK's suggestion would also work if it exists (in my SSMS, there's no such menu command.)

answered Jan 04 '13 at 12:30

Andomar

232,371
49
380
404

Yes, I edited that tag in (ssms). And I actually found that command (the one @AlexK mentioned) in SSMS (for express 2008) – Kalec Jan 04 '13 at 12:33
It really needs a right click "validate" option for things it assumes are not valid objects – Alex K. Jan 04 '13 at 12:37
@AlexK. I'm not sure I can find such an option. Maybe because it's no longer highlighted ? – Kalec Jan 04 '13 at 12:48
There isn't one, I was just observing that there should be :) – Alex K. Jan 04 '13 at 12:56

score 1 · Answer 2 · edited May 23 '17 at 10:24

GO is the default SQL batch delimiter - most DDL statements like CREATE VIEW must be the first statement in the batch, so presumably your SQL failed because you had other statements before your CREATE VIEW.

Also, CREATE VIEW can only have a single SELECT statement, hence you need the GO after the CREATE VIEW and before you SELECT from it.

Note that once the view is created, it is 'permanent' in the database - you don't need to continually create it in order to use it? If you need to change the view, you'll need to drop it before re-creating it, or alter the existing view.

score 1 · Answer 3 · answered Jan 04 '13 at 12:46

1

I have recently (today) started using SQL, I have absolutely no idea what I'm doing.

I suggest you to read a book about SQL before try to use it.

http://msdn.microsoft.com/en-us/library/ms130214.aspx

answered Jan 04 '13 at 12:46

Max

6,821
3
43
59

1

You won't learn SQL by reading a book. And the MSDN documentation is a reference manual, particularly unsuited for new learners. Check out the [free Standford course](https://www.coursera.org/course/db) for a good way to pick up SQL while doing it. – Andomar Jan 04 '13 at 12:50

Just starting SQL, have no real idea what I'm doing, trouble with creating and using a view

3 Answers3