3

Let's say I have an app that uses this connection string to connect to a SQL Server using "DB1" as the default database (a.k.a. initial catalog):

Server=myServerAddress; Database=DB1; User Id=myUsername; Password=myPassword;

But all queries executed through this connection target other databases, never DB1. (Example: SELECT * FROM DB2.dbo.Table1)

Can/will DB1 possibly be used for anything, even though it is not referenced in any queries? I'd like to hear about all possibilities no matter how obscure. (For example, could there be differences in options, cursors, table locking, query performance...?)

I would assume not, except that today I saw strange performance issues with DB1 that are making me second guess my assumption. It could be a red herring but regardless I'd like to make sure I fully understand the role of the default database / initial catalog.

4

Different performance of the same query typically indicates different execution plans. The database context generally won't yield different plans (and performance) but there are some cases where it could.

The default collation of the current database context determines the collation of literals and parameters. Below is an example that shows a slightly different execution plan when the query differs only by database context:

CREATE DATABASE TestDatabase
    COLLATE Latin1_General_CI_AS;
GO
CREATE DATABASE TestDatabase_SC
    COLLATE Latin1_General_100_CI_AS_SC;
GO

USE TestDatabase;
GO

CREATE TABLE dbo.TestTable(
    Col1 varchar(10) CONSTRAINT PK_TestTable PRIMARY KEY
    );
INSERT INTO dbo.TestTable VALUES('A');
GO

--query plan in TestDatabase context shows seek predicate: Seek Keys[1]: Prefix: [TestDatabase].[dbo].[TestTable].Col1 = Scalar Operator([@1])
SELECT Col1 FROM TestDatabase.dbo.TestTable WHERE col1 = 'A';
GO

USE TestDatabase_SC;
GO

--query plan in TestDatabase_SC context shows seek predicate: Seek Keys[1]: Prefix: [TestDatabase].[dbo].[TestTable].Col1 = Scalar Operator(CONVERT_IMPLICIT(varchar(8000),[@1],0))
SELECT Col1 FROM TestDatabase.dbo.TestTable WHERE col1 = 'A';
GO

I wouldn't expect a significant performance difference here because SQL Server coerces a sargable expression even though the literal and column data types differ.

The current database context also determines SET options used when not explicitly set by the client API or SET statement. This may result in different execution plans, especially for legacy APIs.

2

strange performance issues with DB1

This needs a bit more explanation. What exactly is meant by "with DB1"? What is DB1 used for since the app clearly is not using it? And what qualifies as "strange"? Are these performance issues being seen in the app?

Can/will DB1 possibly be used for anything, even though it is not referenced in any queries?

