Desiderata Errata

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

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

Recently, I've been looking for a good way to automatically generate a data dictionary for multiple databases. Some are internally developed, and others are vendor-provided. My company already had licenses for SAP Power Designer, but the reports generated by that software didn't have a great layout for end-users, and reverse-engineering a PDM from a database would choke on the complicated databases with 100+ tables and other objects. I needed an easy way to use a database or SQL script to generate the documentation. Criteria: Open-source is preferred, but not mandatory HTML or PDF output List basic objects like tables, indexes, and foreign key references to other tables Show the details on each object, like datatype and included index columns Showing information in extended properties would be nice, but not necessary Editing the metadata would not be necessary, as that wouldn't be allowed on vendor db's Cross-platform support for MS SQL, DB2, and MySQL A w...

SQL Server offers several methods to find a substring in expressions. I extended a post  found here by adding the PATINDEX method, and also checking against the legacy cardinality optimizer to see if there were any differences under that query hint. 5 methods were used: LIKE SUBSTRING – can only use this method if the substring either starts or ends the string, and only available in SQL 2016+ LEFT/RIGHT – can only use this method if the substring either starts or ends the string CHARINDEX PATINDEX 4 situations were compared: A clustered indexed column A non-clustered indexed column A non-indexed column A loop through a list of strings, using an “IF (<method>)” to search inside the string on each loop iteration (not a common usage) Results under OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) Results under SQL Server 2017 Summary of results: If you force the legacy cardinality: PATINDEX and CHARINDEX are the ...