Viewing 15 posts - 4,426 through 4,440 (of 7,619 total)
Hugo Kornelis (2/19/2016)
ScottPletcher (2/19/2016)
Log tables are almost always best clustered on a datetime, and virtually never on an identity value.
When it's an insert datetime, I think that this guess 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".
February 19, 2016 at 9:56 am
Hugo Kornelis (2/19/2016)
ScottPletcher (2/19/2016)
You should cluster this table on upsrt_dttm instead.
How can you possibly determine that this should be a good clustering key based on seeing only one single query...
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".
February 19, 2016 at 9:24 am
The "standard" performance problem when clustering on an identity column "by default". Trying to work around the wrong clustered index will generate lots of extra I/O.
You should cluster this...
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".
February 19, 2016 at 9:15 am
Make sure the sub-procs have their own CATCH blocks. You can use empty/dummy blocks if you don't care about errors there:
CREATE PROCEDURE sub_proc
AS
...
BEGIN CATCH
--ignore error here
END CATCH
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".
February 17, 2016 at 3:51 pm
Try the RECOMPILE option. That time will be trivial compared to I/O time.
Also, do review the indexes on the table: getting the best clustered index on every table is...
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".
February 17, 2016 at 3:49 pm
In theory you could:
SET ANSI_WARNINGS OFF
But read up very, very carefully on that before you do it!
Or, you could create a stored proc and pass the values into the proc...
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".
February 17, 2016 at 3:45 pm
Yes.
The first query only considers rows where [LotJob] = '0000337495'.
The second first gets the max LotJob and then that max value must be = '0000337495' for the row...
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".
February 17, 2016 at 9:31 am
Hugo Kornelis (2/16/2016)
ScottPletcher (2/16/2016)
Get rid of the dashes (-) in the date, they introduce errors (ambiguity).Use format 'YYYYMMDD [hh:mm:ss]', which is always interpreted correctly, regardless of language/date settings.
Sorry, but that...
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".
February 16, 2016 at 1:26 pm
Sounds like a good way to get yet more of the dreaded:
String or binary data would be truncated.
error. MS, seriously, you can't tell us which column had the 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".
February 16, 2016 at 1:18 pm
These are unlikely, but let's:
1) add brackets around the db name, just in case.
2) specify a 'dbo' schema for the table name, just in case. It's easy to accidentally...
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".
February 16, 2016 at 1:13 pm
Get rid of the dashes (-) in the date, they introduce errors (ambiguity).
Use format 'YYYYMMDD [hh:mm:ss]', which is always interpreted correctly, regardless of language/date settings.
declare @CLUSTER_CODE as nvarchar(3)
declare@START_MONTH as nvarchar(2)
declare@START_YEAR...
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".
February 16, 2016 at 12:58 pm
For statistics, I'd do what DTA said. For indexes, definitely not, all DTA index recommendations should be thoroughly reviewed before creating/implementing. But for stats, if SQL "says" it...
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".
February 16, 2016 at 12:48 pm
Sorry, don't have much time but here's a quick overview of one method to consider:
1) create another table with the new bigint data type to copy the existing data 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".
February 12, 2016 at 1:24 pm
I would think sys.sql_expression_dependencies would contain those references, since a table-type is in sys.objects, type = 'tt'.
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".
February 12, 2016 at 12:58 pm
ALTER TRIGGER [dbo].[BadUpdate]
ON [dbo].[SomeTable]
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
IF EXISTS (
SELECT 1
FROM dbo.Foo
...
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".
February 11, 2016 at 2:28 pm
Viewing 15 posts - 4,426 through 4,440 (of 7,619 total)