Voice of the DBA

Tag: T-SQL

Choosing Sequences Over Identity

When I was building software as a full time job, the choices for automatically generating surrogate keys were the identity property and the NEWID() function. At that time, GUIDs were too cumbersome to deal with, and I often used identity values in tables. I looked for natural keys, but often these were secondary keys for me and I preferred to use numerical values in many tables as PKs and FKs.

In the last few years I’ve run into a few customers that prefer sequences, which are a separate object in your SQL Server database. There are some challenges with sharded systems and using deployment technologies like SQL Compare, which is one reason I’ve avoided them. However, I was reading Dr. Greg Low’s blog recently where he noted that he prefers sequences to identities. He gives a good comparison of the reasons why sequences can be easier to manipulate, with some advantages because these are a separate object. I think he makes a good case why we might always want to consider sequences over identity values in new development.

He’s convinced me, though now I need to learn a new habit and build new skills to quickly and easily develop sequences for table keys instead of relying on identity values. This is a big change for me, with nearly 30 years of writing identity properties in CREATE TABLE statements.

Learning to adopt new techniques and changing habits of the ways that we grow and change, and certainly how we build better software. That’s a tenet of DevOps. Experiment and learn. This is a place where I’ll start to grow and see what I think as I build demos and PoCs for customers, giving sequences a chance. I wonder how many of you might rethink using identities in the future as well.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

May 5, 2020
T-SQL Context Switching

Studies have shown that reading someone else’s code can be both stressful and time consuming. Developers that pick up unfamiliar code find themselves spending much more time comprehending the code than with more familiar projects. I think this might be one of the reasons that so many developers want to build things themselves from scratch, or in least build new things in their team. They find their own code easier to understand.

This might be less of an issue with SQL code, which is often more straightforward for simple queries, but I do find that complex queries can be confusing to many developers if they didn’t write the code. This can especially true when another developer has used some rather uncommon trick. I’m also sure that plenty of people will cut and paste solutions, from places like SQLServerCentral, without understanding how the code works, but even decoding the answer to a question you’ve asked can be hard, even if another poster tries to explain how the code works. Hopefully most developers make an effort rather than coding a poorer solution or deploying code they don’t understand, but I’m sure some don’t when facing their own pressures to get work done.

With staff turnover and the need for developers to work on multiple projects, time spent interpreting code can be a drain on the efficiency of all developers. In addition, since individuals often find code easier to read in certain formats, tools like SQL Prompt can help, but there are plenty of easy ways to keep your codebase readable for staff. Dr. Greg Low has written a nice post on standards, which I think outlines some of the things I like to implement in a team. Having a known way to structure our code can help us communicate intuitively in a group, rather than require extra communication.

Actually, I don’t care what standards exist in a company. I don’t have strong feelings about any particular way to structure code. I prefer spaces, but if we decide on tabs, I think that’s fine. I’ll work within the framework we’ve built. What I do think is very important is that you have some standards. They don’t have to be perfect or exhaustive, but they do need to be followed.

If you don’t have standards, I suggest you start creating them. Add them as needed, when a developer finds a reason to make a decision, get consensus from the rest of the group and document the standard. Like other things in DevOps software development, we can grow these over time as we need them.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

October 7, 2019
Lengthen a Primary Key–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw a post recently where someone needed to increase the size of a PK and was getting a table rebuild message in SSMS. This is short post to show that isn’t required.

First, let’s create a table and give it some data. Note that the PK is set to a specific size.
```
CREATE TABLE dbo.Document

(DocumentKey NVARCHAR(5) NOT NULL CONSTRAINT DocumentPK PRIMARY KEY

, DocumentName NVARCHAR(200)

, DocumentDate DATETIME2

)

GO

INSERT dbo.Document
     (
         DocumentKey
       , DocumentName
       , DocumentDate
     )

VALUES
     (N'ABC23', N'Something very interesting', '2019-01-02'),
     (N'QNI43', N'An adventure you admire', '2019-02-02'),
     (N'MNT33', N'Magnets describing life', '2019-03-04'),
     (N'DEF25', N'Time for nothing', '2019-03-12'),
     (N'HIJ54', N'Dreams of the dark', '2019-04-17')

GO

SELECT top 10
  *
  FROM dbo.Document AS d

GO
```
If I try to insert data that’s larger, I’ll get this message:

In SQL 2019, I’ll get a better error, but for now, this shows me a limitation of my key.

Now I’ll increase the size of the key. I use the ALTER TABLE … ALTER COLUMN statement.
```
ALTER TABLE dbo.Document ALTER COLUMN DocumentKey NVARCHAR(7) NOT NULL

GO
```
Now, I’ll run my failed insert again:

As you can see, I can increase the size of the PK without rebuilding the table. Making it smaller is a post for another day.

SQLNewBlogger

This was a quick repro I set up to answer the question for myself and others. I thought I could do this and spent five minutes proving it.

The longest part of this post was the test data. You could do the same thing, maybe showing how this relates to a child table as well. In fact, start today and you might beat me to creating that post.
April 17, 2019
Upper Casing All the Data–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

There was a post recently where a user asked about needing to update all the data in all their tables to upper case. A somewhat strange requirement, though one I could see in some environments where they want to ensure all data matches in searches.

There isn’t a good way to do this, since you have disparate table names and column names. The quick way to do this one time is with a cursor, though I’d worry about performance here if this ran at any scale, and more often than once in a very rare time.

In any case, the way to get schema, table and column names is to query the INFORMATION_SCHEMA.COLUMNS view. I’d limit this to character columns of the non- and Unicode types. In the code below, I get that data into the cursor.
```
DECLARE updatecurs CURSOR FOR
SELECT
      c.TABLE_SCHEMA
    , c.TABLE_NAME
    , c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE
      c.DATA_TYPE IN ( 'varchar', 'char', 'nvarchar', 'nchar' );
DECLARE
     @schema VARCHAR(100)
   , @table VARCHAR(100)
   , @col VARCHAR(100)
   , @cmd VARCHAR(8000);
OPEN updatecurs;
FETCH NEXT FROM updatecurs
INTO
     @schema
   , @table
   , @col;
WHILE @@FETCH_STATUS = 0
BEGIN
     SELECT @cmd = 'update ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 
     SELECT @cmd = @cmd + ' set ' + @col + '= UPPER(' + @col + ')'
     SELECT @cmd
     FETCH NEXT FROM updatecurs
     INTO
         @schema
       , @table
       , @col;
END;
DEALLOCATE updatecurs;
```
The code then loops through the cursor and builds an update statement for each column. This could be enhanced to get a single statement for all columns in a table, but it’s a quick and dirty piece of code.

There is a SELECT @cmd statement in there that shows what command is executed. To make this work, that would be changed to EXEC(@cmd), but make sure the code is what you want.

Not pretty, but effective.

SQLNewBlogger

Quick, effective code. This has caveats, but works.

If you want to write about this, change the cursor to make one update per table.
March 4, 2019