I'm unsure what value a leading '9' should yield as the slab#, it's not really clear from your OP.

SELECT 
    LEFT(ZIP, 1) + CASE...

• This reply was modified 4 years, 9 months ago by ScottPletcher.

WHERE t_stamp >= DATEDIFF_BIG(MS, '19700101', GETDATE()) - (1000 * 60 * 30)

Note that, for important performance reasons, you do not want to use any function at all on the column...

IF EXISTS(
SELECT* 
FROMsys.columns 
    WHEREName = N'ReferenceName'
AND Object_ID = Object_ID(N'dbo.tbl_Reference')
)
BEGIN
    EXEC ('UPDATE [dbo].[tbl_Reference]
SET ClientReferenceNumber = PARSENAME(REPLACE(ReferenceName,''_'',''.''),2)
FROM dbo.[tbl_Reference]')
END

Create a "base" db with the schema, tables, etc. that you need.

When you need to create a new db, restore a backup of that db to the new db name...

SELECT 
    STUFF(NAME, 1, CASE WHEN LEFT(NAME, 5) = 'RECA-' THEN 5 ELSE 0 END, '') AS NAME
FROM dbo.table_name

A potential problem with REPLACE is...

• This reply was modified 4 years, 9 months ago by ScottPletcher.

Why are using the RTM version instead of applying patches?  You'll have performance issues from that as well.

Actually, best is often to force max values out of row, for every row.  That is generally my preference, although naturally there are a few specific exceptions.

Would have to see the query's use of col1 thru col8: in WHERE, JOINs, GROUP BY and even the SELECT itself.

SQL Server's missing index feature doesn't properly "understand" the importance...

% returns a remainder from division.

So, 1.1 % 1 yields 0.1.

2.54 % 1 = 0.54

3.0 % 1 = 0.

Came across an actual query which gave me an idea about another version of the test script for DISTINCT vs. GROUP BY.

create table #BalanceType (
id int...

SELECT j.name AS job_name, jh.*
FROM (
    SELECT jh.job_id, MAX(jh.instance_id) AS instance_id
    FROM msdb.dbo.sysjobhistory jh
    WHERE jh.run_status = 0...

You need more than just year to accurately calculate age.  Some people born in 2011 are 10 years old, but some are only 9 years old.

My code works fine with that format of string:

;WITH test_data AS (
    SELECT * FROM ( VALUES('(b) Joe Brown -12563'), ('(a) Mary Edwards -15425'),...

Make catalog changes to that your new tempdb file set up takes effect the next time SQL starts up.  You just have to delete the old tempdb files yourself.  I...

SQL provides a CASE expression to test for things like that:

SELECT ..., CASE WHEN PERSON.TERMINATION_DATE > '19000101' THEN 'N' ELSE '' END AS TERMINATION, ...