Tag: T-SQL

  • Creating a “Real” Copy of a View: #SQLNewBlogger

    I saw a post where a developer was trying to read the Information Schema views to create a copy of a view as a “real” table, a user table. This posts shows an easy way to do this.

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

    The Scenario

    Imagine you have a view, for example, I have this one:

    CREATE   VIEW [dbo].[City] 
    AS
    SELECT TOP 10
      cn.CityNameID, cn.CityName
      FROM dbo.CityName AS cn
      WITH CHECK OPTION
    GO

    The structure of the underlying table is:

    2024-11_0122

    I have data being returned from this view as well, as you can see here:

    2024-11_0118

    If I want a copy of this view, I can certainly look in the information_schema views and see some data. Below, I have the column information for this view, which can be used to structure a create table statement.

    2024-11_0119

    However, there’s a better way.

    Quickly Copying a View

    The INTO clause is very valuable and helpful here. Many of us use this to copy a table or part of a table, but it work with views. Here is how I create an empty copy of my view.

    SELECT * 
      INTO dbo.MyCities
      FROM dbo.city
      WHERE 1 = 0;

    This will actually create a new table, as you can see in my Table list when I refresh after running the command.

    2024-11_0120

    The table looks like the structure of the view above. The PK isn’t set, but there isn’t necessarily a PK in a view as it can combine data from multiple tables. If I wanted data, I can run the same statement above without the WHERE clause. I’ve done that below and then selected data from the new table so show this.

    2024-11_0123

    If I needed to add some constraints or other items, I could easily add those with ALTER TABLE statements.

    SQL New Blogger

    This post required about 20 minutes for me to setup a demo, test, and then write with some screenshots. It wasn’t a hard post to write, but it shows a quick technique for doing something I’ve commonly seen from others.

    This is the type of post you can write that might get an interviewer interested in you and perhaps ask you a question. You could add some context as to why you did this, or why you like (or don’t like) this technique.

  • FIRST_VALUE vs. Min: #SQLNewBlogger

    I had mentioned some new T-SQL functions for SQL Server 2022 and a commenter asked about the difference between Min() and First_value. This post looks at a few cases.

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

    The Scenario

    Let’s set up some data and examine these functions.  First a table and some data:

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[MonthlySales](
         [TransactionDate] [date] NULL,
         [SalesAmount] [decimal](10, 2) NULL
    ) ON [PRIMARY]
    GO

    Some data for 2024:

    insert MonthlySales (transactiondate, SalesAmount)
      values
      ('2024-01-30', 100),
      ('2024-02-28', 200),
      ('2024-03-30', 300),
      ('2024-04-30', 400),
      ('2024-05-30', 500),
      ('2024-06-30', 600),
      ('2024-07-30', 700)

    Now, a query and some results. This query runs the same over() clause for first_value, min, and sum. The sum just shows totals.

    2024-11_0108

    It seems that first_value and min do the same thing. Let’s add one item to this. I’ll add two columns that remove the first_value and min from the total sales. Let’s assume we want to get rid of the first month’s sales for some reason (they lag).

    2024-11_0109

    OK, this looks good.

    The Difference

    Now, I’ll add some 2023 sales with this code:

    insert MonthlySales (transactiondate, SalesAmount)
      values
      ('2023-01-30', 10),
      ('2023-02-28', 20),
      ('2023-03-30', 5),
      ('2023-04-30', 40),
      ('2023-05-30', 50),
      ('2023-06-30', 60),
      ('2023-07-30', 70)

    When we re-run the query, we see different results for lines 3-7. This is because above, we had increasing sales in every line, when ordered by date. In 2023, we have a dip in sales, which happens, so the min value after Mar is 5, but the first_value is still 10 (from Jan).

    2024-11_0111The same sort of issue can come with last_value and max, as the ordering might not match the sorting of values.

    At first glance, these might seem like duplicate functions, but that really depends on the use cases you have for windowing functions and your data. You might often order by a numeric used in an aggregate, in which case they can be the same. However, there are plenty of cases where these work differently.

    Another quick example: in my baseball database we can see Barry Bonds HR counts by year, and the min differs from the first year in SFN.

    2024-11_0113

    SQL New Blogger

    This post took me about 15 minutes to setup and write. I’ve practiced telling a story, but I bet most of you could produce this in 30-45 minutes of work. Easy if you spend 1 hour a week on your career branding.

    Showcase your skills and set up a blog today.

  • New SQL Server 2022 Functions

    At the recent 2024 PASS Data Community Summit, I saw a lightning talk from Mikey Bronowski on the New T-SQL Functions in SQL Server 2022.  Before the talk, I made a joke with him that none of these were new because it’s 2024. They’d been out for 2 years.

    Mikey did a nice job, given that he only had 10 minutes, but it was enough to give attendees an idea of some new things they might investigate to use in their own code. If you missed the talk, or you don’t have access to the recordings, we have a series on SQL Server Central that covers these (Part 1, Part 2, Part 3) and helps you understand the new options. The list of language changes is also in the MS Docs under What’s New in the Language.

    I’m curious if any of you are using these new functions? There are a few time series ones, though I think GENERATE_SERIES is the one many of you might have used. Are any of you using DATE_BUCKET? That looks interesting, but I have to admit I haven’t played with it. STRING_SPLIT with the ordinal is my guess for the most popular function people use.

    There are also some new JSON and bit manipulation functions, which might be of use in some situations. GREATEST/LEAST are there, but I’ll have to say these functions haven’t come to mind as solutions in any of my queries or answers I write for questions. I do use the trim functions, though still only with spaces. I guess that some of you might find ways to incorporate trimming with other characters and possibly change old code.

    I do think that many of you can likely refactor code and make it cleaner with these functions, but you should test them extensively. As we’ve seen with some language changes, performance isn’t always better, and some changes (like FORMAT), can cause you resource issues. At the same time, if the code performs well, using cleaner code is a good way to perhaps update your codebase and gain some skill with new techniques.

    One warning. While I like refactoring code, make sure you do some testing, preferably automated, to be sure you aren’t introducing bugs or missing edge cases that your old code covers well. A cleaner codebase is nice, but having working code is more important.

    If you’re using any language features, leave a comment today.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Rename a Database: #SQLNewBlogger

    I had someone ask me how to rename a SQL Server database recently. They were doing some development work and wanted to rename databases to test an application. I thought I remembered, but in this post, I show I learned something.

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

    Using sp_rename

    I thought sp_rename would work, and sure enough, it did.

    2024-10_0232

    However, I need to object type. If I remove that parameter, it fails:

    2024-10_0233

    The command is looking for an object in the current database by default.

    Technically, I ought to do this to be explicit, naming the parameters.

    2024-10_0234

    I have a better way, however. Note: it’s not sp_renamedb, which is marked for deprecation.

    ALTER DATABASE

    I don’t know when this changed, or if, but you can use ALTER DATABASE to change the database name. There is a MODIFY NAME option for this command that works well. You can see this below.

    2024-10_0235

    This is very clear and seems like better DDL For this process, which can easily be captured as code without worrying about parameters or ordering or anything else. I’d recommend using this.

    SQL New Blogger

    This post took me about 10 minutes to write. Easy. I had done a few experiments and I had code ready (which went to the customer), so I didn’t spend time there. Just rewrote what I did and learned in a few minutes.

    You could do this, add to your blog, and maybe get an interviewer to ask you about this after they saw your post.