In a perfect world, any table once designed would never undergo any modifications for the entire life of the product. However, nothing’s perfect and as they say – the only thing that’s constant is that nothing’s constant. Similarly, requirements change and so do table designs. However, there is one little aspect of SQL Server that most developers and novice administrators do not quite understand correctly.

Assume that we have the following setup:

CREATE DATABASE foo
GO

USE foo
GO
CREATE TABLE MyTbl (MyName CHAR(4030))
GO

INSERT INTO MyTbl
SELECT REPLICATE('a',4030)
GO

Let us assume that we need to modify the structure of our table:

ALTER TABLE MyTbl ALTER COLUMN MyName CHAR(4031)
GO

While the ALTER TABLE statement should work just fine, it does not. What we encounter is:

Msg 1701, Level 16, State 1, Line 2

Creating or altering table ‘MyTbl’ failed because the minimum row size would be 8068, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

So, what went wrong? We just wanted to increase one byte!

The post-mortem

Most of us have a tendency not to read error messages. Any message, and we just click “OK” to it. Similarly, if SQL throws anything in red fonts, we simply hit F5 again just in case SQL was not in the right spirits the first time around.

However, in this case, a close look at the message is the key to understanding what’s going on. Let’s start with the final sentence: This exceeds the maximum allowable table row size of 8060 bytes.

What is this number? 8060 bytes? It is the page size of a data page.

Per Books On Line (Understanding Pages & Extents), here’s how the numbers work out:

Data Page length in bytes	8192
Page Header	96
Overhead	36
Total Data space available on page = data page length – header length – overhead	8060

Once we have established this, let’s study what we are trying to do:

	Column properties	Data type length (bytes)	Current Row size in bytes
Current	4030	1	4030
New	4031	1	4031
TOTAL			8061

So, we are trying to take a row (because we only have one column, the column size is the row size) of 4030 bytes, and increase it to 4031 bytes, which should be okay because it ultimately remains under the 8060 byte limit. But that’s where looks are deceiving.

The ALTER Table statement attempts to expand the column “in-place”, i.e. it copies the data over to a new column with the new length specifications on the same row, making the row total now 8061 bytes, which is more than the 8060 maximum.

Now that we know we have a row that’s larger than the maximum number of bytes a page can hold, we move down the Books On Line article to see a section for Large Row support, which mentions – “Rows cannot span pages, however portions of the row may be moved off the row’s page so that the row can actually be very large.”

Rows cannot span pages – everything boils down to this simple statement.Because our request causes the row to exceed 8060 bytes “in-transit”, the ALTER TABLE fails.

You can get more information on Rows exceeding the 8K limit here: http://msdn.microsoft.com/en-us/library/ms186981.aspx

Workarounds

There are a couple of workarounds to this problem. However, all are very crude mechanisms with their own pros & cons and must not be used unless absolutely necessary.

1. The most obvious one – if possible, reduce the data within a row so that we do not exceed the allowed limits
2. Create a new table with the new specifications, copy all the data over, transforming it on the way. Finally, drop the old table
3. Export the existing data to another table/s, truncate the existing table. Make necessary changes and import data back into the original table

I hope that you found this behaviour interesting and also enjoyed learning about the internal workings of Microsoft SQL Server as much as I did.

Until we meet next time,

Be courteous. Drive responsibly.

I had an interesting time at the office recently. We were undertaking a performance tuning exercise for the canned reports of an OLTP that were migrated from Crystal Reports to SSRS, and had to come up with some ground rules for all database developers to follow when re-engineering queries.

One of the basic things to be ensured during a performance tuning exercise is that the queries must be written in such a way that once an execution plan is generated, SQL Server reuses a given execution plan for subsequent executions of the same query.

The above statement seems very simple, but there is a lot of between-the-lines reading that needs to be done before the true meaning of the statement can be understood.

Some basics – Execution & Query Plans

When a query is submitted to the SQL Server database engine for the first time, the query optimizer processes the query (query processing is a huge topic, and out-of-scope for this discussion. For more information, you can refer the BOL page on Query processing here) and generates a two execution plans:

1. For serial execution
2. For parallel execution

Each Execution plan (BOL Reference here) consists of:

1. Query Plan
   • This is the bulk of the query plan, and does not contain the Execution context
2. Execution Context
   • The execution context is unique for each user running the plan because it contains the parameter values for each execution per user

The prime concern is the re-use of the query plan portion of the Execution Plan. This is because generation of execution plans takes time & resources – both of which are scarce.

The importance of using the proper case when writing T-SQL Queries

The query plan, is internally stored as a text value – a string, which is what we see if we execute a query with the SHOWPLAN operator.

When a query is submitted, it is parsed and one of the first things that happens afterwards is that Microsoft SQL Server attempts to find a suitable match for the query plan from the plan cache. This is done via a simple, but crucial to understand string comparison. The problem is that most developers fail to realize that this comparison is a case-sensitive comparison.

Let’s understand this via a simple demo.

Demo

We will begin by clearing out the procedure cache.

DBCC FREEPROCCACHE
GO

Next, let us run a simple T-SQL query (twice) to fetch a list of all the employees in an organization:

USE AdventureWorks2008R2
GO

SELECT * FROM HumanResources.Employee
GO 2

Now, let us use the DMVs – sys.dm_exec_cached_plans, sys.dm_exec_query_plan and sys.dm_exec_sql_text to look at the procedure cache:

--Run this in a separate window

USE AdventureWorks2008R2
GO

SELECT cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan, cp.plan_handle 
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st
GO

You can see from the usecounts column that Microsoft SQL Server used the query plan it generated twice.

Now, assume that different developers working on the same application used some other variants of the same query:

USE AdventureWorks2008R2
GO

SELECT * FROM HUMANRESOURCES.EMPLOYEE
GO

SELECT * FROM HumanResources.Employee
GO;

Let us take a look at that plan usage again:

--Run this in a separate window

USE AdventureWorks2008R2
GO

SELECT cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan, cp.plan_handle 
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st
GO

You can clearly see that SQL Server used a different version of the query plan for each of the two query variants.

Moral of the story

When writing queries, please avoid:

1. Changes to letter case in the text
2. Changes to white space
3. Changes to literal values
4. Changes to text inside comments

Most important: If you are using Microsoft SQL Server 2008 and above – always use intelli-sense to be safe!

Until we meet next time,

Be courteous. Drive responsibly.

“Long years ago we made a tryst with destiny, and now the time comes when we shall redeem our pledge, not wholly or in full measure, but very substantially. At the stroke of the midnight hour, when the world sleeps, India will awake to life and freedom.” ~Pandit Jawaharlal Nehru

These words were spoken by India’s first Prime Minister as part of a speech delivered in the Constituent Assembly, New Delhi, on August 14, 1947, on the eve of the attainment of Independence. Today is India’s 64th Independence Day, and a time to remember the thousands of individuals who laid down their lives in the struggle for independence.

It is a holiday here in India, and if you are working today, I hope that you get to go home on time (if not early) and that you have an otherwise uneventful Monday. I am also on a long-pending holiday. It’s been non-stop work since the last 1.5 years and I was on the edge of pulling my hair out!

I will therefore, present a selection from the posts that I have written till date. These may not have the highest read counts, but they are somehow near & dear to my heart. Some have even come as a result of a query that you, the kind and respected reader, a friend or a colleague might have asked.

Posts:

1. CAST v/s CONVERT – A Performance Comparison
2. CAST v/s CONVERT – Is there a difference as far as SQL Server is concerned? Which is better?
3. Measuring the number of rows in a table – Are there any alternatives to COUNT(*)?
4. The multiple connections of SSMS
5. SQL Server Myth- Log files are removed when a database is made READ_ONLY
6. “GO” as Batch Separator – Customize batch separator in SSMS & SQLCMD – What would you use instead of GO?
7. The Ghost of the Y2K
8. UNIX timestamp and the Year 2038 problem
9. Questions from you!
• “Ad hoc update to system catalogs is not supported.” when using RECONFIGURE
• Clearing SSMS History
10. Other contributions:
• Securing TRUNCATE permissions in Microsoft SQL Server – SQLAuthority.com
• Connection Strings 101 – SQLServerCentral.com
• Deleting large number of rows from a table & a heap in a VLDB – SQLServerCentral.com
• Guest Post for Pinal’s series on Interview Questions & Answers – SQLAuthority.com

