Viewing 15 posts - 4,441 through 4,455 (of 7,619 total)
Rather than being "stuck" on tuning, why not take this as an opportunity to possibly tune the query overall and post the code? Perhaps it can be reduced below...
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".
February 10, 2016 at 9:42 am
If you create a new column, and copy existing data to that column, you don't have to UPDATE every row in a single transaction. And you don't have to...
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".
February 10, 2016 at 7:43 am
So the entire 460GB is in the main part of the table, none in overflow space? Often with a row that wide you'd be able to offload a decent...
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".
February 9, 2016 at 3:38 pm
Yeah, should be good, except that I'd use varchar instead of char so that you don't have trailing spaces after the /s are replaced.
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".
February 9, 2016 at 3:30 pm
Sergiy (2/9/2016)
ScottPletcher (2/9/2016)
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".
February 9, 2016 at 2:23 pm
That sounds like Oracle not SQL Server. SS doesn't have CTAS syntax.
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".
February 9, 2016 at 12:32 pm
Maybe code below, if you're looking for a date:
SELECT CASE WHEN PATINDEX('%[_]20[0-9][0-9][0-1][0-9][0-3][0-9][_]%', string) = 0
THEN ''
...
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".
February 9, 2016 at 12:31 pm
Most flexible is probably to put the dbs names/LIKE patterns into a temp table and process only names that have a match in that table:
IF OBJECT_ID('tempdb.dbo.#databases') IS NOT NULL
...
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".
February 9, 2016 at 10:18 am
Perry Whittle (2/9/2016)
ScottPletcher (2/9/2016)
Perry Whittle (2/9/2016)
Luis Cazares (2/8/2016)
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".
February 9, 2016 at 9:53 am
Perry Whittle (2/9/2016)
Luis Cazares (2/8/2016)
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".
February 9, 2016 at 9:35 am
Sergiy (2/8/2016)
Igor Micev (2/5/2016)
The advise from ScottPletcher is just fine. Changing from smallint to...
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".
February 9, 2016 at 8:29 am
Snapshot won't help you, in fact it will very likely make performance worse.
If you only need to refresh the reporting view once a day, back up the original db and...
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".
February 8, 2016 at 12:40 pm
Interesting. The code below should work fine. What error are you getting? Are you sure that time is always formatted correctly?
SELECT CAST(LEFT([Date],8)+' '+Time_on AS datetime)
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".
February 8, 2016 at 10:13 am
The command to change an existing data type is simple enough to issue:
ALTER TABLE dbo.table_name ALTER COLUMN <new_data_type> NULL --or "NOT NULL" of course
--always explicitly specify NULL or NOT NULL,...
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".
February 4, 2016 at 5:00 pm
Since using character data tends to slow things down, maybe this:
SELECT DATEADD(DAY, b.Fiscal_Year_End_Day_Nbr - 1,
DATEADD(MONTH, b.Fiscal_Year_End_Month_Nbr - 1,
...
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".
February 4, 2016 at 10:34 am
Viewing 15 posts - 4,441 through 4,455 (of 7,619 total)