Tag Archives: Myths

Time to bust some myths around Microsoft SQL Server.

#0370 – SQL Server – Myths – CREATE DATABASE FOR ATTACH_REBUILD_LOG will not work for read-only databases

In the past, I have written about attaching data files to a SQL Server instance when log files are missing by the use of the FOR ATTACH_REBUILD_LOG clause of the CREATE DATABASE statement. Recently, I was referring the CREATE DATABASE documentation on MSDN. It’s a comprehensive document and and the following line for read-only databases caught my attention.

For a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database with a log that is unavailable, you must provide the log files, or the files in the FOR ATTACH clause.

This statement was contrary to what I had observed before. Hence, I decided to re-validate the findings via a demo.

The first step, of course is to create a database.

USE [master];
GO
CREATE DATABASE ReadOnlyDBForAttach
ON PRIMARY (NAME = ReadOnlyDBForAttach_Data,
            FILENAME='C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_Data.mdf'),
   FILEGROUP RODefault (NAME = ReadOnlyDBForAttach_RODefault,
              FILENAME='C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_RODefault.mdf')
LOG ON (NAME = ReadOnlyDBForAttach_Log,
        FILENAME='C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_Log.ldf');
GO

As can be seen from the script referenced above, the database we created as 2 data file groups, one is the default PRIMARY filegroup. Because we cannot make the PRIMARY filegroup READ_ONLY, we will be making the other filegroup (RODefault) READ_ONLY. To add complexity, we will also make it the default filegroup, i.e. any new objects created without specifying a filegroup, it will be created in the filegroup marked as default.

Finally, we will also mark the database as READ_ONLY.

USE [master];
GO
ALTER DATABASE ReadOnlyDBForAttach
MODIFY FILEGROUP [RODefault] DEFAULT;
GO

ALTER DATABASE ReadOnlyDBForAttach
MODIFY FILEGROUP [RODefault] READ_ONLY;
GO

ALTER DATABASE ReadOnlyDBForAttach SET READ_ONLY;
GO

Now, let us check out the database and filegroup properties.

USE ReadOnlyDBForAttach;
GO
SELECT 'Database Properties',
       sd.[name],
       sd.is_read_only,
       sd.is_cleanly_shutdown
FROM sys.databases AS sd
WHERE sd.[name] = 'ReadOnlyDBForAttach';

SELECT 'File properties',
       sdf.file_id,
       sdf.type,
       sdf.data_space_id,
       sdf.type_desc,
       sdf.name,
       sdf.is_read_only,
       sdf.is_media_read_only
FROM sys.database_files AS sdf;
GO

Image showing database and database file properties for the read-only database - ReadOnlyDBForAttach

Read Only database and database file properties

Now, let us detach the database, and delete the log file. (NOTE: We are removing the log file for the purposes of this demo only. Please do not do this in your QA or production environments).

USE [master];
GO
EXEC sp_detach_db @dbname = 'ReadOnlyDBForAttach';
GO

Image showing the log file physically removed from the file system

Read Only Database – Log File Removed

Finally, let us attach the database back to the SQL Server instance using the CREATE DATABASE…FOR ATTACH_REBUILD_LOG clause.

