Using LEN() Wrong

Kenneth Fisher had a recent blog post on a question that was posted to Stack Overflow.  The interesting bit was someone using the LEN() function to determine the length of a float with odd results.

Personally, it had never occurred to me to use the LEN() function against a number.  It’s a string manipulation function, and that’s it.

Here’s a little test harness:

BEGIN TRANSACTION;

-- https://sqlstudies.com/2016/06/02/using-len-wrong/
-- Kenneth Fisher
CREATE TABLE #temp (MyFloat FLOAT, MyStr VARCHAR(50));

INSERT INTO #temp VALUES (12345,'12345'),(123456789,'123456789');

SELECT MyFloat, 
    LEN(MyFloat) AS MyFloatLen, 
    DATALENGTH(MyFloat) AS MyFloatDataLen,
    MyStr, 
    LEN(MyStr) AS MyStrLen, DATALENGTH(MyStr) AS MyStrDataLen,
    LEN(LTRIM(RTRIM(CAST(MyFloat AS VARCHAR)))) AS MyFloatTrimmedLen,
    LEN(LTRIM(RTRIM(CAST(MyStr AS VARCHAR)))) AS MyStrTrimmedLen
FROM #temp;

ROLLBACK;

The results:

MyFloat    MyFloatLen  MyFloatDataLen MyStr      MyStrLen  MyStrDataLen
---------- ----------- -------------- ---------- --------- ------------
12345      5           8              12345      5         5 
123456789  12          8              123456789  9         9

(2 row(s) affected)

If we look at the execution plan, we see a hidden implicit conversion of applying the LEN() function to the float.  These implicit conversions can really hurt code execution: take a look at Grant Fritchey’s SQL Server Execution Plans, published by Red Gate.

Curious stuff.

Advertisements

An interesting way at looking how system views are built

I love metadata.  I think it’s really cool looking at the underpinnings of SQL Server.  While cruising through system views this morning, I came across one that I’d never seen before.  I’m not 100% certain that it is a Microsoft-supplied view, it’s possible that it was provided by one of the utility packages that I’ve installed.

Anyway, here’s the view:

SELECT [Datetime]
    ,[Instance name]
    ,[Storage errors]
    ,[Sql errors]
    ,[Credential errors]
    ,[Other errors]
    ,[Deleted or invalid backup files]
    ,[Number of backup loops]
    ,[Number of retention loops]
FROM [msdb].[dbo].[vw_autoadmin_health_status]

The result returned is also interesting:

Datetime Instance name Storage errors Sql errors Credential errors Other errors Deleted or invalid backup files Number of backup loops Number of retention loops
--------------------- --------------- --------------- ----------- ------------------ ------------- ------------------------------- ----------------------- --------------------------
Jun 10 2016 12:47PM NB-REF-8N4BWZ1 0 0 0 0 0 0 0

(1 row(s) affected)

Since this is my dev server, I’m not surprised these numbers are zero.  I think it would be much more interesting to run this on a production server.  I suspect that if you have multiple linked servers that you’ll get one row per server, I don’t have the environment to test this.

Here’s the odd bit: now I can’t find the view again!  But selecting from that view does return one row.  So I turned on Include Actual Execution Plan and the results were intriguing.

