Desiderata Errata

Came across this recently and thought it was an excellent overview of the ideal software development process. As written it's more of a waterfall process, but the flow could also be used for agile processes in smaller iterations. Gather requirements Systems analysis Software design Module design Unit tests Coding Integration testing Systems testing Acceptance testing

As soon as I finished working with a bulk upsert stored procedure using table valued parameters , my brain jumped to "What about if it's just a list of string values? Would it be simpler to just use STRING_SPLIT?" OK, thanks brain, now I need to spend some time on that or I won't sleep well tonight. Setup was simple, created a stored procedure for each approach that takes the input of a DisplayName list, and queries the StackOverflow2010 database Users table for those records. I also added an index on the DisplayName to avoid a table scan. Test Query:  USE [StackOverflow2010] GO SET STATISTICS IO ON GO DECLARE @DisplayNamesString nvarchar(max) = 'Jeff Atwood,user262577,Alberto A. Medina'; DECLARE @DisplayNamesTVP [dbo].[StringSplitTestTVP]; INSERT INTO @DisplayNamesTVP VALUES  ('Jeff Atwood'), ('user262577'), ('Alberto A. Medina'); EXECUTE [dbo].[GetUsersByDisplayNameUsingStringSplit]     @DisplayNamesString EXECUTE [dbo].[GetUsersByDi...

Not much background to this post. Had an interest in improving a part of some work code that did multiple upsert operations, and came up with a bulk insert stored procedure using the pattern described by Aaron Bertand . Uploading an example that uses the StackOverflow2010 database and Users table. Performance is greatly improved by using this over individual upsert statements. Users table valued parameter:  USE [StackOverflow2010] GO /****** Object:  Table Valued Parameter [dbo].[UsersTVP] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TYPE [dbo].[UsersTVP] AS TABLE ( [Id] [int] NULL, [AboutMe] [nvarchar](max) NULL, [Age] [int] NULL, [CreationDate] [datetime] NOT NULL DEFAULT GETDATE(), [DisplayName] [nvarchar](40) NOT NULL DEFAULT '', [DownVotes] [int] NOT NULL DEFAULT 0, [EmailHash] [nvarchar](40) NULL, [LastAccessDate] [datetime] NOT NULL DEFAULT GETDATE(), [Location] [nvarchar](100) NULL, [Reputation] [int] NOT NULL DEFAULT 1, [UpVot...

Usually I like to open a set of websites for reading while drinking my morning coffee. To save time, I used an extension that would save presets for each day, and it would open each day's links with one button. However, I recently found that the presets were local to the machine when I switched to my laptop on vacation, which was irritating. So, I put together a basic extension that does the same things, but uses Bootstrap for a nicer looking UI, and uses the Chrome storage API to sync presets across all logged-in Chrome instances. It also allows saving presets to a file and loading again, for a backup and or transfer method. Link:  Morning Reader

Continuing the JSON in SQL Server posts found  here  and  here , a co-worker asked the interesting question "can you index the key values in a small SQL Server text column using JSON?" I didn't know the answer to that, so here we go. Setup The test box remains the same: Core i7 CPU, 16 GB RAM, 1 TB 7200 RPM data disk. SQL Server 2019. The DicomFile table had a new column added, populated from the indexed SQL columns, and a new nonclustered index added. alter table DicomFile add IndexedJson varchar(1000); go update DicomFile  set IndexedJson = concat('{'         , '"SopClassUid": "', SopClassUid, '",'         , '"SopInstanceUid": "', SopInstanceUid, '",'         , '"PatientId": "', PatientId, '",'         , '"StudyInstanceUid": "', StudyInstanceUid, '",'         , '"SeriesInstanceUid": "', SeriesInsta...

As a continuation of this performance comparison , I wanted to also compare performance in SQL Server's supposed strong point: reporting on very large relational data sets. Does it truly perform better than MongoDB? Again, this will attempt to be a mostly vanilla comparison, without the different optimizations that can be done in both platforms, for a couple of typical reporting queries. Setup The test box remains the same: Core i7 CPU, 16 GB RAM, 1 TB 7200 RPM data disk. SQL Server 2019, MongoDB 6.0.1. Data was randomly generated using a console app and inserted into both platforms. For SQL Server, I created both a relational table structure, and a columnstore indexed table that is more typically seen in reporting databases. The columnstore table was created as a flat copy of the relational tables, and a SELECT FROM INSERT statement populated the data. MongoDB received the same data, with the Customer and Product data embedded in each document to avoid doing any lookups or joins....

Anyone reading the title of this post may immediately start to question my sanity and/or competency. After all, SQL is a relational datastore and MongoDB is a document datastore, and wouldn't it just be obvious that a document datastore would be more effective at storing unstructured JSON documents? I have been learning more about NoSQL databases and that's the common wisdom, but I've been around long enough to doubt any hype over any technology. I want to know: how much better? Do the new JSON features in SQL Server 2019 compare well? I wanted to see how both systems perform when the rubber meets the road. Setup To do the comparisons, I'm using DICOM files as the data. The protocol details are too deep to get into here, but they're used in health care to transfer information and images between systems. They are distinct documents, with information stored in tags and sequences, and readily translate to a JSON format . Here's a full example . Updates are not rea...