Lynn Pettis (9/1/2015)

---

Sean Lange (9/1/2015)

---

ScottPletcher (9/1/2015)

---

Honestly, though, I still think that overall the "use identity as the 'default' clustering key" myth is even more damaging than an "almost always use...

Lynn Pettis (9/1/2015)

---

Sean Lange (9/1/2015)

---

ScottPletcher (9/1/2015)

---

Honestly, though, I still think that overall the "use identity as the 'default' clustering key" myth is even more damaging than an "almost always use...

Jacob Wilkins (9/1/2015)

---

ScottPletcher (9/1/2015)

---

Jacob Wilkins (9/1/2015)

---

I know it's rather off-topic, but it's an interesting diversion, so I thought I'd just chime in on NOLOCK and temporary tables.

SELECT queries against temporary...

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)

---

drop table #temp

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')...

Jacob Wilkins (9/1/2015)

---

I know it's rather off-topic, but it's an interesting diversion, so I thought I'd just chime in on NOLOCK and temporary tables.

SELECT queries against temporary tables typically only...

Sean Lange (9/1/2015)

---

ScottPletcher (9/1/2015)

---

Sean Lange (9/1/2015)

---

Munabhai (9/1/2015)

---

drop table #temp

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') ...

Sean Lange (9/1/2015)

---

Munabhai (9/1/2015)

---

drop table #temp

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...

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.

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

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:

...

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...

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...

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. ...

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...

The most critical element for performance is the best clustered index on every table. That is rarely an identity column.

Therefore, the first thing you need to do is review...