Viewing 15 posts - 1,036 through 1,050 (of 7,619 total)
Using a sub-SELECT should help, even if the main "table" is actually a view.
SELECT job_op.seq, job_op.description, job_op.part, job_op.router, job_op.router_seq,
(SELECT /*TOP (1)*/ job_op_wc.workcenter...
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".
January 24, 2022 at 10:17 pm
Just off the top of my head, here's on way:
DROP TABLE IF EXISTS #RELATIONSHIP_FLATTENED;
SELECT TOP (0) [CASE], INDIVIDUALID AS ID
INTO #RELATIONSHIP_FLATTENED
FROM RELATIONSHIP
--
CREATE UNIQUE CLUSTERED INDEX [RELATIONSHIP_FLATTENED__CL]
...
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".
January 24, 2022 at 10:10 pm
Did you pre-allocate enough log space to handle the entire insert?
I had a load task that took 4+ hours. I noticed the initial log size was very small. After 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".
January 24, 2022 at 9:53 pm
What "index maintenance" do you perform on the heap?!
I think a REBUILD should release unused space from a heap. REBUILD is just:
ALTER TABLE <your_table_name_here> REBUILD;
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".
January 24, 2022 at 8:29 pm
That load query is fairly straightforward. I think SQL should be able to write output as it is generated, reducing memory requirements. Would have to see the query plan 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".
January 24, 2022 at 8:14 pm
I think this will help ( but maybe not?! 🙂 ). What I sometimes do is rather than using fully dynamic SQL, I use placeholder column names and then rename...
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".
January 24, 2022 at 8:04 pm
...
(
SELECT
EPName,
NTUserName,
ParsedUserName,
CAST(FileSize AS decimal(35,2)) AS FileSize, --<<--
SUBSTRING (NTUserName,0,CHARINDEX('\',NTUserName)) AS Domain,
...
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".
January 24, 2022 at 7:22 pm
SELECT DISTINCT GRPID, PLNCD, XPLNE, EXTNO, EffDate, TermDate
FROM History
WHERE MBRNO IN (SELECT MBRNO FROM dbo.Member_MRN_lkupAllMBRNOs(@strMBRNO))
ORDER BY EffDate ASC
How large (MB/GB) is the "History" table?
How is it clustered?
If it's a (very)...
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".
January 24, 2022 at 7:15 pm
In SQL Server, you can use DELETE from a table using a JOIN. Just be sure to alias the table you're deleting from and DELETE from the alias, not 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".
January 24, 2022 at 3:46 pm
Sound like this to me?:
;WITH cte_pat_ordered_by_fac AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY p.patient_id ORDER BY p.facility_id) AS row_num
FROM patient...
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".
January 24, 2022 at 3:44 pm
--First, I'd strongly urge you to separate the binary data from the main table, which you can do using sp_tableoption.
--Page-compressing the main table will save considerable space, although it may...
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".
January 24, 2022 at 3:29 pm
Oops, quite true, since I did a combined trigger.
I suggest separating the INSERT and UPDATE triggers. Then you can use INNER JOIN and not have to test for INSERT vs...
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".
January 20, 2022 at 8:03 pm
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER tblTriggerAuditRecord
ON dbo.tblOrders
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
INSERT INTO dbo.tblOrdersAudit
( OrderID, OrderApprovalDateTime, OrderStatus, /*...,*/ UpdatedBy, UpdatedOn )
SELECT i.OrderID, i.OrderApprovalDateTime, i.OrderStatus,...
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".
January 20, 2022 at 6:10 pm
The most flexible way is to use CROSS APPLY:
SELECT d.ID, ca1.Amt, ca1.AmtReason
FROM #data d
CROSS APPLY (
SELECT TOP (1) Amt, AmtReason
...
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".
January 20, 2022 at 6:04 pm
Read uncommitted (RU) simply doesn't take out some locks, allowing for reads as data gets changed. This can result in incorrect data, missing or duplicate data. Honestly, a scary...
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".
January 20, 2022 at 5:47 pm
Viewing 15 posts - 1,036 through 1,050 (of 7,619 total)