SQL Server System Databases

For a large majority of SQL developers and BI analysts we will tend to access instances of SQL Server and go directly to the data to pull what we need. We log in, select our user database(s), write queries, and generate datasets from them. But what about those 4 visible databases that we see in our Object Explorer all the time? They just sit there and we don’t entirely know what they do or what we can do with them. Well, I’m going to spell it out for you.

---

master

Some of us have actually used this database before and understand the jist of what this database does and the information that it holds. To clarify, the master database is a system database that contains all of the system-level information for that instance of SQL Server. This means it contains all logins, linked servers, all endpoints, database information and objects, and other configuration settings will be stored here. This database must be present otherwise SQL Server will not start.

Pro Tip: Always make sure you take regular backups, otherwise any corruption of the master database will result in you having a giant headache and all of your changes will be lost in space somewhere

When maintaining and backing up this database always remember, as a rule of thumb, that you shouldn’t ever create user object in master. Otherwise it will need to be backed up much more often.

---

model

This database does exactly as the name describes; it is a model for any new and upcoming databases. The model database contains properties, users, stored procedures, tables, views, etc. and can be changed at any time without disturbing currently existing databases. Using this you can create your ideal model for every new database and pre-load it with users and stored procedures, making life ultimately easier.

This is the only function allowed to this database. It does nothing else.

---

msdb

If you are familiar with any DBA responsibilities or functions then you will be, at least, a little familiar with msdb. This is because msdb is used by the SQL Agent, database mail, Service Broker, log shipping, and other services. This also holds the backup history for the instance.

---

tempdb

Out of all of the databases on an instance, tempdb is probably the busiest. It is the background workspace that SQL Server uses for ERRYTHANG! This includes query processing and sorting, temporary tables, snapshot isolation, user-created temporary tables, table variables, etc.

SQL Server will always drop and re-create the tempdb every time the SQL Server service is started. Once the server restarts everything you once had will be gone.

Maintaining and keeping the tempdb properly sized is vital for the performance of your SQL Server instance. By default, tempdb starts at 8MB in size and will grow by 10% until the drive is full. This means that system resources will be spent during a large dataset resulting query just to be able to accommodate for it.

---

Resource

There is actually one more system database that exists, although you cannot see it. This is a hidden system database where system objects are stored. By normal methods you will not be able to see this; however, you are able to access the data files by navigating to C:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\Binn.

Typically, the only way to access the contents of this database is by using the OBJECT_DEFINITION system function.

JSON Compatibility

Well everyone SQL 2016 is now available to the public and with it comes fucntionality that everyone has been asking and waiting for, JSON support! With today’s world utilizing more JSON for it’s data interchange instead of just XML, the requests for native JSON support in DBMSs have been increasing. SQL Server answered those developers that were requesting this new support and has provided local JSON functions and full native functionality for handling JSON items. Before we jump into the new JSON functionality lets review what JSON is and why it’s good.

What is JSON?

JavaScript Object Notation (JSON) is a way to store information in an easy-to-read, easy-to-access manner. It essentially gives us a more human-readable format of the collected data that can be accessed in a highly logical way. With more and more websites using AJAX in their back end it’s becoming much more important for websites to be able to load data very quick and asynchronously. I say asynchronously because it runs the request for data in the background as to not cause page deadlocks.

These AJAX powered websites all provide what are known as RSS feeds, which are very easy to import and use on the server-side but when loading with AJAX you run into an error: you can only load an RSS feed if you are requesting it from the same domain that it’s hosted on. JSON uses a method called “JSONP” that uses a callback function to push the JSON data back to whichever domain you’re on, thus allowing you to easily store the JSON data and call it whenever you need it.

For a quick reference into what JSON actually looks like see below:

Example

First let’s create a table that will store some test data about a few of our favorite super heroes.

