Viewing 15 posts - 6,451 through 6,465 (of 7,613 total)
ChrisM@Work (8/20/2013)
ScottPletcher (8/20/2013)
ChrisM@Work (8/20/2013)
A primary key doesn’t have to be indexed
I thought it did. Doesn't SQL always create an index to enforce a PK?
It has to be indexed 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".
August 20, 2013 at 9:45 am
ChrisM@Work (8/20/2013)
A primary key doesn’t have to be indexed
I thought it did. Doesn't SQL always create an index to enforce a PK?
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".
August 20, 2013 at 9:40 am
Don't make it a primary key, just make it a unique, clustered index.
CREATE UNIQUE CLUSTERED INDEX CX_ACC_Name
ON dbo.ACC_STRUCTURE ( ACC_Name )
--naturally chg WITH options as needed
WITH (...
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".
August 20, 2013 at 9:38 am
You could:
1) restore the db, and immediately issue a USE <restored_db> command and set the db to single user mode;
2) delete all the users, schemas, etc., that you don't want...
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".
August 20, 2013 at 9:33 am
Erland Sommarskog (8/20/2013)
curious_sqldba (8/20/2013)
In sys.sysprocesses wait type is always 'SOS_SCHEDULER_YIELD' when my CTP is 5 and MAXDOP is 0, what do you want me to check in sys.dm_os_tasks ?
How 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".
August 20, 2013 at 9:24 am
INSERT INTO #tempTable
EXEC dbo.someStoredProc
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".
August 20, 2013 at 8:53 am
Here's one way:
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, c.month), 0) AS Month,
SUM(c.sales) AS Month_Sales
INTO #Monthly_Totals
FROM dbo.Car c
WHERE
c.month >= '20120401' AND --chg...
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".
August 20, 2013 at 8:34 am
What are the results of this query:
SELECT
cpu_count / hyperthread_ratio AS CPUs, *
FROM sys.dm_os_sys_info
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".
August 19, 2013 at 2:45 pm
Data compression should also be considered, if on Enterprise Edition. [My company has some extremely large audit/logging tables that are rarely read. After compressing them, I often shrink...
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".
August 19, 2013 at 11:35 am
Sometimes a database file shrink is indeed needed.
Shrink does not automatically fragment every index. Absolutely you should test for fragmentation after the shrink, and rebuild accordingly, i.e., basically run...
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".
August 19, 2013 at 11:28 am
You should also consider putting the quarter as the first key column of the clustered index. Then you won't need to partition in any way as long as 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".
August 16, 2013 at 3:19 pm
Change Tracking or Change Data Capture also might be useful, depending on your specific requirements.
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".
August 16, 2013 at 3:13 pm
In the context of a LEFT JOIN, yes, the queries would return the same rows from TableA (although, as noted, they might appear multiple times because of the join).
That 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".
August 16, 2013 at 3:11 pm
You shouldn't have any direct issues from doing that.
First, drop the existing nonclus index, then create the clus index.
Build the index ONLINE if at all possible, to minimize disruption 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".
August 16, 2013 at 2:56 pm
Instead of constantly disabling/enabling the trigger, you can set RECURSIVE_TRIGGERS off for the db, unless you really need recursion for other triggers. In that case, you can use CONTEXT_INFO...
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".
August 16, 2013 at 2:53 pm
Viewing 15 posts - 6,451 through 6,465 (of 7,613 total)