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

Connect a SQL Server database to your Excel workbook

How many times a day do you open a table or run a procedure and want to review the results in Excel. You right click on the result set to select all, select copy with header, open Excel, select the entire sheet and format as text then paste. There is a better way. Under the data menu in Excel you will find some menu selections for pulling in external data. If you select “From Other Source” you will see that there is a selection for SQL Server. With the server name and your windows authentication you will be presented with a list of Views and Tables that you can select from in each database. It is fast and easy to open a table for review in Excel. The data connection wizard creates an ODC connection to the data and pulls it into Excel. Once the connection is made you will be able to reuse the ODC connection anytime via Existing Connections. Check it out.

Please keep in mind that this process for selecting data from a table is the same as if you opened a table without any criteria, it returns the entire table. Do not use this for production unless you know how many records you will be retrieving. The impact on production can be substantial.

https://support.office.com/en-us/article/Connect-a-SQL-Server-database-to-your-workbook-22c39d8d-5b60-4d7e-9d4b-ce6680d43bad

 

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/

Using the MERGE Statement to Perform an UPSERT

The term UPSERT refers to an operation that inserts rows into a table if they do not exist, otherwise they are updated.

Simple example:

USE tempdb;
GO
MERGE dbo.Product AS T  
USING dbo.NewInventory AS S 
ON T.ProductName = S.ProductName
WHEN MATCHED THEN  
  UPDATE SET T.Qty = T.Qty + S.Qty
WHEN NOT MATCHED THEN  
  INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty);
SELECT * FROM dbo.Product;

Applicable example:

MERGE example

Good article on the subject:

http://www.databasejournal.com/features/mssql/using-the-merge-statement-to-perform-an-upsert.html

Table valued parameters using User-Defined Data Types

User-Defined Data Types can be used as a reusable memory table schema.

Here is where the types are stored in a database

User-Defined Table Types

Simple example

— Create Table Type
CREATE TYPE StateTbl AS TABLE
( StateID INT
, StateCode VARCHAR(2)
, StateName VARCHAR(200)
);

— Create test procedure
CREATE PROCEDURE SortStates @S StateTbl READONLY
AS
SELECT  StateName
FROM    @s
ORDER BY StateName
RETURN 0;
GO

— Insert data and run procedure
DECLARE @p AS StateTbl
INSERT  @p
VALUES  (1, ‘NC’, ‘North Carolina’)
, (2, ‘VA’, ‘Virginia’)
, (3, ‘CO’, ‘Colorado’);

EXEC SortStates @p

Here is a video that shows how to use it https://www.youtube.com/watch?v=Ewf5bivTKdI