Viewing 15 posts - 7,456 through 7,470 (of 7,613 total)
Storing this will allow to keep our query sargable for best index usage.
Is that really necessary?
Won't SQL treat computations on a declared variable as a constant, so they are still...
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".
April 17, 2012 at 3:26 pm
--edited out, essentially same code, lol.
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".
April 17, 2012 at 2:50 pm
You should have something in place that checks for disk hardware errors, with immediate notification and fast response.
And do proper monitoring of the suspect pages table and the SQL error...
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".
April 16, 2012 at 1:07 pm
178M rows shouldn't be a problem per se, if you index properly.
THE biggest factor is getting the correct clustered index.
Get that right, you should have no issues with that many...
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".
April 16, 2012 at 12:54 pm
I use identity as a clus key only as a last resort. Usually there's a better business key value that yields much better overall performance.
On a table I knew...
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".
March 28, 2012 at 1:28 pm
Why use the CHAR function to convert it back to a character?
Why not just check for the ASCII value?
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".
March 28, 2012 at 1:13 pm
I would do a query with:
SELECT
COUNT(*) AS [Total],
COUNT(DISTINCT column_1) AS [Column_1],
COUNT(DISTINCT column_2) AS [Column_2],
...
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".
March 28, 2012 at 1:06 pm
To be safe, I would move everything.
If/as you feel a particular object is not needed, rename it (or move to a different schema/db, if you prefer). See if any...
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".
March 19, 2012 at 3:50 pm
If you can, for now I'd just move the remaining tables temporarily to another filegroup, completely emptying the original fg, then shrink it. Finally move the remaining tables back...
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".
March 16, 2012 at 1:51 pm
Did you deletes of lots of table data in some cases rather than table drops?
At any rate, just to be sure, I would run sys.dm_db_index_physical_stats () on remaining tables 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".
March 16, 2012 at 12:48 pm
The setup and maintenance on full-text indexes is not trivial.
It's not a problem for a full-time DBA, but if you're not a DBA, it can be a tricky thing, especially...
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".
March 15, 2012 at 1:37 pm
I wouldn't automatically add a leading %. That forces a full scan which is often unnecessary if the requestor knows what the first name starts with.
Instead, allow the user...
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".
March 15, 2012 at 12:52 pm
You should be able to update all rows at once, like so:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_TaxID]
ON [dbo].[Item]
after INSERT, UPDATE AS
UPDATE [dbo].[Item]
SET
taxid...
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".
March 12, 2012 at 8:55 am
I would just use a CASE statement.
But you raised a good point I overlooked.
If you do need only current year's data, I should have added a WHERE clause with a...
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".
March 8, 2012 at 3:23 pm
SELECT
name,
SUM(CASE WHEN date_column >= CAST(YEAR(GETDATE()) AS char(4)) + '0101'
THEN total ELSE 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".
March 8, 2012 at 2:34 pm
Viewing 15 posts - 7,456 through 7,470 (of 7,613 total)