Actually, it is a terrible idea to add an identity to this table.

Depending on your specific data, future inserts could be sequential anyway. Even if not, rows are inserted...

Identity is a terrible idea on that table. Use the existing columns, in whichever order best matches your use of the table: either ( Item, ItemNumber ) or (...

Jeff Moden (1/1/2014)

---

fahey.jonathan (12/12/2013)

---

If you don't want to create a Tally table, you can use sys.columns instead and create a tally number in memory. According to my research, this...

Rando (1/2/2014)

---

Still searching for a solution. Scott's solution yield's syntax errors and Chris' solution yields the entire date field...

Again, I'm just looking for the results to be the following;

Date: 12/30/2013

Time:...

SELECT

CONVERT(varchar(10), CAST(CounterDateTime AS datetime), 101) AS Date,

RIGHT(CONVERT(varchar(20), CAST(CounterDateTime AS datetime), 100), 7) AS Time

FROM table

Jeff Moden (12/24/2013)

---

Awesome! I don't believe anything will...

If you don't have any data longer than 24 bytes (or so), it might be easier just to "brute STUFF" it:

WITH SampleData (MyStr) AS

(

SELECT '12345678'

...

Jeff Moden (12/20/2013)

---

ScottPletcher (12/20/2013)

---

Evil Kraig F (12/20/2013)

---

ScottPletcher (12/20/2013)

---

Admittedly the gain is much clearer when the clustering key is the ISNULLed column. So, let's just flip the clustering keys on...

Evil Kraig F (12/20/2013)

---

ScottPletcher (12/20/2013)

---

Admittedly the gain is much clearer when the clustering key is the ISNULLed column. So, let's just flip the clustering keys on your original table...

Evil Kraig F (12/20/2013)

---

ScottPletcher (12/20/2013)

---

Admittedly the gain is much clearer when the clustering key is the ISNULLed column. So, let's just flip the clustering keys on your original table...

Evil Kraig F (12/20/2013)But, I'd like to bring us back to the origination of this minor alteration in the direction of this topic, and that's SARGability and effeciency and if...

Evil Kraig F (12/20/2013)

ScottPletcher (12/20/2013)

---

There could be great harm in it if either (1) SQL could have done seeks on both terms and produced the final output more efficiently than...

Jeff Moden (12/20/2013)

---

ScottPletcher (12/20/2013)

---

Jeff Moden (12/20/2013)

---

ScottPletcher (12/19/2013)

---

I don't object to ISNULL() in the SELECT list, just in WHERE conditions. Since it shouldn't be used in WHERE, it's only consistent...

Jeff Moden (12/20/2013)

---

ScottPletcher (12/19/2013)

---

I don't object to ISNULL() in the SELECT list, just in WHERE conditions. Since it shouldn't be used in WHERE, it's only consistent not to use...

Snargables (12/19/2013)

---

I had to create an index that added all the columns in the include of the index. This was because i created a function that has to return all...