Voice of the DBA

Tag: T-SQL

The Default Frame for Window Functions
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This bites me constantly, and I was reminded of this while watching Kathi talk at #SQLintheCity. When you write a Window function, there is an implicit default frame for the windows that you might not be aware of.

For example, if I have this data:
```
create table WindowDemo( groupid int,
letterid int
, letter varchar(10))
GO
insert WindowDemo
values
( 1, 1, 'A')
, ( 1, 2, 'B')
, ( 1, 3, 'C')
, ( 2, 4, 'D')
, ( 2, 5, 'E')
GO
```
and I run this code:
```
select groupid
, letterid
, last_value(letter) over (partition by groupid order by letterid)
from WindowDemo
```
I get this:

Not what I expected. I would think the last value for each groupid is the largest letter. Instead, I have a running total of sorts.

The Default Framing

There is a framing clause that I can use after the ORDER BY in the OVER clause. The default frame is RANGE UNBOUNDED PRECEDING AND CURRENT ROW. At least, this is what appears when you include an ORDER BY clause. Many of us do this, but still get confused with the LAST_VALUE() and FIRST_VALUE functions.

What I really want is a complete set of data, which is either starting from the current row to the end, or includes all values. If I modify my framing clause, I’ll get what I expect.
```
select groupid, letterid
, last_value(letter) over (partition by groupid order by letterid rows between unbounded preceding and unbounded following)
from WindowDemo
```
This gives me:

That’s what I’d expect for a LAST_VALUE().

SQLNewBlogger

This has bitten me a few times, so I decided to write about it. I can show that I solved this issue, which is what my next boss wants to see. The other side effect is that blogging helps me remember how this works.

This took about 15 minutes, mostly to reproduce the demo that was similar to my issue, but simpler to explain.
December 13, 2018
Basic Sequences–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I haven’t used sequences much in my work, but I ran into a question recently on how they work, so I decided to play with them a bit.

Sequences are an object in SQL Server, much like a table or function. They have a schema, and are numeric values. In fact, the default is a bigint, which I think is both good, and very interesting. Since this will implicitly cast down to an int or other value, that’s good.

The sequence is created like this:
```
CREATE SEQUENCE dbo.SingleIncrement
  AS INT
  START WITH 1
  INCREMENT BY 1;

GO
```
These can be similar to identity values, and in fact, if I make 5 calls to this object, I’ll get the numbers 1-5 returned. Here I’ve made one call.

This is interesting, as the NEXT VALUE FOR is what accesses the sequence and returns values. I can use this in some interesting ways. For example, if I have to insert values into a table, I can do this:
```
CREATE TABLE SequenceTest

( SequenceTestKey INT IDENTITY(1,1)

, SequenceValue INT

, SomeChar VARCHAR(10)

)

GO

INSERT dbo.SequenceTest

(
     SequenceValue,
     SomeChar

)

VALUES
   (NEXT VALUE FOR dbo.SingleIncrement, 'AAAA')

, (NEXT VALUE FOR dbo.SingleIncrement, 'BBBB')

, (NEXT VALUE FOR dbo.SingleIncrement, 'CCCC')

, (NEXT VALUE FOR dbo.SingleIncrement, 'DDDD')

, (NEXT VALUE FOR dbo.SingleIncrement, 'EEEE')
```
When I query the table, I see:

Notice that the sequence number is off by one from the identity. This because I first accessed the sequence above.

The sequence is independent of a table or columns, unlike the identity. this means, I can keep the sequence numbers going between tables. For example, let’s create another table.
```
CREATE TABLE dbo.NewSequenceTest

( NewSequenceKey INT IDENTITY(1,1)

, SequenceValue INT

, SomeChar VARCHAR(10)

)

GO
```
Now, we can run some inserts to both tables and see what we get.
```
INSERT dbo.NewSequenceTest VALUES (NEXT VALUE FOR dbo.SingleIncrement, 'FFFF')

INSERT dbo.SequenceTest    VALUES  (NEXT VALUE FOR dbo.SingleIncrement, 'GGGG')

INSERT dbo.NewSequenceTest VALUES (NEXT VALUE FOR dbo.SingleIncrement, 'HHHH')

INSERT dbo.SequenceTest    VALUES  (NEXT VALUE FOR dbo.SingleIncrement, 'IIII')

INSERT dbo.NewSequenceTest VALUES (NEXT VALUE FOR dbo.SingleIncrement, 'JJJJ')
```
After running the inserts, I’ll look at both tables. Notice that the values for the sequence are interleaved between the tables. The first insert to the new table has the value, 7, which is the next value for the sequence after running the inserts for the first table.

