A Second Job, a Trip, and a Change of Pace

Posted on May 11, 2023 by way0utwest

Some of you know that I work a second job as a volleyball coach for competitive clubs in the US. I started doing this as my youngest kid grew up, and I was looking for a hobby. I ended up coaching at the club where she competed and kept going when she left for university. Eight years later I still coach kids from Nov-May with my wife.

This weekend I’m traveling with my team of 17-year-oldsto compete in Las Vegas on Fri-Sun. A couple days off from work, to do more work, but it’s fun. I love kids and working with them, seeing them grow and change, succeed and fail.

I also like getting away from technology and watching the competitive nature of sports.

I’ll be back late Sunday, with a morning flight Monday for work. Hopefully I won’t be too tired from the weekend.

Posted in Blog | Tagged , , | Comments Off on A Second Job, a Trip, and a Change of Pace

What Backups Are In This File?–#SQLNewBlogger

Posted on May 10, 2023 by way0utwest

I had a question on multiple backups in a file and had to check my syntax. This post shows how to see which backups are in a file.

Note: Don’t do this. Put backups in separate files.

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

Setup

I have a sandbox database. I made a backup of this.

BACKUP DATABASE [sandbox] TO  DISK = N'D:\SQLBackup\sandbox.bak' 
   WITH NOFORMAT, INIT,  
   NAME = N'sandbox-Full Database Backup', 
   SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Note I used INIT, which will ensure this is the only backup in this file.

I then changed something, in this case, I made a new table (I was testing things for Rich).

CREATE TABLE testforrich (myid INT)
GO
INSERT dbo.testforrich (myid) 
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM sys.columns AS c
GO

I then ran another backup. However, this time I wanted to append to the existing file.

BACKUP DATABASE [sandbox] TO  DISK = N'D:\SQLBackup\sandbox.bak'
  WITH NOFORMAT, NOINIT,  
  NAME = N'sandbox-Full Database Backup', 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

The NOINIT keyword is in here, which appends the backup to the same file. In essence, sandbox.bak will then contain two different backups in one file. For this test, I then made another change and another backup.

TRUNCATE TABLE testforrich
GO
BACKUP DATABASE [sandbox] TO  DISK = N'D:\SQLBackup\sandbox.bak'
  WITH NOFORMAT, NOINIT,  
  NAME = N'sandbox-Full Database Backup', 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


Now I have three backups in the file.


Checking Contents


If I were to click the restore item in SSMS and pick the file, I see this:


2023-05-03 10_15_53-Restore Database - sandbox


Note that the position is listed as “3”, which means this is restoring the newest (most recent) backup by default. I don’t seem to be able to edit this, though if I click timeline and change the time, I can get a different backup. I see different backups in there:


2023-05-03 10_30_16-Backup Timeline_ sandbox


However, when are those backups? This timeline isn’t great.


I can use RESTORE HEADERONLY. The command I ran is:


RESTORE HEADERONLY FROM DISK = 'd:\sqlbackup\sandbox.bak'
GO


This gives me all three backups, which are shown as different positions in the file.


2023-05-03 10_31_38-SQLQuery7.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (72))_ - Microsoft SQL Server


From here, I could perform a restore with a different backup if I needed to.


SQL New Blogger


This was a quick post that I wrote after I spent 5 minutes creating a test for something. I grabbed my code, took a few screen shots, and it took about 10 minutes to assemble this.


Easy for you, and this shows a potential interviewer or manager that you can dig into a small issue, learn, and solve it. Try it for yourself and write a blog post.

							

	
			

													
						Posted in Blog					
					|
				
								
					Tagged , , 				
				|
				
				3 Comments

							

		


		
	

	
			

			
Encouraging Innovation


			

				Posted on May 10, 2023 by way0utwest			


					

				
I was working with a client recently and one of the managers said they wanted to encourage innovation inside their organization. That sounded good, but as I thought about it, I wasn’t sure what this means, or if it’s really something that matters. Let’s take a few examples and think about this in a modern organization that builds and operates software.


If I am a developer, I get a series of requirements or tickets where I have to write code. Perhaps I need to take inputs from a user, query a database, and produce a result. I might write code in C# 7 instead of C#6. Maybe I’d use a lambda instead of creating a new function. Perhaps I decide to ensure I use the stored procedure type with named parameters rather than submitting SQL as a batch. Is that innovative? It might be a better way to write code and improve the quality (or security or maintainability) of the code, but is it innovation?


