Tag: SQLNewBlogger

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

One of the lesser used and known features of T-SQL are the output parameters from a stored procedure. I used one of these recently, so I wanted to blog about it.

Getting a String

I was working on part of the 2018 Advent of Code, which is a great set of programming exercises for anyone. I typically build a procedure to solve each puzzle, since that’s a common way of capturing code. If there’s a single numeric result, a RETURN code works fine.

In one puzzle, I needed to return a string. If you try this in a procedure, it won’t work.

Instead, I need another solution. I could certainly SELECT back my string, but in this case, I wanted to have this assigned to a variable. I could do that in a few ways, but decided the easiest was an OUTPUT parameter.

To add an output parameter to my procedure, I first add my variable as a regular parameter.

CREATE OR ALTER PROCEDURE dbo.StringTest
   @s VARCHAR(10)

AS

BEGIN
     SELECT @s = 'Some Code'

END

GO

Next, I add the OUTPUT keyword after the type.

CREATE OR ALTER PROCEDURE dbo.StringTest
   @s VARCHAR(10) = '' OUTPUT

AS

BEGIN
     SELECT @s = 'Some Code'

END

GO

My call to the procedure should also include the OUTPUT keyword.

DECLARE @result VARCHAR(10);

EXEC dbo.StringTest @s = @result OUTPUT;

SELECT @result;

This works fine, allowing me to pass some value back to the caller.

Not something I use often, but if I need to get some singular value back, this works.

SQLNewBlogger

This post was started at 10:00am one morning. I got back to this sentence at 10:09. That was the entire setup of the code, capturing screen shots, and writing the post. Easy for you to do as well.

Give this a try. How would you use an OUTPUT parameter?

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

This was a post that caught my eye, since I’d just written a piece on STRING_SPLIT(). Someone had this data:

DECLARE @Names VARCHAR(8000) = 'Steve, Grant, Kathi, Kendra';

They wanted this returned as a string that was sorted, so that the values would be:

Grant, Kathi, Kendra, Steve

String manipulation isn’t the strength of SQL Server, but we can do this with STRING_SPLIT(). If we use STRING_SPLIT(), we get a list of values. We want to remove the spaces after the comma, so we use REPLACE to remove that.

SELECT *

FROM STRING_SPLIT(REPLACE(@Names, ' ', ''), ',')

ORDER BY value;

We can then aggregate these back together in a variable assignment, adding the comma for each row.

SELECT @newtext = @newtext + Value + ', '

FROM STRING_SPLIT(REPLACE(@Names, ' ', ''), ',')

ORDER BY value;

This gives us:

SQLNewBlogger

A quick T-SQL application of some skills I learned. Can you do something similar? Maybe order dates or numbers that are in a string in the wrong order?

That should be a 10-15 minute post.

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

Many people think that that an identity property will ensure a consistent, increasing numerical value. I ran across this tweet that indicates that situation.

This isn’t really true, for many reasons, but in this post I’ll look at the possible reasons we get gaps in identity values.

Normal Operation

Let’s start with a basic table that contains an identity value. I’ll use this code:

CREATE TABLE dbo.SalesOrderHeader

( OrderKey INT IDENTITY(1, 1) 

, CustomerName VARCHAR(30)

)

GO

Now I can insert a few rows. Note that the results shown below the code will contain increasing values for the OrderKey.

INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Andy')

INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Brian')

INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Steve')

INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Anna')

GO

Each of these inserts is a separate transaction, and they cause the identity to increment.

Deleting Rows

This is noted in the tweet as a cause, but let’s test this.

One of the common ways that we get gaps in identity values is when rows are deleted. Let’s remove the row with Steve in it.

I clearly have a gap in OrderKey here now. What happens if we add a new row? The identity value is built for (some) efficiency and doesn’t fill the gap. Only the next value is kept. We insert a row and get a 5.

As a side note, there is no index on this table, and no ORDER BY clause, so you can clearly see that there isn’t a reason why I should expect the ORDERKEY column to be returned in numerical or even insert order.

The Rollback

One of the more common occurrences with inserts is a problem with the value. For example, in this table, I have allocated 30 characters. What happens if I run this code?

INSERT dbo.SalesOrderHeader (CustomerName) 
   VALUES ('A Really Long Name Van Something The Third')

I get an error, which is shown here.

Checking the table, I have no value:

Let’s insert a new value and see.

We get a gap. The value “6” was skipped because of the error. The identity was allocated, but the rollback of the transaction due to the error did not rollback the identity sequence.

Reseeding the Property

One of the other ways to miss a value is directly reseeding the table. I can use the DBCC CHECKIDENT function to accomplish this. In my case, let’s run this code and set the identity value to 20.

DBCC CHECKIDENT(SalesOrderHeader, RESEED, 20)

GO

Now I can insert new values and I’ll get these results.

The identity value was set to 20 and the next insert will increment this and take 21, leaving a gap from 8 to 20.

Be Careful

Don’t depend on the identity property to give you uniqueness, consecutive values, or avoid duplicates. It is up to you to code properly to account for these values.

SQLNewBlogger

This post came about from helping someone understand the problems and limitations. I wrote this in about 20 minutes (with setup and testing) to ensure that I understood what I was explaining to someone.

You could write something similar to show that you know the ways in which identity works.

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

As part of my experiments with the sequence object, I wanted to see what allows me to restart a sequence at a new value. This is useful in a few situations, some of which I want to see in this post.

Starting Over

One common scenario might be where I create a sequence and test it a few times, but don’t want those values lost. For example, Suppose I create this sequence and test it a few times.

CREATE SEQUENCE Counters.TopTen

START WITH 1

MAXVALUE 10

CYCLE

GO

SELECT NEXT VALUE FOR Counters.TopTen

GO

SELECT NEXT VALUE FOR Counters.TopTen

GO

I don’t want the first two values to be removed from the sequence. Instead, I want to get the next number back to 1. I could run 8 more SELECTs to allow the sequence to cycle, but if you’re like me, you’ll end up executing this one too many times and then have to repeat the experience.

Instead, I can use the ALTER command to fix this.

ALTER SEQUENCE counters.TopTen RESTART WITH 1

Of course, I’ll test this with a SELECT, but once I am confident this behaves as expected, I’ll re-run the ALTER again.

Going Backwards

One common situation might be a case where an application requests a number of sequence numbers for a situation, but they never get inserted. Suppose I set up an insert statement to load some data in a table, but a key error or some other problem prevents the inserts. I don’t want those values to be lost, so I want to restart numbering.

As an example, I find that one of my sequences has the value, 41.

However, this is because a load of new products failed. The last number used in the table was 8.

In this case, I want to reset the sequence object to 9, so let’s do that.

ALTER SEQUENCE Counters.Products RESTART WITH 9

Now I can proceed on loading products into this table, using the sequence object to get the next value.

SQLNewBlogger

This was a continuation of a series of posts on the sequence object. As I continued to experiments, I captured the code and some images to use in posts, writing this up as I had time.

For this post, I took about 10 minutes of experimenting and then another 5-10 trying to sort out some of the experiments into an area. This writeup was about 10 more minutes.