Escape to the Sky

Scalar UDFs are good if you're working with a handful of rows or a variable.  However, when you throw a lot of rows at them, they generally slow down your queries.Microsoft recommends inlining them whenever possible.   Microsoft Website . Let's see if they are right. Let's say we have to Proper Case a set of rows, just because where we live, if you don't Proper Case, you're an outcast. Step 1 - Create table CREATE TABLE dbo . ProperCasing (      IDCol int not null identity primary key ,      StringCol varchar ( 500 ) ) Step 2 - Add a ton of rows INSERT INTO dbo . ProperCasing ( StringCol ) SELECT ( 'This object has the name ' + OBJECT_NAME ( Object_Id )) FROM sys . Objects GO 1000 -- Scalar UDF to ProperCase a string that works in 2016, 2017, etc -- You can use STRING_AGG in 2017+ and use STUFF in 2016 Step 3 -  Create Scalar UDF to ProperCase a string -- You can use STRING_AGG in 2017+ and STUFF in ...

When you have few rows in your tables, you may be tempted to return all rows to an API or Client call, and let that layer deal with pagination.  This works, till a certain tipping point, after which calls will become slow and your APIs are cursed at and your UIs cussed out by users who're waiting and waiting.  You can avoid this in different ways, and one of my favorite methods is to paginate right from the database, with a cap of how many rows you will return in a call. Coupled with good indexing on the columns the API or UI requests the sorting/ordering on, you can scale better.  Let's look at a simple example of Offset pagination. Step 1 = Create Table for our demo CREATE TABLE dbo . OffsetPaginationUsers ( UserId integer not null identity Primary Key , FirstName varchar ( 255 ) not null, LastName varchar ( 255 ) not null ) Step 2 = Add dummy rows IN SERT INTO dbo . OffsetPaginationUsers ( FirstName , LastName ) SELECT TOP 100 Nam...

A client (if you're self-employed) or a Business Analyst (if you're employed) approaches you and demands "We have a new project that we need you to create a database for". In the conversations below, I will show you how I approach the process of requirements gathering.  I am by no means an expert and I learn new things and correct my old mistakes daily, so if you have better suggestions or see mistakes, please feel free to reach out and let me know.  I love to learn new things all the time.  Lines in blue/italics are their statements, while lines in black regular are mine. Part 1 We have to store geo political data for some of our states By our states, do you mean the USA only? Well, for now, yes. So in future you do see this project growing to other countries? hmmm, probably Canada and Mexico How about Britain or France or Brazil or .. You know what, let's assume this will cover the entire world at some point if it goes well This type of conversation represents m...

Multi-Statement Table Valued Functions (MSTVF) MSTVFs can be useful, when the number of rows returned is about 100 or less. Since SQL Server cannot estimate the returned rowcount correctly, it defaults to a row estimate of 1 or 100 depending on the SQL Server version. Even if the MSTVF returns a lot of rows, SQL Server opts for serial execution rather than parallel, due to this underestimation problem. Let's look at a simple example (SQL Server 2019 running on Compat Level 130) I am not using interleaved execution or any new fancy features for this demo, which are supposed to help improve performance. Step 1 = Change Cost Threshold For Parallelism (CTFP) WARNING - DO NOT RUN THIS CTFP configuration change ON ANY SERVER without knowing the  consequences ****. What we're doing is reducing the Query Cost, at which SQL Server  will decide to go for parallel execution. This is done for this demo, to ensure that  SQL Server will go parallel at a lower cost than usual E XEC sp_...