Archive for the ‘SQL Server Internals’ Category

SQL Basics – What are System databases | master, model, msdb, tempdb, resource

July 17, 2015 Leave a comment

While working on SQL Server Management Studio you might have seen some default databases in System Database folder. And you would have observed the same set of databases on other machines or database servers you might have worked on or seen, as shown in the image below (red-circled):

SQL System Databases

There are total 5 System Databases, out of these you can see 4 under the System Databases folder (above image, ignore the blue circled). The 5th database is Resource which does not appear on SSMS.

–> Let’s check about these databases individually here:

1. master: database records all the system-level information for an instance of SQL Server, which includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.

It records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.

SQL Server cannot start if the master database is unavailable. If in case you would like to move the master database or have moved it and ran into issues, check this post Move Master database.

Starting SQL Server 2005 and ahead, system objects are no longer stored in the master database, instead, they are stored in the Resource database, mentioned below (5th type).

2. model: database is used as the template for all databases created on an instance of SQL Server. The entire contents of the model database, including database options, are copied to the new database. Thus, if you modify the model database, all databases created afterward will inherit those changes.

Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.

3. msdb: database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SSMS, Service Broker and Database Mail.

SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb, which includes the name of the party that performed the backup, the time of the backup, and the devices or files where the backup is stored.

4. tempdb: database is a workspace for holding temporary objects or intermediate result sets.

tempdb is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

– Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

– Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

– Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

– Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.

Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Thus, there is never anything in tempdb to be saved from one session of SQL Server to another.

5. Resource: database does not appear on SSMS, and is a read-only database that contains all the system objects that are included with SQL Server.

SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

The ID of the Resource database is always 32767.

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf, and are located in :\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Binn\ folder.

Check the Resource Database version and the last updated date:

SELECT SERVERPROPERTY('ResourceVersion') AS ResourceVersion;
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime;

SQL System Databases Resource

Check my other post on Resource Database.

–> You can also query all these system databases, except the Resource database.

SQL System Databases files

And you can see above first 4 database IDs, 1-4 are reserved for master, tempdb, model and msdb databases.

–> There are two more databases created while you install SQL Server with Reporting Services (SSRS). You can see these databases appearing in SSMS in the image above (circled blue):

1. reportServer: stores following:

– Items managed by a report server (reports and linked reports, shared data sources, report models, folders, resources) and all of the properties and security settings that are associated with those items.

– Subscription and schedule definitions.

– Report snapshots (which include query results) and report history.

– System properties and system-level security settings.

– Report execution log data.

– Symmetric keys and encrypted connection and credentials for report data sources.

2. reportServerTempdb: Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server. Background server processes will periodically remove older and unused items from the tables in the temporary database.


Pass an array of multiple values with a single Parameter in a Stored Procedure – SQL Server

July 10, 2015 3 comments

This post comes from an old discussion on MSDN T-SQL Forum[link], where someone asked about the same topic.

Check my reply there and here with this post I’m going to collate all the different methods I’ve blog previously that can be used for the same purpose.

Stored Procedures accept only a fixed and pre-defined number of parameters thus there is a limitation you cannot provide dynamic number of params, like you can do with other languages, like C, C#, Java, etc. In these languages there is concept of Arrays which you can pass in a method/function, but in SQL there are no array variables and it does not have any datatype that support arrays. Thus if you have to provide multiple values to any parameter you cannot do it directly, though there are some workarounds.

–> Here are some of the methods or workarounds by which we can pass multiple values as a single Parameter in a Stored Procedure or a Function:

Method #1 – Passing a CSV: list of strings as a parameter to a (N)VARCHAR datatype parameter, then splitting/parsing it inside the SP or UDF, check here.

Method #2 – Passing an XML: string as an XML datatype parameter. We will need to parse the XML inside the SP, check here.

Method #3 – Using a temp table: inside an SP which is created outside just before its execution. Here there is no need to pass any parameter with the SP, check here.

Method #4 – Using TVPs: With SQL Server 2008 and above you can create TVPs or Table Valued Parameters and declare them by using user-defined table types. These TVPs can then be used to send multiple rows of data to SPs or UDFs, without creating a temp table or multiple parameters, check here.

Method #5 – Passing a JSON string: as a NVARCHAR datatype parameter. We will need to parse the JSON inside the SP, check here.

