Voice of the DBA

Tag: syndicated

  • Altering a column to add NOT NULL

    I had to do this recently and needed to look up the syntax as I couldn’t remember it. I don’t do this often, but I was working on a particular item and wanted to test this.

    I had a table, something like this:

    CREATE TABLE SimpleTable
( MyID INT NOT NULL PRIMARY KEY
, MyChar VARCHAR(200)
, [Status] TINYINT
);

    I decide that the [Status] column shouldn’t be NULL and decide to ensure it’s always got a value. I don’t want to rebuild the table, I want to ALTER it. I use this syntax:

    ALTER TABLE dbo.SimpleTable
 ALTER COLUMN Status TINYINT NOT NULL;

    That’s it. I do this and the column now no longer accepts NULL values.

    Of course, if there’s data in there, I need to set those values to something that isn’t NULL before I can do this, but that’s a separate topic.

    By the way, I remembered most of this syntax. What I forgot is that I need to include the datatype in there to make this change. There are a number of restrictions with this command, so be sure that you read carefully before you try to make changes. If you do this in SSMS, you might end up with a script for a new table, which may not be what you want to occur in a production environment.

    Reference

    ALTER TABLE – https://msdn.microsoft.com/en-us/library/ms190273.aspx

  • The May Blogger Challenge–Continuing On

    Last month I participated in Ed Leighton-Dick’s April Challenge. My four posts were tagged the April Challenge, and this month I plan to continue, and hopefully get a few more people to up their blogging.

    Building on April

    To make the next step in starting to blog regularly, I’d like to see people in May continue with their weekly blogging, but do one more thing: write one additional post.

    This additional post I’d like to be one of the T-SQL Tuesday topics. Pick one from the list of past choices and write a post. Feel free to title it with the T-SQL Tuesday number and post it. It really doesn’t matter when you wrote it. The whole idea is to just write.

    Getting Started in May

    If you didn’t participate, or you struggled to get 4 posts, get started in May. Write one post per week and publish it. You can tag it the #SQLNewBlogger or you can just send a note to myself or Ed. We’d like to know what you’re doing.

    If you struggle to write, do one of these two things:

    • Make a note, send yourself an email, whatever, when you close a ticket or complete a section of code.
    • Use one of my topics

    Either one of these things work. I’d prefer you do the first one and just make a note when you do something. I’m sure you do something at work. Maybe it’s check logs, maybe it’s solve a problem with an instance, maybe it’s write a report. Anytime you do something, make a note.

    Then write about that note in a weekly session.

    If you are still struggling for topics, here are a few that I’d like to see you tackle in May:

    • Where do you use a unique index?
    • Generate a script after adding a column in the SSMS table designer
    • Query for the list of recent full backups in msdb for a single database
    • Show how to join two tables.

    Pick one and write. Set aside a 15 minute session a week to write, and work on a short, < 1 page, post.

    Good luck, and hope to see you blogging in May.

  • Windows Phone – What Should Microsoft Do?

    I’ve been intrigued by Windows Phone as a platform. I’ve never owned one, and while I considered getting one, the dearth of apps led me to an Android instead. I really tried to get down to a list of core apps that I needed, which was 12 or 15 at the time. However 3 or 4 weren’t in the Windows App Store and I decided against Windows phone.

    My daughter really wanted a Windows Phone and got one a few years ago. She loved it at first and almost made it a year before she got annoyed by missing apps and moved to Android (which she loves).

    I have been hoping that WP would continue to improve, not only in it’s internal quality, but also with more apps and users. I read a piece about the issues with Windows Phone (The End of Windows Phone?) and while I’m not sure I think it is dying, the author brings up some good points that I worry about.

    Apps

    Apps are critical. I know plenty of people think that a couple calculator apps, and a few password apps, etc. are good enough. People can choose from a few. However that’s missing the point.

    People like diversity. They like choice. They like looking for apps. There are a few places I get news from that publish new and cool apps, or apps that are free for a limited time every week.

    Every week I get a list of 5 or 10 apps, some new, some old. Periodically I’ll download one. Sometimes I’ll buy a paid version. The point is, I’m interacting with the app store and my phone, changing it to my liking on a regular basis.

    My phone is something I’m always tweaking a bit, like my early PCs. However Microsoft seems to almost want to treat the phone like a network router. I set it up and forget about it, just using it. That’s not what’s going to grow the store.

    I checked this week and there isn’t a native Starbucks app. That particular app has become somewhat a strong desire for me, so no Windows Phone for me. There are others, and I think that’s an issue.

    As I’ve noted to many people before, I think the big failure from MS here is not just building more apps. Certainly it takes third parties to provide some diversity, but spending $10mm on developers as salary to build apps, and not as incentives, would be better. Paying interns to work with Starbucks and MLB and many other places to build native apps, would be a wise move.

    Windows Everywhere

    I had a Windows 6 phone. I was excited about the idea of Windows everywhere. Over time I realize that model doesn’t work. The whole way I work on a desktop or laptop, with it’s semi-designed interfaced (it’s more evolved) isn’t how I want to work on a tablet or phone. It just isn’t. The idea of Windows everywhere, with apps running on all platforms without being rebuilt doesn’t make sense. The buttons, flows, controls, actions, etc. on a desktop aren’t practical elsewhere.

    The reverse is also true. The way I use a tablet doesn’t work on a desktop. I’d have hoped Windows 8 taught you that.

    I really want my data everywhere, not my apps. Stop fighting this. Make native Office apps for iOS, Android, WP8, separate from those on Windows. Don’t try to force things to work well and be highly engineered. We saw that fail with the CORBA architectures, with OS/2, etc. You’re better off worrying about data, not apps.

    The Future

    Windows 10 doesn’t really excite me on the desktop, except for one thing: Copy/paste in the command prompt. That’s it. What else do I need Windows to do? A better Start menu? Please, I spend seconds a day in the start menu. Even on a laptop, it’s very few interactions. Perhaps more on a tablet, but to get me to move to a Windows Phone or tablet, I need something compelling over the iPad.  What will Windows Phone 10 do?

    Cortana is interesting, but I still hesitate to speak to my devices. I’m in too many public places and it’s not an interaction I’m looking for. I’ve seen some people that use speech to text heavily, and successfully, even in louder environments. However it still feels silly, and an interruption on life for me. Perhaps if Cortana does more reminders, prediction, and analysis of my interactions on the device, I’ll try it.

    The movement towards limiting switching between apps is good, especially for messaging. The quick respond on iOS hasn’t caught on for me, for some reason, and switching apps is annoying. The link to my desktop makes some sense, but if that’s going to happen, then one thing better be ready:

    Undo.

    I make mistakes all the time. I will continue to do so, and any actions I take better have an easy, quick, and intuitive way to "undo" things.

    I don’t have great hopes for Windows Phone unless the app situation is resolved. If it is, then perhaps there’s a low enough bar to just switch to try a new OS on the phones. However I’m not sure any other features you could build into a phone would overcome the app deficiency.

  • April Blogger Challenge 3 – Filtered Indexes

    The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

    Why Filtered Indexes

    I haven’t used filtered indexes much in my career as they didn’t exist while I was a full time SQL developer and DBA. They appeared in SQL Server 2008, and are a very useful feature in certain circumstances.

    Imagine I have a simple table:

    CREATE TABLE Users ( MyID int IDENTITY(1, 1) , firstname varchar(250) , lastname varchar(250) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (firstname, lastname) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) ); GO

    I want to add some indexes that look for specific fields since those are times I am limiting searching in the table. For example, suppose that gender is an optional entry by users. I find that I often have NULL in this column, so I create a filtered index.

    I’ll do that as follows:

    CREATE INDEX Users_Gender_Filtered 
   ON Users (gender) 
   WHERE gender IS NOT NULL;

    This is the same type of index statement we normally use, but we’ve added a WHERE clause to the statement. This means that only those rows with a non-NULL value in the gender column will be included in this index.

    Why Use Filtered Indexes?

    There are some reasons given in the BOL article below, but the main reason to do this is performance. I could easily add an index using gender that isn’t filtered, but if I had 1.5mm rows in my table and 1.4mm of them have NULL here, then do I want to scan those 1.4mm rows if a query wants a specific gender? I don’t.

    Indexes take space, which means more space on disk, more backup space, and more time for backups. However they also impact performance when we read the index, or we update a column used in an index. Each of those operations requires either a read or write of the index. If I can reduce the reads and writes to indexes, then I can improve performance dramatically.

    I would urge you to look at filtered indexes whenever you have data where a large number of rows are not going to match a query. In these cases, you can use a filtered index that is highly selective.

    References

    A few places I used to research this post.

    Quick and Easy Blogging

    This post occurred to me while I was writing some code and was the first post I thought of for the challenge. However I realized I could start with the other posts, and so I wrote those posts first. This one took about 10 minutes to read through limits on filtered indexes, try 4-5 experiments to get the code to work, and write the post.

    Look for the other posts in the April challenge.