Viewing 15 posts - 3,796 through 3,810 (of 7,619 total)
There's an off chance the slowness has to do with log space, particularly if not in tempdb.
How much total space is the new table? If it's large, make...
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".
July 5, 2017 at 11:41 am
In the real world, I've seen the method (1) style used quite a bit. Yes, it does violate 1NF, but it's pragmatic for the task at hand. Bottle size would...
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".
July 3, 2017 at 11:23 am
Most typically in SQL Server I see this technique used:
SELECT TOP ...
FROM dbo.table_name
ORDER BY NEWID()
I presume that gives a roughly random sample. Of course...
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 29, 2017 at 9:57 am
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 27, 2017 at 12:02 pm
Most of the world uses ISO 5218 (0 = unknown, 1 = male, 2 = female, 9 = lawful person)
I've never seen that, and I've been 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".
June 22, 2017 at 2:11 pm
I was in hurry so my code isn't what I'd normally do. Normally I'd calc only the first/last Monday, then simply subtract/add 7 days to that for other dates. There's...
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 22, 2017 at 8:17 am
The method below is math only, and thus very efficient; works under all date settings; and is flexible/reusable for other days / months / etc..
DECLARE...
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 21, 2017 at 2:25 pm
You can use APPLY to effectively assign alias names to expressions / results. You can even nest APPLYs so that the alias from one is used in the next one. 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 21, 2017 at 10:20 am
If you want a rolling 60 months:
WHERE OpenDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 60, 0) /* or -59 if you count the current month...
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 16, 2017 at 11:27 am
From the very brief description you've given, it sounds as if:
LocationID
should be the lead clustering key.
If ItemID is unique within LocationID, then the full key should 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".
June 15, 2017 at 2:56 pm
Probably the typical way to do that using a CASE expression would be:
WHERE
1 = CASE @Fruit
WHEN 'Apples' THEN CASE WHEN...
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 13, 2017 at 9:59 am
A loop is just as efficient, or close enough to it, for doing what you want here as any other method would be.
I suppose you avoid a loop...
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, 2017 at 8:13 am
1) I don't see why READPAST would lead to deadlocking.
2) I would think you do need a transaction to make the READPAST work properly. You want the first UPDATE lock...
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, 2017 at 2:40 pm
For a temporary "staging" table, where data just waits to be loaded into permanent table(s), no.
But all permanent tables should have at least one candidate key -- i.e....
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, 2017 at 2:19 pm
I have very little time right now, but I want to point out at least a few things.
All tables: Verify the tables are in 2NF and 3NF.
Other...
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 5, 2017 at 11:47 am
Viewing 15 posts - 3,796 through 3,810 (of 7,619 total)