SELECT T1.UNIT,
       T1.DATE,
       CASE WHEN COUNT(T1.PRICE) OVER (PARTITION BY T1.UNIT ORDER BY T1.DATE ROWS BETWEEN...

You need something like this:

DECLARE @var1 nvarchar(30);
SET @var1 = 'Database1';

DECLARE @sql nvarchar(max);
SET @sql = '
USE ' + QUOTENAME(@var1) + ';
-- Add the rest of your script below:
SELECT...

Does that actually make any difference?

I have query which is filling TEMPDB

I need suggestion how to modify this query to which can minimize the TEMPDB space to fill

update #TempShow1 set Name = case...

The day my bank starts using NOLOCK on its SQL Server queries is the day I start ROLLBACK TRANSACTION on my account and JOIN a new bank.

I've changed table [dbo].[CostKeyDeletes]. There is no need for and ID and CostKey should be the clustered index. I've added an extra column to show if the CostKey has been...

Hi Bruin,

Thanks for your question! Could you start a new thread and include the table definitions (DDL), details of any indexes, and explain exactly what you need the cleanup to...

Since you say the updates are not "extremely frequent", it's mostly an "Insert" index by TenantID.  It's a Type 98 Index and since there aren't a large number of...

Hi Jeff

Thank you for the detailed explanation and advice on Fill Factor settings. It is incredibly helpful.

In our case, the clustered index (TENANTID, ID) will likely see updates to rows...

post to get new page

Jeff,

Have you got any recommendations for the fill factor when I create the new clustered primary keys on (TENANTID, ID)?

Where ID is an identity column on the table.

Does partitioning by TENANTID improve query performance when most queries are filtered by tenant? For instance, will partitioning reduce the amount of data SQL Server scans, or is...

Nice.

Also, you don't have to change the FKs in other tables if you need one for just ID.  Instead, just add a unqiue nonclus index on ( ID );...

I've created a stored procedure that takes a table name as a parameter and checks the clustered index column order. If the order is ID, TENANTID, the procedure drops the...

Using YEAR and MONTH Functions:

INSERT INTO #ToDelete (Id)
SELECT Id
 ...