DATABASE VIEWS* for beginners.

[ktillman]

DATABASE VIEWS* for beginners.
Hello all,
Does anyone have any database view best practices? I understand them yet it is hit or miss when I join tables for reporting purposes.(I'm throwing darts in the dark)
****
My questions are:
1)What should I attempt to join between two tables to get a db view of that join? (ie a field in common between the two tables?SYSID to SYSID?)
2)Table order. Does it matter?
3)Where clause: What is allowed? What isn't allowed? Appears any syntax is allowed regarding logic (ie || && = ==)
4)Are there any detailed examples on the web I can use? Google: "Servicenow Database view" even google can't find much but a single you tube video and a knowledge article from servicenow.com Other than those there is nothing explaining this feature.

thanks!

Kimani

[mreaves]

Hi ktillman;

You're not alone in your frustration or confusion with database views.   These are not your DBA's database views.   They're someone from California's database views.

Here are the answers to your questions.

1) yes - sys_id is a reliable field to join provided you have matches in both tables.

2) yes - ORDER matters, think in terms of layers.

3) not much - not much is allowed in the where clause.   I can only get the join stmt to work.   Even the wiki examples fail in Dublin.

4) not much - certainly not via Google - you're already on the most authoritative Service-Now site.

Good luck,

Mike

[Josh Cooper]

I know this is older, but thanks to search, it's still relevant and near the top of the results.

Another thing you want to take into consideration regarding best practices - if you do not specify the View Fields, you'll return all of the columns for that table.   If you specify them, then you will only return the specific ones you're interested in.   That's important for two reasons:

1.   For large tables, like say, Catalog Item Variable Responses, there could be a performance impact of joining tables with many rows.   Particularly as time goes on.

2.   When you DO limit your fields, make sure to include the fields you need for the WHERE clause.   It's easy to forget sys_id, because who really wants to see it?   It's important when you're linking using a reference field though.

So while it's convenient to link everything wide open while you're building something - after you get it working, you should go back and lock it down to only the fields you need, or those you see a use for in the future.

[ktillman]

Thank you Josh. In the years since this post I have become more proficient with db views. EVEN STILL there are times where it doesn't work at all for now apparent reason. Luckily this type of activity I do not have to do much as reporting wise we are getting back the data needed by folks within my company.

thanks for your post!