question

Gehima2016 avatar image
Gehima2016 asked

View Syntax

CREATE VIEW Vw_Curriculum as SELECT P.PathID, P.PathName, P.PathDesc, P.PathActive, P.InsertDate, P.PathOrderID, P.PrePath, P.PrePathID, P.PathLongDesc, P.PathLength, P.PathCost, P.PathNeededMaterials, c.CourseID, c.CourseName, c.CourseOrder, c.CourseDesc, c.CourseActive, s.SectionID, s.SectionName, s.SectionOrder, s.SectionDesc, s.RoleID, s.SectionActive, s.EventCount, e.EventID, e.EventName, e.DocumentID, e.EventOrder, e.EventActive, e.Repeat, t.EventTypeID, t.EventTypeName, d.DocumentName, d.DocumentDesc, d.DocumentLink, d.IsAttached, d.IsActive FROM [dbo].[Path] P LEFT OUTER JOIN [dbo].[Course] c ON P.PathID = C.PathID LEFT OUTER JOIN [dbo].[Section] s ON c.CourseID = s.CourseID LEFT OUTER JOIN [dbo].[ADF_Event] e ON s.SectionID = e.SectionID LEFT OUTER JOIN [dbo].[ADF_EventType] t ON t.[EventTypeID] = e.[EventID] LEFT OUTER JOIN [dbo].[ADF_Document] d ON t.[EventTypeID] = d.[EventTypeID]
viewsyntax
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Gehima2016 There is no question there per se, so let me assume that it is about whether this syntax is possibly valid or not. In my opinion, it might fly in the classroom, but definitely not in corporate environment. - Creating the view without explicit schema spec is evil and should be avoided. - According to @Phil Factor, aliasing all tables in the statement with one letter is anti-social. While I should probably agree with this assessment, I don't, because I am also guilty as charged sometimes, but I do not consider myself anti-social. I just believe that if the statement is short and simple then it is OK, and it is not OK if the statement is long and complicated. - The standard set of options before the view definition is missing. Again, this might work in the classroom but not in real life. In fact, I used to work for a company where we had a policy reading that exclusion of the settings was a reason for immediate termination of employment. Needless to say, we did not have anyone who would actually try it. So, if you worked there and submitted a view like this for deployment then you would be looking where to apply next. Hope this helps.
0 Likes 0 ·
Hi Oleg, Thanks for your response. What are the standard set of options before the view definition. Let me know. Thanks
0 Likes 0 ·
@Gehima2016 It depends on whether the view is indexed view or not. If it is then there are 7 options that must be set: - [ansi_nulls][1] - [ansi_padding][2] - ansi\_warnings - arithabort - concat\_null\_yields\_null - quoted\_identifier - numeric\_roundabort First 6 must be set on on, and the numeric\_roundabort to off. For not-indexed view the first 2 options should be set. These are so-called sticky settings, they are honoured at the time when the view is created and cannot be changed inside of the view definition script. To avoid the situations when the status of the settings is "borrowed" from the settings of the user creating the view (whatever they are), the first 2 settings in the list above should be explicitly set. It is worth it to study the settings in the list above in order to understand their meaning and purpose. First 2 settings are hyperlinked, others can be easily found on [Microsoft Docs][3] [1]: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql [2]: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql [3]: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.