The views expressed herein may not reflect the views of my employer, the Joint Chiefs of Staff, or the Council of Elrond. Fnord.

tsql

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.

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.

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.

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.

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.

--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;

I had an itch of curiosity that I wanted to scratch: I wanted to know what, and how many, of each object type that I had in my database. As I love poking around system tables and views, I came up with the following code:

WITH objs AS
(
SELECT type_desc AS Object_Type,
COUNT(*) AS Object_Count, 1 AS JoinColumn
FROM sys.objects
--exclude internal tables, service queues, and system tables
WHERE TYPE NOT IN ('IT', 'S', 'SQ')
GROUP BY type_desc
),
tots AS
(
SELECT COUNT(*) AS Type_Count, 1 AS JoinColumn
FROM sys.objects
WHERE TYPE NOT IN ('IT', 'S', 'SQ')
)
SELECT Object_Type, Object_Count, Type_Count,
CAST((Object_Count * 1.0) / Type_Count * 100 AS INT) AS Type_Pct
FROM objs o
JOIN tots s
ON o.JoinColumn = s.JoinColumn
ORDER BY Object_Type;

I find CTEs (Common Table Expressions) to be interesting. I don’t have near enough familiarity with them, so I’m trying to use them more, and this was an excellent opportunity. My original query was this:

SELECT type_desc as Object_Type,
COUNT(*) as Object_Count
FROM sys.objects
WHERE TYPE NOT IN ('IT', 'S', 'SQ')
GROUP BY type_desc
ORDER BY type_desc;

And while it did the trick, I was wanting, for no particular reason, to also have the total number of objects and the percentage. Again, no particular reason. It might be able to be done with a window function, but that is also something that I have limited familiarity with, so I decided to approach it as a CTE. And it works nicely. The objs CTE gives me a count of each object type while the tots CTE gives me the count of all objects. By giving each CTE a column with the value of 1, it’s easy to join them together then calculate a percentage.

It was a pleasant little exercise in coding for a utility that scratched my itch. And I hope you had a pleasant holidays, ours were spent in Phoenix where we fortuitously avoided a storm with 60-80 MPH winds that did quite a number on the observatory that employs my wife.

I ran in to this rather odious SQL Server Native Client 11.0 error last week, getting a message box saying “ODBC–update on a linked table failed.” Actually it was an insert, but that’s OK, I never expect software to have particularly good error messages. (but why, oh why, can’t Microsoft make their error messages so that we can copy the error message out with our mouse to make it easier to look up?) I’ve since been banging my head against the wall trying to solve it. It didn’t seem like anything complicated going on: I had a table that I was accessing directly through a subform and adding a record. The insert was consistently blowing up.

I have the date and time as two separate fields as I want my form to display the records sorted by date descending, time ascending. I needed to populate the date, time, student ID, and user ID, and the following code on the BeforeInsert did that:

(the txtNotLen field counts how many characters are in the note since they’re limited to 1,000)

So the above code produced the #0 error message. I could add records through SSMS, so I knew the table was functional. Today I get back from lunch and decide to manually add a record to the table via Access, using Ctrl-; to add the date and Ctrl-: to add the time.

And it blew up.

Turns out that when you use Ctrl-: to enter the time, you get 12:57:41 PM. The PM was the problem. SQL Server was expecting just a time value, so I changed the NoteTime line to say Format(Time, “h:m:s”): the Format “h:m:s” converts the current time to 24 hour format, so 4:49pm becomes 16:49.

So if you’re getting this error and you’re dealing the VBA and a Time(3) field on SQL Server, this might be the cause.

Interesting stuff. With the student information system that I’m developing, there’s one query that’s central to all users, it’s called vwStudentsFiltered. Since I need to enforce row filtering based on what district(s) a user has access to, this became the core. The original view looked something like this:

SELECT st.*
FROM Students AS st
JOIN SeekerUsers AS u
ON u.SeekerUserDBLogin = SYSTEM_USER
AND st.StatusCode in ('0', '1', '9')
AND (u.SeekerUserRole = 1 --superuser sees all, otherwise filter
OR st.DistrictNum IN (SELECT td.SeekerUserDistrict
FROM SeekerUsersDistricts AS td
WHERE u.SeekerUserid = td.SeekerUserid)
);

Obviously I wasn’t doing a Select *, but it’s a long field list and irrelevant to the discussion.

