Whenever we talk about inclusive date comparison & filtering in T-SQL, the only two options that come to mind are the BETWEEN clause and the greater than (>) and less than (<) operators paired with the equal to operator. Today, let us attempt to find out if there is a difference between the two.

Let me mention that our study today will be restricted to dates only. DATETIME values need extra “precautions” on the part of the query developer to cover the time portion in the comparison. However, most legacy systems (and as we would see from the examples in the AdventureWorks2008R2 database), use separate storage for date and time values and therefore, the need to filter on the date value is a very realistic user scenario.

Dissecting BETWEEN

Without spending much time, let’s get right along to the simple test. Let’s assume that once a year, the Adventure Works Bicycle Company prepares a list of all employees who were hired in the organization 10 years ago, (because we are in 2011, this would be in 2001) to send out an organization-wide recognition of service notification. Quite simply, the query would be something like:

SELECT * FROM HumanResources.Employee
WHERE HireDate BETWEEN '2001-01-01' AND '2001-12-31'
GO

We get 2 records in return:

Now, let us study the actual execution plan to find out what goes on under the covers.

As it turns out, BETWEEN is internally implemented by pairing a set of comparison operators with the equal to operator!

Conclusion

We can safely conclude that BETWEEN is just a facade for the comparison operators. The only difference is in the fact that comparison operators, by themselves offer exclusive comparison (i.e. the comparison excludes the value being compared to). For an inclusive comparison, they need to be paired with the equal to (=) operator – something which the BETWEEN does out-of-the-box.

So is BETWEEN really required?

I studied the Books-On-Line pages for BETWEEN and the Comparison operators, and could not find any difference other than the one mentioned above (about BETWEEN being inclusive). So, the big question now becomes whether BETWEEN is really required? Wouldn’t our code be much better if we avoid the need for Microsoft SQL Server to transform the between into comparison operators?

Well, in my humble opinion, that’s where the issue is. We, as developers and ultimately, humans, tend to make mistakes. The compilers warn us of syntactical issues with our code, but there is nothing that would warn us about a logical bug. A single miss in placing an equal to operator (or placing of an operator in excess), might result in code that would be logically incorrect. Using BETWEEN when we need inclusive comparison just helps in avoiding such errors.

So, BETWEEN is required – it’s not mandatory, but recommended. And that’s why it’s a T-SQL coding best practice.

Interested in more such surprises?

If you are interested in more such surprises, please do check out my post on the differences between CAST & CONVERT here: http://beyondrelational.com/blogs/nakul/archive/2011/07/18/cast-v-s-convert-is-there-a-difference-as-far-as-sql-server-is-concerned-which-is-better.aspx.

Until we meet next time,

Be courteous. Drive responsibly.

Formula1 is the pinnacle of motorsports – a test of performance, integrity & robustness. The cars on the race track are technological marvels – they contain technologies that would build the cars of the future – technologies like KERS to harness braking energy for a power burst and DRS to reduce drag being the latest additions to the list this year.

I am a fan of the sport, but little did I know that the subject of my passion – Microsoft SQL Server – is closely associated to it.

A modern F1 car uses a lot of embedded electronics. It has an onboard Electronic Control Unit (ECU) that connects to 100+ onboard sensors which monitor and control critical operations like engine configurations, transmission, pit-lane communications and a lot more. As the car moves along the track at around 200miles per hour, it generates and transmits data to the engineers in the pit-garage at a rate of 100kilobytes to 0.5megabytes per second – totaling to about 2GB/race.

This streaming data is analyzed in almost real-time by the engineers who, with using historical data provide near instant analysis help shape the race strategy and ultimately lead a driver to become a World Champion and earn a Constructors trophy for a manufacturer.

The Case Study

Together with its technical partner Microsoft, McLaren Electronics is the official supplier of engine control units (ECUs) and associated data systems to all of the teams competing in the Fédération Internationale de L’Automobile Formula One World Championship annually between March and November.

Business Problem: “McLaren Electronics needed to replace file-based storage with a relational database for the more than 2 terabytes of engine control unit (ECU) data collected each year by a typical Formula One racing team.”

Solution: “The company is using the FILESTREAM feature of Microsoft® SQL Server® 2008 to store ECU data in a relational database while providing rapid access to the data streams for users.”

How SQL Server helps analyze F1 Racing Data: http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000001476

