A brief introduction to Transaction Logs

Note: This entire article has been published by SQLServerCentral.com. Please find the link to the article at the end of this blog in the "Update" section.

Summary:

This is a very brief introduction to transaction logs. I am currently working on an article (titled "Introduction to the Transaction Log") that will go into a lot of detail, show the ‘ins’ and ‘outs’, and also discuss some ‘good practices’ (and how truncating works). This blog will just give you a basic introduction that discusses what a transaction log is, how it basically works, and what the primary use of the transaction log is (these can be more than just used for backup/restore procedures; that will be discussed in my upcoming article).

What a transaction log is:

Every single database MUST have at least one transaction log. A database can have more than one transaction log. A transaction log can be used over multiple physical files.

A transaction log is a log file (physical and virtual) that records each and every transaction that occurs. This includes data modifications, database modifications, and rollback (restoring) modifications. This file records each transaction in sequence using a Log Sequence Number (LSN). Each transaction is appended to the end of the physical log file and always uses a LSN that is higher in value than the last written LSN. These are best thought of as a journal entry for every action that occurs within your database.

Some of the operations that are recorded within the transaction log are data modifications, database modifications, rollback modifications, the start/end of a transaction, and even creating/dropping of tables and indexes.

Logical Architecture (concept):

Transaction logs operate as if they are being stored with a string of log records. Each record is identified by an LSN. The steps used to recover data can differentiate on how the data is logged within the transaction log (this is covered further in my article). Each transaction reserves enough space to support a successful rollback, either by an explicit action requesting the rollback or from an error occurring within the database. This amount of space can vary; however, it typically mirrors the amount of space that is used to store the logged operation. Transaction logs are loaded into a virtual log; this cannot be controlled or set by the administrator (my article will include a ‘good practice’ to help prevent system performance decreases from Virtual Logs).

Physical Architecture (concept):

Transaction logs can span multiple physical files. You can truncate the transaction log to free internal log space (this will not shrink the log file; I’ll cover shrinking the log file in my article). The basic concept of the physical file is it will append to the end of the log file, once the physical end of the log is reached the transactions will wrap around to the beginning of the log file (assuming there is free space).

If the log file does not contain any free space (internally); there are two possible outcomes:

1) If FILEGROWTH is enabled and there is free disk space on the hard drive, the file will automatically increase in accordance to the settings and then will re-organize the transaction log and append the new record to the end of the log, OR

2) If FILEGROWTH is NOT enabled, or there is NOT enough free disk space on the hard drive, then SQL Server will return a 9002 error code.

Primary Use of a Transaction Log:

A transaction log is typically used to allow recovery of a database to a certain point in time; or to allow the recovery of a database to the last successful transaction committed prior to the database failure. The usage of the transaction log will greatly depend on how the database recovery model is setup and the backup plan that is used.

Additional Notes:

Transaction logs should be periodically backed up. The size of the log is typically recommended to be about 1 ½ the size of your database. Example: If your database is 10 MB in size, then the transaction log would be about 15MB in size. This should be treated as a very general recommendation; there are many instances where this formula is not appropriate and should be modified to the appropriate amount based on your particular situation.

You want to ensure the transaction log is the proper size (the expected ending size) and has a relatively large growth increment value. This will provide you with the best performance and can ensure the proper use of the transaction log (much less likely to have the log expand without your knowledge of the event occurring).

Conclusion:

Transaction logs are a very important aspect of maintaining and recovering your database. If set properly, and maintained properly, they can give you the additional backup support you need without impacting system performance. They can help to give you peace of mind during daily operations. They can be an integral file to recovering a database to a point in time.

Additional Resources:

