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
Good Site for more information.
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.
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:
After using Index Seek:
Please read this article for more information.
The term UPSERT refers to an operation that inserts rows into a table if they do not exist, otherwise they are updated.
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;
Good article on the subject:
I had some issues this week that made me think about error handling. TAC is a technique that was created with SQL 2005.
Please spend some time at least reviewing the SQL error functions.
User-Defined Data Types can be used as a reusable memory table schema.
Here is where the types are stored in a database
— 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
ORDER BY StateName
— Insert data and run procedure
DECLARE @p AS StateTbl
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