In these tests, I’ve used 11 values so far. I can continue to use values, not just for inserts, but elsewhere.

This behavior is both fun, handy, and useful, but also dangerous. These values get used when I query them, whether the inserts work or not. Here’s a short test to look at this:
```
ALTER TABLE dbo.SequenceTest ADD CONSTRAINT SequencePK PRIMARY KEY (SequenceTestKey)

SELECT NEXT VALUE FOR SingleIncrement

SET IDENTITY_INSERT dbo.SequenceTest ON

INSERT dbo.SequenceTest VALUES (NEXT VALUE FOR SingleIncrement, 'ZZZZ')

SET IDENTITY_INSERT dbo.SequenceTest OFF

SELECT NEXT VALUE FOR SingleIncrement
```
This gives me an error:

and I can see the last SELECT has the next sequence value.

There are a lot more to sequences, but I’ve gone on long enough here. This is a good set of basics to experiment further, which I’ll do in future posts.

SQLNewBlogger

This post went on longer than expected, and it was more of a 15-20 minute writeup as I set up a couple quick examples, tore them down, and rebuilt them with screenshots for the post.

This is a place where I can show I’ve started to learn more, and by continuing with other items in this series, I’ll show some regular learning.
December 5, 2018
Adding the Constraint Name to the PK at the End of Create Table–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

A good habit to get into is to explicitly name your constraints. I try to do this when I create tables to be sure that a) I have a PK and b) it’s named the same for all environments.

I can create a PK inline, with a simple table like this:
```
CREATE TABLE Batting
   (
        BattingKey INT NOT NULL CONSTRAINT BattingPK PRIMARY KEY
        , PlayerID INT
        , BattingDate DATETIME
        , AB TINYINT
        , H TINYINT
        , HR tinyint
   )

;
```
This gives a primary key, named “BattingPK, that I can easily see inline with the column.

Not everyone likes this, and I do run into clients and customers that want the keys separated from the column. This is fine, and I understand that this explicitly calls out the keys separately from the column.

This is an easy change to my code. I move the CONSTRAINT part to the end, as a separate item in the column list, and add the column(s) that I want to use in the constraint.
```
CREATE TABLE Batting
   (
        BattingKey INT NOT NULL 
        , PlayerID INT
        , BattingDate DATETIME
        , AB TINYINT
        , H TINYINT
        , HR TINYINT
        , CONSTRAINT BattingPK PRIMARY KEY (BattingKey)
   )

;
```
As you can see, inlining names for constraints is pretty easy, and it’s a good practice to get in the habit of adopting.

If I didn’t do this, I’d get a system generated name, which is fine, but the constraint name would then be different on every system where I deployed this object. Since I often want to test something on one system and deploy on another, future coding gets much more complex than it is by just doing this from the start.

SQLNewBlogger

This was a quick 5 minute post for me, following a short session teaching a client how to add the constraint to their table code.
November 28, 2018
A Problem with POWER()
I ran into an interesting problem while working with the POWER() function. I was trying to do some binary conversions and had a statement like this to process powers of 2.
```
SELECT POWER(2, n)
```
This was designed to take a value and return a power of 2. I then used a different value to determine if this was added to my conversion factor or not. In trying to work with some larger numbers, I ran into this error:

Msg 232, Level 16, State 3, Line 3
Arithmetic overflow error for type int, value = 2147483648.000000.

The error tells me I’ve exceeded the size of an integer. When I looked up the POWER() function, it tells me that it returns a bigint for a bigint input. Since I had ensured my “n” was a bigint, I was confused for a few minutes.

Then I realized that it’s not the n, but the “2” that’s the problem. By default, this scalar value is an integer. That means I need to ensure that this is a bigint to make this work. I changed to:
```
SELECT POWER(CAST(2 AS BIGINT),n)
```
And things worked.

Double check all the data types when you get a conversion error. SQL Server knows what’s wrong, but sometimes you need to dig in to determine where in your code you’ve made the mistake.
November 20, 2018