tag:blogger.com,1999:blog-3678365119756739152024-02-08T09:16:44.895-08:00SQL 'NGeneral discussion of SQL Topics; aimed for the novice-intermediate level Microsoft SQL Server User. Currently focuses on using SQL Server 2005.Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.comBlogger22125tag:blogger.com,1999:blog-367836511975673915.post-84793620153575722122009-06-02T14:28:00.000-07:002009-08-12T10:27:38.219-07:00Backing up directly to a network share<p>Over the past few weeks I’ve seen many users who’ve complained that they can’t directly backup to a network share drive. They’ve all expressed, at one point or another, how disappointing this is for a SQL Server product that boasts about best practices, high availability, and security…yet, you have to backup locally then use a separate script to get the backup file to a network share (seems a bit of a risk involved for the brief time the backup file is stored locally; as well as using up precious local resources to do all of this).</p> <p>Well, I finally intervened in one of those discussions and pointed out that SQL in fact can backup directly to a network share. In fact there are multiple methods that can be performed to accomplish this simple feat! First, you must create a “backup device” that is located on a network share and backup to that device, this is done by one of two ways you can specify to backup to a “disk” (or file) and point to a network share drive/location. Or you may create a backup “media set” which is stored on the network share. These are just two of the methods to accomplish this, which are easy enough to perform; as well as documented in Books Online (BOL). I’ll briefly cover this method here; you’ll want to refer to BOL to find more detail on it, as well as other possible methods.</p> <h3>Some terminology to help…</h3> <p>NOTE: Be sure to understand the differences in terminology here. Notice that I’ve quoted “backup device” and “media set”. </p> <p>A “backup device” is not necessarily a physical device (i.e. tape drive, DVD media, etc). A “backup device” is a virtual device to SQL, meaning that you tell SQL if the ‘device’ is a “DISK” or “TAPE” (understand that DVD-ROM, USB, etc are considered “TAPE” devices in this context). Depending on the ‘device’ you choose will depend on where SQL stores the backup. </p> <p>A “media set” is a logical name of a collection of (or a single) “backup devices”. This means that you could have multiple locations on a “DISK” to where SQL stores the backup (this can be used for striping, to increase your speed of backups…as well as to increase your data integrity capabilities).</p> <p>See the “References” section for BOL definition and details on both “Backup Devices” and “Media Sets”.</p> <h3>Network Share Backup Requirements…</h3> <p>First things first, if I’m pointing out that it’s relatively simple enough to backup to a network share, then why are there so many people claiming they can’t (or believe that SQL can’t)? Well, as with all other features in SQL…most of the problem is either permissions or lack of knowledge on how to implement (accomplish) the required task. </p> <p>Now, to make this all work you need two ensure two things happen. </p> <blockquote> <p>1) You have to know how to tell SQL to make the backup to the network share. As mentioned earlier you need to specify a “backup device” located on the network share. Here are the links in BOL describing this in detail. Note: There is a section in BOL dedicated to this subject, review it!</p> <p>2) Ensure that the network share you are attempting to make the backup to can actually be seen and written to. A simple test is to open up SQL Server Management Studio (SSMS) and attempt to backup the database using the “Back up…” task wizard. You’ll want to backup to “DISK” and then browse to the network share location, or enter the Universal Naming Convention (UNC) path. If you can successfully backup to the network share through SSMS, then you know at a minimum that you have the permission with the account you logged into SSMS with to make the backup to the network share. Even if you can’t pass this test, it doesn’t necessarily mean SQL can’t backup to the shared drive; scheduled jobs and various other methods to create regular backups often times will use different credentials than your local account uses. Regardless you’ll probably want to look into the permissions you are given for the folder you are trying to backup to, or discuss this with the responsible party that sets up the shared folder permissions to ensure that the appropriate SQL services has the required credentials. Now you either will want to set up your back up to use the proper credentials, or ensure that the proper SQL Service account has the permissions to write to this folder (check BOL for service account information for SQL Server).</p> </blockquote> <h3>Performing the backup…</h3> <p>The following is a quick script to backup a sample database called “Backup_Testing” to a folder called “Backups” on a server called “DEV-CLUS-07”. This is a “script” created after using the task “Back up…” in SSMS:</p> <h5><u><font face="Times New Roman">Backup to Network Share using UNC path (T-SQL example):</font></u></h5> <div class="code"><font face="Times New Roman"><font color="#0000ff">BACKUP</font><font color="#808080"> </font><font color="#0000ff">DATABASE</font><font color="#808080"> </font><font color="#000000">[Backup_Testing]</font></font><font face="Times New Roman"><font color="#808080">  <br /></font><font color="#0000ff">TO</font><font color="#808080">  </font><font color="#0000ff">DISK</font><font color="#808080"> </font><font color="#0000ff">=</font></font><font face="Times New Roman"><font color="#808080"> N'\\DEV-CLUS-07\Backups\NetShare01.bak'  <br /></font><font color="#0000ff">WITH</font><font color="#808080">  </font><font color="#0000ff">DESCRIPTION</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> N'Backing up the database to a network share drive'</font><font color="#000000">,</font></font><font face="Times New Roman"><font color="#808080">  <br /></font><font color="#000000">NOFORMAT,</font><font color="#808080"> </font><font color="#000000">INIT,</font><font color="#808080">  </font><font color="#0000ff">NAME</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> N'Backup_Testing_Full_DB_BkUp_to_Network_Share'</font><font color="#000000">,</font></font><font face="Times New Roman"><font color="#808080">  <br /></font><font color="#000000">SKIP,</font><font color="#808080"> </font><font color="#000000">NOREWIND,</font><font color="#808080"> </font><font color="#000000">NOUNLOAD,</font><font color="#808080">  </font><font color="#000000">STATS</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> </font><font color="#800000">10</font><font color="#000000">,</font><font color="#808080"> </font><font color="#000000">CHECKSUM</font></font><font color="#808080"> <br /></font><font color="#0000ff" face="Times New Roman">GO</font> </div> <p>Notice that the strings are prefixed with “N” to designate unicode strings; this is an important part to use when specifying the UNC directly.</p> <p>Now, instead of entering the UNC each time you want to create a backup you can create a “backup device” that specifies the UNC path for you; the drawback is that you must also specify the file name. This is ideal if you want to continuously use the same file over and over, and especially if you make heavy use of expirations for your backups and or want multiple choices of backups in a single file location (maybe for easier file administration).</p> <p>You first create the “backup device”. This is a backup device called “Network_Share_Device_01”, located in the same location as the previous example (except the file name has been also specified, which is “NetShareDev01.bak”).</p> <p><u><font face="Times New Roman">Create a “backup device” that points to a network share location (T-SQL example):</font></u></p> <div class="code"><font face="Times New Roman"><font color="#0000ff">USE</font><font color="#808080"> </font><font color="#000000">[master]</font></font><font color="#808080"> <br /></font><font color="#0000ff" face="Times New Roman">GO</font><font color="#808080"> <br /></font><font face="Times New Roman"><font color="#0000ff">EXEC</font><font color="#808080"> </font><font color="#000000">master.dbo.sp_addumpdevice</font><font color="#808080">    </font><font color="#000000">@devtype</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> N'disk'</font><font color="#000000">,</font></font><font face="Times New Roman"><font color="#808080">  <br />                                </font><font color="#000000">@logicalname</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> N'Network_Share_Device_01'</font><font color="#000000">,</font></font><font face="Times New Roman"><font color="#808080">  <br />                                </font><font color="#000000">@physicalname</font><font color="#808080"> </font><font color="#0000ff">=</font></font><font face="Times New Roman"><font color="#808080"> N'\\DEV-CLUS-07\Backups\NetShare01.bak' <br /></font><font color="#0000ff">GO</font></font> </div> <p>With this device in place you would use the same T-SQL as the first example; except instead of using the “TO DISK” option you’d use the “TO [device_name_here]” option…</p> <p><u><font face="Times New Roman">Backup to Network Share using backup device (T-SQL example):</font></u></p> <font face="Times New Roman"><font color="#0000ff">BACKUP</font><font color="#808080"> </font><font color="#0000ff">DATABASE</font><font color="#808080"> </font><font color="#000000">[Backup_Testing]</font></font><font face="Times New Roman"><font color="#808080">  <br /></font><font color="#0000ff">TO</font></font><font face="Times New Roman"><font color="#808080"> <font color="#000000">[Network_Share_Device_01]</font> <br /></font><font color="#0000ff">WITH</font><font color="#808080">  </font><font color="#0000ff">DESCRIPTION</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> N'Backing up the database to a network share drive'</font><font color="#000000">,</font></font><font face="Times New Roman"><font color="#808080">  <br /></font><font color="#000000">NOFORMAT,</font><font color="#808080"> </font><font color="#000000">INIT,</font><font color="#808080">  </font><font color="#0000ff">NAME</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> N'Backup_Testing_Full_DB_BkUp_to_Network_Share'</font><font color="#000000">,</font></font><font face="Times New Roman"><font color="#808080">  <br /></font><font color="#000000">SKIP,</font><font color="#808080"> </font><font color="#000000">NOREWIND,</font><font color="#808080"> </font><font color="#000000">NOUNLOAD,</font><font color="#808080">  </font><font color="#000000">STATS</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> </font><font color="#800000">10</font><font color="#000000">,</font><font color="#808080"> </font><font color="#000000">CHECKSUM</font></font><font color="#808080"> <br /></font><font color="#0000ff" face="Times New Roman">GO</font> <h3>Conclusion:</h3> <p>The next time you setup your database backup schedule, remember that you can directly backup to your network share and lower the risks of keeping the backup locally…and especially, lower the overhead of resources used on the local instance of SQL to get the backup to your final destination. </p> <p>As you can see, the T-SQL is straight forward; the only complication is in the setting up SQL properly to allow you to directly backup to a network share location, and really the only complicated part for the DBA is ensuring the proper permissions (at the OS level) are in place. This only becomes complicated in the scenarios where the DBA is not in control of permissions for the location of the database files and backup files. With some cooperation between database administrators and network administrators this can easily, and painlessly, be overcome.</p> <p>This is a win-win situation for everyone; it streamlines the process while improving the performance, security, and data integrity of the database!</p> <h3>References:</h3> <p>Microsoft: Backup Devices (SQL 2008) - <a title="http://msdn.microsoft.com/en-us/library/ms179313.aspx" href="http://msdn.microsoft.com/en-us/library/ms179313.aspx">http://msdn.microsoft.com/en-us/library/ms179313.aspx</a> <br />Microsoft: Media Sets, Media Families, and Backup Sets - <a title="http://msdn.microsoft.com/en-us/library/ms178062.aspx" href="http://msdn.microsoft.com/en-us/library/ms178062.aspx">http://msdn.microsoft.com/en-us/library/ms178062.aspx</a></p>Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com3tag:blogger.com,1999:blog-367836511975673915.post-8172885924470887302009-03-24T11:41:00.001-07:002009-03-24T11:41:30.870-07:00What are Tables in SQL Server?<h3>Overview</h3> <p>The following is a general overview of tables within a SQL Server database; it is by no means a thorough discussion of this subject. This is intended to be a primer for future discussions and covers only the basics, in no particular order.</p> <p>Table is a database object containing a column definition (or a collection of) in which data is stored row-by-row. You can think of this very similar to a spreadsheet; with a little extra power.</p> <p>A table is generally designed to handle most relational data in an organized manner; however, SQL Server has evolved to allow handling of data that is not organized.</p> <p>A table must include the following (at a minimum):</p> <ul> <li>A name for the table</li> <li>A column to store the data</li> </ul> <p>A table may include many other attributes/properties, such as: </p> <ul> <li>A description of the table</li> <li>A schema the table belongs to</li> <li>An identity column</li> <li>A filegroup the table belongs to</li> <li>Check Constraints, to ensure data integrity</li> <li>Multiple columns</li> <li>Relationships to other tables and/or objects within the database</li> <li>Indexes</li> </ul> <h3>CREATE TABLE syntax</h3> <p>The following is the bare minimum T-SQL syntax for creating a table:</p> <blockquote> <div class="code"><font color="#0000ff">CREATE</font><font color="#808080"> </font><font color="#0000ff">TABLE</font><font color="#808080">  <br />    </font><font color="#000000">[</font><font color="#808080"> </font><font color="#000000">database_name</font><font color="#808080"> </font><font color="#000000">.</font><font color="#808080"> </font><font color="#000000">[</font><font color="#808080"> </font><font color="#000000">schema_name</font><font color="#808080"> </font><font color="#000000">]</font><font color="#808080"> </font><font color="#000000">.</font><font color="#808080"> </font><font color="#000000">|</font><font color="#808080"> </font><font color="#000000">schema_name</font><font color="#808080"> </font><font color="#000000">.</font><font color="#808080"> </font><font color="#000000">]</font><font color="#808080"> </font><font color="#000000">table_name</font><font color="#808080">  <br />        </font><font color="#000000">(</font><font color="#808080"> </font><font color="#000000">{</font><font color="#808080"> </font><font color="#000000"><column_definition></font><font color="#808080"> </font><font color="#000000">|</font><font color="#808080"> </font><font color="#000000"><computed_column_definition></font><font color="#808080">  <br />                </font><font color="#000000">|</font><font color="#808080"> </font><font color="#000000"><column_set_definition></font><font color="#808080"> </font><font color="#000000">}</font><font color="#808080"> <br />        </font><font color="#000000">[</font><font color="#808080"> </font><font color="#000000"><table_constraint></font><font color="#808080"> </font><font color="#000000">]</font><font color="#808080"> </font><font color="#000000">[</font><font color="#808080"> </font><font color="#000000">,...n</font><font color="#808080"> </font><font color="#000000">]</font><font color="#808080"> </font><font color="#000000">)</font><font color="#808080">  <br />    </font><font color="#000000">[</font><font color="#808080"> </font><font color="#0000ff">ON</font><font color="#808080"> </font><font color="#000000">{</font><font color="#808080"> </font><font color="#000000">partition_scheme_name</font><font color="#808080"> </font><font color="#000000">(</font><font color="#808080"> </font><font color="#000000">partition_column_name</font><font color="#808080"> </font><font color="#000000">)</font><font color="#808080"> </font><font color="#000000">|</font><font color="#808080"> </font><font color="#000000">filegroup</font><font color="#808080">  <br />        </font><font color="#000000">|</font><font color="#808080"> "default" </font><font color="#000000">}</font><font color="#808080"> </font><font color="#000000">]</font><font color="#808080">  <br />    </font><font color="#000000">[</font><font color="#808080"> </font><font color="#000000">{</font><font color="#808080"> </font><font color="#000000">TEXTIMAGE_ON</font><font color="#808080"> </font><font color="#000000">{</font><font color="#808080"> </font><font color="#000000">filegroup</font><font color="#808080"> </font><font color="#000000">|</font><font color="#808080"> "default" </font><font color="#000000">}</font><font color="#808080"> </font><font color="#000000">]</font><font color="#808080">  <br />    </font><font color="#000000">[</font><font color="#808080"> </font><font color="#000000">FILESTREAM_ON</font><font color="#808080"> </font><font color="#000000">{</font><font color="#808080"> </font><font color="#000000">partition_scheme_name</font><font color="#808080"> </font><font color="#000000">|</font><font color="#808080"> </font><font color="#000000">filegroup</font><font color="#808080">  <br />        </font><font color="#000000">|</font><font color="#808080"> "default" </font><font color="#000000">}</font><font color="#808080"> </font><font color="#000000">]</font><font color="#808080"> <br />    </font><font color="#000000">[</font><font color="#808080"> </font><font color="#0000ff">WITH</font><font color="#808080"> </font><font color="#000000">(</font><font color="#808080"> </font><font color="#000000"><table_option></font><font color="#808080"> </font><font color="#000000">[</font><font color="#808080"> </font><font color="#000000">,...n</font><font color="#808080"> </font><font color="#000000">]</font><font color="#808080"> </font><font color="#000000">)</font><font color="#808080"> </font><font color="#000000">]</font><font color="#808080"> <br /></font><font color="#000000">[</font><font color="#808080"> </font><font color="#0000ff">;</font><font color="#808080"> </font><font color="#000000">]</font> </div> </blockquote> <p>See the "References" section at the end of this blog for the complete syntax. If you don't fully understand the text/symbols used in this syntax please reference my February 2008 posting <a title="Understanding MSDN Syntax Conventions" href="http://sqln.blogspot.com/2008/02/understanding-msdn-syntax-conventions.html">Understanding MSDN Syntax Conventions</a>.</p> <p>Here is a CREATE TABLE command, at it's simplest, issued in T-SQL:</p> <blockquote> <div class="code"><font color="#0000ff">CREATE</font><font color="#808080"> </font><font color="#0000ff">TABLE</font><font color="#808080"> </font><font color="#000000">myTable</font><font color="#808080"> <br />    </font><font color="#000000">(myColumn</font><font color="#808080"> </font><font color="#0000ff">TINYINT</font><font color="#000000">)</font><font color="#0000ff">;</font> </div> </blockquote> <p>This is a very simple T-SQL command that will create a table called "myTable". The table will contain 1 column called "myColumn", this column's data type is "INT" (which is short form for Integer); thus only allowing whole numbers to be entered. The numbers are limited to a range of numbers between 0 and 255.</p> <h3>Columns</h3> <p>A column definition will include the following (at a minimum):</p> <ul> <li>The name of the column</li> <li>A data type, and any attributes required to define the data type</li> <li>If it allows NULL values</li> <li>If it is Identity Specific</li> </ul> <p>A column definition may include other attributes/properties, such as:</p> <ul> <li>A default value (or binding)</li> <li>A description</li> <li>A computed column specification</li> </ul> <h3>Example</h3> <p>As mentioned earlier a table will include, at minimum, a column definition. In most cases you will find multiple column definitions within a table. An example customer table for a small business might include a column indicating the CustomerID, CustomerName, BusinessName, Address, PhoneNumber. The table might be called "Customers". To create this example table the T-SQL command might look like this: </p> <blockquote> <div class="code"><font color="#0000ff">CREATE</font><font color="#808080"> </font><font color="#0000ff">TABLE</font><font color="#808080"> </font><font color="#000000">Customers</font><font color="#808080"> <br /></font><font color="#000000">(</font><font color="#808080"> <br />    </font><font color="#000000">CustomerID</font><font color="#808080"> </font><font color="#0000ff">INT</font><font color="#808080"> </font><font color="#0000ff">PRIMARY</font><font color="#808080"> </font><font color="#0000ff">KEY</font><font color="#808080"> </font><font color="#0000ff">NOT</font><font color="#808080"> </font><font color="#0000ff">NULL</font><font color="#000000">,</font><font color="#808080"> <br />    </font><font color="#000000">CustomerName</font><font color="#808080"> </font><font color="#0000ff">VARCHAR</font><font color="#000000">(</font><font color="#800000">100</font><font color="#000000">)</font><font color="#808080"> </font><font color="#0000ff">NOT</font><font color="#808080"> </font><font color="#0000ff">NULL</font><font color="#000000">,</font><font color="#808080"> <br />    </font><font color="#000000">BusinessName</font><font color="#808080"> </font><font color="#0000ff">VARCHAR</font><font color="#000000">(</font><font color="#800000">100</font><font color="#000000">)</font><font color="#808080"> </font><font color="#0000ff">NULL</font><font color="#000000">,</font><font color="#808080"> <br />    </font><font color="#000000">[Address]</font><font color="#808080"> </font><font color="#0000ff">VARCHAR</font><font color="#000000">(</font><font color="#800000">500</font><font color="#000000">)</font><font color="#808080"> </font><font color="#0000ff">NULL</font><font color="#000000">,</font><font color="#808080"> <br />    </font><font color="#000000">PhoneNumber</font><font color="#808080"> </font><font color="#0000ff">VARCHAR</font><font color="#000000">(</font><font color="#800000">10</font><font color="#000000">)</font><font color="#808080"> </font><font color="#0000ff">NULL</font><font color="#808080"> <br /></font><font color="#000000">)</font> </div> </blockquote> <p>Notice that the column named "Address" is enclosed in square brackets "[]", this is because "Address" is a reserved keyword in Transact-SQL and can only be used as an identifier object if it is called using one of the "Delimited Identifiers".  There are other acceptable delimited identifiers that may be used, you may have to set the SQL Server configuration for quoted_identifiers, or follow other methods to use these. Refer to Books Online (BOL) <a title="Delimited Identifiers" href="http://technet.microsoft.com/en-us/library/ms176027.aspx" target="_blank">Delimited Identifiers</a> (see resources at end of posting) for further information.</p> <p>Also, you may have noticed that this table construction isn't the best there is. Logically the "Address" column currently would contain a complete address in the field (such as: 123 Anywhere St, Anyplace, State, 12345). If we wanted to search this field for all customers that are in the state of California or Hawaii then we would have to search using a LIKE operator (this will be discussed further later); this can take quite some time in SQL depending on how large the table is. Also, if we want to further list our customers by city alphabetically and group them by state then we introduce a complex T-SQL query to obtain this seemingly simple information.</p> <p>To make this better we would perform what is known as "Normalization" on our tables. Normalization will be discussed in further detail in a future blog. Right now, the grasp of what a table is, what is does, and how the data is stored is the important information to know.</p> <h3>Conclusion</h3> <p>As you can see a table is very much a corner stone to any database, without a table there is no where to store your data in your database. A table can be very simple to setup; while also can be very complex to maintain a great amount of integrity and Business Intelligence rules. This is just a primer discussion, if you want to delve deeper into tables you will want to start your exploration with BOL's <a title="Understanding Tables" href="http://technet.microsoft.com/en-us/library/ms189905.aspx" target="_blank">Understanding Tables</a> section and follow the appropriate links to further your knowledge. You will find that tables can be very powerful if you setup the structure properly and utilize the appropriate techniques to ensure data integrity. I've already discussed some attributes of tables in earlier postings, such as my postings about <a title="Introduction to Indexes" href="http://sqln.blogspot.com/2008/05/introduction-to-indexes.html" target="_blank">Indexes</a>. There is a wealth of knowledge out there on this subject and may seem like you already know most of it, but you will find that there are many aspects of tables that you may not have known about. I encourage you to take a few moments and review the BOL sections mentioned here, at a minimum. You may also want to look into <a title="Creating and Modifying Table Basics" href="http://msdn.microsoft.com/en-us/library/ms177399.aspx" target="_blank">Temporary Tables</a> and for you SQL 2005+ users look into the new data type <a title="table (Transact-SQL)" href="http://msdn.microsoft.com/en-us/library/ms175010.aspx" target="_blank">Table</a>.</p> <h3>References</h3> <p>CREATE TABLE (Transact-SQL): <a title="http://technet.microsoft.com/en-us/library/ms174979.aspx" href="http://technet.microsoft.com/en-us/library/ms174979.aspx">http://technet.microsoft.com/en-us/library/ms174979.aspx</a> <br />Understanding MSDN Syntax Conventions: <a title="http://sqln.blogspot.com/2008/02/understanding-msdn-syntax-conventions.html" href="http://sqln.blogspot.com/2008/02/understanding-msdn-syntax-conventions.html">http://sqln.blogspot.com/2008/02/understanding-msdn-syntax-conventions.html</a> <br />TechNet - Tables: <a title="http://technet.microsoft.com/en-us/library/ms189104.aspx" href="http://technet.microsoft.com/en-us/library/ms189104.aspx">http://technet.microsoft.com/en-us/library/ms189104.aspx</a> <br />Understanding Tables: <a title="http://technet.microsoft.com/en-us/library/ms189905.aspx" href="http://technet.microsoft.com/en-us/library/ms189905.aspx">http://technet.microsoft.com/en-us/library/ms189905.aspx</a> <br />Creating and Modifying Table Basics: <a title="http://msdn.microsoft.com/en-us/library/ms177399.aspx" href="http://msdn.microsoft.com/en-us/library/ms177399.aspx">http://msdn.microsoft.com/en-us/library/ms177399.aspx</a> <br />table (Transact-SQL): <a title="http://msdn.microsoft.com/en-us/library/ms175010.aspx" href="http://msdn.microsoft.com/en-us/library/ms175010.aspx">http://msdn.microsoft.com/en-us/library/ms175010.aspx</a></p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-6944983306746256382009-01-06T09:11:00.001-08:002009-01-06T09:11:53.531-08:00New Article: Transaction Log Growth, do you need it?<p>Yesterday, <a title="SQLServerCentral.com" href="http://www.sqlservercentral.com/" target="_blank">SQL Server Central</a> had published another article of mine: "<a title="Transaction Log Growth, do you need it?" href="http://www.sqlservercentral.com/articles/Transaction+Log/65255/" target="_blank">Transaction Log Growth, do you need it?</a>". It got some good <a title="Discussion for " transaction log growth, do you need it?" article" href="http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx" target="_blank">reviews</a>, and just over 8,000 views before 9 AM; I've been told anything over 4,000 views (total) is pretty good.</p> <p>Be sure to look it over when you have a chance, it may prove helpful in the future or just might provide you a little more insight about Transaction Logs and how they work with SQL Server and your databases.</p> <p>Until next time, Happy Coding!!!</p> <p> </p> <p>References:</p> <p>Article: <a title="http://www.sqlservercentral.com/articles/Transaction+Log/65255/" href="http://www.sqlservercentral.com/articles/Transaction+Log/65255/">http://www.sqlservercentral.com/articles/Transaction+Log/65255/</a> <br />Discussion: <a title="http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx" href="http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx">http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx</a></p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-51331344813102327802008-12-31T11:56:00.000-08:002008-12-31T11:57:18.674-08:00Updated: Search Stored Procedures for Dependencies / References<p>In a discussion on the MSDN Forums I had posted my original code to search a Stored Procedure (search any object within SQL really) for specific text (<a title="http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/847b421d-332d-40f8-9649-b26fc2306920" href="http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/847b421d-332d-40f8-9649-b26fc2306920">http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/847b421d-332d-40f8-9649-b26fc2306920</a>). You can view my original blog post describing this code block at: <a title="http://sqln.blogspot.com/2008/10/searching-text-of-programmable-objects.html" href="http://sqln.blogspot.com/2008/10/searching-text-of-programmable-objects.html">http://sqln.blogspot.com/2008/10/searching-text-of-programmable-objects.html</a>.</p> <p>Adam Haines had posted a wonderful enhancement to my T-SQL code. He had returned the matching text result as an XML instead of a text, this allows the user to click on the returned text and have the resulting text appear in a new window.</p> <p>The new code, with the XML returned results, is:</p> <div class="code"><font color="#0000ff">DECLARE</font><font color="#808080"> </font><font color="#000000">@StringToSearch</font><font color="#808080"> </font><font color="#0000ff">NVARCHAR</font><font color="#000000">(MAX)</font><font color="#808080"> <br /></font><font color="#0000ff">SET</font><font color="#808080"> </font><font color="#000000">@StringToSearch</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> 'ENTER_SEARCH_TEXT_HERE' <br /> <br /></font><font color="#0000ff">SELECT</font><font color="#808080">     <br />    </font><font color="#000000">[</font><font color="#0000ff">name</font><font color="#000000">],</font><font color="#808080">    <br />    </font><font color="#000000">(</font><font color="#808080">    <br />        </font><font color="#0000ff">SELECT</font><font color="#808080">     <br />            </font><font color="#0000ff">OBJECT_DEFINITION</font><font color?black?="color?black?">(obj2.</font><font color="#0000ff">object_id</font><font color="#000000">)</font><font color="#808080"> </font><font color="#0000ff">AS</font><font color="#808080"> </font><font color="#000000">[</font><font color="#0000ff">text</font><font color="#000000">()]</font><font color="#808080">    <br />        </font><font color="#0000ff">from</font><font color="#808080"> </font><font color="#008000">sys.all_objects</font><font color="#808080"> </font><font color="#000000">obj2</font><font color="#808080">    <br />        </font><font color="#0000ff">where</font><font color="#808080"> </font><font color="#000000">obj2.</font><font color="#0000ff">object_id</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> </font><font color="#000000">obj1.</font><font color="#0000ff">object_id</font><font color="#808080">    <br />        </font><font color="#0000ff">FOR XML PATH(</font><font color="#808080">''</font><font color="#000000">),</font><font color="#808080"> </font><font color="#0000ff">TYPE</font><font color="#808080">    <br />    </font><font color="#000000">)</font><font color="#808080"> </font><font color="#0000ff">AS</font><font color="#808080"> </font><font color="#000000">Obj_text,</font><font color="#808080">    <br />    </font><font color="#000000">[type]</font><font color="#808080"> </font><font color="#0000ff">as</font><font color="#808080"> </font><font color="#000000">ObjType</font><font color="#808080">     <br /></font><font color="#0000ff">FROM</font><font color="#808080"> </font><font color="#008000">sys.all_objects</font><font color="#808080"> </font><font color="#000000">obj1</font><font color="#808080">   <br /></font><font color="#0000ff">WHERE</font><font color="#808080"> </font><font color="#0000ff">OBJECT_DEFINITION</font><font color="#000000">(</font><font color="#0000ff">object_id</font><font color="#000000">([</font><font color="#0000ff">name</font><font color="#000000">]))</font><font color="#808080"> </font><font color="#0000ff">LIKE</font><font color="#808080"> '%' </font><font color="#000000">+</font><font color="#808080"> </font><font color="#000000">@StringToSearch</font><font color="#808080"> </font><font color="#000000">+</font><font color="#808080"> '%' <br /></font><font color="#006400">--Optional to limit search results....     <br />--AND [is_ms_shipped] = 0 --Only search user & post SQL release objects <br />--Change above to 1 if want to search only Microsoft provided objects released with SQL Server <br />--AND [type] = 'P' --Search only Stored Procedures <br />--AND [type] <> 'V' --Exclude Views <br />--See referenced article for listing of additional object types <br />--http://www.sqlservercentral.com/articles/T-SQL/63471/</font> </div> <p>Thanks again for this enhancement Adam!</p> <p>Until next time, Happy Coding!!!</p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-78612739967327534122008-12-12T14:55:00.000-08:002008-12-12T14:57:06.310-08:00Books Online could be better...and we, the users, already can make it better!<p>Yesterday, Kalen Delaney wrote a small article discussing if MS should or shouldn't build in more warning messages or taking better actions in warning users about potential problems from performing tasks/commands within SQL Server. You can read her article at: <a title="http://www.sqlmag.com/Articles/ArticleID/101033/101033.html" href="http://www.sqlmag.com/Articles/ArticleID/101033/101033.html">http://www.sqlmag.com/Articles/ArticleID/101033/101033.html</a></p> <p>I agree that to a certain extent the DBA is responsible for the database and should know how to use the software thoroughly...so ideally the database would never be at an unknown risk of data loss. Unfortunately, SQL Server has jumped from the realm of an application to the realm of a data platform! This changes things quite a bit; there is no realistic expectation that a single DBA (or even a small team of DBAs) can possibly know the database software and all of its commands and internal workings 100%. So there is some portion of relief that Microsoft must provide...rather it is a method of warning the DBA from messages, errors, or warnings I don't know or speculate a foreseeable method. I do agree that Books Online (BOL) is considered the SQL Server documentation and it is Microsoft's responsibility to ensure that the documentation provides all of the information (including inner workings where necessary) of commands and features that have potential unsafe effects.</p> <p>The question is where does this responsibility end? Also, where does it fall onto the DBA (or end-user) shoulders to piece together all of this information and understand how they are utilizing the data platform and the data loss potential is created by this usage?</p> <p>The first thing that comes to my mind is that Microsoft has taken a step in adding a way for the user's of BOL to add in their content that can point this type of stuff out. Why is it that we DBAs aren't doing this? I hope after reading this maybe our community will take a moment or two to place a useful comment (especially warning of potential data loss areas) in BOL for the other less knowledgeable DBAs.</p> <p>Until next time, Happy Coding!</p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-49329718626768553302008-10-23T11:26:00.000-07:002008-10-23T11:31:25.049-07:00TSQL Code: Compare Tables of Databases on Separate Servers<p><font size="2" face="Times New Roman"><em>NOTE: This script was originally developed to compare table row counts, but with the below mentioned modifications this could be helpful if you have a database that is shipped to another server and often times needs to be ETL into the other database and you are worried there may be records that aren't getting transferred properly (or at all).</em></font></p> <p>The other day I came across a question on how to compare the tables in one database to the tables in another database, this was in concerns to a migration project. This person had a database that existed on an older server running SQL 2000 and they chose to migrate their database to a new server running SQL Server 2005. They had wanted to be able to somehow be able to visually display a comparison of the two databases to prove the conversion was successful, and also to display the comparisons of the tables. Results meeting above requirements are shown in Figure 1.</p> <p><a href="http://lh4.ggpht.com/jrea8830/SQDC8xeEJMI/AAAAAAAAACw/O8Y17qrfaZw/s1600-h/AW_Compare_All%5B4%5D.png"><img style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" border="0" alt="AW_Compare_All" src="http://lh6.ggpht.com/jrea8830/SQDC9lpb_nI/AAAAAAAAAC0/cxjYaHCfuOU/AW_Compare_All_thumb%5B2%5D.png?imgmax=800" width="525" height="165" /></a>  <br /><strong><font size="1">Figure 1 (results of running sp_CompareTables_All)</font></strong></p> <p>In response to this I had started to develop a TSQL script that created a stored procedure that would allow you to run it from the current database and specify the location of the original database. The script would then collect the tables and count the records within each table for both databases. It will display the table names on the outside (left and right) of the results, and the record counts will be next to each other on the inside (next to their table names); which will allow for a very simple and pleasing visual comparison. I wanted, and did, avoid using cursors or other techniques that will potentially bog down the system resources (such as the stored proc 'sp_MSForEachDB' and 'sp_MSForEachTable').</p> <p>Now, this is a rough draft that I had thrown together and tested over a lunch period; so, there are some issues that can still be cleaned up on the script and it lacks the ability to detect if a table exists on one database, but not the other. </p> <p>At the end of the script I'll also provide a quick comment that will allow you to change the results from displaying all tables and their row counts to display only the tables with mismatched row counts (which may be useful if you want to use this script as a method to troubleshoot databases that tend to not migrate all records). </p> <p>The first problem faced is how to best access the server with the original database; while there are many options I chose to use the built-in stored procedure "<a title="sp_addlinkedserver (Transact-SQL)" target="_blank" href="http://msdn.microsoft.com/en-us/library/ms190479.aspx">sp_addlinkedserver</a>". This procedure seems to be simple to implement and allows for seamless integration into TSQL code. Testing for this script was performed on the AdventureWorks database (developed for SQL 2005) on both test systems. The 'originating' database was on a SQL 2005 instance (although testing was briefly performed on SQL 2000 and SQL 2008 to validate compatibility). The 'originating' server is called TestServer35, the database is on an instance called Dev05; the database for both instances is called AdventureWorks2005. This information becomes important when using the sp_addlinkedserver command. I used the following TSQL code:</p> <blockquote> <div class="code"><font color="#0000ff">EXEC</font><font color="#808080"> </font><font color="#ff0000">sp_addlinkedserver</font><font color="#808080">    <br />    </font><font color="#000000">@server</font><font color="#0000ff">=</font><font color="#808080">'TestServer35-5'</font><font color="#000000">,</font><font color="#808080">  <br />    </font><font color="#000000">@srvproduct</font><font color="#0000ff">=</font><font color="#808080">''</font><font color="#000000">,</font><font color="#808080"> <br />    </font><font color="#000000">@catalog</font><font color="#0000ff">=</font><font color="#808080">'AdventureWorks2005'</font><font color="#000000">,</font><font color="#808080"> <br />    </font><font color="#000000">@provider</font><font color="#0000ff">=</font><font color="#808080">'SQLNCLI'</font><font color="#000000">,</font><font color="#808080">  <br />    </font><font color="#000000">@datasrc</font><font color="#0000ff">=</font><font color="#808080">'Server35\Dev05'</font> </div> <div class="code"> </div> </blockquote> <p>As you can see, the linked server is referenced as TestServer35-5. We will use this reference, in a four-part identifier (Server.Catalog.Schema.Table). The next obstacle is to obtain a listing of tables and their row counts. I used a script I had modified last year to perform this since this script will run both on SQL 2005 and SQL 2000 (you can view my script on SQLServerCentral.com's Script section at: <a title="http://www.sqlservercentral.com/scripts/Administration/61766/" href="http://www.sqlservercentral.com/scripts/Administration/61766/">http://www.sqlservercentral.com/scripts/Administration/61766/</a>). I then take the results of this and store them into a temporary table; I also do this for the new database (on local server where this stored proc is running at).</p> <p>Then comes the simple part of joining the two temp tables into a final temp table. I chose this route because I wanted to have the two database in separate temp tables in the event I want to work with that data, which I will be working with the data in my update to determine if a table is missing from one of the databases.</p> <p>Here is the TSQL code I used (remember if you want to use this you will need to change the linked server information to the correct information; as well as to create this stored proc in the appropriate database):</p> <blockquote> <div class="code"><font color="#006400">--Change the database name to the appropriate databse <br /></font><font color="#0000ff">USE</font><font color="#808080"> </font><font color="#000000">[AdventureWorks2005]</font><font color="#0000ff">;</font><font color="#808080"> <br /></font><font color="#0000ff">GO</font><font color="#808080"> <br /> <br /></font><font color="#0000ff">CREATE</font><font color="#808080"> </font><font color="#0000ff">PROCEDURE</font><font color="#808080"> </font><font color="#000000">sp_CompareTables_all</font><font color="#808080"> <br /> </font><font color="#0000ff">AS</font><font color="#808080"> <br />  <br /> </font><font color="#0000ff">CREATE</font><font color="#808080"> </font><font color="#0000ff">TABLE</font><font color="#808080"> </font><font color="#0000ff">#</font><font color="#000000">tblNew</font><font color="#808080"> <br /></font><font color="#000000">(</font><font color="#808080"> </font><font color="#000000">tblName</font><font color="#808080"> </font><font color="#0000ff">varchar</font><font color="#000000">(</font><font color="#800000">50</font><font color="#000000">),</font><font color="#808080"> </font><font color="#000000">CountRows</font><font color="#808080"> </font><font color="#0000ff">int</font><font color="#808080"> </font><font color="#000000">)</font><font color="#808080"> <br /> <br /></font><font color="#0000ff">INSERT</font><font color="#808080"> </font><font color="#0000ff">INTO</font><font color="#808080"> </font><font color="#0000ff">#</font><font color="#000000">tblNew</font><font color="#808080"> <br /></font><font color="#000000">(</font><font color="#808080"> </font><font color="#000000">tblName,</font><font color="#808080"> </font><font color="#000000">CountRows)</font><font color="#808080"> <br /></font><font color="#0000ff">SELECT</font><font color="#808080"> </font><font color="#000000">o.</font><font color="#0000ff">name</font><font color="#808080"> </font><font color="#0000ff">AS</font><font color="#808080"> "Table Name"</font><font color="#000000">,</font><font color="#808080"> </font><font color="#000000">i.rowcnt</font><font color="#808080"> </font><font color="#0000ff">AS</font><font color="#808080"> "Row Count" <br /></font><font color="#0000ff">FROM</font><font color="#808080"> </font><font color="#000000">sysobjects</font><font color="#808080"> </font><font color="#000000">o,</font><font color="#808080"> </font><font color="#000000">sysindexes</font><font color="#808080"> </font><font color="#000000">i</font><font color="#808080"> <br /></font><font color="#0000ff">WHERE</font><font color="#808080"> </font><font color="#000000">i.</font><font color="#0000ff">id</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> </font><font color="#000000">o.</font><font color="#0000ff">id</font><font color="#808080"> <br /></font><font color="#0000ff">AND</font><font color="#808080"> </font><font color="#000000">indid</font><font color="#808080"> </font><font color="#0000ff">IN</font><font color="#000000">(</font><font color="#800000">0</font><font color="#000000">,</font><font color="#800000">1</font><font color="#000000">)</font><font color="#808080"> <br /></font><font color="#0000ff">AND</font><font color="#808080"> </font><font color="#000000">xtype</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> 'u' <br /></font><font color="#0000ff">AND</font><font color="#808080"> </font><font color="#000000">o.</font><font color="#0000ff">name</font><font color="#808080"> </font><font color="#000000"><></font><font color="#808080"> 'sysdiagrams' <br /></font><font color="#0000ff">ORDER</font><font color="#808080"> </font><font color="#0000ff">BY</font><font color="#808080"> </font><font color="#000000">o.</font><font color="#0000ff">name</font><font color="#808080"> <br /> <br /></font><font color="#0000ff">CREATE</font><font color="#808080"> </font><font color="#0000ff">TABLE</font><font color="#808080"> </font><font color="#0000ff">#</font><font color="#000000">tblOLD</font><font color="#808080"> <br /></font><font color="#000000">(</font><font color="#808080"> </font><font color="#000000">tblName</font><font color="#808080"> </font><font color="#0000ff">varchar</font><font color="#000000">(</font><font color="#800000">50</font><font color="#000000">),</font><font color="#808080"> </font><font color="#000000">CountRows</font><font color="#808080"> </font><font color="#0000ff">int</font><font color="#808080"> </font><font color="#000000">)</font><font color="#808080"> <br /> <br /></font><font color="#0000ff">INSERT</font><font color="#808080"> </font><font color="#0000ff">INTO</font><font color="#808080"> </font><font color="#0000ff">#</font><font color="#000000">tblOLD</font><font color="#808080"> <br /></font><font color="#000000">(</font><font color="#808080"> </font><font color="#000000">tblName,</font><font color="#808080"> </font><font color="#000000">CountRows)</font><font color="#808080"> <br /></font><font color="#0000ff">SELECT</font><font color="#808080"> </font><font color="#000000">lo.</font><font color="#0000ff">name</font><font color="#808080"> </font><font color="#0000ff">AS</font><font color="#808080"> "Table Name"</font><font color="#000000">,</font><font color="#808080"> </font><font color="#000000">li.rowcnt</font><font color="#808080"> </font><font color="#0000ff">AS</font><font color="#808080"> "Row Count" <br /></font><font color="#006400">--******** <br />--Replace TestServer35-5 and AdventureWorks2005 below with your appropriate values <br />--******** <br /></font><font color="#0000ff">FROM</font><font color="#808080"> </font><font color="#000000">[TestServer35-</font><font color="#800000">5</font><font color="#000000">].[AdventureWorks2005].[dbo].[sysobjects]</font><font color="#808080"> </font><font color="#000000">lo,</font><font color="#808080">  <br />    </font><font color="#000000">[TestServer35-</font><font color="#800000">5</font><font color="#000000">].[AdventureWorks2005].[dbo].[sysindexes]</font><font color="#808080"> </font><font color="#000000">li</font><font color="#808080"> <br /></font><font color="#0000ff">WHERE</font><font color="#808080"> </font><font color="#000000">li.</font><font color="#0000ff">id</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> </font><font color="#000000">lo.</font><font color="#0000ff">id</font><font color="#808080"> <br /></font><font color="#0000ff">AND</font><font color="#808080"> </font><font color="#000000">indid</font><font color="#808080"> </font><font color="#0000ff">IN</font><font color="#000000">(</font><font color="#800000">0</font><font color="#000000">,</font><font color="#800000">1</font><font color="#000000">)</font><font color="#808080"> <br /></font><font color="#0000ff">AND</font><font color="#808080"> </font><font color="#000000">xtype</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> 'u' <br /></font><font color="#0000ff">AND</font><font color="#808080"> </font><font color="#000000">lo.</font><font color="#0000ff">name</font><font color="#808080"> </font><font color="#000000"><></font><font color="#808080"> 'sysdiagrams' <br /></font><font color="#0000ff">ORDER</font><font color="#808080"> </font><font color="#0000ff">BY</font><font color="#808080"> </font><font color="#000000">lo.</font><font color="#0000ff">name</font><font color="#808080"> <br /> <br /></font><font color="#0000ff">CREATE</font><font color="#808080"> </font><font color="#0000ff">TABLE</font><font color="#808080"> </font><font color="#0000ff">#</font><font color="#000000">tblDiff</font><font color="#808080"> <br /></font><font color="#000000">(</font><font color="#808080"> </font><font color="#000000">OldTable</font><font color="#808080"> </font><font color="#0000ff">varchar</font><font color="#000000">(</font><font color="#800000">50</font><font color="#000000">),</font><font color="#808080"> </font><font color="#000000">OldRowCount</font><font color="#808080"> </font><font color="#0000ff">int</font><font color="#000000">,</font><font color="#808080"> </font><font color="#000000">NewRowCount</font><font color="#808080"> </font><font color="#0000ff">int</font><font color="#000000">,</font><font color="#808080"> </font><font color="#000000">NewTableName</font><font color="#808080"> </font><font color="#0000ff">varchar</font><font color="#000000">(</font><font color="#800000">50</font><font color="#000000">))</font><font color="#808080"> <br /> <br /></font><font color="#0000ff">INSERT</font><font color="#808080"> </font><font color="#0000ff">INTO</font><font color="#808080"> </font><font color="#0000ff">#</font><font color="#000000">tblDiff</font><font color="#808080"> <br /></font><font color="#000000">(</font><font color="#808080"> </font><font color="#000000">OldTable,</font><font color="#808080"> </font><font color="#000000">OldRowCount,</font><font color="#808080"> </font><font color="#000000">NewRowCount,</font><font color="#808080"> </font><font color="#000000">NewTableName</font><font color="#808080"> </font><font color="#000000">)</font><font color="#808080"> <br /></font><font color="#0000ff">SELECT</font><font color="#808080"> </font><font color="#000000">ol.tblName,</font><font color="#808080"> </font><font color="#000000">ol.CountRows,</font><font color="#808080"> </font><font color="#000000">nw.CountRows,</font><font color="#808080"> </font><font color="#000000">nw.tblName</font><font color="#808080"> <br /></font><font color="#0000ff">From</font><font color="#808080">    </font><font color="#0000ff">#</font><font color="#000000">tblNew</font><font color="#808080"> </font><font color="#000000">nw</font><font color="#808080"> <br /></font><font color="#0000ff">JOIN</font><font color="#808080"> </font><font color="#0000ff">#</font><font color="#000000">tblOLD</font><font color="#808080"> </font><font color="#000000">ol</font><font color="#808080"> <br /></font><font color="#0000ff">ON</font><font color="#808080"> </font><font color="#000000">(ol.tblName</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> </font><font color="#000000">nw.tblName</font><font color="#808080"> </font><font color="#0000ff">AND</font><font color="#808080"> </font><font color="#000000">ol.CountRows</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> </font><font color="#000000">nw.CountRows)</font><font color="#808080"> <br />         <br /></font><font color="#0000ff">SELECT</font><font color="#808080"> </font><font color="#000000">*</font><font color="#808080"> </font><font color="#0000ff">FROM</font><font color="#808080"> </font><font color="#0000ff">#</font><font color="#000000">tblDiff</font><font color="#808080"> <br />         <br /></font><font color="#0000ff">DROP</font><font color="#808080"> </font><font color="#0000ff">TABLE</font><font color="#808080"> </font><font color="#0000ff">#</font><font color="#000000">tblNEW</font><font color="#808080"> <br /></font><font color="#0000ff">DROP</font><font color="#808080"> </font><font color="#0000ff">TABLE</font><font color="#808080"> </font><font color="#0000ff">#</font><font color="#000000">tblOLD</font><font color="#808080"> <br /></font><font color="#0000ff">DROP</font><font color="#808080"> </font><font color="#0000ff">TABLE</font><font color="#808080"> </font><font color="#0000ff">#</font><font color="#000000">tblDiff</font> </div> </blockquote> <p> </p> <p>You simply execute the code with the following TSQL:</p> <blockquote> <div class="code"><font color="#0000ff">EXECUTE</font><font color="#808080"> </font><font color="#000000">sp_CompareTables_All</font> </div> </blockquote> <div class="code"> </div> <p>The results of this script are shown in Figure 1 (above). </p> <p>Now, this is great if you want to have a list that you can go through yourself to verify each table matches in row counts. But, what if that database has 1000 or more tables? What if you are just, simply put, lazy? Why not utilize SQL Server to process this information for you?</p> <p>Well, I sure enough did just that. With a very small modification to this script you can easily have it only display any tables that don't match up in record counts.</p> <p>All you have to do is change the INSERT INTO #tblDiff block's "ON" statement to join if the CountRows are NOT equal. The following is the modified block of code; the remaining stored procedure remains the same:</p> <blockquote> <div class="code"><font color="#0000ff">ON</font><font color="#808080"> </font><font color="#000000">(ol.tblName</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> </font><font color="#000000">nw.tblName</font><font color="#808080"> </font><font color="#0000ff">AND</font><font color="#808080"> </font><font color="#000000">ol.CountRows</font><font color="#808080"> </font><font color="#000000"><></font><font color="#808080"> </font><font color="#000000">nw.CountRows)</font> </div> <div class="code"> </div> </blockquote> <p>I did also rename the stored procedure from "sp_CompareTables_All" to "sp_CompareTables_Diff", but this is optional for your own ability to clarify which stored proc is being used.</p> <p>To get some results I had made a few modifications to the AdventureWorks2005 database. I had added a couple of rows to a table, and removed some rows from two tables. The results of the stored proc showing only the different tables are shown in Figure 2.</p> <p><a href="http://lh4.ggpht.com/jrea8830/SQDC-K0HmEI/AAAAAAAAAC4/hqbNoul7Fho/s1600-h/AW_Compare_Diff%5B3%5D.png"><img style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" border="0" alt="AW_Compare_Diff" src="http://lh6.ggpht.com/jrea8830/SQDC-q0bXtI/AAAAAAAAAC8/mc0gw8LvFos/AW_Compare_Diff_thumb%5B1%5D.png?imgmax=800" width="670" height="115" /></a> <br /><strong><font size="1">Figure 2 (results of running sp_CompareTables_Diff)</font></strong></p> <p>As you can see the ability to change this script to show all tables or only different tables is very simple. Even setting up this script is simple, where the hardest part of the whole thing is adding a linked server (which is fairly simple also). </p> <p>In a future post I'll revisit this script and include the ability to display tables that exist on one database, but not in the other. Be sure to check back for this update.</p> <p>Until next time...Happy Coding!!</p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com3tag:blogger.com,1999:blog-367836511975673915.post-33579434387132218622008-10-17T10:14:00.000-07:002008-10-20T07:27:27.645-07:00Searching text of programmable objects...<p>This is a follow-up posting to my article "Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005" I wrote for SQLServerCentral.com (<a title="http://www.sqlservercentral.com/articles/T-SQL/63471/" href="http://www.sqlservercentral.com/articles/T-SQL/63471/">http://www.sqlservercentral.com/articles/T-SQL/63471/</a>). This will work with both SQL Server 2005 and SQL Server 2008.</p> <p>I've found a few instances where I would've liked to be able to search through a Stored Procedure or a View for specific text in the definition; specifically to check to see if there was a reference to another Stored Procedure or View. Originally to solve this problem I had just queried the syscomments legacy view for the search string joining with sysobjects, while this was effective it has its limitations...such as this only searches objects created post-release of SQL Server. So, what happens if you want to search for a specific string in ALL of the programmable objects? Well, then we fall back to our trusty view of sys.all_objects! We can join it with sys.all_sql_modules and get almost all of what we need...almost.</p> <p>Now, we can do a clever query that will join sys.all_objects and sys.all_sql_modules. Something like:</p> <div class="code"><font color="#0000ff">DECLARE</font><font color="#808080"> </font><font color="#000000">@SearchString</font><font color="#808080"> </font><font color="#0000ff">NVARCHAR</font><font color="#000000">(MAX)</font><font color="#808080"> <br /></font><font color="#0000ff">SET</font><font color="#808080"> </font><font color="#000000">@SearchString</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> 'ENTER_SEARCH_TEXT_HERE' <br /></font><font color="#0000ff">SELECT</font><font color="#808080"> </font><font color="#000000">[ao].[</font><font color="#0000ff">name</font><font color="#000000">],</font><font color="#808080"> </font><font color="#000000">[asm].[definition]</font><font color="#808080"> <br /></font><font color="#0000ff">FROM</font><font color="#808080"> </font><font color="#000000">sys.all_objects</font><font color="#808080"> </font><font color="#000000">ao</font><font color="#808080"> <br /></font><font color="#0000ff">JOIN</font><font color="#808080"> </font><font color="#000000">sys.all_sql_modules</font><font color="#808080"> </font><font color="#000000">asm</font><font color="#808080"> <br /></font><font color="#0000ff">ON</font><font color="#808080"> </font><font color="#000000">[ao].[</font><font color="#0000ff">object_id</font><font color="#000000">]</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> </font><font color="#000000">[asm].[</font><font color="#0000ff">object_id</font><font color="#000000">]</font><font color="#808080"> <br /></font><font color="#0000ff">WHERE</font><font color="#808080"> </font><font color="#000000">[asm].[definition]</font><font color="#808080"> </font><font color="#0000ff">LIKE</font><font color="#808080"> '%' </font><font color="#000000">+</font><font color="#808080"> </font><font color="#000000">@SearchString</font><font color="#808080"> </font><font color="#000000">+</font><font color="#808080"> '%' <br /></font><font color="#006400">--AND [ao].[is_ms_shipped] = 0 --Only search user & post SQL release objects, or </font></div> <div class="code"><font color="#006400">--Change the above value to 1 to only include </font><font color="#006400">objects provided by Microsoft with the release of SQL Server</font></div> <div class="code"><font color="#006400">--Optional  to limit search results.... <br />--AND [ao].[type] = 'P' --Search only Stored Procedures <br />--AND [ao].[type] <> 'V' --Exclude Views <br />--See referenced article for listing of additional object types</font> </div> <p>This would do the job for the most part, the only missing items would be constraints and rules for the most part; which how many times do you really need to search a constraint for a specific string of text? </p> <p>Now, the execution plan shows that this can be a little bit of a resource monger. Here's the estimated execution plan I get for this:</p> <p><a href="http://lh6.ggpht.com/jrea8830/SPjOp3GC41I/AAAAAAAAACg/W4AJZLg3AIE/s1600-h/Plan_For_Defintion_Search_by_Joins%5B6%5D.jpg"><img style="border-right-width: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" border="0" alt="Plan_For_Defintion_Search_by_Joins" src="http://lh5.ggpht.com/jrea8830/SPjOq6LVsBI/AAAAAAAAACk/nH2b_2_hZ4A/Plan_For_Defintion_Search_by_Joins_thumb%5B4%5D.jpg?imgmax=800" width="1001" height="419" /></a> </p> <p>This Execution Plan is OK; there's a lot of Clustered Index Scans and Compute Scalars. You might be able to improve upon this with some ingenious Indexing, hints and such...but, again...why bother? You'll have to upkeep it and monitor yet another query plan. So, I'm not too keen on this plan myself and I don't like that this query still doesn't do everything...I want everything or nothing (start my pouting and tantrum).</p> <p>As I said, I want it all. So the problem for me here is that there is that 1 in 1,000,000 chance I'll need to search a constraint and then that means I'll have to either modify my code or discover a new method...why bother doing it again later if we can just make it do what we want now. Plus, and this may be more important for myself; I want something that is easier to read and understand at a later time and uses less resources. Time and resources is the name of the game we play as DBAs!</p> <p>How do we solve this? Can it be done? It sure can! Enter the sacred "OBJECT_DEFINITION" function! This little function helps us out a lot by eliminating the need to manually join tables and the return results is the exact column (definition column) that we are wanting also! Let's take a look at the same search function using this syntax instead of joins:</p> <div class="code"><font color="#0000ff">DECLARE</font><font color="#808080"> </font><font color="#000000">@SearchString</font><font color="#808080"> </font><font color="#0000ff">NVARCHAR</font><font color="#000000">(MAX)</font><font color="#808080"> <br /></font><font color="#0000ff">SET</font><font color="#808080"> </font><font color="#000000">@SearchString</font><font color="#808080"> </font><font color="#0000ff">=</font><font color="#808080"> 'ENTER_SEARCH_TEXT_HERE' <br /></font><font color="#0000ff">SELECT</font><font color="#808080"> </font><font color="#000000">[</font><font color="#0000ff">name</font><font color="#000000">],</font><font color="#808080"> </font><font color="#000000">OBJECT_DEFINITION(</font><font color="#0000ff">OBJECT_ID</font><font color="#000000">([</font><font color="#0000ff">name</font><font color="#000000">]))</font><font color="#808080"> <br /></font><font color="#0000ff">FROM</font><font color="#808080"> </font><font color="#000000">sys.all_objects</font><font color="#808080"> <br /></font><font color="#0000ff">WHERE</font><font color="#808080"> </font><font color="#000000">OBJECT_DEFINITION(</font><font color="#0000ff">object_id</font><font color="#000000">([</font><font color="#0000ff">name</font><font color="#000000">]))</font><font color="#808080"> </font><font color="#0000ff">LIKE</font><font color="#808080"> '%' </font><font color="#000000">+</font><font color="#808080"> </font><font color="#000000">@SearchString</font><font color="#808080"> </font><font color="#000000">+</font><font color="#808080"> '%' <br /></font><font color="#006400">--Optional  to limit search results.... <br />--AND [is_ms_shipped] = 0 --Only search user & post SQL release objects </font></div> <div class="code"><font color="#006400">--Change the above value to 1 to only include </font><font color="#006400">objects provided by Microsoft with the release of SQL Server</font><font color="#006400"> <br />--AND [type] = 'P' --Search only Stored Procedures <br />--AND [type] <> 'V' --Exclude Views <br />--See referenced article for listing of additional object types </font></div> <p>As you can see, this is a lot cleaner to read. We can easily tell what is being queried and how the query results should be returned to us. Not much to look at here; we query the sys.all_objects view to find the proper object and then tell SQL that we want to view the object's definition...simple enough.</p> <p>Now, let's look at the execution plan for this query. We know the query is straight forward, I'm guessing the execution plan will be also! Let's see:</p> <p><a href="http://lh3.ggpht.com/jrea8830/SPjOrjiYzoI/AAAAAAAAACo/eEc53SnjXHI/s1600-h/Plan_For_Defintion_Search_by_Object_Definition%5B3%5D.jpg"><img style="border-right-width: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" border="0" alt="Plan_For_Defintion_Search_by_Object_Definition" src="http://lh3.ggpht.com/jrea8830/SPjOsTiV7uI/AAAAAAAAACs/_oh54jaivUI/Plan_For_Defintion_Search_by_Object_Definition_thumb%5B1%5D.jpg?imgmax=800" width="1017" height="249" /></a> </p> <p>Well, it definitely is a smaller execution plan. Almost 1/2 the plan of the original query! Not bad at all. I'd venture a guess that this will be much less resource intense on our machine, and that means less time! I like less time!</p> <p>Now, we can see that using SQL Server's "Object_Definition" function improves the ability to search the definitions. I'm sure you can see with the above that when possible use a SQL built-in function, these are here to help us. As they say...'why reinvent the wheel?'; I'd say for sure that this object_definition function is already optimized to run in SQL and that it's a fairly safe bet that we DBAs wouldn't be able to optimize our JOIN statement to this level...and if you happen to be able to even come close to the same optimization, I'd be willing to wager that you spent more time than it was worth to get to that point. With that said, it brings me back to my original statement...why bother?</p> <p>Keep looking for those built-in functions, you'll find there are many of them inside SQL Server that will not only make your DBA life easier and quicker, but also more fun!</p> <p>Until next time, Happy Coding!!!</p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com1tag:blogger.com,1999:blog-367836511975673915.post-78822399524534264412008-08-22T10:51:00.001-07:002008-08-22T10:54:26.371-07:00VS/SQL 2008 Developers need more space!<p>I’m not just talking about needing a larger office (although, that couldn’t hurt). I’m talking about hard drive space!</p> <p>With the release of SQL Server 2008; I’ve officially moved over my trusty development machine to the latest and greatest MS technology, Visual Studio 2008 Professional Edition and SQL Server 2008 Developer Edition. </p> <p>Before you do the same, make sure you have plenty of Hard Drive space<a href="#_ftn1_2007" name="_ftnref1_2007"><font size="1">[1]</font></a>. If you already have Visual Studio 2008 installed and then you try to install SQL 2008 you’ll get a friendly little message that states you MUST have VS 2008 Service Pack 1 installed. I’m not quite sure why you MUST have VS 2008 SP 1 installed to run SQL Server 2008 and VS 2008 on the same machine; but, if I do find a reason (other than the guesses of to enable some sort of feature or the ability for the two applications to see each other) I’ll post an update. So, now you know the approximate hard drive space it will take to install these applications.</p> <p>Here is a simple break down of the hard drive space requirement for each product I installed:</p> <table border="3" cellspacing="0" cellpadding="2" width="983"><tbody> <tr> <td valign="top" width="281"><u><strong>Product Name</strong></u></td> <td valign="top" width="226"><strong><u>Hard Drive Space Required</u></strong></td> <td valign="top" width="467"><strong><u>URL with Hard Drive Requirement Specifications</u></strong></td> </tr> <tr> <td valign="top" width="282">Visual Studio 2008 (Professional Edition)</td> <td valign="top" width="226"> 2.2 GB</td> <td valign="top" width="467"><a title="http://msdn.microsoft.com/en-us/vs2008/products/bb894726.aspx" href="http://msdn.microsoft.com/en-us/vs2008/products/bb894726.aspx">http://msdn.microsoft.com/en-us/vs2008/products/bb894726.aspx</a></td> </tr> <tr> <td valign="top" width="283">Visual Studio 2008 Service Pack 1</td> <td valign="top" width="226"> 3.8 GB (plus 600 MB on installed Hard Drive for VS 2008)</td> <td valign="top" width="467"><a title="http://download.microsoft.com/download/A/2/8/A2807F78-C861-4B66-9B31-9205C3F22252/VS2008SP1Readme.htm" href="http://download.microsoft.com/download/A/2/8/A2807F78-C861-4B66-9B31-9205C3F22252/VS2008SP1Readme.htm">http://download.microsoft.com/download/A/2/8/A2807F78-C861-4B66-9B31-9205C3F22252/VS2008SP1Readme.htm</a></td> </tr> <tr> <td valign="top" width="282">SQL Server 2008 (Developer Edition)</td> <td valign="top" width="226"> 2.0 GB (required even if installing components to other hard drives)</td> <td valign="top" width="467"><a title="http://msdn.microsoft.com/en-us/library/ms143506.aspx" href="http://msdn.microsoft.com/en-us/library/ms143506.aspx">http://msdn.microsoft.com/en-us/library/ms143506.aspx</a></td> </tr> </tbody></table> <p>As you can see, VS 2008 SP 1 is no small update. Also note that these hard drive space requirements are based on installing all features/options of each application and the final installation space used will typically be smaller than the requirements specifications. Requirements typically include space required for temporary files during installation of the product.</p> <p>This comes to a total of around 8 GB of space during installation, and around 6.5 GB upon completion of installation. This, of course, does not include any project files, database files or add-ons.</p> <p>What if hard drive space is a premium and you just want to test out these new applications? How do you use less space? The simplest way is to use the Express editions of each product and only install the languages/features you absolutely need or want to use/learn. A language such as C++ being installed for a developer who only knows and uses VB would seem pointless, unless that developer is intending to learn and develop with C++. </p> <p>Is there a way to bypass the requirement to install VS 2008 SP 1? Well, I haven’t tried it myself; but, conceptually, if you install SQL 2008 first, then install VS 2008 you won’t get any messages or requirements during either product installation to have SP 1 installed. Now, that doesn’t mean that VS and SQL will play nice with each other. It also doesn’t mean that you won’t get some sort of message or error at a later time stating that you need to install SP 1. As mentioned earlier, I don’t know why SQL 2008 installation requires VS 2008 SP 1 to be installed with a machine that has VS 2008; I’d imagine there is a good reason though. <em><u>Try at your own risk</u></em>.</p> <p>Until next time, Happy Coding!</p> <hr align="left" size="1" width="33%" /> <p><a href="#_ftnref1_2007" name="_ftn1_2007"><font size="1">[1]</font></a> <em><font size="1">The installation hard drive size requirement is based on my personal experience and may vary per installation.</font></em></p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-18631615579876777952008-08-12T10:36:00.000-07:002008-08-12T10:43:03.511-07:00SQL Server 2008 Officially RTM!<p>On August 11, 2008 Microsoft announced through their MSDN Flash newsletter that SQL Server 2008 has been RTM (Released To Manufacturing). This has been a long awaited release for many of us in the SQL community! Congratulations to Buck Woody and the many, many others on the SQL development team on this release!</p> <p>While August 11, 2008 is probably the official RTM date, those of you that have an MSDN subscription may have been enjoying the RTM a week earlier (August 6th, 2008 through the MSDN Subscriptions website) as announced by Buck Woody's blog (Carpe Datum). You can view the post at: <a title="http://blogs.msdn.com/buckwoody/archive/2008/08/06/news-flass-sql-server-2008-is-here.aspx" href="http://blogs.msdn.com/buckwoody/archive/2008/08/06/news-flass-sql-server-2008-is-here.aspx">http://blogs.msdn.com/buckwoody/archive/2008/08/06/news-flass-sql-server-2008-is-here.aspx</a>.</p> <p>Undoubtedly, you can find many blogs and posts on the new features of SQL Server 2008. This looks to have a great improvement on SQL Server 2005; in just about every area. There is improvement on security, developing, and administering; to name a few. There seems to be a little bit of something for everyone to get excited about, ranging from Business Intelligence for the enterprise users to Intellisense in TSQL syntax for the programming DBA to Resource Governing for the IT Administrators. </p> <p>I look forward to using SQL Server 2008, and to enjoying a little more easily administrated database with tons of new features, flexibility, and control!</p> <p>Until next time...Happy Coding!</p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-15872996363437744642008-07-02T10:19:00.000-07:002008-07-02T10:20:17.296-07:00Publication: Introduction to the Transaction Log...<p>Today SQLServerCentral.com (SSC) had published my article that introduces the Transaction Log. The primary purpose of this article was to help the newer DBAs understand how the transaction log works, and to clarify some commonly misunderstood parts of maintaining the transaction log. </p> <p>This article covers a very broad and general introduction; including how the log is filled by SQL Server, easy to follow analogies to help grasp how transaction logs work with SQL Server, and some Good Practices to use when Transaction Logs are in use by SQL Server.</p> <p>You can read the entire article at: <a title="http://www.sqlservercentral.com/articles/Design+and+Theory/63350/" href="http://www.sqlservercentral.com/articles/Design+and+Theory/63350/">http://www.sqlservercentral.com/articles/Design+and+Theory/63350/</a></p> <p>Please feel free to join in the discussion for this article; there are some great posts that help to further clarify the topic and also provides additional information that was not included with this article. You can join the discussion at: <a href="http://www.sqlservercentral.com/Forums/FindPost527448.aspx">http://www.sqlservercentral.com/Forums/FindPost527448.aspx</a>.</p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-30843547068973124812008-07-01T10:39:00.000-07:002008-07-01T10:40:41.509-07:00Concept: Finding the Right Order...<p>The other day I was helping out a friend of mine with a space issue on their server, and one of the topics I discussed was the importance of find the best order to perform the operations in to utilize the least amount of extra hard drive space. While discussing this it came to mind that often times while plugging away with SQL we can get so intent on getting the job done (often times in the first way that works, while this is not necessarily the best way), that we might take for granted all of that hard drive space and server power available to us!</p> <p>With just a little bit of forethought, planning, and understanding what we are trying to accomplish, we can actually use less disk space...and we could also potentially improve the performance we get out of SQL Server! I don't know about you, but getting better performance always ranks high on my list of desired accomplishments. I think this can be best demonstrated with an example....</p> <p>For simplicity reasons I will use a database with a single table that each record being inserted will equal 1 MB of hard drive space. Thus, 1,000,000 records in our "simple database" will result in a database size of 1,000,000 MB. Now, let's say that on average this database grows by about 100,000 records per year. This would mean that with 1,000,000 records this database has been used for about 10 years. Now, assume there is a decent DR in place and the transaction log is kept at a fairly decent size of maybe 1,000 MB to accommodate for a months worth of data before the FULL backup operation. Simple enough, right? </p> <p>So, 10 years has passed and the boss walks in and says they just merged with another company that has an identical database setup and stats with only different data and the two databases will be merged (since this is hypothetical, we will pretend that a merge of the data can be done without any issues and it would simply double our database size). Also, the boss says that they are archiving 5 years worth of data from both databases to match the business rules for hard copy storage on site and archiving. And lastly, the boss says "...because you don't look busy..." that they only want to have enough space in the database to allow for 2 years of data to be added and that they need the extra hard drive space available for other needs.</p> <p>From a first glance you might say not a problem. We'll just merge the two databases, take out 5 years worth of data, perform a FULL backup, shrink the transaction log (leaving enough free space for a month worth of data), and then shrink the database down (leaving enough room for 2 years worth of data to be added later). I'm going to ignore the transaction log size in this analysis because there will typically be one out come of the size regardless of the steps taken; and this will differ based on the database recovery mode and method taken to perform these steps; and to analyze each possibility is beyond the scope and purpose of this blog. Now, let's look at how this might look on our system <strong><u>after</u></strong> each process...</p> <p>First step in the above scenario would be to merge the two databases (remember that since the databases are originally from different companies, they are physically stored on separate hard drives). </p> <p>HD Space Used: 2,000,000 MB <br />SQL Record Count: 2,000,000 records <br />SQL Space Used: 2,000,000 MB</p> <p>Second step would be to take out 5 years worth of data.</p> <p>HD Space Used: 2,000,000 MB <br />SQL Record Count: 1,000,000 records <br />SQL Space Used: 1,000,000 MB</p> <p>Third step would be to perform a FULL backup (this is based on a FULL recovery model).</p> <p>HD Space Used: 2,000,000 MB <br />SQL Record Count: 1,000,000 records <br />SQL Space Used: 1,000,000 MB</p> <p>Fourth and Fifth steps to shrink the log and database.</p> <p>HD Space Used: 1,200,000 MB <br />SQL Record Count: 1,000,000 records <br />SQL Space Used: 1,200,000 MB</p> <p> </p> <p>From the above we can see that the maximum HD space being used would be 2,000,000 MB. Which depending on our available HD space this wouldn't be too much of a problem. The real issue would be the amount of resources needed; since this is hypothetical we can only imagine from a step-by-step that the resources being used would be fairly moderate. There are too many variables to be accurate when it comes to actual performance measuring, so I won't even attempt to measure it.</p> <p>Now, what if we put a little thought towards these requirements from the "boss", and especially what if the HD space available to use was 1,800,000 MB. Now, we have a problem that can easily be resolved with just finding the right order. In this case, just making a simple change of placing Step # 1 at the very end and performing an extra (final) FULL backup and a (final) transaction log shrink will allow us to remain under 1,800,000 MB and also can save our server a lot of processing time, because it will have to process less records. We can also utilize the secondary server (the server holding the data from the company being merged with) processing power to cut total time being required to archive and thus decreasing the time required to merge because of the lower number of records being merged. Here is how the steps would look....</p> <p>Step # 1: Remove data from each database at it's original location</p> <p>HD Space Used (per Server): 1,000,000 MB <br />SQL Record Count (per Server): 500,000 records <br />SQL Space Used (per Server): 1,000,000 MB</p> <p>Step # 2: Perform FULL backups for each database</p> HD Space Used (per Server): 1,000,000 MB <br />SQL Record Count (per Server): 500,000 records <br />SQL Space Used (per Server): 1,000,000 MB <p>Step # 3: Shrink the transaction log and databases</p> <p>HD Space Used (per Server): 500,000 MB <br />SQL Record Count (per Server): 500,000 records <br />SQL Space Used (per Server): 500,000 MB</p> <p>Step # 4: Merge the two databases</p> <p>HD Space Used: 1,000,000 MB <br />SQL Record Count: 1,000,000 records <br />SQL Space Used: 1,000,000 MB</p> <p>Step # 5: Perform FULL backups for database and shrink transaction log</p> <p>HD Space Used: 1,200,000 MB <br />SQL Record Count: 1,000,000 records <br />SQL Space Used: 1,200,000 MB</p> <p> </p> <h3>Conclusion:</h3> <p>Now, you can easily see in the above scenario that we only use a maximum of 1,200,000 MB of space and that is because we are reserving that free space. Logically, by performing the data archiving before merging the two databases we save our server processing power because it will process ONLY that of which will remain in the database. Sometimes the order of operations will already be pre-determined from outside factors, sometimes it may be required to perform certain tasks in a particular sequence. </p> <p>I hope this illustrates that it's not always good enough to just find a way to make things work; and that with little planning in the beginning you can save yourself time, lower the requirements to perform the tasks at hand, and also improve the performance of your SQL Server! </p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-35019807475031958392008-06-18T13:01:00.000-07:002008-10-23T12:10:56.310-07:00Listing User-Defined Stored Procedures in SQL Server 2005<p>You can easily obtain a listing of the User-Defined Stored Procedures that are in any of your databases. In fact, Microsoft had made this information very easily accessible. You only need to access a System Table view called "Sys.Procedures". This information may be easily accessed using a small TSQL code, such as the following... </p> <p><font color="#0000ff">USE</font><font color="#808080"> </font><font color="#000000">[your_database_name_here]</font><font color="#0000ff">;</font><font color="#808080"> <br /></font><font color="#0000ff">GO</font><font color="#808080"> <br /></font><font color="#0000ff">SELECT</font><font color="#808080"> </font><font color="#000000">*</font><font color="#808080"> </font><font color="#0000ff">FROM</font><font color="#808080"> </font><font color="#008000">sys.procedures</font><font color="#808080"> <br /></font><font color="#0000ff">ORDER</font><font color="#808080"> </font><font color="#0000ff">BY</font><font color="#808080"> </font><font color="#000000">[</font>name<font color="#000000">]</font><font color="#0000ff">;</font><font color="#808080"> <br /></font><font color="#0000ff">GO</font> </p> <p>Your results will vary based on what Stored Procedures you or anyone else with access to your database have created. The following is a sample of the results I have obtained when running this code on my 'model' database. </p> <p><a href="http://lh6.ggpht.com/jrea8830/SFlqzr7KG6I/AAAAAAAAACY/-4rDFbWoGMM/s1600-h/SP_Listing_User_Defined_Only%5B13%5D.png"><img style="border-right-width: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" border="0" alt="User-Defined Stored Procedures" src="http://lh4.ggpht.com/jrea8830/SFlq0U1BCdI/AAAAAAAAACc/CgkP7UkwtAg/SP_Listing_User_Defined_Only_thumb%5B9%5D.png?imgmax=800" width="1240" height="109" /></a> </p> <p>The most important thing to keep in mind with this TSQL code is that each database may contain different User-Defined SPs.</p> <p>Do you want to get a listing of <strong><u><em>EVERY</em></u></strong> single Stored Procedure in your database(s)? If so, then be sure to check out SQLServerCentral.com for one of my upcoming articles! I'll post the direct link to the article as soon as it becomes available! </p> <p><strong>You'll want to check this article out when it is published...you'll NEVER have to search the Internet again to find out what SPs are in SQL Server 2005!! UPDATE: You can read this article on SQLServerCentral.com (<a title="http://www.sqlservercentral.com/articles/T-SQL/63471/" href="http://www.sqlservercentral.com/articles/T-SQL/63471/">http://www.sqlservercentral.com/articles/T-SQL/63471/</a>). Please leave feedback if you have a few moments.</strong></p> <p>TIP:</p> <p>Do you want all of your databases you create (from here on...not previously created databases) to use a specified stored procedure or set of stored procedures? If so, then create the desired Stored Procedure(s) in your 'model' database. Now any new databases created will get the SP(s) you created in the 'model' database because your new databases are based on the 'model' database!</p> <p> </p> <p>-----------------------</p> <h6>Legal Disclaimer/Warning</h6> <h6>Please remember that when using an SP you are not sure the outcome or effect it has should be done on test systems; never use anything that has not been thoroughly tested in a production environment. I am not encouraging you to use any type of Stored Procedures (documented or undocumented); I am only informing you of the method that can be used to obtain a listing of all procedures that are found within SQL Server 2005. Microsoft strongly states that undocumented Stored Procedures, Extended Stored Procedures, functions, views, tables, columns, properties, or metadata are NOT to be used and do not have any associated support; and Microsoft Customer Support Services will not support any databases or applications that leverage or use any undocumented entry points. Please refer to: <a title="http://msdn.microsoft.com/en-us/library/ms166021.aspx" href="http://msdn.microsoft.com/en-us/library/ms166021.aspx">http://msdn.microsoft.com/en-us/library/ms166021.aspx</a> for Microsoft's legal disclaimer and further information in Microsoft's support for use of stored procedures.</h6> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-6616945147494200402008-05-22T11:39:00.001-07:002008-05-22T11:39:17.063-07:00Optimizing Indexes<h2>Summary:</h2> <p>Optimizing Indexes is quite a complicated subject. There are many techniques; and what is the most difficult is that there are so many different scenarios as to when and how to optimize your indexes. Instead of trying to describe a method of how to optimize, I am going to discuss how to identify when optimizing indexes should be considered and where to go to find the different options. </p> <p>The first step is NOT to just rebuild all indexes, or to defrag them all. There are some people that believe that rebuilding all indexes is the way to solve this; don't listen to them!! PLEASE DON"T!!! I'm rarely the person to give advice on not listening to other people; most people can help you learn about what's good or bad...but if you go the route of rebuilding and defraging all your indexes, you can in fact be causing additional problems that you didn't have to cause. In particular using system resources that don't need to be used, and causing unneeded fragmentation on the OS level! Any decent DBA is always concerned with using system resources, remember that system resources is always a precious commodity and should never be wasted.</p> <p>The first step is to analyze the database. The next step is to identify the queries that can/will benefit from optimizing the index(es). The third step is to optimize the index. The final step is to periodically review these methods to identify when optimizing indexes will be required; this will be an ongoing project and will ultimately require re-iterrating through your queries to identify when an index has become warranted for optimization. There is no cut and dry method to warn you, prevent indexes from becoming out of tune, or to auto-optimize the queries and indexes.</p> <h2>Analyzing the Database:</h2> <p>So, how do you analyze a database to determine if an index needs to be rebuilt or should run a defrag on the index? Well, first you need to understand how indexes are built and what is causing them to become fragmented. Review my blog entry "<a href="http://sqln.blogspot.com/2008/05/introduction-to-indexes.html">Introduction To Indexes</a>" to learn about how indexes are built. Also, take the time to review this white paper called "<a href="http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx">Microsoft SQL Server 2000 Index Defragmentation Best Practices</a>"; even though it is written for SQL Server 2000, it will still apply the same to SQL Server 2005. It is a long read, but will help out well beyond the scope of this blog entry.</p> <p>Here, in a nutshell, is how to determine when your index is fragmented. Use SQL Profiler to identify poor performing queries; in particular use the template "<b>SQLProfilerTSQL_Duration</b>", this will already contain the traces needed to identify the offending queries. Once the queries are identified you can then start looking into which indexes there queries are accessing; especially queries that are retrieving ranges instead of singletons. These queries are the highest risk of having fragmented indexes, remember though that this is just a method to determine the potential problem areas. Your own judgement will be best to make the final determination.</p> <h2>When to Consider Optimizing Indexes:</h2> <p>The first key in detecting that you should optimize your index is when you are observing performance degradation and have no absolute culprit to cause this. Now, keep in mind that just because you are seeing performance degradation doesn't immediately indicate that there is index fragmentation. This can only be determined by analyzing your database properly. Next, identify which queries are utilizing the most I/O; these are the next candidates. Then consider queries that have workloads that are suspected to benefit from index defrags; this can be quite difficult and should be carefully determined. </p> <p>All of these are covered in the MSDN white paper called "Microsoft SQL Server 2000 Index Defragmentation Best Practices"; mentioned earlier. This paper even goes into very good detail on how to make these determinations, which will probably be enough to get your through while you gain experience. </p> <h2>Getting Help:</h2> <p>Sometimes it's nice to have someone more experienced help out, if that is how you feel then go with that feeling and seek out that someone. </p> <p>If you don't have physical access to an experienced DBA then seek advice from trusted websites, forums, and/or discussion groups. Remember when seeking advice they can only provide an answer with quality that matches the information you provided. Such as stating you have a database that needs indexes rebuilt or defragged, will most likely get you answers stating to use DBCC commands or some other commonly used index rebuilding command. The purpose of seeking advice is to provide detailed information to get an answer that is specific to your scenario; so be sure to provide as much information as possible without breaking any company policies.</p> <h2>Conclusion:</h2> <p>As you can see, Indexes can be fairly simple to optimize. It's determining when to optimize and what to optimize that becomes difficult.</p> <p>There are methods to fine-tune the auto-index handling of your indexes, this is covered in the white paper I mention in the next section. You'll always find different opinions and experiences, embrace all you can and mix and match what works best for you and your situation. There is no one-size fits all for optimizing indexes, just as there isn't one-size fits all for database solutions. It's all about customizing to your needs, and utilizing your available resources to make your work easier and more enjoyable.</p> <p>I can't stress enough that reading the "Microsoft SQL Server 2000 Index Defragmentation Best Practices" white paper will help out tremendously. This paper covers the topic so well, that originally I had planned on providing tips on how to identify the queries that required indexes to be rebuilt, and how to find additional help on this topic. During my research I came across this white paper and it covered absolutely everything and more than I had planned to cover!</p> <p>Until next time, Happy Coding!!</p> <h2>Additional Resources:</h2> <p>Microsoft SQL Server 2000 Index Defragmentation Best Practices (<a title="http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx" href="http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx">http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx</a>) <br />SQL Server Best Practices (<a title="http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx" href="http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx">http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx</a>) <br />How To: Optimize SQL Indexes (<a title="http://msdn.microsoft.com/en-us/library/ms979195.aspx" href="http://msdn.microsoft.com/en-us/library/ms979195.aspx">http://msdn.microsoft.com/en-us/library/ms979195.aspx</a>) <br />Database Journal: Reducing SQL Server Index Fragmentation (<a title="http://www.databasejournal.com/features/mssql/article.php/2238211" href="http://www.databasejournal.com/features/mssql/article.php/2238211">http://www.databasejournal.com/features/mssql/article.php/2238211</a>) <br />SQLServer Performance: SQL Server Index Fragmentation and Its Resolution (<a title="http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx" href="http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx">http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx</a>) </p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-57849961482125303022008-05-06T10:01:00.001-07:002008-05-06T10:01:45.718-07:00CREATE INDEX (Transact-SQL)<h3>Summary:</h3> <p>This covers the syntax and some examples on how to create an Index for a table. I’ll finish this blog entry with an alternative method for creating an index using SQL Server Management Studio (SSMS).</p> <h3>Syntax:</h3> <p>The following is the main syntax for <a href="http://msdn.microsoft.com/en-us/library/ms188783.aspx">CREATE INDEX</a> from Books Online (BOL). You can view the entire syntax by visiting the referenced link. <br />Reference: <a href="http://msdn.microsoft.com/en-us/library/ms188783.aspx">http://msdn.microsoft.com/en-us/library/ms188783.aspx</a> <br />NOTE: If you do not understand how to read this syntax please review my blog entry “<a href="http://sqln.blogspot.com/2008/02/understanding-msdn-syntax-conventions.html">Understanding MSDN Syntax Conventions</a>”</p> <table cellspacing="0" cellpadding="2" width="400" border="3"><tbody> <tr> <td valign="top" width="400"> <p>CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name </p> <p>    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) </p> <p>    [ INCLUDE ( column_name [ ,...n ] ) ] </p> <p>    [ WITH ( <relational_index_option> [ ,...n ] ) ] </p> <p>    [ ON { partition_scheme_name ( column_name ) </p> <p>         | filegroup_name </p> <p>         | default </p> <p>         } </p> <p>    ] </p> <p>[ ; ]</p> </td> </tr> </tbody></table> <p>UNIQUE – The index key used may not be duplicated in another row.</p> <p>CLUSTERED | NONCLUSTERED – When using CLUSTERED, the logical order of the key values determines the physical order of the rows in the table. You may only have a single clustered index per table. CLUSTERED indexes should be created before any NONCLUSTERED indexes. In cases where the CLUSTERED index is created after the NONCLUSTERED index, the NONCLUSTERED indexes will be rebuilt. <br />NOTE: CLUSTERED is the default, if you OMIT the [CLUSTERED | NONCLUSTERED] argument then SQL will first look for a CLUSTERED index, if not found it will create the index as CLUSTERED; if it CLUSTERED already exists then it will create the index as NONCLUSTERED.</p> <p>index_name – Gives your index a name. A common practice is to prefix the name with “idx_” or “ix”. An example of an index for Last Names of a customer table might be called “idx_Customers_Last_Names” or “ix_Customers_Last_Names”.</p> <p><object> - Name of the table the index is being created for. This can be up to a four part name, such as Servername.DatabaseName.Schema.TableName, as with other commands you do not always have to fully qualify (type all four parts) the <object>. You only need to be able to determine the table, without causing confusion. Example if you have two tables called Customers, then you’d at minimum need to qualify the table using the SCHEMA_NAME; such as Colorado.Cusotmers and California.Customers.</p> <p>column_name [ASC | DESC] – Specify the column, or columns, to make the index for. There is no minimum or limit to the number of columns you can specify. Typically it is suggested, for CLUSTERED indexes, to use only the columns that can logically be organized; such as the First_Name and Last_Name columns. In NONCLUSTERED indexes you usually want to add ‘helper’ columns; such as the Street_Address and Phone_Number. See my blog entry “<a href="http://sqln.blogspot.com/2008/05/introduction-to-indexes.html">Introduction to Indexes</a>” for additional details in choosing the appropriate columns for indexing.</p> <p><relational_index_option> - You can specify additional options with this argument, this goes beyond the scope of this blog entry. I might cover this in a later entry. For now, if you want specific details as to what options you can use with this argument and how to use the argument then review the BOL syntax command at the referenced location above.</p> <p>partition_scheme_name (column_name) | filegroup_name | default - You can specify the partition scheme and columns to include in your index. This goes beyond the scope of this blog entry. I might cover this in a later entry. For now, if you want specific details as to what options you can use with this argument and how to use the argument then review the BOL syntax command at the referenced location above.</p> <h3>Simple Terminology:</h3> <p>As you can see with just a little bit of the Syntax I’ve posted, this can be quite a complicated T-SQL command. Yet, it will be among the most commonly used throughout the creation and life-span of your tables. You’ll constantly find yourself tweaking your indexes as your needs change and the data defined within the database changes. You can think of indexes as a child to your table, as with all children they will grow in complication and evolve as their experiences grow. Indexes among a table can have the same phases of ‘life’ occur also.</p> <p>As with the Customer’s table example you might originally only be storing the customer’s first name, last name, street address, city, state, zip. So you may have a CLUSTERED index on the last name, then first name columns. Maybe even create a NONCLUSTERED index for the street address.</p> <p>Now, let’s say a couple of years later you find that you now want to store the customer’s phone number, fax number, maybe mailing lists columns with an opt-in designator for your mailing lists. Then you decide it would be nice to just look up customer’s by their phone numbers, or find the customer’s who’ve ‘opted-in’ to certain mailing lists. You might then create additional NONCLUSTERED indexes to make these searches more efficient. Especially the Mailing Lists opt-in columns (assuming you have 100s of mailing lists…following Normalization rules would mean this should be in a separate table; but for this example it is in the Customer’s table).</p> <p>Now, let’s say a year later you decide to Normalize your Customer’s table and separate the Mailing Lists columns into a “Mailing_Lists” table. Obviously the indexes for the Mailing Lists won’t be needed in the Customer’s Table, thus you’d drop those indexes; and most likely you would’ve created the appropriate indexes in the “Mailing_Lists” table when you created the table.</p> <p>As you can see, the indexes can have different reasons to be tweaked. I find most commonly I will look into tweaking the indexes when I have large queries running that are taking up resources. I can usually find an index that could be added or modified that can help to improve the efficiency of the search results being returned. There are many different methods and ways to determine when to use an index and how to optimize your indexes; I’d suggest trial and error (with test systems only) as a first option. I’d also suggest reading up on optimizing queries and/or SQL Server performance (in that order). Queries are what drive your data, what gives you your results.</p> <p>It’s usually a good idea to be in the habit of obtaining performance information, especially in large databases and periodically review the usage of your indexes and adjust them as appropriate. There is no perfect formula, but there are many good methods and discussions on how to achieve the best performance. Always be willing to read and try to understand your options; and when possible spend time testing to see how things are affected by your changes. What might look good today, could end up causing problems you won’t see until a few days have passed…this is why I must stress…TEST, TEST, TEST!</p> <h3>Example Syntax:</h3> <p>The following will create a UNIQUE CLUSTERED index on the Customer’s table using the Last Name and First Name columns (notice the order of names is Last then First because of logical searches will typically be performed on the last name, and then the results would be sorted by the first name):</p> <table cellspacing="0" cellpadding="2" width="400" border="3"><tbody> <tr> <td valign="top" width="400"> <p>USE myDemoTable; </p> <p>GO </p> <p>CREATE UNIQUE CLUSTERED INDEX idx_Customers_Names </p> <p>ON Customers (Last_Name, First_Name); </p> <p>GO</p> </td> </tr> </tbody></table> <p>The following example will create a NONCLUSTERED index on the Customers table using the Street Address column:</p> <table cellspacing="0" cellpadding="2" width="400" border="3"><tbody> <tr> <td valign="top" width="400"> <p>USE myDemoTable; </p> <p>GO </p> <p>CREATE NONCLUSTERED INDEX ix_Customers_Addresses </p> <p>ON Customers (Street_Address); </p> <p>GO</p> </td> </tr> </tbody></table> <p>The following example creates a UNIQUE NONCLUSTERED index on the Customers table using the customer’s phone number column. This will ensure that none of our customers have a duplicate phone number as an existing customer already has:</p> <table cellspacing="0" cellpadding="2" width="400" border="3"><tbody> <tr> <td valign="top" width="400"> <p>USE myDemoTable; </p> <p>GO </p> <p>CREATE UNIQUE NONCLUSTERED INDEX idx_Customers_Phone_Numbers </p> <p>ON Customers (Phone_Number); </p> <p>GO</p> </td> </tr> </tbody></table> <p>If you were to attempt to enter a new customer and use a phone number that already exists with another customer you will get a “Msg 2601, Level 14” error code that states you cannot insert a duplicate key.</p> <p>Also, note that in the second example I used the prefix “ix_” and the other examples I used the prefix “idx_”. First, I used the “idx_” prefix because for my personal uses this means it is a UNIQUE index; thus anytime I see “idx_SOMETHING” I know it is a UNIQUE index and will not allow multiple keys. I use “ix_” to mean that it is NOT unique and is NONCLUSTERED. </p> <p>Remember that CLUSTERED index is the default index type; however, I strongly recommend stating the type of index in every syntax command for two reasons. First, distinction can be easily made when reviewing the syntax at a later time. Second, just because CLUSTERED is the default right now does not guarantee it will be in future SQL Server releases. The less you leave to be interpreted the more compatible you can make your code for future releases (and for backwards compatibility in many cases).</p> <h3>Using SSMS to create your indexes:</h3> <p>You can create indexes within SSMS in several places. The more common areas to create indexes are: Database Engine Tuning Advisor, the Table Designer, and in Database Diagrams, as well as in Object Explorer.</p> <p>The easiest method, in my opinion, is to create a new index using Object Explorer. In object explorer you will want to navigate to the table you want to create your index on. Expand the table by clicking on the plus sign to the immediate left of the table icon to show the folders containing the objects for that table (Columns, Keys, Constraints, etc). You will right-click on the folder labeled “indexes” and select “New Index…”, this will bring up a new window called “New Index”. Here you can name your index, add the columns for the index and choose many options to go with the creation of your index.</p> <p>If the “New Index…” is grayed out when you right-click the “indexes” folder then this means you have the table opened in “Design” mode. If this is the case, you can either close the “Table Designer” window and then access the “New Index…” or you can right-click anywhere in the “Table Designer” and select “Indexes/Keys…”. This will bring up a slightly different window, but just as easy to follow to create your indexes.</p> <p>To modify or delete an index you have two simple methods. If in Object Explorer you can select the Index name under the table’s “Index” folder and right-click the index name and select “Delete”. Otherwise, when in “Table Designer” you can right-click anywhere and select “Indexes/Keys…”, in the resulting window you will highlight the index name you wish to delete. You will then click on “Delete”. CAUTION: There is NO confirmation or UNDO in this window; once that button has been clicked you have removed your index. You cannot CANCEL out of the window either. So make sure this is what you want to do before clicking that button!</p> <h3>Conclusion:</h3> <p>Indexes are helpful, simple to create and very powerful in making your queries and database operate at a very efficient level. Anyone can quickly learn to create indexes, modify indexes, and to drop (delete) indexes. Most people will spend a fair amount of time reading about indexes when first learning about them, this is because they are so versatile in usage and can provide such a powerful result when leveraged correctly.</p> <p>I suggest at minimum to try to understand how indexes are determined and how to optimize them. These are key aspects to indexes that can make the most difference. I also suggest for you to schedule in a regular review period for indexes on your most heavily accessed tables and queries. This doesn’t need to be daily, weekly, or even monthly; but, it should be done over some periodic time because your data needs and accessing will evolve as your database evolves.</p> <p>Until next time, happy coding!</p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com1tag:blogger.com,1999:blog-367836511975673915.post-47799299563599831112008-05-02T12:36:00.001-07:002008-05-02T12:36:01.491-07:00Introduction To Indexes<h3>Summary:</h3> <p>This is a basic introduction to what Indexes are and how to determine what should be indexed. I will cover the basic concept behind indexes, how they are intended to be helpful, why you would want to use them, and how to determine what should be included in your index.</p> <p>I’ll also cover the differences between Clustered and Non-Clustered indexes, and provide some tips that will help you to know how to differentiate when to use which kind of index, as well as an analogy to break down the differences between Clustered and Non-Clustered indexes in simple terms.</p> <h3>What indexes are and why to use them:</h3> <p>Indexes are intended to help you to efficiently find information within your table; they are meant help you to lower the amount of CPU resources needed to find this information, and also to minimize the amount of Input/Output (I/O) used to access this information. All of this can result in a much faster result being returned. </p> <p>Indexes in SQL Server can be thought of in a similar fashion as an index in the back of a book or a table of contents in the front of the book. In the book and Index is intended to allow you to specify a word that you are interested in finding and point you to the page(s) that word has been referenced. A Table Of Contents can be used to specify a topic you are interested in, and will point you to the section covering that topic; some table of contents will even point you to sub-sections that refine the context of the topic that can help you to more accurately focus your reading on relevant information.</p> <p>Indexes within SQL Server are designed to perform these same functions, and provide the same helpful information. Indexes are created either through a T-SQL command or through a form of interface application that connects directly to the database and supports T-SQL commands (such as SQL Server Management Studio, or Enterprise Management, etc). NOTE: I will cover the topics of “Creating Indexes” and “Tuning Indexes” in separate blog entries. Once indexes are created SQL Server will automatically use them to ‘help’ in returning result sets; there are no options you must specify to take advantage of the created indexes…this is all built-in to SQL Server. </p> <p>There are some minor differences in SQL Server 2000 and SQL Server 2005; since I primarily use SQL Server 2005 my discussion is based on this version. However, all of this information can be confirmed with the use of Books Online (BOL) for SQL Server 2000. I will try to avoid using specific information that can’t be used in SQL Server 2000; however, I am unable to guarantee that all information applies to previous SQL Server versions. Please use BOL if you are unsure of any of this information will work with your version of SQL Server.</p> <h3>How SQL Server Retrieves Data without Indexes:</h3> <p>Before we can go into how an index works, we should really understand how SQL Server finds data without the indexes; this will illustrate the importance of using Indexes. My examples will be based on a Customer Table that holds basic customer information such as the customer’s first and last name, their street address, city, state and zip code. When I use the term “Customer” I am referring to the Customer Table; this also goes for “First Name”, “Last Name”, etc. These are referencing their respective columns within the Customer Table.</p> <p>What happens when SQL Server attempts to retrieve data from a table that does not contain any indexes is called a “Table Scan”. This is where SQL Server will go through each record (column by column, then row by row) to find matching records. After it has gone through the entire table it will return any matching results it has found. As you can probably image this is no quick task. This would be similar to picking up a book and deciding to find any pages that contain the word “alligator” in it. There might be a page with that word, there might now. In either case, without an index or table of contents you’d have to flip through every page to see if it contains this search word. If the book is under 30 pages, this may not be too bad…but, what if the book is 1500+ pages; that’s a whole different story.</p> <p>This brings us to Indexes to help lower the amount of time SQL Server needs to spend finding matches in results!</p> <h3>Different Types of Indexes (and the general structure of an index):</h3> <p>There are two basic types of indexes: Clustered and Non-Clustered. Before I get into the specifics of each type and the differences between the types I want to cover the basic structure of an index; and the minor structural differences between the two types of indexes.</p> <p>Basically all indexes are formulated with what is called “index key(s)” for a column (or combination of columns). These basically are pointers that tell SQL Server were specific words (or data) is contained within a table. Each table is broken into pages (this is physical storage of the data; each page is 8 KB and typically is constructed based on the order of entry…not a sort order). A page can contain hundreds of records, or just a few records. Because of the size limit in a data page, it all depends on the data types and how many columns are being stored for each record. There are formulas that can pin point the number of records being stored on a data page; this is beyond the scope of this blog entry.</p> <p>Each index key(s) has a definition of the page the indexed word (or data) is stored. SQL Server will use these keys to determine what page to go to; so if the search term shows up in data page # 4 of 27 pages, SQL will skip over the first 3 pages and search page # 4. It will halt its search if there are no other keys specifying that data is stored in other pages; there are often times cases where data will be stored through multiple pages (like page #s 4, 12, 13, 21, 25, 26) or a data record could span multiple pages (such as page #4 & 5). Again, this is beyond the scope of this blog entry. The important thing to know is that data isn’t naturally stored in logical order within the physical file; this is why we need to have indexes to speed up the search. Also, since searches aren’t pre-determined when data is being entered SQL cannot have the data sorted, and in many cases you may have a specific method of sorting the data…but you might not be searching the sorted data (such as in Customer table, you might sort data based on customer’s location like City/State…but want to search for customer’s with Last Name starting with R; this type of a query and sort method can’t be pre-determined…hence the difficulty in storing data logically). Data is basically stored on a First In basis, and the rows just sequentially increase as data is entered, or removed. We need searches to tell us where the data is so that we can easily find this data to match our unknown (at entry time) search criteria and sorting methods.</p> <p>This brings us to how do we tell SQL Server the best method to search if we don’t know it ourselves?! The answer is Clustered and Non-Clustered indexes, some research and knowledge of the data being stored, and a lot of testing (and refined Tuning as the database is used).</p> <h3>Clustered indexes:</h3> <p>Clustered indexes are a logical sorting and storing of index keys. This can allow SQL Server to very efficiently find data. This is all based on the defined columns within the clustered index. So, why don’t we want to just have every column within every table to be included in the clustered index? Because the more indexes included in the clustered index takes away from performance for your INSERTs, UPDATEs, and DELETE statements. </p> <p>Basically the idea is that Indexes, especially Clustered, are wonderful to increase the speed to obtain results for Queries and Reports. However, there is a tradeoff which is that it takes more time write and/or modify data within the table. This is because the clustered index needs to update itself.</p> <p>Imaging a file cabinet holding your customer information, you originally store all data by the customer’s Last name and then the First name. Now, you decide well, for getting information it would be quicker to store all of the data within each file by the date the data was added (and incase of similar dates then by alphabetical titles). So, now you need to find a customer John Smith and a paper that hold his Personal Address information…you look under Smith, find John, then in his folder look up Address section, then Personal and you quickly get the information. </p> <p>Now, what if you have to update his file to include his birthdate, so that might go under Personal Information…not personal address though! So, now you find the last name, first name, then information section, then personal information page and add the file. Wouldn’t it have been quicker to just find his file and add her personal information to the end of his folder? That’s where the balance comes in, so question is partly how well your system performs with just reading queries/reports versus entering/modifying information…but the other part is what will be done more often. Are you going to query the information more often or are you going to update/modify the information more often? If you are querying, then siding on extra columns in your cluster MIGHT be ok; but if you are inserting/modifying data then you want only to use the number of columns it is to filter the result sets into manageable sections (maybe filter only name; last name, then first name…or maybe name and location; such as last name, first name, then city/state). It all just depends on what you are doing, how much data is being stored, and how you anticipate accessing this data will occur (i.e. often, seldom, with reports, lots of updates, lots of inserts, few inserts, etc).</p> <p>Now, an <b><u>important note</u></b> is that you can <b><i><u>ONLY</u></i></b> have 1 clustered index per table! So, this can bring up the point of where you want the data to be accessible, but you don’t want to bog down the system each time you need to add/modify/delete data. So, how do you do this? How do you walk this fine line of optimal performance? Here come non-clustered indexes to the rescue!</p> <h3>Non-Clustered indexes:</h3> <p>You can have up to 249 Non-Clustered indexes; however, just as with Clustered Indexes there is such as thing as too many! Non-clustered indexes have its intended usage, which will get covered in the Tips section. </p> <p>Non-Clustered indexes are indexes that aren’t sorted at the physical data page layer. This means that SQL Server can be pointed to the data page containing the data matching the search page; but the index stops after that point; it then becomes up to SQL to search that page and pick out the data. Remember Clustered Indexes point to the exact location of the data, and is quick because it is sorted. Non-Clustered indexes only point to the page; the Index Keys in the index are sorted also…but not the logical locations. So, there is some performance loss between Clustered and Non-Clustered. Always try to think of non-clustered indexes as an alternative to listing every column in a clustered index, and a method to allow for data that is less accessed or returns more Exact matched results.</p> <p>So what does all of this mean? Well, basically if you were to tell SQL Server to find results of last names that start with ‘RE’ and used a non-clustered index to index the last name column then the index would point you to the page(s) that contain the last name(s) starting with “RE”, but there could be names also that start with “RA” or “RI” or “RH”, it all depends on what is actually stored at the time the query is executed. This then means that SQL would go through these other results until it finds the matching result; this is still faster than an entire table scan (which a table scan would start with Last Name “A” and End with Last Name “Z”).</p> <h3>Tips (putting this all together):</h3> <p>Now, that we have an understanding of what Clustered and Non-Clustered indexes are, and how SQL uses them let’s look at how we can determine what we should index (and what type of index to use). Remember:</p> <ul> <li>· A table can only have a single Clustered Index</li> <li>· Up to 249 non-clustered indexes</li> <li>· Clustered Indexes point to the exact location of matching results</li> <li>· Non-Clustered Indexes only point to the data page containing matching results, so some scanning will occur upon searching for matching results (however, much less scanning than not having any indexes)</li> <li>· Clustered Indexes can retrieve data much more efficiently; BUT have a cost of slower data INSERT, UPDATE, and DELETE</li> <li>· Non-Clustered indexes will use index key(s) if available, if none are available it will then use the data page ID or Row ID to perform its search for matching data </li> </ul> <p>So now let’s put this all together into a simple understandable analogy. I will use a Library as our ‘database’, each section of the library (such as Romance, History, Computers, etc) will be our ‘Tables’, and of course each book will be our ‘record, or row’. </p> <p>A catalog will contain two types of information. A ‘clustered’ catalog that will utilize the Dewey Decimal System; and is stored in the index box in Alphabetical Order by Book Title, followed by Author Name. A ‘non-clustered’ catalog will simply tell us the Section (‘Table’), the Shelf Number where top shelf is # 1 and bottom shelf is # 5 (This simulates our ‘data pages’), and finally the title of the book and the author (this is our First Name and Last Name columns). This is not sorted but does contain a listing of all book titles with Author Names that we can look at on a separate sheet to quickly get the index card # it is stored on. </p> <p>Now with this in mind; imagine searching for a book called “James’ Awesome T-SQL Blog Book” by James R (no such book by the way exists). Now if we did this with a ‘clustered’ catalog. We could quickly look in the Title section and find the book that matches, if there were two books with this title we could then further define the result by looking at the author name.</p> <p>Now imagine this same scenario but with the ‘non-clustered’ index. Let’s say the book title is somewhere around index card # 100 of 15000. Since we can quickly review the index cards listed we can see the card is around #100. So, we open the index drawer…we find our book information and now we are off.</p> <p>Since the ‘clustered’ index card includes the Dewey Decimal System we can simply find the closest matching book numbered and quickly jump up or down as need to the exact book. Now with the ‘non-clustered’ index information we only know the section, title and shelf number. Now we get to the proper shelf, we then go through each book one at time to find the proper book. This of course is much quicker than starting at the beginning of the section and looking through each book, or even worse at the beginning of the first book in the library and sequentially going through the entire collection of books until we found the information (which may not even be there!). </p> <h3>Choosing Type of Index:</h3> <p>When to choose Clustered index and when to choose Non-Clustered indexes…</p> <p>When to choose Clustered Indexes:</p> <ul> <li>· Columns that are frequently accessed</li> <li>· Columns intended to be stored sequentially (such as Last Name, First Name, etc)</li> <li>· Columns that will be queried in ranges (when you use WHERE clause with the BETWEEN, >, or < type of operators)</li> </ul> <p>Once you have determined the columns that should be in your clustered index it then becomes <b><u>ESPECIALLY CRITICAL</u></b> to consider what type of queries will be ran most often and what queries <b><u>MUST</u></b> have optimal performance. If these columns are not within these queries then you may want to reconsider revising your clustered index NOT to include unnecessary columns (consider creating non-clustered index for these columns). If you find that you require columns within these queries that meet the above Index selection tips and they are not already included, and then consider revising your index to include these columns. </p> <p>When to choose Non-Clustered Indexes:</p> <ul> <li>· Columns frequently in the WHERE clause that return EXACT matches</li> <li>· Columns that contain many distinct values (Such as Last Name, or Street Address; but not City, State or Zip Code).</li> <li>· Queries that do NOT return large result sets</li> <li>· <b><u>Especially</u></b> in columns that are needed for critical queries, and aren’t already covered in your Clustered Index; or may be queried in a non-sequential manner that isn’t already covered in your Clustered Index</li> </ul> <p>Keep in mind that once you have created your Indexes, rather it be Clustered or Non-Clustered you can always revise them to meet your current needs. Also, remember that your needs will change and this often times will require revising your Indexes to meet those needs!</p> <h3>Conclusion:</h3> <p>Indexes are here to help you; however they are a complicated concept to master. In most cases it is easiest to figure out what columns qualify for being indexed and then to simply try them out. Don’t be afraid to try out a few different combinations of Clustered and Non-Clustered Indexes; performance is never a one size fits all. Your indexes should not be determined with that type of mind set.</p> <p>I would also suggest setting a personal reminder or adding to your occasional checklist to check your indexes and how the table is accessing your indexes and to see if there is a way to improve the performance of your queries with the indexes for that table. Indexes will always be evolving, your queries will always be evolving, and your tables will always be evolving…don’t let me evolve without your intervention.</p> <p>Remember that Speed = Happy Users…to me a Happy User = a Happy DBA! =)</p> <p>Until next time, happy coding!</p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-23873480149036389232008-04-21T09:40:00.001-07:002008-07-02T10:35:00.810-07:00A brief introduction to Transaction Logs<h6></h6> <h6></h6> <h6><font size="2"><em>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.</em></font></h6> <h3>Summary:</h3> <p>This is a very brief introduction to transaction logs. I am currently working on an article (titled "<a href="http://www.sqlservercentral.com/articles/Design+and+Theory/63350/" target="_blank">Introduction to the Transaction Log</a>") 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).</p> <h3>What a transaction log is:</h3> <p>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.</p> <p>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.</p> <p>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.</p> <h3>Logical Architecture (concept):</h3> <p>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).</p> <h3>Physical Architecture (concept):</h3> <p>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).</p> <p>If the log file does not contain any free space (internally); there are two possible outcomes:</p> <p>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</p> <p>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.</p> <h3>Primary Use of a Transaction Log:</h3> <p>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. </p> <h3>Additional Notes:</h3> <p>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.</p> <p>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).</p> <h3>Conclusion:</h3> <p>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.</p> <h3>Additional Resources:</h3> <p>MSDN: Introduction to Transaction Logs (<a title="http://msdn2.microsoft.com/en-us/library/ms190925.aspx" href="http://msdn2.microsoft.com/en-us/library/ms190925.aspx">http://msdn2.microsoft.com/en-us/library/ms190925.aspx</a>) <br />MSDN: Transaction Log Logical Architecture (<a title="http://msdn2.microsoft.com/en-us/library/ms180892.aspx" href="http://msdn2.microsoft.com/en-us/library/ms180892.aspx">http://msdn2.microsoft.com/en-us/library/ms180892.aspx</a>) <br />MSDN: Transaction Log Physical Architecture (<a title="http://msdn2.microsoft.com/en-us/library/ms179355.aspx" href="http://msdn2.microsoft.com/en-us/library/ms179355.aspx">http://msdn2.microsoft.com/en-us/library/ms179355.aspx</a>)</p> <h3>Update:</h3> <p>Posted on 7/2/08 by James:</p> <p>SQLServerCentral.com has published the article in full. You can read the article at: <a title="http://www.sqlservercentral.com/articles/Design+and+Theory/63350/" href="http://www.sqlservercentral.com/articles/Design+and+Theory/63350/">http://www.sqlservercentral.com/articles/Design+and+Theory/63350/</a>. You can join the discussion of this article at: <a href="http://www.sqlservercentral.com/Forums/FindPost527448.aspx">http://www.sqlservercentral.com/Forums/FindPost527448.aspx</a>. I hope you enjoy the article and find it helpful!</p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com1tag:blogger.com,1999:blog-367836511975673915.post-62783601859761828902008-03-26T15:50:00.001-07:002008-03-26T15:51:46.518-07:00Types of backups<h3>Overview:</h3> <p>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.</p> <p>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).</p> <h3></h3> <h3>What is a backup? When do I make them?</h3> <p>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.</p> <p>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!</p> <p>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.</p> <p>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.</p> <h3>Recovery Models:</h3> <p>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. </p> <p><em>Full</em>: 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.</p> <p><em>Simple</em>: 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.</p> <p><em>Bulk-logged</em>: 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.  </p> <h3>Types of Backups:</h3> <p>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.</p> <p>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:</p> <p><em><strong><u>Full</u></strong></em> <br />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.</p> <p><em><strong><u>Differential</u></strong></em> <br />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. </p> <p>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. </p> <p>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.</p> <p><strong><em><u>Transaction Log</u></em></strong> <br />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).</p> <h3></h3> <h3></h3> <h3></h3> <h3></h3> <h3></h3> <h3>How do I make a backup?</h3> <p>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.</p> <p>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.</p> <h3>Conclusion:</h3> <p>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.</p> <p>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. </p> <p>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.</p> <p>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!</p> <p>Until next time....Happy Coding!</p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-57128632798192309852008-03-10T11:55:00.001-07:002008-04-08T13:26:48.429-07:00BACKUP (Transact-SQL)<style type="text/css"><br /><br /><br /><br /><br /><!--<br />.code<br /> {<br /> word-wrap:break-word;<br /> margin:10px;<br /> padding:10px;<br /> border=3;<br /> border-color:#000000;<br /> border-style:groove;<br /> background-color:#f5f5f5;<br /> font-family:times new roman;<br /> font-size:8.5pt;<br /> }<br />--></style> <p>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.</p> <p>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.</p> <p>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):</p> <table border="2"><tbody> <tr> <td><font color="#0000ff"><font color="#0000ff">DATABASE</font> <font color="#808080"> </font> <font color="#000000">AdventureWorks</font> <font color="#808080"> </font> </font> <br /><font color="#0000ff">TO</font> <font color="#808080"> </font> <font color="#0000ff">DISK</font> <font color="#808080"> </font> <font color="#0000ff">=</font> <font color="#808080">'Z:\SQLServerBackups\AdvWorksData.bak'   </font> <br /><font color="#0000ff">WITH</font> <font color="#808080"> </font> <font color="#000000">FORMAT</font> <font color="#0000ff">;</font> <font color="#808080"></font> <br /><font color="#0000ff">GO</font> </td> </tr> </tbody></table> <p>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.</p> <p>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.</p> <p>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.</p> <p><strong><u>Keep your backups fresh</u></strong>..treat them like Milk..don't let one sit around for too long without checking it, and...</p> <p><strong><em><u>Replace your backups on a regular basis!</u></em></strong></p> <p>Until next time...happy coding! <br /></p> <blockquote> <div class="code"> <p>SQL Server 2005 Books Online (September 2007)</p> <p>BACKUP (Transact-SQL)</p> <p>Updated: <b>1 February 2007 </b></p> <p>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). </p> <p><img alt="Topic link icon" src="http://msdn2.microsoft.com/en-us/library/ms186865.05b1d166-d807-482c-891f-30b3b6b58046(en-US,SQL.90).gif" /> <a href="http://msdn2.microsoft.com/en-us/library/ms177563.aspx">Transact-SQL Syntax Conventions</a></p> <p><img src="http://i.msdn.microsoft.com/Platform/Controls/CollapsibleArea/resources/minus.gif" /> Syntax </p> <p><a name="syntaxToggle"></a></p> <pre>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 ] } ]<br />[;]<br /><br />Backing Up Specific Files or Filegroups<br />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 ] } ]<br />[;]<br /><br />Creating a Partial Backup<br />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 ] } ]<br />[;]</pre><br /><br /> <pre>Backing Up the Transaction Log (full and bulk-logged recovery models)<br />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 ] ]<br />[;]<br /><br />Truncating the Transaction Log (breaks the log chain) <br />BACKUP LOG { database_name | @database_name_var }   WITH { NO_LOG | TRUNCATE_ONLY } <br />[;]<br /><br /><backup_device>::=  {    { logical_device_name | @logical_device_name_var }  | { DISK | TAPE } =      { 'physical_device_name' | @physical_device_name_var }  } <br /><br /><MIRROR TO clause>::=  MIRROR TO <backup_device> [ ,...n ]<br /><br /><file_or_filegroup>::=  {    FILE = { logical_file_name | @logical_file_name_var }  | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }  } <br /><br /><read_only_filegroup>::=<br />FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }<br /><br /><general_WITH_options> [ ,...n ]::= <br />--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 <br /><br />--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 } <br /><br />--Data Transfer Options    BUFFERCOUNT = { buffercount | @buffercount_variable }  | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }<br /><br />--Error Management Options    { NO_CHECKSUM | CHECKSUM }  | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }<br /><br />--Compatibility Options    RESTART <br /><br />--Monitoring Options    STATS [ = percentage ] <br /><br />--Tape Options    { REWIND | NOREWIND }  | { UNLOAD | NOUNLOAD } <br /><br />--Log-specific Options    { NORECOVERY | STANDBY = undo_file_name }  | NO_TRUNCATE</pre><br /> </div><br /></blockquote><br /><br /><p><a href="http://msdn2.microsoft.com/en-us/library/ms186865.aspx">BACKUP (Transact-SQL)</a></p> Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-60352329225278421982008-02-21T11:13:00.001-08:002008-02-21T11:15:50.623-08:00Getting Started – SQL Server Overview<span xmlns=''><h3>Overview:<br /></h3><p>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.<br /></p><h3>SQL Server 2005 – The Platform:<br /></h3><p>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: <a href='http://msdn2.microsoft.com/en-us/library/ms166352.aspx'>SQL Server Overview</a>. 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.<br /></p><h3>SQL Server Components:<br /></h3><p>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:<br /></p><ul><li>To import/export data to/from other databases (including, Access, Oracle, MySQL, etc)<br /></li><li>To generate live-time reports<br /></li><li>To achieve live-time notifications of events or data milestones<br /></li><li>To hold data that is considered unorganized and get useful information from within this data<br /></li></ul><p>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.<br /></p><h3>How data is gathered, retrieved, and manipulated:<br /></h3><p>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.<br /></p><h3>How data is accessed and stored:<br /></h3><p>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.<br /></p><p>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.<br /></p><p>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).<br /></p><p>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.<br /></p><h3>How SQL Server is configured and/or monitored:<br /></h3><p>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.<br /></p><p>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 <a href='http://msdn2.microsoft.com/en-us/sqlserver/bb671105.aspx'>SQL Server 2005 Tools and Utilities</a> website you will find other tools/utilities such as: RML Utilities and SQL Server 2005 Best Practices Analyzer.<br /></p><h3>Conclusion:<br /></h3><p>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!<br /></p><p>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.<br /></p><p>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.<br /></p><p>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!<br /></p><h3>Additional Resources:<br /></h3><p>SQL Server 2005 Tools and Utilities on MSDN: <a href='http://msdn2.microsoft.com/en-us/sqlserver/bb671105.aspx'>http://msdn2.microsoft.com/en-us/sqlserver/bb671105.aspx</a><br/>SQL Server Overview: <a href='http://msdn2.microsoft.com/en-us/library/ms166352.aspx'>http://msdn2.microsoft.com/en-us/library/ms166352.aspx</a><br/><br /> </p><p><br /> </p></span>Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-40458012195948253282008-02-20T10:26:00.001-08:002008-02-20T10:31:04.528-08:00Getting Started – Transact-SQL (T-SQL)<span xmlns=''><h3>Overview:<br /></h3><p>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.<br /></p><h3>What is T-SQL?<br /></h3><p>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.<br /></p><p>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:<br /></p><p> SELECT * FROM table1<br /></p><p>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! <br /></p><h3>How Do I use T-SQL?<br /></h3><p>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.<br /></p><p>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.<br /></p><h3>Where do I find the information I need?<br /></h3><p>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: <a href='http://msdn2.microsoft.com/en-us/library/ms189826.aspx'>Transact-SQL Reference</a> (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!<br /></p><p>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. <br /></p><p>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.<br /></p><h3>What books, training or other materials do you suggest?<br /></h3><p>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. <br /></p><p>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. <br /></p><p>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.<br /></p><h3>Conclusion:<br /></h3><p>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!<br /></p><h3>Additional References<br /></h3><p>Transact-SQL Reference: <a href='http://msdn2.microsoft.com/en-us/library/ms189826.aspx'>http://msdn2.microsoft.com/en-us/library/ms189826.aspx</a><br/></p></span>Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com0tag:blogger.com,1999:blog-367836511975673915.post-67418538081022499412008-02-20T09:21:00.001-08:002008-02-20T09:34:43.094-08:00Understanding MSDN Syntax Conventions<span xmlns=""><h1><span style="font-size:100%;">Overview:</span><br /></h1><p>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: <a href="http://msdn2.microsoft.com/en-us/library/ms177563.aspx">http://msdn2.microsoft.com/en-us/library/ms177563.aspx</a> (or by clicking on the link at the end of this blog under the "Additional Resources" section).<br /></p><h1><span style="font-size:100%;">General Syntax Convention:</span><br /></h1><p>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. </p><div><table style="BORDER-COLLAPSE: collapse" border="0"><colgroup><col style="WIDTH: 319px"><col style="WIDTH: 319px"></colgroup><tbody valign="top"><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: black 0.5pt solid; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid" valign="center"><p style="TEXT-ALIGN: center"><strong>Convention</strong></p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: black 0.5pt solid; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid" valign="center"><p style="TEXT-ALIGN: center"><strong>Description</strong></p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p>UPPERCASE</p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>T-SQL Keyword</p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p><em>Italic</em></p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>User-defined parameter</p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p> | (Vertical Bar)</p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>Separates Multiple Choices; but ONLY ONE may be chosen/used</p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p>[ ] (Brackets)</p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>Optional items. *Do not type/use the brackets*</p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p>{ } (Braces)</p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>Required items. *Do not type/use the braces*</p></td></tr></tbody></table></div><p><br /></p><h1><span style="font-size:100%;">Example:</span><br /></h1><p>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.<br /></p><p>This example is for the T-SQL keyword '<a href="http://msdn2.microsoft.com/en-us/library/ms187928.aspx">CONVERT</a>'; you can view the entire definition and other information by visiting: <a href="http://msdn2.microsoft.com/en-us/library/ms187928.aspx">http://msdn2.microsoft.com/en-us/library/ms187928.aspx</a>.<br /></p><h2><span style="font-family:arial;"><span style="font-size:100%;"><em>Syntax</em> </span><br /></span></h2><p><span style="font-family:times new roman;"><strong>CONVERT</strong> <strong>( </strong><em>data_type</em> [ <strong>(</strong> <em>length</em> <strong>)</strong> ] <strong>,</strong> <em>expression</em> [ <strong>,</strong> <em>style</em> ] <strong>)</strong></span><br /></p><h3><span style="font-size:100%;">Breakdown:</span><br /></h3><p><br /></p><div><table style="BORDER-COLLAPSE: collapse" border="0"><colgroup><col style="WIDTH: 100px"><col style="WIDTH: 538px"></colgroup><tbody valign="top"><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: black 0.5pt solid; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid" valign="center"><p><strong>Syntax</strong></p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: black 0.5pt solid; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid" valign="center"><p style="TEXT-ALIGN: center"><strong>Description</strong></p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p>CONVERT</p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>T-SQL Keyword. This must be typed to use this keyword.</p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p>(</p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>This is the leading parenthesis. This must be typed to use the CONVERT keyword. Its purpose is to place hold the conversion data_type.</p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p><em>data_type</em></p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>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.</p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p>[ <strong>(</strong> <em>length</em> <strong>)</strong> ]</p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>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.</p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p><strong>, </strong><em>expression</em></p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>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.</p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p>[ <strong>,</strong> <em>style</em> ]</p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>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.</p></td></tr><tr><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid"><p><strong>)</strong></p></td><td style="BORDER-RIGHT: black 0.5pt solid; PADDING-RIGHT: 7px; BORDER-TOP: medium none; PADDING-LEFT: 7px; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid"><p>This is the closing parenthesis that belongs to the 'leading' parenthesis you had used after typing the <strong>CONVERT</strong> keyword. This is required to complete the enclosure of the data_type conversion parameters that have been entered.</p></td></tr></tbody></table></div><p><br /></p><p>An example of the usage of the CONVERT keyword is:<br /></p><p>CONVERT(int, myInteger)<br /></p><h1><span style="font-size:100%;">Conclusion:</span><br /></h1><p>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.<br /></p><p>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.<br /></p><p>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).<br /></p><p>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.<br /></p><h1><span style="font-size:100%;">Additional Resources:</span><br /></h1><p><a href="http://msdn2.microsoft.com/en-us/library/ms177563.aspx">Transact-SQL (T-SQL) Syntax Conventions</a></p></span>Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com2tag:blogger.com,1999:blog-367836511975673915.post-46199885328857595282008-02-19T12:14:00.001-08:002008-02-19T12:47:08.325-08:00Welcome to SQL ‘N<span xmlns=''><p>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.<br /></p><p>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!<br /></p><p>Enjoy your visit!</p></span>Jameshttp://www.blogger.com/profile/12968105462220114467noreply@blogger.com