The TOP operator is used to restrict the number of rows being returned. If you’re familiar with other database engines, you might have used a LIMIT operator. TOP is a bit like that.
You can see that in the first query shown below:
If you look at the bottom query, you can see that it can also be used to limit the rows to a percentage instead of a number of rows.
2019-07-15
I noticed a post the other day by one of my friends and fellow MVP Aaron Bertrand where he mentioned that he used to scoff at the TRIM() function, at least until he read the documentation.
Almost as long as I’ve worked with SQL Server (since 1992), I’ve heard people complaining about the lack of a TRIM() function. No-one could understand why we had to keep writing both LTRIM() and RTRIM() to get the required result. So it wasn’t surprising that we were all happy in SQL Server 2017 when TRIM() finally got added.
2019-07-11
When you create a SQL Server stored procedure, some session-related settings are part of the configuration of the procedure. The two most common problems with this are related to ANSI_NULLS and QUOTED_IDENTIFIER.
When procedures are scripted, SQL Server tools also script the settings for the session, but what often happens is that the person creating the procedure, just executes the CREATE PROC statement and doesn’t execute the SET statements before it. When they do this, you can end up with procedures that don’t have the settings you expect.
2019-07-10
The T-SQL IN operator allows you to choose from a list of matching values.
In the top case shown below:
I’ve said where StateName is one of QLD, NSW, or VIC.
Now see that’s exactly the same as if I had written the option shown at the bottom where I said:
where StateName equals QLD or StateName equals NSW or StateName equals VIC.
The two options work exactly the same and mean the same thing, so having an IN operator is logically equivalent to having a whole lot of predicates joined by OR operators.
2019-07-08
In an earlier post, I showed how to create a function that worked out if a date was a weekday or not. In this example, I’ve used that function to create a more complex function that adds a number of weekdays to a given date.
In Power Query, go to the Edit Queries option and choose to add a new query. (You could also start by duplicating an existing query). Then on the View menu, choose the Advanced Editor and replace any code that’s there with this:
2019-07-05
I’ve seen a number of interesting discussions lately around how to handle deletes in database tables. One discussion in Stack Overflow had a variety of suggestions.
One easy option in SQL Server 2016 and later is to use temporal tables as they’ll keep the history for you. But there are a number of reasons why they might not be suitable.
Sometimes, you just want to stop rows being deleted, and to set an IsDeleted flag instead. If you don’t want to depend upon the application doing that for you, the easiest way to accomplish that is to use INSTEAD OF triggers.
2019-07-04
SQL Server tables should have a primary key. I’m not talking about a clustering key (that’s a different topic) but a key that logically identifies each row, and isn’t ever NULL.
I have SQL purist friends that argue that a table without a primary key really isn’t a table.
When I’m reviewing database designs, one of the things I look for is the lack of a primary key on tables. It’s not just a purist issue. Without a primary key, other features like replication are problematic.
2019-07-03
I really love SQL Server Integration Services (SSIS). In fact, I wish Microsoft was giving it far more love than it is. So many of my clients use it, but unfortunately, not everyone uses it well, or for the right things.
One really common mistake is to perform lots of row-by-row logic within the SSIS packages. Let’s take an example of loading a fact table in a data warehouse as an example:
2019-07-02
The T-SQL BETWEEN operator let’s you specify a range of values. So in this case shown here:
I’m asking for rows where the DateCreated is between the first date and the second date. Notice how I’ve written the date here. We’ll talk about that a bit more in a later post. 2012, zero one, twenty-one is the 21st of January 2012. This is one of the formats that makes dates consistent.
2019-07-01
In the T-SQL language you can specify a positive integer constant in an ORDER BY clause as I’ve shown in the main image above.
Please don’t do this !
For those that haven’t used this, the number refers to the position of the column in the SELECT list. If you do this, you are creating really fragile code and also code that’s really hard to read, particularly as the statements get more complex.
2019-06-27
