Tag: T-SQL

  • Finding Identity Columns–#SQLNewBlogger

    I had to find a set of identity columns recently and through this would make a good blog post.

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

    Getting a List of Tables and Columns with Identity Properties

    Finding out which tables have an identity isn’t very easy, especially in SSMS’s Object Explorer. This property is somewhat hidden, which is annoying to me as I use them often. However, I get this is just a property.

    This is, however, stored in sys.columns.is_identity. This is set to 1 if the property is set, so filtering on this is good thing. If we join to sys.columns on object_id, we can get a list of table names.

    Here’s a short script to do this.

    SELECT
       o.name AS TableName
    , columns.name AS ColumnName
    , is_identity
    FROM
       sys.columns
       INNER JOIN sys.objects AS o
         ON o.object_id = columns.object_id
    WHERE sys.columns.is_identity = 1;

    However, there’s an easier way. There is a sys.identity_columns view which inherits from sys.columns and does the filtering for you. You can use this code instead.

    SELECT
      o.name AS TableName
    , columns.name AS ColumnName
    , is_identity
    FROM
      sys.identity_columns AS columns
      INNER JOIN sys.objects AS o
        ON o.object_id = columns.object_id
    

    SQL New Blogger

    I had to do this as a quick test for a client that wanted to do some checking of identity seeds. They asked for a list of tables to check, and I showed them how to get this quickly.

    This was literally about 2 minutes to set up and about 5 minutes to write this post. Something you could easily do.

  • T-SQL Tuesday #164: Code that makes you feel

    tsqltuesdayThe invitation this month is from Erik Darling, and it’s a neat one. I like this thought, asking us to find code that impressed us or made us feel something. I tend to look at this as positive, but it could be negative.

    In any case, I was on vacation from 1 Jul to 9 Jul, out of touch with the world and unwired. So I’m doing this as a quick post by the seat of my pants. I might have to come back and make a second post in the future.

    Changing the way I think about T-SQL

    I’m a decent T-SQL developer. Not amazing, or great even, but I am effective. I’ve learned a lot over the years and I’ve been able to get things done for my employers. I often look at other’s code and I try to improve how I view problem solving. I’ve learned a lot from Itzik and others over the years, though I have to admit that many of us solutions go over my head. I’m just not in those spaces where I need complex coded solutions very often.

    That being said, years ago I got an article from Jeff Moden on the tally table. I hadn’t used this, and was fascinated. I know Itzik had written about numbers tables early on, but it hadn’t caught my attention. However, in a follow-up, Jeff wrote about a splitter function, which would use the tally table to split strings efficiently. This is the function (credit to Jeff in his article):

    CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1)) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE! RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000... -- enough to cover VARCHAR(8000) WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ), cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT 1 UNION ALL SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter ), cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000) FROM cteStart s ) --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), Item = SUBSTRING(@pString, l.N1, l.L1) FROM cteLen l ;

    Now, note this is not the original code, but updated and improved code. I love that Jeff has maintained this.

    What I found great is how Jeff approached the problem. This is limited to 8k strings, but that’s for performance reasons. One could easily enhance this to be larger if needed. I also like the tally table is quickly generated from simple code that gives us 10,000 numbers.

    I also found the simplicity of the substring and the charindex to be something I think I not only understand, but could have written.

    Could have. Certainly didn’t and might not have. This is great code that’s been helpful to me over the years in places where I wanted to break up code. I’ve used this in a number of demos for clients and I’ve referred people to this over the years as well.

  • Adding a Computed Column–#SQLNewBlogger

    Recently I needed to add a computed column to a table and realized that I didn’t remember the syntax. This short post show how to do this.

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

    Adding the Column

    I had a table, OrderHeader, and wanted to add a new column, OrderedByDate. I can do this with a simple ALTER statement and an ADD. The inserting part is the computation. You use as AS clause with the computation instead of a datatype. For example, my code was:

    ALTER TABLE dbo.OrderHeader 
       ADD OrderedBy AS OrderDate;
    GO

    This added a copy of my OrderDate column with a new name. This is useful for zero downtime deployments in some cases, and in this case, I wanted to just have a copy. However, if I wanted some calculation, I could easily do that by specifying this as I would in a SELECT statement. For example, if I wanted the computed column to be a week later, I could do this:

    ALTER TABLE dbo.OrderHeader 
       ADD OrderedBy AS DATEADDD(DAY, 7, OrderDate);
    GO

    This would add a week to the original value and return that. I can likewise do any sort of string or numeric manipulation I want. A common one is adding or multiplying two columns together for a new value. For example, adding various charges for a total in an order.

    When you do this as a computed column that is not persisted, no space is taken in the actual table rows. If you persist this, then space is used.

    More information on Microsoft Learn.

    SQL New Blogger

    I realized that I needed to double check the syntax in the docs, and when I did, I took this as an opportunity to write a short blog post. I grabbed a link, wrote some code, and then spent 10 minues knocking out this post.

    If some employer does this a lot, they might search your blog to see if you can do this. A nice few posts on how to do this, what persisted does, how you index this, etc. Take a few minutes and start blogging on topics like this throughout your week.

  • The Growth of T-SQL

    I saw this tweet recently, where Richie Rump asked what has changed in T-SQL since the SQL Server 2012 version. A few people from Microsoft responded that there were changes in all versions, and while I think some versions have few changes, I decided to look.

    SQL Server 2012 introduced the window functions with the OVER() clause to SQL Server. This was a huge change in that many aggregate queries were much easier to write without needing complex GROUP BY lists and subqueries or unions to join together different data. While I’m not an expert by any means, I find lots of queries for reporting easier to write with the window functions, and I’ve grown to enjoy using these in code.

    Looking across other versions, I’ve seen these changes:

    SQL Server 2014

    • UTF-8 for Bulk insert
    • SELECT..INTO works in parallel
    • In-Memory OLTP language enhancements

    SQL Server 2016

    • temporal tables
    • JSON support
    • more In-Memory T-SQL changes
    • Security – DDM, RLS, AE T-SQL changes
    • R services

    SQL Server 2017

    • graph query
    • CONCAT_WS, TRANSLATE, TRIM, WITHIN GROUP
    • BULK INSERT options
    • Memory-optimized enhancements (CASE, TOP, JSON, computed columns
    • Python language services

    SQL Server 2019

    • Graph enhancements
    • UTF-8
    • Java and other language enhancements

    Some of these were to support other features, so perhaps these aren’t really T-SQL changes per se. If I look at PostgreSQL release notes, I see enhancements and changes, but relatively few new language changes. Certainly, there are some additions, but lots of improvements, which I think reflect the nature of a mature product. Not a lot of new things, but regular improvements and refinements to existing items.

    I’ve been working with SQL Server since 1991, and it feels like T-SQL has grown a lot in that time. Back then it felt like there were relatively few keywords and functions, requiring complex coding for tough problems. Now, with the way the language changed a lot in 2005, 2012, and 2016, it feels like we have a lot of tools at our disposal. We could always use more, and we got some neat ones in SQL Server 2022. I hope to see more useful changes in future versions to come.

    Steve Jones

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