Category: SQL Server

To use Help in SQL Server 2014 in offline mode you should perform the following steps:

1) install Documentation Components – at least you need the Help Viewer to see the content of help library

2) download the installer from Microsoft site Product Documentation for Microsoft SQL Server 2014 for firewall and proxy restricted environments

3) run the installer SQLServer2014Documentation_August2014_EN.exe

4) unzip files to a folder (I prefer to manually create a folder and unzip files there)

5) launch Help Settings

This can be made from Windows Start menu (Microsoft SQL Server 2014 -> Documentation & Community -> Manage Help Settings) or from SQL Server Management Studio (Help -> Manage Help Settings)

6) click Install content from disk in the Help Library Manager window

7) locate the folder to which you have unzipped the files at step 4 and select the file HelpContentSetup.msha, then press Open

8) press Next in the Help library Manager

9) click the links in Actions column to select the desired sections, then press Update

10) after you’ll see a message “Finished updating” press Finish button, then press Exit

11) now you can delete the folder with unzipped files

Tip: to check that you are really using the local help start Manage Help Settings again, click the first link Choose online or local help, and ensure the setting is correct.

When you run a database from previous version on a SQL Server instance with a higher version, the database is automatically upgraded to the version of the instance. It happens on:

1. attaching a database from older version;
2. restroring a database from older version;
3. and, of course, when you make an in-place upgrade of SQL Server.

For example, when I attach a database from SQL Server 2008 R2 in SQL Server 2014, I’ll get these messages telling about database upgrade.
Converting database 'test' from version 661 to the current version 782.
Database 'test' running the upgrade step from version 661 to version 668.
Database 'test' running the upgrade step from version 668 to version 669.
...
Database 'test' running the upgrade step from version 781 to version 782.

The version numbers you see in Messages window is internal database version numbers. The list of these numbers can be found at this post.

Be aware this is a one-direction upgrade. There’s no possibility to downgrade the database nor open it in SQL Server with original version or any intermediate version prior to upgraded one. This means that if your database was created in SQL Server 200, then you opened it in SQL Server 2014, you never can open in SQL Server version from 2005 till 2012. The error message is self-explanatory.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'test'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'test' cannot be opened because it is version 782. This server supports version 663 and earlier. A downgrade path is not supported.

Another issue is that you can NOT prevent SQL Server from upgrading the database. If you make the primary database file read-only in order to get a read-only database and try to open it in a higher version, you’ll get an error 3415.
Msg 3415, Level 16, State 2, Line 3
Database 'test' cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.


I’ve encountered a strange situation when I could not find SQLServer:Databases group of counters in Windows Performance Monitor.

It was happened on my test system with SQL Server 2014. I checked it on production SQL Server 2008 R2 – everything is OK.

The most important thing to solve this problem was found in Windows Application Log. It was error 8310.

Simple search in Google with the error message leads me to the description of that particular problem. It’s on SQL Server 2014 installation and is corrected by installing Cumulative Update 2. I’ve installed CU7 and now it works.

You can check all CUs in KB2936603 SQL Server 2014 build versions

The latest Service Pack can be checked in KB2958069 How to obtain the latest service pack for SQL Server 2014

SQL Server has a capability to create many database objects with the same name. But they must belong to different schema. Let’s look how we can deal with this.

My simple test case:

1. Create a new schema tst
2. Create a new login test
3. Create a new database user test for that login and assign schema tst to him
4. Make the user test to be the owner of schema tst
5. Create two table with the same name in dbo and tst schema
6. Insert different values to the correspondent tables

Script can be downloaded from db_object_names_resolution

This time we run SELECT command without schema name. What should we see? ‘dbo’ or ‘tst’?
The answer is ‘dbo’.

Try to change execution context to user test and run the same command.

We’ve just got the results from the table in tst schema. Why?
If you look at the User Mapping in the Login Properties window, you see the user and default schema for that particular login.

Do you understand what happens?
When I connect to SQL Server as Administrator, I run the query as user dbo and have a default schema dbo. The results are taken from [dbo].[Clients].
When I connect as test, I run the query as user test, so the default schema for that user is tst. The results are taken from [tst].[Clients].

The next question – what will be if the user test run the query from table in dbo schema without denoting the schema name? There are 2 possible answers: the query will return the results from that table or will not be executed. What do you think about that?

To sum up, I would recommend to use unique names for database objects even if they belong to different schema.

Hint: the first search to resolve the database object’s name is made in user default schema, then in dbo schema.

As you know SQL Server can hold NULL values in table’s columns. It usually means that a value of the column is unknown. For example, what is better to answer the question about on which floor does your friend live: on the 1st or 2nd, maybe 5th, N-th or just say “I’m afraid I don’t know exactly”. So NULL values can save this situation.

But when we try to match values we should be cautious about NULL values. Let’s look at the following examples.
I create two tables with sample data in tempdb.

In my previous post I compared one big DECLARE statement vs many small statements. The result was there’s no significant difference between these two approaches. This time I wonder is assignment sensitive to how it is implemented? I mean if you will use one big SELECT command or many small SELECT or SET commands.

At first I would clarify that there’s no difference to SQL Server how you write a single assignment – with SELECT or SET command. Just look at the execution plan – each command imply the ASSIGN operation.

For experiment I use stored procedure from previous test and write a new one that implement one assignment in SELECT command. If we look at estimated execution plan we’ll see that first stored procedure OneBigDeclare has 30 ASSIGN operation, on the other side the stored procedure OneBigDeclareWithBigSelect has only one ASSIGN operation. Probably the speed of the second SP will be higher.

All source code can be downloaded from assignment_execution_test

My results
Average execution time of a cycle:
OneBigDeclare – 77.98ms
ManySmallDeclare – 140.22ms