If you do create a tally table, page compress it at 100 fillfactor.  That will save significantly on the overhead of reading the table.  And, since that table is static,...

In SQL Server, the CASE WHEN/THEN results are scalar (single) values only: keywords, operators, etc. are not allowed.  Thus, you need something more like below.

Edit: That said, the expressions within...

• This reply was modified 5 years, 2 months ago by ScottPletcher.

SQL works based on tables.  By definition, a SQL Server table must have a name.

To look at my own version of this:

SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)

The VALUES clause is...

Nvm, OOPS, I posted this same code earlier.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION [DP].[GetYears] ( @StartYear AS INT )
RETURNS TABLE
AS
RETURN
WITH
tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
tally1000 AS (
 ...

• This reply was modified 5 years, 2 months ago by ScottPletcher. Reason: Added SETs before function code

I have always found this to...

After the 30 days, move them to some type of historical table.  Personally I would never permanently delete such data.  Compress it to the max and it will take very...

DECLARE @StartYear SMALLINT = 2017;

SELECT  DATETIMEFROMPARTS(@StartYear + number, 1, 1, 0, 0, 0, 0) AS StartDate,
        @StartYear +...

Not enough details to get very specific, obviously.

However, this rules will always apply:

1. Create the clus index on all temp tables before loading them.  There are techniques for doing this...

What about opportunity cost?  The tally is used here to gen years, so the number is inherently very small and will always be so.  It is really worth the time...

But what if I need to know...

In general, agreed.  Although that specific implementation of an inline tally table is, frankly, horrible.  There's no need for anything remotely close to that number of rows (exceeding 2B for...

would be nice to have something like this i guess, sucks it doesnt work

create table #table
(old_column int)

insert into #table
values(1),(2)

alter table #table
add new_column int default (case when...

You can change the default file locations when installing SQL Server.  You can also move the system databases later if you want to.

Use ENCRYPTBYASYMKEY and DECRYPTBYASYMKEY.