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

Thursday, February 21, 2008

Getting Started – SQL Server Overview

Overview:

Now that we have covered the basics of T-SQL and how to get the information you will need to make your queries, I'm going to focus on getting familiar with SQL. This blog will briefly cover an overview of SQL Server 2005 and some of the various tools/utilities you can use with it.

SQL Server 2005 – The Platform:

Believe it or not, SQL Server 2005 is NOT an application (like Visual Studio, Microsoft Office, etc); it's an actual platform. What would make this a platform vs. being an application? This is because SQL Server 2005 is a layer (a data layer in particular) within the Microsoft Operating System. When we are done with this blog you'll understand this concept much better; for now, you may want to look at the MSDN website: SQL Server Overview. This will show you that SQL Server offers different components that make up the entire SQL Server Platform; those components are: Integration Services, Database Engine, Reporting Services, Analysis Services, Service Broker, Full-Text Search, Replication, and Notification Services.

SQL Server Components:

Each of these components have different functions it is intended to perform; the basic idea behind having these services is that you can leverage data in almost any fashion you see best fit. Examples of leveraging could be:

  • To import/export data to/from other databases (including, Access, Oracle, MySQL, etc)
  • To generate live-time reports
  • To achieve live-time notifications of events or data milestones
  • To hold data that is considered unorganized and get useful information from within this data

This is only some of the uses you can make of data and SQL Server. You'll find that nearly any way of storing, retrieving and/or manipulating the data may be performed. It all boils down the knowing what component you can get the most leverage using and how to present that data to the user and/or to the other components in a meaningful way.

How data is gathered, retrieved, and manipulated:

This is only the tip of the iceberg. With the advances made today you'll find that data is an evolving volatile market that is important to almost everyone; if you think about it data runs most things. Your bank account uses data to know how much money you have, you've used, and where you got it from and spent it at. When you shop the grocery stores keep data to know what is being sold, sitting on the shelves, and they will use this data to know what items to keep in stock that target your local area. Ever notice that some items you find in the store near your home aren't carried in the same store that's located a couple of states away? This is usually because they have collected data that says your area likes that item and the other area may not have as high of sales for it.

How data is accessed and stored:

So, now that you have a general understanding that SQL Server is way more than just an application and you can do immense of data storage and extract almost any information at any given time; the question then comes to how can you access the data and/or get more leverage.

This answer can be quite complex; it generally comes down to what you want to do with the data and how you want to do it. The common methods are to use an application to access the methods, or to create an application that can present the data, and capture the data, in a unique method suited best for whatever audience is targeted.

A common used application to create databases, store data, view data, generate reports, and/or manipulate data is by using built-in tools designed by Microsoft. The most common tool used is SQL Server Management Studio (SSMS). This tool provides a seamlessly integrated solution to access any databases stored within SQL Server, and to create any database to be stored in SQL Server. You can also write direct T-SQL statements, stored procedures, and perform a large number of tasks (such as backing up the database, restoring the database, monitoring the activity on an attached database).

Another commonly used application is Visual Studio (VS). This is a development software application used to develop custom software solutions that can interact with the SQL Server or any attached database within the SQL Server platform. This can be done with only using the wizards and controls within the VS environment to create a simple access solution to the data; however, you'll find that ONLY using the built-in wizards and controls will lack many features you would want (such as the ability to search all the records for specific words, the ability to mass delete records, backup the database, etc). If you wanted to have these features, then you would use the VS environment with custom coding to access the data and to present the data to the end-user. There are many books dedicated to doing these types of things, and go beyond the basis of this blog.

How SQL Server is configured and/or monitored:

SQL Server comes with utilities and tools that can help to configure it and to monitor it. A common tool used for configuring SQL Server is the SQL Server Surface Area Configuration tool, sometimes in conjunction with the SSMS (see above) tool; but, it doesn't stop there. Another useful tool included with SQL Server is the SQL Server Configuration Manager. These types of tools allow you to tell SQL Server where it is accessed from, how the database is stored, and many other options.

When it comes to monitoring the database platform you will find that there are many tools/utilities available for this too. SQL Server comes pre-bundled with: SQL Server Profiler and Database Engine Tuning Advisor. Also if you go to MSDN's SQL Server 2005 Tools and Utilities website you will find other tools/utilities such as: RML Utilities and SQL Server 2005 Best Practices Analyzer.

Conclusion:

As you probably know by now, SQL Server is far beyond any application and includes so many components, tools, and utilities that you could spend years just learning how to use them and to capture the information that you want. You probably have even started to realize that SQL Server has tools for its components!

It's well worth to mention that not all tools/utilities will work on all editions of SQL Server; however, Microsoft has gone through great lengths to ensure that SQL Server can be used by just about anyone with a data need, from the single user to the multi-world enterprises. Microsoft has even gone to the extent of making it's commonly used tools available for use with its "free" (at time of this blog) express edition. Sometimes the tools may have slightly modified names (such as, SQL Server Management Studio Express) and there are some options that aren't available for the lower end SQL Server Platforms (such as 64-bit support, and remote login <internet> access), but in most cases you will find that if you are not concerned about those areas or any other affected components (such as reporting services, analysis services) you can get by with using the Express edition.

Even if the express edition won't work for your needs, it's usually worth the time to download it and test out some basic uses to see if its bigger brothers can address those needs. Microsoft can even offer evaluation and development editions that will provide great means of determine the usefulness of the product.

Most of all, don't forget, there are tons and tons of information available for free to help you along the way; when those aren't quite cutting it for you any longer or you don't have access to the free content information you will find tons and tons of books that cover just about all topics you can think of and more!

Additional Resources:

SQL Server 2005 Tools and Utilities on MSDN: http://msdn2.microsoft.com/en-us/sqlserver/bb671105.aspx
SQL Server Overview: http://msdn2.microsoft.com/en-us/library/ms166352.aspx


 

Wednesday, February 20, 2008

Getting Started – Transact-SQL (T-SQL)

Overview:

This blog is aimed to help you get started using T-SQL. T-SQL is a syntax used in SQL Server; it generally composes of the majority work that is performed in the SQL database to get the data, store the data and/or work with the data. There are various other aspects of SQL Server that are used to perform many tasks that will be covered in other blogs; you'll find that in fact many of the tools used to setup new databases, new users, and other operations/tasks within SQL Server are nothing more than a nice Graphical User Interface (GUI) based on T-SQL commands.

What is T-SQL?

T-SQL, as already mentioned, is the syntax you will use to directly interact with the data stored within your database. T-SQL is typically entered via a GUI tool or application. A typical Database Administrator (DBA) might use Microsoft's SQL Server Management Studio (SSMS) or Enterprise Manager (EM) to enter T-SQL statements. A typical Database Programmer (DBP) might use a programming language such as Visual Basic or C# to enter T-SQL statements.

A common T-SQL statement used, almost in every instance of obtaining data information, is the "SELECT" statement. This statement can be a simple one or very complex. A typical usage may be:

    SELECT * FROM table1

This example would select all data from all columns from a table called "table1". This would be the simplest of all possible examples; but you aren't limited to only this single use. You can create complex SELECT statements that can calculate multiple fields and return the results, could join multiple tables, you could even join tables (or columns) from other databases!

How Do I use T-SQL?

T-SQL is used by entering a line of text within the chosen editor and then executing the command(s) within the SQL Server environment. A DBA may use SSMS to directly enter the commands within the SQL Server environment, or he/she may create a series of commands stored within a file (this is commonly referred to as a 'script') that can later be executed, or he/she may create a stored procedure that holds these commands within the SQL Server environment that can be later called by utilizing the "EXECUTE" keyword.

The important thing to know isn't how to use T-SQL; it's how to find the information to learn how to do what you want with T-SQL. I could spend this whole blog, and many more, on just how to use the SELECT statement; I could spend a lifetime blogging on how to use every T-SQL command there is within SQL Server (and before I even could get through half those commands and new version of SQL Server would appear causing us to restart the learning process!). As the famous saying goes: "Give a person a fish and they eat for a day; teach the person to fish and they eat for the rest of their lives". SQL Server is no different. So, I'm going to concentrate on how to find the information you need.

Where do I find the information I need?

This is actually quite simple to answer…the internet! But, if you want really detailed information that provides you with ALL of the information on how to use ANY (and all) T-SQL commands there is only one place you can go. NO, not this blog…you'd go straight to the horse's mouth; MSDN website. In particular you go to: Transact-SQL Reference (this URL is listed in the "Additional References" section at the end of this blog). This is the pillar to the T-SQL commands; everything starts there! When you first go there you are welcomed with a small page telling you what Transact-SQL is and how it is used; but, that's not the great part yet. The great part is (at the time of this blog) that to the left is a pane (navigation listing) that will list ALL of the T-SQL commands. You can even access other SQL Server reference information from here!

You'll also find many other additional resources on the internet. Microsoft contains a section called "Community" this will provide links to their forums, newsgroups and other websites; I highly recommend reviewing this section and discovering what is contained within these links. Outside of Microsoft there are many, many, hundreds of thousands of websites that cover SQL Server, T-SQL and/or anything to do with databases.

My favorite sites can be found in the "Resources" section of the main page of my blog; this doesn't mean I endorse or agree with these sites…I find that I frequent these sites on an almost daily basis. This listing may change and you are welcome to check back periodically. I also welcome any other suggestions of websites that you may find you can't live without..Please leave a comment to this posting and I'll be sure to make it available to all of the readers.

What books, training or other materials do you suggest?

Well, that is very objectionable. There seems to be no one book that covers all topics; most books targeted at beginner/intermediate users will focus on most popular areas of SQL Server and a good book will make you aware of other areas that exist but are not covered by that book. In regards to T-SQL in particular, I'd recommend for the beginning user to get a book such as SAMS 'Teach yourself Microsoft SQL Server T-SQL in 10 minutes'; this covers the most commonly used T-SQL commands and leads you progressively with each lesson.

I, personally, enjoy the Microsoft Press series for detailed information if I know a particular subject I want to learn about. I also have found that SAMS has other titles that cover a large spectrum of topics and are usually worth looking into.

It all comes down to taste, objective and personal preference. I still, without any doubt, always recommend keeping Books Online handy and using the MSDN website; you'll find most of the answers right there already; the answers you don't find can usually be asked (and receive quick responses) in the forums within the MSDN website.

Conclusion:

There is no single source of reference or answer for SQL Server; you will find that Microsoft has gone to great lengths to provide you with the knowledge and means to gain the knowledge to use SQL Server. If you find that Microsoft doesn't offer what you need then there are many other sites readily available to provide you with that information. Learning is everything!

Additional References

Transact-SQL Reference: http://msdn2.microsoft.com/en-us/library/ms189826.aspx

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

Tuesday, February 19, 2008

Welcome to SQL ‘N

This blog is intended to discuss topics that may be helpful and/or interesting to the beginning and intermediate Microsoft SQL Server user. It primarily focuses on SQL Server 2005 (as that is the currently available optimal…main stream…version offered by Microsoft). This blog may change its platform focus, or support both platforms, as SQL Server 2008 nears being released to the general public.

Please feel free to leave any comments, suggestions or requests for topic discussions. This is to help you (and me) to learn more about the technology we use to store our data!

Enjoy your visit!