It depends on what the code is doing and what you mean by "use":

  1. At bare minimum, the fact that you connect to Database1 will mean that a Shared lock will be taken on Database1 (resource_type = "DATABASE", request_owner_type = "SHARED_TRANSACTION_WORKSPACE"). This could result in blocking for operations that need a more restrictive lock on Database1.

  2. Objects such as Stored Procedures, Functions, and Views, use the collation of the database in which they exist. Meaning, string literals and local string variables inside of Database2.SchemaName.StoredProcA will use the default collation of Database2.

  3. Current database for code in objects such as Stored Procedures, Functions, and Views, is the database in which they exist. Hence code running inside of Database2.SchemaName.StoredProcA should be a non-issue for Database1.

  4. Collation of literals and local variables for ad hoc queries is affected. This is generally not a problem, thanks to Collation Precedence. For example, the following should work correctly:

    SELECT alias.SomeColumn
    FROM   Database2.SchemaName.TableName alias
    WHERE  alias.AnotherColumn = @ParameterFromAppCode; -- uses Database2 collation
    

    However, there are a few situations in which this can make a functional difference:

    DECLARE @LocalVariable VARCHAR(50); -- uses Database1 collation
    
    SELECT @LocalVariable = alias.SomeColumn
    FROM   Database2.SchemaName.TableName alias
    WHERE  alias.AnotherColumn = @ParameterFromAppCode; -- uses Database2 collation
    
    IF (@LocalVariable = 'SomeValue') -- uses Database1 collation
    BEGIN
      ...
    

    That IF statement might not behave as expected if there are sensitivity differences between the database. Meaning, if Database1's default collation is case-sensitive and Database2's default collation is case-INsensitive, then one might expect "A" to equal "a", yet that IF statement will evaluate to false because the "current" database is using a case-sensitive collation.

    Also, if you are using VARCHAR data with different Code Pages between the databases, then certain characters might not translate. For example:

    CREATE TABLE #WrongCodePage (SomeText VARCHAR(50) COLLATE Hebrew_CI_AS);
    INSERT INTO  #WrongCodePage (SomeText) VALUES (0xF9C8D1ECE5C9ED);
    
    SELECT SomeText FROM #WrongCodePage; -- שָׁלוֹם
    
    DECLARE @Shalom VARCHAR(50);
    SELECT @Shalom = SomeText FROM #WrongCodePage;
    SELECT @Shalom; -- only works if DB collation uses Code Page 1255
    

    Returns:

    שָׁלוֹם
    
    ???????
    

    Similarly, if you have NVARCHAR data and Database2 has a collation that supports Supplementary Characters (i.e. full UTF-16 support via collations ending in _SC) but Database1 uses a collation that does not support them, then things might not work as expected:

    -- only works if the "current" DB uses a collation ending in `_SC`
    SELECT NCHAR(127137) -- 🂡 (Ace of Spades)
    

    And:

    CREATE TABLE #PokerHands (Name NVARCHAR(50),
                              Cards NVARCHAR(50) COLLATE Latin1_General_100_CI_AS_SC);
    INSERT INTO #PokerHands (Name, Cards) VALUES (N'Straight (Royal) Flush', N'🂡🂮🂭🂬🂫');
    
    DECLARE @LocalVariable NVARCHAR(50);
    SELECT @LocalVariable = alias.Cards
    FROM   #PokerHands alias;
    
    SELECT Cards, LEN(Cards) AS [LEN], DATALENGTH(Cards) AS [DATALENGTH],
           SUBSTRING(Cards, 2, 1) AS [SecondCardFromTheLeft]
    FROM  #PokerHands;
    
    SELECT @LocalVariable AS [LocalVar], LEN(@LocalVariable) AS [LocalVarLEN],
           DATALENGTH(@LocalVariable) AS [LocalVarDATALENGTH],
           SUBSTRING(@LocalVariable, 2, 1) AS [SecondCardFromTheLeft];
    

    Returns:

    Cards     LEN          DATALENGTH          SecondCardFromTheLeft
    🂡🂮🂭🂬🂫    5            20                  🂮
    
    LocalVar  LocalVarLEN  LocalVarDATALENGTH  SecondCardFromTheLeft
    🂡🂮🂭🂬🂫    10           20                  �
    

    Notice how LEN and SUBSTRING returned different values.

  5. If you use Table Variables in any ad hoc queries, then be aware that the default collation for string fields that do not specify their own COLLATE clause will be the current database's default collation (i.e. Database1's default collation). Just like with string literals and local variables, certain operations might not behave as expected: simply pulling in data from databases with different Code Pages (affects VARCHAR data) can mangle the data, and if the current database is not using a collation that supports Supplementary Characters yet the data is NVARCHAR and has Supplementary Characters in it, then pulling in the data will be fine, but certain string functions/operations will return incorrect values.

  6. CURSOR default for GLOBAL vs LOCAL. This would likely be more of a functional difference than a performance difference.

  7. Session properties (ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING, etc) should not be an issue. While differences in them can affect queries, there would be no visible indication of DB1 being involved. Besides, these properties are typically set by the client: .NET SqlConnection, ODBC, etc.

  8. Transactions (logging and locking) affect each database containing objects that are a part of the Transaction. If none of the objects are in Database1, then Transactions shouldn't have any impact on it.

1

DB1 will be used as the default/current database for that connection string unless a USE statement changes the current database, and will show up in some of the DMVs, as well as sp_who and sp_who2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.