Viewing 15 posts - 4,831 through 4,845 (of 7,613 total)
Finally, there's the cost to fix each issue.
Once people are properly convinced that NOLOCK is a bad idea in many, many cases, they simply remove those specific bytes -- "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".
September 2, 2015 at 9:00 am
Lynn Pettis (9/1/2015)
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
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 1, 2015 at 1:58 pm
Lynn Pettis (9/1/2015)
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
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 1, 2015 at 1:51 pm
Jacob Wilkins (9/1/2015)
ScottPletcher (9/1/2015)
Jacob Wilkins (9/1/2015)
SELECT queries against temporary...
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 1, 2015 at 1:10 pm
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
Sean Lange (9/1/2015)
Munabhai (9/1/2015)
CREATE TABLE #TEMP
(
date1 DATE,
date2 DATE
)
insert into #TEMP values
('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),
('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),
('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01')...
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 1, 2015 at 1:08 pm
Jacob Wilkins (9/1/2015)
SELECT queries against temporary tables typically only...
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 1, 2015 at 12:42 pm
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
Sean Lange (9/1/2015)
Munabhai (9/1/2015)
CREATE TABLE #TEMP
(
date1 DATE,
date2 DATE
)
insert into #TEMP values
('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),
('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),
('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') ...
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 1, 2015 at 11:58 am
Sean Lange (9/1/2015)
Munabhai (9/1/2015)
CREATE TABLE #TEMP
(
date1 DATE,
date2 DATE
)
insert into #TEMP values
('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),
('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),
('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0
WHEN...
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 1, 2015 at 11:41 am
Instead of a view, look into creating an inline table-valued function. That can function essentially as a parameterized view and is very efficient at doing so.
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 1, 2015 at 10:27 am
select
OBJECT_NAME(constraint_object_id) constraint_naam
, constraint_column_id as constraint_column_sequence
, OBJECT_NAME(parent_object_id) Child_table
, parent_column_id
, COL_NAME(parent_object_id, parent_column_id) as child_column
, OBJECT_NAME(referenced_object_id) Parent_table
, referenced_column_id
, COL_NAME(parent_object_id, parent_column_id) as Parent_column
into ##C
from sys.foreign_key_columns
--order by constraint_naam, constraint_column_sequence
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 1, 2015 at 10:20 am
When you store dates as character, store them as:
YYYYMMDD
which always works under any/all SQL settings.
For the current data, you put it into that format to check 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".
September 1, 2015 at 10:06 am
The slowness must be in one of the functions. We would need to see the function code to tune those.
But you can get rid of the local variables for...
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 31, 2015 at 12:48 pm
If the tables are part of a live system, I'd always want to see at least the current missing index stats and index usage stats from SQL itself (cardinality can...
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 31, 2015 at 9:40 am
I, too, await what Jeff will propose as the best method to identity the proper clustered indexes, since we clearly very differently weigh what's most important in that regard. ...
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 29, 2015 at 3:47 pm
Create a new, work db. Create 275 procs from the existing code. Hopefully that's as as adding "CREATE PROCEDURE proc_NNN AS " to the beginning of the code...
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 29, 2015 at 2:30 pm
Viewing 15 posts - 4,831 through 4,845 (of 7,613 total)