Viewing 15 posts - 5,806 through 5,820 (of 7,619 total)
Another option is to rename the column(s) then use "SELECT *" for output:
--CREATE TABLER #temp ...
--INSERT INTO #temp ... ...
DECLARE @sql varchar(8000)
SET @sql = 'EXEC tempdb.sys.sp_rename ''#temp.sale'', ''Sales' + CAST(YEAR(GETDATE())...
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".
September 16, 2014 at 1:06 pm
I first look at SQL's usage stats (incl. row counts), operational stats and missing index info. Be aware they get reset when SQL stops and restarts, so you get...
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".
September 16, 2014 at 12:57 pm
You could start with a url summary table and add a details table later if you need to.
I'd still strongly urge you to use an int to represent the url,...
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".
September 16, 2014 at 12:51 pm
Create an index::
keyed on : id
INCLUDE : serialnumber
Use quotes in the WHERE:
WHERE id IN ('1', '2', '3')
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".
September 16, 2014 at 12:47 pm
You can really get around that error directly, as SQL has control of master.
Back up the master db and restore it to a different name.
RESTORE DATABASE master_dbcc_checkdb
FROM DISK = 'x:\full\path\to\master.BAK'
Then...
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".
September 16, 2014 at 11:09 am
You can have odd errors occur in that situation.
You're better off having a fixed, permanent owner for dbs and granting permissions as needed to other users, up to and including...
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".
September 16, 2014 at 11:03 am
The second style would at least have a chance of being fully optimized, particularly if you force a recompile of the query. Stick with it.
I'd avoid the first style...
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".
September 16, 2014 at 11:01 am
The problem with a table is that one option must apply to everyone. Parameter or other methods allow customized runs, so that your own special cases can still 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".
September 15, 2014 at 4:10 pm
Eirikur Eiriksson (9/15/2014)
ScottPletcher (9/15/2014)
Two quick possibilities:
1) Add an optional parameter to the proc which requires a certain value or the proc just...
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".
September 15, 2014 at 3:01 pm
That GROUP BY is gonna kill your performance. See if the code below is close to what you need.
I've added clustered indexes on the temp tables prior to loading...
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".
September 15, 2014 at 2:56 pm
You can't consider missing index info in isolation. At the very least, you also need to look at index usage stats. Both of those are the minimum needed...
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".
September 15, 2014 at 12:27 pm
The big delay is likely in allocating and formatting log space, not in the identity number assignment, which is trivial.
Thus, before you run the big INSERT, pre-allocate sufficient log space...
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".
September 15, 2014 at 12:23 pm
All of those seem far too disruptive to me.
Two quick possibilities:
1) Add an optional parameter to the proc which requires a certain value or the proc just exits (or 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".
September 15, 2014 at 12:09 pm
Luis Cazares (9/11/2014)
Please find the errors in the following code:
INSERT INTO dbo.FactInternetSalesCopy
(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey,...
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".
September 11, 2014 at 4:29 pm
thomashohner (9/9/2014)
So it skips...
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".
September 9, 2014 at 3:14 pm
Viewing 15 posts - 5,806 through 5,820 (of 7,619 total)