Voice of the DBA

Tag: syndicated

  • T-SQL Tuesday #101–Tools

    tsqltuesdayThis month’s T-SQL Tuesday is brought to you by Jens Vestergaard. The invitation for #101 is for essential SQL Server tools. Since I work for a tool vendor, you can guess that I’m going to be shaded that way.

    This is the monthly blog party on the second Tuesday of the month. You can participate by writing your blog, linking to the invite and leaving a comment there (or a pingback). This is the creation of Adam Machanic, and if you want to host, drop him a note.

    My Essential SQL Server Tools

    There are really three main tools I’d say that are needed for SQL Server work. At least, these are the ones I need.

    1. SSMS – It’s free, and it’s the main tool for connecting to SQL Server. You might like VS Code or some other editor, especially on other platforms, but really SSMS is required for most people.
    2. SQL Prompt – It’s expensive for some, but after using this for years, I think the the native intellisense hurts more than helps. Just formatting alone make this critical for me. I’m moving through quicksand, mixed with molasses, when I don’t have Prompt installed.
    3. Git – Another free one, but an important one. As we write code, SQL Server will only keep the latest version. Much of what we do means that we need to understand where we came from with code. We do have integrations at Redgate for SQL code, but no matter what, just having a record of work is important, especially with multiple people. I love git, but if you want SVN or something else, use that. Just use something.

    These are the main ones. Making these the main ones doesn’t mean that others aren’t important, but it just means these are the top of the list. After these, you can add in others. Here are the ones that I think are important for getting things done in a modern, multi-environment world.

    SQL Search – From Redgate, but useful for finding things in a large environment. Way better than browsing.

    Plan Explorer – I think SQL Monitor has a nice plan tool, but for those of you writing code and wanting to test it, this is way better than the native plan viewer.

    DLM Dashboard – Free, and let’s you see and track those changes in production. Find out when code is being released and you aren’t aware of it.

    Sublime Text – I tend to do some work in other languages, and in text, and having a quick, easy to use editor is nice. I think Sublime is great and worth the small fee.

    Password Safe – Having long, secure passwords is important. I don’t try to remember them anymore, and I do want separate ones for every site. I like this open source tool, and have ports on my mobile devices.

    Dropbox – Critical for me, since I use files on multiple machines. I keep copies of my presentations, password safe files, and more in sync across devices with Dropbox. Plus I can scan in docs from my phone if I need to send and share them.

    dbatools – This is an amazing project. I hated working with the sqlps module and really avoided PoSh for many things before dbatools. Give this module a try and you may learn to love PowerShell.

    SQLServerCentral – I’m biased, for sure. However, everyone could use a few things that we provide. A community, where you can get inspired and learn things (daily articles and editorial). A way to ask questions and get answers (our forums), and a place to keep scripts that are helpful for you (Scripts and Briefcase). These are important to rounding out your career, beyond just your current job.

    There are plenty more choices, and certainly lots of tools within SQL Server. You should know about Extended Events, or Query Store (or Open Query Store) and more. Learn to use the resources you have, practice and test with them, and if you need a paid tool, don’t be afraid of asking. If the tool provides an ROI, it’s worth it. If it doesn’t, tell the vendor why.

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

  • RIP, Robert Davis

    A sad day for me.

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

  • 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:

    2018-03-27 10_56_20-SQLQuery10.sql - (local)_SQL2014.master (PLATO_Steve (53))_ - Microsoft SQL Serv

    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.