Voice of the DBA

Category: Blog

Code Snippet Cove on the #SQLPrompt Treasure Map

I’ve been away a bit in the last week with personal and family commitments. As a result, I feel a bit out of touch with work, and I have some catching up to do. However, when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it.

Code Snippet Cove

I love snippets. I first loved the idea in Visual Studio and Query Analyzer a long time ago, but SQL Prompt has taken this to a new level. I’ve used snippets quite often to make life easier and speed up development.

This part of the island talks about all the tokens that are available. There are all sorts, but some of my favorite are using $CURSOR$ to ensure the cursor gets dropped where I want it. This lets me immediately start typing.

I love $DATE$ and $USER$ for pre-populating comment sections of code, and there are plenty more metadata/environment type tokens that you can use, including the $PASTE$ for the clipboard.

While the tokens are great, I think that just the Snippets themselves, with custom placeholders, such as $userrole$, which I use for adding security to object creates, are incredibly handy. I’ve even used partial snippets for joins. For one job, we constantly needed to put together these tables: Product, ProductCategory, OrderHeader, OrderLine, and Address. We had a snippet that just contained these tables with the appropriate ON clauses for joins.

Explore snippets, and you might never want to work without SQL Prompt again.

In the next post, I’ll take at look at the rest of the map.

April 9, 2018
RIP, Robert Davis

A sad day for me.

A Memorial and Grief fund has been set up, if you’d like to contribute.

April 5, 2018
Date Fun with SQL Server

I saw a people discussing date formats on Twitter after PASS put out an advertisement for a webinar with a mm-dd-yyyy format. Plenty of people were annoyed and wanted to be sure that they realized that much of the world might misinterpret 3-2-2018 as Feb 3, as opposed to the US Mar 2 view.

That’s fair, and while some people noted that yyyy-mm-dd is the best format, I saw this tweet from Mladen Prajdic. It looks at the DATEFORMAT setting can cause issues.

Here are a few reproductions. On my US centric system, I ran this:

SET LANGUAGE ‘us_english’
GO
SET DATEFORMAT YMD
GO
DECLARE @dt DATETIME = ‘2016-03-02’;
SELECT yyyy = YEAR(@dt) ,
mm = MONTH(@dt) ,
dd = DAY(@dt);
GO
SET LANGUAGE ‘French’;
DECLARE @dt DATETIME = ‘2016-03-02’;
SELECT yyyy = YEAR(@dt) ,
mm = MONTH(@dt) ,
dd = DAY(@dt);
GO
SET LANGUAGE ‘French’;
DECLARE @dt DATETIME = ‘20160302’;
SELECT yyyy = YEAR(@dt) ,
mm = MONTH(@dt) ,
dd = DAY(@dt);
GO
SET LANGUAGE ‘us_english’
GO
SET DATEFORMAT YMD

My results:

Now, let’s change a few things. I’ll move to datetime2. If I do this, all three queries return 2016, 3, 2 for year, month, day. The same thing occurs with the DATE datatype.

If you examine Mladen’s test, you’ll see that without dashes things work fine. However, with dashes, the datetime datatype has issues. These can manifest themselves with both SET LANGUAGE and SET DATEFORMAT settings.

One thing to keep in mind is that SET LANGUAGE will automatically change the date formats to match that language, and you can override those with SET DATEFORMAT if needed.

The takeaway? First, use modern datatypes. Not worth using datetime and avoiding the “2” if there is potential for incorrect dates.

Second, avoid the dashes for now.

April 3, 2018
Learning is Hard–March Goals Update

This is the third update for my goals for 2018. This is about 3 weeks after the last update, and it’s too soon. Not in time, but because I haven’t done a great job.

This was a busy month. March and April are prime volleyball season, so I’ve had some longer weekends and time off to coach and watch my daughter play. Add to that some ski time and I haven’t had a lot of work or free time. As a result, my days at work are busy with catching up and ensuring I’m getting things done for work.

Learning has suffered, and like the Oct/Nov conference season, learning is suffering here.

My two appointments, Tues afternoon just after work and Friday afternoon, have been busy with other events, or with the need to do more work. As a result, not enough learning. Here’s an update in my two areas.

Extended Events

Very little here. This is Tuesday learning, and I’ve gotten myself jammed up for time every week. I’m considering moving this to another day, but not sure which one is better. I’m tempting to try and get through ski season and see if Tues then lightens a bit at work.

I did watch more of Jonathan Kehayias’ Pluralsight course while on the bike, but not practical work.

Python

I spent a couple of hours working through some basics on lists and dictionaries. I realized that I haven’t been doing this enough and I needed a refresher. That was good, and it helped me build a few Questions of the Day, as well as get some practice.

The one upside is that I was struggling with a puzzle in T-SQL and decided to try Python. I solved it in about 10 minutes, and my solution worked the first time. I was proud of that, and spent another 10-15 minutes solving another problem. So, I know a few things.

No real formal learning, but I feel like I should race through a Pluralsight course and try to be sure I have a good grounding. Plans for next month as I’m traveling a bit.

Too Busy

A few things with family and then volleyball affected me more than I thought, but that’s part of a busy life and it challenges my focus on learning. I’m also letting myself get distracted with the shiny things, reading a bit on various other technologies, which is part of my job. However, CosmosDB and other items aren’t what I planned for this year.

I’m putting pressure on myself to get through some XE and Python learning and then move into other areas. We’ll see if I can pick things up next month.

March 29, 2018