The first operator (bottom-most, right-most, was a table scan against a TVF called fn_get_health_status, which is part of the [msdb].[smartadmin] schema.

I’m running SQL Server 2014 Developer Edition, and Help for that TVF says “Smart Admin automates administration tasks on the SQL Server instance using minimal user configuration.”  Cool.  Definitely a good thing.  I’m used to monitoring a lot of things in SQL Server by myself, using tools that I’ve developed over the last 20ish years.  And I’d never heard of Smart Admin.

Poking around in Books Online and Google Search I came across this Microsoft page, talking about Smart Admin, talking about “The function is used to report health status of services under Smart Admin.

Definitely something worth looking in to with the intent of making some utilities to simplify monitoring servers.

Sometimes you need only one record in a table

To be specific, I want a table that contains a single record, typically this reports version information or status or something like that.  Do not allow a second record to be added.

I’ve done this in both Access and SQL Server, here’s the code for SQL:

CREATE TABLE [dbo].[z­DBInfo] (
[ID] [tinyint] NOT NULL IDENTITY(1, 1),
[App­Is­Locked] [bit] NOT NULL,
[Info­Date] [datetime2] (0) NOT NULL,
[Version­Date] [datetime2] (0) NOT NULL,
[DBVersion] [varchar] (10) COLLATE SQL_­Latin1_­General_­CP1_­CI_­AS NOT NULL,
[Info­Msg] [varchar] (512) COLLATE SQL_­Latin1_­General_­CP1_­CI_­AS NOT NULL,
[Row­Ver] [timestamp] NOT NULL) ON [PRIMARY]
GO

ALTER TABLE [dbo].[z­DBInfo] ADD CONSTRAINT [CK_z­DBInfo_­There­Can­Be­Only­One] CHECK (([ID]=(1)))
GO

ALTER TABLE [dbo].[z­DBInfo] ADD CONSTRAINT [PK_z­DBInfo] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO

The key to this is the constraint.  The ID field doesn’t need to be an identity, it can just as not have that parameter as long as you plug in the 1.  Users should only have Select permissions on the table so that your application program can see it, when you need to modify any information you do it direct at a higher permission level.

Personally, I like the AppIsLocked field.  In my application, the startup menu is actually invisible: it checks if the current user has access to the system and then launches the appropriate menu.  But first it checks the AppIsLocked field, and if it is true, doesn’t allow them in to the system at all.

A couple of things: SQL 2014 SP1 CU6 re-issue and SQL 2016 auto-phones home

Let’s start with CU6.  This update for 2014 SP1 came out recently and had a bug: it could cause NOLOCK issues on your server.  Not a good thing.  That CU has been removed and this new one issued.  But they didn’t change the number: it’s still CU6.  They changed the KB article number behind the update, but they kept the same update number.  This could definitely cause some confusion.  Steve Jones, one of the founders of SQL Server Central, wrote about this on his blog, expressing an interesting opinion.  He thinks that, while the prompt and frequent release of CUs is a good thing, that the rapid release cycle could cause some problems with shops that have narrow maintenance windows.  I think that’s a good point.  I’ve been fortunate in that every shop that I’ve worked in had strictly an 8-5 operation timeframe, so I never had any problems applying patches, but I can see the issue.  Steve also posits that Microsoft might go away from Service Packs entirely and issue all bug fixes through Cumulative Updates.  I’m not sure I agree with that, but I can’t point to anything solid behind my belief, so we’ll wait and see.

Regardless of whether or not you’ve already installed CU6, you need to re-install it.

Perhaps the best way to keep track of Service Packs and Cumulative Updates for any currently supported version of SQL Server is through a web site that Brent Ozar created, SQL Server Updates.  You can subscribe and whenever a new CU or SP appears, you’ll get an email.  Pretty nifty, eh?

Speaking of Brent, a blog post today had some news that I’m not too keen on.  As we know, SQL Server 2016 launched this week.  A study of the EULA that you must agree to before installing produced a couple interesting bits of tid.  First, SQL Server, if it’s connected to the internet, auto-updates.  If a patch is available, it will download and apply it.  I am not at all keen on this as there are far too many stories over the years of an SP or CU breaking an installation.  And what happens if said patch auto-install requires a service or server restart?  Does it do it regardless of usage, or what?  I do not know, but I expect we’ll be finding out about this soon.

The second part of Brent’s post was that SQL Server 2016, by default, phones home with telemetry reports.  THIS IS BUILT-IN, AND YOU CAN’T TURN IT OFF.  Well, you can turn it off if you’re running an Enterprise edition via a registry key change.  And you can probably block said transmissions via firewall rules.  Here’s the problem: what if your network contains HIPAA information?  Or student information?  Or credit cards?  How do you know what is being sent to Microsoft and what is being done with it.  You can’t.  For that matter, it’s possible that the information is not going to Microsoft and that it’s going to a third party for aggregation and analysis.

There is a Connect item, actually created by Brent, to give DBAs the ability to turn off this automatic telemetry transmission.  As of the time of me writing this, there were two comments on the Connect page describing installations that, due to the information that they store and process, absolutely would not upgrade to 2016 until this is removed.

As it happens, I’ll be setting up a new server with 2016 Developer’s Edition in the near future, and I’ll be keeping a close eye on my firewall log and blocking ports left and right.  For that matter, that particular computer doesn’t need internet access, so I might just block it from accessing the outside world at the router level.

T-SQL += started in 2008?!

A few days ago, The Impatient DBA wrote that in Microsoft SQL Server 2008, MS added an old C operator: +=.  Simply put, add a value to a variable.  You can run the following code that he provided:

DECLARE @shamwow int = 0;
SET @shamwow += 1;
SET @shamwow += 1;
SELECT @shamwow AS ShamWow;

And @shamwow is now 2.  Without using the += operator, we’d be doing @shamwow = @shamwow +1.  Is += better?  It is certainly shorter, but I think that’s mainly a matter of personal choice.  If you code in other languages that support it, I think it would probably be a good thing.

But there’s more than just adding: there’s also operators for the other basic math functions: -=, *=, /=.  Let’s continue playing with @shamwow with the following code:

DECLARE @shamwow FLOAT = 3;

SET @shamwow *= 3;

SELECT @shamwow;

SET @shamwow /= 2;

SELECT @shamwow;

SET @shamwow -= 3;

SELECT @shamwow;

SSMS will return 9, 4.5, and 1.5.

Pretty cool, eh?

But there’s two problems.  First, this is supported in T-SQL, but not in Access 2013 VBA.  The other is that there’s another pair of C operators, ++ and — (that’s minus minus, not an em dash).  Add or subtract one from the preceding variable, so you’d do set @shamwow++ if it were supported.  And these latter two are not supported in either T-SQL or VBA.  I’m sure they’re alive and well in C# and probably several other .Net languages.

I’m frankly amazed that this happened EIGHT YEARS AGO and I haven’t seen it in common usage.  It’s been in C for probably as long as there has been C, and now we have it in T-SQL.  Something about new tricks and old dogs comes to mind.

Excellent post from Brent Ozar on what to do when a database gets corrupted

You are running DBCCs on your systems at proper intervals, right?  If not, you are keeping your resume up to date, right?  Having a database get corrupted and not being able to recover it can definitely be an RGE (resume generating event), i.e. your job just ended.

Brent goes over an excellent plan for how to address what happens if a DBCC reports an error, stressing the importance of backups and regularly running DBCCs.  If there’s one weakness in his plan, it’s that he does not say to never use the REPAIR_ALLOW_DATA_LOSS option.  This should always be the absolute last option as your data is going to fall in to the bit bucket and will never be seen again.

An excellent defense against database corruption is to use the WITH_CHECKSUM option on your backups.  My job for full backups looks like this:

BACKUP DATABASE [BlahBlahBlahBlah] TO [BlahBlahBlahBlah_data_bkup] WITH NOFORMAT, INIT, NAME = N'BlahBlahBlahBlah-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR;
GO

DECLARE @backupSetId AS INT;

SELECT @backupSetId = POSITION FROM msdb..backupset WHERE database_name=N'BlahBlahBlahBlah' AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name=N'BlahBlahBlahBlah' );

