Voice of the DBA

Tag: SQLNewBlogger

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:

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.
December 11, 2024
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.

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.
December 4, 2024
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.

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

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.

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.

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.

November 13, 2024
Counting Groups with Window Functions: #SQLNewBlogger
I looked at row_number() in a previous post. Now I want to build on that and do some counting of rows with COUNT() and the OVER clause. I’ll show how this differs a bit from a normal aggregate.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also part of a series on Window Functions.

The Raw Data

Let’s use AdventureWorks as a sample database. In the Sales.SalesOrderHeader table there are lots of customer orders, each with a Customer ID. I’ll limit my results to these customers, which has a spread of orders:

WHERE soh.CustomerID IN (11011, 11015, 11019, 11012)

We’ll use this WHERE clause to help us decide

Counting Orders

If I wanted to count the number of orders each customer hasplaced, I can use this code:
```
SELECT
   soh.CustomerID

, COUNT (*) AS OrderCount

FROM Sales.SalesOrderHeader AS soh

WHERE
   soh.CustomerID IN ( 11011, 11015, 11019, 11012 )

GROUP BY soh.CustomerID;
```
I see these results:

We can see that each of these customers has different numbers of orders. If I want a simple summary, this is the best code. However, what if I want to add other columns?

Supposed I want to add not only the customer ID, but also the shipdate. I’m wondering when orders were shipped. If I use the code above, I’d need to add this in the column list, and the group by. If I do this, I get this code:
```
SELECT
   soh.CustomerID

, soh.ShipDate

, COUNT (*) AS OrderCount

FROM Sales.SalesOrderHeader AS soh

WHERE
   soh.CustomerID IN ( 11011, 11015, 11019, 11012 )

GROUP BY
   soh.CustomerID

, soh.ShipDate;
```
And this result:

The results are cut off, but they repeat with a 1 for each row. The front end can sum these, but it’s easy to make a mistake here. Especially if there are filters.

With an OVER() clause, my aggregate changes a bit. I would use this code, where I am paritioning, or essentially grouping, the data by the customer ID. I don’t have a group by, so I don’t need to add other fields in two places. Here is the code:
```
SELECT
   soh.CustomerID

, soh.ShipDate

, COUNT (*) OVER (PARTITION BY soh.CustomerID) AS OrderCount

FROM Sales.SalesOrderHeader AS soh

WHERE
   soh.CustomerID IN ( 11011, 11015, 11019, 11012 );
```
And the results.

I have the count repeated, but if I am showing this in the front end, I can hide that column in a table, but I can also access the total count from any row.

Differences with Group By

Let’s say I decide to add something else, like the account number and the SalesOrderNumber. If I do this in a GROUP BY, I need to add this to the column list and the group by, as shown here:
```
SELECT
   soh.CustomerID

, soh.AccountNumber

, soh.SalesOrderNumber

, soh.ShipDate

, COUNT (*) AS OrderCount

FROM Sales.SalesOrderHeader AS soh

WHERE
   soh.CustomerID IN ( 11011, 11015, 11019, 11012 )

GROUP BY
   soh.CustomerID

, soh.ShipDate

, soh.AccountNumber

, soh.SalesOrderNumber;

GO
```
The change the Window function is as shown:
```
SELECT
   soh.CustomerID

, soh.AccountNumber

, soh.SalesOrderNumber

, soh.ShipDate

, COUNT (*) OVER (PARTITION BY soh.CustomerID) AS OrderCount

FROM Sales.SalesOrderHeader AS soh

WHERE
   soh.CustomerID IN ( 11011, 11015, 11019, 11012 );
```
Maintenance is easy, and what’s more, I don’t have to worry about ordering my group by in different ways. If I do care about grouping, then I can alter the partitioning, but often I just want to add other columns without making the code more complex in a second place.

The results for the group by have the fields, but all 1s in the count again.

The window function has the counts for each customer.

Small things, but those points of maintenance can be annoying and they can cause problems. For complex data, the other fields might not group well, or the order of grouping might change what is shown.

There are more things to be concerned about here, but one of the big things might be a running total, where I count the orders over time. I’d like results like this:

My window function code is:
```
SELECT
   soh.CustomerID

, soh.AccountNumber

, soh.SalesOrderNumber

, soh.ShipDate

, COUNT (*) OVER (PARTITION BY soh.CustomerID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS OrderCount

FROM Sales.SalesOrderHeader AS soh

WHERE
   soh.CustomerID IN ( 11011, 11015, 11019, 11012 );
```
The regular code with group by is this:
```
select soh1.customerid, soh1.AccountNumber, soh1.SalesOrderNumber, soh1.ShipDate

, count(*) 'running_total'

from sales.salesorderheader soh1

inner join sales.salesorderheader soh2 on soh2.salesorderid <= soh1.salesorderid
                                    and soh2.customerid = soh1.customerid

group by soh1.customerid, soh1.AccountNumber, soh1.SalesOrderNumber, soh1.ShipDate

order by soh1.customerid, soh1.ShipDate
```
Not bad, but the big difference is in resources used. The statistics IO below shows the difference. Can you guess which is which?

Hint, the one using less reads is the OVER() code.

SQL New Blogger

Setting up a good scenario is a little tricky, and that took time. I messed with a few data sets that helped explain this to me, and you, in a way that made sense. I have a few other scenarios, which I will write about because an OVER() isn’t magic and it might not be the right choice.

Those will be other blogs.

This is a good way to showcase that you understand part of how this works. There’s a whole series to be written on different aspects of how the OVER() clause works. Spending 15-30 minutes each time you experiment helps showcase your knowledge and learning to employers.

That’s what employers need: experimenting and learning.
October 30, 2024