Series:

1. Underappreciated features of Microsoft SQL Server
2. VTD Recorded session & PPT – Learn Underappreciated Features of Microsoft SQL Server
3. Tech-Ed India 2011 – Part 01, Part 02, Part 03
4. T-SQL Worst Practices – a compilation from Jacob’s session at Tech-Ed 2011 – Part 01, Part 02, Part 03
5. T-SQL Debugging
• Enabling the Debugger
• Basic debugging demo
• Troubleshooting T-SQL Debugger – Connection Errors & Firewall warnings, MSDBG2.dll

On this day, I urge you all to Be courteous, Drive responsibly.

SQL 11 (“Denali”) has been around us for quite some time now and is almost ready for making a dash towards the “Release Candidate” (RC) state with Community Technology Preview (CTP) 03 being released last month. That being said, this post might seem a little late, but I tend to unconsciously study the new productivity features of SSMS that we, as the developer community tend to use more.

One such productivity feature that I welcome about the SQL 11 (“Denali”) is the fact that Intellisense in SSMS now uses a “like” search. To demonstrate my point, let’s try to search for the AdventureWorks database (While I am demonstrating this with database names, you would notice that it is true for all database objects).

SQL Server 2008 SP2	SQL 11 (“Denali”) CTP 03

Here’s how this feature is useful.

If we want to look for all sales orders related information, we can be pretty sure that the information would be in the Sales schema. We can search for the word “orders” and get all the objects that have the word “orders” in their name, giving us the opportunity to scan through a filtered list, thus increasing our chances of remembering the correct object name.

I am bad at remembering names and therefore, this is a productivity enhancement for me. As time goes along, I will keep sharing other such enhancements that I use with SQL 11 (“Denali”), CTP03.

Until we meet next time,

Be courteous. Drive responsibly.

Today, I attempt to bust a myth related to transaction log files for read-only databases in Microsoft SQL Server. Read-only databases, as the name suggests are databases containing static data. Quite often, applications use read-only copies of 3rd party data, restored in the data-center as read-only databases. For instance, there are services that provide a universal lookup for ISO codes for currencies & countries. Countries and currency codes do not change daily, and it makes great sense to convert the databases to read-only databases.

Just recently, I read in a book something that jolted me because it was an authoritative book. The sentence goes something like:

“When a database is placed in READ_ONLY mode, SQL Server removes any transaction log file that is specified for the database.”

The above statement is incorrect. What is correct is that the transaction log continues to exist – irrespective of whether or not the database is in READ_ONLY mode.

When in doubt, I always believe that a simple test should be done to confirm. So, here’s a very simple, do-it-yourself test that you can run on your test/development server:

--Step 01: Create a test database & confirm that it is not READ_ONLY
USE master
GO
CREATE DATABASE ReadOnlyTest
GO

SELECT sdb.is_read_only,
       sdb.* 
FROM sys.databases sdb 
WHERE sdb.name = 'ReadOnlyTest'

--Step 02: Confirm that the database has 2 files - one primary data file, one log file
USE ReadOnlyTest
GO
SELECT 'Before',* FROM sys.sysfiles
GO

--Step 03: Make Database READ_ONLY and confirm
USE master
GO
ALTER DATABASE ReadOnlyTest SET READ_ONLY
GO

SELECT sdb.is_read_only,
       sdb.* 
FROM sys.databases sdb 
WHERE sdb.name = 'ReadOnlyTest'

--Step 04: Confirm that the database still has 2 files - one primary data file, one log file
USE ReadOnlyTest
GO
SELECT 'After',* FROM sys.sysfiles
GO

--Step 05: Finally drop the test database as cleanup
USE master
GO
DROP DATABASE ReadOnlyTest
GO

Here’s what you would see:

What we know for sure is that the log files are not “removed”. Using the undocumented DBCC commands like DBCC LOGINFO and DBCC LOG, I could determine to a fair level of confidence that the log file is not used for some common read-only operations (SELECT, BACKUP).

Do you know why the transaction log file is required for READ_ONLY databases? Do let me know. I look forward eagerly to your response.

Until we meet next time,

Be courteous. Drive responsibly.