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.

Advertisements

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.

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;

A couple of interesting posts from other bloggers

First, from the illustrious Phil Factor at Simple Talk, a post on using T-SQL to generate UML code to create database relational diagrams.  The diagram generator is PlantUML and does a fair job, but the diagram can become very wide and complex.

I did have one problem with Phil’s script that I haven’t been able to correct yet: some rows are returned as Null and have to be deleted from the output that you feed PlantUML lest it crash.  Easy enough to clean up.

https://www.simple-talk.com/sql/sql-tools/automatically-creating-uml-database-diagrams-for-sql-server/

 

The second is from Kendra Little, formerly a business associate of Brent Ozar, also illustrious, with a script for generating the code to recreate all of the indexes in a database.  This is cool for a couple of reasons.  First, it includes a note showing whether or not a table is a heap.  Second, it also shows you the usage statistics which tells you whether an index is getting hit.  If it’s not getting hits, it might not be worth the overhead to keep the index.

Personally I’m probably going to make it part of my Model database.  You can turn it into a view if you remove the Option(Recompile) line at the end of the code.  You’re probably better off not looking at the execution plan of the code, just be satisfied that this is system management and not production code.

How to Script Out Indexes from SQL Server

Concatenating text fields from multiple records in to one string

We have a Caseload system where a teacher can assign certain specific kids to their caseload, so with one set of menu buttons they can access any kid in the district, with another they access only a filtered list.  These are the kids that they are supposed to pay special attention to.  To complicate matters, because they can never be simple, it was decided that it was OK that one kid could be on multiple caseloads.  Okay, not a difficult change, except that I displayed the caseload teacher’s name on the bottom of the student edit screen.  Now I had multiple teachers and needed to build a string so they could all be shown together.

This is something that I’ve done before, but I couldn’t find the code that I needed.  I remembered the basics and eventually found a sample that let me build out the code that worked.

This is the basic code:

DECLARE @s AS VARCHAR(40); --MUST be varchar!  Doesn’t work if it’s char.
SET @s = '';
SELECT @s = @s + t.TeacherName + ', '
FROM Caseload cl
JOIN Teachers t
       ON cl.TeacherID = t.TeacherID
WHERE cl.StudentID = 1
ORDER BY t.TeacherName;
SELECT SUBSTRING(@s, 1, LEN( @s) - 1);
SELECT @s;

It builds up the @s variable, holding all the teacher names, for StudentID 1.  Then the substring removes the last comma and space.

First, our test rig.  Two tables, Teachers and Caseload.  We’ll keep it basic with a half dozen teachers and we’ll just use three students.  No student table because we’re just concerned with an integer student ID number.

