Tag: T-SQL

  • 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.

  • Adding a Foreign Key in the CREATE TABLE statement–#SQLNewBlogger

    I had someone ask this question recently and had to double check the syntax myself, so I thought this would make a nice SQL New Blogger post.

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

    Defining a Foreign Key

    Most people define a foreign key like this:

    ALTER TABLE [dbo].[OrderLine]  WITH CHECK ADD  CONSTRAINT [FK_OrderLine_Order] FOREIGN KEY([OrderID])
    REFERENCES [dbo].[Order] ([OrderID])
    GO

    This assumes I’ve added a table called dbo.Order with a PK of OrderID.

    However, I can do this in the CREATE TABLE statement, like shown below. I add a new section after a column with the CONSTRAINT keyword. Then I name the constraint, which is always a good practice. I can then add the FK keyword, the column and the references that connects this child column to the parent column.

    CREATE TABLE dbo.OrderLine
    ( OrderLineID INT NOT NULL CONSTRAINT OrderLinePK PRIMARY KEY
    , OrderID INT
    , Qty INT
    , Price NUMERIC(10,2)
    , CONSTRAINT FK_OrderLine_Order FOREIGN KEY (OrderID) REFERENCES dbo.[Order](OrderID)
    )
    GO

    Easy to do and this keeps my code clean.

    Note that if I script this out in SSMS, I’ll get this:

    CREATE TABLE [dbo].[OrderLine](
    [OrderLineID] [int] NOT NULL,
    [OrderID] [int] NULL,
    [Qty] [int] NULL,
    [Price] [numeric](10, 2) NULL,
    CONSTRAINT [OrderLinePK] PRIMARY KEY CLUSTERED
    (
    [OrderLineID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[OrderLine]  WITH CHECK ADD  CONSTRAINT [FK_OrderLine_Order] FOREIGN KEY([OrderID])
    REFERENCES [dbo].[Order] ([OrderID])
    GO

    Nothing wrong with that, but knowing both syntaxes is a good idea. Plus, if you know this is a child column, define it right away.

    SQL New Blogger

    This is a post that took me about 15 minutes to write. I had to create and drop the tables a few times and verify I had the syntax correct, and then explain and format things.

    This is a core skill for a DBA or developer. You ought to know how to define a FK and use them where appropriate. Write your own post to show how to build a FK for some scenario that you work with in your job, or in a project.

  • SQL Prompt Quick Access to Helper Code

    We’ve been doing some events as part of the Redgate Roadshow, and at one of the events, we had a customer ask about something that we demo’d. This post looks at quick access to snippets.

    This is part of a series on SQL Prompt that I’ve written. If you’ve never tried SQL Prompt, it’s amazing. Download an eval today.

    The Command Palette from the Query Editor

    When typing a query in SSMS, I can hit the CTRL button and get the palette on the side. Here’s a basic query:

    2023-09-04 04_39_35-SQLQuery3.sql - ..dlm_1_dev (WAY0UTWESTHP_way0u (89))_ - Microsoft SQL Server Ma_thumb[1]

    I might want to add the begin and end around my code in the proc. If I highlight some code and hit CTLR, I see this:

    2023-09-04 04_40_25-SQLQuery3.sql - ..dlm_1_dev (WAY0UTWESTHP_way0u (89))_ - Microsoft SQL Server Ma_thumb[1]

    On the left, I have opened the palette where I can down arrow to pick something relevant or search for a command that helps me. As you can see, the first one is the very common “add begin end” to the code. If I pick this, it will surround my code with begin end, as seen here:

    2023-09-04 04_43_07-SQLQuery3.sql - ..dlm_1_dev (WAY0UTWESTHP_way0u (89))_ - Microsoft SQL Server Ma_thumb[1]

    I can also get other helper code, like the CTE outline, as you can see below:

    2023-09-25 15_16_58-SQLQuery14.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (75))_ - Microsoft SQL Serve

    SQL Prompt is amazing, as this video shows. Download an eval today.