Recently, I had the opportunity to review the newest SQL book, hot off the press – the SAMS Teach Yourself SQL in 24 Hours, 5^th Edition. The book has been written by Ryan Stephens, Ron Plew & Arie D. Jones, and includes coverage of both – Oracle and Microsoft SQL Server implementations.

For 10 years now, the SAMS Teach Yourself series has helped kick-start the careers of millions of professionals around the world, and this book continues the tradition.

First impressions

The first thing that I realized was that this book was not about the specific implementation, but it is about the ANSI SQL standard, which to me increases the appeal even more. I am a strong believer in the fact that once the standard is known, implementations are not that hard to conquer. Every vendor will have their own version of the standard, but at the end, it’s all about combining the knowledge of the standard and logic to create practical solutions. This book does a very good job at explaining the standard, and earns points from me right from the start.

I am a Microsoft SQL Server professional, and I felt quite at home going through the book. The book follows a very balanced approach by explaining the ANSI standard first, and then following up with Oracle, Microsoft SQL Server & MySQL implementation samples, while explaining the differences between each!

Anatomy of the book

The book has been arranged into 24 chapters (or “hours”) spanning 8 parts (start from the very basics of a SQL query and go all the way to cover complex queries, performance, security & high-profile applications). Each chapter comprises of the following:

• Step-by-step instructions
  • Every explanation is backed by a step-by-step walk-through of the SQL standard code
• Quizzes & Exercises
  • The best part of the book
  • I enjoyed working my way through the Exercises & the Bonus Exercises in the appendices
• “By the way” nuggets
  • These are interesting pieces of information, and would recommend spending some time thinking about them – each nugget has the potential to expand into hours of research
• “Did you know?” moments
  • There are some hidden secrets about SQL and its various implementations in the book. I won’t spoil the experience by giving examples
• “Watch out!” indicators
  • I would say, watch out for the Watch outs! – they are goldmines of information
  • Throughout parts 4 to 7 (Complex queries, Performance tuning, Security & summarized data structures), these parts will give experienced professionals a run for their money!

My recommendations to the reader

What I would recommend is that everyone – beginners and intermediates alike – must take the time out and run through the “Watch out!” and “By the way” sections. The Quizzes & Exercises (including Bonus Exercises) are also a must solve. Surprisingly, they gave me a flashback of all that I knew in Microsoft SQL Server – definitely worth spending the time.

For beginners though, I would recommend spending time out and gain background on entity relations (ER) and ER Diagramming. Once the reader knows what a relational database is, and what the key concepts are, this book would be a breeze. Remember that the book is about a standard and implementations, not about concepts.

Readers should not ignore the appendices either. They contain a summary of all commands learnt in the 24 hours, bonus exercises and answers to the exercises & workshops after each chapter.

Finally, here’s my biggest tip – don’t try to complete this in 24 hours back-to-back. The best ratio I found was to cover 1 hour (chapter) each day, and then spending the time to solve exercise, and conduct follow-up research/reading.

What I liked most?

I liked the fact that the book tackles following difficult concepts with ease:

• Data-types
• Complex Query & Performance tuning concepts
  • Go from the smallest table to the largest
  • Think about and arrange your WHERE clause to be the most selective – a concept also called as SARGability in the Microsoft SQL Server world
• Basic database and server security

If I had to change one thing…

If I had to change one thing in the book, I would add a chapter, or an appendix on basics of relational databases. Readers need to understand that at the end of the day, the only relationship that exists is a 1:1 relationship between instances of entities (even a 1:N is multiple 1:1 relationships). Therefore, it is essential that the reader understands that a relational database is at its best if the user designs them in such a way that the query filters can be designed to return one and only one row (i.e. are most selective).

The final score

I would give this book a sound score of 4.5/5. Happy reading and best of luck with SQL!

I would like to thank Pearson education for giving me the chance to review the book. Best of luck!

Until we meet next time,

Be courteous. Drive responsibly.

Today morning, as I was reading the morning newspaper and having a nice cup of tea, the following article grabbed my attention:

Edition: Times Of India, Ahmedabad edition
Date     : July 04, 2011
Page     : 08
Article : After 823 years, five weekends this July
Author : Chittaranjan Tembhekar

I was shocked! It is sad to note that a responsible print media fell into the trap of a rumour that seems to have started only to help numerologists and astrologists mint money.

Calendars are a periodic set of numbers and repeat approximately after a given time. This period however, is not a huge 823, but approximately 6-10 years.

Here’s the proof – such an arrangement of days last came about in 2005, and before that in 1994. Don’t believe me? See for yourself in your Windows calendar!

July 2011
July 2005
July 1994

Moral of the story: Don’t believe everything you see! Even the media may be wrong.

Until we meet next time,

Be courteous. Drive responsibly.

For years, Microsoft SQL Server developers and administrators have been familiar with a two letter verb – GO. “GO” is used as a batch separator, and we have never even thought of customizing the batch separator to be something other than “GO”. Here’s what Books On Line has to say about “GO”:

“GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.“

Recently, Madhivanan ( blog ) posted an interesting tip on the recently launched “Just Learned” page on BeyondRelational.com. He was talking about how the batch separator (“GO”) can be used as a loop – http://beyondrelational.com/justlearned/posts/75/go-command-is-also-a-while-loop-from-version-2005-onwards.aspx. Jacob mentioned that the batch separator can be customized, which got us (Madhivanan, Jacob & I) into an interesting discussion. Here’s the trail of thought that followed:

Customizing the Batch separator in SSMS

Here are a set of very simple, easy steps to customize the batch separator in SSMS:

1. Launch SSMS
2. Go to Tools –> Options
3. Click on the “Query Execution” node
4. Notice that we have an option to change the Batch Separator
5. Change the batch separator
6. Click “OK”

I changed the “GO” with “RUN”, and here’s the impact – it’s fun, isn’t it?

USE AdventureWorks2008R2
GO
USE AdventureWorks2008R2
RUN

Here’s what you would get if you run the first 2 lines above.

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘GO’.

Surprised? Don’t be – if you run the next 2 lines, you will see that our change has taken effect:

Command(s) completed successfully.

The good and the bad…

Customizing the batch separator is fun, but we soon encounter the dark side of the coin. Because SSMS is a client-side utility, these changes are restricted to you for the SSMS on your machine only. What this means is that if you share your SQL file with the custom batch separator to a colleague, they will encounter errors.

What this also means is that other SQL Server utilities like SQLCMD are unaware of this change!

Connect to your SQL Server instance via SQLCMD, and run the batch from above. Here’s what you would get:

Summarizing, customizing the batch separator is more a means of having some fun rather than some practical use.

Using a custom batch separator in SQLCMD

Let’s assume for a while that you want to continue using a customized batch separator, and use SQLCMD a lot. The best option is to explore the various options available with SQLCMD:

Microsoft (R) SQL Server Command Line Tool

Version 10.0.4000.0 NT INTEL X86

Copyright (c) Microsoft Corporation.  All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]

  [-S server]            [-H hostname]          [-E trusted connection]

  [-? show syntax summary]

I have high-lighted a particular parameter –c, which per Books On Line:

“-c cmd_end

Specifies the batch terminator. By default, commands are terminated and sent to SQL Server by typing the word "GO" on a line by itself. When you reset the batch terminator, do not use Transact-SQL reserved keywords or characters that have special meaning to the operating system, even if they are preceded by a backslash.”

Let’s try to use the –c parameter and supply our own batch separator:

The big question – If you had to, what would you propose as a replacement of “GO”?

I hope that you liked the discussion above, and a chain of thought has also started in your mind. What would you propose as a replacement of “GO”? I request you to post your thoughts in continuation to our original discussion at: http://beyondrelational.com/justlearned/posts/75/go-command-is-also-a-while-loop-from-version-2005-onwards.aspx as replies to this post.

Until we meet next time,

Be courteous. Drive responsibly.

