Viewing 15 posts - 1,036 through 1,050 (of 7,613 total)
...
(
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
You could probably use TRIGGER_NESTLEVEL to do that.
I tend to use session context values instead, set via sys.sp_set_session_context and SESSION_CONTEXT(N'<key_name>'). If you want more info on this method, let me...
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:32 pm
Do you have an index on table_a that covers the UPDATE query? That could help prevent some contention.
You could also try to "help" SQL by explicitly "telling" SQL about 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 20, 2022 at 5:15 pm
I agree with Steve.
Also, wouldn't you want the MIN() time for the LogOn?
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:09 pm
The customer would have an identifying customer number and account number, and the ATM would have an identifying ATM number.
Therefore, the transaction would broadly only need to capture cust#, acct#,...
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:07 pm
That join is not needed.
are the data changes made by a trigger included in the transaction, so that they are rolled back if the INSERT or UPDATE on the table...
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:01 pm
The first big difference is that the second command is not valid, and thus won't run and therefore won't do anything.
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 17, 2022 at 4:29 pm
Viewing 15 posts - 1,036 through 1,050 (of 7,613 total)