I maintain a playlist of YouTube videos that I like about SQL Server. I’m sharing that playlist on a tab of its own now. Of course, I’ll be adding more videos to the playlist. The video collection oriented more towards performance tuning and general administration.
Hope you find this useful!
Link –
The YOUTUBE tab!
I work primarily on SQL Server these days but I had originally started learning SQL on Oracle. I find the sample tables EMP, DEPT, BONUS, SALGRADE and DUMMY provided with Oracle to be useful for beginners to practice SQL commands. Oracle provides the script by the name of DEMOBLD.SQL to create these tables which can be found in the <OracleHome>sqlplusdemo folder of your Oracle installation or at this URL. Some more useful scripts for Oracle users can be found at this OraFAQ page.
I thought that few folks might like to have those tables on SQL Server so I made some changes to the script so that it can run on SQL Server too. It might prove useful if you don’t have access to an Oracle installation but want want to practice SQL commands with these tables. Of course, MS SQL Server does not support all the SQL syntax as supported by Oracle, but the tables can be handy in some cases.
The SQL Server compatible script is at the TechNet link below. The basic difference from the original script is in the datatypes and the datetime convert function. The script will drop any tables with the same names in the current database so be sure that existing tables do not have any critical data.
Download URL –
http://gallery.technet.microsoft.com/TSQL-Script-to-Create-6bc846bd
I had to work on providing an effort estimate to convert multiple DTS packages into corresponding SSIS packages. An automated conversion tool was not available to us so the SSIS packages were to be built from scratch based on the existing DTS packages.
I took this opportunity to catalog all our DTS packages and created an Excel workbook to aid in the estimation. The formulas in the workbook calculate the effort in person-days for the rewrite of the current DTS logic into a SSIS package.
I approached this requirement as follows –
You can try that estimation workbook by downloading it at the link give below and tweaking the values to suit your needs. While you are there, don’t forget to rate it! 🙂 Please let me know in the comments section of this post if there are any suggestions or questions.
Download from the Technet Gallery link –
DTS to SSIS Conversion Effort Estimation Template.xlsx approx size: 30 KB
http://gallery.technet.microsoft.com/DTS-to-SSIS-Conversion-7153a957
Do you have some of your own SSIS estimation tips? Please share in the comments section here or at this SSIS Development Effort Estimation Methodologies Wiki on TechNet.
Developers use comments within the code primarily for two purposes –
It depends on the team’s culture and their coding standards as to how much they choose to comment for documentation. Ideally, there should be a change history included as a header at the top of the code and additional comments to highlight why a particular logic was used. Another developer (or even the original) can look at the code and understand what is it doing, but sometimes it is not apparent why was that particular implementation chosen over others. So it is a good idea to mention the rationale behind using a particular logic. Other interesting paradigms can be checked on the Wikipedia article on Comments in Programming.
TSQL supports two types of comments –
The implementation of comments has not changed much between various editions of SQL Server but there have been few considerations. The MSDN article for comments syntax in SQL Server 2000 mentions that comments cannot span multiple batches. If the batch delimiter keyword GO is within a multi-line comment then the ending */ will not be parsed correctly and cause syntax errors. The SQL Server 2005 or later versions could have issues when the ‘*/’ string is put in a multi-line comment. Try the code below in SQL Server 2005 or later versions to see an example –
DECLARE @s VARCHAR(10) /* SET @s = '*/' */ SELECT @s
Each comment type has its uses but I personally prefer and recommend using the multi-line syntax, even for short comments that could fit on a single line. My reason for that is that I frequently get requests to review queries and give suggestions on tuning them. Sometimes that query has been captured from DBCC INPUTBUFFER(spid) or the dm_exec_sql_text(sql_handle) DM function. Both of these do not preserve any formatting of the code. As a result I get the entire stored procedures’ code in one single long line!
I usually try to format such code using an excellent online code formatter by the name of SQLInForm. When the formatter comes across a double hyphen, it considers the rest of the line as a comment and leaves it as it is. At that point I have to make a choice either to painfully go through the remaining code and try to guess what is a comment and what is not. At times I send it back to the developer to provided a better fomatted code. Both these approaches increase the turnaround time for the whole tuning process.
If the developer had used the multi-line comment syntax in the first place, then the code formatter could have handled it and life would have been much easier.
A great collection of beginner to advanced level videos for SQL Server. These are of quite short duration but packed with information.
Watch some amazing SQL Server 2008 training videos on Microsoft TechNet.
Video home page: http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx
A little better organised list is: http://www.sqlskills.com/T_MCMVideos.asp
If you’d like to download all of the videos, here is a PowerShell script to get all of them quickly.
http://sqlvariant.com/wordpress/index.php/powershell-script-to-download-sql-mcm-videos/
You are using the query editor in SQL Server Management Studio (SSMS). You have spent an hour writing and tweaking a query. Then probably another 15 minutes to format it to your liking. But you haven’t saved it yet. If the SSMS crashes now before you could save your work, is all of your effort wasted? Maybe not. The in-built auto-recover features make it possible to recover unsaved queries after SSMS crash. I discuss all the manual or tool based recovery options in this article.
The SSMS developer team anticipated this situation so they have built in some auto recovery options in the tool. Usually, the next time you start SSMS after a crash, it tries to recover your unsaved queries. The recovered files show up in a dialog box as shown below. The dialog box has a list of files that SSMS was able to recover. You can select the files that you wish to salvage.
The file names are cryptic with no indication about their content. This kind of naming is ok if you were working with only one query window and know which query is being salvaged. But if you had multiple query windows open at the time of crash, the cryptic file names will be of no help. So you might want to recover all the files in this list and review them later. Next, make a note of the folder locations where the files will be recovered so that you can take a look at them. Now you can click the Recover Selected Files button.
Sometimes SSMS may not present the recovery dialog box. It might seem that all the work is lost. Well, most of the queries (if not all) can still be recovered because the recovery dialog shows where are the backup files located.
On Windows XP, SSMS saves auto recover copies of queries as you work, in the folder –
C:/Documents and Settings/<user name>/My Documents/SQL Server Management Studio/Backup Files/Solution1
On Windows 7, the queries can be recovered from –
C:/Users/<user name>/Documents/SQL Server Management Studio/Backup Files/Solution1
The names of the auto saved files are cryptic. So you will need to open all the files to find the one you are interested in.
If auto recover does not work help for any reason, then here are a few more options.
You can try recovering TSQL statement text using some of the DMVs and DMFs. The sys.dm_exec_requests along with sys.dm_exec_sql_text can show the statement text. But this is helpful only if you executed the query and the query is still in SQL Server’s memory. If the query text was just sitting in the editor and you never executed it before the SSMS crash then this method cannot help.
USE [your_database_name] GO SELECT s.last_execution_time AS [ExecutionTime], t.text AS [Statement] FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t ORDER BY s.last_execution_time DESC
Some third party tools like SSMS Boost (free), Red Gate SQL Prompt (paid after trial), SSMS Tools Pack (paid after trial) etc. can help in a situation like this, but only if you had them installed and running at the time of the crash. They cannot help otherwise.
While talking about tools, you can even up vote this Connect item to add an undo closed tab option in SSMS.