Viewing 15 posts - 1,306 through 1,320 (of 7,619 total)
You don't need to shrink the database, but you do need shrink the affected file(s). NEVER shrink a database, only file(s).
Run:
DBCC SHOWFILESTATS
and find the FileId(s) with lots of free space. ...
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".
September 1, 2021 at 11:28 am
I think maybe you can just change the CROSS APPLY in Steve's code?!:
...
cross apply (values (cast(w.CREATE_DATE 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".
August 31, 2021 at 9:32 pm
(1) Cluster the main table on ID: a nonclus index is worthless here.
(2) Cluster the lookup table on ID. The join of the two tables should now become a MERGE...
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".
August 31, 2021 at 8:35 pm
Sure, you can do that. But I can't really follow the logic of the the original statement -- particularly the logic of starting from a negative starting byte, so 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".
August 31, 2021 at 8:30 pm
Sorry, I left out a step.
dbo.DelimitedSplit8k is a function commonly used on this site to efficiently split a string into multiple parts based on a single-character delimiter.
Here's a version of...
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".
August 31, 2021 at 3:42 pm
SELECT PERSONNUM,ORGPATHTXT,LOC
FROM SampleData2
CROSS APPLY (
SELECT TOP (1) LOC
FROM (
...
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".
August 31, 2021 at 7:21 am
I believe SQL caches table variables the same way it does for temp tables. If so, an entries for them would still be in the system tables. It would be...
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".
August 30, 2021 at 6:37 pm
For the actual tables, post the DDL, including all indexes.
For any views, post the view definition.
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".
August 29, 2021 at 7:04 am
Agree, there's no super-easy, "automatic" way to do that. However, you can generate a script(s) that will change each column as needed, so it's not like you have to do...
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".
August 27, 2021 at 3:18 pm
To allow me to write full code, would you provide the full DDL for the Error_Log table? The big gain will come from encoding strings as numbers using a conversion...
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".
August 27, 2021 at 7:15 am
Of course it's optional if you make the new clustering index a PK or not.
That is, you could drop the existing PK, create the new clustering index, then re-add the...
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".
August 26, 2021 at 9:00 pm
Get rid of the functions around the usrt_dtm_DateTime column; you don't need them and they will prevent lookup seeks, drastically harming performance:
SET @ToDate = CAST(@ToDate AS date)...
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".
August 26, 2021 at 8:53 pm
If the Production.ProductInventory table is clustered on ProductId (and it almost certainly should be), and you specify a specific ProductId or a list of ProductIds, as in the WHERE clause...
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".
August 25, 2021 at 10:48 pm
Looking at the Clustered Index for the oeordlin_sql table, I'm a wee bit concerned (especially for insert and update performance) over having the leading column be in...
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".
August 25, 2021 at 5:53 pm
SELECT
J.Job,
J.Category,
COALESCE(C.Value, C_All.Value) AS Value
FROM Job J
LEFT OUTER JOIN Category C ON C.Category =...
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".
August 25, 2021 at 4:31 am
Viewing 15 posts - 1,306 through 1,320 (of 7,619 total)