MSDN: Introduction to Transaction Logs (http://msdn2.microsoft.com/en-us/library/ms190925.aspx)
MSDN: Transaction Log Logical Architecture (http://msdn2.microsoft.com/en-us/library/ms180892.aspx)
MSDN: Transaction Log Physical Architecture (http://msdn2.microsoft.com/en-us/library/ms179355.aspx)

Update:

Posted on 7/2/08 by James:

SQLServerCentral.com has published the article in full. You can read the article at: http://www.sqlservercentral.com/articles/Design+and+Theory/63350/. You can join the discussion of this article at: http://www.sqlservercentral.com/Forums/FindPost527448.aspx. I hope you enjoy the article and find it helpful!

Types of backups

Overview:

There are different objects you can backup using SQL Server; in most cases when DBAs refer to backups they are referring to backing up databases. You can also backup the physical files and folders, but that will go beyond the scope of this blog posting. First I'll cover the three "Recovery Models" that you can choose for you database (Full, Simple, and Bulk-logged, respectively), then I'll cover the three different types of database backups: Full, Differential, and Transactions. Then I'll conclude with a quick overview of how to create backups for your database.

The most important thing to know is that you should always have a current backup in place that matches the needs of your company, you should have set procedures that detail how to backup your database and how to restore your database, and most importantly you should practice the database restoring plan you use (so you can verify it actually works, the data being backed up is actually being backed up, and that when the time comes for that once-in-a-lifetime emergency restore you will remain calm and collected so you can ensure the data is restored in a very prompt manner).

What is a backup? When do I make them?

Backups are copies of your database that is stored on your hard drive, on a server, on a removable media source, or any other type of location (physical and/or virtual). The basic idea behind a backup is to have a copy (or copies) of your data in a secure and easily obtainable location, and for the business critical information to have another copy stored somewhere offsite that is secure and obtained within a reasonable timeframe. Backups can contain all the information that is stored within your database, these are called "Full" backups. Most common backups contain entire copies of the database; there are also times when you will only retain copies that contain only the data that has been added since your last complete database backup. These are called "differential" and "transaction log" backups. Both accomplish the same thing in different ways and have different positives and negatives to them. I'll cover those later in this posting.

What you are aiming to accomplish is the ability to quickly recover from an incident that causes your current database to become corrupt or inoperable. Examples would be: Your database was hacked into the previous night, the database has somehow become corrupt and cannot be started, the data was accidentally deleted from a careless system maintenance procedure, or worse yet the building has been burned down or otherwise destroyed with all the computers and backups in it!

In most cases, you will access a backup copy that is stored in a secure location within your business location for the unforeseen events that can occur at any moment. In the extreme cases you will obtain your backup from your offsite location for the catastrophic events that require the business to be temporarily or permanently relocated without any prior notices.

So, now that we know what the backup is and where it is stored we now need to know when and how do we make them. The great thing about SQL, regardless of the version you get, you can make a backup at anytime. SQL uses online backup technology, so this means that your users and database can be accessing and modifying your database at anytime, even during your backup cycle! Do be aware that to create a backup takes up some resources, such as CPU, Memory, and I/O. It is most common to make backups during the business' "down cycle"; usually in the middle of the night for traditional 9-5 companies...and the 24x7 companies that are always accessing data will require a little more planning by determining the data usage trends and adjusting the backup procedures to occur during the lowest peaks or generalized time frame that matches the expected lower usage peaks.

Recovery Models:

There are three recovery models that can be chosen at the time you create your database: Full, Simple, and Bulk-logged. Each database can have it's own recovery model; and this recovery model may be changed from within the database in a future time.

Full: This model allows you to recover your database to a specific point in time, or to the point of the failure. This model will record every transaction that occurs within your database, as well as the stored data, structure, and every other object; this includes the bulk operations and bulk loading of data. For this model you will typically want to use Full and Differential backup types at a minimum; to maintain a more complete backup solution you would also use the Transaction Log backup type.

Simple: This model will allow you to recover to the point of your last backup creation; it's very important to understand that this model does NOT allow the use of Transaction Log backups. This model uses the 'TRUNCATE LOG ON CHECKPOINT' option; which in effect deletes all old transactions when the database gets to a checkpoint. This is more ideal for system databases because the transaction log is deleted at each checkpoint.

Bulk-logged: Bulk operations and bulk loads are logged at the most minimal levels. This means that during a restore you will need to repeat the bulk operations and bulk loading of data, should the database fail prior to a Full backup or a Differential backup. The ideal strategy will implement the Full and Differential backup types at a minimum, and include Transaction Log backups on a regular re-occurrence. 

Types of Backups:

I'll clarify now that I am discussing using the SQL Server Management Studio (SSMS) backup options; and that there are many different methods to create backups, but this discussion is limited to performing file backups using the BACKUP syntax & wizards. Some other options that are commonly used, but are beyond the scope of this posting, are: Log Shipping, High Availability (i.e. Redundancy hard drive configurations, standby servers, etc), and many others using a combination of hardware/software solutions.

The three types of backups that can be accessed from within SSMS are: Full, Differential, and Transaction Log. Here is a brief breakdown of each type:

Full
Creates a backup that contains all objects, system tables and data from the database, it also includes portions of the transaction log that required because of database being used at the time the backup was created. A Full backup is intended to bring you right back to complete working order up to the point in time the backup was created.

Differential
This type of backup is designed to create a backup of only the objects, tables and data (including transaction logs) that have been changed or not recorded on the last Full backup. It is very important to understand that you can NOT create a differential backup until after you have created at least 1 full backup.

On a personal observation note: you'll notice that a Differential is quite a bit faster than the Full backup because it requires less information to be created and is recording much less information, however, as with everything else there is a trade off. If you choose to create only 1 Full backup and then from there on use Differential backups to save time and resources, you will have to use all of these Differential files to restore to the last point of your Differential backup. What I'm saying here is that assume you make a daily Differential every single day for the next 2 years; on the first day of the 3rd year you have a database failure and must restore the database. You will have to first restore the Full backup file you originally made and then you will have restore all 730 Differential files.

You can choose to create a Full backup at anytime and it will automatically replace your original Full backup and ALL differential backups made between the original Full backup and the current Full backup you are making.

Transaction Log
Transaction Logs are a special type that compliment the Full and Differential backup types. These are serialized records of all database modifications made since the last transaction log backup. They are used during the recovery process to roll-back or commit transactions. Unlike the Full and Differential backups, Transaction Logs backups are recording the state of the transaction log at the START of the backup operation. Transaction Logs are best to think of a way you can have backed up checkpoints between the time you have made your last Full or Differential backup. A most common method of use is in OLTP databases that have daily Differential backups and weekly Full backups; you would use the Transaction Log backup on an hourly basis to help minimize the lost of transactions in case of a failure within the middle of the day. It is very common to be used with live websites that have many transactions occur per hour; instead of losing a full days worth of transactions you can minimize it down to an hour, or in highly volatile situations could even get down to the seconds (however, it is very uncommon to have a transaction log backup on a per second basis).

How do I make a backup?

In SSMS you can easily create a backup of your database by right-clicking on your database and selecting "Tasks" then "Back Up...". This will open the "Back Up Database" dialog box that allows you to easily fill in the appropriate information and then either have SSMS perform the backup, or create a script using the scripting wizard from within the "Back Up Database" dialog box.

From the "Back Up Database" dialog box you can choose the backup type, the name of the backup file, the location to place the file (including using devices that contain the file location and other information). By click on "Options" in the left pane, you will be brought to another screen that will allow you to give even more specific options for your backups. Some options include creating a new set of backups, appending to an existing set, verifying the backup reliability, and for transaction logs you can specify how to handle the log after being backed up.

Conclusion:

As you can tell, the backup options and wizard are each powerful in their own rights. I can't stress enough the importance of having a backup plan and a recovery plan in place now, it can save you time and also provide you with the confidence that you know when a disaster occurs you are ready and have reliable backups that can be restored in an instance.

You have now seen that backups can cover most any situation that you can come across, and depending on the need can back up a database down to the second in the highest volatile uses.

This is just the tipping of the iceberg, there are many vendors out there that specialize in create backup/recovery solutions that are based on software, hardware, and hardware/software combinations.

With some careful planning, execution and understanding you can handle any situation that will cross your path and know that in the worse of conditions you are prepared to get your business back up and running faster than they can get a grasp on what just happened to their business!

Until next time....Happy Coding!

BACKUP (Transact-SQL)

This is a simple reference to the "Backup" T-SQL syntax. This syntax is probably one of the most simple, complicated and yet important syntaxes to know.

It's simple because you have a few different options, first you can use SQL Server Management Studio to do your complete backup (full, differential, and transactional). You can also create a backup using the T-SQL syntax directly and it is quite simple even though the below T-SQL syntax can make it look scarier.

Here's an example of a very simple T-SQL backup (you can find more at the link to the MSDN Books Online reference below the syntax):

DATABASE   AdventureWorks   TO   DISK   = 'Z:\SQLServerBackups\AdvWorksData.bak'   WITH   FORMAT ; GO

It's complex because...well, just look at the full syntax! It can get to be very complex if you want to enable a lot of features; and this can be a very, very good thing if you need to leverage power when the SSMS wizards/built-in tools just can't give you what you want.

It's important because...well, I hope you already know this. If you don't have a backup and something happens to your data, then you are up a creek without a paddle and just about starting to go over the Niagara Falls! If you ever hear someone tell you that you are backing up too frequently or you are overkill on backups, then I'd simply walk away from that person as quickly as possible. If you value your position, and don't want to be the one to explain to the owner (or your boss) why it is that at 4 AM the system went down and the $100,000 in transactions didn't get saved because you are only backing up daily instead of after EVERY transaction then make sure you understand backups, how to restore, and what different options are available to you.

Ok, enough of the CYA talk. Now lets get to the syntax. As mentioned already, it looks scary and complicated. Hopefully, with the above example of 1 usage you will see that it is simple to use. The thing to keep in mind is that Microsoft is providing all of your backup needs rolled into 1 command. So this means that there are many options you won't use unless you are doing a differential backup, likewise with full and transactional backups.

Keep your backups fresh..treat them like Milk..don't let one sit around for too long without checking it, and...

Replace your backups on a regular basis!

Until next time...happy coding!

SQL Server 2005 Books Online (September 2007)

BACKUP (Transact-SQL)

Updated: 1 February 2007

Backs up a complete database, or one or more files or filegroups (BACKUP DATABASE). Also, under the full recovery model or bulk-logged recovery model, backs up the transaction log (BACKUP LOG).

Transact-SQL Syntax Conventions

Syntax

Backing Up a Whole Database  BACKUP DATABASE { database_name | @database_name_var }   TO <backup_device> [ ,...n ]   [ <MIRROR TO clause> ] [ next-mirror-to ]   [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]

Backing Up Specific Files or Filegroups
BACKUP DATABASE { database_name | @database_name_var }  <file_or_filegroup> [ ,...n ]   TO <backup_device> [ ,...n ]   [ <MIRROR TO clause> ] [ next-mirror-to ]   [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]

Creating a Partial Backup
BACKUP DATABASE { database_name | @database_name_var }  READ_WRITE_FILEGROUPS [ , <read_only_filegroup> [ ,...n ] ]   TO <backup_device> [ ,...n ]   [ <MIRROR TO clause> ] [ next-mirror-to ]   [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]

Backing Up the Transaction Log (full and bulk-logged recovery models)
BACKUP LOG { database_name | @database_name_var }   TO <backup_device> [ ,...n ]   [ <MIRROR TO clause> ] [ next-mirror-to ]   [ WITH { <general_WITH_options> | <log-specific_optionspec> } [ ,...n ] ]
[;]

Truncating the Transaction Log (breaks the log chain) 
BACKUP LOG { database_name | @database_name_var }   WITH { NO_LOG | TRUNCATE_ONLY } 
[;]

<backup_device>::=  {    { logical_device_name | @logical_device_name_var }  | { DISK | TAPE } =      { 'physical_device_name' | @physical_device_name_var }  } 

<MIRROR TO clause>::=  MIRROR TO <backup_device> [ ,...n ]

<file_or_filegroup>::=  {    FILE = { logical_file_name | @logical_file_name_var }  | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }  } 

<read_only_filegroup>::=
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }

<general_WITH_options> [ ,...n ]::= 
--Backup Set Options       COPY_ONLY   | DESCRIPTION = { 'text' | @text_variable }  | NAME = { backup_set_name | @backup_set_name_var }  | PASSWORD = { password | @password_variable }  | [ EXPIREDATE = { date | @date_var }         | RETAINDAYS = { days | @days_var } ]  | NO_LOG 

--Media Set Options    { NOINIT | INIT }  | { NOSKIP | SKIP }  | { NOFORMAT | FORMAT }  | MEDIADESCRIPTION = { 'text' | @text_variable }  | MEDIANAME = { media_name | @media_name_variable }  | MEDIAPASSWORD = { mediapassword | @mediapassword_variable }  | BLOCKSIZE = { blocksize | @blocksize_variable } 

--Data Transfer Options    BUFFERCOUNT = { buffercount | @buffercount_variable }  | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

--Error Management Options    { NO_CHECKSUM | CHECKSUM }  | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

--Compatibility Options    RESTART 

--Monitoring Options    STATS [ = percentage ] 

--Tape Options    { REWIND | NOREWIND }  | { UNLOAD | NOUNLOAD } 

--Log-specific Options    { NORECOVERY | STANDBY = undo_file_name }  | NO_TRUNCATE

BACKUP (Transact-SQL)

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

 

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

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!