Another example. I’m a DBA and I need to run scripts against production. All the DBAs use SSMS, but I decide to change. I store the scripts in a folder and use Flyway to execute them against production with an automated system like Octopus Deploy, which manages the security. Is that innovative or just adopting a more DevOps-y practice?


Jason Fried says innovation is overrated, which I think is true. At least when that’s an aim or we expect to build something radically different. I know there are sometimes we might come up with something new that our organization hasn’t done, but most of the time work is work. It’s tedious mundane, and it can be very repetitive. That’s why the urge to do and try something new pervades most development organizations.


I do think that you can regularly innovate in your workplace. You might not create something that’s never been done anywhere in the world, but you might get people in your org to try something new. To adopt a new technique or habit, to create higher quality code with a small change. That’s the type of innovation that I like to see and foster. That’s what DevOps encourages, experimentation and learning. It’s also what I think my examples above show. It’s innovation in this particular space, which hopefully makes work more interesting and enjoyable.


Steve Jones


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

							

	
			

													
						Posted in Editorial					
					|
				
								
					Tagged 				
				|
				
				Comments Off on Encouraging Innovation

							

		


		
	

	
			

			
T-SQL Tuesday #162–Using AI with Data Science


			

				Posted on May 9, 2023 by way0utwest			


					

				
This month is a timely topic, with Tomaz Kastrun hosting. I was lucky to meet Tomaz before the pandemic, and we had a great time at the SQL Saturday as well as at Lake Bled the next day.


2023-05-02 09_58_26-Photo - Google Photos


Tomaz does a lot of data science with R, Python, and a lot of data. He has some great blogs and does a neat advent series every December. This month he asks us about Data Science in the era of ChatGPT, with two prompts:


    

  • Where and what have you used it [ChatGPT] for?
    • 

  • Have you considered responsible usage of Chat GPT?
    • 



I’ll tackle these below. First my experiments with the AI, and second, Ethics.


















This is part of a series of experiments with the ChatGPT and other AI systems.


















Getting a Visualization


One of Tomaz’ ideas was a visualization. I have some data, so I used this prompt:


2023-05-02 10_16_40-


A very poor prompt on my part, but the AI tried. I asked other questions and kept context of the process.


2023-05-02 10_17_10-Create KW Visualization.


Not sure why the image didn’t appear, but there was an explanation, so I asked for code.


2023-05-02 10_17_59-Create KW Visualization.


I got some code, and decided to restart this experiment. I started a new chat and recorded my efforts. That result  is on YouTube and (hopefully) embedded below.




It wasn’t perfect, but I could see this being the way software developers end up using ChatGPT.


Ethics in AI


There are a lot of things to think about here, but let me focus in on two things. First, is it ethical to get paid for a job if you are asking ChatGPT for help? Second, is it ethical for ChatGPT to synthesize code samples from SQL Server Central, Stack Overflow, etc., that others have written, and give it to you to use in your code.


The first one is easy. I say yes, since this is what many of us do with colleagues. We ask them for help, they give us code or examples, and we use them in our work that we commit, turn in, etc. We don’t write all code by ourselves, so I see ChatGPT as asking someone else or posting on a forum.


The second one is tricky. If I see code on SQL Server Central, then I know who wrote it, or who posted it. If I use that, I need to have rights to do so, or give someone credit. For example, I can post this code:


–============================================================================= — Create and populate a Tally table –============================================================================= –===== Conditionally drop IF OBJECT_ID(‘dbo.Tally’) IS NOT NULL DROP TABLE dbo.Tally –===== Create and populate the Tally table on the fly SELECT TOP 11000 –equates to more than 30 years of dates IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 –===== Add a Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100 –===== Let the public use it GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC




In doing so, I would give credit to Jeff Moden for writing this code. If I used this inside an organization, I should ask Jeff for permission.


If I get this from ChatGPT, it is providing sources? Potentially the AI read a lot of code and then synthesized something that works the same, but perhaps it’s also copied the most popular code it’s seen in many training sets of data. Hard to know.


It’s also hard to know what rights we want to give to the AI, it’s programmers, it’s corporate (or personal) owner, etc. How do we deal with rights here? I’m not sure. This is a an area where I don’t even know what I want.


What do you think?

							

	
			

													
						Posted in Blog					
					|
				
								
					Tagged , , , 				
				|
				
				Comments Off on T-SQL Tuesday #162–Using AI with Data Science