Towering Queries

 I ran into an issue the other day (which my co-worker just ran into as well) when doing a git push, where it fails, saying "RPC failed; HTTP 500 curl 22 The Requested URL returned error: 500 send-pack: unexpected disconnect while reading sideband packet". I found almost no useful articles on the matter; most just talked about adding tracing flags or saying "your internet sucks", neither of which addressed the issue. What fixed it for me was setting the http.postBuffer size to something very large, like git config --global http.postBuffer 157286400 This allowed larger files/repositories or something like that to be posted successfully.

AWS Lambda is awesome, and conceptually, layers are awesome too. Layers are a way to share code between lambdas. However I found the documentation on them a little spartan, and some very core use cases were barely documented at all. What I'm going to do in this blog post is walk you through how to: Create a NodeJS Layer with a custom function in it Create a NodeJS Lambda which consumes that layer And I'm going to do it using SAM. Prerequisites: - VSCode - SAM CLI Creating your Project We're going to make a simple app which contains a layer we wish to reuse across a number of lambda functions (even though we'll just do one here). The layer will export a function which reverses a string provided. We'll then create a lambda function which uses that lambda function and passes data form its input event to the function exported by the layer. I'm going to use SAM CLI for this, but really the only thing I'm gaining from that is to stub out a SAM template for me. You...

Temporal tables are awesome. But they're kind of a pain to work with during those initial stages of development when you're constantly dropping and re-creating objects whole. The process of dropping a temporal table isn't super complicated, just: Disable versioning on the main table Drop the main table Drop the history table To that end, I wrote this stored procedure, marked as a system object (using sp_msmarksystemobject) which you can build which will do those three tasks for you automatically. Even if you don't choose to use the full script, you can still de-construct it for the logic. use master go set nocount on go /***************************** PROC: dbo.sp_droptemporaltable create table Util.dbo.Test ( ID int primary key clustered, StartDate datetime2 generated always as row start, EndDate datetime2 generated always as row end, period for system_time (StartDate, EndDate) ) with (system_versioning = on (history_table = dbo.Test_History)) exec Uti...

There seems to be a lot of confusion around the differences between  temp tables and table variables (in the classic sense; I'm not talking about in memory table types or anything here). Some people say you should only use table variables. Some people say you should only use temp tables. Most people caution a more nuanced approach. Here I want to cover what I think are the biggest issues in an attempt to shed some light on this surprisingly tricky topic. Statistics This is usually the most important factor when considering which one to use. It boils down to two facts, and everything else flows from these: Temp Tables maintain statistics, Table Variables do not. Table Variables Because table variables maintain no statistics, the Query Optimizer always assumes the contain exactly one row. As a result, any joins against a table variable, unless explicitly told to behave otherwise will (probably) always be a nested loop join. As long as the number of rows in the table varia...

I just spent several hours trying to figure this out, so I felt it was worth posting here too. If you are installing Master Data Services on Windows 10, there are a bunch of  IIS Web Application Requirements  you have to satisfy in IIS, or the Master Data Services Configuration Manager will say you're missing all kinds of services and roles. Most of the documentation relates to using Windows Server, since that's usually where SQL is running. But for a localhost instance you might have for testing, it's probably not. And in my case, it's on Windows 10 home   edition . That last fact is what threw me most because even after enabling all the features I could find on the list in the link above, it still didn't work. That's because by default, you need Windows 10 professional edition  to use Windows Authentication, and MDS requires Windows Authentication enabled for IIS to work. So you can either upgrade to Pro or manually enable that feature through the command ...

A few co-workers asked me to explain some of the nuances of different join types (outer joins in particular). I've always reached for Venn diagrams in the past, but I was trying to think of a better way to get the information across. Whether or not I succeeded, I'll let you be the judge. I'm not going to do much in the way of explaining each of the diagrams, but I will label each diagram. I also included a diagram at the end of a common trick for inserting only rows which don't already exist in the target table (using a left outer join, and checking where the right values are null). Please feel free to use this code and the diagrams if you think they're useful. If you do, it would be nice to credit me with them, but I'm hardly going to get mad if you don't. I'd much rather there just be good training materials out there. The Code if object_id('tempdb.dbo.#Cards') is not null drop table #Cards create table #Cards ( NumericValue tinyint n...

One of the core ways of making dynamic sql safe is the use of the QUOTENAME() function. QUOTENAME wraps either square brackets (default), double quotes, or single quotes around a provided string. This is crucial for dynamic SQL because if, for example, you need to concatenate a column name, the only way to make it 100% safe is to wrap it in square brackets so it's treated as an identifier. Conceptually the same as doing this: select [Not A Valid Column] = @@version Background Naively you might think then, that if you had a dynamic sql string like this, you would be safe, since you're wrapping brackets around the identifier declare @ColumnName nvarchar(128) = ''' union all select ''injected code'';--' declare @SQL nvarchar(max) = 'select [' + @ColumnName + '] from sys.columns' select @sql exec (@SQL) /* -- dsql string select [' union all select 'injected code';--] from sys.columns */ And in this example, you ...