IF @backupSetId IS NULL BEGIN RAISERROR(N'Verify failed. Backup information for database ''BlahBlahBlahBlah'' not found.', 16, 1); END;

RESTORE VERIFYONLY FROM [BlahBlahBlahBlah_data_bkup] WITH FILE = @backupSetId, NOUNLOAD, NOREWIND;
GO

You see the CHECKSUM on the backup along with the RESTORE VERIFYONLY.  The code was generated by right clicking on the database, selecting Tasks, then Backup, plug in the parameters, and select Script.  I put it in a new query window as I may back up several databases in the same job.  Sometimes I’ll just do a find/replace for the other databases since my backup.  The Restore Verifyonly gives you some confidence that your backup is recoverable: NEVER assume that just because your backup ran that the database is restorable!  The ONLY way to know is to actually restore it to another file!  You don’t want to accidentally clobber your production that probably has newer data in it.

(I love backing up with compression!  If your CPU is not under undue strain, it will speed up your backups because your CPU is faster than disk I/O so the CPU is able to compress faster than your disks can take the writes, resulting in shorter backup times and smaller backup sets!)

Of course, the best backup methodology would be to do a full restore to another database after the backup, and then DBCC CHECKDB on that copy.

There is absolutely nothing wrong with belts and suspenders.  It may look silly, but if it preserves your job then it’s a good thing.

