#Sqlpro save queries code

When I'm looking at search code performance, I'll often ask: * If we pass in multiple fields, like this, * Will that work? Is there a bug in that logic? */ It's a bad way - it doesn't really work - but let'sĬREATE OR ALTER PROC NVARCHAR(100) = NVARCHAR(100) = INT = NULL AS They want a stored proc that works like this:ĮXEC usp_SearchUsers = 'Brent%', = 'Las Vegas%' ĮXEC usp_SearchUsers = 'Indiana%', = 107548 You should be able to pass in 1, 2, or all 3, and find just the users that We'll be searching by DisplayName, Location, and/or Reputation, so createĬREATE INDEX DisplayName ON dbo.Users(DisplayName) ĬREATE INDEX Location ON dbo.Users(Location) ĬREATE INDEX Reputation ON dbo.Users(Reputation) įolks want a single stored procedure that can search on any of these 3 fields.

How big is the users table? Look at how many rows are returned from this query,Īnd on the Messages tab, check the STATISTICS IO results to see how many * Turn on our tuning options and Actual Execution Plans: */ Make sure we don't have extra nonclustered indexes that would interfere with Run these demos one at a time.', 20, 1) WITH LOG RAISERROR(N'Oops! No, don''t just hit F5. This first RAISERROR is just to make sure you don't accidentally hit F5 and * Any supported SQL Server or Azure SQL DB Open source with the Creative Commons License.

#Sqlpro save queries how to

In one all-demo hour, you'll learn how to write dynamic SQL that's easy to So how do we make those go fast? And how can we get 'em to use indexes? Order, etc, and you have to accept any of 'em. Stored proc with parameters for product category, name, price range, sort For example, you have that 'product search' You're comfortable writing T-SQL, and you've built a lot of stored procedures

#Sqlpro save queries pro

Video: Dynamic SQL Pro Tips – from my Mastering Query Tuning class, where I go into even more detailsĭynamic SQL - When, Why, and How to Use It.Dynamic Search Conditions in T-SQL by Erland Sommarskog.The Curse and Blessings of Dynamic SQL by Erland Sommarskog.4 SQL Injection Techniques for Stealing Data by Bert Wagner.You can use any size, small/medium/large – they’ll all work to show the problems.Īfter this session, your next steps for learning are: I’ll be using the Stack Overflow public database. Show you my favorite tips to make it faster and easier to troubleshoot (and note that in the live class, I don’t cover all of the tips – the script keeps going with my conversational style in the comments, so you can read through more tips & tricks after class).Build a multi-parameter stored procedure the old-fashioned way.It can also be a route to sitting under your desk, banging your head against a wine bottle, wishing you could stop seeing double apostrophes mocking you. It can be an awesome, powerful, and fast solution to a lot of performance problems.

If you’ve ever needed to build a stored procedure that took a lot of different parameters and served a lot of different purposes, you’ve probably used dynamic SQL.