Student Status Codes of 0, 1, and 9 indicated an active student. District users were only allowed to see students that were not assigned to a district, or students assigned to their district. And SeekerUserRole 1 is a superuser who is allowed to see everyone, regardless of status code. The problem is that if you looked at it through SSMS Tools Pack by Mladen Prajdić, it would report that the query had an estimated cost of 200%. (SSMS Tools Pack is pretty neat, I recommend checking it out) The SeekerUsersDistricts table consists of two fields: SeekerUserID and a district number. For district users, they’d have one record in this table, teachers could have more than one district number.

The problem is that the u.SeekerUserRole = 1 OR st.DistrictNum IN confuse the optimizer: it could execute either path, and you’ll only know at runtime. Kind of hard to optimize for something like that. Turning on client statistics gives us the following:

There are three types of users: superusers, district users, and teachers. The front-end app gives them different menus to control what they can do and what data they can see. In this post I’ll be looking at how to improve the view but only for the superuser.

Here’s the code that I came up with:

--Variation 1
SELECT st.*
FROM Students AS st
WHERE 1 = (SELECT 1
FROM SeekerUsers AS u
WHERE u.SeekerUserDBLogin = SYSTEM_USER
AND u.SeekerUserRole = 1);

--Variation 3
SELECT st.*
FROM Students AS st
WHERE EXISTS (SELECT 1
FROM SeekerUsers AS u
WHERE u.SeekerUserDBLogin = SYSTEM_USER
AND u.SeekerUserRole = 1);

Running all three in the same query window shows each result set having the same number of rows returned, 939 students. So far, so good.

Variation 3 made sense to me. There are no common fields between the Student table and the SeekerUsers table, so a join, on the surface, doesn’t make sense. So why not a Where Exists? The devil, as they say, is in the details. The most glaring thing to show in the graphic execution plan was an operator that I had never seen before: a Row Count Spool (Lazy Spool) with a cost of 78%. With all three queries running together, Variation 3 had a cost relative to the batch of 70%, the other two each being 15%. The Row Count Spool was doing rebinds and rewinds, which are not good. The estimated number of rows and the estimated row size on that operator were farcical: it estimated one row and returned 939 rows (no, updating statistics and reindexing did nothing to improve that mismatch). On top of that, the entire estimated subtree cost was 0.12, the other two variations were each 0.026.

So variation 3 is out. What’s the difference between 1 and 2? The graphic plans are identical, as are the numbers on every operator, but there’s a subtle difference: the cache plan size for #2 is 96 kb, #1 is 88 kb. Kind of trivial, but this plan is going to be called regularly, so it’ll live in the cache pretty much forever. At this point there’s no way to forecast how much cache memory pressure there will be, but 8k is 8k, so since every statistic between the two variations is identical, I’ll go with #1. Aesthetically, I personally prefer #2, but I can live with #1.

Here’s the execution plans:

Yes, I’m getting scans on my Students base table. Right now the queries are returning 99% of students, so a seek doesn’t improve things since a covering index would require every field in the table. As the number of students grow with diverging status codes, then indexing might come in to play.

Wow! I didn’t realize that I had missed a week posting. I wasn’t intending to post daily, still…. My excuse is that I had a board meeting in Albuquerque last week that forced me in to a last minute review to make sure my system was working properly and loaded on to my laptop correctly, plus travel. And even with that prep, I still had a bug creep in. Still, my presentation and demo went quite well and the bug was somewhat minor.

ANYWAY, today I have a code snippet that I just wrote. I love working with base tables in SQL Server, and needed all of the field names of my largest (field count-wise) table, some 50 fields. So I wrote this code to give me all of the field names so that I would’t be doing a SELECT * in a view.

If you want a table prefix to appear in front of each field name, change the @tblpre variable. Run this code with the output window set to Text, copy and paste, delete the row of dashes, and you should be good.

--GetTableFieldNames.sql, WW, 04/28/2015
--returns all field names with some exclusions:
--makes it easier to add a long list of field names
--to a view to eliminate SELECT *
declare @tblpre char(3) = ''
set nocount on
select ', ' + rtrim(@tblpre) + sc.name
from sys.columns sc
join sys.tables st
on sc.object_id = st.object_id
and st.name = '[TABLE NAME TO GET FIELDS FOR]'
where sc.is_identity = 0
and sc.is_computed = 0
and system_type_id <> 189 --exclude timestamps
set nocount off