Voice of the DBA

Tag: sql server

I Feel Like a Magician

I ran across this comparison of SQL Server to PostgreSQL. It’s written from the point of view of a PostgreSQL developer, who certainly doesn’t like the Microsoft product much, with no shortage of complaints. Whether you agree or not, I do think there are a few valid points.

However one of the quotes that really caught my attention was in the section on converting dates. The author says that “MSDN provides a table of these magic numbers.”, referring to the arcane and completely unintuitive format codes that we use with CONVERT(). Fortunately FORMAT() was introduced in 2012, and simplifies things, but still has issues and limitations.

Certainly all systems and languages will have some codes and parameters that don’t make sense, left over from earlier times for backwards compatibility. However the more I look at T-SQL and SQL Server, the more I do find it silly that many of the small conveniences haven’t evolved across the versions. The bcp utility is outdated, functions haven’t been updated to work with more than 8,000 characters, SSIS has issues with CSV files, and more.

It does seem at times that when I’m answering questions on the Internet that the answers I give, while logical and familiar to me, seem magic to others. This might be especially true when talking about transaction logs, which still seem far to difficult for many people to grasp.

I enjoy working with SQL Server and look forward to a long career in the future developing software on the platform, however I do worry about some of the long term health of the platform for new users. It seems that the usability advantages of SQL Server have dramatically narrowed in recent years, and in some ways the other platforms have implemented features that SQL Server is sorely lacking.

Hopefully Microsoft will focus on reducing the friction of manipulating data in SQL Server. Not that SQL Server will go away, but I can see companies migrating to other platforms if it becomes substantially easier and cheaper to manipulate data.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

July 6, 2015
Half a Year

Today is a holiday for much of the US workforce. Tomorrow is Independence Day, though we have another year until the sequel of the movie by that name. This is the day the US started down the road of becoming a country, and if you’re a US citizen, i’d urge you to take a few minutes and look back at our declaration.

Half a year is gone in 2015. It’s amazing to think that this year is already halfway gone. At the beginning of 2015, we had little information on the next version of SQL Server, though we had a preview of some features, like Row-Level Security (RLS), in January. Now we have CTP 2.1 and a lot of features that are being added. We’ve added some jumpstart links for Learning SQL Server 2016, so if today is quiet, check out an area and learn a bit about what’s coming.

We continue to see data loss incidents on a regular basis. It’s almost becoming a piece of news that we are desensitized to hearing. I’m not sure if it’s good or bad, but I don’t think the financial penalties are high enough to get companies taking it more seriously, or certainly not serious enough to ensure developers change their ways they build applications. If you doubt the issues here, read Troy Hunt’s blog. He regularly finds companies not implementing security well.

All in all it’s been a good year for technology and it’s exciting to see many products changing, technologies maturing, and more options than ever for building software. I hope you have a great holiday weekend in the US, and a pleasant one elsewhere.

Steve Jones

July 3, 2015
SSMS Maturity

I’ve used a lot of tools with SQL Server over the years. We had a variety of individual tools from Microsoft for SQL Server 4.2, including the isqlw query editor that I used for years. I wrestled with the Enterprise Manager MMC plugin and eventually moved away to use the Embarcadero suite to work with SQL Server before coming back to Microsoft’s SSMS in SQL Server 2005. I’ve had a love/hate relationship with that tool ever since, though the Redgate suite of extentions has certainly made life easier.

However Management Studio (SSMS) hasn’t really evolved in quite a few years. It seems that the enhancements and additions that have been made with each version have been minimal, sometimes barely working and rarely improved across versions. It’s been disappointing that relatively few resources have been expended on SSMS, despite the regular evolution of SQL Server every 2-3 years. And despite the fact that one of the big reasons SQL Server was touted over Oracle and other RDBMSs is that the tooling was better.

I suspect some of the problems were the pressure to release the core parts of SQL Server first, and link SSMS to the server product, despite the fact that it really needs to support multiple versions and previous tooling should have been improved.

That’s changing a bit. As Tim Ford noted, SSMS now has it’s own release and upgrade path. The tool should not have it’s own, separate download and lightweight installer along with a separate release cadence from SQL Server. I suspect this will evolve more rapidly, probably closer to an Azure like schedule, with more regular patches and enhancements. At least that’s what I hope.

I was glad to see SSMS being made available for 2012 and 2014 versions as a separate download, and the change to a completely separate product that will likely become de-linked from SQL Server versions. I expect we’ll just have SSMS in the future, with some version that’s more like what we see in Chrome and Firefox.

For now SSMS is still based on the 2010 Visual Studio shell, but the comments in this announcement seem to indicate it will move to the 2015 shell soon. Let’s hope that happens and the performance improves along with the maturity of the tool in the future.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and LibSyn.

July 2, 2015
Updating Extended Properties on a Table
I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

I wrote recently about adding an extended property to a table. As part of what I was testing, I also needed to update properties, changing values back and forth. It’s fairly easy to do so, and I wanted to document this for my own reference.

The sp_updateexteendedproperty is analogous to the sp_addextendedproperty procedure. Here’s the code I used to change my property value on the table from the last post.
```
EXEC sp_updateextendedproperty 
@name = N'PKException', 
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'SalesTax3'
;
```
As you can see, I pass in the same parameters. The procedure then changes the parameter in the table. A quick check in SSMS will show you the values changed. In my case, I was changing the value from 0 to 1 to test a query.

The property does need to exist. If I execute this:
```
EXEC sp_updateextendedproperty 
@name = N'PKcheck', 
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'SalesTax3'
;
```
I get an error thrown from the database engine.

Msg 15217, Level 16, State 2, Procedure sp_updateextendedproperty, Line 112

Property cannot be updated or deleted. Property ‘PKcheck’ does not exist for ‘dbo.SalesTax3’.

This is a good way to handle this, as a TRY..CATCH can trap the error and do an insert instead of something else.

SQLNewBlogger

This was another side post from my testing of a solution. As I used this code to solve a problem, I kept a copy and made a few screenshots. This one was about 10 minutes in total.

References

sp_updateextendedproperty – https://msdn.microsoft.com/en-us/library/ms186885.aspx

sys.extendedproperties – https://msdn.microsoft.com/en-us/library/ms177541(v=sql.90).aspx
July 1, 2015