Thanks a lot, please provide your valuable comments and suggestions on this topic.

>> Check & Subscribe my [YouTube videos] on SQL Server.

Check & Like my FB Page

ColumnStore Indexes evolution from SQL Server 2012, 2014 to 2016

June 8, 2015 4 comments

ColumnStore Indexes were first introduced in SQL Server 2012, and this created a new way to store and retrieve the Index or Table data in an efficient manner.

ColumnStore uses xVelocity technology that was based on Vertipaq engine, this uses a new Columnar storage technique to store data that is highly Compressed and is capable of In-memory Caching and highly parallel data scanning with Aggregation algorithms.

ColumnStore or Columnar data format does not store data in traditional RowStore fashion, instead the data is grouped and stored as one column at a time in Column Segments.

The Traditional RowStore stores data for each row and then joins all the rows and store them in Data Pages, and is still the same storage mechanism for Heap and Clustered Indexes.

Thus, a ColumnStore increases the performance by reading less data that is highly compressed, and in batches from disk to memory by further reducing the I/O.

–> To know more about ColumnStore Indexes check [MSDN BoL].

–> Let’s see the journey of ColumnStore index from SQL Server 2012 to 2016:

We will start with SQL Server 2012 offering for ColumnStore Indexes:

1. A Table (Heap or BTree) can have only one NonClustered ColumnStore Index.

2. A Table with NonClustered ColumnStore Index becomes readonly and cannot be updated.

3. The NonClustered ColumnStore Index uses Segment Compression for high compression of Columnar data.

With SQL Sever 2014 some new features were added, like:

1. You can create one Clustered ColumnStore Index on a Table, and no further Indexes can be created.

2. A Table with Clustered ColumnStore Index can be updated with INSERT/UPDATE/DELETE operations.

3. Both Clustered & NonClustered ColumnStore Index has new Archival Compression options i.e. COLUMNSTORE_ARCHIVE to further compress the data.

Now with SQL Server 2016 new features have been added and some limitations are removed:

1. A Table will still have one NonClustered ColumnStore Index, but this will be updatable and thus the Table also.

2. Now you can create a Filtered NonClustered ColumnStore Index by specifying a WHERE clause to it.

3. A Clustered ColumnStore Index table can have one or more NonClustered RowStore Indexes.

4. Clustered ColumnStore Index can now be Unique indirectly as you can create a Primary Key Constraint on a Heap table (and also Foreign Key constraints).

5. Columnar Support for In-Memory (Hekaton) tables, as you can create one one ColumnStore Index on top of them.

–> Here is the full feature summary of ColumnStore Indexe evolution from SQL Server 2012 to 2016:

SQL Server 2016 - ColumnStoreIndexes

–> Check more about this on [MSDN BoL].

Check the above details explained in the video below:

Reading XML data in SQL Server – MSDN TSQL forum

May 26, 2015 Leave a comment

–> Question:

I have a table with a column with data type XML.

I don’t know the contents of the XML structure etc and I need to extract it

Please suggest.

–> My Answer:

Check these blog posts on how to query and work with XML data in SQL:

Convert a table to XML and back to tabular format

Query XML data in tabulat format

Query Nested XML

Using APPLY operator

Read XML from a file

XML with Namespaces

Export XML to a file

All XML posts

Ref Link.

Export XML column data to a file.xml

April 13, 2015 2 comments

There are times when you want to export XML data stored in Table’s columns to xml files for some purpose.

Here is a small hands on script that uses BCP command with QueryOut option to export data from SQL Server to file system:

USE [AdventureWorks2012]

-- Save XML records to a file:


SET @fileName = 'D:\SQL_Queries\PersonAdditionalContactInfo.xml'
SET @sqlStr = 'select TOP 1 AdditionalContactInfo from AdventureWorks2012.Person.Person where AdditionalContactInfo IS NOT NULL'

SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + ' -w -T'

EXEC xp_cmdshell @sqlCmd

Starting copy...
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 140    Average : (7.14 rows per sec.)

If you have to extract XMLs stored in all the rows then you can iterate through all the rows one by one by using a WHILE loop or a CURSOR and extract data in separate files. You can provide unique names to your files dynamically by tweaking the above code.

Categories: XML Tags: , , , ,