Voice of the DBA

Tag: SQLNewBlogger

DevOps Basics – Ignoring Files in Git

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

One of the things you’ll run into at times is the need to keep some scratch files, or extra files, in your Git repository, but not track them. One common type of file for me when working with SQL is a .zip file. I may zip up code to share or copy to a friend (without giving repo access).

Ignoring files is easy in Git. We just add a .gitignore file. This is a list of files that the git repository will not track or show in status. Essentially, we see them in our file system, but git doesn’t.

Creating .gitignore

To create a .gitignore file, the easiest method for me is to just create a text file. I can do it like this:

This gives me a new file. Certainly VS Code, Sublime, etc. will make this easy as well. The format is simple, with a list of files and/or patterns to ignore. For example, I’ve got a .zip file in my repo.

I don’t want to see this, but I do right now:

If I want to ignore this file, I’ll enter this in my .gitignore file:

GitTests.zip

If I want to ignore all zips, I’ll do this:

This is a part of my repo, so I need to commit it.

Now my status is clean.

Generated .gitignore

Some applications will generate a .gitignore. For example, my C# project gets this file from Visual Studio.

That’s a subset of files that are often in a VS project, but we don’t want to track in a VCS. Images, archives, executables, etc.

You can customize this as you need, and it’s easy to just edit the text file and commit the changes.

Hopefully this helps you understand how to best work with git and keep your repo clean. This also means your git add –all is easy to use without adding unnecessary files.

October 30, 2017
Basic FORMATting– #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw the addition of FORMAT() to the T-SQL language, but didn’t play with it much. Recently it appeared in some code, and decided to experiment a bit. I had assumed this was mainly for dates, but it’s a general format/culture function that handles numbers as well.

On the doc’s page, there are the basic description of the parameters, which are NVARCHAR(), so passing in VARCHAR() causes an implicit conversion. It shouldn’t be much, but there are already performance penalties (see Aaron Bertrand’s piece), so don’t add to the overhead.

One good thing to note is that if you pass in invalid formats or cultures, a NULL is returned. Since the format and culture strings aren’t completely intuitive, this might be a source of issues in your code.

This is a neat function, relying on CLR formatting rules. That means I can do fun things like:
```
DECLARE @i int = 5000;

SELECT FORMAT(@i, N'USD$#');
```
Which returns:
```
USD$5000
```
Or even:
```
DECLARE @i INT = 5000
;
SELECT  FORMAT(@i, N'# dahlahs')
;
GO
```
Which gives me:
```
5000 dahlahs
```
There are lots of formats, and certainly lots of nuances to numeric formatting strings. It’s worth reading up if you plan to use this, but again, beware of performance. I’d avoid using this if the data size is large, maybe more than a few hundred rows.

After all, the database server is a shared resource, and using this CPU to handle simple formatting may not be the best use of your system.
October 25, 2017
Rebooting Ubuntu Linux from the Command Line–#SQLNewBlogger

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

Since SQL Server 2017 is now out, and Linux is an option, I thought I’d write a bit more about little Linux things that admins might want to know.

After installing an update, I neede to reboot my Ubuntu install. Apparently there was a dialog, but it was hidden. Unlike on Windows, I didn’t see an easy way to click a button and restart. However, since I had a terminal shell open, I was sure there was an easy way to do this.

I could have run a quick search, but I fell back to the old standby:

man shutdown

This showed that there is a shutdown, as on Windows, with the same –r parameter. This needs sudo to run, and by default it reboots after a minute. The command I ran was:

shutdown -r

Learned something, which I should have known. I almost typed shutdown, but I decided to double check.

Note: I realized later that there is a small menu in the upper right. I had the VM in a window and didn’t notice the icon. Clicking the gear gives a menu.

This lets you then choose shutdown or restart.

October 18, 2017
WAITFOR isn’t a function–#SQLNewBlogger

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

I needed to delay the execution of some code the other day. This was a test that was trying to simulate a few things happening, and one batch needed a random delay. I started typing and got something I didn’t expect.

Where’s the TIME or DELAY? SQL Prompt didn’t like this, and since I expect SQL Prompt to save me from writing bad code, I knew I’d done something wrong. I backed up and removed the parenthesis and ‘R’ and then typed again, this time adding a space.

That works. Now I can change this test to real code.

WAITFOR is designed to delay execution, but it’s not a function. No parenthesis. Instead, it’s a control of flow statement, like CASE, so it just takes other expressions after.

SQLNewBlogger

This was one of those commands I haven’t used in a long time, but is a handy one. Hopefully this 5 minute writeup will help me remember this in the future.

October 11, 2017