How SQL Server helps access huge data stores for F1 racing teams: http://www.microsoft.com/casestudies/Microsoft-SQL-Server-2008-R2-Enterprise/McLaren-Electronic-Systems/Firm-s-Solution-Accelerates-Access-to-Huge-Data-Stores-for-Formula-One-Race-Teams/4000009547

A brief video from Microsoft Showcase: http://www.microsoft.com/showcase/en/us/details/be728b1e-7f78-470f-be7e-4003b023ff1c

This week-end…

This week-end (September 09-11, 2011), when you enjoy the race at Monza in Italy (are you a Ferrari fan? If yes, this is your home race!), do not forget that deep inside the pit-garages are many instances of Microsoft SQL Server crunching huge amounts of data that help shape the race, and bring tomorrow’s technology on the road, today.

By the way, how many of you are coming over next month to the first ever Grand Prix of India at the Buddh International Circuit at Delhi, India?

Finally, remember that your road to/from work is not a Formula1 race-track. Until we meet next time,

Be courteous. Drive responsibly.

Microsoft SQL Server is home to many myths and legends, and I have developed an interest in knowing about them. It is really a very interesting exercise to know about these myths, and prove them incorrect if the situation permits.

The Myth

One such myth is that a table name in a Microsoft SQL Server database cannot be the same as the database name.

The application that I work on does not have such a scenario. I had never ever heard about anything remotely similar, and therefore, I had to investigate this one. So, let’s see if this one is true or not via a simple DIY ("Do It Yourself") test.

The DIY Test

I have always believed that a simple test can go a long way in clearing out the concepts. So, let’s begin by connecting to a SQL Server 2008 R2 instance, and checking if SQL Server allows us to create a table of the same name as a database.

/*
    WARNING - THIS CODE IS PRESENTED AS-IS AND WITHOUT WARRANTY.
              PLEASE DO NOT USE IT IN PRODUCTION/QA ENVIRONMENTS
*/
USE AdventureWorks2008R2
GO

CREATE TABLE AdventureWorks2008R2 (MyKey INT)
GO

Next, let’s insert a few values:

/*
    WARNING - THIS CODE IS PRESENTED AS-IS AND WITHOUT WARRANTY.
              PLEASE DO NOT USE IT IN PRODUCTION/QA ENVIRONMENTS
*/
USE AdventureWorks2008R2
GO
INSERT INTO AdventureWorks2008R2 VALUES (1), (2), (3)
GO

Finally let’s attempt to select from this new table followed by necessary cleanup:

/*
    WARNING - THIS CODE IS PRESENTED AS-IS AND WITHOUT WARRANTY.
              PLEASE DO NOT USE IT IN PRODUCTION/QA ENVIRONMENTS
*/
USE AdventureWorks2008R2
GO
SELECT * FROM AdventureWorks2008R2
GO

--Cleanup
DROP TABLE AdventureWorks2008R2
GO

Surprised? Don’t be. It’s perfectly legal to have a table name same as the database name.

So, what’s going on here?

Great question! That’s what one should be after.

