Voice of the DBA

Tag: T-SQL

  • Changing the Data Type of a Primary Key–#SQLNewBlogger

    A client asked this question recently: How do I change my numeric PK to a character type?

    I decided to write a short blog on how to do this. This is the happy path, and not intended to cover all situations. I’ll write about some exceptions in a separate post.

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

    The Scenario

    A customer had a table where the PK was a number and wanted to change this to a character field. Here’s an example table with some data.

    CREATE TABLE Invoice
( InvoiceID   INT NOT NULL CONSTRAINT InvoicePK PRIMARY KEY
, InvoiceDate DATE
, CustomerID  INT);
GO
    INSERT dbo.Invoice
   (InvoiceID, InvoiceDate, CustomerID)
VALUES
   (1, '20230102', 3)
, (2, '20230103', 3)
, (3, '20230105', 4)
, (4, '20230106', 8)
, (5, '20230108', 11)
, (6, '20230109', 37);
GO
    

    Now, the situation was really that the customer was generating numbers for documents, but they realized their business had changed and they needed to add characters to the data.
    

    The Problem
    

    There are two things to think about here. First, what happens with the data? In this case, converting an integer to a character is easy and works. As long as the character field is long enough, this works fine. We first want to be aware of the data loss potential, though SQL Server won’t allow this.
    

    Second, we can’t change the data type because the PK is a constraint. If we try to change this type, we get an error:
    

    2023-10-23 11_01_39-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (69))_ - Microsoft SQL Server
    

    We really need to remove this constraint.
    

    If we have an outage window, this isn’t hard. If we don’t, then we have to be careful. In this case, I’ll assume we can pause the system and can make the changes without data changing in the table.
    

    The Solution
    

    The process to change the type is a few steps. I’ve shown them here.
    

      

    1. remove the PK constraint
      2. 

    2. change the column
      3. 

    3. add the PK constraint back
      4. 

    

    This code will do this. I run three statements to make the change, wrapped in a transaction, with error handling to rollback if one fails
    

    BEGIN TRAN
DECLARE @e INT = 0
ALTER TABLE dbo.Invoice DROP CONSTRAINT InvoicePK
IF @@ERROR<> 0 
  SELECT @e = 1
ALTER TABLE dbo.Invoice ALTER COLUMN InvoiceID VARCHAR(20) NOT NULL
IF @@ERROR<> 0 
  SELECT @e = 1
ALTER TABLE dbo.Invoice ADD CONSTRAINT InvoicePK PRIMARY KEY (InvoiceID)
IF @@ERROR<> 0 
  SELECT @e = 1
IF @e = 0
     COMMIT
