Last week I shared the ODBC connection method for connecting SQL Server data to Excel. Now that you’ve mastered the intricacies of that technique, I want to share a simpler method.
You can use the Data Connection Wizard to create a dynamic connection between a SQL Server database and your Excel workbook.
This type of connection is read-only — you cannot update any data in the database from your workbook.
Here is a link to the article:
I will be attending PASS SQL Saturday on March 11 – I HOPE TO SEE YOU THERE ! !
It will be a mind-blowing event that should not be missed . . .
This is not new but can be very useful. There are many ways to work with data. The tool you use may be able to do everything you want. Maybe you thought that it would be cool to be able to use the functionality in another software to make your life easier. This article might open your eyes to new possibilities.
Check it out…
We think of SSIS packages as something you build in Visual Studio, deploy to Integration Services and run from a SQL Server Agent job. Did you know that you can execute packages from within a T-SQL procedure?
Check out this demonstration on how to accomplish this.
Comments can be extremely valuable for anyone who has to review, maintain, or troubleshoot your code. This includes future you. I highly recommend against worrying about the performance impact of a reasonable amount of comments, and instead focus on prioritizing the usefulness of context that the comments provide. As someone on Twitter said, there is a limit. If your comments amount to the abridged version of War and Peace, you might consider – at the risk of decoupling the code from its documentation – putting that documentation elsewhere, and reference the link in the procedure body comments.
Good T-SQL code formatting really does matter. There are things that will help you and others read the code more quickly. It also helps to avoid mistakes that can take hours to find and fix.
Microsoft is requiring the semi-colon in more and more statements. You’re going to hit issues if you don’t use it as a statement-terminator in all your statements. Yes, older code can still work, but as you add newer functions, you’re going to hit the issue. Eventually they’re going to require the terminator for all statements, just start writing it that way now and avoid the issue.
SQL Prompt includes tools to help with this.
This article makes some very good points. Please read this. It will be well worth your time.
Everyone in our department has SQL Prompt installed. You are able to write scripts faster using it. When you start to type it looks at the SQL Schema system views and supplies a list for you to choose from. It also uses the Snippet Manager to select shortcuts for commonly used statements. But did you know that you can add your own shortcuts? Just Select the Snippet Manager from the SQL Prompt menu item and add away. It takes a little effort to remember to use the snippet instead of just typing everything out. Typing only “scf” will produce “SELECT COUNT(*) FROM” to Count number of records returned by query. Typing three letters is much quicker. Take a few minutes to check it out. It will be worth your time.
Here is an article about this topic:
Here is little trick to change the CSV save-as option to use something other then a comma. Sometimes you need to make an output file with a delimiter other than a comma, tab or space. Excel does not come with an option to save a file as pipe delimited. You have to make a system change to accomplish this. This option is in the strangest place and you would never think to look there.
This link will show you the easy way to make the change. Be aware that when you make this change, you make it for the computer not just Excel.
I think that the OVER clause is underutilized. I plan to start using it more for development solutions.
It determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.
This link provides a great overview of the OVER clause.
BTW: You can now subscribe to this blog.
I use some of these all the time. I know I would like to setup some custom shortcuts to make my life easier.
Here is an article including a script you can run on your SSMS to setup custom shortcuts.
If you happen to be in Charlotte on Saturday Sept. 17th, Sign up to attend SQL Saturday. It is a great learning experience.
Free book available.
Execution plans tell you everything you need to know about what data is processed. By knowing what you should look for, you can improve your query plan and therefore improve overall performance.