Tidbits I Discovered Today…

I’ve figured out a couple of tidbits today that I wanted to share. First and foremost, I’ve discovered a (new to me) option in SSMS to convert tabs to spaces. I had previously seen the “Untabify Selected Lines” option (under Edit –> Advanced), but this only seemed to remove the tabs at the beginning of the line; it would not remove in-line tabs. I’ve now found another option that will remove the tabs throughout the selected code.

Here’s how you can do it:

Disclaimer: I’m using SSMS 2008 and have not confirmed this in other versions of SSMS.

From your toolbar, right-click in the empty gray space, or click on the drop-down arrow. Navigate to Customize:

Click on the Commands tab, then navigate down to Edit –> Convert Tabs to Spaces.
Note: if you’re one of those weirdos who like tabs instead of spaces, you can convert in the other (wrong) direction, too. ;)

Once you have found the option you want, click on it and drag it to your toolbar.


Cool, huh?

Another little thing I ran into today was a mismatch between nullable and non-nullable columns in related tables. I started to perform a manual audit before I said, “There has to be a faster way.” And you know what? There was. Here’s the little script I used:

SELECT c1.name AS [columnName]
    , c1.is_nullable AS [FactInternetSales_isNullable]
    , c2.is_nullable AS [FactResellerSales_isNullable]
/* replace AdventureWorksDW2008R2 with your database */
FROM AdventureWorksDW2008R2.sys.columns AS c1 
JOIN AdventureWorksDW2008R2.sys.columns AS c2
    ON c1.name = c2.name
WHERE c1.object_id = object_id('dbo.FactInternetSales') -- replace with your table
    AND c2.object_id = object_id('dbo.FactResellerSales') -- replace with your table
    AND c1.is_nullable <> c2.is_nullable; -- find discrepancies

This returned the following results:

columnName             FactInternetSales_isNullable FactResellerSales_isNullable
---------------------- ---------------------------- ----------------------------
RevisionNumber         0                            1
OrderQuantity          0                            1
UnitPrice              0                            1
ExtendedAmount         0                            1
UnitPriceDiscountPct   0                            1
DiscountAmount         0                            1
ProductStandardCost    0                            1
TotalProductCost       0                            1
SalesAmount            0                            1
TaxAmt                 0                            1
Freight                0                            1
 
(11 row(s) affected)

This script isn’t as polished as my usual, but it’s really just a quick-and-dirty way of checking for mismatches in NULL properties.

Okay, that’s all I have for now, my SQL friends. Oh, and if you know any other cool little tidbits, please feel free to share with me. :)

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , , . Bookmark the permalink.

7 Responses to Tidbits I Discovered Today…

  1. IL says:

    Hi Michelle,
    Would this picture http://i46.tinypic.com/2dj9w1f.png for Convert Tabs to Spaces and vice versa is of right direction to yours? :) Actually, I’ve shortened these just to images (Default style) and SSMS looks prettier now. Thanks!
    More serious questions:
    1) You’ve mentioned on twitter about converting tabs automatically, but do you mean Tools -> Options -> Text Editor -> Transact-SQL -> Tabs -> Insert spaces option?
    2) Do you know the option in SSMS to force it to save SQL files in Unicode-Codepage 1200? It is always set to ANSI page for new files by default.

  2. Love these kinds of posts! They’re more useful to me in my day to day work then 90% of the other posts in my news feeds.

    Let’s see, tidbits, tidbits… Okay, I’ve got one:

    I’ve recently started using INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES instead of sys.columns and sys.tables because in one view, we’ve got db, schema, table, column and type names without having to join with the other system tables.

  3. @IL – haha, I love it! :) As for the “Insert spaces option,” yes, I know it and love it. It’s one of the very first things I change any time I install SSMS. But no, I didn’t know about the option to force SSMS to save to Unicode-Codepage 1200! Thanks for that!

    @Michael – Thank you, sir! I often procrastinate on blogging because I think that something is “not good enough,” but lately I’ve decided to just put it all out there. That’s in no small part due to my recent investigation of Google Analytics stats. Some of my “little tips” posts get far more hits than any of my complex posts. Who woulda thunk it? :) As for INFORMATION_SCHEMA.*, I agree that they are more user-friendly than the catalog views. I just don’t end up using them that much because they’re missing object_id, which I typically need to join to other views (i.e. sys.partitions & sys.allocation_units).

  4. Hi Michelle

    You might want to check out Jes “@grrl_geek” Borland’s post on “making SSMS awesome” – http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/tips-and-tricks-to-make

    There’s a good few odds and ends in there that I find really useful.

  5. Victor Shahar says:

    Hi Michelle,

    I am using your defrag smart procedure and thank you for the v4.1 which is even better of course, i want to ask you something if it’s possible, can you please add also a ‘smart’ way of setting some ‘automatic’ FILLFACTOR for indexes that are discovered as being rebuild many times ?
    Also, I added a small thing inside, we are using replications and there is a database named ‘distribution’, I added it inside the exclude list.

    Thanks a lot again,
    Victor

  6. @Thomas – thanks!

    @Victor – Thank you for the suggestion. I’ve thought about that before, but there seemed to be a lot of issues with implementing something like that. Do you have any logic you could share that you use for a similar process?

  7. Fatherjack says:

    Hi Michelle, thanks for the post, 3 great bits of information.

    I like the way the new SSMS UI allows you to build a profile of your favourite settings. If you want to have a particular set up for working and another for when you are presenting then switching between the two has become very simple indeed. I detailed it on my blog – http://www.simple-talk.com/community/blogs/jonathanallen/archive/2012/06/22/110962.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>