This content originally appeared on DEV Community and was authored by Jakub Rumpel
For most of the last two years, My job has been mostly about writing another and another SQL query, be it to check what's happening with some data, debug a weird error or develop a stored procedure as a part of a feature I'm working on. During that time I've written dozens of different procedures consisting of multiple queries, even to the point of committing small heresy in the form of two thousand lines monster full of XML deserialization and business validations.
Bad times.
But every such experience had left me with some new knowledge to use in future projects, things I could use to further optimize my SQL code or make some debugging easier, so I decided to share them in the form of this blog post
Easily commented out conditions
This one is very small and simple, but very useful when I need to write a query with multiple conditions. Let's take a look at the following example:
SELECT
Id,
Name
FROM
SomeTable
WHERE
Field1 = "X"
AND Field2 = "Y"
Now, here we already made our life easier by putting an AND keyword on the new line, right before second condition. It's a good practice when you're actively working on a query, because if you need to see what kind of result you'll achieve without the second condition, you can simply comment it out:
WHERE
Field1 = "X"
--AND Field2 = "Y"
See? Nice and easy, and in most SQL IDEs, like SSMS or DataGrip, you can comment out a single line with a simple shortcut.
But what if you want to see what would happen with second condition, but without first? If you leave AND keyword on its place, you'll just create an error, because there will be no condition preceding it:
--*SQL server won't let you do this, as there is no working condition before AND!*
WHERE
--Field1 = "X"
AND Field2 = "Y"
Of course, you could move AND to the end of the first condition, but then it would trouble you if you need to comment out the second condition again. Or you could comment out AND like this: /AND/ and it would work, too. It's all fine and dandy, but what if there is other option?
WHERE 1=1
AND Field1 = "X"
AND Field2 = "Y"
Looks weird, but it could actually save you a lot of time. That way if you don't need any condition, you can simply comment it out, without breaking your whole query, which depending on size might be very difficult to find where did the error happened. Adding this simple 1=1 will not affect your qery performance in any significant way, but instead will make it much easier to work on query "right now".
Of course, it's not something I would leave in a production code, but for the sake of debugging, it can save you some precious seconds or even minutes, and most importantly, it will let you stay "in the flow", without being bothered by unexpected and irritating errors.
Instant insert into TempDB table from SELECT
Now this is something I've picked up just recently, but it instantly made my life much easier. Consider following code:
CREATE TABLE #Sample (Id INT, Name NVARCHAR(100), Description NVARCHAR(500))
INSERT INTO #Sample (Id, Name, Description)
SELECT
Id,
Name,
Description
FROM
SampleTable
WHERE
Condition = true
This example is very simple:
- We create a temporal table (which is marked by # character at the beginning of the name)
- We write its columns and their types
- We repeat both table name and columns in INSERT statement
- We fill it with data from SELECT statement
It still made us write quite a lot of words, and any mistake in data type or in choosing columns in an INSERT statement can lead to irritating errors, which could stop you for a long enough time that you'll lose your focus.
Instead, you could try this:
SELECT
Id,
Name,
Description
INTO #Sample
FROM
SampleTable
WHERE
Condition = true
Yes: I just replaced both CREATE TABLE and INSERT INTO statements with single INTO between SELECT and FROM statements. What does it do? It creates a table with the same column names and types as ones we just retrieved from the database using our SELECT statement, without writing a lot of unnecessary code, and making it more difficult to make a mistake in the list of columns or risking that one of the columns will have a wrong type.
Of course, there is a "but": If you have to run this command multiple times, it won't just insert new data into our #Sample table, but instead it will throw an error saying that such a table already exists. We will deal with this in just a second:
Ensuring that unnecessary temporal table doesn't already exist
When we use temporal tables, sometimes we also have to delete them, especially when we are actively developing our query. Of course, it's less important when we use #table in stored procedure, as it will get dropped when the procedure is finished, and it's scoped to this procedure, just as our #table will be scoped to our DB session - but if it exists when it shouldn't, it's still a problem.
An obvious choice would be to simply DROP #table at the end of our query, but there is a risk: If for any reason #table doesn't exist when DROP is called, it will throw an error. Similarly, if for any reason the DROP statement won't be called (for example, because there was an error before it) next time we run our query we will also run into a problem.
So what's the solution?
IF OBJECT_ID('tempdb..#app') IS NOT NULL DROP TABLE #app
This single line of code will do two things:
- Check if table #app already exists in tempdb
- If it exists, it will drop it.
Put this line at the beginning of your SQL script, change the name to whatever you like, and you're good to go. If a table doesn't exist yet, nothing will happen, but if it exists before it should, it will be dropped.
This line makes an excellent work next to SELECT INTO statement, as it protects you against unnecessary errors while letting you use this statement easily.
For now, this will be all. If I remember (or find out) any other tips that can make my life a bit easier, this post will have a part 2.
Summary
- You can make debugging SQL queries much simpler by adding "fake" 1=1 condition, and putting every "real" condition after AND or OR keywords.
- Creating and filling tempdb tables can be very simple in SQL Server
- There are a lot of possible errors while operating on tempdb tables, but you can avoid them if you remember that they are there
But what about You? Do You have any interesting tips or tricks you use in SQL Server that helps you get through the day? If you do, please share them in comments, I'd love to learn something new to use in my day-to-day SQL work.
This content originally appeared on DEV Community and was authored by Jakub Rumpel
Jakub Rumpel | Sciencx (2021-04-22T14:29:48+00:00) 3 small tips to make your SQL Server experience easier. Retrieved from https://www.scien.cx/2021/04/22/3-small-tips-to-make-your-sql-server-experience-easier/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.