For years I’ve looked at various queries online in sample code, diagnostic queries using DMVs, and the like and seen CROSS APPLY in the FROM clauses. But I’ve never really managed to comprehend what it was for or how it worked because I never saw a case where it was directly applied to something I was doing.

Finally, this week I had a breakthrough. I was working on updating a bunch of data but it was breaking on a small subset of that data. In this case, I was attempting to JOIN two tables on fields that should have been INTs, but in a very small number of cases one side was using a comma-delimited string. The user told me that someone else had done these updates in the past and didn’t encounter the problem I was having (so I knew that it was something i was doing “wrong”), but given that it was only a handful of broken updates she was OK with manually doing the updates (we were scripting it because we were updating potentially tens of thousands of records).

In a previous post, I expressed some frustration over Invoke-SqlCmd not setting an Application Name for its ODBC connection, leaving us with the generic .NET SqlClient Library when looking at active sessions in sp_who2 and sp_whoisactive (and any other monitoring tool). Unfortunately, I can’t really do anything about Invoke-SqlCmd aside from posting a suggestion on Connect or the Client Tools Trello board, but Invoke-SqlCmd2 has the same issue and that’s on GitHub. So, here we go!

This month’s T-SQL Tuesday is hosted by Brent Ozar and he’s asked everyone to find interesting bug or enhancement requests in Microsoft Connect related to SQL Server.

The Connect item doesn’t have to have anything to do with T-SQL – it could be about the engine, SSRS, R, the installer, whatever. Now, more than ever, Microsoft has started to respond to Connect requests and get ’em fixed not just in upcoming versions of SQL Server, but even in cumulative updates for existing versions.

Odds are, you’ve got more than one application or script accessing your database or SQL Server instance at any given time. You’re probably stacking them on a small number of servers in an attempt to cut down on licensing costs and resource usage. All those PowerShell scripts running on the central job server are running under a single service account, and you’ve got a lazy vendor who set up both the website and back-end application server to run under the same account, maybe even on a single app/web server.

The Rochester, NY chapter of PASS is holding our 6th annual SQL Saturday on April 29th, 2017! As always, RIT is hosting our event on campus.

SQL Saturday is a free day of training centered on the Microsoft Data Platform. Volunteer speakers come from all over the country (and sometimes beyond) to share their knowledge with attendees. There are sessions available for professionals of all skill levels, whether you’re just starting to learn about databases or a seasoned veteran, in addition to valuable professional development guidance.

This post is in support of Tim Ford’s (blog|twitter) #iwanttohelp challenge. And also written because this has burned me twice in the past 3 months and by blogging about it, hopefully it’ll stick in my mind.

Setup

I’ve recently been doing a bunch of work with stored procedures, trying to improve performance that’s been suffering due to suboptimal queries. Some of this tuning has resulted in creating temporary tables. After making my changes and running the procedures in SSMS, everything looked good - data’s correct, performance metrics are all improved. Everyone wins!

It’s 2016. So why are we still dealing with T-SQL code and design patterns that were designed 7 versions ago?

In the 15 years I have been using databases professionally, we’re still dealing with:

• Peoples’ names are split into first name, last name and middle initial fields. Ignoring that this falls afoul of several of the myths programmers believe about names, the first name column was defined as CHAR(10) in a standard installation. How many characters are in the name Christopher (hint: I had to take off a shoe to count them all)?
• Other arbitrarily short column sizes which cause problems as the system scales out in usage. For example, an event ID field that’s 8 characters: 2 letters and a 6-digit number which is used as a sequence. Guess what happens when you hit the millionth event in that sequence.
• Processes originally developed as transactions (for good reasons), but not designed in such a way that they scale to today’s demands.
• NOLOCK hints everywhere. It’s even in newly-developed code for this application.
• Cursors used anytime a set of records has to be updated with a small bit of conditional logic built in. A set-based operation with appropriate CASE statements would work much better.

The primary system I deal with on a daily basis was originally developed as a DOS application and several of the above examples are drawn from it. Looking at the core tables and columns, it’s easy to identify those that began life in those early days - they all have 8-character names. Time moved on and the system grew and evolved. DOS to Windows. Windows to the web. But the database, and the practices and patterns used in the database, haven’t come along for the ride.

Something in SQL Server that isn’t always obvious to beginners is that when you create a default value for a column on a table, SQL Server creates a constraint (much like a primary or foreign key). All constraints must have a name, and if one isn’t specified SQL Server will generate one for you. For example:

1
2
3
4
5

CREATE TABLE [dbo].[point_types] (
   [typeid] [int] NOT NULL DEFAULT(NEXT VALUE FOR [pointtypeid])
  ,[typename] [nvarchar](30) NOT NULL DEFAULT 'Unspecified'
  ,CONSTRAINT [PK_PointType] PRIMARY KEY CLUSTERED ([typeid] ASC)
)

We’ve got a simple table here and both fields have a default value set (the primary key’s value is generated from a sequence object, pointtypeid). Time goes on, and a change in policy comes up which requires that I change the default value of typename to Unknown. To do this, I have to drop the constraint and re-create it. To find the name of the constraint, I can either ask sp_help, or run this query:

This week’s #sqlnewblogger posts!

I have a situation where I need to retrieve the data in an encrypted column from, but don’t want to give all my users access to the symmetric key used to encrypt that column. The data is of the sort where it’s important for the  application to produce the required output, but if a user runs the stored procedure to see what the application is getting from it, it’s not critical that they see this one field.