Window Function Basics

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

Then, he starts looking at how to build a window function, starting with the OVER clause:

— here’s a simple SELECT statement from the Products table

SELECT ProductName,
UnitPrice
FROM Products
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.

System Objects And Helper Functions

Kenneth Fisher compares and contrasts querying system tables versus using built-in helper functions which query the system tables:

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.

COALESCE, ISNULL, And Data Types

Shane O’Neill shows a subtle difference between ISNULL and COALESCE:

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.

Joins With Kafka

Florian Trossbach and Matthias J Sax show the various sorts of joins offered in Kafka, both streams and tables:

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.

Using RAISERROR For Debug Info

Doug Lane exhorts people to use RAISERROR instead of PRINT when printing messages:

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.

So You Want To Wait…

If you need your queries to be slower, Kenneth Fisher has you covered:

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.

Parent-Child Relationships And Native Compilation

Ned Otter looks at different ways to insert data with parent-child relationships using natively compiled, memory-optimized procedures:

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.

Truncation Versus Deletion

Richie Lee contrasts two methods of getting rid of data:

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:

  1. The TRUNCATE TABLE statement is a DDL operation, whilst DELETE is a DML operation.

  2. 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.

Finding Out Whodunnit Using The Transaction Log

David Fowler shows us how to figure out which user made a bad data change when you don’t have auditing mechanisms in place:

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.

Substrings In SQL Server Versus Oracle

Kevin Feasel

2017-08-25

Syntax

Daniel Janik continues his SQL Server versus Oracle syntax comparison series:

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.

Categories

October 2017
MTWTFSS
« Sep  
 1
2345678
9101112131415
16171819202122
23242526272829
3031