Viewing 15 posts - 1,516 through 1,530 (of 7,613 total)
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 15, 2021 at 7:02 pm
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 15, 2021 at 6:56 pm
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
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 15, 2021 at 1:38 pm
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 14, 2021 at 8:57 pm
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 14, 2021 at 8:25 pm
Why are using the RTM version instead of applying patches? You'll have performance issues from that as well.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 14, 2021 at 3:05 pm
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.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 11, 2021 at 7:22 pm
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 9, 2021 at 9:31 pm
% returns a remainder from division.
So, 1.1 % 1 yields 0.1.
2.54 % 1 = 0.54
3.0 % 1 = 0.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 9, 2021 at 9:26 pm
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 8, 2021 at 2:37 pm
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 7, 2021 at 6:22 pm
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.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 7, 2021 at 3:38 pm
My code works fine with that format of string:
;WITH test_data AS (
SELECT * FROM ( VALUES('(b) Joe Brown -12563'), ('(a) Mary Edwards -15425'),...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 7, 2021 at 3:35 pm
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 DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 7, 2021 at 3:22 pm
SQL provides a CASE expression to test for things like that:
SELECT ..., CASE WHEN PERSON.TERMINATION_DATE > '19000101' THEN 'N' ELSE '' END AS TERMINATION, ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 7, 2021 at 3:19 pm
Viewing 15 posts - 1,516 through 1,530 (of 7,613 total)