Tag: T-SQL

Recently I was talking with someone who had not named any of the primary keys (PKs) in their database. They used system generated names and when they ran comparisons, they got all sorts of drops and creates they didn’t expect. This post shows how easy it is to declare PKs with names, even with complexity.

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

The Scenario

I’m going to use a schema that I have for baseball data. It illustrates the point well, I think.

For my friend, imagine a table like this one:

CREATE TABLE [dbo].[salaries](
     [yearID] [int] NOT NULL,
     [teamID] [varchar](3) NOT NULL,
     [lgID] [varchar](2) NOT NULL,
     [playerID] [varchar](9) NOT NULL,
     [salary] [int] NULL,
PRIMARY KEY CLUSTERED 
(
     [yearID] ASC,
     [teamID] ASC,
     [lgID] ASC,
     [playerID] ASC
)
)

If I create this table, the system will decide what the PK is. In fact, after I run this, I can see in sys.objects that the name is some random code.

What’s worse is that if this were an FK and I were dropping these in some script, I’d have issues in other systems. This name is different on each system.

The better solution is to make a simple change. Before the Primary Key keyword, I can add Constraint and a name, like this:

CREATE TABLE [dbo].[salaries](
     [yearID] [int] NOT NULL,
     [teamID] [varchar](3) NOT NULL,
     [lgID] [varchar](2) NOT NULL,
     [playerID] [varchar](9) NOT NULL,
     [salary] [int] NULL,
CONSTRAINT salariesPK PRIMARY KEY CLUSTERED 
(
     [yearID] ASC,
     [teamID] ASC,
     [lgID] ASC,
     [playerID] ASC
)
)

If I run this, then I have a better named PK.

Be explicit in your work. It makes for better code and easier, repeatable, reliable deployments.

SQL New Blogger

This is a simple thing, a code smell, but one that wastes DBA and developer time. This post shows a simple thing you can do to have better code. This took me about 10 minutes and you could do something similar.

Write this and maybe someone asks you how to do this in an interview.

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:

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

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.

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.

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.

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.

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.

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

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

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

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.