/****** Object:  Table [dbo].[Teachers] ******/
CREATE TABLE [dbo].[Teachers](
       [TeacherID] [int] NOT NULL,
       [TeacherName] [varchar](10) NOT NULL,
 CONSTRAINT [PK_Teachers] PRIMARY KEY CLUSTERED
(      [TeacherID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
/****** Object:  Table [dbo].[Caseload] ******/
CREATE TABLE [dbo].[Caseload](
       [TeacherID] [int] NOT NULL,
       [StudentID] [int] NOT NULL,
 CONSTRAINT [PK_Caseload] PRIMARY KEY CLUSTERED
(      [TeacherID] ASC, [StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
--Populate the two tables:
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (1, N'Bob');
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (2, N'Carol');
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (3, N'Ted');
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (4, N'Alice');
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (5, N'Woody');
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (6, N'Buzz');
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (1, 1);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (1, 2);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (2, 1);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (3, 1);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (4, 1);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (5, 2);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (6, 3);
--Show the joined caseloads so we can more easily see what the UDF will produce:
SELECT cl.StudentID, cl.TeacherID, t.TeacherName
FROM Caseload cl
JOIN Teachers t
       ON cl.TeacherID = t.TeacherID
ORDER BY cl.StudentID, cl.TeacherID;
--same code as above demonstrating the concatenation
DECLARE @s AS VARCHAR(40);
SET @s = '';
SELECT @s = @s + t.TeacherName + ', '
FROM Caseload cl
JOIN Teachers t
       ON cl.TeacherID = t.TeacherID
WHERE cl.StudentID = 1
ORDER BY t.TeacherName;
SELECT SUBSTRING(@s, 1, LEN( @s) - 1);
SELECT @s;
*/
--The UDF that does everything needed and returns the populated string
ALTER FUNCTION dbo.udf_GetCaseloads (@SeekerID INT)
RETURNS VARCHAR(80)
AS
BEGIN
       DECLARE @s AS VARCHAR(80);
       SET @s = '';
       SELECT @s = @s + t.TeacherName + ', '
       FROM Caseload cl
       JOIN Teachers t
              ON cl.TeacherID = t.TeacherID
       WHERE cl.StudentID = @SeekerID
       ORDER BY t.TeacherName;
       --make sure there's data in the var before trying substrings!
       IF LEN(RTRIM(@s)) > 0
              SET @s = (SUBSTRING(@s, 1, LEN(@s) - 1));
       RETURN @s;
END;
--test harness for the above:
SELECT dbo.udf_GetCaseloads(1) AS CaseloadList; --test UDF
SELECT dbo.udf_GetCaseloads(2) AS CaseloadList; --test UDF
SELECT dbo.udf_GetCaseloads(3) AS CaseloadList; --test UDF
SELECT dbo.udf_GetCaseloads(4) AS CaseloadList; --test UDF

Unfortunately I wasn’t able to get this code working in Access VBA.  My final solution was to wrap it in to a stored procedure which updated a record that’s always available.  I’ll show that tomorrow.

Parsing First and Last Names out of one field

I don’t know if you do a lot of string parsing, like getting First/Middle/Last name in one field. It’s something that I do fairly regularly. I got a spreadsheet of kids that I need to add to my system, and it has their full name in one field so I have to break it apart. Shouldn’t be a big deal.

Famous last words.

I actually couldn’t do it in Access, which was a first for me and quite got my knickers in a twist. Normally I’d do an update query with the Update From, but it didn’t work. I thought maybe the space between the first and last name was actually something weird in hex, but it wasn’t.  So I copied the data from Access back in to Excel (it was fewer than 500 records, and only eight fields or so). The Excel code is as follows:

=LEFT(B2,FIND(" ",B2))

And that should give you the first name. But it didn’t. Turned out that the field had a leading space! So I used the following code:

=LEFT(TRIM(B2),FIND(" ",TRIM(B2)))

And it worked. Then I had a clever thought for getting the last name:

=TRIM(RIGHT(B2,LEN(B2)-LEN(I2)))

(I2 being the first name)  Subtract the length of the first name from the full string length to give you the length of the last name, then a right() gives you the rest.

I was mildly proud of myself. After getting the data back in to an Access table in preparation of uploading it to SQL Server, I went ahead and trimmed leading and trailing spaces.

It’s not perfect. If they have a middle name, or a non-hyphenated compound last name, it’ll plug it all in to the last name and you’ll have to go back and do some manual cleanup. But it’ll get you most of the way there.

How I Do SQL Server Database Maintenance

(One of the reasons for this blog is for me to document my processes for myself.  These are the ways that I do certain things.  In your career as a DBA, if that is your job, you’ll develop your own preferred techniques.  If these are useful for you, bonus!  Regardless, it gives me a source code repository that if I go somewhere to do some consulting to help them get their system maintaining itself properly, I can pull up posts like this and get them going quickly.)

 

The most critical thing as a SQL Server DBA is to ensure that your databases can be restored in the event of the loss of a server for whatever reason: disk crash, fire in the server room, tribble invasion, whatever.  To do this, not only do you have to back up your databases, you also have to test restores!  Create a database and restore the backups of your production DB to them.  It’s the safest way to make sure that everything works.  This test restore can be automated to run every night, but that’s outside the scope of what I want to talk about right now.

There are lots of places that problems can creep in, this is just one part of how you’ll need to monitor systems.  This is how I’ve done things for a number of years, and thus far it has served me well.

First and foremost, backups.  Your requirements will vary depending on your Recovery Point Objectives (RPO, how much data loss is acceptable) and Recovery Time Objectives (RTO, how long before the system is available).  If your normal business hours are Monday to Friday 8-5 with your largest database just a few gigabytes, you’ll have radically different requirements than someone with databases in the hundreds or thousands of gigabytes that require 24/7 uptime.

We’ll go with demonstrating a simple installation.

When your operation is 8-5 Mon-Fri, you have a huge maintenance window that you can get everything done in, so there’s no reason not to get everything done.

My personal method is to do my maintenance tasks at night.  These include DBCC CheckDB, Update Statistics, and checking the suspect pages table.  Note that I DO NOT USE MAINTENACE PLANS.  Gail Shaw recently described them as brain dead, doing far more than what is needed.  I particularly don’t like the miniscule amount that they report, so you’re never clear what was done.  So I do everything through SQL jobs.  And if I have a SQL Express installation, since everything is based on .CMD and .SQL files, it’s not a big deal to run everything through Window’s Task Scheduler.

Since my normal business hours normally see no activity at night, that’s when I do my maintenance.  I do a full backup of each database, followed by a full transaction log backup.  Those will be caught by the server backup later that night and written to tape, which will then be moved to another building the next work day.  I do differential database backups at noon, and transaction backups throughout the day, typically every 10 minutes.  As a rule you don’t want to start multiple backups at the exact same time, so stagger the start time slightly.  If you start the transaction log backups at 7:30 to capture people who come in early, fine.  I have the first transaction log backup initialize the log, then the appending t-log backups would actually begin at 7:40.  Have the noon differential fire at 12:02pm.  Stop the t-log backups at 6pm, start your nightly maintenance at 7pm.  You will develop a feel for how long each task takes based on the size of the database.

On to the job specifics.

My first step calls a file called NightlyDBCC.cmd, which is this:

osql -S"[servername]" -E -id:\dbccs\nightlydbcc.sql -od:\dbccs\dbccresult.txt -w200
osql -S"[servername]" -E -id:\dbccs\nightlysuspectpages.sql -od:\dbccs\suspectpagesresult.txt -w200

This .CMD file is two lines, each starting with osql -S.

The OSQL program is a SQL Server command line tool that lets you run T-SQL commands from the command line.  The switches that I use are:

  • –S: the name of your SQL Server
  • -E: use the current user’s credentials for authentication
  • -I: the input file that contains the commands to be run
  • -O where to write the output
  • -W: column width

Like most Windows utilities, you can get a full list of options by typing OSQL /? in a command prompt window.

The nightlydbcc.sql file contains the following:

use master
go
exec sp_MSforeachdb @command1="print getdate() print '? checkdb' DBCC CHECKDB(?)"
go

The stored procedure, sp_MSforeachdb, is an undocumented system stored procedure that runs a command string against every database on a server.  It accepts a question mark as a wildcard representing each database name.  So the string being run might be better visualized as

For Every Database:
print getdate()      --print the date for the output log
print '? checkdb'    --print header record of the database name
DBCC CHECKDB(?)      --run DBCC CHECKDB against every database

And the output is routed to dbccresult.txt.  Inspect that file every morning and you’ll have a good idea if your system is clean or if something nasty is lurking inside waiting to leap out, like the chestburster from the movie Alien.

NightlySuspectPages.sql has a little bit more going on.

--nightlysuspectpages.sql
SET NOCOUNT ON;
SELECT 'Run date:', GETDATE();
SET NOCOUNT OFF;
--Server and SQL uptime
SELECT DATEADD(SECOND, -1 * (ms_ticks / 1000), GETDATE()) AS ServerStartDate,
    sqlserver_start_time AS SQLStartDate
FROM sys.dm_os_sys_info;
SELECT 'Suspect Pages' AS SuspectPages, *
FROM msdb.dbo.suspect_pages;
SELECT GETDATE() AS RunTime, LEFT(name, 15) AS DBName,
    LEFT(filename, 60) AS Filename, LEFT(status ,6) AS Status
FROM sysdatabases
WHERE dbid > 4
--    AND status <> 528
ORDER BY name;

The first code selects the time stamp of when the code ran in to the output file.  The second block prints when the server was last started and when SQL Server started up.  They should be fairly close, but could vary if you’d installed some SQL service packs or updates that could have restarted the SQL services.

The third select looks for any pages in msdb.dbo.suspect_pages.  These pages represent an I/O error, which could be a transient glitch or a warning of a problem in your disk system: a SAN or disk, whatever, it’s something to look at.  Take a look at Books Online for more information, just enter suspect_pages in to the search box.  Hopefully, normally, there won’t be any records here and you can ignore it.  But if something ever appears here, you have a problem!

And finally, the fourth select lists every database on that instance and shows the Status field.  It will be a number like 65536 or 65544.  That number will change depending on what options are selected for the database, like file recovery mode, etc.  The thing that I watch for is if it changes — if someone changes a database from full recovery to simple, I want to know that!

More on this soon.