Lunch time for me, is time to refresh myself. It’s a time when I try to daily to take 10-15minutes out of my lunch time to close my E-mail client, stop all work and take my mind away from the day’s work. I spend the time learning about anything I am in the mood for – science, sports and SQL Server (of course!).

If you use a train or a bus to commute to and from work, you might be using your smart phone to check out your personal E-mail, listen to music or surf the Internet for social networking updates. Take 15 minutes from the journey to learn something new, and you will find yourselves charged up and ready to take on the day head-on.

Recently, we have a new kid on the block. Jacob Sebastian, the founder of BeyondRelational.com has introduced a new section on the website that allows reader to learn something new each day! The rules are simple – if you learnt something today, share it with everyone! The section is aptly called – Just Learned.

What’s more – by indicating whether you learned something (or knew a piece before hand) and whether you liked a tip or not, your knowledge score on BeyondRelational.com is boosted. You would not believe how much I learn by just spending 5 minutes on Just Learned each day! Here are some examples:

• SSMS Allows you to ask a question in the MSDN forum directly from SSMS
• A quick and easy alternative to print (output) strings (especially TSQL queries) longer than 800
• You can drop multiple tables using single DROP statement
• Semicolon can be used to execute a procedure
• Arithmetic operations with $
• $ is equal to zero
• Output table as HTML table
• Copy and paste a SQL Server database from one server to another using SSMS GUI Wizard

Isn’t hanging out on BeyondRelational.com’s Just Learned section cool?

A golden rule to remember when posting

There is a reason why the above heading is in Red. Whenever you post anything online, please ensure that you pass on due, visible credit to the original author. Also, Remember that some authors might require an express written permission in advance of you publishing their content somewhere. Even after passing due credit and obtaining the permissions, ensure that you do not publish more than 25% of the original content.

So, what are you waiting for? How will you use your lunch/post-dinner learning time today? If you learnt something today, Share it!

Until we meet next time,

Be courteous. Drive responsibly.

Ever since anyone starts learning Microsoft SQL Server, a misconception sets in. The misconception is that data and log files can be defined as under:

1. File with extension – .mdf – is a Primary data file
2. File with extension – .ndf – is a Secondary data file
3. File with extension – .ldf – is a log file

These extensions are the default extensions that are used by Microsoft SQL Server for the default system and sample databases available. Default extensions are good because it helps us to set some sort of uniformity across multiple Microsoft SQL Server environments.

But, the situation is more like the difference between a “recommended configuration” and “minimum configuration”. MDF, NDF and LDF are recommended configurations. But, SQL Server does not really care about the file extension. Here’s a little demo (while this demo shows screenshots of SQL 11 (“Denali”) CTP01, this is also valid for previous releases of Microsoft SQL Server):

/*************************************************************************************
CREATE A DATABASE SUCH THAT:
Primary Data File Extension   - prf
Secondary Data File Extension - sdf
Log File Extension            - dbl

NOTE: This script is provided "AS IS" and without Warranty. This script is for 
      demonstration purposes only.
*************************************************************************************/
CREATE DATABASE [ExtensionTestDB]
    ON 
    PRIMARY(NAME = [DB_DEFAULT], 
            FILENAME = 'E:DatabasesDB_DEFAULT.prf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB), 
    FILEGROUP [DB_DATA_FG](NAME = [DB_DATA], 
                           FILENAME = 'E:DatabasesDB_DATA.sdf', 
                           MAXSIZE = UNLIMITED, 
                           FILEGROWTH = 102400 KB)
    LOG ON (NAME = [DB_LOG], 
            FILENAME = 'E:DatabasesDB_LOG.dbl', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 102400 KB) 
    COLLATE SQL_Latin1_General_CP1_CI_AS
GO

When we run the above script to create a new database, we find that the database creation is successful. In a new Query window connecting to the same SQL Server instance, we can fire a query using the sys.sysfiles catalog view to confirm that the SQL Server is indeed okay with the non-default extensions.

USE [ExtensionTestDB]
GO
SELECT * FROM sys.sysfiles

I hope that this post helps clear out the misconception around the default primary/secondary data and log file extensions.

Until we meet next time,

Be courteous. Drive responsibly.