August 6, 2019 at 2:39 am
What are the most commonly used database objects that I should become familiar with? I'm trying to apply the Pareto Principle (80/20 rule) here to save myself time.
August 6, 2019 at 11:41 am
That's going to be tough. We have to define what you mean by "most common" because it's very function dependent. Are you developing a database for an organization? Then the "common" tables are the ones that you're working on, not system tables. However, having a knowledge of sys.dm_exec_* Dynamic Management Views will help you writing queries, tuning, etc. Are you maintaining backups? Then you need to learn the agent jobs tables, history, and how to get the files where databases are stored so that you can properly restore them. Are you working with Availability Groups or not? Do you have replication in house? Mirroring? All these will change where we focus our time and attention.
What's your job? Generic DBA, Developer, System Administrator, Jack of All Trades, Data Analyst? Each of these is going to focus where we pay attention as well.
There's just so much to learn here and the topics are so broad. I couldn't possibly tell you a single list of tables, views & procs. Not even trying to focus on the 80/20 rule. I'll be fascinated if someone thinks they've got that answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 6, 2019 at 6:13 pm
I agree with Grant for the most part. It's hard to say what you should know how to use without knowing your job.
For a developer, I think that the INFORMATION_SCHEMA views can become helpful, but really you ought to know about sys.objects, sys.columns, sys.indexes for querying these structures for metadata. However, I rarely would do that, instead focusing on the objects themselves rather than looking at the DMVs.
For a DBA, I think the dm_exec (as Grant mentioned) are good, as well as the disk, os, perf counter tables for metrics.
August 6, 2019 at 10:42 pm
I am a beginner. Let's say I wanted to view this from the standpoint of somebody who is building databases.
August 7, 2019 at 12:34 am
OK. Most of the work then is going to be with the structures you're building. It's a good idea to know that there are system views (frequently called tables) that define the tables, keys and indexes you're building. These are like sys.tables and others. However, you'll only use them lightly, so don't memorize them. Know they're there so you can use them for searching, aggregating, etc.. Most of your work is going to be around CREATE, ALTER, DROP commands, that's what you should focus on. That'll keep you busy for a bit. There's more, but that's a foundation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 12, 2019 at 9:23 pm
OK. Most of the work then is going to be with the structures you're building. It's a good idea to know that there are system views (frequently called tables) that define the tables, keys and indexes you're building. These are like sys.tables and others. However, you'll only use them lightly, so don't memorize them. Know they're there so you can use them for searching, aggregating, etc.. Most of your work is going to be around CREATE, ALTER, DROP commands, that's what you should focus on. That'll keep you busy for a bit. There's more, but that's a foundation.
Ok. Thank you for the suggestions.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply