Doug Kline has a new series on window functions. First, he looks at differences between RANK, DENSE_RANK, and ROW_NUMBER:
— Quick! What’s the difference between RANK, DENSE_RANK, and ROW_NUMBER?
— in short, they are only different when there are ties…
— here’s a table that will help show the difference
— between the ranking functions
— note the [Score] column,
— it will be the basis of the ranking
— here’s a simple SELECT statement from the Products table
ORDER BY UnitPrice DESC
— this shows that the highest priced product is Cote de Blaye, productID 38
— but sometimes the *relative* price is more important than the actual price
— in other words, we want to know how products *rank*, based on price
Doug’s entire posts are T-SQL scripts along with embedded videos.
So which should you use? Well, like most things in the database world, it depends. Do you need to cross databases? Do you need to use NOLOCK for a diagnostic query? Do you need additional information available in one of the views that isn’t available in one of the helper functions?
Click through for the comparison. I agree with his end result, that you should at least know both methods, even if you lean toward one.
You may be asking yourself “What the…?!”.
I asked myself the same question but then I thought of a better idea and asked my Senior the question instead (he usually has better answers than I do), who proceeded to tell me that the final test would only work exactly at midnight!
….so I repeated my question to him.
It’s an interesting read, and not something you’d commonly think about.
Apache Kafka’s Streams API provides a very sophisticated API for joins that can handle many use cases in a scalable way. However, some join semantics might be surprising to developers as streaming join semantics differ from SQL semantics. Furthermore, the semantics of changelog streams and tombstone messages (that are used for deletes) are a new concept in stream processing.
Kafka’s journey from Pub/Sub broker to distributed streaming platform is well underway, and our times as engineers are very exciting!
I didn’t know you could join streams together in Kafka, so that’s really cool.
It wasn’t until a few years ago, when I started contributing to the First Responder Kit at Brent Ozar Unlimited, that I noticed every status message in the kit scripts was thrown with something other than PRINT.
Strange, I thought, since those scripts like to report on what statements are running. Turns out, they avoided PRINT because it has some serious drawbacks:
- PRINT doesn’t necessarily output anything at the moment it’s called.
- PRINT statements won’t show up in Profiler.
- PRINT can’t be given variable information without CAST or CONVERT.
Those are important limitations, as Doug shows.
And in case you run into a development team that complains that when they time their code the duration is all over the place, this little gem will make sure their query will always take the same amount of time (assuming normal run time is under 90 seconds).
It’s the T-SQL equivalent of speed-up loops.
This blog post demonstrates various approaches when using native compilation to insert rows into parent/child tables.
First, let’s create tables named Parent and Child, and relate them with a FOREIGN KEY constraint. Note that the Parent table uses the IDENTITY property for the PRIMARY KEY column.
Ned’s first example uses @@IDENTITY and shows that this doesn’t work. But there’s an easy version which is superior to @@IDENTITY and is supported: SCOPE_IDENTITY(). Even outside of memory-optimized tables, you want to use SCOPE_IDENTITY() over @@IDENTITY anyhow because of the risk of triggers changing the “current” identity value. Here’s a quick example I ginned up using SCOPE_IDENTITY:
CREATE TABLE dbo.Parent ( Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY NONCLUSTERED, SomeChar CHAR(1) NOT NULL ) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY); GO CREATE TABLE dbo.Child ( Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY NONCLUSTERED, ParentId INT NOT NULL, SomeChildChar CHAR(1) NOT NULL ) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY); GO CREATE PROCEDURE dbo.TestParentChild ( @SomeChar CHAR(1), @SomeChildChar CHAR(1) ) WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') INSERT INTO dbo.Parent ( SomeChar ) VALUES ( @SomeChar ); INSERT INTO dbo.Child ( ParentId, SomeChildChar ) VALUES ( SCOPE_IDENTITY(), @SomeChildChar ); SELECT p.Id, p.SomeChar FROM dbo.Parent p; SELECT c.Id, c.ParentId, c.SomeChildChar FROM dbo.Child c; END; GO EXEC dbo.TestParentChild @SomeChar = 'A', @SomeChildChar = 'B'; EXEC dbo.TestParentChild @SomeChar = 'Z', @SomeChildChar = 'Y'; EXEC dbo.TestParentChild @SomeChar = 'W', @SomeChildChar = 'L';
The results are what you’d expect.
I’ve been using TRUNCATE TABLE to clear out some temporary tables in a database. It’s a very simple statement to run, but I never really knew why it was so much quicker than a delete statement. So let’s look at some facts:
The TRUNCATE TABLE statement is a DDL operation, whilst DELETE is a DML operation.
TRUNCATE Table is useful for emptying temporary tables, but leaving the structure for more data. To remove the table definition in addition to its data, use the DROP TABLE statement.
Read on for more details and a couple scripts to test out Richie’s statements.
So it’s looking like things are in a bad way, obviously we could go to a backup and get the old values back but that’s never going to tell us who made the change. So that transaction log again, how do we actually go about getting our hands dirty and having a look at it.
Well there’s a nice little undocumented function called fn_dblog. Let try giving that a go and see what we get back. By the way, the two parameters are the first and last LSNs that you want to look between. Leaving them as NULL with return the entire log.
This is great unless you have connection pooling and the problem happened through an application. In that case, the returned username will be the application’s username.
Parsing strings is a feature that is often needed in the database world and SUBSTRING/SUBSTR are designed to do just that. I find it interesting how these two platforms approached the functions differently and that’s definitely shows how you can do many things to get to the same answer.
It’s a short post, but Daniel does show one big difference between the Oracle and SQL Server substring functions.