Do you have an underlying "master" table for these codes? If so, add a sort_sequence value to that table and sort based on that. Then you don't have...

sqlvogel (12/22/2014)

---

Alexander Suprun (12/22/2014)

Are you saying that having a big composite primary key on multiple varchar columns which also has to be used in other tables for referential integrity has...

Jeff Moden (12/22/2014)

---

CELKO (12/22/2014)

---

Pull all the punctuation out and pad with digits as needed until you get the Standard International phone number. Let the presentation layers handle presentations.

http://en.wikipedia.org/wiki/E.123

You also...

Ray Herring (12/22/2014)

---

I think we see a common misunderstanding illustrated here.

We think of and represent DateTime as a string and SSMS (and most other providers) obligingly display the value as...

zerbit (12/22/2014)

---

Ok, but the point stated was to use a bigint to improve performance not to get...

If you're going to do this, do it much more efficiently without all the unnecessary variables:

alter function fn_generate_bigint(@datetime datetime)

returns bigint

as

begin

return (

select (

...

PiMané (12/19/2014)

---

yep. DMV queries... already use it... Thanks 🙂

the problem is that all the possible indexes generate lots of fragmentation and right now the main issue is to reduce page...

PiMané (12/19/2014)

---

CELKO (12/19/2014)

---

Remember that first class on SQL? The fundamental concepts the first week? A key is a subset of attributes of an entity which uniquely identifies it!

This...

Edit: Don't have data to test, but something like this should do it.

SELECT tn.ID,ca.Client,tn.TxDate,tn.GrossCost

FROM table_name tn

CROSS APPLY (

SELECT tn.Client AS Client

UNION ALL

...

1) Cluster the table on ( Date, TableName )

2) Edit: The variables for date are just in case you later want to keep more history and do other rowcount comparisons....

Not sure. I suggest running both queries and then looking at the plan cache:

SELECT ecp.usecounts, ecp.cacheobjtype, ecp.objtype, est.text

FROM sys.dm_exec_cached_plans ecp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) est

WHERE est.text LIKE '%field_enum_values%'

PiMané (12/18/2014)

---

There is no problem having an uniqueidentifier as the pk since it ain't the cidx.

You can have the pk on the uniqueid and have a fillfactor of 70 or...

You can do the full db backup ahead of time. You can do a differential just before you do the index changes.

Script out all existing non-clustered indexes ahead of...

Here's a possible re-write of the first temp table INSERT:

insert into #temp_violation

select

tv.OrderID

,tv.StartDate

,tv.EndDate

,ca1.HourID

from EMSTBLSN.dbo.TimeViolation tv

cross apply (

select distinct fh.HourID

from G4SFEP1.dbo.Service fs

...

SELECT name

FROM table_name

UNION ALL

SELECT DISTINCT LEFT(name, 1) AS letter

FROM table_name

ORDER BY name