Getting Data between Excel and SQL Server using ODBC

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…

https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc–/

 

 

A Better Way to Execute SSIS Packages with T-SQL

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.

https://www.timmitchell.net/post/2016/11/28/a-better-way-to-execute-ssis-packages-with-t-sql/

Comments in SQL Stored Procedures

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.

https://sqlperformance.com/2016/11/sql-performance/comments-hamper-performance

Format your T-SQL code

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.

https://www.simple-talk.com/sql/t-sql-programming/devil-wrote-code/

SQL Prompt Snippet Manager

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:

http://www.sqlservercentral.com/blogs/steve_jones/2016/09/27/quick-tipsusing-ii-in-sqlprompt/

OVER Clause

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.

http://www.sqlservercentral.com/articles/Over+Clause/132079/

BTW: You can now subscribe to this blog.

SSMS Query Shortcuts

I use some of these all the time. I know I would like to setup some custom shortcuts to make my life easier.

https://msdn.microsoft.com/en-us/library/ms174205.aspx?f=255&MSPPError=-2147217396

Here is an article including a script you can run on your SSMS to setup custom shortcuts.

http://slavasql.blogspot.com/2016/02/ssms-query-shortcuts.html

If you happen to be in Charlotte on Saturday Sept. 17th, Sign up to attend SQL Saturday. It is a great learning experience.

http://www.sqlsaturday.com/560/eventhome.aspx#

SQL Server Execution Plans, 2nd Edition

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.

http://www.red-gate.com/library/sql-server-execution-plans-2nd-edition?utm_source=ssc&utm_medium=publink&utm_campaign=books&utm_content=executionplans_ebook&utm_term=executionplans

SQL Join Types

The quote “One Picture Worth Ten Thousand Words” could not be more appropriate for SQL joins. This picture helps me think about the kind of join I want for any situation. The correct join makes the results correct. The wrong join may look correct but you could be missing data.

Find Images here

SQL JoinsGood Site for more information.

http://www.dotnettricks.com/learn/sqlserver/different-types-of-sql-joins

Index Hints

While modifying a function one day. I noticed that a SELECT statement was running slow.
Looking at the execution plan, I found that 90% of the performance was consumed by one Key Lookup on RPT_ACTION_TEST.
After reviewing the function I saw this.
FROM [Database].[dbo].[RPT_ACTION_TEST] O WITH (INDEX(PK_IDX_ACTION_3))

I reviewed the index that is used and it does not include the STATUS_DATE or ACTUAL_DATET. That is why you should not use the WITH statement to force an index. I had already created an index that would work great with this but it won’t get used. IDX_RECORD_STAGE includes everything the query would need to be freaky fast.

By removing the WITH hint, now it is able to use the IDX_RECORD_STAGE index.
This change increased the speed and it no longer needs the Key Lookup.
Execution time went from 20 seconds to 3 seconds.

Before using Index scan and Key lookup:

Before

After using Index Seek:

After

Please read this article for more information.

https://www.brentozar.com/archive/2013/10/index-hints-helpful-or-harmful/