USE [master]
GO
CREATE DATABASE [ReadOnlyDBForAttach]
ON  PRIMARY ( NAME = N'ReadOnlyDBForAttach_Data',
                FILENAME = N'C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO

File activation failure. The physical file name "C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_Log.ldf" may be incorrect.
New log file 'C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_log.ldf' was created.

As can be seen from the message above, the log file was successfully created and the database was successfully attached to the SQL Server instance.

Let us cross-check the database and file properties again:

USE ReadOnlyDBForAttach;
GO
SELECT 'Database Properties',
       sd.[name],
       sd.is_read_only,
       sd.is_cleanly_shutdown
FROM sys.databases AS sd
WHERE sd.[name] = 'ReadOnlyDBForAttach';

SELECT 'File properties',
       sdf.file_id,
       sdf.type,
       sdf.data_space_id,
       sdf.type_desc,
       sdf.name,
       sdf.is_read_only,
       sdf.is_media_read_only
FROM sys.database_files AS sdf;
GO

Read Only Database and Data File Properties After Attach

Conclusion

Contrary to the MSDN remark, a read-only database can be successfully attached to a SQL Server instance even when the log file does not exist by the use of CREATE DATABASE…FOR ATTACH_REBUILD_LOG
A read-only database becomes a read/write database if it has been attached to the SQL Server using FOR ATTACH_REBUILD_LOG and the log file was rebuilt

My findings above do not agree with the MSDN remark. At the moment, I am inclined to believe that this is a bug in the documentation as I have found a couple of years ago as well (see references). Please do share your views on the same in the post comments.

References/Further Reading

SQL Server Myth: Log files are removed when a database is made READ_ONLY [Link]
Creating a database without the log backup file – Error Msg. 5120 [Link]
CREATE DATABASE…ATTACH_REBUILD_LOG resets database recovery mode to SIMPLE [Link]
Setting database to READ_ONLY does not change the file-group properties [Link]
sp_attach_db v/s CREATE DATABASE FOR ATTACH – Number of files limitation; Msg. 8144 (Procedure has too many arguments specified) [Link]
CREATE DATABASE [MSDN Documentation]

Until we meet next time,
Be courteous. Drive responsibly.

#0368 – SQL Server – Myths – Windows Authentication – Windows login name change does not mandate a change to SQL login

2 Replies

In today’s world, mergers and acquisitions of organizations are a reality. Having been through a couple of M&As in the last couple of years myself, I have had a unique opportunity to experience changes which would not be experienced in the normal course of work.

One such change is the changing of the domain logins. M&As often come with renaming of domain logins to confirm to a common standard. The question that came up in our team was:

What happens to our SQL Servers that use windows authentication if the login name changes?

The answer? Nothing. SQL Server continues to work as usual if the login name is the only thing that changed (and your applications do not use the physical “login name” in any way).

Here’s a demo for clarity.

On my VM, I created a user “OldUser” and logged in to SSMS using Windows Authentication when running as that user.

Screenshot showing the use of an existing windows login for authentication into a SQL Server instance.

Using an Old Login to login to SSMS

For the record, we will also execute the following query:

SELECT SUSER_SNAME() AS LoginName,SUSER_SID() AS LoginSID;

OldUser’s SID

Now, because this is a local login, all I do is rename the Windows login using Computer Management. The same behaviour applies to logins renamed via Active Directory as well.

Renaming the “OldUser” to “NewUser”

I load the SSMS again – this time as “NewUser”.

Login to SSMS using the new, renamed login

Notice how the authentication works even though the login name has changed.

Wrapping it up…

Windows authentication in SQL Server is based on the SID. Hence, even though the login name changed, SQL Server allowed me to login because the SID did not change. In fact, this is why when we create or modify a login, the only requirement is to ensure that the SID of the login is same as the one we want to create/modify.

In a practical scenario, the IT teams would not re-generate the SIDs because that would mean too much work – realigning file & folder shares, resetting SSO and what have you. Instead, they simply update the login name (or the human interpretative part of the login).

What this means is that the authentication on the SQL Servers continue to work as usual – even after the login changes. No manual intervention is required!

Note of caution: Because the SQL Login name and the Windows login name are independent, renaming the Windows login will NOT rename the SQL login. The entry under sys.server_principals continues to read the old value. So, if your applications are designed to work based on the login name in sys.server_principals, you need to use ALTER LOGIN statements whenever IT changes the Windows login names.

#0356 – SQL Server – Search with the LIKE operator and the underscore (_) wildcard character

Solution(s)

There are not one, but two possible solutions to choose from to work around this issue.

Using square brackets ([]) around the wildcard character

Because we want to consider the underscore as a character and not as a wildcard, the easiest thing to do would be to surround it with square brackets, similar to what is shown in the query below.

SELECT * FROM @testTbl WHERE Value LIKE 'SQL[_]20%';

Using the ESCAPE keyword

The other option, which I have discussed in one of my earlier posts, is to use the ESCAPE keyword. The ESCAPE keyword works on the basis of a user specified escape sequence. When encountered, the query engine will simply ignore the wildcard character and treat it as a normal character.

SELECT * FROM @testTbl WHERE Value LIKE 'SQL*_20%' ESCAPE '*';

Conclusion

The moral here is that one must always remember the basics – this thumb rule not only applies to software, but in everything that we go about doing in our day to day lives.

The other key learning that I encourage everyone to take from this is that Occam’s Razor is real – from the multiple explanations that a problem may have, the explanation with the least number of assumptions should be selected as the answer. In this case, the simplest theory was – human error, which was indeed the case.

References

Pattern matching in SQL Server [http://msdn.microsoft.com/en-US/library/ms187489(v=sql.105).aspx]
Implementing Windows style wildcard search in SQL Server, using the ESCAPE keyword [https://nakulvachhrajani.com/2013/04/22/0259-sql-server-implementing-wildcard-search-escape-keyword/]
LIKE Operator [https://msdn.microsoft.com/en-us/library/ms179859.aspx]
Best Practices – Avoid wild-card characters at the beginning of a search string [https://nakulvachhrajani.com/2013/10/31/0306-sql-server-best-practices-avoid-wild-card-characters-at-the-beginning-of-a-search-string/]

Until we meet next time,
Be courteous. Drive responsibly.

#0342 – SQL Server – LEN v/s DATALENGTH – A feature comparison

1 Reply

There are few typical questions that developers ask when developing applications or when troubleshooting an issue:

What is the length of this string?
How many bytes does this string take for storage?
How many bytes are occupied in storing this BLOB data?

The answer to all these questions lie in 2 T-SQL functions: The DATALENGTH() and the LEN(), which are often used interchangeably by developers who may not have fully understood the subtle differences between them.

Today, I present before you, a feature comparison of these two functions.

The LEN() function

Purpose: Returns the number of characters of the specified string expression, excluding trailing blanks.

Usage example: The following example demonstrates how to use the LEN() function:

SELECT LEN('Four');
GO
--Result: 4

Behaviour for Unicode Strings: Unicode strings store a wider range of characters and hence the same number of characters occupy more space. The LEN() function reports the number of characters, and not the space occupied within a UNICODE string.

SELECT LEN(N'Four');
GO
--Result: 4

Effect of white spaces: The LEN() function has a very interesting behaviour when the string expression being evaluated is padded with white spaces. The function trims out white spaces after a string, but does consider white spaces padded before the string.

SELECT LEN(' Four');
GO
--Result: 8
SELECT LEN('Four ');
GO
--Result: 4

Behaviour with non-string based data-types: Because the DATALENGTH() function returns the number of bytes consumed, it can work with any expression and return the number of bytes consumed. Here is an example of how DATALENGTH() reports the number of bytes accurately for non-character based data-types:

DECLARE @integerData INT = 4;
SELECT LEN(@integerData);
GO
--Result: 1 (the number of characters)
DECLARE @dataTimeValue DATETIME = GETDATE();
SELECT LEN(@dataTimeValue);
GO
--Result: 19 (the number of characters)

The DATALENGTH() function

Purpose: Check the length of any expression.

Usage example: The following example demonstrates how to use the DATALENGTH() function. From a usage perspective, there is no difference between DATALENGTH() and LEN()

SELECT DATALENGTH('Four');
GO
--Result: 4

Behaviour for Unicode Strings: Unicode strings store a wider range of characters and hence the same number of characters occupy more space. Unlike the LEN() function, the DATALENGTH() function returns the actual number of bytes consumed for the storage of the Unicode value.

SELECT DATALENGTH(N'Four');
GO
--Result: 8

Effect of white spaces: Because the DATALENGTH() function returns the number of bytes consumed, and not the number of characters in an expression, white spaces in a string are reported as-is, without any trimming. Here’s an example:

SELECT DATALENGTH(' Four');
GO
--Result: 8
SELECT DATALENGTH('Four ');
GO
--Result: 8

DECLARE @integerData INT = 4;
SELECT DATALENGTH(@integerData);
GO
--Result: 4 (the number of bytes consumed for storage)
DECLARE @dataTimeValue DATETIME = GETDATE();
SELECT DATALENGTH(@dataTimeValue);
GO
--Result: 8 (the number of bytes consumed for storage)

Summary

The feature comparison between LEN() and DATALENGTH() can be made in the following table:

	LEN()	DATALENGTH()
Purpose	Returns the number of characters of the specified string expression, excluding trailing blanks.	Returns the number of bytes used to represent any expression.
Effect of Unicode	Number of characters in the expression	Number of bytes used for storage
Effect of white spaces	Prefixed spaces: Considered as part of length Suffixed spaces: Trimmed	Both prefixed & suffixed spaces are considered
Behaviour with non-string data-types	Supported, but reports the number of characters as if it were a character string	Reports the number of bytes used for storage

#0340 – SQL Server – Myths – ORDER BY can only work on columns fetched in the SELECT clause

6 Replies

We were recently working on an ad-hoc reporting request that involved customizations to an existing report (more specifically, customizations to an existing query). The query was to be embedded into a custom Excel reporting solution.

One of the requirements that we had was to ensure that by default the results appear in a given order. It’s a fairly common request, but what made this one stand out is the fact that we were asked to perform the sort on a column that was not part of the SELECT statement.

The myth

Every result that is processed by Microsoft SQL Server has two elements – a set of records and a set of columns, common to all data represented by the set of records.

The normal understanding of Microsoft SQL Server’s ORDER BY clause is that it works on the “selected” set of records. This is often misinterpreted to mean that it works on the columns selected, i.e. one can only use ORDER BY on columns which are selected in the SELECT clause.

So, what’s the truth and what’s going on behind the scenes.

The test

The following is a simple query that fetches details about some Employees from the HumanResources schema of the AdventureWorks2012 sample database. The only catch is that it is performing an ORDER BY operation on the DepartmentId column, which is not included in the SELECT clause.

USE AdventureWorks2012;
GO
--Notice that DepartmentId used in the ORDER BY is not fetched
--in the results returned by the SELECT clause
SELECT hredh.BusinessEntityID,
hre.JobTitle,
--hredh.DepartmentID,
hrd.Name,
hredh.StartDate,
hredh.EndDate,
hredh.ShiftID,
hrs.Name
FROM HumanResources.Shift AS hrs
INNER JOIN HumanResources.EmployeeDepartmentHistory AS hredh ON hredh.ShiftID = hrs.ShiftID
INNER JOIN HumanResources.Employee AS hre ON hredh.BusinessEntityID = hre.BusinessEntityID
INNER JOIN HumanResources.Department AS hrd ON hredh.DepartmentID = hrd.DepartmentID
ORDER BY hredh.DepartmentID;
GO

Attempting to execute this query does not return any error. The results do not appear to have been returned in any specific order (un-commenting the DepartmentId from the SELECT clause and running the query again will clear out the confusion).

Studying the execution plan

So, the question comes – what’s going on in the background that helps Microsoft SQL Server perform an ORDER BY on a column (or a set of columns) which are not even selected?

A quick look at the execution plan reveals the truth – DepartmentId is actually fetched from the underlying tables, and is filtered from the final result once the ORDER BY/Sort operation is complete.

This is identical to the execution plan produced if we keep the ORDER BY in the SELECT clause.

Conclusion

This proves that when an ORDER BY operation is performed, the columns required to perform the SORT need to be fetched by the database engine. This is irrespective of whether the query needs a particular column to be returned via the SELECT clause.

While the database engine will fetch the column as part of data retrieval, there is no restriction that mandates the presence of a column in the SELECT if a sort/ORDER BY is being done on that column in the query.

A Word of caution

While it is legal to have an implementation as highlighted above, be careful when combining this with the DISTINCT and TOP clauses – because the column(s) on which the ordering/sorting is done are not available, it can result into query results which are difficult to understand.

Until we meet next time,

Be courteous. Drive responsibly.

SQLTwins by Nakul Vachhrajani

SQL Server tips and experiences dedicated to my twin daughters.

Tag Archives: Myths

#0370 – SQL Server – Myths – CREATE DATABASE FOR ATTACH_REBUILD_LOG will not work for read-only databases

Conclusion

References/Further Reading

#0368 – SQL Server – Myths – Windows Authentication – Windows login name change does not mandate a change to SQL login

Wrapping it up…

Further Reading

#0356 – SQL Server – Search with the LIKE operator and the underscore (_) wildcard character

Solution(s)

Using square brackets ([]) around the wildcard character

Using the ESCAPE keyword

Conclusion

References

#0342 – SQL Server – LEN v/s DATALENGTH – A feature comparison

The LEN() function

The DATALENGTH() function

Summary

Further Reading

#0340 – SQL Server – Myths – ORDER BY can only work on columns fetched in the SELECT clause

The myth

The test

Studying the execution plan

Conclusion

A Word of caution