General discussion of SQL Topics; aimed for the novice-intermediate level Microsoft SQL Server User. Currently focuses on using SQL Server 2005.

Wednesday, February 20, 2008

Understanding MSDN Syntax Conventions

Overview:

Sometimes you may find yourself overwhelmed when you use the MSDN network or Books-Online (BOL); especially when it comes to understanding all of the options for any given syntax found. The purpose of this blog is to help you understand that Syntax a little easier. You can find the complete Syntax Conventions on MSDN website by going to this web address: http://msdn2.microsoft.com/en-us/library/ms177563.aspx (or by clicking on the link at the end of this blog under the "Additional Resources" section).

General Syntax Convention:

The following is a brief description of common conventions found when reviewing T-SQL syntax, this is no way is comprehensive and may be changed by Microsoft without notification. You may want to review the above (and below) mentioned link to get a complete and accurate listing of the conventions used.

Convention

Description

UPPERCASE

T-SQL Keyword

Italic

User-defined parameter

| (Vertical Bar)

Separates Multiple Choices; but ONLY ONE may be chosen/used

[ ] (Brackets)

Optional items. *Do not type/use the brackets*

{ } (Braces)

Required items. *Do not type/use the braces*


Example:

The following example is only a partial syntax of a randomly chosen T-SQL keyword; it's only purpose is to provide a working example of how to interpret the above conventions. This example should not be used as a syntax definition; the complete syntax should be reviewed before use by using BOL or the MSDN website.

This example is for the T-SQL keyword 'CONVERT'; you can view the entire definition and other information by visiting: http://msdn2.microsoft.com/en-us/library/ms187928.aspx.

Syntax

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Breakdown:


Syntax

Description

CONVERT

T-SQL Keyword. This must be typed to use this keyword.

(

This is the leading parenthesis. This must be typed to use the CONVERT keyword. Its purpose is to place hold the conversion data_type.

data_type

This is the target system data type. It is a required user supplied system data type. Length may/may not be required…see below. This means that you tell CONVERT what you want to convert into.

[ ( length ) ]

The brackets indicate this is optional, in this particular case this is optional because not all data types use a length parameter. The bold parenthesis indicate that if this option is used you MUST include the parenthesis to hold the length parameter. The "length" is specified by the user. NOTE: in BOL it is commented that if length is not specified it defaults to 30; so, this means it is possible to use a data_type that uses a length parameter and still not specify in the CONVERT syntax what that length is; this makes this part of the syntax completely optional. If you omit this part of the syntax and get errors, you may want to first attempt entering the correct number for the length here.

, expression

This is a user-defined expression. This is where you tell CONVERT what you want converted. A typical usage may include a declared local variable; such as: myInteger, myVar, etc.

[ , style ]

This is where you would define the style to be used for the format to convert datetime data, character data, or string data. This is typically used in datetime formats to define how to read the datetime into a string format. See BOL for further details.

)

This is the closing parenthesis that belongs to the 'leading' parenthesis you had used after typing the CONVERT keyword. This is required to complete the enclosure of the data_type conversion parameters that have been entered.


An example of the usage of the CONVERT keyword is:

CONVERT(int, myInteger)

Conclusion:

As you can see there is a lot of information stored within just the syntax of any given keyword. There are also a lot of notations, exceptions and other remarks to go along with these keywords. You will often find decent examples of how to use the most commonly used/supported keywords; unfortunately you can sometimes find scarce examples in BOL for the lesser used keywords.

I have personally found it's easier to understand a seldom used, or newly used, keyword by breaking down the syntax. I've also found that the more time you spend in BOL the easier it is to understand what information can be found and how to quickly find that information.

One last note from personal experience; I have found that the local copy (copy stored on your computer's hard drive) of BOL is usually better formatted (in example the Online BOL tends not to utilize the BOLD as often).

I highly encourage you to review, at very least periodically, BOL for keywords you find yourself using. You may be surprised to find out there are other ways to utilize a keyword, and may even find that you can do more powerful and faster operations.

Additional Resources:

Transact-SQL (T-SQL) Syntax Conventions

5 comments:

lance said...

i have joined your feed and look forward to seeking more of your post.

www.n8fan.net

andrea chiu said...

There are certain point in our life that we encounter failure but it doesn't mean you will lose hope and give up everything but it only means that every failure there's an exchange and that is new beginning. Well, thank you for sharing your article and keep on posting. Visit my site too for more information.

triciajoy.com

www.triciajoy.com

Cindy Dy said...

First time I commented in a blog! I really enjoy it. You have an awesome post. Please do more articles like this. I'm gonna come back surely. God bless.

Rica
www.imarksweb.org

sarah lee said...

I really enjoyed reading your article. I found this as an informative and interesting post, so i think it is very useful and knowledgeable. I would like to thank you for the effort you have made in writing this article.


edupdf.org

lee woo said...

Love it! Very interesting topics, I hope the incoming comments and suggestion are equally positive. Thank you for sharing this information that is actually helpful.


ufgop.org
ufgop.org