Differential Backups for System Databases

There was a question in www.sqlservercentral.com asking what the system database you can’t take differential backups.

My initial thought was it is tempdb as I couldn’t see a reason why you need take differential backups in tempdb. Also, msdb and model you might need to take the differential backups and in master database you should be able to take differential backups since it has user information.

But the answer is master.

Then I thought of digging further into this.

1. Is this true?

Yes it is. If you go through the backup UI , you will only see Full backup option under master database while for all the other system databases you will see Full and Differential backup option.

2. Why?

I was wondering why is this since I can’t see a logic behind this. However, it is very simple.

What is the mechanism to restore differential backups.

1. Restore the Full backup with NORECOVERY option on.
2. Then Restore the differential backup.

So if you want restore differential backup of a master database, first you need to restore full back with NORECOVERY option. which you CANNOT do for master database. Since you can’t do that there is no point of taking a differential backups.

Contained Databases in SQL Server 2012

Contained databases is a new security feature in SQL Server 2012. Read the new article on Contained Databases in SQL Server 2012.

SS SLUG - May 2012 Meetup

The May 2012 meet-up of the SQL Server Sri Lanka User Group is happening...

SESSION #1 - Windowing Functions in SQL Server
==========
Windowing functions in SQL Server allows you to apply aggregations on partitions of a result set. Wonder how this is done? Join in and find out.
This session is developer focused and appeals to DBAs as well.

LEVEL: Intermediate/Advanced

SPEAKER
--------------
Dinesh Karunarathne
Development DBA - Pearson Lanka
MCTS - SQL Server
http://dbbitsandpieces.blogspot.com

SESSION #2 - Breaking a SQL Myth in 300 Seconds - Table Variables with less data does not go to tempdb
==========
One myth involving SQL is that table variables that hold a small data set do not go into tempdb. Join Dinesh Asanka as he breaks this myth in 300 seconds.

SPEAKER
--------------
Dinesh Asanka - MVP, SQL Server
Production DBA - Pearson Lanka
http://dbfriend.blogspot.com

LOCATION: Microsoft Sri Lanka [Level 11 - DHPL Building, No. 42 Nawam Mawatha, COLOMBO 2]
DATE: Wednesday 23rd May, 2012
TIME: 6:00 PM Onwards
ENTRANCE: FREE

On a related note, the SQLServerUniverse.com site is down for a few weeks. We are working on getting it up and running again. Till then we shall communicate via LinkedIn, Facebook and Twitter...

SQL Server 2012 CU 1 Released

SQL Server 2012 Cumulative Update 1 contains fixes released in SQL Server 2008 R2 SP1 CU 3 and 4. For customers upgrading to SQL Server 2012 from SQL Server 2008 R2 SP1 CU3 (Build 10.50.2789.00) or CU4 (Build 10.50.2796.0) should consider applying SQL Server 2012 CU 1 to ensure fixes resident on the system are available post upgrade.

• CU#1 KB Article: http://support.microsoft.com/kb/2679368
• SQL Server Support Information: http://support.microsoft.com/ph/2855

TIES vs RANK

Let us come with this scenario. I want to display student who has got highest marked.

Let us say my data set is similar to following.

You might not think this is a hard question and you will say you can go with TOP 1 with DESC for the Value column as shown in the below query.

However, the problem is what if there are more than one top scored students in the list as above will return only record.

To solve the above problem, you can go with RANK function as shown bellow.

However, in TOP syntax there is a feature called WITH TIES to address the above issue.

Following is the results for this.

You can see that though you specified TOP 1, it returns you all the records which has highest marks.

Let us analysis the query plan for the both queries. First is the query with WITH TIES and the second is with RANK function.

So, WITH TIES query is preforming better to the RANK.

CTEs for SQL Server Developers

These days my blog is hit with temp table, table variable and CTE discussions. So I did a full analysis of CTEs, how and when they can be used. Here is the full article of it.

Pivot Task in SQL Server 2012

Pivot transformation in SQL Server Integration Services has gone into user interface change to ease the pivot configurations. If you have done this in SQL Server 2008 and before, you know how difficult to configure Pivot in SSIS.

Well, above interface will tell you that how easily you can configure pivot. Sometimes, you might have dilemma as which column should I select for columns and for rows. However, with this user friendly interface, user can visualize which column coming where.

From the following image, you can see how data was pivoted using the above configuration.

What is Best for Table Scan? Clustered Index or No Index

Always we believe Clustered Index is the best. However, what it will for table scan.

Here is the simple test of it.

First let me create a data to play around.

Then let me create a table with clustered primary key.

Then populate some data. Following script will some time to populate considering the page splits.

Then let me create a table with no index.

As before let me populate same set of data to the table with no indexes.

Now let us select data from both tables.

Following times shows that table with index has taken more time to read than table with no indexes.

and execution plan confirmed it.

Question is why?

Let us see the fragmentation on tables.

Result is,

So this tells you the story. Since you have clustered index, there are fragmentations. In the above scenario, table with clustered index has 26,330 pages while table with no index has 18,519 which is why table with no index is faster.

Ok, let us do rebuild the index and rerun all the queries with clearing the cache. Executions plans are 50/50.

Let us see number of pages for both cases.

In this you can see pages are high in table without any indexes. However, you will see that querying the entire table is slightly higher in the table with clustered index.

Here is the sample script you can play around.