A table is ultimately a database object. The database object name is referred to as its identifier. If we look into MSDN for the rules of naming identifiers (http://msdn.microsoft.com/en-us/library/ms175874.aspx), we see that the rules for naming database objects can be summarized as under:

1. First character must be one of:
   • A Unicode letter (Latin characters from a-z, A-Z and characters from other languages)
   • The Underscore ("_"), at sign ("@") or the number sign ("#")
2. Subsequent characters can be
   • Letters
   • Decimal numbers
   • Dollar sign ("$"), Number sign ("#") or underscore ("@")
3. The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words
4. Embedded spaces or special characters are not allowed
5. Supplementary characters are not allowed

Nowhere is it mentioned that identifiers cannot be the same as the database name.

NOTE: When identifier names conflict with reserved words, they must be enclosed in square braces. So, a table name like "TABLE" is illegal, while "[TABLE]" is perfectly legal.

Special Thanks to…

This post has been inspired from Pinal Dave’s (blog) series – SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Efficient Query Writing Strategy – Day 4 of 35

Until we meet next time,

Be courteous. Drive responsibly.

It is a well-known recommendation and best practice that the transaction log of any database must be on a drive different than the data files are on. This is especially useful to improve transaction log file performance, which manifests itself as a high value for the LOGBUFFER wait type. Refer Pinal’s post (blog) on the LOGBUFFER wait type here. Pinal has demonstrated this at multiple community meets (Tech Ed 2011, CTD – June 2011) and every time he performs the demo, the crowd erupts in admiration.

So, all’s well. But, the question that kept coming back to me was – WHY? Why does moving the transaction log to it’s own dedicated drive benefit the performance of the SQL Server?

To understand the WHY behind this best practice, it is imperative for us to understand the differences in the physical architecture of the transaction log and the data files.

SQL Server uses something called as a Write Ahead Log (WAL) mechanism. What this means is that even before data is persisted to the disk/data file, data is written to the transaction log. When data is written to a database, it moves from the memory (where the manipulation happened) to the transaction log. Later, when background check-pointing happens, this data is written from the log to the data file. Therefore, the data file performance does not directly affect the throughput of the database. The transaction throughput of the database ultimately depends upon the performance of the transaction log.

Since users can read or write any data from the data files, the read & write activity is essentially random in nature. Physically, the read-write heads inside of the disk are jumping around all over the place moving from one sector to another randomly – which slows down the drive, reducing the throughput.

The transaction log on the other hand, is written to serially. This is one of the reasons why instant file initialization cannot be used for transaction logs (refer Paul Randal’s post here), but that’s a different story. Because the transaction log is written to serially, and read from only during check-pointing, a log backup or a restart recovery, it is much more beneficial to place the transaction log on a drive that does not need it’s heads to move around randomly.

This is why moving the transaction log to it’s dedicated drive benefits the SQL Server performance wise.

You can read more on the physical architecture of the transaction log in Books On Line at: http://msdn.microsoft.com/en-us/library/ms179355.aspx

Now that I understand the reason why this arrangement works, I feel much more confident in implementing the same in my development, quality assurance and production environments.

Until we meet next time,

Be courteous. Drive responsibly.

Recently, a friend told me that they were experiencing performance issues in production when importing data from 3rd party interfaces at the end of the day. The entire import was done via BCP and at a time when no users were logged into the system. Also, since it was a disconnected import, the entire import could be repeated again in case of any errors or issues.

After some discussion and research on MSDN, we came across the following page: Considerations for Switching from the Full or Bulk-Logged Recovery Model. Sure enough, the issues were resolved by a simple process change and addition of less than 10 lines of T-SQL code his end.

The Secret of faster Bulk operations

At this point, allow me to mention that detailed discussion on recovery models within SQL Server is out-of-scope of this post. Please refer Books-on-line for more details on each recovery model.

What the MSDN page suggests is that while FULL recovery model is ideal for normal usage, the BULK_LOGGED recovery model might help speed-up large bulk imports. So, the sequence that BOL recommends is:

1. Under the FULL recovery model, take a log backup (in addition to the normal scheduled backup)
2. Switch to the BULK_LOGGED recovery model
3. Undertake the bulk operation
4. Switch back to the FULL recovery model
5. Take a log backup (in addition to the normal scheduled backup)
6. Database available for normal operations

While under the BULK_LOGGED recovery model, the scheduled database, log and differential backups can continue to occur.

The secret lies in the fact that under the BULK_LOGGED recovery model, some operations are minimally-logged whereas the same operations are fully logged under the FULL recovery model. The fact that these operations are minimally logged means that the bulk operation would complete faster under the BULK_LOGGED model than under than the FULL recovery model.

These most commonly used operations which can benefit from the BULK_LOGGED recovery model are bulk import operations (BCP, SELECT…INTO, BULK INSERT and INSERT… SELECT).

IMPORTANT:

While following these steps it is strongly recommended that:

1. No users be there in the system
2. No modification be made that cannot be recovered without a log or a full database backup. Simply re-executing the bulk operation should be sufficient to restore the database from last backup if necessary

What this would not benefit

There are many myths & legends that surround SQL Server. One of them is that switching to the BULK_LOGGED recovery model would speed up log shipping because the transaction log file would be small. This myth has been busted here – http://blogs.msdn.com/b/sqlserverfaq/archive/2011/01/07/using-bulk-logged-recovery-model-for-bulk-operations-will-reduce-the-size-of-transaction-log-backups-myths-and-truths.aspx

I hope that the above has helped you in setting up your bulk import processes.

Until we meet next time,

Be courteous. Drive responsibly.