Another good thing to do is to look at the contents of msdb.dbo.suspect_pages table, hopefully it will always have zero records.  With older versions of SQL Server I’ve seen records appear in this before the DBCC noticed major problems.  And yet another is to make sure that you receive emails when alerts 823, 824, and 825.  Those can give you an early indication that something bad is looming over the horizon.

DBCCs are most commonly caused by a bug in SQL Server, thus it is a good idea to keep your server fully patched with Service Packs and Cumulative Updates, also caused by errors in your storage system.  If you’re suddenly seeing corruption across multiple databases, then your storage has a serious problem and you’re in for a long day.

Create backup devices for all databases via T-SQL

Dating back to the formative days of SQL Server, backups were referred to as dumps.  Thus the stored procedure is sp_addumpdevice, even though when you back up a database you use the Backup command.  Whatever.

I have always enjoyed looking at the information stored in the metadata in SQL Server, and one such source is sys.databases, a repository of all databases defined on the server instance.   By appending strings around the name, and filtering for database_id > 4, it’s easy to build dump devices for each database.   I use one dump device to back up the system databases: if you want to have an individual dump device for each system DB, then remove the WHERE clause.

--CreateDumpDevicesFromSysdatabases.sql
/*
 Creates backup devices for all databases.
 The code from Tim Ford reads the registry to see where backups are stored, then used to create dump devices.
 Run Part 1 to create the function, then run Part 2 to create the dump devices.
 Change to text output (Ctrl-T), then run script. Copy the output to a new query window and run it.
 Found a link to Tim Ford's code at https://www.mssqltips.com/sqlservertip/1966/function-to-return-default-sql-server-backup-folder/,
 then found the actual code at http://thesqlagentman.com/2010/04/turn-management-studio-into-a-web-browser/.

 WW, 17 Oct 07
 WW, 16 May 15
 WW, 12 May 16: Added Tim Ford's registry reader code
*/

--****************************************************--
--Code for fn_SQLServerBackupDir():
--Author: Timothy Ford (sqlagentman@yahoo.com)
-- returns nvarchar(4000) = dbo.fn_SQLServerBackupDir()
--****************************************************--
--PART 1:
USE master;

IF OBJECT_ID('dbo.fn_SQLServerBackupDir') IS NOT NULL
 DROP FUNCTION dbo.fn_SQLServerBackupDir
GO

CREATE FUNCTION dbo.fn_SQLServerBackupDir()
RETURNS NVARCHAR(4000)
AS
BEGIN
   DECLARE @path NVARCHAR(4000)

   EXEC master.dbo.xp_instance_regread
       N'HKEY_LOCAL_MACHINE',
       N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
       @path OUTPUT,
       'no_output'
   RETURN @path
END;

--SELECT fn_SQLServerBackupDir = dbo.fn_SQLServerBackupDir();


--PART 2: SET OUPUT TO TEXT! CTRL-T!
DECLARE @BackupLocation NVARCHAR(4000);
SELECT @BackupLocation = master.dbo.fn_SQLServerBackupDir() + '\';
SET NOCOUNT ON;

SELECT 'USE master;';

--Each DB gets two dump devices: one for data, one for log.
SELECT 'EXEC sp_addumpdevice ''disk'', ''' + name + '_data_bkup'', ''' 
    + @BackupLocation + name + '_data_bkup.bak''' + ';'
    + CHAR(13) + CHAR(10)
    + 'EXEC sp_addumpdevice ''disk'', ''' + name + '_log_bkup'', ''' 
    + @BackupLocation + name + '_log_bkup.bak''' + ';'
FROM master.sys.databases
WHERE database_id > 4
UNION ALL
--I back up all system databases to the same file via append.
SELECT 'EXEC sp_addumpdevice ''disk'', ''SystemDB_data_bkup'', ''' 
    + @BackupLocation + 'SystemDB_data_bkup.bak''' + ';'
    + CHAR(13) + CHAR(10)
    + 'EXEC sp_addumpdevice ''disk'', ''SystemDB_log_bkup'', ''' 
    + @BackupLocation + 'System_log_bkup.bak''' + ';';

SET NOCOUNT OFF;