CREATE TABLE [dbo].[People]
(
[Id] [INT] NULL ,
[FirstName] [VARCHAR](50) NULL ,
[LastName] [VARCHAR](50) NULL ,
[Address] [VARCHAR](50) NULL ,
[City] [VARCHAR](50) NULL ,
[State] [VARCHAR](50) NULL ,
[Zip] [VARCHAR](50) NULL 
)
ON [PRIMARY];
GO

Let’s add in our super heroes.

INSERT INTO dbo.People
( Id ,
FirstName ,
LastName ,
Address ,
City ,
State ,
Zip
)
VALUES ( 1 ,
‘Captain’ ,
‘America’ ,
‘1234 Freedom Street’ ,
‘Ann Arbor’ ,
‘MI’ ,
‘48108’
),
( 2 ,
‘Iron’ ,
‘Man’ ,
‘5678 Genius Ave.’ ,
‘Chelsea’ ,
‘MI’ ,
‘48118’
),
( 3 ,
‘Black’ ,
‘Widow’ ,
‘910 Russian Lane’ ,
‘Detroit’ ,
‘MI’ ,
‘48108’
)

Now that we have a table filled with data let’s pull all of it out and convert to a JSON item. For this example let’s simply grab everything from the table and return it using JSON AUTO.

SELECT *
FROM dbo.People
FOR JSON AUTO

You will notice in the above function that there is a section labeled “FOR JSON AUTO” and I know exactly what you’re thinking, it looks exactly like how you have to tell SQL to return XML data type. Of course Microsoft would fit JSON in the same way, it makes total sense. Using FOR JSON AUTO works the same way as FOR XML AUTO and will simply return the JSON format of the data you request. The result returned from SQL is below as well as the formatted version of the JSON.

SQL:

Formatted JSON:

[
{
“Id”:1,
“FirstName”:”Captain”,
“LastName”:”America”,
“Address”:”1234 Freedom Street”,
“City”:”Ann Arbor”,
“State”:”MI”,
“Zip”:”48108″
},
{
“Id”:2,
“FirstName”:”Iron”,
“LastName”:”Man”,
“Address”:”5678 Genius Ave.”,
“City”:”Chelsea”,
“State”:”MI”,
“Zip”:”48118″
},
{
“Id”:3,
“FirstName”:”Black”,
“LastName”:”Widow”,
“Address”:”910 Russian Lane”,
“City”:”Detroit”,
“State”:”MI”,
“Zip”:”48108″
}
]

We must keep in mind that this is just JSON support and not the native JSON type. In SQL Server 2016 the JSON data will be represented as the NVARCHAR data type. But what happens if we want to reverse the process? Take a JSON object and then transform it into a relational table? Microsoft built in conversion for this as well! Let’s use the previous result as the base for the transformation from JSON object to relational table.

DECLARE @JSON NVARCHAR(MAX)=(
SELECT Id ,
FirstName ,
LastName ,
Address ,
City ,
State ,
Zip
FROM dbo.People
FOR JSON AUTO)

SELECT Array.Id ,
Array.FirstName ,
Array.LastName ,
Array.Address ,
Array.City ,
Array.State ,
Array.Zip
FROM OPENJSON(@JSON,’$’)
WITH (Id INT,
FirstName VARCHAR(50) ,
LastName VARCHAR(50) ,
Address VARCHAR(50) ,
City VARCHAR(50) ,
State VARCHAR(50) ,
Zip VARCHAR(50)
) AS Array

Much like XML paths you are able to reference specific paths in JSON objects. The dollar sign ($) represents the input JSON object and is using a JavaScript-like syntax for referencing properties within JSON text. A few examples include:

• ‘$’ – references the entire JSON object
• ‘$.property1’ – references property1 in the JSON object
• ‘$[5]’ – references the 5th element in the JSON array

Other JSON Functions

• ISJSON() – this checks if the NVARCHAR text input is properly formatted to JSON specifications
• JSON_VALUE() – parses through the JSON text to extract scalar values on the path