Data model · LessonBy Sneha I, ServiceNow Trainer, 8 yrs · Published · ServiceNow · all levels
Database views
Join two or more tables for reporting without duplicating data, and know when a view is the wrong tool.
Quick answer
Join two or more tables for reporting without duplicating data, and know when a view is the wrong tool.
Key takeaways
- Views join at query time, so filters matter more than usual
- Order the tables so the most selective is first
- Views are read only by design
- Index join columns or expect slow reports
What a view is
A database view joins tables at query time using fields you nominate, and exposes the result as a read only table you can report on. The classic example joins incident to metric instance or to task SLA for resolution reporting.
Building one
Add one view table record per source table, give each a prefix, set the order, and define the where clause that links them. The left table is queried first, so put the most selective table first.
Performance and limits
Views can be slow because the join happens on every query.
- Always filter the report, never open an unfiltered view on a large table
- Index the join fields on both tables
- Views are read only, so they are for reporting not for process
- Consider a scheduled data collection or a flattened reporting table for very large joins
Practice challenge
+0 XPStreak ×0
Question 1 of 2
When is a database view evaluated?
Frequently asked questions
Can users update records through a database view?
No. Views are read only. Build a report or a dashboard on top of them.
Our SLA report on a view times out, what now?
Filter by a date range, index the join fields, or move to a scheduled job that writes a summarised table for reporting.