ELSE 
     ROLLBACK
    

    This will change the data type and then reset the PK, as you can see below. With all my data intact.
    

    2023-10-23 11_10_38-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (69))_ - Microsoft SQL Server
    

    This is a simple scenario, and there are more considerations, but those are for another post.
    

    SQL New Blogger
    

    This post took me about 15 minutes to write. I took something I’d mocked up as a test for a client and then added that to this post. The code was barely changed, and I really renamed something and removed a few columns. Adding the text around this took most of the time.
    

    This is something that all of you could do to show that you have this skill. Changing a PK isn’t something you want to do, and it is unusual, but it does happen at times. I’ve had this happen before, and there are various other exceptions. Note I’ve added a note at the bottom to link this in to a series looking at other changes. What if the data isn’t compatible? what if the type is too short? What decisions would you make about the new PK, as int to bigint is easy, but what about char to date and possible collisions? What about identity values?
    

    You can do this and easily build 3-4 posts on this topic. Showcase your knowledge and you might create (and control) a fun discussion in an interview.
    

    
			
    
		
    
		
	
  • 
		
		
    
			
			
    T-SQL Tuesday #168–Using Window Functions
    
			
    tsqltuesdayI am the host for T-SQL Tuesday this month, and I hope that a lot of people like the topic. This idea actually came to me earlier this year when I happened to see someone ask about a T-SQL problem and get an answer using a Window function. This person mentioned they hadn’t used the window function before, and I wondered how many people haven’t even tried using the OVER() clause with a window function.
    

    I also saved the idea of window functions just in case I didn’t have a host, and I realized a few months ago November was blank. So I created an invitation for technical solutions using window functions, hopefully mature solutions you’ve use many times.
    

    If you want to host, contact me, and send me your blog link and I’ll get you scheduled. FYI, I’m looking for people in the second half of 2024, so it’s not an immediate need.
    

    Cleaning Up Window Functions
    

    I don’t write a ton of code, and I don’t have any really cool solutions, but I did want to highlight one thing from SQL Server 2022: the WINDOW clause.
    

    In the past we’ve often had code like this:
    

    WITH    HRCTE
           AS ( SELECT   hrorder = ROW_NUMBER() OVER ( PARTITION BY p.franchName ORDER BY HR DESC )
                       , p.nameFirst
                       , p.nameLast
                       , p.franchName
                       , p.HR
                FROM     dbo.Players p
              )
     SELECT  hrdenserank = DENSE_RANK() OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
           , hrrank = RANK() OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
           , playercound = COUNT(p.nameLast) OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
           , hrsum = sum(p.HR) OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
     FROM    HRCTE
     WHERE   HRCTE.hrorder <= 5;
    

    That’s not bad, but I’ve written a bunch of repeating code in the OVER() clauses. In SQL Server 2022, I can do something more like this (just the outer query):
    

        SELECT  hrdenserank = DENSE_RANK() OVER frname
           , hrrank = RANK() OVER frname
           , playercound = COUNT(p.nameLast) OVER frname
           , hrsum = sum(p.HR) OVER frname
     FROM    HRCTE
     WHERE   HRCTE.hrorder <= 5
     WINDOW frname AS ( PARTITION BY HRCTE.franchName ORDER BY HR DESC );
    

    That, to me, is a cool maturity of the Windowing function capability in T-SQL. I can alias a window and reuse it in my code. This also makes I can make a few different windows and easily see which one is used with which aggregate.
    

    
			
    
		
    
		
	
  • 
		
		
    
			
			
    T-SQL Tuesday #168 – Mature Window Functions
    
			
    tsqltuesdayIt’s time for T-SQL Tuesday and I’m hosting this month. I usually do one a year, just because I can and being responsible for a month keeps me engaged in the party.
    

    This month my invitation is on Window functions and is described below.
    

    If you’d like to host T-SQL Tuesday, let me know. I have lots of openings in 2024 and I’m looking for someone with a blog, some creativity, and an idea for a technical topic that you’d like to see other people write about.
    

    Mature Window Functions
    

    We’ve had window functions in SQL Server for a decade now, since SQL Server 2012.
    

    This month I’m asking you to write on how window functions have made your life easier. A few ideas for you:
    

      

    • What problems have you solved with a window function? Bonus points for lead/lag/first_value/last_value
      • 

    • Have you used the SQL Server 2022 enhancements in any queries?
      • 

    • How has performance improved for you with a window function
      • 

    • Draw a picture of a window with a spatial function – more extra points
      • 

    

    Give us some specifics, with real world problems. Obfuscate the data, at least if you have my name in your dev system, but help others understand how they might solve a complex aggregate using a Window function. The more specific examples, the more others might get help from one of the posts.
    

    The Rules
    

    Not many rules, but a few of them.
    

      

    • Post between 00:00:00 and 23:59:59 on 2023-11-14
      • 

    • Include the logo above in your post
      • 

    • Link that logo to this post
      • 

    • Leave me a trackback or comment on this post (double check if you have this automated)
      • 

    • Post you URL on Twitter, LinkedIn, etc. with the hashtag #tsql2sday
      • 

    • Have fun
      • 

    

    
			
    
		
    
		
	
  • 
		
		
    
			
			
    Creating a Self Referencing FK in a CREATE Statement–#SQLNewBlogger
    
			
    I had written about a FK in a CREATE TABLE statement recently, but the second half of this was that after the original question, the person asked if this would also work for a self-referencing FK. It does, and I wrote this to show that.
    

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

    Creating the FK
    

    The last post showed how to create the FK, but this works within a table as well. Let’s say I want to have an Employee table that links back one employee to another, who is their manager. That type of structure looks like this:
    

    CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
)
) ON [PRIMARY]
GO
    

    I can add a link that makes MgrID a FK reference by altering the code like this:
    

    CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
),
CONSTRAINT FK_MgrID_EmpID FOREIGN KEY (MgrID) REFERENCES dbo.Employee (EmpID)
) 
GO
    

    Easy.
    

    SQL New Blogger
    

    This is a post that took me less than 10 minutes to write. I changed the code from the previous post and wrote this right after the other one. The search and replace was the longest code part, and then the writing was quick, 5 minutes.
    

    This is a core skill for a DBA or developer. Write your own post to show how and why to build a self referencing FK for some scenario that you work with in your job, or in a project.