Viewing 15 posts - 6,001 through 6,015 (of 7,619 total)
You can't have a
GO
within a stored procedure. The first GO ends the stored procedure 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".
June 24, 2014 at 3:24 pm
Don't use a simple UNIQUE constraint. You have to define a primary key, and the only way it's really useful to your query is if the first column 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 24, 2014 at 3:06 pm
I'd strongly urge you to use a character-based method rather than simply trying to CAST to numeric, which is almost certain to cause a non-numeric data exception.
SELECT valid_id, SUBSTRING(valid_no, PATINDEX('%[^0]%',...
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 24, 2014 at 2:57 pm
Table variables don't ever get statistics.
Be sure to specify the appropriate clustering key on either table variables for temp tables. Than can make a vast difference in the performance...
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 24, 2014 at 2:43 pm
rquest7 (6/22/2014)
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 24, 2014 at 12:41 pm
If you have time, add a rebuild step to your process:
1. I remove very old records:
DELETE FROM ArchiveDB..a_tblLog WHERE PKID < ?
1B. Rebuild a_tblLog table WITH ( FILLFACTOR =...
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 24, 2014 at 11:05 am
Igor Micev (6/20/2014)
ScottPletcher (6/20/2014)
Is snapshot isolation in use?
The fact that his delete had been waiting to commit tells you that it's not a snapshot isolation level in use.
No, it does...
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 20, 2014 at 1:31 pm
Taking a higher-level view, what's most important for performance of very large tables is making sure the clustered index is based on the best column(s). That can often eliminate...
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 20, 2014 at 1:27 pm
Are you just trying to determine the relative Saturday# within the month? If so, there are methods with vastly less difficulty and overhead.
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 20, 2014 at 1:05 pm
Does the read query use "WITH (NOLOCK)"?
Is snapshot isolation in use?
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 20, 2014 at 12:57 pm
Minor correction:
t.TransactionDate >= 'Apr 2014' AND
t.TransactionDate < DATEADD(mm,1,'Apr 2014') --"<" rather than "<="
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 18, 2014 at 2:20 pm
If you basically just want to copy/clone one table but without the identity, why not just "cancel" the identity as part of the SELECT ... INTO the new 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".
June 18, 2014 at 2:17 pm
If I run a bunch of DELETE statements not wrapped in a BEGIN TRAN, COMMIT TRAN, will my deletes commit after each statement?
Yes, because SQL DELETE | INSERT | UPDATE...
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 18, 2014 at 2:14 pm
seequill (6/16/2014)
They also have indexes on the keys that I am linking them with too.
Verify that those indexes are actually being used; an estimated query plan is enough for
You...
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, 2014 at 4:57 pm
If you are using a native SQL account, you can check in:
sys.server_principals
There is no date specifically for password changes, but the modify_date does get reset when the password is changed.
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, 2014 at 1:13 pm
Viewing 15 posts - 6,001 through 6,015 (of 7,619 total)